Search This Blog

Tuesday, September 6, 2011

expdp fail and create table SYS_EXPORT_SCHEMA_nn

expdp fail and create table SYS_EXPORT_SCHEMA_10

What is SYS_EXPORT_SCHEMA_NN table?
This is introduced with DATA PUMP EXPORT feature in 10g. if you use datapump expdp utility to export database/schema/table then this table will create and once the job successfully finished then it will drop automatically. 
the purpose of this table to store information about export operation.

How and When SYS_EXPORT_SCHEMA_NN table created?
This table will create while exporting schema /database/table and export operation failed with errors.

How to clear/delete this table and will it effect if i delete?
We can delete this table anytime after export operation failed. this table is just a information log export operation. and it will not effect any other database activities.
SQL> drop table SYS.SYS_EXPORT_SCHEMA_nn purge;

NOTE: DBA role assigned user can delete the SYS_EXPORT_SCHEMA_nn table.

What is NN in the SYS_EXPORT_SCHEMA_nn ?
nn represent number of failed operation. mean first time the export operation will failed then it will store in the database with SYS_EXPORT_SCHEMA_01. 

LIVE EXAMPLE:
Export the schema using expdp
C:\>expdp directory=data schemas=SCOTT

Export: Release 11.2.0.2.0 - Production on Tue Sep 6 14:16:31 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: scott/tiger@db01

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@db01 directory=data sch
emas=SCOTT
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
[output cut]

you can check the above SYS_EXPORT_SCHEMA_01 table is created in the below view
SQL> SET lines 200
SQL> COL owner_name FORMAT a10;
SQL> COL job_name FORMAT a20
SQL> COL state FORMAT a12
SQL> COL operation LIKE state
SQL> COL job_mode LIKE state
SQL> SELECT owner_name, job_name, operation, job_mode,
  2  state, attached_sessions
  3  FROM dba_datapump_jobs
  4  WHERE job_name NOT LIKE 'BIN$%'
  5  ORDER BY 1,2;

OWNER_NAME JOB_NAME             OPERATION    JOB_MODE     STATE        ATTACHED_
SESSIONS
---------- -------------------- ------------ ------------ ------------ ---------
--------
SCOTT      SYS_EXPORT_SCHEMA_01 EXPORT       SCHEMA       EXECUTING
       1

once the export operation completed then above created table will automatically delete.
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\EXPDAT.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:19:15

SQL> --export operation completed now re-run the above query to check table
SQL> --exist or deleted
SQL> SELECT owner_name, job_name, operation, job_mode,
  2  state, attached_sessions
  3  FROM dba_datapump_jobs
  4  WHERE job_name NOT LIKE 'BIN$%'
  5  ORDER BY 1,2;

no rows selected

C:\>expdp directory=data schemas=SCOTT

Export: Release 11.2.0.2.0 - Production on Tue Sep 6 14:21:48 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: scott/tiger@db01

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@db01 directory=data sch
emas=SCOTT

Export> exit

SQL> SELECT owner_name, job_name, operation, job_mode,
  2  state, attached_sessions
  3  FROM dba_datapump_jobs
  4  WHERE job_name NOT LIKE 'BIN$%'
  5  ORDER BY 1,2;

OWNER_NAME JOB_NAME             OPERATION    JOB_MODE     STATE        ATTACHED_
SESSIONS
---------- -------------------- ------------ ------------ ------------ ---------
--------
SCOTT      SYS_EXPORT_SCHEMA_01 EXPORT       SCHEMA       NOT RUNNING
       0

---if you run export operation again then this time it will use SYS_EXPORT_SCHEMA_02 because SYS_EXPORT_SCHEMA_01 is already exist but once you delete the SYS_EXPORT_SCHEMA_01.

hope this help ...

write me email in case of any query at askdbataj

2 comments:

Anonymous said...

Good work taj...


John
USA

Anonymous said...

raely good explanation