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