Welcome to HTML DB

Configurable cancel command Wednesday May 16th 2012 03:31 AM
Ask Tom MOST RECENT ARTICLES
<code>Tom, as part of our database tidy up I was attempting to cut the output from the SQL*Plus session on a unix terminal emulator (AttachMate Extra) to include in an email to report progress to other members of the project team. Imagine my gri...

SQL%Rowcount equivalent for MERGE Wednesday May 16th 2012 02:30 AM
Ask Tom MOST RECENT ARTICLES
When I insert or update, I can find out using SQL%ROWCOUNT how many rows were affected. But how do I find out how many rows a MERGE statement does? Any help would be greatly appreciated. Thanks

Evaluate Expression Wednesday May 16th 2012 02:30 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, How can I evaluate a character string ? For example, if I have a character string '5 * (4 + 6)' how can I evaluate it to 50 Or character string '1/3' to 0.3333333 Thanks Roop </code>

SPFILE parameter Wednesday May 16th 2012 02:30 AM
Ask Tom MOST RECENT ARTICLES
<code>If the instance is running with SPFILE parameter and you have changed the dynamic parameter to SPFILE only. At this time it does not validate the new parameter value. Now if you shutdown the database and start the database, it fails as SPFILE h...

Optimistic Locking in 10G Wednesday May 16th 2012 02:30 AM
Ask Tom MOST RECENT ARTICLES
<code>I've just having a poke around the doco for 10G. I came across this. </code> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/pseudocolumns006.htm#sthref707 <code> Usually in an optimistic locking strategy you would re-q...

what is redo log sync and what more can be done about it Tuesday May 15th 2012 03:30 AM
Ask Tom MOST RECENT ARTICLES
<code>If I see redo logs are causing log sync wait event on the application processes, what could be the cause? Too often commits, too small log buffer (1M at present on solaris 2.6), some resource limit? How does one figure if 1M log buffer is su...

Rows locks from select for update clause Tuesday May 15th 2012 03:30 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, From my knowledge of oracle, i understand that SELECT FOR UPDATE clause acquires row locks. In that case, if in a given table, when my query updates one set of data, same query should be able to update different set of data by using se...

Analyze Tkprof output Tuesday May 15th 2012 02:30 AM
Ask Tom MOST RECENT ARTICLES
I am trying to interpret this output and it is not making much sense. Thank you Tom. --------------------------------------------------------------------- SELECT COUNT(*) FROM SACNTPR SAC , SAPROD SAP , SACONTR SA WHERE SA....

Oracle Database 11g Release 2: Performance Tuning Tips and Techniques (Richard Niemiec) Tuesday May 15th 2012 12:26 AM
Oracle FAQ blogs

I've been asked by the publishers to review "Oracle Database 11g Release 2: Performance Tuning Tips and Techniques" by Richard Niemiec of TUSC. Conclusion: it is good but not perfect. This is an extended version of the review I put up on Amazon.

Mass Delete Monday May 14th 2012 06:29 AM
Ask Tom MOST RECENT ARTICLES
<code>Tom, Two Very simple questions for you. A. In a datawarehouse environment there are instances when after loading say more than 500 million records one discovers that one of the loads that loaded 200 million records was not correct and ne...

paging through results on the web. Monday May 14th 2012 06:29 AM
Ask Tom MOST RECENT ARTICLES
<code>I have a Search field in my form. When my user enters a value in the Search field, I want to display the first 10 rows which match the query, then the next 10..and so on. I will give him 2 button (Previous 10 & Next 10) options. How do I ...

Shutdown/startup activity Monday May 14th 2012 01:29 AM
Ask Tom MOST RECENT ARTICLES
<code>Tom, I'm required to produce a monthly report with shutdown/startup activity for a database {hopefully there will be nothing to report:)} I just want you to comment on my aproach, and maybe you'll have a better idea on how to automate this....

SELECT columns with conditions. Sunday May 13th 2012 06:28 AM
Ask Tom MOST RECENT ARTICLES
Hi Tom, What I need to do is select column A (credit_hours) data if it is not null, otherwise select column B (credit_hours_low? Thanks Ravi

database vs instances Sunday May 13th 2012 02:29 AM
Ask Tom MOST RECENT ARTICLES
Tom, I am little confused about Oracle Database and instances. If I have created a database DB1, and started it,open for all users, does it mean it is one instance? How can I have multiple instances of a database and how do I find what Instance I...

how to import sequence Sunday May 13th 2012 12:28 AM
Ask Tom MOST RECENT ARTICLES
<code>export is done on table level using 9.2.0.1 exp user/password tables=emp,foo file=test.dmp during import sequences never got imported. This is the default behavior of oracle. I would appreciate if you please advise on the followings: 1. How...

Like operator Sunday May 13th 2012 12:28 AM
Ask Tom MOST RECENT ARTICLES
Hi TOM, I have one question, which is asked recently in interview. I was trying to solve but i was unable to solve. select * from emp; 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1...

String Concatenation Saturday May 12th 2012 01:27 PM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, Is there anyway that I can just use SQL to do string concatenation on a VARCHAR2 field much the same way that a SUM() function is used on a NUMBER field. For example, SELECT SUM(revenue), department FROM sales_table ...

Reading deadlock trace files Saturday May 12th 2012 01:27 PM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom ! How can I understand which two rows where involved in the following deadlock, which appears in this excerpt of a tracefile ? Especially in the last two lines, Oracle seems to convey this information, but I was not able to locate...

consistent reads Saturday May 12th 2012 12:27 PM
Ask Tom MOST RECENT ARTICLES
<code>Hi I have been wondering what are consistent gets and db block gets, according to the docs consistent gets are reads from rollback segments. Now this puzzles me, why everytime I excute any query consistent gets are never 0, even there arent ...

Returning Sub Summary Report with Apex Saturday May 12th 2012 01:27 AM
Ask Tom MOST RECENT ARTICLES
Hi Tom. I have an application written using apex 3.0.1 and oracle xe. I have a report that I am trying to create which I need to return a weekly work summary list by supported application. I know how to do this using java and result sets but w...

Is a composite primary key a good idea? Thursday May 10th 2012 04:25 PM
Ask Tom MOST RECENT ARTICLES
<code>Tom, I have a table that has 3 fields that, when considered in combination, could uniquely identify each record. These fields are... Object_ID CHAR(4) Ticket_Number NUMBER Start_DateTime DATE It will never be the case that any...

Secure Application Role Disable Role Thursday May 10th 2012 11:25 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom, I was learning Secure Application Roles and created an example to test it. Everything worked fine to actually create and enable the role, but I'm getting an error disabling the role (actively disabling it, not just letting the end o...

Predicate pushing in views containing analytic functions Thursday May 10th 2012 07:25 AM
Ask Tom MOST RECENT ARTICLES
<code>Tom, I have a view in my database that contains an Analytic function. The analytic function windows on a set of rows in the table and returns a subset of them. The initial testing using this view was quite promising, as the work performed ...

R12 - XLA / FAH - Business Events in Create Accounting Wednesday May 09th 2012 02:42 PM
Oracle FAQ blogs

The following business events are disabled as default by Oracle.
For any custom process those can be enabled by users. But only extract and postprocessing events will be called in document mode.
Custom code / package can be used as a subscription to the respective business event.

oracle.apps.xla.accounting.preaccounting  - To identify the events, which will be processed by create accounting
oracle.apps.xla.accounting.extract        - To populate the transaction objects only for the events in xla_events_gt

Is RAC the logical choice for a modern database system? Wednesday May 09th 2012 12:24 PM
Ask Tom MOST RECENT ARTICLES
<code>We are in the planning stages of implementing a fairly large datawarehouse. The current thinking for size estimates is 250 GB to start and to grow by about 100 GB per year. We don't require high availability, but we would like to be able to sca...

Flushing Out Database Buffer Cache/parsed sql query from shared pool. Wednesday May 09th 2012 12:24 PM
Ask Tom MOST RECENT ARTICLES
Hi Tom, On many occasions during load testing/other PL/SQL code testing we run queries using different approach to find the optimum approach. In such cases we want to start fresh with each approach and do not want data/parsed query to pre-exist (f...

Not Null Constraint Wednesday May 09th 2012 12:24 PM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom Thanks for previous help !!! 1.Could you let me know how to disable,enable,drop a NOT NULL constraint ? 2. What is the meaning of following values in Constraint_Type in DBA_CONSTRAINTS view ? Value : - "O" , "V", "C" Th...

CBO doesn't do well with a function call as a condition in the Where clause Wednesday May 09th 2012 08:24 AM
Ask Tom MOST RECENT ARTICLES
Tom, I saw for the first time a function call being used as one of the qualifiers in the "Where Clause". The "Where Clause" looks similar to this: <code>Select ?. (Select ? WHERE a.a = b.a AND b.b = c.b AND c.d=d.d AND packageA.check_cond...

Convert date to string and compare, or convert string to date and compare? Wednesday May 09th 2012 08:24 AM
Ask Tom MOST RECENT ARTICLES
Tom, please consider this: I have a database/table with a DATE column that has no index and has the format "DD-MM-YYYY". I would like to use a standard format in my PL/SQL code that does not lead to implicit database conversions between da...

shoud I use char or varchar2 Tuesday May 08th 2012 04:23 PM
Ask Tom MOST RECENT ARTICLES
<code>I have a database column that shall store data always 10 characters long. Which datatype should I use for it CHAR or VARCHAR2 and What is the specific reason for using that datatype ? </code>

advantages and disadvantages of indexes, cursor usage, what are x$ tables. Tuesday May 08th 2012 03:23 PM
Ask Tom MOST RECENT ARTICLES
<code>Tom I recently attended an interview.. one of the question was to tell the advantages and disadvantages of using indexes. my answer was that indexes are useful for faster access to rows in a table, where as they can be disadvantageous whe...

System View for package body internal functions and procedures Tuesday May 08th 2012 09:23 AM
Ask Tom MOST RECENT ARTICLES
Hello Tom, I have a package creating call templates and documentation templates for procedures, functions and package procedures and package functions. I use something like: <code> SELECT DISTINCT p.procedure_name, Argum...

Question about RAC ONE CLUSTER Tuesday May 08th 2012 03:22 AM
Ask Tom MOST RECENT ARTICLES
Hi Tom, I just saw your online presentation of 11g2 new features. Would you please elaborate on RAC ONE Cluster? Does this mean that you can dynamically add a node to a single instance database, the 'failover' to the new node, then, say, patch...

Update statement Tuesday May 08th 2012 03:22 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom 1. I have an Update statement which is taking too long It has a co-related subquery. How can I optimize it ? 2. This SQL also has a WHERE EXISTS clause, if I remove this will the columns that fail the join be set to null ? ...

PIVOT with dynamic number of columns and rows Sunday May 06th 2012 01:20 PM
Ask Tom MOST RECENT ARTICLES
Hello, I have a requirement that I am dealing with. I have 2 tables with parent-child relationship. The relationship is zero-to-many. More specifically, the parent table is called "ServiceRequest" and the child table is called "SR_FAQ". One r...

Can we have multiple versions of Oracle database instance in 11gR2 RAC Sunday May 06th 2012 01:20 PM
Ask Tom MOST RECENT ARTICLES
We would like to install 2 11.2 database and one 10.2 database in 11GR2 RAC. Is this possible? ? Can we run all 3 instances on a Single RAC 11.2 Cluster? Will this complicate backup/recovery? Thanks

printing data vertically like PRINT_TABLE via sqlplus Sunday May 06th 2012 01:20 PM
Ask Tom MOST RECENT ARTICLES
In some 10gr2 and 11gr2 Customer's databases I need, inside sqlplus , print some data vertically, much like your PRINT_TABLE function, but my user is "read-only" , I'm not allowed to create nothing (ie, no stored PL/SQLs, no tables, nothing perman...

utlrp Opposite Sunday May 06th 2012 12:20 PM
Ask Tom MOST RECENT ARTICLES
Hi Tom. We migrated few User Schema and consolidated all DB objects from those few schema users to a new schema users. We want to keep the older schema users and objects for reference, but want to prevent "accidential reference" to it. Do we h...

Export direct =y Sunday May 06th 2012 11:20 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi Tom When I say direct=y in the export the processing is much faster. What r the scanerious where i should not use direct=y Regards </code>

dba_free_space Sunday May 06th 2012 11:20 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi tom, I see dba_free_space_collesped,dba_free_space_collesped_temp1,temp2 In data dictionary ... what is this temp1,2 is all about ?? Thanks Regards Ak </code>

Listagg Vs Stragg Saturday May 05th 2012 03:23 PM
Ask Tom MOST RECENT ARTICLES
Tom: I was benchmarking stragg with listagg and results below, is that stragg is better than listagg? Please let me know your thoughts on this. <code>drop table t purge; create table t as select * from ( select * from all_objects ...

making an index invisible just for some clients Saturday May 05th 2012 11:22 AM
Ask Tom MOST RECENT ARTICLES
Hallo Tom, we stumbled on a probably optimizer bug where some queries don't use a bitmap index M and prefer instead using a range scan on another (btree) index B (on the same table as M), resulting in a execution time of some minutes instead one s...

V$session_wait Saturday May 05th 2012 11:22 AM
Ask Tom MOST RECENT ARTICLES
<code>Hi tom, Here is the query SQL> select segment_name,segment_type,owner,tablespace_name from 2 dba_extents,v$session_wait 3 where file_id=p1 4 and p2 between block_id and block_id + blocks -1; SEGMENT_NAME ...

Merging multiple tables with LOBs into one table Saturday May 05th 2012 09:22 AM
Ask Tom MOST RECENT ARTICLES
Hi Tom, We have an application that uses two tables in an 11.2 database to store document information. The CURRENT_INFO table shows the current version of a document; the ARCHIVE_INFO shows the history information. The two tables have identical de...

Hierarchical Query - Deleting a node from middle Saturday May 05th 2012 09:22 AM
Ask Tom MOST RECENT ARTICLES
Hello Tom I have a below requirement in hierarchical queries. If a node is removed from the middle of a hierarchy, all children of the removed node should automatically be assigned as children of the removed node?s parent.. How it can be don...

Need to read blob column and process it and update that content to 5 new columns in a large database Saturday May 05th 2012 08:22 AM
Ask Tom MOST RECENT ARTICLES
Hi Tom, I have table with large data, which has blob field contains JMSMessage Object. I need to read that object, split the object and process it, need to update processed content to 5 new columns. My clients are storing JMSMessage Object(in...

How to find the previous changed data Saturday May 05th 2012 08:22 AM
Ask Tom MOST RECENT ARTICLES
Hi Tom, Hope you are doing. Request your help on the following. Please help. <code>create table t (date_from date,date_to date,attr1 varchar2(1), attr2 varchar2(1)); Insert into t Values (TO_DATE('09/29/2010 06:00:13', 'MM/DD/YYYY HH24:MI:...

Set Date format in RMAN Saturday May 05th 2012 07:22 AM
Ask Tom MOST RECENT ARTICLES
I see Rman always shows date in DD-MON-YY format. Like the following if we list backupet or any rman command Control File Included: Ckp SCN: 473578586 Ckp time: 26-APR-12 How to set date format to MM/DD/YYYY HH24:MI:SS in rman ?

How To FULL DB EXPORT/IMPORT Saturday May 05th 2012 06:22 AM
Ask Tom MOST RECENT ARTICLES
Hello Tom, How to do a Full DB exp and Import. I do say exp system/manager@xyz FULL=Y FILE=FULL.DMP Then If I want to do a full Import to a new freshly created DB which only has the default schemas sys , system , etc. 1) Please...

General PL/SQL Record type Friday May 04th 2012 04:21 PM
Ask Tom MOST RECENT ARTICLES
Hi, i have number of tables (about 100), which have common columns. For example: table T1(C1 number, C2 number, T11 number, T12 number, ...) table T2(C1 number, C2 number, T21 number, T22 number, ...) table T3(C1 number, C2 number, T31 number, T32...

1 - 50

iStore

WIP Workbench

Web Telesales