World of Oracle DBA

Problem Description E:\>impdp directory=test dumpfile=testexp_07_03_09.dmp userid=shaik/a Import: Release 10.2.0.1.0 - Production on Thursday, 07 May, 2009 10:07:00 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 ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39143: dump file "E:\oracle\Test\testexp_07_03_09.dmp" may be an original export dump file Cause of the problem The above problem happened whenever you try to use the Import Data Pump client (impdp) to import a dumpfile that was created with the original Export client (exp). Though not related but similar error occured. Like whenever you try to import from an empty file, E:\>impdp directory=test dumpfile=testexp_07_03_09.dmp userid=shaik/a Import: Release 10.2.0.1.0 - Production on Thursday, 07 May, 2009 10:16:52 Copyright (c) 2003, 2005, Orac...

Problem Description Connecting to local 10.2.01 database whenever I want to take data pump export into remote 11g database machine using NETWORK_LINK parameter data pump export fails with ORA-39001, ORA-39169 as below. [oracle@localhost bin]$ ./expdp system/a NETWORK_LINK=maestro.net schemas=maximsg VERSION=10.2 Export: Release 10.2.0.1.0 - Production on Wednesday, 04 March, 2009 5:27:39 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 ORA-39001: invalid argument value ORA-39169: Local version of 10.2.0.1.0 cannot work with remote version of 11.1.0.6.0. Similarly, connecting to local 10.1.0.* database whenever I want to take data pump export into remote 11g database machine using NETWORK_LINK parameter data pump export fails with ORA-39006: internal error ORA-39022: Database version 11.1.0.6.0 is not supported. Doing expdp/impdp connected to a ...

Problem Description In oracle 10.2.0.1 while creating database with dbca it fails with message below. ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], [] If you try to create your database manually then also the command startup nomount fails with above error message. Cause of the Problem In the error ldmInit indicates that the problem is related while getting host information of oracle during startup. The first argument 46 indicates the exception LDMERR_HOST_NOT_FOUND which is "gethostbyname system call fails". Oracle was unable to get host information from OS and bug fires. Solution of the Problem Step 01: Check permission on /etc/hosts $ ls -l /etc/hosts -rw-r--r-- 1 root root 153 Nov 24 2007 /etc/hosts Note that you need read permission of all users. Step 02: Check the contents of /etc/hosts Open the contents of /etc/hosts and check the contents inside it. $ less /etc/hosts Note that the contents of this files follow following form...

Problem Description You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp. In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc) Your parameter file is like below. userid=smilebd/a directory=d dumpfile=b.dmp include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" include =TABLE:"LIKE 'CV%' " And you invoke expdp as expdp parfile=d:\parfile.txt from command line. But it fails with below message on my windows PC. C:\>expdp parfile=d:\parfile.txt Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc tion With the Partitioning, OLAP and Data Mining ...

Problem Description In my machine I had oracle 10g home , using sqlplus of 10g I could connect to an Oracle database 10g. Now I have installed a new oracle 11g home, but using sqlplus of 11g I could not connect to Oracle database 10g. Below is an example, With 10.2g sqlplus I can connect to 10g database. C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe shaik/a@192.168.100.160/tiger SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009 Copyright (c) 1982, 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 With 11g sqlplus I could not connect to oracle database 10g. It returns error message, ORA-12557: TNS:protocol adapter not loadable. C:\>d:\app\oracle\BIN\sqlplus.exe shaik/a@192.168.100.160/tiger SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 01:55:00 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. ERROR: ORA-12557: ...

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec shaik.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subseq...

Problem Description You are trying to create or drop a database link to the same database name. This may be true if you have a database that was cloned from another database on a different machine and now you try to create or drop a database link with the name of the original database. Below is an example. SQL> create database link tiger; create database link tiger * ERROR at line 1: ORA-02082: a loopback database link must have a connection qualifier SQL> drop database link tiger; drop database link tiger * ERROR at line 1: ORA-02082: a loopback database link must have a connection qualifier Cause of the Problem This is an expected behavior if database global name match with the database link creation name. Now let's see the global_name of the database. SQL> select * from global_name; GLOBAL_NAME --------------------------------------- TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM We see the global name of the database is started with tig...

Problem Description While creating a startup database using dbca the database creation GUI gives error message in a pop up window, ORA-00845: MEMORY_TARGET not supported on this system from where you can ignore the error message. The similar scenario also occur whenever you try to start your database then startup shows error message like below. SQL> STARTUP ORA-00845: MEMORY_TARGET not supported on this system Cause of the Problem •Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMMORY_MAX_TARGET. •On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system. •And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET. •The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process. •The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g. •And MEMORY_MAX_TARGET parameter in...

Problem Description You are trying to create or drop a database link to the same database name. This may be true if you have a database that was cloned from another database on a different machine and now you try to create or drop a database link with the name of the original database. Below is an example. SQL> create database link tiger; create database link tiger * ERROR at line 1: ORA-02082: a loopback database link must have a connection qualifier SQL> drop database link tiger; drop database link tiger * ERROR at line 1: ORA-02082: a loopback database link must have a connection qualifier Cause of the Problem This is an expected behavior if database global name match with the database link creation name. Now let's see the global_name of the database. SQL> select * from global_name; GLOBAL_NAME --------------------------------------- TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM We see the global name of the database is started with...

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, maestro is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec maestro.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But su...

Problem Description $expdp parfile=pfile_maxim_history_sel.par directory=d Export: Release 10.2.0.1.0 - 64bit Production on Monday, 05 January, 2009 17:23:52 Copyright (c) 2003, 2005, Oracle. All rights reserved. Username: system Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** parfile=pfile_maxim_history_sel.par directory=d Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 964 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPO...

Problem Description I wanted to update a table column based on another table data but it fails with error ORA-01779: cannot modify a column which maps to a non key-preserved table. SQL> update (select t1.code col1, t2.code col2 from table_1 t1 JOIN table_2 t2 ON t1.id=t2.id) set col1=col2; set col1=col2 * ERROR at line 4: ORA-01779: cannot modify a column which maps to a non key-preserved table Cause, Description and Solution of the Problem Let's look both of table's data. 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 From the above update query, the select part return following rows, SQL> select t1.code col1, t2.code col2 from table_1 t1 JOIN table_2 t2 ON t1.id=t2.id; COL1 COL2 -------------------- -------------------- Rows to...

Problem Description SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 4 from STATION_RATE_DUMMY; 7561 rows created. SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 5 from STATION_RATE_DUMMY; INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT * ERROR at line 1: ORA-12838: cannot read/modify an object after modifying it in parallel Cause of the Problem A table is modified in parallel or with direct path load in a transaction. Now within the same transaction if an attempt was made to read or modification statements on a table then ORA-12838 will occur. In oracle within same transaction table is modified with direct path load or parallel and then access of it is not permitted. Solution of the Problem Break up the t...

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...

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('...

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('...

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...