FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / ORA - Oracle Database Error Codes - Search via specific error codes. / ORA-30000 Through ORA-40273

ORA-39060: table(s) dropped because of conflict with master table

AnirudhDas9 — Mon, 20 Jun 2011 21:25:16 GMT

Hi,

I am trying to import back the dump file into the schema and facing the below issue. We have cleared the schema before trying to import, the schema is completely empty i.e. select count(*) from user_ojects returns 0.
Database version is 10.2.0.3. The database is a RAC database. We have checked with the DBA as we do not have admin access that the dumpfile do exist on the server under DATAPUMP_DIR directory itself.

Any idea on the below error?

Command: impdp <schema>\/<password>@<dbname> dumpfile=ESMSTG_BCKUP_JUNE132011.dmp directory=DATAPUMP_DIR logfile=ESMSTG_BCKUP_JUNE132011_imp.log parallel=4

Import: Release 10.2.0.3.0 - Production on Monday, 20 June, 2011 17:06:55

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORA-39060: table(s) dropped because of conflict with master table
Master table "SWC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SWC"."SYS_IMPORT_FULL_01": SWC/********@ESMSTG1 dumpfile=ESMSTG_BCKUP_JUNE132011.dmp directory=DATAPUMP_DIR logfile=ESMSTG_BCKUP_JUNE132011_imp.log parallel=4
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6351

----- PL/SQL Call Stack -----
object line object
handle number name
0x84ce2478 15032 package body SYS.KUPW$WORKER
0x84ce2478 6372 package body SYS.KUPW$WORKER
0x84ce2478 3579 package body SYS.KUPW$WORKER
0x84ce2478 6972 package body SYS.KUPW$WORKER
0x84ce2478 1314 package body SYS.KUPW$WORKER
0xb06730d8 2 anonymous block

Job "SWC"."SYS_IMPORT_FULL_01" stopped due to fatal error at 20:07:03

Thanks in advance.

ora error ORA-39029: worker 2 with process name "DW02" prematurely terminated

Admin — Mon, 14 Jun 2010 11:54:29 GMT

ORA-39029: worker 2 with process name "DW02" prematurely terminated

I got the following error message when using impdp :

ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS01'
ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS01'
ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS01'
ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS01'
ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS01'
ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS01'
ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS01'
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 2 with process name "DW02" prematurely terminated
ORA-31672: Worker process DW02 died unexpectedly.
Job "SYS"."SYS_IMPORT_SCHEMA_03" stopped due to fatal error at 16:30:38
ORA-39014: One or more workers have prematurely exited.

This was pretty clear cut. My undo tablespace needed tuning.

I updated my undo_retention and set it to 900 :

alter system set undo_retention=900 scope=both;

I added another undo datafile and then kicked off the import again and it went through fine.

Ora Error ORA-39125 and ORA-04063 during export for transportable tablespace

Admin — Mon, 14 Jun 2010 11:11:15 GMT

ora error ORA-39125 and ORA-04063 during export for transportable tablespace

I'm using the Oracle Enterprise Manager (browser is IE) to create a tablespace transport file. Maintenance...Transport Tablespaces uses the wizard to walk me through each step. The job gets created and submitted.

The 'Prepare' and 'Convert Datafile(s)' job steps complete successfully. The Export step fails with the following error. Can anyone shed some light on this for me?

Thank you in advance!

=======================================================
Output Log

Export: Release 10.2.0.2.0 - Production on Sunday, 03 September, 2006 19:31:34

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Username:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."GENERATETTS000024": SYS/******** AS SYSDBA dumpfile=EXPDAT_GENERATETTS000024.DMP directory=EM_TTS_DIR_OBJECT transport_tablespaces=SIEBEL job_name=GENERATETTS000024 logfile=EXPDAT.LOG
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE_STATISTICS]
ORA-04063: view "SYS.KU$_IOTABLE_VIEW" has errors

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6241

PL/SQL Call Stack

object line object
handle number name
2CF48130 14916 package body SYS.KUPW$WORKER
2CF48130 6300 package body SYS.KUPW$WORKER
2CF48130 2340 package body SYS.KUPW$WORKER
2CF48130 6861 package body SYS.KUPW$WORKER
2CF48130 1262 package body SYS.KUPW$WORKER
2CF0850C 2 anonymous block

Job "SYS"."GENERATETTS000024" stopped due to fatal error at 19:31:44

ora error ORA-31694: master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" failed to load/unload

Admin — Mon, 14 Jun 2010 11:04:20 GMT

ORA-31694: master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" failed to load/unload

Iam trying to take a datapump back up of a schema 'DSS', and i get this error message..here are the details..I have about 25GB space left on that file system.

====================================
SQL> SELECT * FROM dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
----------------------------------------------------------
SYS DPUMP_DIR1 /tmp/dump_dir
SYS WORK_DIR /ade/aime_ship_10gR2_050630/oracle/work
SYS DATA_PUMP_DIR /u10/app/ORACLE/product/10.2.0.1/rdbms/log/
SYS ADMIN_DIR /ade/aime_ship_10gR2_050630/oracle/md/admin

$expdp system/password@dss DIRECTORY=DATA_PUMP_DIR dumpfile=dssdp.dmp schema=dss

Export: Release 10.2.0.1.0 - 64bit Production on Thursday, 19 April, 2007 11:02:34

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
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_SCHEMA_02": system/********@dss DIRECTORY=DATA_PUMP_DIR dumpfile=dssdp.dmp schemas=dss
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 9.202 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
....
....
ORA-31694: master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" failed to load/unload
ORA-31644: unable to position to block number 1 in dump file "/u10/app/ORACLE/product/10.2.0.1/rdbms/log/dssdp.dmp"
ORA-19502: write error on file "/u10/app/ORACLE/product/10.2.0.1/rdbms/log/dssdp.dmp", blockno 892675 (blocksize=4096)
ORA-27063: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 262144
ORA-19502: write error on file "/u10/app/ORACLE/product/10.2.0.1/rdbms/log/dssdp.dmp", blockno 892483 (blocksize=4096)
ORA-27063: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 262144

ora error ORA-39002 ORA-39070 ORA-39087 with Datapump

Admin — Mon, 14 Jun 2010 10:37:47 GMT

ORA-39002 ORA-39070 ORA-39087 with Datapump

If you are using the datapump (expdp or impdp etc.) and you get the following errors:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

There is no directory defined. You have to define it on the server.
Choose a user with the rights to create an directory, start sqlplus and enter following
command:

create directory data_pump_dir as '\exphd\datapump';

for windows...

create directory data_pump_dir as 'x:\exportdrive\datapump';

Be sure, that the directory exists on the os before you define it in the database!

ORA-31693, ORA-06502, ORA-31605, LPX-314 EXPDP FAILS ora error

Admin — Fri, 11 Jun 2010 15:59:09 GMT

Expdp fails with ORA-31693, ORA-06502, ORA-31605, LPX-314

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/AUDIT_OBJ
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "MAXIM"."HISTORY" failed to load/unload and is being skipped due to error:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLSetTextVar in routine kuxslSetParam:
LPX-314: an internal failure occurred
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/db1/oracle/dump/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 17:24:11

Cause of the Problem
In the data pump export operation ORA-31693, ORA-06502, ORA-31605, LPX-314 happened due to incorrect usage of single or double quotes for the QUERY parameter. So in the query parameter within the parameter files quote is given incorrect.
We see there is single quote(') surrounding double quote(") in the query parameter.

Solution of the Problem
Just omit the single quote (') from the query parameter in the expdp.

ORA-31202: DBMS_LDAP: LDAP client/server error: string

Admin — Tue, 08 Sep 2009 14:43:54 GMT

ORA-31202: DBMS_LDAP: LDAP client/server error: string

Cause: There is a problem either on the LDAP server or on the client.

Action: Please report this error to the LDAP server administrator or your Database administrator.

Please add more information about this Error if you have any.

The ORA-31202 error is a generic error returned by the DBMS_LDAP and DBMS_LDAP_UTL packages whenever they encounter an unexpected return code from the LDAP server being called. To be able to diagnose this error further it is necessary to determine the LDAP error that is being returned.

To do this trace the LDAP calls made and their responses.

If the server is Oracle Internet Directory (OID) then the simple solution is to enable OID debug and check the response codes.

For all LDAP servers network packet trace can be used as this will show the LDAP requests sent and the responses received.

ORA-31655: no data or metadata objects selected for job

Admin — Tue, 23 Jun 2009 12:09:11 GMT

Hi,

I am getting the following error message when using impdp and trying to import just the tables that were exported using expdp.

impdp parfile=test.par

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 23 June, 2009 11:27:16

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORA-31655: no data or metadata objects selected for job
ORA-39039: Schema expression "IN (SELECT object_name FROM "SYS"."SYS_IMPORT_SCHEMA_01" WHERE process_order = -56 AND duplicate BETWEEN 1 AND 1)" contains no valid schemas.
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": parfile=test.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 11:27:20

Here is my expdp parfile:

userid="/ as sysdba"
directory=special_export
dumpfile=expdp_NON_LOB_PROD_062309_%U.dmp
logfile=expdp_NON_LOB_PROD.log
parallel=10
tables=.......

The tables parameter had about 40 tables listed as example:

ecom.customer, ecom.account, ecom.address etc......

My impdp parameter file was :

userid="/ as sysdba"
directory=special_export
schemas=ecom
dumpfile=expdp_NON_LOB_PROD_062309_%U.dmp
logfile=impdp_NON_LOB_PROD.log
parallel=10
remap_schema=ecom:ecom
TABLE_EXISTS_ACTION=REPLACE
CONTENT=ALL

When i used this impdp parameter file, i kept getting that error :

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 23 June, 2009 11:27:16

This was very frustrating and all the help online couldn't fix it . .. because all i wanted to do was import just the tables... and all of them not specific ones. So i tried a few things manually and here is the fix :

In the impdp parfile add this to the bottom:

INCLUDE=TABLE

This will include all the tables in the exported dump files and the error message went away. Basically what you have to do is to make sure you put in any of the following: INCLUDE, EXCLUDE, CONTENT etc.. . otherwise it'll throw that error message.

Hope this helps.

Thanks,

ORA-04021: timeout occurred while waiting to lock object

Admin — Tue, 09 Jun 2009 02:06:32 GMT

Hi,

I was getting the following error while compiling a procedure:

alter procedure ECOM1.MAINRUN compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object ECOM1.MAINRUN

I checked the following two queries to see if there were any blockers etc.. and the query came back with no rows and still I was not sure where to go from here.

SQL> select * from dba_blockers;

no rows selected

SQL> select * from dba_waiters;

no rows selected

So then I ran the compile statement again and then ran this query:

SQL> select p.spid "Thread", s.sid "SID-Top Sessions",
substr(s.osuser,1,15) "OS User", substr(s.program,1,25) "Program Running"
from v$process p, v$session s
where p.addr=s.paddr
order by substr(s.osuser,1,15);

Using this query, I found a couple of processes that were running. I killed them using the OS process id at OS prompt:

kill -9 <thread from above>

That fixed it .

SQL> alter procedure ECOM1.MAINRUN compile;

Procedure altered.

Thanks,

ORA-39087: directory name DATA_PUMP_DIR is invalid

Admin — Tue, 28 Apr 2009 15:58:55 GMT

Hi,

I got the following error message when doing an impdp.

prod005:(ECOM01)/oracle/db03/ECOM01/EXPORTS > impdp parfile=imp_ecom.par

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 28 April, 2009 17:00:44

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

Problem is either 1 of two things:

1. The datapump directory doesn't exist

2. The user trying to import doesn't have read/write privilege to access the directory.

In my case this was my parfile:

userid=ecom/ecompassword
DIRECTORY=DATA_PUMP_DIR
dumpfile=ecom_from_QA_042809.dmp
logfile=imp_ecom_from_QA_042809.log
TABLE_EXISTS_ACTION=APPEND

I issued the following statment and then it went through successfully:

SQL> grant read,write on directory DATA_PUMP_DIR to ecom;

Also, just make sure that the DATA_PUMP_DIR exists.

Thanks,