Ask Anantha

What is Oracle Real Application Clusters or RAC? Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide highly scalable and available database solutions for business applications. http://www.oracle.com/technology/products/database/clustering/index.html Evolution of RAC? The evolution of RAC can be tracked back from Oracle 7, where it was called Oracle Parallel Server (OPS). But it was in Oracle 9i that RAC was introduced. It was not merely a name change from Oracle 7 to Oracle 9i. Oracle has changed almost everything. RAC supports even direct sharing of Oracle database blocks between two or more database instances. This sharing is the major change that can be tracked from OPS. In OPS blocks where not directly shared leaving one instance notifies another instance of a required database block and wait till the other instance hand overs the same after it has been written its conte...

If you are unable to compile your reports or plls in Oracle 10g Version, due to the error like PL SQL ERROR 201: SRW.___ must be declared, then check the existence of the following file and its privilege: rwrun.jar in /Oracle10gAS/reports/jlib folder. Where Oracle10gAS is the folder where 10g Application server is installed. You will normally get this error only in UNIX/Linux flavors, because this is related to privilege rights. If you are getting this error in Windows also, then it is quietly possible that the file is missing. The file must have the privilege 755 or -rwx-r-x-r-x.

Use this query to get the record count of all tables in a schema. select table_name, to_number( extractvalue( xmltype( dbms_xmlgen.getxml('select count(*) c from '||table_name) ),'/ROWSET/ROW/C')) count from user_tables order by 1 The output is like table_name count ----------- -------- DEPT 4 EMP 14 Courtesy: http://laurentschneider.com/wordpress/2007/04/ how-do-i-store-the-counts-of-all-tables.html

To escape certain characters such as _ which serve as wild card characters in a SQL Query, there is an extension to the LIKE clause ESCAPE. The syntax is: LIKE string ESCAPE character Why use escape We will take on an example to make clear its usage. We have a table namely TAB_100 which has only column FIELD_100, which is VARCHAR2(20). The example data in the table is as below. Code: FIELD_100 ------------ sample_data sample data not applicable not_applicable Now if we want to select the rows which have the underscores. What command will you normally issue. It would be naturally the first time like below: Code: select FIELD_100 from TABLE_100 where FIELD_100 like '%_%'; But you will be surprised to see the results: Code: FIELD_100 ------------ sample_data sample data not applicable not_applicable Why did this happen? For experienced Oracle campaigners it is well known that _ is a wild card character. T...

Use this query to convert any length string in comma separated format to rows: SELECT SUBSTR('AA,BBB,C,D,E,F,G,H', INSTR(CHR(44)||'AA,BBB,C,D,E,F,G,H'|| CHR(44),',',1,LEVEL), INSTR('AA,BBB,C,D,E,F,G,H'||CHR(44),',',1,LEVEL)- INSTR(CHR(44)||'AA,BBB,C,D,E,F,G,H'|| CHR(44),',',1,LEVEL)) FROM DUAL CONNECT BY LEVEL LENGTH(REPLACE('AA,BBB,C,D,E,F,G,H',','))+1

Say for example we have a string 'A,B,C,D,E,F'. We would like to have it printed in separate lines say A B C D E F Like so. Now we can use a simple SQL statement to convert the same. SELECT SUBSTR('A,B,C,D,E,F', INSTR('A,B,C,D,E,F',',',1, LEVEL)-1,1) FROM DUAL CONNECT BY LEVEL REPLACE('A,B,C,D,E,F',',')) To elaborate the technique, we have used the following logic: 1. Used CONNECT BY statement to find out how many rows are necessary 2. INSTR to find out the place of each row 3. SUBSTR to cut the string between the comma Well what was the assumptions before using this query. The string between comma are having length of one. Any number of strings can be given in this fashion.