Home » Other » Marketplace » McDP: like expdp/impdp Data Pump programs and much more (10g-11gR2)
McDP: like expdp/impdp Data Pump programs and much more [message #654960] Wed, 17 August 2016 09:46 Go to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Before presenting McDP, I will make some reminders about export and import in Oracle.

These programs allows to unload into files part of a database and reload these files into another or the same database, with or without modification on the objects.
Oracle has 2 engines to make this: the original one (exp/imp) and Data Pump (expdp/impdp).

The original mechanism is a client-only one which means that the client programs do all the work.
"exp" reads the objects definition and data from the database using SQL interface like any database application, transfers them across the network (if the client is not on the database server), converts them in a proprietary format and write them into one or several files.
"imp" reads these files, converts their content into SQL statements applying the desired transformation (only changing the owner is possible) and send these SQL statements to the database to create the objects and/or insert the data.

Data Pump is made of 2 parts: 1) a client one, the expdp and impdp programs, and 2) a server one, the Data Pump engine which resides inside the database and runs in its instance.
The client program allows you to start an export or import job which will be executed by the server part, the dump files are on the database server. Unlike the previous mechanism, if you interrupt the program, you do not abort the export/import, this one continues on the server part. More, using a Control-C (SIGINT), the client program shows a prompt allowing you to continue, stop or make other actions on the current job. You can then leave the client program, let him continue on the server and, after a while, execute a new expdp/impdp program attaching the previous job to see what it is currently doing.
Data Pump also allows to modify many and many objects parameters during the import process (see the help below).

Now back to McDP, the title is more the target than the current status, I focused on the "much more" to implement some features the current Data Pump client programs (expdp and impdp) have not.
So what does McDP not do (for the moment):
- it does not export
- it does not import
- it is not an interactive client, just a command line tool

Saying that you will ask but what does it do then? No export, no import, what is the relation with Data Pump?
The answer is that it is another Data Pump client in the same way than expdp and impdp but doing different things.
There they are:
  • List the current known Data Pump jobs with some of their parameters: owner, job name, operation (EXPORT, IMPORT...), mode (FULL, SCHEMA, TABLE), state, degree of parallelism, number of active sessions, number of attached sessions and, if you want, the command line and last message (LIST command)
  • Stop/suspend, kill and restart a Data Pump job (STOP, KILL and RESTART commands)
  • Alter some execution parameters of a Data Pump job (ALTER command)
  • Display the command line of a Data Pump job (COMMAND command)
  • Display a detailed status of a Data Pump job (DISPLAY command)
  • Display where is a Data Pump job that is what it has already done and what it is currently doing (WHERE command)
  • Display the content of a Data Pump dump (CONTENT command)
  • Display information about a Data Pump dump file (DISPLAY command)
  • Follow a Data Pump job and display information as if you were the one who launched it (FOLLOW command)
  • Get the DDL of a set "objects" applying on them transformation (DDL command)
  • Get the DDL of one or more Oracle users with or without their privileges to help you recreate them (USER command)
  • Copy a schema or part of it into another or the same one and a remote or local database
Interested?
So here's the (quite long) help:
C:\>McDP -h

McDP Utility by Michel Cadot: Version 2016.08.25

Copyright (c) Michel Cadot, 2016. All rights reserved.

Usage 1: mcdp.pl { { -h | --help }
                 | [<logon>] <command> [<option> [...]] }

where "-h" displays the version and the usage help and <command> can be one of:
  { -a   | --alter }    [<schema>.]<dp job> [[-opt] <execution option>]
          Modifies some execution parameters if the job.
  { -c   | --content }  [<directory>:]<file>[,...]
                        [[--]show] [{[--]keep|[--]drop}]
                        [[-opt] <filter/transform/display option>]
          Gives the content of dump file(s) accordingly with given options.
  { -cmd | --command }  [<schema>.]<dp job> [[-opt] <display option>]
          Gives the Data Pump job command line.
  { -cp  | --copy }     <db link> [<source schema> [<target schema>]]
                        [[-opt] <filter/transform/display option>]
          Copies a schema or part of it into another or same one.
  { -d   | --display }  [<schema>.]<dp job> [[-opt] <display option>]
          Gives information about a Data Pump job.
  { -d   | --display }  {<directory>:<file>|<full path file>} [[-opt] <display option>]
          Gives information about a dump file.
  { -ddl | --ddl }
    [<directory>:]<file> <dblink> <type> [<object>[,<object>...]]
                                         [[--]show] [{[--]keep|[--]drop}]
                                         [[-opt] <filter/transform/display option>]
    Retrieves the DDL for all <object> related to <type> Data Pump session accross
    <dblink> applying the filter and/or transform options.
  { -f   | --follow }   [<schema>.]<dp job> [[-opt] <display option>]
          Follows a Data Pump job displaying information as and when it runs.
  { -k   | --kill }     [<schema>.]<dp job>
                        [{[--]keep|[--]drop [[--]force]}] [[--]noprompt]
          Kills a Data Pump job.
  { -l   | --list }     [<schema>] [{[--]active|[--]idle}] [{-v|[--]verbose}]
                        [-opt schema=<schema>]
          Lists Data Pump jobs of all or one schema.
  { -m   | --modify }   [<schema>.]<dp job> [[-opt] <execution option>]
          Modifies some execution parameters of Data Pump job.
  { -r   | --restart | --continue } [<schema>.]<dp job> [<service name>]
                                    [[--]skip_current]
                                    [[-opt] service_name=<service name>]
          Restarts a previously stopped or killed job.
  { -s   | --stop | --suspend } [<schema>.]<dp job>
                                [{ [--]keep | [--]drop [force] }] [[--]noprompt]
          Stops a Data Pump job.
  { -u   | --user }             [<user>[,...]] [[-opt] grants=<grant type>[,...]]
                                               [-opt user=<user>[,...]]
          Get the DDL to recreate accounts with, possibly, roles, system grants and
          object grants.
  { -w   | --where }    [<schema>.]<dp job> [[--]safe] [[-opt] <display option>]
          Displays what a job has already completed and what it is currently working on.

and <logon> is:
  { <username>[/<password>][@<connect_identifier>]
  | /[@<connect_identifier>] }
  | <proxyuser>[<username>][/<password>][@<connect_identifier>]
  | [<username>]/[@<connect_identifier>] }
  [AS SYSDBA]
in the 2 lines before the last one, [] around <username> are real [] not syntactical
characters denoting an optional parameter. These lines refer to proxy connection.
<logon> must be the first parameter if not introduced by the USERID keyword (see below).

Notes:
* If <username> is not the same than <schema>, <username> must have the
  "DATAPUMP_EXP_FULL_DATABASE" or "DATAPUMP_IMP_FULL_DATABASE" role (depending on
  the command or job type). If it has "SELECT_CATALOG_ROLE" role or
  "SELECT ANY DICTIONARY" privilege it can also have more information about its
  own Data Pump jobs.
* About "-cmd" command, if the job was not launched using expdp or impdp or if
  CLIENT_COMMAND was given, McDP gives this later one and not the original command.
* [directory] in "--content", "--ddl" and "--display" commands is an Oracle directory;
  if it is not given and <file> is not a complete file path, the directory given with
  the "DIRECTORY" option is taken and if there is none, DATA_PUMP_DIR is taken.
* If full path files are given and the directory they are in does not exist, the
  account must have the "CREATE ANY DIRECTORY" privilege to be able to create a
  temporary Oracle directory to the files.
* You can restrict the "--list" to active or idle Data Pump jobs using the "ACTIVE"
  or "IDLE" option (see below). You can also restrict to a schema using the <schema>
  parameter or the "-opt schema=<schema>" option.
* You can constrain the service on which a job is restarted using the <service name>
  parameter or the "-opt service_name=<service name>" option.
* If you use "DROP" option on "--kill" or "--stop" command the master table is dropped
  and the job cannot be restarted later (default option is KEEP). If you use "--kill"
  command, the job and its workers are killed and the job may or not be restartable,
  even if DROP is not given, and some parts of the job may have to be rerun. If you use
  "--stop" command, the workers are allowed to finish their current task before ending.
* If you use "KEEP" option on "--content" or "-ddl" command the SQL result file is kept.
* "SHOW" indicates if you want the result SQL file to be displayed on the screen or not.
* "SAFE" option is useful in commands that may create some objects (type, procedure)
  (if the current account has the privileges to)  to display more information;
  this option lays down that McDP can't create any object.
* In "COPY" command, if you don't give the target schema, the source one is taken;
  if you don't give the source schema, the current user is taken.
* There can be several "-opt" parameters, see "OPTION" below.

Usage 2: mcdp.pl [KEYWORD=<value> [...]]

The available keywords and their descriptions follow.

  ACTIVE={YES|NO}                   Displays only active jobs for LIST command.
  COMMAND=<command>                 Command to apply this can be ALTER, COMMAND, CONTENT,
                                      COPY, CONTINUE, DDL, FOLLOW, DISP[LAY], KILL, [LIST],
                                      MOD[IF[Y]], RESTART, STOP, SUSP[END], USER or WHERE.
  DIRECTORY=<Oracle directory>      Oracle directory for CONTENT, DDL and DISPLAY commands.
  DROP={YES|NO}                     Drops master table, for KILL and STOP/SUSPEND commands.
                                    Drops SQL result file for CONTENT and DDL commands.
  DUMPFILE=[<dir>:]<file>[,...]     File(s) to analyze.
  FORCE={YES|NO}                    Forces drop of master table (default is NO).
  HELP={YES|NO}                     Displays this help (default is NO).
  FROMUSER=<source schema>          Gives the source schema for COPY command.
  IDLE={YES|NO}                     Displays only idle jobs for LIST command.
  JOB=[<schema>.]<dp job>           Data Pump job on which to apply the command.
  KEEP={YES|NO}                     Keeps master table, for KILL and STOP/SUSPEND commands.
                                    Keeps SQL result file for CONTENT and DDL commands.
  NETWORK_LINK=<dblink>             Name of remote database link to the source database.
  NOPROMPT={YES|NO}                 Do not prompt for command confirmation
                                      (default is NO that is ask for confirmation).
  OBJECT=<object>[,<object>...]     Object lists for DDL command.
  SAFE={YES|NO}                     Lays down to create no new objects.
  SERVICE_NAME=<service name>       RAC service name on which the job will run.
  SHOW={YES|NO}                     Displays the generated SQL file for DDL and CONTENT.
  SKIP_CURRENT={YES|NO}             Skips current step when restarting an import.
  SOURCE=<source schema>            Gives the source schema for COPY command.
  SQLFILE=[<directory>:]<file>      SQL result file
  TARGET=<target schema>            Gives the target schema for COPY command.
  TOUSER=<target schema>            Gives the target schema for COPY command.
  TYPE=<type>                       Data Pump session type: FULL, SCHEMA or TABLE.
  USER=<user>[,...]                 Account name(s) for USER command; joker characters
                                      (_%) are expanded.
  USERID=<logon>                    User credentials to connect (see above).
  VERBOSE={YES|NO}                  Sets a verbose output
  <miscellaneous options>           Syntax is <option>=<value> where <option> and <value>
                                      depend on the command and are described below.

Notes:
* "SUSPEND" and "STOP" are synonymous as well as "RESTART" and "CONTINUE", and
  "ALTER" and "MODIFY".
* "SOURCE" and "FROMUSER" are synonymous as well as "TARGET" and "TOUSER".
* Valid but irrelevant options for a command are (almost always) ignored (no error)
    whereas invalid options return an error.
* Default value for "DIRECTORY" is DATA_PUMP_DIR.
* "DUMPFILE" can be a full path file name or a file inside the <dir> or "DIRECTORY" value.
* Depending on the command there can be or not multiple "OPTION" values among possible ones.
* For "ALTER" and "MODIFY" commands (execution options):
    ADD_FILE                  Adds dumpfiles to dumpfile set (export only), syntax:
                                [<file size>#][<directory>:]<file>[,...]
    PARALLEL                  Changes the number of active workers.
    REUSE_DUMPFILES           Overwrites destination dump file if it exists
                                (export only), syntax: { YES | NO }
* For "CONTENT", "COPY" and "DDL" commands (filter and transform options):
    CLIENT_COMMAND           Assigns a text to the current session
                               VALUE[:OBJECT_TYPE][,VALUE[:OBJECT_TYPE]...]
    DISABLE_ARCHIVE_LOGGING  Specifies whether to disable archive logging: YES/NO
    EXCLUDE                  Gives an expression for objects to exclude, syntax:
                               OBJECT_TYPE[:"EXPRESSION"][,OBJECT_TYPE[:"EXPRESSION"]...]
    INCLUDE                  Gives an expression for objects to include, syntax:
                               OBJECT_TYPE[:"EXPRESSION"][,OBJECT_TYPE[:"EXPRESSION"]...]
    INMEMORY                 Indicates if IN MEMORY has to be specified or not, syntax:
                               VALUE[:OBJECT_TYPE][,VALUE[:OBJECT_TYPE]...]
    INMEMORY_CLAUSE          Specifies an IN MEMORY clause for, syntax:
                               TEXT[:OBJECT_TYPE][,TEXT[:OBJECT_TYPE]...]
    LOB_STORAGE              Specifies a LOB STORAGE clause for tables, possible
                               values: SECUREFILE, BASICFILE, DEFAULT, NO_CHANGE
    NAME                     Gives the name pattern of the displayed objects, syntax:
                               OBJECT_TYPE:"EXPRESSION"[,OBJECT_TYPE:"EXPRESSION"...]
    OID                      Indicates if OID has to be included or not, syntax:
                               VALUE[:OBJECT_TYPE][,VALUE[:OBJECT_TYPE]...]
    PARALLEL                 Gives a degree of parallelism to the analysis
    PARTITION                Gives an expression to filter partitions, syntax:
                               [[SCHEMA.]TABLE:]EXPRESSION
    PCTSPACE                 Indicates if PCTSPACE has to be included or not, syntax:
                               VALUE[:OBJECT_TYPE][,VALUE[:OBJECT_TYPE]...]
    REMAP_DATAFILE           Indicates how to remap a datafile, syntax:
                               OLD_VALUE:NEW_VALUE[,OLD_VALUE:NEW_VALUE...]
    REMAP_SCHEMA             Indicates how to remap a schema, syntax:
                               OLD_VALUE:NEW_VALUE[,OLD_VALUE:NEW_VALUE...]
    REMAP_TABLE              Indicates how to remap a table, syntax:
                               OLD_VALUE:NEW_VALUE[,OLD_VALUE:NEW_VALUE...]
    REMAP_TABLESPACE         Indicates how to remap a tablespace, syntax:
                               OLD_VALUE:NEW_VALUE[,OLD_VALUE:NEW_VALUE...]
    SCHEMA                   Gives the name pattern of the displayed schema, syntax:
                               OBJECT_TYPE:"EXPRESSION"[,OBJECT_TYPE:"EXPRESSION"...]
    SEGMENT_ATTRIBUTES       Indicates if segment attributes have to be included or not,
                               syntax: VALUE[:OBJECT_TYPE][,VALUE[:OBJECT_TYPE]...]
    SEGMENT_CREATION         Indicates if segment creation clause has to be included or
                               not, syntax: VALUE[:OBJECT_TYPE][,VALUE[:OBJECT_TYPE]...]
    SOURCE_EDITION           Indicates the source edition of the objects.
    STORAGE                  Indicates if STORAGE clause has to be included or not,
                               syntax: VALUE[:OBJECT_TYPE][,VALUE[:OBJECT_TYPE]...]
    TABLE_COMPRESSION_CLAUSE Specifies a COMPRESSION clause for tables, See Oracle
                               Database SQL Language Reference for more information
                               about table compression options and syntax, for example
                               "COMPRESS BASIC".
    TABLESPACE               Gives the name pattern of the displayed tablespace, syntax:
                               OBJECT_TYPE:"EXPRESSION"[,OBJECT_TYPE:"EXPRESSION"...]
    TARGET_EDITION           Indicates the target edition of the objects.
    VIEWS_AS_TABLE           Indicates if a view has to be converted to a table, syntax;
                               [SCHEMA_NAME.]VIEW_NAME[:TABLE_NAME][,...]
   "EXPRESSION" can be any SQL expression allowed in a SQL WHERE clause.
   "VALUE" can be YES or NO.
   "TEXT" can be any valid clause text for the transformation (see Oracle documentation).
* For "FOLLOW" command (display options):
    TIMEOUT                   Number of minutes without any new message before a new
                                status message is displayed by McDP (default is 5 minutes).
    TIMEOUTMAX                Maximum value for time-out (default is NULL which is
                                unlimited, 0 is also unlimited).
    TIMEOUTMF                 Multiplying factor to apply to TIMEOUT when TIMEOUTNB
                                times-out have been raised.
    TIMEOUTNB                 Number of consecutive times-out before a multiplying
                                factor is applied (default is 5).
   To avoid too many messages, when TIMEOUTNB times-out have raised, the time-out time
   is multiplied by TIMEOUTMF until it reaches or exceeds TIMEOUTMAX.
* For "LIST" command (filter option):
    SCHEMA                    Restricts the list of Data Pumps to a schema.
* For "USER" command (grant options):
    GRANT                     Gives the grant types that should be included in the script,
                                could be one or several among ROLE, SYSTEM and OBJECT, or
                                ALL for all these ones.
* For all commands (display option):
    LINESIZE                  Sets the total number of characters that McDP displays on
                                one line before beginning a new line (default is 120).

If <logon> is not given, McDP asks for it.

The program is provided as it is without any guarantees or warranty. Although the
author has attempted to find and correct any bugs in this free program, the author
is not responsible for any damage or losses of any kind caused by the use or misuse
of the program. The author is under no obligation to provide support, service,
corrections, or upgrades to this program.

You can freely use, copy and distribute this program but you can't modify it without
the permission of the author you can contact on http://www.orafaq.com
You can post your comments, ask for improvements, report bugs... on the program at
http://www.orafaq.com/forum/t/201760/

As you can see, the program supports 2 types of usages: a Unix style one with "-<opt> <value>" parameters and another one similar to the expdp/impdp program with "KEYWORD=<value>" parameters. You can even mix both syntax.
Note: If you want to alter others Data Pump jobs you must have the DATAPUMP_EXP_FULL_DATABASE or DATAPUMP_IMP_FULL_DATABASE roles, if you want to see information about others Data Pump jobs, you must have SELECT privileges on some DBA_DATAPUMP and V$ views or have the SELECT_CATALOG_ROLE role or the SELECT ANY DICTIONARY privilege.
Note: You can give files within an Oracle directory or with an absolute path. In this later case, if the file path matches an Oracle directory you have access there is no problem otherwise McDP will try to create a new Oracle directory for this path, if you have the privilege to do so. The directory is dropped before the program leaves.
Note: Some commands can give more information if you have CREATE TYPE and/or CREATE PROCEDURE privileges, if so the program will create objects to execute the command and remove them at the end. If you don't want McDP creates these objects give the "-safe" or "SAFE=YES" option.
Note: McDP has currently not been tested on version 12c but it is most likely it works, waiting for your feedback if you see anything wrong...

Don't worry, the next posts will show examples of McDP executions.

Latest version: 2016.08.28, download in Datapump wiki page
32-bit: 
  MD5: bdb37f1b6ca4c1a8b388ebca6738d3a1
  SHA-1: bcc1e8098da51ef7803d781841bdba8feccb6503
64-bit:
  MD5: 279d59a57c25ef8f10e25b8d171d79bb
  SHA-1: 5f49d334a0b9ed6c854b6d4a7224417062bbf4d9

[Updated on: Sun, 30 July 2017 09:29]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655114 is a reply to message #654960] Mon, 22 August 2016 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

LIST command

I will start with LIST command which is the default action if you specify none.
If you have a privileged account, McDP will list all Data Pump jobs otherwise you will see only yours.

Syntax:
McDP [<logon>] [{ -l | --list }] [<schema>] [{[--]active|[--]idle}] [{-v|[--]verbose}] [-opt schema=<schema>]
McDP [USERID=<logon>] [COMMAND=LIST] [ACTIVE={YES|NO}] [IDLE={YES|NO}] [VERBOSE={YES|NO}] [SCHEMA=<schema]
  • "--active" or "ACTIVE=YES" restricts the result to running Data Pump jobs (default is "ACTIVE=NO").
  • "--idle" or "IDLE=YES" restricts the result to not running Data Pump jobs (default is "IDLE=NO").
  • "-v", "--verbose" or "VERBOSE=YES" adds some information about the Data Pump jobs and their sessions.
Of course, ACTIVE and IDLE parameters are exclusive.
Note: If you don't give database credentials, McDP will ask you for them.
Note: The line size can be adjusted as you want using the LINESIZE=<n> parameter. The default is 120.

Examples where MICHEL is a privileged account

Display all Data Pump jobs
C:\>McDP michel/michel

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 18-AOÛT-2016 09:39:33

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_EXPORT_SCHEMA_01           EXPORT    SCHEMA     NOT RUNNING     0    0    0
MICHEL                         SYS_EXPORT_SCHEMA_02           EXPORT    SCHEMA     NOT RUNNING     0    0    0
MICHEL                         SYS_EXPORT_SCHEMA_03           EXPORT    SCHEMA     NOT RUNNING     0    0    0
MICHEL                         SYS_EXPORT_SCHEMA_04           EXPORT    SCHEMA     NOT RUNNING     0    0    0
MICHEL                         SYS_EXPORT_TABLE_01            EXPORT    TABLE      NOT RUNNING     0    0    0
MICHEL                         SYS_IMPORT_FULL_02             IMPORT    FULL       NOT RUNNING     0    0    0
MICHEL                         SYS_IMPORT_SCHEMA_01           IMPORT    SCHEMA     NOT RUNNING     0    0    0
MICHEL                         SYS_IMPORT_SCHEMA_02           IMPORT    SCHEMA     NOT RUNNING     0    0    0
MICHEL                         SYS_SQL_FILE_FULL_01           SQL_FILE  FULL       NOT RUNNING     0    0    0
MICHEL                         SYS_SQL_FILE_FULL_02           SQL_FILE  FULL       NOT RUNNING     0    0    0
MICHEL                         SYS_SQL_FILE_FULL_03           SQL_FILE  FULL       NOT RUNNING     0    0    0
MICHEL                         SYS_SQL_FILE_FULL_04           SQL_FILE  FULL       NOT RUNNING     0    0    0
MICHEL                         SYS_SQL_FILE_FULL_05           SQL_FILE  FULL       NOT RUNNING     0    0    0
MICHEL                         SYS_SQL_FILE_FULL_06           SQL_FILE  FULL       NOT RUNNING     0    0    0
MICHEL                         SYS_SQL_FILE_SCHEMA_01         SQL_FILE  SCHEMA     NOT RUNNING     0    0    0
SCOTT                          SYS_EXPORT_SCHEMA_01           EXPORT    SCHEMA     NOT RUNNING     0    0    0
SCOTT                          SYS_EXPORT_SCHEMA_02           EXPORT    SCHEMA     EXECUTING       1    3    1
  • "Operation" column gives the type of Data Pump job; it can be "EXPORT", "ESTIMATE", "IMPORT", "SQL_FILE" or "NETWORK"
  • "Mode" column gives the mode of the Data Pump job; it can be "FULL", "SCHEMA", "TABLE", "TABLESPACE" or "TRANSPORTABLE"
  • "Par." column gives the parallelism degree
  • "Ses." column gives the number of sessions interested in the data Pump job: expdp/impdp or attached program sessions, master session, workers sessions (here 3: the expdp program, the master and one worker)
  • "Att." column gives the number of attached sessions (here 1: the expdp program)
Display SCOTT's active Data Pump jobs
C:\>McDP userid=michel/michel schema=scott active=yes

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 18-AOÛT-2016 09:39:56

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
SCOTT                          SYS_EXPORT_SCHEMA_02           EXPORT    SCHEMA     EXECUTING       1    3    1

Display SCOTT's active Data Pump jobs in verbose mode
C:\>McDP userid=michel/michel schema=scott active=yes verbose=yes

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 18-AOÛT-2016 09:40:10

Data Pump jobs
==============

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
SCOTT                          SYS_EXPORT_SCHEMA_02           EXPORT    SCHEMA     EXECUTING       1    3    1

"SCOTT"."SYS_EXPORT_SCHEMA_02":
  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 18/08/2016 09:39:26 - State: EXECUTING
  Client command: scott/ dumpfile=scott.dmp reuse_dumpfiles=yes schemas=scott keep_master=yes directory=DATA_PUMP_DIR

Data Pump sessions
==================

Owner                          Job                            Session type   Sid           Serial Status
------------------------------ ------------------------------ -------------- ------------ ------- --------
SCOTT                          SYS_EXPORT_SCHEMA_02           DBMS_DATAPUMP  1:143           1563 ACTIVE
SCOTT                          SYS_EXPORT_SCHEMA_02           MASTER         1:31             281 ACTIVE
SCOTT                          SYS_EXPORT_SCHEMA_02           WORKER         1:155           1013 ACTIVE

Data Pump waiting sessions
==========================


Data Pump long operations
=========================

--> None.
Note: "Sid" column is in the format <instance number>:<sid>

Following an export
Launching the following command:
expdp michel/michel schemas=michel directory=my_dir parallel=2 dumpfile=dmp1.dmp,dmp2.dmp
Now here's what we see with the LIST command during the execution of the export:
C:\>McDP michel/michel -l active verbose

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 18-AOÛT-2016 10:04:01

Data Pump jobs
==============

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_EXPORT_SCHEMA_05           EXPORT    SCHEMA     EXECUTING       2    4    1

"MICHEL"."SYS_EXPORT_SCHEMA_05":
  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 18/08/2016 10:03:25 - State: EXECUTING
  Client command: michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmp1.dmp,dmp2.dmp

Data Pump sessions
==================

Owner                          Job                            Session type   Sid           Serial Status
------------------------------ ------------------------------ -------------- ------------ ------- --------
MICHEL                         SYS_EXPORT_SCHEMA_05           DBMS_DATAPUMP  1:144            695 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           MASTER         1:22             363 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:38            1953 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:148            709 ACTIVE

Data Pump waiting sessions
==========================

Sid          Status    Suspend time            Timeout Resumable cause
SQL text
------------ --------- -------------------- ---------- -----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
1:148        NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.2
 BEGIN    SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_05', 'MICHEL', 0);  END;
1:22         NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
1:38         NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.1
SELECT /*+rule*/ KU$.OBJ_NUM FROM SYS.KU$_PROCOBJ_OBJNUM_VIEW KU$ WHERE  KU$.OWNER_NAME  IN ('MICHEL') AND  NOT EXISTS (
SELECT 1 FROM  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='PROCOBJ' AND A.NAME=KU$.NAME AND A.SCHEMA=KU$.OWNER_NAME) AND NOT EX
ISTS (SELECT 1 FROM  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND A.NAME=KU$.OWNER_NAME)


Data Pump long operations
=========================

--> None.
C:\>McDP michel/michel -l active verbose

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 18-AOÛT-2016 10:04:44

Data Pump jobs
==============

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_EXPORT_SCHEMA_05           EXPORT    SCHEMA     EXECUTING       2    4    1

"MICHEL"."SYS_EXPORT_SCHEMA_05":
  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 18/08/2016 10:03:25 - State: EXECUTING
  Client command: michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmp1.dmp,dmp2.dmp

Data Pump sessions
==================

Owner                          Job                            Session type   Sid           Serial Status
------------------------------ ------------------------------ -------------- ------------ ------- --------
MICHEL                         SYS_EXPORT_SCHEMA_05           DBMS_DATAPUMP  1:144            695 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           MASTER         1:22             363 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:38            1953 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:148            709 ACTIVE

Data Pump waiting sessions
==========================

Sid          Status    Suspend time            Timeout Resumable cause
SQL text
------------ --------- -------------------- ---------- -----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
1:148        NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.2
SELECT NVL((SELECT flags    FROM "MICHEL"."SYS_EXPORT_SCHEMA_05" WHERE process_order = :1), 0), NVL((SELECT xml_clob FRO
M "MICHEL"."SYS_EXPORT_SCHEMA_05" WHERE process_order = :2), NULL) FROM SYS.DUAL
1:22         NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
1:38         NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.1
SELECT /*+rule*/ 'KU$.STRIPPED_VAL', 0 ,KU$.LOCAL ,KU$.URL ,KU$.URL ,'XMLSCHEMA' ,KU$.OWNER_NAME ,KU$.SCHEMAOID ,KU$.SCH
EMA_LEVEL FROM SYS.KU$_EXP_XMLSCHEMA_VIEW KU$ WHERE  KU$.OWNER_NAME  IN ('MICHEL') AND  NOT EXISTS (SELECT 1 FROM  SYS.K
U$NOEXP_TAB A WHERE A.OBJ_TYPE='XMLSCHEMA' AND A.NAME=KU$.URL AND A.SCHEMA=KU$.OWNER_NAME) AND NOT EXISTS (SELECT 1 FROM
  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND A.NAME=KU$.OWNER_NAME)  ORDER BY CASE KU$.SCHEMA_LEVEL WHEN 0 THEN 99
9999 ELSE KU$.SCHEMA_LEVEL END


Data Pump long operations
=========================

Ins Sid   CSid  Start    Remain Operation...
--- ----- ----- -------- ------ ----------------------------------------------------------------------------------------
1   22    0     10:03:54     27 SYS_EXPORT_SCHEMA_05: EXPORT : 249 out of 468 MB done
Note: "Remain" column gives the estimated remaining time in seconds.

C:\>McDP michel/michel -l active verbose

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 18-AOÛT-2016 10:04:59

Data Pump jobs
==============

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_EXPORT_SCHEMA_05           EXPORT    SCHEMA     EXECUTING       2    4    1

"MICHEL"."SYS_EXPORT_SCHEMA_05":
  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 18/08/2016 10:03:25 - State: EXECUTING
  Client command: michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmp1.dmp,dmp2.dmp

Data Pump sessions
==================

Owner                          Job                            Session type   Sid           Serial Status
------------------------------ ------------------------------ -------------- ------------ ------- --------
MICHEL                         SYS_EXPORT_SCHEMA_05           DBMS_DATAPUMP  1:144            695 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           MASTER         1:22             363 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:38            1953 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:148            709 ACTIVE

Data Pump waiting sessions
==========================

Sid          Status    Suspend time            Timeout Resumable cause
SQL text
------------ --------- -------------------- ---------- -----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
1:148        NORMAL                               7200  MICHEL.SYS_EXPORT_SCHEMA_05.2
 BEGIN    SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_05', 'MICHEL', 0);  END;
1:22         NORMAL                               7200  MICHEL.SYS_EXPORT_SCHEMA_05
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
1:38         NORMAL                               7200  MICHEL.SYS_EXPORT_SCHEMA_05.1
SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$
.ANC_OBJ.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPAR
E1 ,KU$.TSTZ_COLS ,KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,KU$.PARENT_OBJ.NAME ,KU$.PARENT
_OBJ.OWNER_NAME ,KU$.PROPERTY ,KU$.REFPAR_LEVEL ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,KU$.TRIGFLAG FROM SYS.KU$_FHTAB
LE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND  NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND   KU$.OBJ_NUM I
N (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) AND  (BITAND(KU$.FLAGS,536870912)=0)


Data Pump long operations
=========================

Ins Sid   CSid  Start    Remain Operation...
--- ----- ----- -------- ------ ----------------------------------------------------------------------------------------
1   22    0     10:03:54     37 SYS_EXPORT_SCHEMA_05: EXPORT : 289 out of 468 MB done
And so on...

Following an import with resumable error
Here's another example where the target tablespace is too short.
Creating the SCOTT2 tablespace and schema with the following statements:
create tablespace scott2 datafile '...' size 10m;
create user scott2 identified by TIGER default tablespace scott2 quota unlimited on scott2;
Using the following import command:
C:\>impdp michel/michel dumpfile=scott.dmp directory=my_dir schemas=scott remap_schema=scott:scott2 remap_tablespace=ts_d01:scott2

Import: Release 11.2.0.4.0 - Production on Jeu. Août 18 10:27:39 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MICHEL"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded
Starting "MICHEL"."SYS_IMPORT_SCHEMA_03":  michel/******** dumpfile=scott.dmp directory=my_dir schemas=scott remap_schema=scott:scott2 remap_tablespace=ts_d01:scott2
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT2" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table SCOTT2.T by 128 in tablespace SCOTT2
McDP will display you:
C:\>McDP michel/michel -l active verbose

McDP Utility by Michel Cadot: Version 2016.08.18

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 18-AOÛT-2016 10:31:05

Data Pump jobs
==============

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_IMPORT_SCHEMA_03           IMPORT    SCHEMA     EXECUTING       1    3    1

"MICHEL"."SYS_IMPORT_SCHEMA_03":
  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 18/08/2016 10:27:42 - State: EXECUTING
  Client command: michel/ dumpfile=scott.dmp directory=my_dir schemas=scott remap_schema=scott:scott2 remap_tablespace=t
s_d01:scott2

Data Pump sessions
==================

Owner                          Job                            Session type   Sid           Serial Status
------------------------------ ------------------------------ -------------- ------------ ------- --------
MICHEL                         SYS_IMPORT_SCHEMA_03           DBMS_DATAPUMP  1:32             987 ACTIVE
MICHEL                         SYS_IMPORT_SCHEMA_03           MASTER         1:155           1037 ACTIVE
MICHEL                         SYS_IMPORT_SCHEMA_03           WORKER         1:22             369 ACTIVE

Data Pump waiting sessions
==========================

Sid          Job                            Waiting (s) Event
------------ ------------------------------ ----------- ----------------------------------------------------------------
1:22         SYS_IMPORT_SCHEMA_03                     2 statement suspended, wait error to be cleared

Sid          Status    Suspend time            Timeout Resumable cause
SQL text
------------ --------- -------------------- ---------- -----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
1:155        NORMAL                               7200 MICHEL.SYS_IMPORT_SCHEMA_03
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
1:22         SUSPENDED 08/18/16 10:27:47          7200 MICHEL.SYS_IMPORT_SCHEMA_03.1
 BEGIN    SYS.KUPW$WORKER.MAIN('SYS_IMPORT_SCHEMA_03', 'MICHEL', 0);  END;

Sid       Error
Param 1                  Param 2                 Param 3                 Param 4                 Param 5
--------- --------------------------------------------------------------------------------------------------------------
------------------------ ----------------------- ----------------------- ----------------------- -----------------------
1:22      ORA-01653: unable to extend table SCOTT2.T by 128 in tablespace SCOTT2
SCOTT2                   T                       128                     SCOTT2

Data Pump long operations
=========================

--> None.
We see that the Data Pump job is waiting for the tablespace SCOTT2 to be extended when it is working on the import of table SCOTT2.T.
This is useful when you are a DBA and some user is complaining its export is "hanging".

[Updated on: Mon, 22 August 2016 15:22]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655115 is a reply to message #655114] Mon, 22 August 2016 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

DISPLAY job command

After the LIST command the first thing that comes in mind is "can I have more information about this specific job?" and the answer is "Yes, using the DISPLAY command on this job".

Its syntax is:
McDP [<logon>] { -d | --display }  [<schema>.]<job name> [[-opt] <display option>]
McDP [USERID=<logon>] COMMAND=DISPLAY JOB=[<schema>.]<job name> [<display keyword>=<value>]
Note: If you don't provide the schema, it is yours. You must be a privileged user to see others jobs.
Note: The Data Pump job can be currently running or not.

Examples where MICHEL is a privileged account

Display a job following its execution
For the job we followed in the previous post with the LIST command we could get with the DISPLAY one the following output.
Remind: the command which launched the export was:
expdp michel/michel schemas=michel directory=my_dir parallel=2 dumpfile=dmp1.dmp,dmp2.dmp
C:\>McDP michel/michel -d SYS_EXPORT_SCHEMA_05

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 18-AOÛT-2016 10:04:32

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_EXPORT_SCHEMA_05           EXPORT    SCHEMA     EXECUTING       2    4    1

  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 18/08/2016 10:03:25 - State: EXECUTING
  Client command: michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmp1.dmp,dmp2.dmp
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Client Command: michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmp1.dmp,dmp2.dmp
  Command Line Client: 1
  Compression: METADATA_ONLY
  Data Access Method: AUTOMATIC
  Data Options: 0
  Estimate: BLOCKS
  Include Metadata: 1
  Keep Master: 0
  Log File Directory: MY_DIR
  Log File Name: export.log
  Metrics: 0
  Table Consistency: 0
  Trace: 0
  User Metadata: 1
  Schema List: 'MICHEL'
  Schema Expr:  IN ('MICHEL')
  Dump file(s):
    C:\DMP1.DMP
    C:\DMP2.DMP

  Dump file(s):
    C:\DMP1.DMP 280MB
    C:\DMP2.DMP 0MB
  Start time: 18/08/2016 10:03:25
  Done      : 90.8% (285214K/314270K)
  Errors    : 0
  Parallel  : 2
  Worker   1: (DW00) EXECUTING MICHEL.SPKG
  Worker   2: (DW01) EXECUTING MICHEL.SYS_EXPORT_SCHEMA_02   0% Done

Data Pump sessions
==================

Owner                          Job                            Session type   Sid           Serial Status
------------------------------ ------------------------------ -------------- ------------ ------- --------
MICHEL                         SYS_EXPORT_SCHEMA_05           DBMS_DATAPUMP  1:144            695 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           MASTER         1:22             363 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:38            1953 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:148            709 ACTIVE

Data Pump waiting sessions
==========================

Sid          Status    Suspend time            Timeout Resumable cause
SQL text
------------ --------- -------------------- ---------- -----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
1:148        NORMAL                         7200       MICHEL.SYS_EXPORT_SCHEMA_05.2
 BEGIN    SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_05', 'MICHEL', 0);  END;
1:22         NORMAL                         7200       MICHEL.SYS_EXPORT_SCHEMA_05
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
1:38         NORMAL                         7200       MICHEL.SYS_EXPORT_SCHEMA_05.1
SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TYPE_T', '7')), KU$.OBJ_NUM ,KU$.SCHEMA_OBJ.NAME ,KU$.S
CHEMA_OBJ.NAME ,'TYPE' ,KU$.SCHEMA_OBJ.OWNER_NAME FROM SYS.KU$_TYPE_VIEW KU$,TABLE(DBMS_METADATA.FETCH_SORTED_OBJNUMS(20
0001)) KU1$ WHERE     KU$.OBJ_NUM =KU1$.NUM1 ORDER BY KU1$.NUM2


Data Pump long operations
=========================

Ins Sid   CSid  Start    Rest   Operation...
--- ----- ----- -------- ------ ----------------------------------------------------------------------------------------
1   22    0     10:03:54 27     SYS_EXPORT_SCHEMA_05: EXPORT : 249 out of 468 MB done
  • The first part is the same chart than with the LIST command.
  • The second part extends the information given in the LIST command (lines starting with "Server" and "Client command") with all the internal parameters of the Data Pump job.
  • The third part, from the Data Pump engine itself, gives where the job currently is: how the dump files are filled, how much job it has done, what are the workers currrently doing.
  • The next parts are the same ones than in a verbose display of LIST command.
C:\>McDP michel/michel -d SYS_EXPORT_SCHEMA_05

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 18-AOÛT-2016 10:05:07

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_EXPORT_SCHEMA_05           EXPORT    SCHEMA     EXECUTING       2    4    1

  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 18/08/2016 10:03:25 - State: EXECUTING
  Client command: michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmp1.dmp,dmp2.dmp
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Client Command: michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmp1.dmp,dmp2.dmp
  Command Line Client: 1
  Compression: METADATA_ONLY
  Data Access Method: AUTOMATIC
  Data Options: 0
  Estimate: BLOCKS
  Include Metadata: 1
  Keep Master: 0
  Log File Directory: MY_DIR
  Log File Name: export.log
  Metrics: 0
  Table Consistency: 0
  Trace: 0
  User Metadata: 1
  Schema List: 'MICHEL'
  Schema Expr:  IN ('MICHEL')
  Dump file(s):
    C:\DMP1.DMP
    C:\DMP2.DMP

  Dump file(s):
    C:\DMP1.DMP 290MB
    C:\DMP2.DMP 0MB
  Start time: 18/08/2016 10:03:25
  Done      : 96.9% (295815K/305159K)
  Errors    : 0
  Parallel  : 2
  Worker   1: (DW00) EXECUTING MICHEL.TSTATUS
  Worker   2: (DW01) EXECUTING MICHEL.TEMP_ACCOUNT_2   0% Done

Data Pump sessions
==================

Owner                          Job                            Session type   Sid           Serial Status
------------------------------ ------------------------------ -------------- ------------ ------- --------
MICHEL                         SYS_EXPORT_SCHEMA_05           DBMS_DATAPUMP  1:144            695 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           MASTER         1:22             363 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:38            1953 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:148            709 ACTIVE

Data Pump waiting sessions
==========================

Sid          Status    Suspend time            Timeout Resumable cause
SQL text
------------ --------- -------------------- ---------- -----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
1:148        NORMAL                         7200       MICHEL.SYS_EXPORT_SCHEMA_05.2
 BEGIN    SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_05', 'MICHEL', 0);  END;
1:22         NORMAL                         7200       MICHEL.SYS_EXPORT_SCHEMA_05
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
1:38         NORMAL                         7200       MICHEL.SYS_EXPORT_SCHEMA_05.1
SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$
.ANC_OBJ.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPAR
E1 ,KU$.TSTZ_COLS ,KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,KU$.PARENT_OBJ.NAME ,KU$.PARENT
_OBJ.OWNER_NAME ,KU$.PROPERTY ,KU$.REFPAR_LEVEL ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,KU$.TRIGFLAG FROM SYS.KU$_FHTAB
LE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND  NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND   KU$.OBJ_NUM I
N (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) AND  (BITAND(KU$.FLAGS,536870912)=0)


Data Pump long operations
=========================

Ins Sid   CSid  Start    Rest   Operation...
--- ----- ----- -------- ------ ----------------------------------------------------------------------------------------
1   22    0     10:03:54 37     SYS_EXPORT_SCHEMA_05: EXPORT : 289 out of 468 MB done
And so on...

Display a "hanging" Data Pump job
For the import session which "hanged" in the previous post, we could get:
C:\>McDP michel/michel -d SYS_IMPORT_SCHEMA_03

McDP Utility by Michel Cadot: Version 2016.08.18

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 18-AOÛT-2016 10:31:39

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_IMPORT_SCHEMA_03           IMPORT    SCHEMA     EXECUTING       1    3    1

  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 18/08/2016 10:27:42 - State: EXECUTING
  Client command: michel/ dumpfile=scott.dmp directory=my_dir schemas=scott remap_schema=scott:scott2 remap_tablespace=t
s_d01:scott2
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Client Command: michel/ dumpfile=scott.dmp directory=my_dir schemas=scott remap_schema=scott:scott2 remap_tablespace=t
    s_d01:scott2
  Command Line Client: 1
  Data Access Method: AUTOMATIC
  Data Options: 0
  Include Metadata: 1
  Keep Master: 0
  Log File Directory: MY_DIR
  Log File Name: import.log
  Master Only: 0
  Metrics: 0
  Skip Unusable Indexes: 1
  Streams Configuration: 1
  Table Exists Action: SKIP
  Trace: 0
  Schema List: 'SCOTT'
  Schema Expr:  IN ('SCOTT')
  Dump file(s):
    C:\scott.dmp
  Export parameters:
    Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 01/08/2016 18:44:44 - State: EXECUTING
    Client command: michel/ dumpfile=scott.dmp directory=my_dir schemas=scott reuse_dumpfiles=yes
    Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC
      _CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_S
      ORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT=
      'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BI
      NARY' NLS_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
    Client Command: michel/ dumpfile=scott.dmp directory=my_dir schemas=scott reuse_dumpfiles=yes
    Command Line Client: 1
    Compression: METADATA_ONLY
    Data Access Method: AUTOMATIC
    Data Options: 0
    Estimate: BLOCKS
    Include Metadata: 1
    Keep Master: 0
    Log File Directory: MY_DIR
    Log File Name: export.log
    Metrics: 0
    Table Consistency: 0
    Trace: 0
    User Metadata: 1
    Schema List: 'SCOTT'
    Schema Expr:  IN ('SCOTT')
    Dump file(s):
      C:\scott.dmp

  Dump file(s):
    C:\scott.dmp
  Start time: 18/08/2016 10:27:42
  Done      : 0% (0K/19345K)
  Errors    : 0
  Parallel  : 1
  Worker   1: (DW00) EXECUTING SCOTT2.T

Data Pump sessions
==================

Owner                          Job                            Session type   Sid           Serial Status
------------------------------ ------------------------------ -------------- ------------ ------- --------
MICHEL                         SYS_IMPORT_SCHEMA_03           DBMS_DATAPUMP  1:32             987 ACTIVE
MICHEL                         SYS_IMPORT_SCHEMA_03           MASTER         1:155           1037 ACTIVE
MICHEL                         SYS_IMPORT_SCHEMA_03           WORKER         1:22             369 ACTIVE

Data Pump waiting sessions
==========================

Sid          Job                            Waiting (s) Event
------------ ------------------------------ ----------- ----------------------------------------------------------------
1:22         SYS_IMPORT_SCHEMA_03                     1 statement suspended, wait error to be cleared

Sid          Status    Suspend time            Timeout Resumable cause
SQL text
------------ --------- -------------------- ---------- -----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
1:155        NORMAL                               7200 MICHEL.SYS_IMPORT_SCHEMA_03
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
1:22         SUSPENDED 08/18/16 10:27:47          7200 MICHEL.SYS_IMPORT_SCHEMA_03.1
 BEGIN    SYS.KUPW$WORKER.MAIN('SYS_IMPORT_SCHEMA_03', 'MICHEL', 0);  END;

Sid       Error
Param 1                  Param 2                 Param 3                 Param 4                 Param 5
--------- --------------------------------------------------------------------------------------------------------------
------------------------ ----------------------- ----------------------- ----------------------- -----------------------
1:22      ORA-01653: unable to extend table SCOTT2.T by 128 in tablespace SCOTT2
SCOTT2                   T                       128                     SCOTT2

Data Pump long operations
=========================

--> None.
In case of an import, McDP also displays what were the export parameters.

Display information about a completed job (so not running)
C:\>McDP michel/michel -d SYS_EXPORT_SCHEMA_04

McDP Utility by Michel Cadot: Version 2016.08.18

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 18-AOÛT-2016 16:29:55

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_EXPORT_SCHEMA_04           EXPORT    SCHEMA     NOT RUNNING     0    0    0

  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 24/06/2016 20:56:09 - State: COMPLETED
  Message: Job "MICHEL"."SYS_EXPORT_SCHEMA_04" successfully completed at Dim. Août 14 10:56:38 2016 elapsed 0 00:00:00
  Client command: michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmppar1.dmp,dmppar2.dmp keep_master=yes re
use_dumpfiles=true
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Client Command: michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmppar1.dmp,dmppar2.dmp keep_master=yes re
    use_dumpfiles=true
  Command Line Client: 1
  Compression: METADATA_ONLY
  Data Access Method: AUTOMATIC
  Data Options: 0
  Estimate: BLOCKS
  Include Metadata: 1
  Keep Master: 1
  Log File Directory: MY_DIR
  Log File Name: export.log
  Metrics: 0
  Table Consistency: 0
  Trace: 0
  User Metadata: 1
  Schema List: 'MICHEL'
  Schema Expr:  IN ('MICHEL')
  Dump file(s):
    C:\DMPPAR1.DMP
    C:\DMPPAR2.DMP

  Dump file(s):
    C:\DMPPAR1.DMP 273MB
    C:\DMPPAR2.DMP 5MB
  Start time: 24/06/2016 20:56:09
  Done      : 100% (282703K/282703K)
  Errors    : 0
  Parallel  : 2
  Worker   1: (DW00) SUCCESS
  Worker   2: (DW01) SUCCESS
The internal state of a job is the "Server" line at top of the second part (here COMPLETED).

Display information about a suspended job
C:\>McDP michel/michel COMMAND=DISPLAY JOB=SYS_SQL_FILE_FULL_04

McDP Utility by Michel Cadot: Version 2016.08.18

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 18-AOÛT-2016 16:32:48

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_SQL_FILE_FULL_04           SQL_FILE  FULL       NOT RUNNING     0    0    0

  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 07/08/2016 11:02:00 - State: STOPPED
  Message: Job "MICHEL"."SYS_SQL_FILE_FULL_04" stopped due to fatal error at Dim. Août 7 11:03:00 2016 elapsed 0 00:01:0
    0
  Client command:
  MICHEL: Display content of file(s):
    DMPPAR2.DMP
    DMPPAR1.DMP
  SQL FILE = McDPsql_20160807110159.sql
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Client Command:
      MICHEL: Display content of file(s):
        DMPPAR2.DMP
        DMPPAR1.DMP
      SQL FILE = McDPsql_20160807110159.sql
  Data Options: 0
  Include Metadata: 1
  Keep Master: 0
  Metrics: 0
  Reuse Datafiles: 0
  Sql File Directory: MY_DIR
  Sql File Name: McDPsql_20160807110159.sql
  Trace: 0
  Dump file(s):
    C:\DMPPAR1.DMP
    C:\DMPPAR2.DMP
  Export parameters:
    Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 29/06/2016 19:20:50 - State: EXECUTING
    Client command: michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmppar1.dmp,dmppar2.dmp keep_master=yes
reuse_dumpfiles=true
    Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC
      _CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_S
      ORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT=
      'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BI
      NARY' NLS_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
    Client Command: michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmppar1.dmp,dmppar2.dmp keep_master=yes
      reuse_dumpfiles=true
    Command Line Client: 1
    Compression: METADATA_ONLY
    Data Access Method: AUTOMATIC
    Data Options: 0
    Estimate: BLOCKS
    Include Metadata: 1
    Keep Master: 1
    Log File Directory: MY_DIR
    Log File Name: export.log
    Metrics: 0
    Table Consistency: 0
    Trace: 0
    User Metadata: 1
    Schema List: 'MICHEL'
    Schema Expr:  IN ('MICHEL')
    Dump file(s):
      C:\DMPPAR1.DMP
      C:\DMPPAR2.DMP

  ORA-39002: invalid operation
  ORA-39025: jobs of type SQL_FILE are not restartable
  Unable to get info: ORA-39002: invalid operation
This case is different from the previous ones.
First the mode is SQLFILE, that is neither export nor import (although Oracle does it using impdp) but analysis of a dump returning the SQL statements residing in it.
Then it has not been created by expdp or impdp but by McDP, you can see this from the "Client command" data, it is here:
      MICHEL: Display content of file(s):
        DMPPAR2.DMP
        DMPPAR1.DMP
      SQL FILE = McDPsql_20160807110159.sql
This is what generates McDP for a CONTENT command, we'll see below, saying it analyzes the content of the dump made of the 2 mentioned files and returning the result in the given SQL file. We can see that the job was stopped. Such a job is not restartable which means if you stop it you lose all the work that has been done, and we can't get any information from the Data Pump engine which refuses our connection for this job.

[Updated on: Mon, 22 August 2016 15:46]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655116 is a reply to message #655115] Mon, 22 August 2016 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

WHERE job command

And the next question raises "Can we have more information about what the job has done till now?" and the answer is "Yes, using the WHERE command."

Its syntax is:
McDP [<logon>] { -w | --where } [<schema>.]<dp job> [[--]safe] [[-opt] <display option>]
McDP [USERID=<logon>] COMMAND=WHERE JOB=[<schema>.]<job name> SAFE={YES|NO} [<display keyword>=<value>]

Note: This command can give more information if you have the privileges CREATE TYPE and CREATE PROCEDURE it needs to build some result, these objects are removed after the command execution. If you have these privileges but don't want McDP creates any object use the "--safe" or "SAFE=YES" option.

Examples where MICHEL is a privileged account

Display information during an export of several schemas
The export command was:
expdp michel/michel dumpfile=stage1.dmp,stage2.dmp directory=my_dir full=y "include=SCHEMA:\"LIKE 'STAGE%'\"" parallel=2
C:\>McDP michel/michel -w MICHEL.SYS_EXPORT_FULL_01

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 28-JUIL.-2016 19:20:40

"MICHEL"."SYS_EXPORT_FULL_01" at 28/07/2016 19:20:40
EXPORT FULL, EXECUTING, Parallel 2, 1 attached sessions
Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 28/07/2016 19:05:08 - State: EXECUTING

Master work:
  DATABASE_EXPORT/SCHEMA/USER                                    Start: 28/07/2016 19:15:15, End: 28/07/2016 19:15:24
  DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT                      Start: 28/07/2016 19:15:24, End: 28/07/2016 19:16:25
  DATABASE_EXPORT/SCHEMA/ROLE_GRANT                              Start: 28/07/2016 19:16:25, End: 28/07/2016 19:16:27
  DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE                            Start: 28/07/2016 19:16:27, End: 28/07/2016 19:16:29
  DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA                        Start: 28/07/2016 19:16:29, End: 28/07/2016 19:16:31
  DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA                          Start: 28/07/2016 19:16:31, End: 28/07/2016 19:17:40
  DATABASE_EXPORT/SCHEMA/TABLE/TABLE                             Start: 28/07/2016 19:17:40, End: 28/07/2016 19:19:05
  DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA                        Start: 28/07/2016 19:13:41, End: 28/07/2016 19:14:12
  DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT    Start: 28/07/2016 19:19:05, End: 28/07/2016 19:19:06
  DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX                       Start: 28/07/2016 19:19:06, End: 28/07/2016 19:19:13
  DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT             Start: 28/07/2016 19:19:13, End: 28/07/2016 19:20:15
  DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Start: 28/07/2016 19:20:15, End: 28/07/2016 19:20:17
  DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT         Start: 28/07/2016 19:20:17, End: 28/07/2016 19:20:19
  DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS       Start: 28/07/2016 19:20:19, End: 28/07/2016 19:20:36

Worker 1: (DW00) Executing on DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
  16 out of 60 done (26.7%)
  working on table data STAGE8.T
Worker 2: (DW01) Work Waiting
We see all the steps that have been done with the start and end times and the current operation of the workers. Here Worker 1 is executing the step "export of table data" (DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA), has already done 16 out 60 tables (that is 26.7% of them) and is working on table STAGE8.T. Worker 2 is waiting for the Master to send it work. Master only works on metadata and send work to Workers.

Display information during an import of several schemas
We import the schemas exported above, the import command was:
impdp michel/michel dumpfile=stage1.dmp,stage2.dmp directory=my_dir full=y parallel=2
C:\>McDP michel/michel -w "MICHEL"."SYS_IMPORT_FULL_01"

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 28-JUIL.-2016 21:25:04

"MICHEL"."SYS_IMPORT_FULL_01" at 28/07/2016 21:25:04
IMPORT FULL, EXECUTING, Parallel 2, 1 attached sessions
Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 28/07/2016 21:09:56 - State: EXECUTING

Master work:
  DATABASE_EXPORT/SCHEMA/USER               Start: 28/07/2016 21:16:28, End: 28/07/2016 21:16:30
  DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Start: 28/07/2016 21:16:30, End: 28/07/2016 21:16:32
  DATABASE_EXPORT/SCHEMA/ROLE_GRANT         Start: 28/07/2016 21:16:32, End: 28/07/2016 21:16:34
  DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE       Start: 28/07/2016 21:16:34, End: 28/07/2016 21:16:36
  DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA   Start: 28/07/2016 21:16:36, End: 28/07/2016 21:16:38
  DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA     Start: 28/07/2016 21:16:38, End: 28/07/2016 21:16:40
  DATABASE_EXPORT/SCHEMA/TABLE/TABLE        Start: 28/07/2016 21:16:40, End: 28/07/2016 21:17:44
  DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA   Start: 28/07/2016 21:17:44, End: in progress...

All workers, tasks 100% done:
- DATABASE_EXPORT/SCHEMA/USER, 10 items:
    STAGE0.STAGE0,STAGE9.STAGE9,STAGE8.STAGE8,STAGE7.STAGE7,STAGE6.STAGE6,STAGE5.STAGE5,STAGE4.STAGE4,STAGE3.STAGE3,STA
    GE2.STAGE2,STAGE1.STAGE1
- DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT, 10 items:
    STAGE0.UNLIMITED TABLESPACE,STAGE9.UNLIMITED TABLESPACE,STAGE8.UNLIMITED TABLESPACE,STAGE7.UNLIMITED TABLESPACE,STA
    GE6.UNLIMITED TABLESPACE,STAGE5.UNLIMITED TABLESPACE,STAGE4.UNLIMITED TABLESPACE,STAGE3.UNLIMITED TABLESPACE,STAGE2
    .UNLIMITED TABLESPACE,STAGE1.UNLIMITED TABLESPACE
- DATABASE_EXPORT/SCHEMA/ROLE_GRANT, 20 items:
    STAGE0.CONNECT,STAGE9.RESOURCE,STAGE9.CONNECT,STAGE8.RESOURCE,STAGE8.CONNECT,STAGE7.RESOURCE,STAGE7.CONNECT,STAGE6.
    RESOURCE,STAGE6.CONNECT,STAGE5.RESOURCE,STAGE5.CONNECT,STAGE4.RESOURCE,STAGE4.CONNECT,STAGE3.RESOURCE,STAGE3.CONNEC
    T,STAGE2.RESOURCE,STAGE2.CONNECT,STAGE1.RESOURCE,STAGE1.CONNECT,STAGE0.RESOURCE
- DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE, 10 items
- DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA, 20 items:
    STAGE7.TEST,STAGE7.TS_I01,STAGE6.TEST,STAGE6.TS_I01,STAGE5.TEST,STAGE5.TS_I01,STAGE4.TEST,STAGE4.TS_I01,STAGE3.TEST
    ,STAGE3.TS_I01,STAGE2.TEST,STAGE2.TS_I01,STAGE1.TEST,STAGE1.TS_I01,STAGE0.TEST,STAGE0.TS_I01,STAGE9.TEST,STAGE9.TS_
    I01,STAGE8.TEST,STAGE8.TS_I01
- DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA, 10 items
- DATABASE_EXPORT/SCHEMA/TABLE/TABLE, 60 items:
    STAGE0.DEPT,STAGE9.T2,STAGE9.T,STAGE9.SALGRADE,STAGE8.EMP,STAGE8.DEPT,STAGE7.T2,STAGE7.T,STAGE7.SALGRADE,STAGE7.BON
    US,STAGE7.EMP,STAGE7.DEPT,STAGE6.T2,STAGE6.T,STAGE6.SALGRADE,STAGE6.BONUS,STAGE6.EMP,STAGE6.DEPT,STAGE5.T2,STAGE5.T
    ,STAGE5.SALGRADE,STAGE5.BONUS,STAGE5.EMP,STAGE5.DEPT,STAGE4.T2,STAGE4.T,STAGE4.SALGRADE,STAGE4.BONUS,STAGE9.BONU...

Worker 1: (DW00) Work Waiting
Worker 2: (DW01) Executing on DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
  47 out of 60 done (78.3%, 100.0% of dump)
  working on table data STAGE8.SALGRADE

Here, in addition to the information we have for an export, we also have the information about what all workers have already done: each step with the number of items and the first 3 lines of the items list.
Note: this later information is displayed only if McDP can create the type and function it needs for it, otherwise you only have the number of items without the list.

Few minutes later:
C:\>McDP michel/michel -w "MICHEL"."SYS_IMPORT_FULL_01"

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 28-JUIL.-2016 21:29:41

"MICHEL"."SYS_IMPORT_FULL_01" at 28/07/2016 21:29:41
IMPORT FULL, EXECUTING, Parallel 2, 1 attached sessions
Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 28/07/2016 21:09:56 - State: EXECUTING

Master work:
  DATABASE_EXPORT/SCHEMA/USER                                 Start: 28/07/2016 21:16:28, End: 28/07/2016 21:16:30
  DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT                   Start: 28/07/2016 21:16:30, End: 28/07/2016 21:16:32
  DATABASE_EXPORT/SCHEMA/ROLE_GRANT                           Start: 28/07/2016 21:16:32, End: 28/07/2016 21:16:34
  DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE                         Start: 28/07/2016 21:16:34, End: 28/07/2016 21:16:36
  DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA                     Start: 28/07/2016 21:16:36, End: 28/07/2016 21:16:38
  DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA                       Start: 28/07/2016 21:16:38, End: 28/07/2016 21:16:40
  DATABASE_EXPORT/SCHEMA/TABLE/TABLE                          Start: 28/07/2016 21:16:40, End: 28/07/2016 21:17:44
  DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA                     Start: 28/07/2016 21:17:44, End: in progress...
  DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Start: 28/07/2016 21:27:21, End: 28/07/2016 21:27:22
  DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX                    Start: 28/07/2016 21:27:22, End: 28/07/2016 21:27:25
  DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT          Start: 28/07/2016 21:29:29, End: in progress...

All workers, tasks 100% done:
- DATABASE_EXPORT/SCHEMA/USER, 10 items:
    STAGE0.STAGE0,STAGE9.STAGE9,STAGE8.STAGE8,STAGE7.STAGE7,STAGE6.STAGE6,STAGE5.STAGE5,STAGE4.STAGE4,STAGE3.STAGE3,STA
    GE2.STAGE2,STAGE1.STAGE1
- DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT, 10 items:
    STAGE0.UNLIMITED TABLESPACE,STAGE9.UNLIMITED TABLESPACE,STAGE8.UNLIMITED TABLESPACE,STAGE7.UNLIMITED TABLESPACE,STA
    GE6.UNLIMITED TABLESPACE,STAGE5.UNLIMITED TABLESPACE,STAGE4.UNLIMITED TABLESPACE,STAGE3.UNLIMITED TABLESPACE,STAGE2
    .UNLIMITED TABLESPACE,STAGE1.UNLIMITED TABLESPACE
- DATABASE_EXPORT/SCHEMA/ROLE_GRANT, 20 items:
    STAGE0.CONNECT,STAGE9.RESOURCE,STAGE9.CONNECT,STAGE8.RESOURCE,STAGE8.CONNECT,STAGE7.RESOURCE,STAGE7.CONNECT,STAGE6.
    RESOURCE,STAGE6.CONNECT,STAGE5.RESOURCE,STAGE5.CONNECT,STAGE4.RESOURCE,STAGE4.CONNECT,STAGE3.RESOURCE,STAGE3.CONNEC
    T,STAGE2.RESOURCE,STAGE2.CONNECT,STAGE1.RESOURCE,STAGE1.CONNECT,STAGE0.RESOURCE
- DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE, 10 items
- DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA, 20 items:
    STAGE7.TEST,STAGE7.TS_I01,STAGE6.TEST,STAGE6.TS_I01,STAGE5.TEST,STAGE5.TS_I01,STAGE4.TEST,STAGE4.TS_I01,STAGE3.TEST
    ,STAGE3.TS_I01,STAGE2.TEST,STAGE2.TS_I01,STAGE1.TEST,STAGE1.TS_I01,STAGE0.TEST,STAGE0.TS_I01,STAGE9.TEST,STAGE9.TS_
    I01,STAGE8.TEST,STAGE8.TS_I01
- DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA, 10 items
- DATABASE_EXPORT/SCHEMA/TABLE/TABLE, 60 items:
    STAGE0.DEPT,STAGE9.T2,STAGE9.T,STAGE9.SALGRADE,STAGE8.EMP,STAGE8.DEPT,STAGE7.T2,STAGE7.T,STAGE7.SALGRADE,STAGE7.BON
    US,STAGE7.EMP,STAGE7.DEPT,STAGE6.T2,STAGE6.T,STAGE6.SALGRADE,STAGE6.BONUS,STAGE6.EMP,STAGE6.DEPT,STAGE5.T2,STAGE5.T
    ,STAGE5.SALGRADE,STAGE5.BONUS,STAGE5.EMP,STAGE5.DEPT,STAGE4.T2,STAGE4.T,STAGE4.SALGRADE,STAGE4.BONUS,STAGE9.BONU...
- DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA, 110 items:
    STAGE0.DEPT,STAGE9.T2,STAGE9.T,STAGE9.SALGRADE,STAGE9.BONUS,STAGE9.EMP,STAGE9.DEPT,STAGE8.T2,STAGE8.T,STAGE8.SALGRA
    DE,STAGE8.BONUS,STAGE8.EMP,STAGE8.DEPT,STAGE7.T2,STAGE7.T,STAGE7.SALGRADE,STAGE7.BONUS,STAGE7.EMP,STAGE7.DEPT,STAGE
    6.T2,STAGE6.T,STAGE6.SALGRADE,STAGE6.BONUS,STAGE6.EMP,STAGE6.DEPT,STAGE5.T2,STAGE5.T,STAGE5.SALGRADE,STAGE5.BONU...
- DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT, 30 items
- DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX, 20 items:
    STAGE0.PK_DEPT,STAGE9.PK_EMP,STAGE9.PK_DEPT,STAGE8.PK_EMP,STAGE8.PK_DEPT,STAGE7.PK_EMP,STAGE7.PK_DEPT,STAGE6.PK_EMP
    ,STAGE6.PK_DEPT,STAGE5.PK_EMP,STAGE5.PK_DEPT,STAGE4.PK_EMP,STAGE4.PK_DEPT,STAGE3.PK_EMP,STAGE3.PK_DEPT,STAGE2.PK_EM
    P,STAGE2.PK_DEPT,STAGE1.PK_EMP,STAGE1.PK_DEPT,STAGE0.PK_EMP

Worker 1: (DW00) Executing on DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
  0 out of 40 done (0.0%)
  working on constraint STAGE0.SYS_C0056783 (STAGE0.T2) since 21:29:31
Worker 2: (DW01) Work Waiting

We see the Master has done a couple of new steps, workers have completed new tasks and are working on constraints now. We have the time the current work (building constraint STAGE0.SYS_C0056783) has started.

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655117 is a reply to message #655116] Mon, 22 August 2016 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

COMMAND command

But if you just want the command line of a Data Pump job it would be faster to execute the COMMAND command than to ask the user who launched the job or your memory, above all if it is an old stopped job. Smile

Its syntax is:
McDP [<logon>] { -cmd | --command } [<schema>.]<dp job> [[-opt] <display option>]
McDP [USERID=<logon>] COMMAND=COMMAND JOB=[<schema>.]<job name> [<display keyword>=<value>]
Note: If you don't provide the schema, it is yours. You must be a privileged user to see others jobs.
Note: The Data Pump job can be currently running or not.

Examples where MICHEL is a privileged account
Here are the commands of some of the Data Pump jobs we saw in the previous posts.

C:\>McDP michel/michel -cmd SYS_EXPORT_SCHEMA_03

McDP Utility by Michel Cadot: Version 2016.08.18

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 19-AOÛT-2016 09:29:17

Client command is:
expdp michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmppar1.dmp,dmppar2.dmp reuse_dumpfiles=true
You can notice that the password is not exposed.

C:\>McDP michel/michel -cmd SYS_EXPORT_TABLE_01

McDP Utility by Michel Cadot: Version 2016.08.18

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 19-AOÛT-2016 09:30:28

Client command is:
expdp michel/ tables=michel.t dumpfile=t.dmp keep_master=y reuse_dumpfiles=y

C:\>McDP michel/michel -cmd SYS_IMPORT_SCHEMA_01

McDP Utility by Michel Cadot: Version 2016.08.18

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 19-AOÛT-2016 09:31:34

Client command is:
impdp michel/ dumpfile=scott.dmp directory=my_dir schemas=scott remap_schema=scott:scott2 remap_tablespace=ts_d01:scott2

C:\>McDP michel/michel -cmd scott.SYS_EXPORT_SCHEMA_02

McDP Utility by Michel Cadot: Version 2016.08.18

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 19-AOÛT-2016 09:36:34

Client command is:
expdp scott/ dumpfile=scott.dmp reuse_dumpfiles=yes schemas=scott keep_master=yes directory=DATA_PUMP_DIR

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655118 is a reply to message #655117] Mon, 22 August 2016 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

STOP/SUSPEND and KILL commands

We saw how to get information about Data Pump jobs, now we start commands to manage them.
The first set of commands allow to stop running jobs.

Their syntax are:
McDP [<logon>] { -s | --stop | --suspend } [<schema>.]<dp job> [{ [--]keep | [--]drop [force] }] [[--]noprompt]
McDP [USERID=<logon>] COMMAND={STOP|SUSPEND} JOB=[<schema>.]<job name> [KEEP={YES|NO}] [DROP={YES|NO} [FORCE={YES|NO}]] [NOPROMPT={YES|NO}] 
McDP [<logon>] { -k | --kill } [<schema>.]<dp job> [{ [--]keep | [--]drop [force] }] [[--]noprompt]
McDP [USERID=<logon>] COMMAND=KILL JOB=[<schema>.]<job name> [KEEP={YES|NO}] [DROP={YES|NO} [FORCE={YES|NO}]] [NOPROMPT={YES|NO}] 
Note: If you don't provide the schema, it is yours. You must be a privileged user to stop others jobs.
Note: STOP and SUSPEND are synonymous.
  • "--keep" and "KEEP=YES" parameters mean the job Master table is kept; this is the default option.
  • "--drop", "DROP=YES" and "KEEP=NO" parameters mean the job Master table is dropped after the job is stopped.
  • "--force" and "FORCE=YES" parameters can only be used with "--drop" or "DROP=YES" and mean the Master table is dropped even if we can't properly stop the job and drop the Master table the using Data Pump engine (in this case a DROP TABLE is executed, PURGE option is not used). The default is "FORCE=NO".
The command starts to display few information about the Data Pump job and, unless you give the parameter "--noprompt" or "NOPROMPT=YES", it will prompt you to confirm the action before stopping the job.
The difference between STOP/SUSPEND and KILL is that the former let the workers complete their current task before stopping whereas the later aborts them. This means if you use KILL and the job is restartable and restarted (see next post), the current tasks at KILL time have to be completely reexecuted and may lead to errors like "ORA-00001: unique constraint violated".

Example where MICHEL is a privileged account
We will execute an import job we have already used in the previous posts: import exported SCOTT schema into SCOTT2 remapping the tablespace:
impdp michel/michel dumpfile=scott.dmp directory=my_dir schemas=scott remap_schema=scott:scott2 remap_tablespace=ts_d01:scott2

We now stop the job:
C:\>McDP michel/michel -s SYS_IMPORT_SCHEMA_04

McDP Utility by Michel Cadot: Version 2016.08.20

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 20-AOÛT-2016 15:26:02

MICHEL.SYS_IMPORT_SCHEMA_04: EXECUTING
  Start time: 20/08/2016 15:26:01
  Mode      : SCHEMA
  State     : EXECUTING
  Done      : 0% (0K/19345K)
  Errors    : 0
  Parallel  : 1
  Worker   1: (DW00) EXECUTING SCOTT2.DEPT
Confirm stop job (y(es)/[n(o)])? y
Done!

The session who launched the import sees:
C:\>impdp michel/michel dumpfile=scott.dmp directory=my_dir schemas=scott remap_schema=scott:scott2 remap_tablespace=ts_d01:scott2

Import: Release 11.2.0.4.0 - Production on Sam. Août 20 15:26:00 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MICHEL"."SYS_IMPORT_SCHEMA_04" successfully loaded/unloaded
Starting "MICHEL"."SYS_IMPORT_SCHEMA_04":  michel/******** dumpfile=scott.dmp directory=my_dir schemas=scott remap_schema=scott:scott2 remap_tablespace=ts_d01:scott2
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT2" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT2"."T"                                9.435 MB 1090001 rows
Job "MICHEL"."SYS_IMPORT_SCHEMA_04" stopped by user request at Sam. Août 20 15:26:05 2016 elapsed 0 00:00:04

The current complete status of the job is now:
C:\>McDP michel/michel -d SYS_IMPORT_SCHEMA_04

McDP Utility by Michel Cadot: Version 2016.08.20

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 20-AOÛT-2016 15:27:22

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_IMPORT_SCHEMA_04           IMPORT    SCHEMA     NOT RUNNING     0    0    0

  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 20/08/2016 15:26:01 - State: STOPPED
  Message: Job "MICHEL"."SYS_IMPORT_SCHEMA_04" stopped by user request at Sam. Août 20 15:26:05 2016 elapsed 0 00:00:04
  Client command: michel/ dumpfile=scott.dmp directory=my_dir schemas=scott remap_schema=scott:scott2 remap_tablespace=t
s_d01:scott2
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Command Line Client: 1
  Data Access Method: AUTOMATIC
  Data Options: 0
  Include Metadata: 1
  Keep Master: 0
  Log File Directory: MY_DIR
  Log File Name: import.log
  Master Only: 0
  Metrics: 0
  Schema Expr:  IN ('SCOTT')
  Schema List: 'SCOTT'
  Skip Unusable Indexes: 1
  Streams Configuration: 1
  Table Exists Action: SKIP
  Trace: 0
  Dump file(s):
    C:\scott.dmp
  Export parameters:
    Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 01/08/2016 18:44:44 - State: EXECUTING
    Client command: michel/ dumpfile=scott.dmp directory=my_dir schemas=scott reuse_dumpfiles=yes
    Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC
      _CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_S
      ORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT=
      'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BI
      NARY' NLS_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
    Command Line Client: 1
    Compression: METADATA_ONLY
    Data Access Method: AUTOMATIC
    Data Options: 0
    Estimate: BLOCKS
    Include Metadata: 1
    Keep Master: 0
    Log File Directory: MY_DIR
    Log File Name: export.log
    Metrics: 0
    Schema Expr:  IN ('SCOTT')
    Schema List: 'SCOTT'
    Table Consistency: 0
    Trace: 0
    User Metadata: 1
    Dump file(s):
      C:\scott.dmp

  Dump file(s):
    C:\scott.dmp
  Start time: 20/08/2016 15:27:22
  Done      : 49.9% (9662K/19345K)
  Errors    : 0
  Parallel  : 1
  Worker   1: (DW00) UNDEFINED

C:\>McDP michel/michel -w SYS_IMPORT_SCHEMA_04

McDP Utility by Michel Cadot: Version 2016.08.20

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 20-AOÛT-2016 15:28:22

"MICHEL"."SYS_IMPORT_SCHEMA_04" at 20/08/2016 15:28:22
IMPORT SCHEMA, IDLING, Parallel 1, 0 attached sessions
Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 20/08/2016 15:28:12 - State: IDLING

Master work:
  SCHEMA_EXPORT/USER                      Start: 20/08/2016 15:26:02, End: 20/08/2016 15:26:02
  SCHEMA_EXPORT/ROLE_GRANT                Start: 20/08/2016 15:26:02, End: 20/08/2016 15:26:02
  SCHEMA_EXPORT/DEFAULT_ROLE              Start: 20/08/2016 15:26:02, End: 20/08/2016 15:26:02
  SCHEMA_EXPORT/TABLESPACE_QUOTA          Start: 20/08/2016 15:26:02, End: 20/08/2016 15:26:02
  SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Start: 20/08/2016 15:26:02, End: 20/08/2016 15:26:02
  SCHEMA_EXPORT/TABLE/TABLE               Start: 20/08/2016 15:26:02, End: 20/08/2016 15:26:03
  SCHEMA_EXPORT/TABLE/TABLE_DATA          Start: 20/08/2016 15:26:03, End: in progress...

All workers, tasks 100% done:
- SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA, 1 item
- SCHEMA_EXPORT/TABLE/TABLE, 6 items:
    SCOTT2.DEPT,SCOTT2.T2,SCOTT2.T,SCOTT2.SALGRADE,SCOTT2.BONUS,SCOTT2.EMP

Worker 1: (DW00) Undefined

[Updated on: Mon, 22 August 2016 15:53]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655119 is a reply to message #655118] Mon, 22 August 2016 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

CONTINUE/RESTART commands

Now that we saw how to stop a job, can we restart it? Yes, using the RESTART or CONTINUE command.

Its syntax is:
McDP [<logon>] { -r | --restart | --continue } [<schema>.]<dp job> [<service name>] [[--]skip_current] [[-opt] service_name=<service name>]
McDP [USERID=<logon>] COMMAND={CONTINUE|RESTART} JOB=[<schema>.]<job name> [SERVICE_NAME=<service name>] [SKIP_CURRENT={YES|NO}]
Note: If you don't provide the schema, it is yours. You must be a privileged user to restart others jobs.
Note: CONTINUE and RESTART are synonymous.
  • "<service_name>" and "SERVICE_NAME=<service_name>" parameters allows you to restart the job, in a RAC environment, in an instance supporting this specific service.
  • "--skip_current" and "SKIP_CURRENT=YES" parameters, only valid for an import, allow to restart a Data Pump skipping the steps the workers were executing when the job was killed or failed. This is useful when you want to skip a task which is taking too much time or resources or continuously fails.
Example where MICHEL is a privileged account
We will restart the import job we stopped in the previous post.
C:\>McDP michel/michel -r SYS_IMPORT_SCHEMA_04

McDP Utility by Michel Cadot: Version 2016.08.20

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 20-AOÛT-2016 15:36:01

MICHEL.SYS_IMPORT_SCHEMA_04: IDLING
Trying to restart the job...
MICHEL.SYS_IMPORT_SCHEMA_04: EXECUTING
  Start time: 20/08/2016 15:36:02
  Mode      : SCHEMA
  State     : EXECUTING
  Done      : 49.9% (9662K/19345K)
  Errors    : 0
  Parallel  : 1
  Worker   1: UNDEFINED
McDP restarts the job and gives its status afterwards.

We can also check it with the DISPLAY and WHERE commands.
C:\>McDP michel/michel -d SYS_IMPORT_SCHEMA_04

McDP Utility by Michel Cadot: Version 2016.08.20

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 20-AOÛT-2016 15:36:13

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_IMPORT_SCHEMA_04           IMPORT    SCHEMA     EXECUTING       1    2    0

  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 20/08/2016 15:36:02 - State: EXECUTING
  Client command: michel/ dumpfile=scott.dmp directory=my_dir schemas=scott remap_schema=scott:scott2 remap_tablespace=t
s_d01:scott2
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Command Line Client: 1
  Data Access Method: AUTOMATIC
  Data Options: 0
  Include Metadata: 1
  Keep Master: 0
  Log File Directory: MY_DIR
  Log File Name: import.log
  Master Only: 0
  Metrics: 0
  Schema Expr:  IN ('SCOTT')
  Schema List: 'SCOTT'
  Skip Unusable Indexes: 1
  Streams Configuration: 1
  Table Exists Action: SKIP
  Trace: 0
  Dump file(s):
    C:\scott.dmp
  Export parameters:
    Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 01/08/2016 18:44:44 - State: EXECUTING
    Client command: michel/ dumpfile=scott.dmp directory=my_dir schemas=scott reuse_dumpfiles=yes
    Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC
      _CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_S
      ORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT=
      'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BI
      NARY' NLS_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
    Command Line Client: 1
    Compression: METADATA_ONLY
    Data Access Method: AUTOMATIC
    Data Options: 0
    Estimate: BLOCKS
    Include Metadata: 1
    Keep Master: 0
    Log File Directory: MY_DIR
    Log File Name: export.log
    Metrics: 0
    Schema Expr:  IN ('SCOTT')
    Schema List: 'SCOTT'
    Table Consistency: 0
    Trace: 0
    User Metadata: 1
    Dump file(s):
      C:\scott.dmp

  Dump file(s):
    C:\scott.dmp
  Start time: 20/08/2016 15:36:02
  Done      : 100% (19345K/19345K)
  Errors    : 0
  Parallel  : 1
  Worker   1: (DW00) EXECUTING SCOTT2.PK_DEPT

Data Pump sessions
==================

Owner                          Job                            Session type   Sid           Serial Status
------------------------------ ------------------------------ -------------- ------------ ------- --------
MICHEL                         SYS_IMPORT_SCHEMA_04           MASTER         1:138              3 ACTIVE
MICHEL                         SYS_IMPORT_SCHEMA_04           WORKER         1:9               21 ACTIVE

Data Pump waiting sessions
==========================

Sid          Status    Suspend time            Timeout Resumable cause
SQL text
------------ --------- -------------------- ---------- -----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
1:138        NORMAL                               7200 MICHEL.SYS_IMPORT_SCHEMA_04
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
1:9          NORMAL                               7200 MICHEL.SYS_IMPORT_SCHEMA_04.1
ALTER TABLE "SCOTT2"


Data Pump long operations
=========================

--> None.
Note the "Att? 0" (and "0 attached sessions" below), the Data Pump job is executing without no one to follow it, no impdp running for it, just the server part is running.

C:\>McDP michel/michel -w SYS_IMPORT_SCHEMA_04

McDP Utility by Michel Cadot: Version 2016.08.20

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 20-AOÛT-2016 15:36:17

"MICHEL"."SYS_IMPORT_SCHEMA_04" at 20/08/2016 15:36:17
IMPORT SCHEMA, EXECUTING, Parallel 1, 0 attached sessions
Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 20/08/2016 15:36:02 - State: EXECUTING

Master work:
  SCHEMA_EXPORT/USER                                    Start: 20/08/2016 15:26:02, End: 20/08/2016 15:26:02
  SCHEMA_EXPORT/ROLE_GRANT                              Start: 20/08/2016 15:26:02, End: 20/08/2016 15:26:02
  SCHEMA_EXPORT/DEFAULT_ROLE                            Start: 20/08/2016 15:26:02, End: 20/08/2016 15:26:02
  SCHEMA_EXPORT/TABLESPACE_QUOTA                        Start: 20/08/2016 15:26:02, End: 20/08/2016 15:26:02
  SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA               Start: 20/08/2016 15:26:02, End: 20/08/2016 15:26:02
  SCHEMA_EXPORT/TABLE/TABLE                             Start: 20/08/2016 15:26:02, End: 20/08/2016 15:26:03
  SCHEMA_EXPORT/TABLE/TABLE_DATA                        Start: 20/08/2016 15:26:03, End: 20/08/2016 15:36:03
  SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT    Start: 20/08/2016 15:36:05, End: 20/08/2016 15:36:05
  SCHEMA_EXPORT/TABLE/INDEX/INDEX                       Start: 20/08/2016 15:36:05, End: 20/08/2016 15:36:05
  SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT             Start: 20/08/2016 15:36:05, End: 20/08/2016 15:36:16
  SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Start: 20/08/2016 15:36:16, End: in progress...

All workers, tasks 100% done:
- SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA, 1 item
- SCHEMA_EXPORT/TABLE/TABLE, 6 items:
    SCOTT2.DEPT,SCOTT2.T2,SCOTT2.T,SCOTT2.SALGRADE,SCOTT2.BONUS,SCOTT2.EMP
- SCHEMA_EXPORT/TABLE/INDEX/INDEX, 2 items:
    SCOTT2.PK_DEPT,SCOTT2.PK_EMP
- SCHEMA_EXPORT/TABLE/TABLE_DATA, 6 items:
    SCOTT2.T,SCOTT2.BONUS,SCOTT2.SALGRADE,SCOTT2.EMP,SCOTT2.DEPT,SCOTT2.T2
- SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT, 4 items:
    SCOTT2.PK_DEPT,SCOTT2.SYS_C0056500,SCOTT2.SYS_C0056499,SCOTT2.PK_EMP

Worker 1: (DW00) Executing on SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  working on index statistics  (SCOTT2.PK_DEPT) since 15:36:16

Note: The "Start time" is actually the "restart time", this explains that some steps were executed before the "start time". Smile

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655120 is a reply to message #655119] Mon, 22 August 2016 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ALTER/MODIFY commands

Sometimes we would like to modify some parameters of a Data Pump job, for instance parallelism to decrease it because the job is taking too much resources or to increase it to speed up the job. This is possible with McDP using the ALTER/MODIFY command.

Its syntax is:
McDP [<logon>] { -a | --alter | -m | --modify } [<schema>.]<dp job> [[-opt] <execution option>]
McDP [USERID=<logon>] COMMAND={ALTER|MODIFY} JOB=[<schema>.]<job name> [<execution option>=<value>]
Note: If you don't provide the schema, it is yours. You must be a privileged user to alter others jobs.
Note: ALTER and MODIFY are synonymous.

Currently the execution options you can give are the following ones:
ADD_FILE=[<file size>#][<directory>:]<file>[,...] to add dumpfiles to dumpfile set (export only); 
                                                    <file size> is the size the file will have, 
                                                    <directory> is the Oracle directory for the file.
PARALLEL=<n>                                      to change the degree of parallelism of the job.
REUSE_DUMPFILES={YES|NO}                          to change the setting of the same parameter in expdp program 
                                                    (export only and only if the program is still in definition step).
Note: if you give a size, the file is preallocated with this size, if you don't give any size, the file can grow (from 0) without any limit (but an OS one).
Note: you can use the "DIRECTORY=<Oracle directory>" parameter to specify a default directory for the new files.

Example where MICHEL is a privileged account
We will launch an export and then modify its parameters, the export command is the following one:
expdp michel/michel schemas=michel dumpfile=mic1.dmp,mic2.dmp directory=my_dir parallel=2

Check the export is running:
C:\>McDP michel/michel -d SYS_EXPORT_SCHEMA_05

McDP Utility by Michel Cadot: Version 2016.08.20

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 20-AOÛT-2016 16:37:41

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_EXPORT_SCHEMA_05           EXPORT    SCHEMA     EXECUTING       2    4    1

  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 20/08/2016 16:37:05 - State: EXECUTING
  Client command: michel/ schemas=michel dumpfile=mic1.dmp,mic2.dmp directory=my_dir parallel=2
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Command Line Client: 1
  Compression: METADATA_ONLY
  Data Access Method: AUTOMATIC
  Data Options: 0
  Estimate: BLOCKS
  Include Metadata: 1
  Keep Master: 0
  Log File Directory: MY_DIR
  Log File Name: export.log
  Metrics: 0
  Schema Expr:  IN ('MICHEL')
  Schema List: 'MICHEL'
  Table Consistency: 0
  Trace: 0
  User Metadata: 1
  Dump file(s):
    C:\MIC1.DMP
    C:\MIC2.DMP

  Dump file(s):
    C:\MIC1.DMP 0MB
    C:\MIC2.DMP 58MB
  Start time: 20/08/2016 16:37:05
  Done      : 17.7% (59636K/336820K)
  Errors    : 0
  Parallel  : 2
  Worker   1: (DW00) EXECUTING MICHEL.
  Worker   2: (DW01) EXECUTING MICHEL.SAV

Data Pump sessions
==================

Owner                          Job                            Session type   Sid           Serial Status
------------------------------ ------------------------------ -------------- ------------ ------- --------
MICHEL                         SYS_EXPORT_SCHEMA_05           DBMS_DATAPUMP  1:151            115 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           MASTER         1:150             91 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:26              35 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:143            207 ACTIVE

Data Pump waiting sessions
==========================

Sid          Status    Suspend time            Timeout Resumable cause
SQL text
------------ --------- -------------------- ---------- -----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
1:143        NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.2
 BEGIN    SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_05', 'MICHEL', 0);  END;
1:150        NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
1:26         NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.1
SELECT /*+rule*/ KU$.OBJ_NUM FROM SYS.KU$_PROCOBJ_OBJNUM_VIEW KU$ WHERE  KU$.OWNER_NAME  IN ('MICHEL') AND  NOT EXISTS (
SELECT 1 FROM  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='PROCOBJ' AND A.NAME=KU$.NAME AND A.SCHEMA=KU$.OWNER_NAME) AND NOT EX
ISTS (SELECT 1 FROM  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND A.NAME=KU$.OWNER_NAME)


Data Pump long operations
=========================

--> None.
We see we have 2 workers, 2 dump files and 58MB was already written into the second one.

Now we alter some parameters:
- change the REUSE_DUMPFILES to YES
- add 2 files in 2 different directories, one in MYDIR with a size of 100MB and one in DATA_PUMP_DIR with an unlimited size
- change the parallelism to 4
C:\>McDP michel/michel -m SYS_EXPORT_SCHEMA_05 reuse_dumpfiles            ^
More? -opt parallel=4 -opt add_file=100M#mydir:new_file.dmp,new_file2.dmp

McDP Utility by Michel Cadot: Version 2016.08.20

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 20-AOÛT-2016 16:37:49

MICHEL.SYS_EXPORT_SCHEMA_05: EXECUTING
Setting REUSE_DUMFILES to YES...   ORA-39004: invalid state
  ORA-31624: A job cannot be modified after it has started.
Adding new_file.dmp in MYDIR (size 100M)... done
Adding new_file2.dmp in DATA_PUMP_DIR (size unlimited)... done
Setting PARALLEL to 4... done
Done with errors.
We see we can't change REUSE_DUMFILES as the job is already executing.
The second file goes to DATA_PUMP_DIR directory because it is the default directory if none is given with the file name or with a DIRECTORY parameter.

Let's check this is what actually happened:
C:\>McDP michel/michel -d SYS_EXPORT_SCHEMA_05

McDP Utility by Michel Cadot: Version 2016.08.20

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 20-AOÛT-2016 16:38:02

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_EXPORT_SCHEMA_05           EXPORT    SCHEMA     EXECUTING       4    6    1

  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 20/08/2016 16:37:05 - State: EXECUTING
  Client command: michel/ schemas=michel dumpfile=mic1.dmp,mic2.dmp directory=my_dir parallel=2
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Command Line Client: 1
  Compression: METADATA_ONLY
  Data Access Method: AUTOMATIC
  Data Options: 0
  Estimate: BLOCKS
  Include Metadata: 1
  Keep Master: 0
  Log File Directory: MY_DIR
  Log File Name: export.log
  Metrics: 0
  Schema Expr:  IN ('MICHEL')
  Schema List: 'MICHEL'
  Table Consistency: 0
  Trace: 0
  User Metadata: 1
  Dump file(s):
    C:\MIC1.DMP
    C:\MIC2.DMP
    E:\ORACLE\ADMIN\MIKB2\LOG\NEW_FILE.DMP
    E:\ORACLE\ADMIN\MIKB2\DPDUMP\NEW_FILE2.DMP

  Dump file(s):
    C:\MIC1.DMP 0MB
    C:\MIC2.DMP 163MB
    E:\ORACLE\ADMIN\MIKB2\LOG\NEW_FILE.DMP 100MB
    E:\ORACLE\ADMIN\MIKB2\DPDUMP\NEW_FILE2.DMP 11MB
  Start time: 20/08/2016 16:37:05
  Done      : 74.6% (194046K/260286K)
  Errors    : 0
  Parallel  : 4
  Worker   1: (DW00) EXECUTING MICHEL.
  Worker   2: (DW01) EXECUTING MICHEL.SYS_SQL_FILE_FULL_01
  Worker   3: (DW02) EXECUTING MICHEL.OBJ
  Worker   4: (DW03) EXECUTING MICHEL.SYS_SQL_FILE_FULL_04

Data Pump sessions
==================

Owner                          Job                            Session type   Sid           Serial Status
------------------------------ ------------------------------ -------------- ------------ ------- --------
MICHEL                         SYS_EXPORT_SCHEMA_05           DBMS_DATAPUMP  1:151            115 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           MASTER         1:150             91 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:144             79 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:143            207 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:153            113 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:26              35 ACTIVE

Data Pump waiting sessions
==========================

Sid          Status    Suspend time            Timeout Resumable cause
SQL text
------------ --------- -------------------- ---------- -----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
1:143        NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.2
 BEGIN    SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_05', 'MICHEL', 0);  END;
1:144        NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.3
 BEGIN    SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_05', 'MICHEL', 0);  END;
1:150        NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
1:153        NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.4
 BEGIN    SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_05', 'MICHEL', 0);  END;
1:26         NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.1
BEGIN :1 :="SYS"."DBMS_REFRESH_EXP_SITES"."SCHEMA_INFO_EXP"(:2,:3,:4,:5,:6); END;


Data Pump long operations
=========================

--> None.
We see we have now 4 workers and 4 dump files.

30 seconds later:
C:\>McDP michel/michel -d SYS_EXPORT_SCHEMA_05

McDP Utility by Michel Cadot: Version 2016.08.20

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 20-AOÛT-2016 16:38:37

Owner                          Job                            Operation Mode       State        Par. Ses. Att.
------------------------------ ------------------------------ --------- ---------- ------------ ---- ---- ----
MICHEL                         SYS_EXPORT_SCHEMA_05           EXPORT    SCHEMA     EXECUTING       4    6    1

  Server: MIKB2.micserver, platform: Microsoft Windows IA (32-bit) - Start: 20/08/2016 16:37:05 - State: EXECUTING
  Client command: michel/ schemas=michel dumpfile=mic1.dmp,mic2.dmp directory=my_dir parallel=2
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Command Line Client: 1
  Compression: METADATA_ONLY
  Data Access Method: AUTOMATIC
  Data Options: 0
  Estimate: BLOCKS
  Include Metadata: 1
  Keep Master: 0
  Log File Directory: MY_DIR
  Log File Name: export.log
  Metrics: 0
  Schema Expr:  IN ('MICHEL')
  Schema List: 'MICHEL'
  Table Consistency: 0
  Trace: 0
  User Metadata: 1
  Dump file(s):
    C:\MIC1.DMP
    C:\MIC2.DMP
    E:\ORACLE\ADMIN\MIKB2\LOG\NEW_FILE.DMP
    E:\ORACLE\ADMIN\MIKB2\DPDUMP\NEW_FILE2.DMP

  Dump file(s):
    C:\MIC1.DMP 0MB
    C:\MIC2.DMP 171MB
    E:\ORACLE\ADMIN\MIKB2\LOG\NEW_FILE.DMP 100MB
    E:\ORACLE\ADMIN\MIKB2\DPDUMP\NEW_FILE2.DMP 29MB
  Start time: 20/08/2016 16:37:05
  Done      : 95.7% (222986K/233098K)
  Errors    : 0
  Parallel  : 4
  Worker   1: (DW00) EXECUTING MICHEL.SPKG
  Worker   2: (DW01) WORK WAITING
  Worker   3: (DW02) EXECUTING MICHEL.VEOLIA_MONITOR2
  Worker   4: (DW03) WORK WAITING

Data Pump sessions
==================

Owner                          Job                            Session type   Sid           Serial Status
------------------------------ ------------------------------ -------------- ------------ ------- --------
MICHEL                         SYS_EXPORT_SCHEMA_05           DBMS_DATAPUMP  1:151            115 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           MASTER         1:150             91 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:144             79 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:143            207 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:153            113 ACTIVE
MICHEL                         SYS_EXPORT_SCHEMA_05           WORKER         1:26              35 ACTIVE

Data Pump waiting sessions
==========================

Sid          Status    Suspend time            Timeout Resumable cause
SQL text
------------ --------- -------------------- ---------- -----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
1:143        NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.2
BEGIN :1 := sys.kupc$que_int.transceive_int(:2, :3, :4, :5, :6); END;
1:144        NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.3
 BEGIN    SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_05', 'MICHEL', 0);  END;
1:150        NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
1:153        NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.4
BEGIN :1 := sys.kupc$que_int.transceive_int(:2, :3, :4, :5, :6); END;
1:26         NORMAL                               7200 MICHEL.SYS_EXPORT_SCHEMA_05.1
SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TYPE_T', '7')), KU$.OBJ_NUM ,KU$.SCHEMA_OBJ.NAME ,KU$.S
CHEMA_OBJ.NAME ,'TYPE' ,KU$.SCHEMA_OBJ.OWNER_NAME FROM SYS.KU$_TYPE_VIEW KU$,TABLE(DBMS_METADATA.FETCH_SORTED_OBJNUMS(20
0001)) KU1$ WHERE     KU$.OBJ_NUM =KU1$.NUM1 ORDER BY KU1$.NUM2


Data Pump long operations
=========================

Ins Sid   CSid  Start    Remain Operation...
--- ----- ----- -------- ------ ----------------------------------------------------------------------------------------
1   150   0     16:37:34     36 SYS_EXPORT_SCHEMA_05: EXPORT : 218 out of 343 MB done
And so on...

[Updated on: Mon, 22 August 2016 15:57]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655121 is a reply to message #655120] Mon, 22 August 2016 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

DISPLAY file command

But what if now I have not a Data Pump job but just a dump file. What can I know to what Data Pump job it belongs? Or something else?
Yes, you can with the McDP DISPLAY command.

Its syntax is:
McDP [<logon>] { -d | --display } {<directory>:<file> | <full path file>} [[-opt] <display option>]
McDP [USERID=<logon>] COMMAND=DISPLAY DUMPFILE={<dir>:<file> | <full path file>} [LINESIZE=<n>]
Note: You must be give either a full path file name or a file name with the corresponding Oracle directory.

Example where MICHEL is a privileged account

What can I get on the c:\MC1.DMP and c:\MC2.DMP (or MY_DIR:MC2.DMP) files generated in the previous post?
C:\>McDP michel/michel -d c:\mic1.dmp

McDP Utility by Michel Cadot: Version 2016.08.21

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 21-AOÛT-2016 09:35:47

(MY_DIR)c:/mic1.dmp
  OS file size: 1356KB
  File Version: 3.1
  Database version: 11.02.00.00.00
  Master table present: No
  Flags: 2
  GUID: 65DC154908FA4E45A9648AECB07C4E86
  Character set: 178 = WE8MSWIN1252
  File number: 1
  Export job name: "MICHEL"."SYS_EXPORT_SCHEMA_05"
  Platform: IBMPC/WIN_NT-8.1.0
  Instance: mikb2
  Language: WE8MSWIN1252
  Creation date: Sat Aug 20 16:37:24 2016
  Block size: 4096
  Metadata compression: NONE
  Data compression: No
  Metadata encrypted: No
  Data Encrypted: No
  Columns Encrypted: No
  Encryption mode: NONE

C:\>McDP michel/michel -d my_dir:mic2.dmp

McDP Utility by Michel Cadot: Version 2016.08.21

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 21-AOÛT-2016 09:47:49

(MY_DIR)/mic2.dmp
  OS file size: 175300KB
  File Version: 3.1
  Database version: 11.02.00.00.00
  Master table present: No
  Flags: 2
  GUID: 65DC154908FA4E45A9648AECB07C4E86
  Character set: 178 = WE8MSWIN1252
  File number: 2
  Export job name: "MICHEL"."SYS_EXPORT_SCHEMA_05"
  Platform: IBMPC/WIN_NT-8.1.0
  Instance: mikb2
  Language: WE8MSWIN1252
  Creation date: Sat Aug 20 16:37:25 2016
  Block size: 4096
  Metadata compression: NONE
  Data compression: No
  Metadata encrypted: No
  Data Encrypted: No
  Columns Encrypted: No
  Encryption mode: NONE

Among other information we can see that these files have been generated by a Data Pump job named "MICHEL"."SYS_EXPORT_SCHEMA_05" on the instance "mikb2" on "Sat Aug 20 16:37:24 2016" and they are files number 1 and 2 of the dump.

Now have a look at the first added dump file in the previous post: mydir:new_file.dmp.
C:\>McDP michel/michel -d mydir:new_file.dmp

McDP Utility by Michel Cadot: Version 2016.08.21

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 21-AOÛT-2016 09:59:59

(MYDIR)/new_file.dmp
  OS file size: 24896KB
  File Version: 3.1
  Database version: 11.02.00.00.00
  Master table present: Yes
  Flags: 2
  GUID: 65DC154908FA4E45A9648AECB07C4E86
  Character set: 178 = WE8MSWIN1252
  File number: 3
  Export job name: "MICHEL"."SYS_EXPORT_SCHEMA_05"
  Platform: IBMPC/WIN_NT-8.1.0
  Instance: mikb2
  Language: WE8MSWIN1252
  Creation date: Sat Aug 20 16:39:58 2016
  Block size: 4096
  Metadata compression: NONE
  Data compression: No
  Metadata encrypted: No
  Data Encrypted: No
  Columns Encrypted: No
  Encryption mode: NONE
  Master piece count: 1
  Master piece number: 1

We can see that this file is number 3 and it contains the Master table of the Data Pump job ("Master table present: Yes"); this Master table is "cut" in 1 piece ("Master piece count") and this is piece number 1 ("Master piece number").

[Updated on: Mon, 22 August 2016 16:03]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655122 is a reply to message #655121] Mon, 22 August 2016 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CONTENT command

Now I want to know what's inside the dump, more I want the DDL, more I want not all but some of these DDL and I want to apply some transformation on them.
This is possible with McDP using the CONTENT command.

Its syntax is:
McDP [<logon>] { -c | --content } [<directory>:]<file>[,...] [[--]show] [{[--]keep|[--]drop}]                      ^
                                  [[-opt] <filter/transform/display option>]
McDP [USERID=<logon>] COMMAND=CONTENT DUMPFILES=[<dir>:]<file>[,...] [SHOW={YES|NO} [{KEEP={YES|NO}|DROP={YES|NO}] ^
                                      [<option>=<value>]...
Note: McDP generates a SQL file containing the desired DDL.

  • "-show" and "SHOW=YES" parameters indicate you want McDP displays on the screen the content of the result file.

  • "-keep" and "KEEP=YES" parameters indicate you want to keep the result file.

  • "-drop" and "DROP=YES" parameters indicate you want McDP removes the result file.
The default values are: SHOW=YES, KEEP=NO and DROP=YES but if you choose KEEP=YES or DROP=NO default value for SHOW becomes NO.
Note: If you choose to give DROP=YES option, SHOW is forced to YES.
Note: The filters and transformations you can give are the same ones you can give using impdp program (also adding some syntax this later does not accept). A list is given in the help at top of this topic, the detailed descriptions can be read in Data Pump documentation within Oracle Database Utilities book.

Examples where MICHEL is a privileged account

We want to get in a file all the DDL from the export of SCOTT schema we have seen in a previous post, excluding the statistics:
C:\>McDP michel/michel -c my_dir:SCOTT.DMP -keep -opt EXCLUDE=STATISTICS

McDP Utility by Michel Cadot: Version 2016.08.21

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 21-AOÛT-2016 10:30:21

21/08/2016 10:30:21.484 Getting the content of following file(s):
    my_dir:SCOTT.DMP
21/08/2016 10:30:21.484 Defining the job
21/08/2016 10:30:22.687 Adding file: MY_DIR/SCOTT.DMP
21/08/2016 10:30:23.281 Result file will be McDPsql1160_20160821103021.sql
21/08/2016 10:30:24.437 Data Pump job started successfully
21/08/2016 10:30:24.531 Master table "MICHEL"."SYS_SQL_FILE_FULL_07" successfully loaded/unloaded
21/08/2016 10:30:24.562 Starting "MICHEL"."SYS_SQL_FILE_FULL_07":
  MICHEL: Display content of file(s):
    my_dir:SCOTT.DMP
  SQL FILE: McDPsql1160_20160821103021.sql
  EXCLUDE_PATH_EXPR='STATISTICS'
21/08/2016 10:30:24.890 Processing object type SCHEMA_EXPORT/USER
21/08/2016 10:30:25.890 Processing object type SCHEMA_EXPORT/ROLE_GRANT
21/08/2016 10:30:26.015 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
21/08/2016 10:30:26.109 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
21/08/2016 10:30:26.250 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
21/08/2016 10:30:26.625 Processing object type SCHEMA_EXPORT/TABLE/TABLE
21/08/2016 10:30:27.343 Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
21/08/2016 10:30:27.546 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
21/08/2016 10:30:27.796 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
21/08/2016 10:30:28.062 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
21/08/2016 10:30:29.062 Job "MICHEL"."SYS_SQL_FILE_FULL_07" successfully completed at Dim. Août 21 10:30:28 2016 elapsed
 0 00:00:06
21/08/2016 10:30:29.140 End of data pump job, status COMPLETED
21/08/2016 10:30:29.375 Result file DATA_PUMP_DIR/McDPsql1160_20160821103021.sql kept
The result is in file DATA_PUMP_DIR/McDPsql1160_20160821103021.sql.

Now we have a dump of schema "MICHEL" and we want tables "XXX" and "TTTT" but only if they are in tablespace "ORAFAQ" and we want to remap the schema to "SCOTT" and tablespace to "TS_D01", without the statistics.
C:\>McDP michel/michel -c 'DMPPAR1.DMP','DMPPAR2.DMP' -show   ^
More? -opt directory=my_dir                                   ^
More? -opt INCLUDE=TABLE                                      ^
More? -opt EXCLUDE=STATISTICS                                 ^
More? -opt NAME=table:\"'XXX','TTTT'\"                        ^
More? -opt client_command=----------------------------------- ^
More? -opt remap_schema=MICHEL:SCOTT                          ^
More? -opt remap_tablespace=ORAFAQ:TS_D01                     ^
More? -opt tablespace=table:"'ORAFAQ'"

McDP Utility by Michel Cadot: Version 2016.08.21

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 21-AOÛT-2016 10:56:48

21/08/2016 10:56:48.468 Getting the content of following file(s):
    MY_DIR:'DMPPAR1.DMP'
    MY_DIR:'DMPPAR2.DMP'
21/08/2016 10:56:48.468 Defining the job
21/08/2016 10:56:49.765 Adding file: MY_DIR/DMPPAR1.DMP
21/08/2016 10:56:49.890 Adding file: MY_DIR/DMPPAR2.DMP
21/08/2016 10:56:50.140 Result file will be McDPsql5144_20160821105648.sql
21/08/2016 10:56:51.781 Data Pump job started successfully
21/08/2016 10:56:51.828 Master table "MICHEL"."SYS_SQL_FILE_FULL_07" successfully loaded/unloaded
21/08/2016 10:56:51.843 Starting "MICHEL"."SYS_SQL_FILE_FULL_07":
  MICHEL: Display content of file(s):
    MY_DIR:'DMPPAR1.DMP'
    MY_DIR:'DMPPAR2.DMP'
  SQL FILE: McDPsql5144_20160821105648.sql
  EXCLUDE_PATH_EXPR='STATISTICS'
  INCLUDE_PATH_EXPR='TABLE'
  NAME_LIST=TABLE:"'XXX','TTTT'"
  REMAP_SCHEMA=MICHEL:SCOTT
  REMAP_TABLESPACE=ORAFAQ:TS_D01
  TABLESPACE_LIST=TABLE:"'ORAFAQ'"
-----------------------------------
21/08/2016 10:56:51.921 Processing object type SCHEMA_EXPORT/TABLE/TABLE
21/08/2016 10:56:52.593 Job "MICHEL"."SYS_SQL_FILE_FULL_07" successfully completed at Dim. Août 21 10:56:52 2016 elapsed
 0 00:00:03
21/08/2016 10:56:52.609 End of data pump job, status COMPLETED
21/08/2016 10:56:52.625 Content of data pump files satisfying the options:
========================================================================================================================
-- CONNECT MICHEL
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT"."XXX"
   ( "ID" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_D01" ;
========================================================================================================================
21/08/2016 10:56:52.625 Result file MY_DIR/McDPsql5144_20160821105648.sql removed
Table "TTTT" exists in the dump but is not in the proper tablespace so it is skipped.
Note that McDP records in the job it creates the parameters you gave, translating them into valid Data Pump expressions, and displays them so you can check there were no problems and this is what you want. This starts with "<USER>: Display content of file(s):"

Now we want, from the same dump, all the procedures starting with "P" (but procedure "PRINT_TABLE") from source edition "ORA$BASE" remapping them in schema "SCOTT" and in edition "TOTO":
C:\>McDP michel/michel -c 'DMPPAR1.DMP','DMPPAR2.DMP' -show   ^
More? -opt directory=my_dir                                   ^
More? -opt INCLUDE=PROCEDURE                                  ^
More? -opt NAME=procedure:"LIKE 'P%'"                         ^
More? -opt EXCLUDE=PROCEDURE:"'PRINT_TABLE'"                  ^
More? -opt client_command=----------------------------------- ^
More? -opt remap_schema=MICHEL:SCOTT                          ^
More? -opt source_edition=ORA$BASE                            ^
More? -opt target_edition=TOTO

McDP Utility by Michel Cadot: Version 2016.08.21

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 21-AOÛT-2016 10:59:09

21/08/2016 10:59:09.671 Getting the content of following file(s):
    MY_DIR:'DMPPAR1.DMP'
    MY_DIR:'DMPPAR2.DMP'
21/08/2016 10:59:09.671 Defining the job
21/08/2016 10:59:10.906 Adding file: MY_DIR/DMPPAR1.DMP
21/08/2016 10:59:10.968 Adding file: MY_DIR/DMPPAR2.DMP
21/08/2016 10:59:11.156 Result file will be McDPsql2956_20160821105909.sql
21/08/2016 10:59:12.796 Data Pump job started successfully
21/08/2016 10:59:12.859 Master table "MICHEL"."SYS_SQL_FILE_FULL_07" successfully loaded/unloaded
21/08/2016 10:59:12.859 Starting "MICHEL"."SYS_SQL_FILE_FULL_07":
  MICHEL: Display content of file(s):
    MY_DIR:'DMPPAR1.DMP'
    MY_DIR:'DMPPAR2.DMP'
  SQL FILE: McDPsql2956_20160821105909.sql
  NAME_EXPR=PROCEDURE:"NOT IN ('PRINT_TABLE')"
  INCLUDE_PATH_EXPR='PROCEDURE'
  NAME_EXPR=PROCEDURE:"LIKE 'P%'"
  REMAP_SCHEMA=MICHEL:SCOTT
  SOURCE_EDITION=ORA$BASE
  TARGET_EDITION=TOTO
-----------------------------------
21/08/2016 10:59:12.984 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
21/08/2016 10:59:13.328 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
21/08/2016 10:59:14.218 Job "MICHEL"."SYS_SQL_FILE_FULL_07" successfully completed at Dim. Août 21 10:59:13 2016 elapsed
 0 00:00:03
21/08/2016 10:59:14.218 End of data pump job, status COMPLETED
21/08/2016 10:59:14.343 Content of data pump files satisfying the options:
========================================================================================================================
-- CONNECT MICHEL
ALTER SESSION SET EDITION = "TOTO";
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/PROCEDURE/PROCEDURE
-- CONNECT SCOTT
CREATE procedure p1 (i int)
is
  type birthdates_d is table of date index by pls_integer;
  birthdays  birthdates_d;
  index1 pls_integer;
  l_date date;
begin
  birthdays (1):=to_date ('20-mar-1972', 'dd-mon-yyyy','nls_date_language=american');
  birthdays(2) := to_date ('1-oct-1986', 'dd-mon-yyyy','nls_date_language=american');
  dbms_output.put_line(birthdays(2));

  begin
l_date :=birthdays(i);
end;
end;
/
CREATE PROCEDURE PROC1
AS
BEGIN
  $IF $plsql_code_type = 'INTERPRETED' $THEN
    DBMS_OUTPUT.PUT_LINE('INTERPRETED');
  $END
  NULL;
END;
/
-- new object type path: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

ALTER PROCEDURE "SCOTT"."PROC1"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

 REUSE SETTINGS TIMESTAMP '2016-04-01 18:31:25'
/

ALTER PROCEDURE "SCOTT"."P1"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

 REUSE SETTINGS TIMESTAMP '2016-04-04 10:37:31'
/
========================================================================================================================
21/08/2016 10:59:14.375 Result file MY_DIR/McDPsql2956_20160821105909.sql removed
Note: McDP supports to have both INCLUDE and EXCLUDE parameters; if you try the same thing with impdp you will get the error:
UDI-00011: parameter include is incompatible with parameter exclude
Note: You must have ALL the files of the dump otherwise McDP reports a Data Pump engine error.
If a file contains the Data Pump job Master table:
C:\>McDP michel/michel -c mydir:new_file.dmp

McDP Utility by Michel Cadot: Version 2016.08.21

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 21-AOÛT-2016 10:00:13

21/08/2016 10:00:13.109 Getting the content of following file(s):
    mydir:new_file.dmp
21/08/2016 10:00:13.109 Defining the job
21/08/2016 10:00:15.062 Adding file: MYDIR/new_file.dmp
21/08/2016 10:00:15.140 Result file will be McDPsql5104_20160821100013.sql
21/08/2016 10:00:20.687 Data Pump job started with info:
  ORA-39002: invalid operation
  ORA-39059: dump file set is incomplete
21/08/2016 10:00:20.687 Data Pump job not started: ORA-39002: invalid operation
*** CONTENT: Unable to start the job.

If no files contain the Master table of the Data Pump job:
C:\>McDP michel/michel -c my_dir:mic2.dmp

McDP Utility by Michel Cadot: Version 2016.08.21

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 21-AOÛT-2016 09:58:44

21/08/2016 09:58:44.843 Getting the content of following file(s):
    my_dir:mic2.dmp
21/08/2016 09:58:45.093 Defining the job
21/08/2016 09:58:56.359 Adding file: MY_DIR/mic2.dmp
21/08/2016 09:58:56.750 Result file will be McDPsql4640_20160821095844.sql
21/08/2016 09:58:56.921 Data Pump job started with info:
  ORA-39002: invalid operation
  ORA-39059: dump file set is incomplete
  ORA-39246: cannot locate master table within provided dump files
21/08/2016 09:58:56.921 Data Pump job not started: ORA-39002: invalid operation
*** CONTENT: Unable to start the job.

[Updated on: Wed, 12 July 2017 12:14]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655123 is a reply to message #655122] Mon, 22 August 2016 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

DDL command

In the same vein than the previous command, CONTENT, McDP allows you to get the DDL of all or part of a database, with the transformation you want, using the DDL command.

Its syntax is:
McDP [<logon>] { -ddl | --ddl } [<directory>:]<file> <dblink> <type> [<object>[,<object>...]]                   ^
                                [[--]show] [{[--]keep|[--]drop}] [[-opt] <filter/transform/display option>]
McDP [USERID=<logon>] COMMAND=DDL SQLFILE=[<directory>:]<file> NETWORK_LINK=<dblink> TYPE=<type>                ^
                                  [OBJECT=<object>[,<object>...]] [SHOW={YES|NO} [{KEEP={YES|NO}|DROP={YES|NO}] ^
                                  [<option>=<value>]...
Note: McDP generates a SQL file containing the desired DDL.
  • "[<directory>:]<file>" and "SQLFILE=[<directory>:]<file>" parameters give the SQL result file.
  • "<dblink>" and "NETWORK_LINK=<dblink>" parameters give the database link through which the Data Pump engine will inquire the "remote" database, even if you want DDL from the database on which you are connected and with the same user, you must provide a database link, this is a restriction of the Data Pump engine.
  • "<type>" and "TYPE=<type>" parameters indicate the Data Pump job type, it could be "FULL", "SCHEMA" or "TABLE", just like for an export using "expdp" program you must provide one (and only one) of the parameters "FULL", "SCHEMAS" or "TABLES", this is the mode you'll find in USER_DATAPUMP_JOBS.JOB_MODE column.
  • "[<object>[,<object>...]]" and "OBJECT=<object>[,<object>...]" parameters indicate a possible object list in the Data Pump job type you mentioned.
  • "-show" and "SHOW=YES" parameters indicate you want McDP displays on the screen the content of the result file.
  • "-keep" and "KEEP=YES" parameters indicate you want to keep the result file.
  • "-drop" and "DROP=YES" parameters indicate you want McDP removes the result file.
The default values are: SHOW=NO, KEEP=YES and DROP=NO.
Note: If you choose to give DROP=YES option, SHOW is forced to YES.
Note: The filters and transformations you can give are the same ones you can give using impdp program (also adding some syntax this later does not accept). A list is given in the help at top of this topic, the detailed descriptions can be read in Data Pump documentation within Oracle Database Utilities book.

Examples where MICHEL is a privileged account

The first example will extract the CREATE USER statement for MICHEL account as well as its roles and system privileges, the result will be displayed and stored in "michel.sql" file:
C:\>McDP michel/michel -ddl my_dir:michel.sql mylink schema michel -opt include=user,role_grant,system_grant show=y

McDP Utility by Michel Cadot: Version 2016.08.21

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 21-AOÛT-2016 17:01:47

21/08/2016 17:01:47.265 Getting DDL from database link MYLINK
21/08/2016 17:01:47.562 Defining the job
21/08/2016 17:01:56.203 Add the SQL file
21/08/2016 17:01:56.203 Add the object list
21/08/2016 17:01:56.734 Result file will be MY_DIR/michel.sql
21/08/2016 17:01:58.843 Data Pump job started successfully
21/08/2016 17:01:58.906 Starting "MICHEL"."SYS_SQL_FILE_SCHEMA_02":
  MICHEL: get DDL from MYLINK for 'MICHEL'
  SCHEMA_LIST="'MICHEL'"
  SQL FILE: michel.sql
  INCLUDE_PATH_EXPR='USER'
  INCLUDE_PATH_EXPR='ROLE_GRANT'
  INCLUDE_PATH_EXPR='SYSTEM_GRANT'
21/08/2016 17:02:07.359 Processing object type SCHEMA_EXPORT/USER
21/08/2016 17:02:07.968 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
21/08/2016 17:02:08.546 Processing object type SCHEMA_EXPORT/ROLE_GRANT
21/08/2016 17:02:10.875 Job "MICHEL"."SYS_SQL_FILE_SCHEMA_02" successfully completed at Dim. Août 21 17:02:08 2016 elaps
ed 0 00:00:19
21/08/2016 17:02:10.890 End of data pump job, status COMPLETED
21/08/2016 17:02:11.234 DDL for the asked objects are:
========================================================================================================================
-- CONNECT MICHEL
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "MICHEL" IDENTIFIED BY VALUES 'S:BA778276560DC659148B46111FFFA6B96D94BEEC19B7B4BFE98AD7EAA0F3;A16FD5BFC24EA
1EC'
      DEFAULT TABLESPACE "TS_D01"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT DEBUG ANY PROCEDURE TO "MICHEL";
GRANT SELECT ANY DICTIONARY TO "MICHEL";
GRANT EXECUTE ANY LIBRARY TO "MICHEL";
GRANT CREATE ANY LIBRARY TO "MICHEL";
GRANT CREATE TYPE TO "MICHEL";
GRANT CREATE TRIGGER TO "MICHEL";
GRANT CREATE PROCEDURE TO "MICHEL";
GRANT CREATE SEQUENCE TO "MICHEL";
GRANT CREATE VIEW TO "MICHEL";
GRANT CREATE SYNONYM TO "MICHEL";
GRANT CREATE TABLE TO "MICHEL";
GRANT UNLIMITED TABLESPACE TO "MICHEL";
GRANT CREATE SESSION TO "MICHEL";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "MICHEL";
 GRANT "SELECT_CATALOG_ROLE" TO "MICHEL";
 GRANT "MESDVD_LECTURE" TO "MICHEL" WITH ADMIN OPTION;
 GRANT "MESDVD_TOUT" TO "MICHEL" WITH ADMIN OPTION;
 GRANT "SECURITY_SSN" TO "MICHEL" WITH ADMIN OPTION;
========================================================================================================================
21/08/2016 17:02:11.250 Result file MY_DIR/michel.sql kept
As for the CONTENT command, McDP displays the translation of the options you gave into Data Pump engine syntax; this the part starting with "<user>: get DDL from <dblink>".
We will see another way, faster, later, in a couple of posts: the command USER.

The second example will extract SCOTT account definition and all its tables but "T%" ans 'SYS%' ones, without the statistics and the storage parameters, the result will be displayed and stored in "scott.sql" file:
C:\>McDP michel/michel -ddl my_dir:scott.sql mylink schema scott                            ^
More? -opt exclude=statistics exclude="table:\"like 'T%'\"" exclude="table:\"like 'SYS%'\"" ^
More? -opt segment_attributes=no storage=no show=y

McDP Utility by Michel Cadot: Version 2016.08.21

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 21-AOÛT-2016 17:23:46

21/08/2016 17:23:46.812 Getting DDL from database link MYLINK
21/08/2016 17:23:46.812 Defining the job
21/08/2016 17:23:53.718 Add the SQL file
21/08/2016 17:23:53.781 Add the object list
21/08/2016 17:23:54.031 Result file will be MY_DIR/scott.sql
21/08/2016 17:23:55.140 Data Pump job started successfully
21/08/2016 17:23:55.187 Starting "MICHEL"."SYS_SQL_FILE_SCHEMA_02":
  MICHEL: get DDL from MYLINK for 'SCOTT'
  SCHEMA_LIST="'SCOTT'"
  SQL FILE: scott.sql
  EXCLUDE_PATH_EXPR='STATISTICS'
  NAME_EXPR=TABLE:"NOT like 'T%'"
  NAME_EXPR=TABLE:"NOT like 'SYS%'"
  SEGMENT_ATTRIBUTES/%=0
  STORAGE/%=0
21/08/2016 17:24:01.359 Processing object type SCHEMA_EXPORT/USER
21/08/2016 17:24:01.609 Processing object type SCHEMA_EXPORT/ROLE_GRANT
21/08/2016 17:24:01.734 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
21/08/2016 17:24:01.953 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
21/08/2016 17:24:04.203 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
21/08/2016 17:24:11.375 Processing object type SCHEMA_EXPORT/TABLE/TABLE
21/08/2016 17:24:12.625 Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
21/08/2016 17:24:15.812 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
21/08/2016 17:24:17.937 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
21/08/2016 17:24:19.703 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
21/08/2016 17:24:23.531 Job "MICHEL"."SYS_SQL_FILE_SCHEMA_02" successfully completed at Dim. Août 21 17:24:21 2016 elaps
ed 0 00:00:33
21/08/2016 17:24:23.531 End of data pump job, status COMPLETED
21/08/2016 17:24:23.562 DDL for the asked objects are:
========================================================================================================================
-- CONNECT MICHEL
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:85BB00A6574C42152E6390E07358309D10A4E2024B9EF7493B06E9A5A725;F894844C34402B
67'
      DEFAULT TABLESPACE "TS_D01"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "CONNECT" TO "SCOTT";
 GRANT "RESOURCE" TO "SCOTT";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "SCOTT" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/TABLESPACE_QUOTA
DECLARE
  TEMP_COUNT NUMBER;
  SQLSTR VARCHAR2(200);
BEGIN
  SQLSTR := 'ALTER USER "SCOTT" QUOTA UNLIMITED ON "TS_I01"';
  EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -30041 THEN
      SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
              WHERE TABLESPACE_NAME = ''TS_I01'' AND CONTENTS = ''TEMPORARY''';
      EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
      IF TEMP_COUNT = 1 THEN RETURN;
      ELSE RAISE;
      END IF;
    ELSE
      RAISE;
    END IF;
END;
/
DECLARE
  TEMP_COUNT NUMBER;
  SQLSTR VARCHAR2(200);
BEGIN
  SQLSTR := 'ALTER USER "SCOTT" QUOTA UNLIMITED ON "TS_D01"';
  EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -30041 THEN
      SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
              WHERE TABLESPACE_NAME = ''TS_D01'' AND CONTENTS = ''TEMPORARY''';
      EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
      IF TEMP_COUNT = 1 THEN RETURN;
      ELSE RAISE;
      END IF;
    ELSE
      RAISE;
    END IF;
END;
/
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT SCOTT

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'MIKB2.ILLU
VATAR', inst_scn=>'74097081');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT MICHEL
CREATE TABLE "SCOTT"."DEPT"
   ( "DEPTNO" NUMBER(2,0),
 "DNAME" VARCHAR2(14 CHAR),
 "LOC" VARCHAR2(13 CHAR)
   ) ;
CREATE TABLE "SCOTT"."EMP"
   ( "EMPNO" NUMBER(4,0),
 "ENAME" VARCHAR2(10 CHAR),
 "JOB" VARCHAR2(9 CHAR),
 "MGR" NUMBER(4,0),
 "HIREDATE" DATE,
 "SAL" NUMBER(7,2),
 "COMM" NUMBER(7,2),
 "DEPTNO" NUMBER(2,0)
   ) ;
CREATE TABLE "SCOTT"."BONUS"
   ( "ENAME" VARCHAR2(10 CHAR),
 "JOB" VARCHAR2(9 CHAR),
 "SAL" NUMBER,
 "COMM" NUMBER
   ) ;
CREATE TABLE "SCOTT"."SALGRADE"
   ( "GRADE" NUMBER,
 "LOSAL" NUMBER,
 "HISAL" NUMBER
   ) ;
-- new object type path: SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
-- CONNECT SCOTT
GRANT SELECT ON "SCOTT"."EMP" TO "R";
GRANT SELECT ON "SCOTT"."EMP" TO "MICHEL";
GRANT UPDATE ("EMPNO") ON "SCOTT"."EMP" TO "MICHEL";
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
  ;

  ALTER INDEX "SCOTT"."PK_DEPT" NOPARALLEL;
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  ;

  ALTER INDEX "SCOTT"."PK_EMP" NOPARALLEL;
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT MICHEL
ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX "SCOTT"."PK_DEPT"  ENABLE;
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX "SCOTT"."PK_EMP"  ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
   REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;
========================================================================================================================
21/08/2016 17:24:23.609 Result file MY_DIR/scott.sql kept

[Updated on: Thu, 25 August 2016 04:49]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655124 is a reply to message #655123] Mon, 22 August 2016 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

FOLLOW command

Now that we have seen how to get information about Data Pump jobs, active or stopped, a new point reaches: it would be nice to see what a running Data Pump job is doing when it is doing it, either because the expdp or impdp which launched it is no more there or because, as a DBA, you see a Data Pump job running and you are concerned about it, is it possible?
The answer is: Yes, with McDP using FOLLOW command.

Its syntax is:
McDP [<logon>] { -f | --follow } [<schema>.]<dp job> [[-opt] <display option>]
McDP [USERID=<logon>] COMMAND=FOLLOW JOB=[<schema>.]<dp job> [<timeout param>=<value>] [LINESIZE=<n>]
Note: If you don't provide the schema, it is yours. You must be a privileged user to alter others jobs.
Timeout parameters are (see a detailed explanation at the bottom of this post):
  • TIMEOUT: Number of minutes without any new message before a new status message is displayed by McDP (default is 5 minutes).
  • TIMEOUTMAX: Maximum value for time-out (default is NULL which is unlimited, 0 is also unlimited).
  • TIMEOUTMF: Multiplying factor to apply to TIMEOUT when TIMEOUTNB times-out have been raised.
  • TIMEOUTNB: Number of consecutive times-out before a multiplying factor is applied (default is 5).
Example where MICHEL is a privileged account

We will launch an import using the following command:
impdp michel/michel full=y remap_schema=michel:michel2 dumpfile=mic.dmp directory=my_dir transform=oid:n
then we will follow it using McDP in another window.

The complete "impdp" session is the following one:
impdp session

Now in another window we will alternatively follow the job using the FOLLOW command and get the detailed steps using the WHERE command we saw above.
C:\>McDP michel/michel -f SYS_IMPORT_FULL_01 

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 10-AOÛT-2016 18:01:46

10/08/2016 18:01:46.937 Following "MICHEL"."SYS_IMPORT_FULL_01":
10/08/2016 18:01:46.956 IMPORT FULL, DEFINING, Parallel 1, 1 attached sessions

  Serveur: MIKB2.micserver - Start: 10/08/2016 18:01:43 - State: DEFINING
  Client command: michel/ full=y remap_schema=michel:michel2 dumpfile=mic.dmp directory=my_dir transform=oid:n
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Client Command: michel/ full=y remap_schema=michel:michel2 dumpfile=mic.dmp directory=my_dir transform=oid:n
  Command Line Client: 1
  Data Access Method: AUTOMATIC
  Data Options: 0
  Include Metadata: 1
  Keep Master: 0
  Log File Directory: MY_DIR
  Log File Name: import.log
  Master Only: 0
  Metrics: 0
  Reuse Datafiles: 0
  Skip Unusable Indexes: 1
  Streams Configuration: 1
  Table Exists Action: SKIP
  Trace: 0
  Dump file(s):
    C:\mic.dmp
  Export parameters:

  Dump file(s):
    C:\mic.dmp
  Start time: 10/08/2016 18:01:43
  Done      : 0% (0/0)
  Errors    : 0
  Parallel  : 1
  Worker   1: (DW00) WORK WAITING

10/08/2016 18:01:48.156 Master table "MICHEL"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
10/08/2016 18:01:48.171 Starting "MICHEL"."SYS_IMPORT_FULL_01":  michel/******** full=y remap_schema=michel:michel2 dump
file=mic.dmp directory=my_dir transform=oid:n
10/08/2016 18:01:48.171 Processing object type SCHEMA_EXPORT/USER
10/08/2016 18:01:48.546 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
10/08/2016 18:01:48.640 Processing object type SCHEMA_EXPORT/ROLE_GRANT
10/08/2016 18:01:48.703 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
10/08/2016 18:01:48.796 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
10/08/2016 18:01:48.890 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
10/08/2016 18:01:49.234 Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
10/08/2016 18:01:50.218 Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
10/08/2016 18:02:08.453 Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
10/08/2016 18:02:08.796 Processing object type SCHEMA_EXPORT/DB_LINK
Terminating on signal SIGINT(2)
We can see that the first thing McDP FOLLOW command does is to display information about the Data Pump job and its status. Here the job is still in DEFINING step which means it is doing its initialization and did not actually start the import process (the worker is waiting for the master to send it some work). The "Starting" message appears a second later as you can see.
(Ignore the "Terminating on signal SIGINT(2)" message, it is there because I aborted McDP session using Control-C.)

After terminating the FOLLOW command, we launch a WHERE one to see where the Data Pump job is:
C:\>McDP michel/michel -w SYS_IMPORT_FULL_01

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 10-AOÛT-2016 18:02:13

"MICHEL"."SYS_IMPORT_FULL_01" at 10/08/2016 18:02:13
IMPORT FULL, EXECUTING, Parallel 1, 1 attached sessions
Server: MIKB2.micserver - Start: 10/08/2016 18:01:43 - State: EXECUTING

Master work:
  SCHEMA_EXPORT/USER                                Start: 10/08/2016 18:01:48, End: 10/08/2016 18:01:48
  SCHEMA_EXPORT/SYSTEM_GRANT                        Start: 10/08/2016 18:01:48, End: 10/08/2016 18:01:48
  SCHEMA_EXPORT/ROLE_GRANT                          Start: 10/08/2016 18:01:48, End: 10/08/2016 18:01:48
  SCHEMA_EXPORT/DEFAULT_ROLE                        Start: 10/08/2016 18:01:48, End: 10/08/2016 18:01:48
  SCHEMA_EXPORT/TABLESPACE_QUOTA                    Start: 10/08/2016 18:01:48, End: 10/08/2016 18:01:48
  SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA           Start: 10/08/2016 18:01:48, End: 10/08/2016 18:01:49
  SCHEMA_EXPORT/SYNONYM/SYNONYM                     Start: 10/08/2016 18:01:49, End: 10/08/2016 18:01:50
  SCHEMA_EXPORT/TYPE/TYPE_SPEC                      Start: 10/08/2016 18:01:50, End: 10/08/2016 18:02:08
  SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT Start: 10/08/2016 18:02:08, End: 10/08/2016 18:02:08
  SCHEMA_EXPORT/DB_LINK                             Start: 10/08/2016 18:02:08, End: in progress...

All workers, tasks 100% done:
- SCHEMA_EXPORT/USER, 1 item:
    MICHEL2.MICHEL2
- SCHEMA_EXPORT/SYSTEM_GRANT, 13 items:
    MICHEL2.DEBUG ANY PROCEDURE,MICHEL2.CREATE SESSION,MICHEL2.UNLIMITED TABLESPACE,MICHEL2.CREATE TABLE,MICHEL2.CREATE
     SYNONYM,MICHEL2.CREATE VIEW,MICHEL2.CREATE SEQUENCE,MICHEL2.CREATE PROCEDURE,MICHEL2.CREATE TRIGGER,MICHEL2.CREATE
     TYPE,MICHEL2.CREATE ANY LIBRARY,MICHEL2.EXECUTE ANY LIBRARY,MICHEL2.SELECT ANY DICTIONARY
- SCHEMA_EXPORT/ROLE_GRANT, 5 items:
    MICHEL2.DBA,MICHEL2.SECURITY_SSN,MICHEL2.MESDVD_TOUT,MICHEL2.MESDVD_LECTURE,MICHEL2.SELECT_CATALOG_ROLE
- SCHEMA_EXPORT/DEFAULT_ROLE, 1 item
- SCHEMA_EXPORT/TABLESPACE_QUOTA, 2 items:
    MICHEL2.TS_I01,MICHEL2.TS_D01
- SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA, 1 item
- SCHEMA_EXPORT/SYNONYM/SYNONYM, 6 items:
    MICHEL2.S1,MICHEL2.SPKG,MICHEL2.ST,MICHEL2.SQ,MICHEL2.S2,MICHEL2.S
- SCHEMA_EXPORT/TYPE/TYPE_SPEC, 64 items:
    MICHEL2.RECORD1,MICHEL2.TRANS_DTL,MICHEL2.STRAGG5_TYPE,MICHEL2.T_TYP_TRANS_DTL,MICHEL2.STRAGG3_TYPE,MICHEL2.RECORDS
    2,MICHEL2.McDPWhereType,MICHEL2.CPSCOPTLIST,MICHEL2.DDLOPTLIST,MICHEL2.DPOPTLIST,MICHEL2.STRAGG5_ARRAY,MICHEL2.SUB_
    TRANS_DTL,MICHEL2.USER_REC_TT,MICHEL2.TESTER,MICHEL2.ADDR_VARRAY,MICHEL2.ADDR_TYPE,MICHEL2.EMPLOYEE,MICHEL2.LT_T...
- SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT, 6 items

Worker 1: (DW00) Executing on SCHEMA_EXPORT/DB_LINK
  0 out of 4 done (0.0%)
  working on db link MICHEL2.MIK8.micserver since 18:02:08
We recognize in the steps that have already been done those we have followed using the FOLLOW command (we have in addition the details of these steps).

Then we start a new FOLLOW session:
C:\>McDP michel/michel -f SYS_IMPORT_FULL_01 

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 10-AOÛT-2016 18:02:18

10/08/2016 18:02:18.281 Following "MICHEL"."SYS_IMPORT_FULL_01":
10/08/2016 18:02:18.283 IMPORT FULL, EXECUTING, Parallel 1, 1 attached sessions

  Serveur: MIKB2.micserver - Start: 10/08/2016 18:01:43 - State: EXECUTING
  Client command: michel/ full=y remap_schema=michel:michel2 dumpfile=mic.dmp directory=my_dir transform=oid:n
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Client Command: michel/ full=y remap_schema=michel:michel2 dumpfile=mic.dmp directory=my_dir transform=oid:n
  Command Line Client: 1
  Data Access Method: AUTOMATIC
  Data Options: 0
  Include Metadata: 1
  Keep Master: 0
  Log File Directory: MY_DIR
  Log File Name: import.log
  Master Only: 0
  Metrics: 0
  Reuse Datafiles: 0
  Skip Unusable Indexes: 1
  Streams Configuration: 1
  Table Exists Action: SKIP
  Trace: 0
  Dump file(s):
    C:\mic.dmp
  Export parameters:
    Serveur: MIKB2.micserver - Start: 10/08/2016 16:27:03 - State: EXECUTING
  Client command: michel/ schemas=michel dumpfile=mic.dmp directory=my_dir metrics=yes
    Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC
      _CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_S
      ORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT=
      'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BI
      NARY' NLS_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
    Client Command: michel/ schemas=michel dumpfile=mic.dmp directory=my_dir metrics=yes
    Command Line Client: 1
    Compression: METADATA_ONLY
    Data Access Method: AUTOMATIC
    Data Options: 0
    Estimate: BLOCKS
    Include Metadata: 1
    Keep Master: 0
    Log File Directory: MY_DIR
    Log File Name: export.log
    Metrics: 1
    Table Consistency: 0
    Trace: 0
    User Metadata: 1
    Schema List: 'MICHEL'
    Schema Expr:  IN ('MICHEL')
    Dump file(s):
      C:\MIC.DMP

  Dump file(s):
    C:\mic.dmp
  Start time: 10/08/2016 18:01:43
  Done      : 0% (0K/294618K)
  Errors    : 0
  Parallel  : 1
  Worker   1: (DW00) EXECUTING MICHEL2.PARTITIONED_TABLE

10/08/2016 18:02:31.437 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
10/08/2016 18:02:59.218 . . imported "MICHEL2"."T1"                              66.76 MB 9999999 rows
10/08/2016 18:02:59.218 23 percent done
Terminating on signal SIGINT(2)
Compare to the "impdp" log, we can see that McDP displays how much job has been done as it advances (line "percent done").

New WHERE status:
C:\>McDP michel/michel -w SYS_IMPORT_FULL_01

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 10-AOÛT-2016 18:03:11

"MICHEL"."SYS_IMPORT_FULL_01" at 10/08/2016 18:03:11
IMPORT FULL, EXECUTING, Parallel 1, 1 attached sessions
Server: MIKB2.micserver - Start: 10/08/2016 18:01:43 - State: EXECUTING

Master work:
  SCHEMA_EXPORT/USER                                Start: 10/08/2016 18:01:48, End: 10/08/2016 18:01:48
  SCHEMA_EXPORT/SYSTEM_GRANT                        Start: 10/08/2016 18:01:48, End: 10/08/2016 18:01:48
  SCHEMA_EXPORT/ROLE_GRANT                          Start: 10/08/2016 18:01:48, End: 10/08/2016 18:01:48
  SCHEMA_EXPORT/DEFAULT_ROLE                        Start: 10/08/2016 18:01:48, End: 10/08/2016 18:01:48
  SCHEMA_EXPORT/TABLESPACE_QUOTA                    Start: 10/08/2016 18:01:48, End: 10/08/2016 18:01:48
  SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA           Start: 10/08/2016 18:01:48, End: 10/08/2016 18:01:49
  SCHEMA_EXPORT/SYNONYM/SYNONYM                     Start: 10/08/2016 18:01:49, End: 10/08/2016 18:01:50
  SCHEMA_EXPORT/TYPE/TYPE_SPEC                      Start: 10/08/2016 18:01:50, End: 10/08/2016 18:02:08
  SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT Start: 10/08/2016 18:02:08, End: 10/08/2016 18:02:08
  SCHEMA_EXPORT/DB_LINK                             Start: 10/08/2016 18:02:08, End: 10/08/2016 18:02:17
  SCHEMA_EXPORT/SEQUENCE/SEQUENCE                   Start: 10/08/2016 18:02:17, End: 10/08/2016 18:02:18
  SCHEMA_EXPORT/TABLE/TABLE                         Start: 10/08/2016 18:02:18, End: 10/08/2016 18:02:31
  SCHEMA_EXPORT/TABLE/TABLE_DATA                    Start: 10/08/2016 18:02:31, End: in progress...

All workers, tasks 100% done:
- SCHEMA_EXPORT/USER, 1 item:
    MICHEL2.MICHEL2
- SCHEMA_EXPORT/SYSTEM_GRANT, 13 items:
    MICHEL2.DEBUG ANY PROCEDURE,MICHEL2.CREATE SESSION,MICHEL2.UNLIMITED TABLESPACE,MICHEL2.CREATE TABLE,MICHEL2.CREATE
     SYNONYM,MICHEL2.CREATE VIEW,MICHEL2.CREATE SEQUENCE,MICHEL2.CREATE PROCEDURE,MICHEL2.CREATE TRIGGER,MICHEL2.CREATE
     TYPE,MICHEL2.CREATE ANY LIBRARY,MICHEL2.EXECUTE ANY LIBRARY,MICHEL2.SELECT ANY DICTIONARY
- SCHEMA_EXPORT/ROLE_GRANT, 5 items:
    MICHEL2.DBA,MICHEL2.SECURITY_SSN,MICHEL2.MESDVD_TOUT,MICHEL2.MESDVD_LECTURE,MICHEL2.SELECT_CATALOG_ROLE
- SCHEMA_EXPORT/DEFAULT_ROLE, 1 item
- SCHEMA_EXPORT/TABLESPACE_QUOTA, 2 items:
    MICHEL2.TS_I01,MICHEL2.TS_D01
- SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA, 1 item
- SCHEMA_EXPORT/SYNONYM/SYNONYM, 6 items:
    MICHEL2.S1,MICHEL2.SPKG,MICHEL2.ST,MICHEL2.SQ,MICHEL2.S2,MICHEL2.S
- SCHEMA_EXPORT/TYPE/TYPE_SPEC, 64 items:
    MICHEL2.RECORD1,MICHEL2.TRANS_DTL,MICHEL2.STRAGG5_TYPE,MICHEL2.T_TYP_TRANS_DTL,MICHEL2.STRAGG3_TYPE,MICHEL2.RECORDS
    2,MICHEL2.McDPWhereType,MICHEL2.CPSCOPTLIST,MICHEL2.DDLOPTLIST,MICHEL2.DPOPTLIST,MICHEL2.STRAGG5_ARRAY,MICHEL2.SUB_
    TRANS_DTL,MICHEL2.USER_REC_TT,MICHEL2.TESTER,MICHEL2.ADDR_VARRAY,MICHEL2.ADDR_TYPE,MICHEL2.EMPLOYEE,MICHEL2.LT_T...
- SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT, 6 items
- SCHEMA_EXPORT/DB_LINK, 4 items:
    MICHEL2.MIK8.micserver,MICHEL2.MYLINK.micserver,MICHEL2.MIKA.micserver,MICHEL2.MIK9.micserver
- SCHEMA_EXPORT/SEQUENCE/SEQUENCE, 5 items:
    MICHEL2.STUDENT_ID_SEQ,MICHEL2.TS,MICHEL2.SEQ,MICHEL2.SEQ_T,MICHEL2.FOO_ID_SEQ

Worker 1: (DW00) Executing on SCHEMA_EXPORT/TABLE/TABLE_DATA
  1 out of 254 done (0.4%, 23.2% of dump)
  working on table data MICHEL2.ORAFAQ_MSG

And FOLLOW the job again:
C:\>McDP michel/michel -f SYS_IMPORT_FULL_01 timeout=1 timeoutmf=1.1 timeoutmax=2 timeoutnb=2

McDP Utility by Michel Cadot: Version 2016.08.17

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 10-AOÛT-2016 18:03:19

10/08/2016 18:03:19.515 Following "MICHEL"."SYS_IMPORT_FULL_01":
10/08/2016 18:03:19.566 IMPORT FULL, EXECUTING, Parallel 1, 1 attached sessions

  Serveur: MIKB2.micserver - Start: 10/08/2016 18:01:43 - State: EXECUTING
  Client command: michel/ full=y remap_schema=michel:michel2 dumpfile=mic.dmp directory=my_dir transform=oid:n
  Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_C
    HARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_SORT=
    'BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT='HH.MI
    .SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NL
    S_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
  Client Command: michel/ full=y remap_schema=michel:michel2 dumpfile=mic.dmp directory=my_dir transform=oid:n
  Command Line Client: 1
  Data Access Method: AUTOMATIC
  Data Options: 0
  Include Metadata: 1
  Keep Master: 0
  Log File Directory: MY_DIR
  Log File Name: import.log
  Master Only: 0
  Metrics: 0
  Reuse Datafiles: 0
  Skip Unusable Indexes: 1
  Streams Configuration: 1
  Table Exists Action: SKIP
  Trace: 0
  Dump file(s):
    C:\mic.dmp
  Export parameters:
    Serveur: MIKB2.micserver - Start: 10/08/2016 16:27:03 - State: EXECUTING
  Client command: michel/ schemas=michel dumpfile=mic.dmp directory=my_dir metrics=yes
    Environment: NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC
      _CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' NLS_DATE_LANGUAGE='FRENCH' NLS_S
      ORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF3' NLS_TIME_TZ_FORMAT=
      'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZH:TZM' NLS_DUAL_CURRENCY='$' NLS_COMP='BI
      NARY' NLS_LENGTH_SEMANTICS='CHAR' NLS_NCHAR_CONV_EXCP='FALSE'
    Client Command: michel/ schemas=michel dumpfile=mic.dmp directory=my_dir metrics=yes
    Command Line Client: 1
    Compression: METADATA_ONLY
    Data Access Method: AUTOMATIC
    Data Options: 0
    Estimate: BLOCKS
    Include Metadata: 1
    Keep Master: 0
    Log File Directory: MY_DIR
    Log File Name: export.log
    Metrics: 1
    Table Consistency: 0
    Trace: 0
    User Metadata: 1
    Schema List: 'MICHEL'
    Schema Expr:  IN ('MICHEL')
    Dump file(s):
      C:\MIC.DMP

  Dump file(s):
    C:\mic.dmp
  Start time: 10/08/2016 18:01:43
  Done      : 23.2% (68364K/294618K)
  Errors    : 0
  Parallel  : 1
  Worker   1: (DW00) EXECUTING MICHEL2.ORAFAQ_MSG

10/08/2016 18:03:29.671 . . imported "MICHEL2"."ORAFAQ_MSG"                      58.23 MB  622968 rows
10/08/2016 18:03:29.671 43 percent done
10/08/2016 18:03:47.500 . . imported "MICHEL2"."SAV"                             57.28 MB  614217 rows
10/08/2016 18:03:47.500 63 percent done
10/08/2016 18:04:00.921 . . imported "MICHEL2"."ORAFAQ_MEMBER_HIST"              26.36 MB  778300 rows
10/08/2016 18:04:00.921 72 percent done
10/08/2016 18:04:08.125 . . imported "MICHEL2"."IPCOUNTRY"                       9.829 MB  120565 rows
10/08/2016 18:04:08.125 75 percent done
10/08/2016 18:04:13.296 . . imported "MICHEL2"."ORAFAQ_MEMBER"                   11.02 MB  110953 rows
10/08/2016 18:04:13.296 79 percent done
10/08/2016 18:04:17.343 . . imported "MICHEL2"."SAV2"                            11.07 MB  111597 rows
10/08/2016 18:04:17.343 83 percent done
10/08/2016 18:04:21.843 . . imported "MICHEL2"."ORAFAQ_TOPIC"                    8.666 MB  140776 rows
10/08/2016 18:04:21.843 86 percent done
10/08/2016 18:04:24.421 . . imported "MICHEL2"."OBJ"                             8.489 MB   87493 rows
10/08/2016 18:04:24.421 89 percent done
10/08/2016 18:04:26.203 . . imported "MICHEL2"."OBJS"                            7.004 MB   72402 rows
10/08/2016 18:04:27.031 . . imported "MICHEL2"."MV_OBJS"                         2.982 MB   31047 rows
10/08/2016 18:04:27.031 93 percent done
10/08/2016 18:04:33.109 . . imported "MICHEL2"."SYS_SQL_FILE_FULL_01"            1.759 MB    2758 rows
10/08/2016 18:04:34.312 . . imported "MICHEL2"."SYS_SQL_FILE_FULL_04"            1.771 MB    2797 rows
10/08/2016 18:04:35.500 . . imported "MICHEL2"."SYS_SQL_FILE_FULL_06"            1.766 MB    2787 rows
10/08/2016 18:04:35.500 94 percent done
10/08/2016 18:04:37.265 . . imported "MICHEL2"."ENCOUNT"                         1.976 MB  100000 rows
10/08/2016 18:04:38.093 . . imported "MICHEL2"."HIST"                            1.344 MB   74773 rows
10/08/2016 18:04:38.093 96 percent done
10/08/2016 18:04:39.187 . . imported "MICHEL2"."SYS_EXPORT_SCHEMA_01"            1.519 MB    2665 rows
10/08/2016 18:04:40.281 . . imported "MICHEL2"."SYS_EXPORT_SCHEMA_02"            1.602 MB    2536 rows
10/08/2016 18:04:41.359 . . imported "MICHEL2"."SYS_EXPORT_SCHEMA_04"            1.743 MB    2714 rows
10/08/2016 18:04:42.703 . . imported "MICHEL2"."SYS_EXPORT_SCHEMA_03"            1.528 MB    1791 rows
10/08/2016 18:04:42.703 98 percent done
10/08/2016 18:04:43.218 . . imported "MICHEL2"."PATIENT"                         970.7 KB  100000 rows
10/08/2016 18:04:43.609 . . imported "MICHEL2"."VEOLIA_MONITOR"                  1.012 MB    5768 rows
10/08/2016 18:04:44.265 . . imported "MICHEL2"."VEOLIA_MONITOR2"                 1.503 MB    8642 rows
10/08/2016 18:04:44.265 99 percent done
10/08/2016 18:04:44.875 . . imported "MICHEL2"."SYS_IMPORT_SCHEMA_01"            197.3 KB    1285 rows
10/08/2016 18:04:45.984 . . imported "MICHEL2"."SYS_IMPORT_SCHEMA_02"            193.5 KB    1271 rows
10/08/2016 18:04:46.406 . . imported "MICHEL2"."SPERRORLOG"                      9.117 KB      12 rows
10/08/2016 18:04:46.906 . . imported "MICHEL2"."SYS_EXPORT_TABLE_01"             79.20 KB     378 rows
10/08/2016 18:04:47.421 . . imported "MICHEL2"."BREAKFAST_MENU"                  6.421 KB       1 rows
10/08/2016 18:04:48.093 . . imported "MICHEL2"."CONTENT"                         5.921 KB       3 rows
10/08/2016 18:04:49.359 . . imported "MICHEL2"."DLL_AUDIT_LOG"                   8.554 KB       1 rows
10/08/2016 18:05:06.000 . . imported "MICHEL2"."PURCHASE_ORDER"                  6.507 KB       1 rows
     <<< cut a long list of empty tables, see impdp log above >>>
10/08/2016 18:05:47.781 . . imported "MICHEL2"."TEMP":"EMPTY"."EMPTY_JULY"           0 KB       0 rows
10/08/2016 18:05:47.781 . . imported "MICHEL2"."TEMP":"EMPTY"."EMPTY_JUNE"           0 KB       0 rows
10/08/2016 18:05:47.796 . . imported "MICHEL2"."TEMP":"EMPTY"."EMPTY_MARCH"          0 KB       0 rows
10/08/2016 18:05:47.812 . . imported "MICHEL2"."TEMP":"EMPTY"."EMPTY_MAY"            0 KB       0 rows
10/08/2016 18:05:47.812 . . imported "MICHEL2"."TEMP":"EMPTY"."EMPTY_NOVEMBER"       0 KB       0 rows
10/08/2016 18:05:47.812 . . imported "MICHEL2"."TEMP":"EMPTY"."EMPTY_OCTOBER"        0 KB       0 rows
10/08/2016 18:05:47.828 . . imported "MICHEL2"."TEMP":"EMPTY"."EMPTY_SEPTEMBER"      0 KB       0 rows
10/08/2016 18:05:47.875 . . imported "MICHEL2"."TEST_PARTITION":"TEST_PART1"         0 KB       0 rows
10/08/2016 18:05:47.906 . . imported "MICHEL2"."TIME"                                0 KB       0 rows
10/08/2016 18:05:47.906 . . imported "MICHEL2"."TMP3_ORGANIZATIONS"                  0 KB       0 rows
10/08/2016 18:05:47.921 . . imported "MICHEL2"."TMP_MAF_2"                           0 KB       0 rows
10/08/2016 18:05:47.921 . . imported "MICHEL2"."TMP_TEST"                            0 KB       0 rows
10/08/2016 18:05:47.937 . . imported "MICHEL2"."TOTO"                                0 KB       0 rows
10/08/2016 18:05:47.937 . . imported "MICHEL2"."T_LIC"                               0 KB       0 rows
10/08/2016 18:05:47.984 . . imported "MICHEL2"."T_MINE"                              0 KB       0 rows
10/08/2016 18:05:47.984 . . imported "MICHEL2"."V_VDN_MAP"                           0 KB       0 rows
10/08/2016 18:05:48.000 . . imported "MICHEL2"."XML_TEST"                            0 KB       0 rows
10/08/2016 18:05:48.015 . . imported "MICHEL2"."XX"                                  0 KB       0 rows
10/08/2016 18:05:48.015 . . imported "MICHEL2"."Z"                                   0 KB       0 rows
10/08/2016 18:05:48.031 Processing object type SCHEMA_EXPORT/TABLE/COMMENT
10/08/2016 18:05:48.031 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
10/08/2016 18:05:49.609 Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
10/08/2016 18:05:49.781 Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
10/08/2016 18:05:52.500 Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
10/08/2016 18:05:52.640 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
10/08/2016 18:05:53.171 Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
10/08/2016 18:05:53.296 Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
10/08/2016 18:05:56.265 Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
10/08/2016 18:06:02.375 ORA-39082: Object type ALTER_FUNCTION:"MICHEL2"."MYF" created with compilation warnings
10/08/2016 18:06:02.375 ORA-39082: Object type ALTER_FUNCTION:"MICHEL2"."RETURN" created with compilation warnings
10/08/2016 18:06:02.375 ORA-39082: Object type ALTER_FUNCTION:"MICHEL2"."RETURNS" created with compilation warnings
10/08/2016 18:06:02.375 ORA-39082: Object type ALTER_FUNCTION:"MICHEL2"."GET_QUOTES" created with compilation warnings
10/08/2016 18:06:02.375 ORA-39082: Object type ALTER_FUNCTION:"MICHEL2"."VF_COMPUTE_FIELD_VALUE" created with compilatio
n warnings
10/08/2016 18:06:02.375 ORA-39082: Object type ALTER_FUNCTION:"MICHEL2"."MONITOR_SCHEMA_INDEXES" created with compilatio
n warnings
10/08/2016 18:06:02.375 ORA-39082: Object type ALTER_FUNCTION:"MICHEL2"."F" created with compilation warnings
10/08/2016 18:06:02.375 ORA-39082: Object type ALTER_FUNCTION:"MICHEL2"."McDPWhere" created with compilation warnings
10/08/2016 18:06:02.390 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
10/08/2016 18:06:03.390 ORA-39082: Object type ALTER_PROCEDURE:"MICHEL2"."COPY_SID" created with compilation warnings
10/08/2016 18:06:03.390 ORA-39082: Object type ALTER_PROCEDURE:"MICHEL2"."PROC1" created with compilation warnings
10/08/2016 18:06:03.390 ORA-39082: Object type ALTER_PROCEDURE:"MICHEL2"."SANDEEP_TST_TBL" created with compilation warn
ings
10/08/2016 18:06:03.390 ORA-39082: Object type ALTER_PROCEDURE:"MICHEL2"."SLEEPTEST3" created with compilation warnings
10/08/2016 18:06:03.406 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
10/08/2016 18:07:01.578 Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
10/08/2016 18:07:07.921 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
10/08/2016 18:07:12.812 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
10/08/2016 18:07:19.218 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
10/08/2016 18:07:19.640 Processing object type SCHEMA_EXPORT/VIEW/VIEW
10/08/2016 18:07:21.921 Processing object type SCHEMA_EXPORT/VIEW/CONSTRAINT
10/08/2016 18:07:22.453 ORA-31684: Object type CONSTRAINT:"MICHEL2"."E_PK" already exists
10/08/2016 18:07:22.453 Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
10/08/2016 18:07:23.640 ORA-39082: Object type VIEW:"MICHEL2"."DATA" created with compilation warnings
10/08/2016 18:07:23.640 ORA-39082: Object type VIEW:"MICHEL2"."V3" created with compilation warnings
10/08/2016 18:07:23.640 ORA-39082: Object type VIEW:"MICHEL2"."V" created with compilation warnings
10/08/2016 18:07:23.640 ORA-39082: Object type VIEW:"MICHEL2"."V4" created with compilation warnings
10/08/2016 18:07:23.640 ORA-39082: Object type VIEW:"MICHEL2"."V5" created with compilation warnings
10/08/2016 18:07:23.640 ORA-39082: Object type VIEW:"MICHEL2"."V6" created with compilation warnings
10/08/2016 18:07:23.640 ORA-39082: Object type VIEW:"MICHEL2"."V2" created with compilation warnings
10/08/2016 18:07:23.640 ORA-39082: Object type VIEW:"MICHEL2"."V4_1" created with compilation warnings
10/08/2016 18:07:23.796 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
10/08/2016 18:07:32.500 ORA-39082: Object type PACKAGE_BODY:"MICHEL2"."MCA_LONGOPS" created with compilation warnings
10/08/2016 18:07:32.500 ORA-39082: Object type PACKAGE_BODY:"MICHEL2"."P21" created with compilation warnings
10/08/2016 18:07:32.500 ORA-39082: Object type PACKAGE_BODY:"MICHEL2"."PKG" created with compilation warnings
10/08/2016 18:07:32.500 ORA-39082: Object type PACKAGE_BODY:"MICHEL2"."PKG2" created with compilation warnings
10/08/2016 18:07:32.500 ORA-39082: Object type PACKAGE_BODY:"MICHEL2"."PKG_SYSTEM" created with compilation warnings
10/08/2016 18:07:32.937 Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
10/08/2016 18:07:38.031 ORA-39082: Object type TYPE_BODY:"MICHEL2"."McDPWhereType" created with compilation warnings
10/08/2016 18:07:38.031 ORA-39082: Object type TYPE_BODY:"MICHEL2"."McDPWhereType" created with compilation warnings
10/08/2016 18:07:38.078 Processing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE
10/08/2016 18:07:40.968 Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS
10/08/2016 18:07:41.140 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
10/08/2016 18:07:41.750 Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
10/08/2016 18:07:44.812 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
10/08/2016 18:07:45.281 Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
10/08/2016 18:07:46.109 Processing object type SCHEMA_EXPORT/VIEW/TRIGGER
10/08/2016 18:07:46.265 Processing object type SCHEMA_EXPORT/EVENT/TRIGGER
10/08/2016 18:07:46.687 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
10/08/2016 18:08:46.718 Awaken by time-out
      Worker 1: Executing on SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
        68 out of 219 done (31.1%)
        Working on table statistics  (MICHEL2.INCOMING) since 18:08:46
10/08/2016 18:09:47.968 Awaken by time-out
      Worker 1: Executing on SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
         146 out of 219 done (66.7%)
        Working on table statistics  (MICHEL2.EMPLOYEES) since 18:09:47
10/08/2016 18:10:48.062 Awaken by time-out
      Worker 1: Executing on SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
        214 out of 219 done (97.7%)
        Working on table statistics  (MICHEL2.SYS_IMPORT_SCHEMA_02) since 18:10:45
10/08/2016 18:10:53.953 Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
10/08/2016 18:10:55.203 Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
10/08/2016 18:10:56.156 Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG

10/08/2016 18:11:06.812 Job "MICHEL"."SYS_IMPORT_FULL_01" completed with 30 error(s) at Mer. Août 10 18:10:58 2016 elaps
ed 0 00:09:17
10/08/2016 18:11:06.812 End of job: COMPLETED
We can better see here the progress of the job with the "percent done" lines, it is a percentage of the reading of the dump files not in number of imported objects.

At this point let me explain these "timeout*" parameters.
As Data Pump engine only reports when something is completed or an error occurs, it is possible that it does not report anything during minutes or even hours for very long operations. So to avoid we don't know if the job is hanging somewhere or it is executing something, I set this timeout mechanism. If the Data Pump did not report something for some time, the time-out one, then McDP displays the current status of each worker: what operation it is doing, how much it has already done, what it is currently working on. This is the purpose of the first parameter "timeout" which set an initial value for the time-out, in minutes. Here, we set it to 1 minute which means if the Data Pump engine did not report a message within a minute McDP displays some status messages. This is what happened there:
10/08/2016 18:07:46.687 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
10/08/2016 18:08:46.718 Awaken by time-out
      Worker 1: Executing on SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
        68 out of 219 done (31.1%)
        Working on table statistics  (MICHEL2.INCOMING) since 18:08:46
The statistics computation has started at 18:07:46.687 and lasts more than a minute so at 18:08:46.718 McDP displayed a message saying that "Worker 1" (the only one in this case) has completed 68/219 of the tables and is currently working on INCOMING table.
Now the execution time could be so long that we can have hundred of such status lines which will fill the screen and may make us lose some important messages.
So I introduced 2 other parameters: a threshold (timeoutnb) and a multiplying factor (timeoutmf), when the number of time-out exceeds the threshold then time-out time is multiplied by the factor. (We can't actually see this happening in my example as the steps are not long enough.)
The last parameter, timeoutmax, set a maximum time for the time-out to increase, if we want to have not too much messages but at least a status each "timeoutmax" minutes.
When the Data Engine reports a new message, the time-out time is reset to its initial value.

[Updated on: Tue, 23 August 2016 03:48]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655125 is a reply to message #655124] Mon, 22 August 2016 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

USER command

The last command of the first version of McDP is not a real Data Pump command as it does not use its engine but it is a useful one for a requirement that is often asked to DBA: get the DDL to recreate one or several accounts, possibly with its privileges.

Its syntax is:
McDP [<logon>] { -u | --user } [<user>[,...]] [[-opt] grants=<grant type>[,...]]
McDP [USERID=<logon>] COMMAND=USER [USER=<user>[,...]] [GRANT={ROLE|SYSTEM|OBJECT|ALL}[,...]]
Note: If no user is given, the current one is taken.
Note: joker characters (_%) are expanded.
Note: Only privileged accounts can use this command.

Examples where MICHEL is a privileged account

Get the DDL to create users MICHEL and SCOTT with their roles and system privileges:
C:\>McDP michel/michel -u michel,scott grant=role,system linesize=130

McDP Utility by Michel Cadot: Version 2016.08.22

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 22-AOÛT-2016 11:34:18


-- User MICHEL
-- ===========

   CREATE USER "MICHEL" IDENTIFIED BY VALUES 'S:BA778276560DC659148B46111FFFA6B96D94BEEC19B7B4BFE98AD7EAA0F3;A16FD5BFC24EA1EC'
      DEFAULT TABLESPACE "TS_D01"
      TEMPORARY TABLESPACE "TEMP";

   GRANT "DBA" TO "MICHEL";
   GRANT "SELECT_CATALOG_ROLE" TO "MICHEL";
   GRANT "MESDVD_LECTURE" TO "MICHEL" WITH ADMIN OPTION;
   GRANT "MESDVD_TOUT" TO "MICHEL" WITH ADMIN OPTION;
   GRANT "SECURITY_SSN" TO "MICHEL" WITH ADMIN OPTION;

  GRANT DEBUG ANY PROCEDURE TO "MICHEL";
  GRANT SELECT ANY DICTIONARY TO "MICHEL";
  GRANT EXECUTE ANY LIBRARY TO "MICHEL";
  GRANT CREATE ANY LIBRARY TO "MICHEL";
  GRANT CREATE TYPE TO "MICHEL";
  GRANT CREATE TRIGGER TO "MICHEL";
  GRANT CREATE PROCEDURE TO "MICHEL";
  GRANT CREATE SEQUENCE TO "MICHEL";
  GRANT CREATE VIEW TO "MICHEL";
  GRANT CREATE SYNONYM TO "MICHEL";
  GRANT CREATE TABLE TO "MICHEL";
  GRANT UNLIMITED TABLESPACE TO "MICHEL";
  GRANT CREATE SESSION TO "MICHEL";

-- User SCOTT
-- ==========

   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:85BB00A6574C42152E6390E07358309D10A4E2024B9EF7493B06E9A5A725;F894844C34402B67'
      DEFAULT TABLESPACE "TS_D01"
      TEMPORARY TABLESPACE "TEMP";

   GRANT "CONNECT" TO "SCOTT";
   GRANT "RESOURCE" TO "SCOTT";

Get the DDL to create all "ST%" accounts:
C:\>McDP michel/michel -u st% linesize=130

McDP Utility by Michel Cadot: Version 2016.08.22

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 22-AOÛT-2016 11:35:44


-- User STAGE0
-- ===========

   CREATE USER "STAGE0" IDENTIFIED BY VALUES 'S:B7FFF4AD00038FD533B5FD3222C374D36826E372FD6C7E24FF3EF0FF42C3;E565F170885091E5'
      DEFAULT TABLESPACE "TEST"
      TEMPORARY TABLESPACE "TEMP";

-- User STAGE1
-- ===========

   CREATE USER "STAGE1" IDENTIFIED BY VALUES 'S:EBE000EA425ACDAA30AB749FE61B665283E4FB2B54C6785CD456A3A1D672;EBEDB937D0D4B812'
      DEFAULT TABLESPACE "TEST"
      TEMPORARY TABLESPACE "TEMP";

-- User STAGE2
-- ===========

   CREATE USER "STAGE2" IDENTIFIED BY VALUES 'S:F39708D4CB09BD96C29B60C9A9FB105217381202B72D2F4C1CE95AF29C75;2C4DA151473B3207'
      DEFAULT TABLESPACE "TEST"
      TEMPORARY TABLESPACE "TEMP";

-- User STAGE3
-- ===========

   CREATE USER "STAGE3" IDENTIFIED BY VALUES 'S:56E21C9ED76377344947584F724256947C1EBA8E7A120888C41A0CEC0675;0E9658D5CA0CC310'
      DEFAULT TABLESPACE "TEST"
      TEMPORARY TABLESPACE "TEMP";

-- User STAGE4
-- ===========

   CREATE USER "STAGE4" IDENTIFIED BY VALUES 'S:8A3868D0A48F2FF63605CDB7B3580BFE17311F2B6766819657A171477798;BC1DF2F3B8F5CB8D'
      DEFAULT TABLESPACE "TEST"
      TEMPORARY TABLESPACE "TEMP";

-- User STAGE5
-- ===========

   CREATE USER "STAGE5" IDENTIFIED BY VALUES 'S:C331891446E76E76E9991B453CEF5282F36349D79F11672874E5CBA50D58;BD586FA5A86844C3'
      DEFAULT TABLESPACE "TEST"
      TEMPORARY TABLESPACE "TEMP";

-- User STAGE6
-- ===========

   CREATE USER "STAGE6" IDENTIFIED BY VALUES 'S:DDB607FD6796DCD7443814958E137FC6B9F13A36D025FF7AF59EAF232CF8;E70AFAC58F18EE79'
      DEFAULT TABLESPACE "TEST"
      TEMPORARY TABLESPACE "TEMP";

-- User STAGE7
-- ===========

   CREATE USER "STAGE7" IDENTIFIED BY VALUES 'S:FB66E5174CF8FC0130FF130A41B6F831F7642E33867B3096C31242F559C6;82223BB7636690D6'
      DEFAULT TABLESPACE "TEST"
      TEMPORARY TABLESPACE "TEMP";

-- User STAGE8
-- ===========

   CREATE USER "STAGE8" IDENTIFIED BY VALUES 'S:BAAE5908C82C6BED4C3FD6631A0A06BD5FFAC64F25585A7C0B96DEF327AF;1EB3BBF2BF41B9DD'
      DEFAULT TABLESPACE "TEST"
      TEMPORARY TABLESPACE "TEMP";

-- User STAGE9
-- ===========

   CREATE USER "STAGE9" IDENTIFIED BY VALUES 'S:B90C451A0371D1EADBBC54D8E51AC33F61333792C9B70887D5880E2D52D0;6BA8B797B3F6D66F'
      DEFAULT TABLESPACE "TEST"
      TEMPORARY TABLESPACE "TEMP";

Get the DDL to recreate my account with my privileges:
C:\>McDP michel/michel -u grant=all linesize=130

McDP Utility by Michel Cadot: Version 2016.08.22

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 22-AOÛT-2016 11:37:05


-- User MICHEL
-- ===========

   CREATE USER "MICHEL" IDENTIFIED BY VALUES 'S:BA778276560DC659148B46111FFFA6B96D94BEEC19B7B4BFE98AD7EAA0F3;A16FD5BFC24EA1EC'
      DEFAULT TABLESPACE "TS_D01"
      TEMPORARY TABLESPACE "TEMP";

   GRANT "DBA" TO "MICHEL";
   GRANT "SELECT_CATALOG_ROLE" TO "MICHEL";
   GRANT "MESDVD_LECTURE" TO "MICHEL" WITH ADMIN OPTION;
   GRANT "MESDVD_TOUT" TO "MICHEL" WITH ADMIN OPTION;
   GRANT "SECURITY_SSN" TO "MICHEL" WITH ADMIN OPTION;

  GRANT DEBUG ANY PROCEDURE TO "MICHEL";
  GRANT SELECT ANY DICTIONARY TO "MICHEL";
  GRANT EXECUTE ANY LIBRARY TO "MICHEL";
  GRANT CREATE ANY LIBRARY TO "MICHEL";
  GRANT CREATE TYPE TO "MICHEL";
  GRANT CREATE TRIGGER TO "MICHEL";
  GRANT CREATE PROCEDURE TO "MICHEL";
  GRANT CREATE SEQUENCE TO "MICHEL";
  GRANT CREATE VIEW TO "MICHEL";
  GRANT CREATE SYNONYM TO "MICHEL";
  GRANT CREATE TABLE TO "MICHEL";
  GRANT UNLIMITED TABLESPACE TO "MICHEL";
  GRANT CREATE SESSION TO "MICHEL";

  GRANT SELECT ON "SYS"."IND$" TO "MICHEL" WITH GRANT OPTION;
  GRANT SELECT ON "SYS"."USER$" TO "MICHEL" WITH GRANT OPTION;
  GRANT SELECT ON "SYS"."OBJ$" TO "MICHEL" WITH GRANT OPTION;
  GRANT SELECT ON "SYS"."OBJECT_USAGE" TO "MICHEL" WITH GRANT OPTION;
  GRANT EXECUTE ON "SYS"."DBMS_JAVA_TEST" TO "MICHEL";
  GRANT EXECUTE ON "SYS"."DBMS_LOCK" TO "MICHEL" WITH GRANT OPTION;
  GRANT EXECUTE ON "SYS"."DBMS_CRYPTO" TO "MICHEL";
  GRANT EXECUTE ON "SYS"."DBMS_SYSTEM" TO "MICHEL";
  GRANT WRITE ON DIRECTORY "LOG_FILE_DIR" TO "MICHEL" WITH GRANT OPTION;
  GRANT READ ON DIRECTORY "LOG_FILE_DIR" TO "MICHEL" WITH GRANT OPTION;
  GRANT EXECUTE ON DIRECTORY "LOG_FILE_DIR" TO "MICHEL" WITH GRANT OPTION;
  GRANT WRITE ON DIRECTORY "MYDIR" TO "MICHEL" WITH GRANT OPTION;
  GRANT READ ON DIRECTORY "MYDIR" TO "MICHEL" WITH GRANT OPTION;
  GRANT EXECUTE ON DIRECTORY "MYDIR" TO "MICHEL" WITH GRANT OPTION;
  GRANT SELECT ON "SCOTT"."EMP" TO "MICHEL";
  GRANT UPDATE ("EMPNO") ON "SCOTT"."EMP" TO "MICHEL";
  GRANT WRITE ON DIRECTORY "MY_DIR" TO "MICHEL" WITH GRANT OPTION;
  GRANT READ ON DIRECTORY "MY_DIR" TO "MICHEL" WITH GRANT OPTION;
  GRANT EXECUTE ON DIRECTORY "MY_DIR" TO "MICHEL" WITH GRANT OPTION;
  GRANT SELECT ON "SCOTT2"."EMP" TO "MICHEL";
  GRANT UPDATE ("EMPNO") ON "SCOTT2"."EMP" TO "MICHEL";
  GRANT USE ON "SYS"."TOTO" TO "MICHEL" WITH GRANT OPTION;
  GRANT SELECT ON "STAGE0"."EMP" TO "MICHEL";
  GRANT UPDATE ("EMPNO") ON "STAGE0"."EMP" TO "MICHEL";
  GRANT SELECT ON "STAGE1"."EMP" TO "MICHEL";
  GRANT UPDATE ("EMPNO") ON "STAGE1"."EMP" TO "MICHEL";
  GRANT SELECT ON "STAGE2"."EMP" TO "MICHEL";
  GRANT UPDATE ("EMPNO") ON "STAGE2"."EMP" TO "MICHEL";
  GRANT SELECT ON "STAGE3"."EMP" TO "MICHEL";
  GRANT UPDATE ("EMPNO") ON "STAGE3"."EMP" TO "MICHEL";
  GRANT SELECT ON "STAGE4"."EMP" TO "MICHEL";
  GRANT UPDATE ("EMPNO") ON "STAGE4"."EMP" TO "MICHEL";
  GRANT SELECT ON "STAGE5"."EMP" TO "MICHEL";
  GRANT UPDATE ("EMPNO") ON "STAGE5"."EMP" TO "MICHEL";
  GRANT SELECT ON "STAGE6"."EMP" TO "MICHEL";
  GRANT UPDATE ("EMPNO") ON "STAGE6"."EMP" TO "MICHEL";
  GRANT SELECT ON "STAGE7"."EMP" TO "MICHEL";
  GRANT UPDATE ("EMPNO") ON "STAGE7"."EMP" TO "MICHEL";
  GRANT SELECT ON "STAGE8"."EMP" TO "MICHEL";
  GRANT UPDATE ("EMPNO") ON "STAGE8"."EMP" TO "MICHEL";
  GRANT SELECT ON "STAGE9"."EMP" TO "MICHEL";
  GRANT UPDATE ("EMPNO") ON "STAGE9"."EMP" TO "MICHEL";
  GRANT WRITE ON DIRECTORY "WORK_DIR" TO "MICHEL" WITH GRANT OPTION;
  GRANT READ ON DIRECTORY "WORK_DIR" TO "MICHEL" WITH GRANT OPTION;
  GRANT EXECUTE ON DIRECTORY "WORK_DIR" TO "MICHEL" WITH GRANT OPTION;

That's all for this first version of McDP.

[Updated on: Mon, 22 August 2016 10:15]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655229 is a reply to message #655125] Thu, 25 August 2016 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
COPY command

The COPY command allows to copy a schema or part of it into another schema or the same one and a remote or local database.

Its syntax is:
McDP [<logon>] { -cp | --copy } <db link> [<source schema> [<target schema>]]                 ^
                                          [[-opt] <filter/transform/display option>]
McDP [USERID=<logon>] COMMAND=COPY NETWORK_LINK=<dblink> [{SOURCE|FROMUSER}=<source schema>]  ^
                                                         [{TARGET|TOUSER}=<target schema>]    ^
                                                         [<option>=<value>]... 
"<dblink>" and "NETWORK_LINK=<dblink>" parameters give the database link through which the Data Pump engine will inquire the "remote" database, even if you want to copy into the database on which you are connected and with the same user, you must provide a database link, this is a restriction of the Data Pump engine.
Note: If you don't give the target schema, the source one is taken; if you don't give the source schema, the current user is taken.

Examples where MICHEL is a privileged account

We will copy "SCOTT" schema into "SCOTT2" but "T%" and 'SYS%' tables, remapping the tablespace.
First we check there is no objects in SCOTT2 schema:
SQL> select count(*) from dba_objects where owner='SCOTT2';
  COUNT(*)
----------
         0

Then we execute McDP:
C:\>McDP michel/michel COMMAND=COPY NETWORK_LINK=mylink SOURCE=scott TARGET=scott2                 ^
More? EXCLUDE="table:\"like 'T%'\"" EXCLUDE="table:\"like 'SYS%'\"" REMAP_TABLESPACE=ts_d01:scott2

McDP Utility by Michel Cadot: Version 2016.08.25

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 25-AOÛT-2016 11:39:36

25/08/2016 11:39:36.500 Exporting "SCOTT" into "SCOTT2" using "MYLINK"
25/08/2016 11:39:36.500 Defining the job
25/08/2016 11:39:43.468 Data Pump job started successfully
25/08/2016 11:39:43.500 FLASHBACK automatically enabled to preserve database integrity.
25/08/2016 11:39:43.515 Starting "MICHEL"."SYS_IMPORT_SCHEMA_04":
  MICHEL: copy schema SCOTT@MYLINK into SCOTT2
  NAME_EXPR=TABLE:"NOT like 'T%'"
  NAME_EXPR=TABLE:"NOT like 'SYS%'"
  REMAP_TABLESPACE=TS_D01:SCOTT2
25/08/2016 11:39:48.515 Estimate in progress using BLOCKS method...
25/08/2016 11:39:49.281 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
25/08/2016 11:39:49.328 Total estimation using BLOCKS method: 192 KB
25/08/2016 11:39:49.828 Processing object type SCHEMA_EXPORT/USER
25/08/2016 11:39:49.968 ORA-31684: Object type USER:"SCOTT2" already exists
25/08/2016 11:39:49.984 Processing object type SCHEMA_EXPORT/ROLE_GRANT
25/08/2016 11:39:50.000 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
25/08/2016 11:39:50.031 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
25/08/2016 11:39:50.359 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
25/08/2016 11:39:53.500 Processing object type SCHEMA_EXPORT/TABLE/TABLE
25/08/2016 11:39:54.359 . . imported "SCOTT2"."DEPT"                                  4 rows
25/08/2016 11:39:54.437 . . imported "SCOTT2"."EMP"                                  14 rows
25/08/2016 11:39:54.484 . . imported "SCOTT2"."SALGRADE"                              5 rows
25/08/2016 11:39:54.484 . . imported "SCOTT2"."BONUS"                                 0 rows
25/08/2016 11:39:54.500 Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
25/08/2016 11:39:54.984 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
25/08/2016 11:39:55.359 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
25/08/2016 11:39:55.515 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
25/08/2016 11:39:56.125 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
25/08/2016 11:39:56.468 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
25/08/2016 11:39:59.187 Job "MICHEL"."SYS_IMPORT_SCHEMA_04" completed with 1 error(s) at Jeu. Août 25 11:39:58 2016 elap
sed 0 00:00:21
25/08/2016 11:39:59.187 End of data pump job, status COMPLETED
As for the commands we already saw, McDP displays the options you gave after translation into Data Pump engine syntax.

In the end, we verify SCOTT2's objects:
SQL> col object_name format a30
SQL> select object_name, object_type from dba_objects where owner='SCOTT2' order by 1, 2;
OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
PK_DEPT                        INDEX
PK_EMP                         INDEX
SALGRADE                       TABLE

SQL> select segment_name, tablespace_name from dba_segments where owner='SCOTT2' order by 1;
SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           SCOTT2
EMP                            SCOTT2
PK_DEPT                        SCOTT2
PK_EMP                         SCOTT2
SALGRADE                       SCOTT2

[Updated on: Wed, 12 July 2017 12:22]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #655311 is a reply to message #655229] Fri, 26 August 2016 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A new version has been delivered today.
It better supports interruption by Control-C (SIGINT), removes the created objects and displays the partial result file, if asked.

For instance using a DDL command we executed in a previous post and interrupting it a short while after it started:
C:\>McDP michel/michel -ddl my_dir:scott.sql mylink schema scott drop                       ^
More? -opt exclude=statistics exclude="table:\"like 'T%'\"" exclude="table:\"like 'SYS%'\"" ^
More? -opt segment_attributes=no storage=no

McDP Utility by Michel Cadot: Version 2016.08.26

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 26-AOÛT-2016 21:40:31

26/08/2016 21:40:31.281 Getting DDL from using "MYLINK"
26/08/2016 21:40:31.281 Defining the job
26/08/2016 21:40:36.781 Add the SQL file
26/08/2016 21:40:36.796 Add the object list
26/08/2016 21:40:36.937 Result file will be MY_DIR/scott.sql
26/08/2016 21:40:37.890 Data Pump job started successfully
26/08/2016 21:40:37.937 Starting "MICHEL"."SYS_SQL_FILE_SCHEMA_02":
  MICHEL: get DDL from MYLINK for 'SCOTT'
  SCHEMA_LIST="'SCOTT'"
  SQL FILE: scott.sql
  EXCLUDE_PATH_EXPR='STATISTICS'
  NAME_EXPR=TABLE:"NOT like 'T%'"
  NAME_EXPR=TABLE:"NOT like 'SYS%'"
  SEGMENT_ATTRIBUTES/%=0
  STORAGE/%=0
^C
26/08/2016 21:40:43.359 *** Job aborted at user request (Control-C); cleaning...
26/08/2016 21:40:46.093 Partial result:
========================================================================================================================
-- CONNECT MICHEL
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER

*** Job aborted at user request (Control-C)
========================================================================================================================
26/08/2016 21:40:46.162 *** Job aborted at user request (Control-C)

Now check the job and the result file are no more there:
C:\>McDP michel/michel -d "MICHEL"."SYS_SQL_FILE_SCHEMA_02"

McDP Utility by Michel Cadot: Version 2016.08.26

Copyright (c) Michel Cadot, 2016. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on micserver on 26-AOÛT-2016 21:40:51

*** DISPLAY: no such job ("MICHEL"."SYS_SQL_FILE_SCHEMA_02")

C:\>dir c:\scott.sql
 Le volume dans le lecteur C s'appelle System
 Le numéro de série du volume est FFFF-FFFF

 Répertoire de c:\

Fichier introuvable

I advise those who have downloaded the previous version to download this new one to avoid some pending or running Data Pump jobs: it was like expdp/imdp when you killed the client program the server process still runs, now when you kill McDP it cleans the job (Control-C or kill -15, of course not kill -9 which means immediate abort).

[Updated on: Fri, 26 August 2016 14:50]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #656365 is a reply to message #655311] Tue, 04 October 2016 01:33 Go to previous message
Michel Cadot
Messages: 65190
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

McDP now supports 64-bit client.
There are actually 2 downloads, one for 32-bit and one for 64-bit (see link in first post).

Previous Topic: ORacle Scheduler
Next Topic: Senior Oracle Application Express (APEX) Developer
Goto Forum:
  


Current Time: Sun Oct 22 16:13:56 CDT 2017

Total time taken to generate the page: 0.03477 seconds