World of Oracle DBA

Problem Description I like to differentiate between columns of table HISTORY from two databases using database link as below and it resulted error ORA-02070. SQL> select * from cols where table_name='HISTORY' minus select * from cols@tiger1.net where table_name='HISTORY'; select * from cols where table_name='HISTORY' minus select * from cols@tiger1.net where table_name='HISTORY' * ERROR at line 1: ORA-02070: database TIGER1.NET does not support operator NLS_CHARSET_DECL_LEN in this context I found that the cols contain LONG column and hence result above error. If I only query long datatype data_default then same error return with different error message. SQL> select data_default from cols minus select data_default from cols@tiger1.net; select data_default from cols minus select data_default from cols@tiger1.net

Problem Description I ran my datapump export operation with the combination of multiple dumpfile arguments and filesize parameter but it fails with ORA-39095 as below. maximsg@TIGER> $expdp maximsg/a dumpfile=d:parti1.dmp,e:parti2.dmp,c:resit%U.dmp filesize=188417 tablespaces=users Export: Release 10.2.0.1.0 - Production on Thursday, 01 January, 2009 12:23:30 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Starting "MAXIMSG"."SYS_EXPORT_TABLESPACE_05": maximsg/******** dumpfile=d:parti1.dmp,e:parti2.dmp,c:resit%U.dmp filesize=188417 tablespace s=users Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 20.12 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE

Problem Description In my production database Oracle 10.2g while I was adding column to one of my transaction table it fails with ORA-54 error as below. SQL> alter table student add b number; alter table student add b number * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified Description of the Problem Let's try to produce the problem in our development environment. I have opened two session that connected to database under a schema. In one session, I have created a table and inserted data into it. SQL> create table a (a number); Table created. SQL> insert into a values(1); 1 row created. I did not yet committed data in session 1. Now in another session whenever I try to any ddl like (alter table, drop table) ORA-00054 will produce. In another session, SQL> alter table a add b number; alter table a add b number * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified SQL> drop table a; drop table a * ERROR at l

In many times we need to update column(s) of a table based on the data of another table column(s). There are several ways to do the task. Let's show example by creating table and entering values into it. Create table table_1(id number, code varchar2(20)); insert into table_1 values(1,'First Row'); insert into table_1 values(2, 'Rows to be updated'); Create table table_2(id number, code varchar2(20)); insert into table_2 values(2,'Second Row'); After above statements let's look at the data on the table. SQL> select * from table_1; ID CODE ---------- -------------------- 1 First Row 2 Rows to be updated SQL> select * from table_2; ID CODE ---------- -------------------- 2 Second Row Now my requirement is to update table_1 based on table_2 id column data. If corresponding id in table_1 exist then that row's code will be updated. Method 01: SQL> update table_1 set code= (select t2.code from table_2 t2 JO

In many times we need to update column(s) of a table based on the data of another table column(s). There are several ways to do the task. Let's show example by creating table and entering values into it. Create table table_1(id number, code varchar2(20)); insert into table_1 values(1,'First Row'); insert into table_1 values(2, 'Rows to be updated'); Create table table_2(id number, code varchar2(20)); insert into table_2 values(2,'Second Row'); After above statements let's look at the data on the table. SQL> select * from table_1; ID CODE ---------- -------------------- 1 First Row 2 Rows to be updated SQL> select * from table_2; ID CODE ---------- -------------------- 2 Second Row Now my requirement is to update table_1 based on table_2 id column data. If corresponding id in table_1 exist then that row's code will be updated. Method 01: SQL> update table_1 set code= (select t2.code from table_2 t2 JO

Computing Summary of a column With combination of BREAK and COMPUTE command you can do several calculations on SQL reports using sql*plus. Remember the COMPUTE command has no effect without a corresponding BREAK command. Below is the lists of compute functions of Sql*plus and their effects. 1)SUM: Sum of the values in the column. 2)MINIMUM: Minimum value in the column. 3)MAXIMUM: Maximum value in the column. 4)AVG: Average of the values in the column. 5)STD: Standard deviation of the values in the column. 6)VARIANCE: Variance of the values in the column. 7)COUNT: Number of non-null values in the column. 8)NUMBER: Number of rows in the column. Let's look at our data, SQL> select dept_id,name,salary from emp; DEPT_ID NAME SALARY ---------- ------------ ---------- 10 SHAIK 60000 10 ABDUL 22000 10 KHALEEL 40000 20 IBRAHIM 35000 20 RAVI 60000 30 KASARAPU 250

Computing Summary of a column With combination of BREAK and COMPUTE command you can do several calculations on SQL reports using sql*plus. Remember the COMPUTE command has no effect without a corresponding BREAK command. Below is the lists of compute functions of Sql*plus and their effects. 1)SUM: Sum of the values in the column. 2)MINIMUM: Minimum value in the column. 3)MAXIMUM: Maximum value in the column. 4)AVG: Average of the values in the column. 5)STD: Standard deviation of the values in the column. 6)VARIANCE: Variance of the values in the column. 7)COUNT: Number of non-null values in the column. 8)NUMBER: Number of rows in the column. Let's look at our data, SQL> select dept_id,name,salary from emp; DEPT_ID NAME SALARY ---------- ------------ ---------- 10 SHAIK 60000 10 ABDUL 22000 10 KHALEEL 40000 20 IBRAHIM 35000 20 RAVI 60000 30 KASARAPU 250

In sql*plus with AUTOTRACE command you can see the execution plan of successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements which is useful for monitoring and tuning the performance of these statements. You can control the report by setting the AUTOTRACE system variable. Following is the available AUTOTRACE settings. 1)SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default. Result of a query will be displayed on the console as usual. 2)SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows the optimizer execution path along with query output and note but does not show statistics. 3)SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows the SQL statement execution statistics along with query output but does not display execution plan. 4)SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics along with query output. 5)SET AUTOTRACE TRACEONLY: Report displays both optimizer execu

Below is the output of an execution plan statistics. SQL> set autot trace statistics SQL> select * from tab; 107 rows selected. Statistics ---------------------------------------------------------- 8 recursive calls 0 db block gets 898 consistent gets 0 physical reads 0 redo size 3459 bytes sent via SQL*Net to client 458 bytes received via SQL*Net from client 9 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 107 rows processed You sometimes want to know what these fields indicates. Below is the details of these fields. 1)recursive calls: For processing of sql statements oracle database maintain internal tables. Sometimes for processing sql statements change is needed in the internal tables and sometimes not. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call. 2)

In sql*plus with AUTOTRACE command you can see the execution plan of successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements which is useful for monitoring and tuning the performance of these statements. You can control the report by setting the AUTOTRACE system variable. Following is the available AUTOTRACE settings. 1)SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default. Result of a query will be displayed on the console as usual. 2)SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows the optimizer execution path along with query output and note but does not show statistics. 3)SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows the SQL statement execution statistics along with query output but does not display execution plan. 4)SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics along with query output. 5)SET AUTOTRACE TRACEONLY: Report displays both optimizer execu

Problem Description I was performing data pump export operation and it fails with ORA-39125, ORA-04031 as below. SQL> $expdp full=y dumpfile=fulldb.dmp directory=d userid=system/a Export: Release 10.2.0.1.0 - Production on Tuesday, 16 December, 2008 17:07:44 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_FULL_02": full=y dumpfile=fulldb.dmp directory=d userid=system/******** Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB [] ORA-04031: unable to allocate 28 bytes of shared memory ("shared pool","SELECT /*+rule*/ SYS_XMLGEN(...",&

SQL> delete from task; delete from task * ERROR at line 1: ORA-02292: integrity constraint (OMS.TASKUSER_TASK) violated - child record found SQL> drop table task; drop table task * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL> truncate table task; truncate table task * ERROR at line 1: ORA-02266: unique/primary keys in table referenced by enabled foreign keys To get list of child table with the constraint name that is referencing the TASK table issue, SQL> set lines 140 SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table", p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint" FROM user_constraints p JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name) WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U') AND c.constraint_type = 'R' AND p.table_name = UPPER('&a

SQL> delete from task; delete from task * ERROR at line 1: ORA-02292: integrity constraint (OMS.TASKUSER_TASK) violated - child record found SQL> drop table task; drop table task * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL> truncate table task; truncate table task * ERROR at line 1: ORA-02266: unique/primary keys in table referenced by enabled foreign keys To get list of child table with the constraint name that is referencing the TASK table issue, SQL> set lines 140 SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table", p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint" FROM user_constraints p JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name) WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U') AND c.constraint_type = 'R' AND p.table_name = UPPER('&a

Problem Description Whenever you try to disable a constraint of a table it fails with error message ORA-02297: cannot disable constraint -dependencies exist as below. SQL> alter table transaction disable constraint TRANSACTION_PK; alter table transaction disable constraint TRANSACTION_PK * ERROR at line 1: ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) - dependencies exist Cause of the Problem Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint. Solution of the Problem Two solutions exist for this problem. 1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint. Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint. SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table", 2 p.constraint_name "Parent Constrai

Problem Description Whenever you try to disable a constraint of a table it fails with error message ORA-02297: cannot disable constraint -dependencies exist as below. SQL> alter table transaction disable constraint TRANSACTION_PK; alter table transaction disable constraint TRANSACTION_PK * ERROR at line 1: ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) - dependencies exist Cause of the Problem Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint. Solution of the Problem Two solutions exist for this problem. 1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint. Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint. SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table", 2 p.constraint_name "Parent Constrai

Bind variables are variables that are declared inside PL/SQL or a variable in a SQL statement that must be replaced with a valid value. In the SQL*Plus you can also create bind variable. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus. Creating Bind Variables In order to create bind variable in Sql*plus you have to use variable keyword. To create a bind variable named bind_var as number datatype use, variable bind_var NUMBER Displaying Bind Variables To display all bind variables in the session just use variable keyword. SQL> variable variable bind_var datatype NUMBER In order to display the value of a particular bind variable use, SQL> print bind_var BIND_VAR ---------- Using Bind Variables To use bind variable first use colon(:) and then give variable name. In order to change values of bind variable you must enter in a PL/SQL block. To change bind varia

Sometimes you may find difficulties while displaying result on SQL*Plus like a column length displayed as a long length and thus does not result a fine output. In this post I will try to show how we can make good looking result displayed on SQL*Plus. With COLUMN a column can be formatted. For string type data format is specified by A and then length. Like A7 means total column will span to 7 word length. For number data type the format is 99999 where number of 9s decide the length of digits of number data type. Changing Column Headings To change the heading of column a,b and c use commands like below. The vertical bar is used if you want to display the columns in a new line. SQL> COLUMN A HEADING 'FIRST|COLUMN' SQL> COLUMN C HEADING 'THIRD|COLUMN' SQL> COLUMN B HEADING 'SECOND|COLUMN' FORMAT A7 SQL> select a,b,c from test1; FIRST SECOND THIRD COLUMN COLUMN COLUMN ---------- ------- ------ 1 Hi Hi2 Setting the Underline Character

Bind variables are variables that are declared inside PL/SQL or a variable in a SQL statement that must be replaced with a valid value. In the SQL*Plus you can also create bind variable. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus. Creating Bind Variables In order to create bind variable in Sql*plus you have to use variable keyword. To create a bind variable named bind_var as number datatype use, variable bind_var NUMBER Displaying Bind Variables To display all bind variables in the session just use variable keyword. SQL> variable variable bind_var datatype NUMBER In order to display the value of a particular bind variable use, SQL> print bind_var BIND_VAR ---------- Using Bind Variables To use bind variable first use colon(:) and then give variable name. In order to change values of bind variable you must enter in a PL/SQL block. To change bind varia

Problem Description I wanted to do online redefinition on a table. Whenever I run start_redef_table procedure then, while creating materialized view it failed with ORA-03114 and ORA-03113. Below is the scenario. SQL> exec dbms_redefinition.start_redef_table('M', 'HISTORY', 'HISTORY_INT'); ERROR: ORA-03114: not connected to ORACLE BEGIN dbms_redefinition.start_redef_table('M', 'HISTORY', 'HISTORY_INT'); END; * ERROR at line 1: ORA-03113: end-of-file on communication channel In the Alert log file it displays, Sat Dec 6 16:34:40 2008 Errors in file /var/opt/dumpfile/udump/shaikdb_ora_12860.trc: ORA-07445: exception encountered: core dump [kghalp()+58] [SIGSEGV] [Address not mapped to object] [0x000000068] [] [] ORA-10980: prevent sharing of parsed query during Materialized View query generation Sat Dec 6 16:36:39 2008 Cause of the Problem This is an oracle bug. When auditing is enabled and creating materialized view or executing start_r

Problem Description I used both remap_schema(import operation will be performed in another user than the user whose data to be imported) and remap_tablespace(tablespace to be changed while importing) and the import operation failed with ORA-39005 and ORA-31600. $host impdp directory=d dumpfile=user1_metadata04-12-08.dmp remap_schema=user1:user2 table_exists_action=skip logfile=user1_metadata04-12-08.log \ remap_tablespace=user1_SPC:user2_SPC \ remap_tablespace=user1_HISTORY_DETAIL_SPC: user2_HISTORY_DETAIL_SPC \ remap_tablespace=user1_HISTORY_SPC:user2_HISTORY_SPC \ remap_tablespace=user1_ACC_SPC:user2_SPC \ remap_tablespace=user1_HISTORY_DETAIL_INDX_SPC:user2_HIS_DETAIL_INDX_SPC \ remap_tablespace=user1_HISTORY_INDX_SPC:user2_HISTORY_INDX_SPC Import: Release 10.2.0.1.0 - 64bit Production on Thursday, 04 December, 2008 19:17:08 Copyright (c) 2003, 2005, Oracle. All rights reserved. Username: system Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64b

Problem Description I wanted to do online redefinition on a table. Whenever I run start_redef_table procedure then, while creating materialized view it failed with ORA-03114 and ORA-03113. Below is the scenario. SQL> exec dbms_redefinition.start_redef_table('M', 'HISTORY', 'HISTORY_INT'); ERROR: ORA-03114: not connected to ORACLE BEGIN dbms_redefinition.start_redef_table('M', 'HISTORY', 'HISTORY_INT'); END; * ERROR at line 1: ORA-03113: end-of-file on communication channel In the Alert log file it displays, Sat Dec 6 16:34:40 2008 Errors in file /var/opt/dumpfile/udump/shaikdb_ora_12860.trc: ORA-07445: exception encountered: core dump [kghalp()+58] [SIGSEGV] [Address not mapped to object] [0x000000068] [] [] ORA-10980: prevent sharing of parsed query during Materialized View query generation Sat Dec 6 16:36:39 2008 Cause of the Problem This is an oracle bug. When auditing is enabled and creating materialized view or executing start_r

The Sql Buffer The Sql buffer stores most recently SQL command or most recently PL/SQL block. SQL*Plus does not store SQL*Plus commands, or the semicolon or slash characters you type to execute a command in the SQL buffer. You can execute the command or block in the SQL buffer using the RUN or /(slash) commands. RUN commands displays the command or block in the buffer and then execute it. /(slash) executes the command or block in the buffer but does not display it. Below is the example. A simple SQL command. SQL> select sysdate from dual; SYSDATE --------- 29-NOV-08 This one (Setting linesize) is SQL*plus command and hence is not stored in SQL buffer. SQL> set linesize 100 Invoking list will show SQL buffer, not SQL*plus command. SQL> list 1* select sysdate from dual Run will execute the command after displaying it. SQL> run 1* select sysdate from dual SYSDATE --------- 29-NOV-08 /(Slash) will not display the command. It will only execute it. SQL> / SYSDATE --------- 2

Running Sql Scripts To run a sql scripts in Sql*plus use @ or @@ or start command. To run a script named C:\test.sql use, @c:\test or, @@c:\test or, start c:\test In addition, CLEAR BUFFER (CL BUFF): Will clear buffer, i.e delete all lines from the Sql buffer. To insert a line before line 1, enter a zero ("0") and follow the zero with text. 0 SELECT Username Placing comments in Sql Scripts 1)Using Sql*plus REMARK command: If a line starts with REMARK rest words of the line are considered as comment and hence ignored to compile. This is applied to single line only. Example: REMARK This script will generate the salary history REM This script is generated on 11/01/2008 2)Using /*...*/: In order to comment multiple lines start with /* and until it gets */ entire commands inside it are considered as comments. With /*...*/ you can also comment a single line. You must enter a space after the slash-asterisk(/*). Example: /* Column Salary for 9999 Column Name for a30 */ 3)Using --: