DataPump Expdp KEEP_MASTER Option

_____________________________________________________________________________________________________________________

Master Table:- Master table is the key table for the data pump jobs. It keep track of the respective datapump jobs. The table name is same as the job name of the export or import job.
Master table gets created during the initiation of the export activity. By default, this table get dropped at the end of the successful completion of the export job. If we drop the master table during the export job then the export job will terminate. Also, when we kill the expdp using kill_job command this table gets dropped. This table keeps track of detailed progress of the export job.
KEEP_MASTER Option
This option determines whether the master table should be retained or not.
Syntax:- KEEP_MASTER=[YES | NO]

Example:-
expdp directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=test job_name=kmaster keep_master=yes

Export: Release 19.0.0.0.0 - Production on Fri Sep 27 08:56:30 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"."KMASTER":  /******** AS SYSDBA directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=test job_name=kmaster keep_master=yes
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 "TEST"."BONUS"                                  0 KB       0 rows
. . exported "TEST"."DEPARTMENT"                         6.007 KB       4 rows
. . exported "TEST"."EMPLOYEE"                           8.734 KB      14 rows
. . exported "TEST"."PROJECT"                            6.609 KB       4 rows
. . exported "TEST"."PROJECT_PARTICIPATION"              7.265 KB      14 rows
. . exported "TEST"."ROLE"                               5.554 KB       3 rows
. . exported "TEST"."SALARYGRADE"                        6.007 KB       5 rows
Master table "SYS"."KMASTER" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.KMASTER is:
  /u02/exp/exp.dmp
Job "SYS"."KMASTER" successfully completed at Fri Sep 27 08:57:48 2019 elapsed 0 00:01:09

SQL> select tname from tab where tname='KMASTER';

TNAME
--------------------------------------------------------------------------------
KMASTER

col base_object_schema for a10
col object_name for a30
col base_object_schema for a30
col sizeinkb for 999.99
select base_object_schema, object_name, process_order,  completed_rows, dump_length/1024 sizeinkB
from kmaster
where process_order >=1
and object_type_path = 'SCHEMA_EXPORT/TABLE/TABLE_DATA'
and object_name is not null

BASE_OBJECT_SCHEMA         OBJECT_NAME               PROCESS_ORDER COMPLETED_ROWS SIZEINKB
-------------------------- ------------------------- ------------- -------------- --------
TEST                       BONUS                           13             0      .00
TEST                       DEPARTMENT                      14             4     6.01
TEST                       EMPLOYEE                        15             14    8.73
TEST                       PROJECT                         16             4     6.61
TEST                       PROJECT_PARTICIPATION           17             14    7.27
TEST                       ROLE                            18             3     5.55
TEST                       SALARYGRADE                     19             5     6.01

7 rows selected.


_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

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