advertisements
_____________________________________________________________________________________________________________________
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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment