DataPump Expdp ABORT_STEP Option

advertisements

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
ABORT_STEP is the option with the DataPump export utility (expdp) to stop the job after the initialization. This is available from 12.1.0.2. 
Purpose of this option is to abort the job after the initialization. The master table would be created by this time. The master table name would be the job name of the export job. You can query the master table after aborting the job. Generally this master table get deleted or dropped after the successful export. 

Values: ABORT_STEP=[n | -1] 
n: If the value is zero or greater, then the export operation is started and the job is aborted at the object that is stored in the master table with the corresponding process order number. 

-1: If the value is negative one (-1), then abort the job after setting it up, but before exporting any objects or data. 

Example 1: with value -1 

With this -1 value the export job abort just after the initiation.

[oracle@TESTBOX dbhome_1]$ expdp directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=dev ABORT_STEP=-1 job_name=abort

Export: Release 19.0.0.0.0 - Production on Fri Sep 20 11:53:40 2019
Version 19.3.0.0.0

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

Username: / as sysdba
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Job "SYS"."ABORT" stopped by user request at Fri Sep 20 11:53:50 2019 elapsed 0 00:00:02


Now the jobname ABORT is not running.
select * from dba_datapump_jobs
SQL> /

OWNER_NAME JOB_NAME   OPERATION       JOB_MODE       STATE                DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ---------- --------------- --------------- -------------------- ---------- ----------------- -----------------
SYS       ABORT      EXPORT             SCHEMA       NOT RUNNING                 0              0             0

You can attach this job and resume the export.
oracle@TESTBOX dbhome_1]$ expdp attach=abort

Export: Release 19.0.0.0.0 - Production on Fri Sep 20 11:57:50 2019
Version 19.3.0.0.0

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

Username: / as sysdba
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Job: ABORT
  Owner: SYS                           
  Operation: EXPORT                        
  Creator Privs: TRUE                          
  GUID: 92FF1345EB0E4706E053017AA8C0906F
  Start Time: Friday, 20 September, 2019 11:57:57
  Mode: SCHEMA                        
  Instance: cdb
  Max Parallelism: 1
  Timezone: +00:00
  Timezone version: 32
  Endianness: LITTLE
  NLS character set: AL32UTF8
  NLS NCHAR character set: AL16UTF16
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        /******** AS SYSDBA directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=dev ABORT_STEP=-1 job_name=abort
     TRACE                 0
  State: IDLING                        
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Job heartbeat: 0
  Dump File: /u02/datapump/exp.dmp
    bytes written: 4,096

Export> continue_client
Job ABORT has been reopened at Fri Sep 20 11:57:57 2019
Restarting "SYS"."ABORT":  /******** AS SYSDBA directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=dev ABORT_STEP=-1 job_name=abort
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "DEV"."OBJECT_LIST"                         71.37 MB  536920 rows
. . exported "DEV"."BONUS"                                   0 KB       0 rows
. . exported "DEV"."DEPT"                                6.023 KB       4 rows
. . exported "DEV"."EMP"                                 8.773 KB      14 rows
. . exported "DEV"."SALGRADE"                            5.953 KB       5 rows
Master table "SYS"."ABORT" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.ABORT is:
  /u02/datapump/exp.dmp
Job "SYS"."ABORT" successfully completed at Fri Sep 20 12:00:18 2019 elapsed 0 00:02:22

Example 2: with value >0 

With this value we can abort the job after the particular object which stored in the master table with the corresponding process order number. 

In this example the I mentioned the abort_step value is 9 which is equivalent to the table EMP in the master table. You can query the master table parallelly.

advertisements
 
oracle@TESTBOX dbhome_1]$ expdp directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=dev abort_step=9 job_name=abort

Export: Release 19.0.0.0.0 - Production on Fri Sep 20 13:36:38 2019
Version 19.3.0.0.0

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

Username: / as sysdba
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."ABORT":  /******** AS SYSDBA directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=dev abort_step=9 job_name=abort
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "DEV"."OBJECT_LIST"                         71.37 MB  536920 rows
. . exported "DEV"."BONUS"                                   0 KB       0 rows
. . exported "DEV"."DEPT"                                6.023 KB       4 rows
ORA-31697: aborting operation at process order number 9

Job "SYS"."ABORT" stopped due to fatal error at Fri Sep 20 13:37:39 2019 elapsed 0 00:00:55

select process_order, object_name , object_schema, object_type from abort  where process_order>0
SQL> /

PROCESS_ORDER OBJECT_NAME             OBJECT_SCHEMA             OBJECT_TYPE
------------- ------------------------------ ------------------------------
           6 OBJECT_LIST              DEV                      TABLE_DATA
           6
           7 BONUS                    DEV                      TABLE_DATA
           8 DEPT                     DEV                      TABLE_DATA
           8
           9 EMP                      DEV                      TABLE_DATA
          10 SALGRADE                        DEV                       TABLE_DATA
          11                                                   INDEX_STATISTICS
          12                                                   INDEX_STATISTICS
          13                                                   TABLE_STATISTICS
          14                                                   TABLE_STATISTICS
          15                                                   TABLE_STATISTICS
          16                                                   TABLE_STATISTICS
          17                                                   TABLE_STATISTICS

_____________________________________________________________________________________________________________________

Website Stats

0 comments:

Post a Comment

Labels

Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)
 

acehints.com Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer