Home » Other » Marketplace » McDP: like expdp/impdp Data Pump programs and much more (10g-12cR2)
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: 65529
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)
    (*] Stop or kill a specific Data Pump worker (STOP and KILL commands)
  • Alter some execution or trace 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 a status of some specific objects during 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 (COPY command)
  • Copy tables and views of one or several schemas into one or several other or same ones, possibly renaming them, and a remote or local database (COPY command)
Interested?
So here's the (quite long) help:
C:\>McDP -h

McDP Utility by Michel Cadot: Version 2018.03.20

Copyright (c) Michel Cadot, 2016-2018. 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/trace option> [...]
          Modifies some execution parameters of the Data Pump job.
  { -c   | --content }  [<directory>:]<file>[,...]
                        [[--]show] [{[--]keep|[--]drop}]
                        [[-opt] <filter/transform/display/trace 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>]] [<access method>]
                        [[-opt] <filter/transform/display/execution/trace option>]
          Copies a schema or part of it into another or same one.
  { -cp  | --copy }     <db link> [<schema.>]<table/view name>[:[<schema.>]<new name>][,...]
                        [<source schema> [<target schema>]] [<access method>]
                        [[-opt] <filter/transform/display/execution/trace option>]
          Copies tables or views from a schema to another one, possibly renaming them;
          views are converted into tables.
  { -d   | --display }  [<schema>.]<dp job> [[-opt] <display/trace option>]
          Gives information about a Data Pump job.
  { -d   | --display }  [<schema>.]<dp job> [<schema>.]<object name>[:<type>][,...]
          Retrieves status information of one or several objects in master table;
          default type is TABLE; default schema is job's one.
  { -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/trace 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/trace option>]
          Follows a Data Pump job displaying information as and when it runs.
  { -k   | --kill }     [<schema>.]<dp job>
                        [{[--]keep|[--]drop [[--]force]}] [[--]noprompt]
                        [[-opt] <trace option>]
          Kills a Data Pump.
  { -l   | --list }     [<schema>] [{[--]active|[--]idle}] [{-v|[--]verbose}]
                        [[-opt] schema=<schema>] [[-opt] <trace option>]
          Lists Data Pump jobs of all or one schema.
  { -m   | --modify }   [<schema>.]<dp job> [-opt] <execution/trace option> [...]
          Modifies some execution parameters of the Data Pump job.
  { -r   | --restart | --continue } [<schema>.]<dp job> [<service name>]
                                    [[--]skip_current]
                                    [[-opt] service_name=<service name>]
                                    [[-opt] <trace option>]
          Restarts a previously stopped or killed job.
  { -s   | --stop | --suspend } [<schema>.]<dp job>
               { [[--]step <n>] [{ [--]keep | [--]drop [force] }] [[--]noprompt]
               | <worker_id> [[--]skip_current] }
               [[-opt] <trace option>]
          Stops a Data Pump job, possibly at a specified step, or a worker
          retrying the current step or not depending on "skip_current" option.
  { -u   | --user }             [<user>[,...]] [[-opt] grants=<grant type>[,...]]
                                               [[-opt] user=<user>[,...]]
                                               [[-opt] <trace option>]
          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.
* The Data Pump job owner must have the "EXP_FULL_DATABASE" or "IMP_FULL_DATABASE"
  role set by default to set a trace/debug flag on its job. Traces are written in
  the standard processes trace file (you'll find in alter.log file the process ids
  of the Data Pump job).
* 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.
* <username> must have the UPDATE ANY TABLE privilege to be able to use "--step" with
  "--stop"/"--suspend" command.
* 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" schema command, if you don't give the source or target schema, the current
  user is taken.
* In "COPY" tables command, source and/or target schema can be given with the table or
  view names, when not given at this place the source or target one defined in the
  previous point is taken.
* Non-privileged user can copy only in their own schema.
* There can be several "-opt" parameters, see "OPTION" below.

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

The available keywords and their descriptions follow.

  ACCESS_METHOD=<method>            Access method to use with COPY command (12.2+ only).
  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).
  FROMUSER=<source schema>          Gives the source schema for COPY command.
  HELP={YES|NO}                     Displays this help (default is NO).
  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).
  OBJECTS=<object>[,<object>...]    Object list for DDL or DISPLAY commands.
  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
                                      or when stopping a worker.
  SOURCE=<source schema>            Gives the source schema for COPY command.
  SQLFILE=[<directory>:]<file>      SQL result file.
  TABLES=[<schema.>]<table/view name>[:[<schema.>]<new name>][,...]
                                    Table and view list for COPY command.
  TARGET=<target schema>            Gives the target schema for COPY command.
  TOUSER=<target schema>            Gives the target schema for COPY command.
  TRACE=<trace options>             Sets a trace/debug flag, see below.
  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.
  WORKER[_ID]=<n>                   Gives the id of the worker to stop.
  <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
    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
    METRICS                  Indicates if additional information have to be displayed,
                               syntax: { YES | NO }
    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_DIRECTORY          Indicates how to remap a directory, 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_TABLES          Indicates if a view has to be converted to a table, syntax;
                               [SCHEMA_NAME.]VIEW_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 "COPY" command (in addition to the previous section):
    ABORT_STEP               Enables you to stop the job at a specified step, syntax:
                               { -1 | <n> }
                               -1 means after job set up but before any export/import,
                               <n> means when processing order number <n> is reached.
    CONTENT                  Enables you to filter what Export unloads:
                               { ALL | DATA_ONLY | METADATA_ONLY }
    DATA_OPTIONS             Designates how certain types of data should be handled, syntax:
                               { DISABLE_APPEND_HINT | ENABLE_NETWORK_COMPRESSION |
                                 GROUP_PARTITION_TABLE_DATA | REJECT_ROWS_WITH_REPL_CHAR |
                                 SKIP_CONSTRAINT_ERRORS | TRUST_EXISTING_TABLE_PARTITIONS |
                                 VALIDATE_TABLE_DATA | VERIFY_STREAM_FORMAT | XML_CLOBS }
    DISABLE_ARCHIVE_LOGGING  Specifies whether to disable archive logging: YES/NO
    FLASHBACK_SCN            Specifies the SCN that McDP will copy
    FLASHBACK_TIME           Specifies the SCN that most closely matches the specified
                                time that McDP will copy, syntax:
                                "TO_TIMESTAMP(<time value>)"
    INCLUDE_ROWS             Includes rows or not for some or all tables,
                               syntax: [TABLE:]VALUE
    KEEP_MASTER              Indicates whether the master table should be deleted or
                               retained at the end of the job, syntax: { YES | NO }
    PARTITION_OPTIONS        Specifies how table partitions should be created during the
                               operation, syntax:
                               { NONE | DEPARTITION | MERGE }
    QUERY                    Allows you to specify a query clause that is used to filter
                               the data that gets exported, syntax:
                               [TABLE:]"EXPRESSION"
    SAMPLE                   Allows you to specify a percentage of the data rows to be
                               sampled and unloaded from the source, syntax:
                               [TABLE:]SAMPLE_PERCENT
    SKIP_UNUSABLE_INDEXES    Specifies whether McDP skips loading tables that have indexes
                               that were set to the Index Unusable state , syntax:
                               { YES | NO }
    STREAMS_CONFIGURATION    Specifies whether to copy any Streams metadata, syntax:
                               { YES | NO }
    TABLE_EXISTS_ACTION      Tells Import what to do if the table it is trying to create
                               already exists, syntax:
                               { SKIP | APPEND | TRUNCATE | REPLACE }
* For "FOLLOW" command (display and execution 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 "STOP" command (execution option):
    ABORT_STEP               Enables you to stop the job at a specified step, syntax: <n>
                               stops when processing order number <n> is reached.
    SKIP_CURRENT             Enables to skip the current worker step, by default it is
                               retried.
* 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 "ALTER", "CONTENT", "CONTINUE", "COPY", "DDL", "DISPLAY", "FOLLOW", "KILL", "LIST",
  "MODIFY", "RESTART", "STOP", "SUSPEND", "USER" (trace option):
    TRACE                    Sets/clears trace/debug flag, it can be:
                               OFF or 0                to clear all tracing and debugging
                               ON or 1                 to set SQL trace
                               or one or more of the following options separated by "+":
                                 ALL                   see below
                                 API                   to trace the Data Pump API
                                 FILE                  to trace Data Pump file actions
                                 MASTER                to trace Data Pump master
                                 METADATA              to trace DBMS_METADATA
                                 NOTIME                to remove timestamp on each trace line
                                                         (make easier the comparison between traces)
                                 PROCESS               to trace DP session processes
                                 QUEUE                 to trace Data Pump queue table
                                 SQL                   to set SQL trace
                                 TABLE                 to trace DP master table actions
                                 WORKER                to trace Data Pump workers
                               or an 8 hexadecimal figure string: XXXXXXXX
     "METADATA" is the only option taken into account with "USER" command
     "ALL" is "API+FILE+MASTER+METADATA+PROCESS+QUEUE+TABLE+WORKER"
     "OFF", "ON" or an 8 hexadecimal figure string must be given alone
* For all commands (display option):
    DTFORMAT                  Sets the format of the displayed datetimes.
                                Default is DD/MM/YYYY HH24:MI:SS.FF3
    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/
This link also contains detailed explaination and many examples of McDP usage.
I encourage you to subscribe to this topic (link at top of the page) if you want to
be informed when a new version is released.

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: 2018.03.20, download in Datapump wiki page
32-bit: 
  MD5: f82b9ce93be058107e0fd7082337af84
  SHA-1: e634f8f25653dada51566ed5b7eef8ebf3c0138b
64-bit:
  MD5: d7767f1d7abf2515c61c6df4b1a107bd
  SHA-1: 5d9bbf4a9ef3eab76e8381de6a325d9c397e4a8d

[Updated on: Tue, 20 March 2018 09:18]

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: 65529
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: 65529
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: 65529
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: 65529
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: 65529
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: 65529
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: 65529
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: 65529
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: 65529
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 McDPsql_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: McDPsql_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/McDPsql_20160821103021.sql kept
The result is in file DATA_PUMP_DIR/McDPsql_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 McDPsql_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: McDPsql_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/McDPsql_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 McDPsql_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: McDPsql_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/McDPsql_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 McDPsql_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 McDPsql_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: Fri, 09 February 2018 03:28]

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: 65529
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>                ^
                                  [OBJECTS=<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 "OBJECTS=<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: Tue, 20 March 2018 05:41]

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: 65529
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: 65529
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: 65529
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>]]  [<access method>]  ^
                                          [[-opt] <filter/transform/display/trace option>]
McDP [USERID=<logon>] COMMAND=COPY NETWORK_LINK=<dblink> [{SOURCE|FROMUSER}=<source schema>]      ^
                                                         [{TARGET|TOUSER}=<target schema>]        ^
                                                         [ACCESS_METHOD=<method>]                 ^
                                                         [<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: Default source and target schemas are job owner one.
Note: ACCESS_METHOD is only available from version 12.2.

Note: Non-privileged user can copy only in their own schema.

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: Tue, 20 March 2018 05:42]

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: 65529
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 messageGo to next message
Michel Cadot
Messages: 65529
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).

Re: McDP: like expdp/impdp Data Pump programs and much more [message #668156 is a reply to message #656365] Fri, 09 February 2018 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65529
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here comes a major update (version 2018.02.09) with many improvements (and its drawbacks) (see Oracle documentation about new parameters, all 12c options, commands and parameters are supported (but one: TRACE) but not all are used by McDP):
  • Fix some bugs mostly in messages displayed and help
  • Check if ORACLE_HOME is set
  • Improve security model to earlier detect missing privileges when accessing others Data Pump jobs; in this case McDP returns the error "ORA-31631: privileges are required"
  • Rewrite and improve error handling to better display messages
  • Improve messages especially in FOLLOW command to display the execution step to help you use the new ABORT_STEP option (see below).

  • Support of version 12.1:
    • New execution options:
      • ABORT_STEP (see below)
      • ACCESS_METHOD (supported in all commands but only used by COPY)
      • COMPRESSION_ALGORYTHM (supported but not used by McDP)
      • DISABLE_ARCHIVE_LOGGING
      • KEEP_MASTER (supported in all commands but only used by COPY)
      • LOGTIME
      • MASTER_ONLY (supported but not used)
      • METRICS
    • New transforms:
      • INMEMORY
      • INMEMORY_CLAUSE
      • LOB_STORAGE
      • TABLE_COMPRESSION_CLAUSE
      • VIEWS_AS_TABLES (supported but not used)

  • Support of version 12.2:
    • New command:
      • STOP a worker (see example below)
    • New supported execution options:
      • ENABLE_NETWORK_COMPRESSION
      • GROUP_PARTITION_TABLE_DATA
      • TRUST_EXISTING_TABLE_PARTITIONS
      • VALIDATE_TABLE_DATA
      • VERIFY_STREAM_FORMAT
    • New transform: REMAP_DIRECTORY

  • New display option, DTFORMAT, which allows you to specify the date/time format; default is 'DD/MM/YYYY HH24:MI:SS.FF3' (see below)
  • STOP/SUSPEND, ALTER/MODIFY and COPY commands have a "step"/"ABORT_STEP" option to stop the job at a specific step (see below)
  • You can now specify a worker on the STOP/SUSPEND command (see below)
  • New McDP command: DISPLAY object information (see below)
Note: Outputs in the previous posts have not been updated so you'll see some minor differences with the current version, most of them are new information like the "phase" and "step" mentioned above as well as new lines like job GUID or last completed step for stopped jobs.


About new ABORT_STEP option

Oracle officially introduces a new option in 12.1 in the expdp/impdp command lines: ABORT_STEP. This option existed but was hidden in the previous versions, it allows to stop a job either just after the master table is created (abort_step=-1) or when the specified step is reached.
It is not clear what you can actually do with this, as stated in the script creating Data Pump package: "For testing only".

Here's an example of execution using this option (executed in version 11.2; lines are stripped to shorten the listing):
C:\>expdp michel/michel schemas=michel directory=my_dir parallel=2 dumpfile=dmp1.dmp,dmp2.dmp reuse_dumpfiles=y abort_step=20

Export: Release 11.2.0.4.0 - Production on Lun. Déc. 4 10:14:05 2017

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "MICHEL"."SYS_EXPORT_SCHEMA_07":  michel/******** schemas=michel directory=my_dir parallel=2 dumpfile=dmp1.dmp,dmp2.dmp reuse_dumpfiles=y abort_step=20
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.131 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
. . exported "MICHEL"."IPCOUNTRY"                        304.8 MB 3394569 rows
. . exported "MICHEL"."ORAFAQ_MEMBER_HIST"               36.67 MB 1113774 rows
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
<............................................................................>
. . exported "MICHEL"."SYS_EXPORT_SCHEMA_01"             1.519 MB    2665 rows
ORA-31697: aborting operation at process order number 20

Job "MICHEL"."SYS_EXPORT_SCHEMA_07" stopped due to fatal error at Lun. Déc. 4 10:16:16 2017 elapsed 0 00:02:04

And here's McDP FOLLOW output:
C:\>McDP michel/michel -f SYS_EXPORT_SCHEMA_07

McDP Utility by Michel Cadot: Version 2018.02.09

Copyright (c) Michel Cadot, 2016-2017. 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 myserver on 4-DÉC.-2017 10:14:15

04/12/2017 10:14:15.359 Following "MICHEL"."SYS_EXPORT_SCHEMA_07":
04/12/2017 10:14:15.361 EXPORT SCHEMA, DEFINING, Parallel 2, 1 attached sessions

  Server: MIKB2.myserver, platform: Microsoft Windows IA (32-bit)
  Start: 04/12/2017 10:14:13 - State: DEFINING - Abort step:
  GUID: D2F6B55394DC48E4A41369FC599557BB
  Client command: michel/ schemas=michel directory=my_dir parallel=2 dumpfile=dmp1.dmp,dmp2.dmp reuse_dumpfiles=y abort_
step=20
  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:\DMP1.DMP
    C:\DMP2.DMP

  Dump file(s):
    C:\DMP1.DMP 0MB
    C:\DMP2.DMP 0MB
  Start time: 04/12/2017 10:14:13
  Done      : 0% (0/0)
  Errors    : 0
  Parallel  : 2
  Worker   1: IDLE
  Worker   2: IDLE
04/12/2017 10:14:15.663 Start date: 04/12/2017 10:14:13

04/12/2017 10:14:15.984 Job phase 0
04/12/2017 10:14:16.640 Job phase 1
04/12/2017 10:14:16.640 Starting "MICHEL"."SYS_EXPORT_SCHEMA_07":  michel/******** schemas=michel directory=my_dir paral
lel=2 dumpfile=dmp1.dmp,dmp2.dmp reuse_dumpfiles=y abort_step=20
04/12/2017 10:14:17.218 Job phase 2
04/12/2017 10:14:17.218 Estimate in progress using BLOCKS method...
04/12/2017 10:14:26.406 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA (step -13.64)
04/12/2017 10:14:29.421 Total estimation using BLOCKS method: 1.131 GB
04/12/2017 10:14:30.359 Processing object type SCHEMA_EXPORT/USER (step -13.1)
04/12/2017 10:14:30.890 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT (step -13.2)
04/12/2017 10:14:31.031 Processing object type SCHEMA_EXPORT/ROLE_GRANT (step -13.3)
04/12/2017 10:14:31.093 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE (step -13.4)
04/12/2017 10:14:31.296 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA (step -13.5)
04/12/2017 10:15:00.359 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA (step -13.11)
04/12/2017 10:15:34.140 . . exported "MICHEL"."IPCOUNTRY"                        304.8 MB 3394569 rows (step 1)
04/12/2017 10:15:42.812 . . exported "MICHEL"."ORAFAQ_MEMBER_HIST"               36.67 MB 1113774 rows (step 2)
04/12/2017 10:15:42.812 51 percent done
04/12/2017 10:15:45.234 Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM (step -13.27)
<............................................................................>
04/12/2017 10:16:15.640 93 percent done
04/12/2017 10:16:16.390 . . exported "MICHEL"."SYS_EXPORT_SCHEMA_01"             1.519 MB    2665 rows (step 19)
04/12/2017 10:16:16.484 ORA-31697: aborting operation at process order number 20

04/12/2017 10:16:29.609 Job "MICHEL"."SYS_EXPORT_SCHEMA_07" stopped due to fatal error at Lun. Déc. 4 10:16:16 2017 elap
sed 0 00:02:04
04/12/2017 10:16:29.609 End of job: STOPPED
As you can see FOLLOW command now displays the "phases" and "steps" the job is.

Oracle expdp and impdp programs allow you to specify the step only when you start the job, with McDP you can modify or clear this step when you want using the SUSPEND/STOP or ALTER/MODIFY commands (see "help" in first post). An abort step value of 0 means clear the previous abort step request so that the job continues till its natural end.

Note: Modifying the abort step when the Data Pump job is running is quite undeterministic (sometimes the new abort is taken into account and sometimes not, it depends on the job state and how far is the abort step from the current one(s)); the higher is your version the more likely you will have the expected behavior.

Note: In case the specified abort step is currently executing or already completed, if your version is lower than 12c McDP will abort the command (not the job), if your version is at least 12c, it will ask you if you want to suspend the job. This is because in lower versions Data Pump code is not stable for this action and may at least make your job not restartable or loop on sending not dequeued messages allocating more and more space till the instance crashes.

Note: An aborted job can be restarted using the RESTART/CONTINUE command.

The safiest way to specify a new abort step after the job has been started is: stop the job (using SUSPEND/STOP command), specify a new abort step (using ALTER/MODIFY command) and restart the job (using RESTART/CONTINUE command).


About new STOP worker command

As you can see in the script creating the Data Pump packages the purpose of STOP_WORKER command is to "kill a worker process determined to be stuck or hung".
As an example, we will launch an export and will stop the worker number 1.

Export session:
C:\>expdp michel/michel@mikc2db1 schemas=michel dumpfile=mic1.dmp,mic2.dmp directory=my_dir parallel=2 reuse_dumpfiles=yes

Export: Release 12.2.0.1.0 - Production on Dim. Janv. 21 11:42:40 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "MICHEL"."SYS_EXPORT_SCHEMA_01":  michel/********@mikc2db1 schemas=michel dumpfile=mic1.dmp,mic2.dmp directory=my_dir parallel=2 reuse_dumpfiles=yes
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
. . exported "MICHEL"."T2"                               324.9 MB 2464448 rows
. . exported "MICHEL"."T3"                               324.9 MB 2464448 rows
. . exported "MICHEL"."T1"                               324.9 MB 2464448 rows
Master table "MICHEL"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MICHEL.SYS_EXPORT_SCHEMA_01 is:
  D:\ORACLE\SAVE\MIC1.DMP
  D:\ORACLE\SAVE\MIC2.DMP
Job "MICHEL"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at Dim. Janv. 21 11:43:51 2018 elapsed 0 00:01:00

McDP session:
C:\>McDP michel/michel@mikc2db1 -s SYS_EXPORT_SCHEMA_01 1

McDP Utility by Michel Cadot: Version 2018.01.09

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

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Advanced Analytics, Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKC2 pdb MIKC2DB1 instance mikc2 on myserver on 21-JANV.-2018 11:43:37

MICHEL.SYS_EXPORT_SCHEMA_01: EXECUTING
  Start time: 21/01/2018 11:42:51
  Mode      : SCHEMA
  State     : EXECUTING
  Done      : 0% (0K/650225K)
  Errors    : 0
  Parallel  : 2
  Worker   1: (DW00) EXECUTING
  Worker   2: (DW01) WORK WAITING
  
Trying to stop worker 1...
Done!
You will notice in the export log the following lines which show the DW00 worker was stopped:
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
As "skip" option was not given, the step the worker was executed at the moment it was stopped was restarted and the dump is complete.

Now if you use the "skip" option here's what you will see in the export log:
C:\>expdp michel/michel@mikc2db1 schemas=michel dumpfile=mic1.dmp,mic2.dmp directory=my_dir parallel=2 reuse_dumpfiles=yes

Export: Release 12.2.0.1.0 - Production on Lun. Janv. 22 09:45:45 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "MICHEL"."SYS_EXPORT_SCHEMA_01":  michel/********@mikc2db1 schemas=michel dumpfile=mic1.dmp,mic2.dmp directory=my_dir parallel=2 reuse_dumpfiles=yes
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "MICHEL"."T2"                               324.9 MB 2464448 rows
ORA-31693: Table data object "MICHEL"."T3" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-00448: normal completion of background process

. . exported "MICHEL"."T1"                               324.9 MB 2464448 rows
Master table "MICHEL"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MICHEL.SYS_EXPORT_SCHEMA_01 is:
  D:\ORACLE\SAVE\MIC1.DMP
  D:\ORACLE\SAVE\MIC2.DMP
Job "MICHEL"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Lun. Janv. 22 09:46:51 2018 elapsed 0 00:00:54
Unload of table T3 was aborted and skipped, it is not in the dump in the end.


About new DISPLAY object command

In 12.2 Oracle introduces a hidden feature, "get object information" (procedure get_object_info in dbms_datapump package), marks it as "API for internal users" in the script creating the Data Pump package; this procedure gives a status about objects in a Data Pump job (2 output parameters of 1 character each to decode).
McDP takes up the idea but gives much more complete and clear information and back-ports the feature to the very first version of Data Pump.

See examples in next post.


About new DTFORMAT option

This new option allows you to choose the format you want McDP use to display the date and timestamp data. By default, the format is "DD/MM/YYYY HH24:MI:SS.FF3". McDP does not use NLS_DATA_LANGUAGE (or similar) environment variable.

For example, the following output with default date format:
C:\>McDP michel/michel -w "MICHEL"."SYS_IMPORT_SCHEMA_01"

McDP Utility by Michel Cadot: Version 2018.02.09

Copyright (c) Michel Cadot, 2016-2018. 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 myserver on 1-FÉVR.-2018 15:20:09

"MICHEL"."SYS_IMPORT_SCHEMA_01" at 01/02/2018 15:20:09.468

IMPORT SCHEMA, NOT RUNNING, parallel 0, 0 attached sessions
Server: MIKB2.myserver, platform: Microsoft Windows IA (32-bit)
Start: 18/08/2016 16:32:01 - State: STOPPED - Abort step: 0
GUID: 25D95DD621CD45F28313D66105D626C0
Message: Job "MICHEL"."SYS_IMPORT_SCHEMA_01" stopped due to fatal error at Lun. Août 1 18:46:26 2016 elapsed 0 00:00:16
Last completed step: 24 (INDEX: SCOTT2.PK_EMP) on 01/08/2016 18:46:23

Master work:
  SCHEMA_EXPORT/USER                                 Start: 01/08/2016 18:46:14, End: 01/08/2016 18:46:14
  SCHEMA_EXPORT/ROLE_GRANT                           Start: 01/08/2016 18:46:14, End: 01/08/2016 18:46:14
  SCHEMA_EXPORT/DEFAULT_ROLE                         Start: 01/08/2016 18:46:14, End: 01/08/2016 18:46:14
  SCHEMA_EXPORT/TABLESPACE_QUOTA                     Start: 01/08/2016 18:46:14, End: 01/08/2016 18:46:14
  SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA            Start: 01/08/2016 18:46:14, End: 01/08/2016 18:46:14
  SCHEMA_EXPORT/TABLE/TABLE                          Start: 01/08/2016 18:46:14, End: 01/08/2016 18:46:15
  SCHEMA_EXPORT/TABLE/TABLE_DATA                     Start: 01/08/2016 18:46:15, End: in progress...
  SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Start: 01/08/2016 18:46:21, End: 01/08/2016 18:46:21
  SCHEMA_EXPORT/TABLE/INDEX/INDEX                    Start: 01/08/2016 18:46:21, End: 01/08/2016 18:46:23
  SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT          Start: 01/08/2016 18:46:24, 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/TABLE_DATA, 6 items:
    SCOTT2.T,SCOTT2.BONUS,SCOTT2.SALGRADE,SCOTT2.EMP,SCOTT2.DEPT,SCOTT2.T2
- SCHEMA_EXPORT/TABLE/INDEX/INDEX, 2 items:
    SCOTT2.PK_DEPT,SCOTT2.PK_EMP

Worker 1: (DW00) Undefined
Becomes with dates in ISO-8601 format:
C:\>McDP michel/michel -w "MICHEL"."SYS_IMPORT_SCHEMA_01" dtformat=YYYY-MM-DD\"T\"HH24:MI:SS.FF3TZH:TZM

McDP Utility by Michel Cadot: Version 2018.02.09

Copyright (c) Michel Cadot, 2016-2018. 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 myserver on 1-FÉVR.-2018 15:21:03

"MICHEL"."SYS_IMPORT_SCHEMA_01" at 2018-02-01T15:21:03.296+01:00

IMPORT SCHEMA, NOT RUNNING, parallel 0, 0 attached sessions
Server: MIKB2.myserver, platform: Microsoft Windows IA (32-bit)
Start: 2016-08-18T16:32:01 - State: STOPPED - Abort step: 0
GUID: 25D95DD621CD45F28313D66105D626C0
Message: Job "MICHEL"."SYS_IMPORT_SCHEMA_01" stopped due to fatal error at Lun. Août 1 18:46:26 2016 elapsed 0 00:00:1
Last completed step: 24 (INDEX: SCOTT2.PK_EMP) on 2016-08-01T18:46:23

Master work:
  SCHEMA_EXPORT/USER                                 Start: 2016-08-01T18:46:14, End: 2016-08-01T18:46:14
  SCHEMA_EXPORT/ROLE_GRANT                           Start: 2016-08-01T18:46:14, End: 2016-08-01T18:46:14
  SCHEMA_EXPORT/DEFAULT_ROLE                         Start: 2016-08-01T18:46:14, End: 2016-08-01T18:46:14
  SCHEMA_EXPORT/TABLESPACE_QUOTA                     Start: 2016-08-01T18:46:14, End: 2016-08-01T18:46:14
  SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA            Start: 2016-08-01T18:46:14, End: 2016-08-01T18:46:14
  SCHEMA_EXPORT/TABLE/TABLE                          Start: 2016-08-01T18:46:14, End: 2016-08-01T18:46:15
  SCHEMA_EXPORT/TABLE/TABLE_DATA                     Start: 2016-08-01T18:46:15, End: in progress...
  SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Start: 2016-08-01T18:46:21, End: 2016-08-01T18:46:21
  SCHEMA_EXPORT/TABLE/INDEX/INDEX                    Start: 2016-08-01T18:46:21, End: 2016-08-01T18:46:23
  SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT          Start: 2016-08-01T18:46:24, End: in progress...

All workers, tasks 100% done:
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/TABLE_DATA, 6 items:
    SCOTT2.T,SCOTT2.BONUS,SCOTT2.SALGRADE,SCOTT2.EMP,SCOTT2.DEPT,SCOTT2.T2
- SCHEMA_EXPORT/TABLE/INDEX/INDEX, 2 items:
    SCOTT2.PK_DEPT,SCOTT2.PK_EMP

Worker 1: (DW00) Undefined

Note: McDP uses the following Oracle datetime datatypes: TIMESTAMP WITH TIME ZONE and DATE. When DATE data are displayed subsecond and time zone parts of the format are ignored (as in the above example).

[Updated on: Tue, 20 March 2018 05:42]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #668157 is a reply to message #668156] Fri, 09 February 2018 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 65529
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

DISPLAY object information (new in version 2018.02.09)

This new McDP command allows you to get information and follow one or several specific objects during the export or import.

Its syntax is:
McDP [<logon>] { -d | --display } [<schema>.]<dp job> [<schema>.]<object name>[:<type>][,...]
McDP [USERID=<logon>] COMMAND=DISPLAY OBJECT=<object>[,<object>...][LINESIZE=<n>][DTFORMAT=<format>]
Where <object> syntax is [<schema>.]<object name>[:<type>]
Default schema is job's one; default type is TABLE.

Note: Use SYS as schema for object with no actual owner like DIRECTORY, TABLESPACE, USER...
Note: For objects in two parts like PACKAGE, giving PACKAGE as type returns also information about its BODY whereas giving "PACKAGE BODY" returns only information about this later one.

Example

We will execute the following export command and during this export we will follow the status of the 3 tables: T1, T2 and T3.
C:\>expdp michel/michel@mikc2db1 schemas=michel dumpfile=mic.dmp directory=my_dir reuse_dumpfiles=yes keep_master=y

Export: Release 12.2.0.1.0 - Production on Jeu. Févr. 1 11:28:01 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "MICHEL"."SYS_EXPORT_SCHEMA_01":  michel/********@mikc2db1 schemas=michel dumpfile=mic.dmp directory=my_dir reuse_dumpfiles=yes keep_master=y
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "MICHEL"."T2"                               324.9 MB 2464448 rows
. . exported "MICHEL"."T3"                               324.9 MB 2464448 rows
. . exported "MICHEL"."T1"                               324.9 MB 2464448 rows
Master table "MICHEL"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MICHEL.SYS_EXPORT_SCHEMA_01 is:
  D:\ORACLE\SAVE\MIC.DMP
Job "MICHEL"."SYS_EXPORT_SCHEMA_01" successfully completed at Jeu. Févr. 1 11:29:12 2018 elapsed 0 00:01:00

The first step of a Data Pump job, after having created its work table, is the "DEFINING" step which fills this master table with the tasks to execute. At the beginning of this step if you try to find the status of the tables you will have the following output:
C:\>McDP michel/michel@mikc2db1 -d SYS_EXPORT_SCHEMA_01 t1,t2,t3

McDP Utility by Michel Cadot: Version 2018.02.09

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

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Advanced Analytics, Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKC2 pdb MIKC2DB1 instance mikc2 on myserver on 1-FÉVR.-2018 11:28:15

--> No objects found.

Then, as this step goes along, you will have more lines for each table; each line describes an operation for the table: estimating the size, creating the table (or its CREATE TABLE statement), exporting/importing data, indexing, creating the constraints, gathering/filling the statistics...
C:\>McDP michel/michel@mikc2db1 -d SYS_EXPORT_SCHEMA_01 t1,t2,t3

McDP Utility by Michel Cadot: Version 2018.02.09

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

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Advanced Analytics, Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKC2 pdb MIKC2DB1 instance mikc2 on myserver on 1-FÉVR.-2018 11:28:39

Object information at 01/02/2018 11:28:39.852

"MICHEL"."T1" (Table)
=====================
  operation: CREATE TABLE - state: ESTIMATE - status: CURRENT
    estimated size: 10,017KB
  operation: TABLE STATISTICS - state: RETRIEVE - status: CURRENT

"MICHEL"."T2" (Table)
=====================
  operation: CREATE TABLE - state: ESTIMATE - status: CURRENT
    estimated size: 320,104KB
  operation: TABLE STATISTICS - state: RETRIEVE - status: CURRENT

"MICHEL"."T3" (Table)
=====================
  operation: CREATE TABLE - state: ESTIMATE - status: CURRENT
    estimated size: 320,104KB
  operation: TABLE STATISTICS - state: RETRIEVE - status: CURRENT
As soon as the step is reached the status is CURRENT and stays at this value even if the step is completed (Data Pump seldom updates the status on completion) unless an error occurs.
A state of "ESTIMATE" means Data Pump is currently estimating the size of the object.
A state of "RETRIEVE" means Data Pump is currently retrieving the object (or has retrieved it) and/or its information.

C:\>McDP michel/michel@mikc2db1 -d SYS_EXPORT_SCHEMA_01 t1,t2,t3

McDP Utility by Michel Cadot: Version 2018.02.09

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

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Advanced Analytics, Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKC2 pdb MIKC2DB1 instance mikc2 on myserver on 1-FÉVR.-2018 11:28:51

Object information at 01/02/2018 11:28:51.786

"MICHEL"."T1" (Table)
=====================
  operation: CREATE TABLE - state: RETRIEVE - status: CURRENT
  operation: TABLE DATA - state: ESTIMATE - status: CURRENT
    estimated size: 10,017KB
  operation: TABLE STATISTICS - state: RETRIEVE - status: CURRENT

"MICHEL"."T2" (Table)
=====================
  operation: CREATE TABLE - state: RETRIEVE - status: CURRENT
  operation: TABLE DATA - state: ESTIMATE - status: CURRENT
    estimated size: 320,104KB
  operation: TABLE STATISTICS - state: RETRIEVE - status: CURRENT

"MICHEL"."T3" (Table)
=====================
  operation: CREATE TABLE - state: RETRIEVE - status: CURRENT
  operation: TABLE DATA - state: ESTIMATE - status: CURRENT
    estimated size: 320,104KB
  operation: TABLE STATISTICS - state: RETRIEVE - status: CURRENT
Here our tables have no indexes, no constraints so there are only 3 operations: CREATE TABLE (retrieve CREATE TABLE statement), TABLE DATA (export table data) and TABLE STATISTICS (retrieve table statistics).

C:\>McDP michel/michel@mikc2db1 -d SYS_EXPORT_SCHEMA_01 t1,t2,t3

McDP Utility by Michel Cadot: Version 2018.02.09

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

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Advanced Analytics, Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKC2 pdb MIKC2DB1 instance mikc2 on myserver on 1-FÉVR.-2018 11:29:03

Object information at 01/02/2018 11:29:03.892

"MICHEL"."T1" (Table)
=====================
  operation: CREATE TABLE - state: RETRIEVE - status: CURRENT
  operation: TABLE DATA - state: ESTIMATE - status: CURRENT
    estimated size: 10,017KB
  operation: TABLE STATISTICS - state: RETRIEVE - status: CURRENT

"MICHEL"."T2" (Table)
=====================
  operation: CREATE TABLE - state: RETRIEVE - status: CURRENT
  operation: TABLE DATA - state: RETRIEVE - status: COMPLETE
    start time: 01/02/2018 11:29:00 - completion time: 01/02/2018 11:29:03 - elapsed: 3s
    completed rows: 2464448 - estimated size: 320,104KB
  operation: TABLE STATISTICS - state: RETRIEVE - status: CURRENT

"MICHEL"."T3" (Table)
=====================
  operation: CREATE TABLE - state: RETRIEVE - status: CURRENT
  operation: TABLE DATA - state: ESTIMATE - status: CURRENT
    start time: 01/02/2018 11:29:03
    estimated size: 320,104KB
  operation: TABLE STATISTICS - state: RETRIEVE - status: CURRENT
At this point the export of table T2 data is completed (this is one of the rare cases you will see a status of COMPLETE) and those of table T3 has started.
For this operation (TABLE DATA) you have the start, end and elapsed times of the operation and the number of rows exported/imported.

At the end of the job you have:
C:\>McDP michel/michel@mikc2db1 -d SYS_EXPORT_SCHEMA_01 t1,t2,t3


McDP Utility by Michel Cadot: Version 2018.02.09

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

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Advanced Analytics, Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKC2 pdb MIKC2DB1 instance mikc2 on myserver on 1-FÉVR.-2018 11:29:15

Object information at 01/02/2018 11:29:15.997

"MICHEL"."T1" (Table)
=====================
  operation: CREATE TABLE - state: RETRIEVE - status: CURRENT
  operation: TABLE DATA - state: RETRIEVE - status: COMPLETE
    start time: 01/02/2018 11:29:06 - completion time: 01/02/2018 11:29:10 - elapsed: 4s
    completed rows: 2464448 - estimated size: 10,017KB
  operation: TABLE STATISTICS - state: RETRIEVE - status: CURRENT

"MICHEL"."T2" (Table)
=====================
  operation: CREATE TABLE - state: RETRIEVE - status: CURRENT
  operation: TABLE DATA - state: RETRIEVE - status: COMPLETE
    start time: 01/02/2018 11:29:00 - completion time: 01/02/2018 11:29:03 - elapsed: 3s
    completed rows: 2464448 - estimated size: 320,104KB
  operation: TABLE STATISTICS - state: RETRIEVE - status: CURRENT

"MICHEL"."T3" (Table)
=====================
  operation: CREATE TABLE - state: RETRIEVE - status: CURRENT
  operation: TABLE DATA - state: RETRIEVE - status: COMPLETE
    start time: 01/02/2018 11:29:03 - completion time: 01/02/2018 11:29:06 - elapsed: 3s
    completed rows: 2464448 - estimated size: 320,104KB
  operation: TABLE STATISTICS - state: RETRIEVE - status: CURRENT

[Updated on: Tue, 20 March 2018 05:41]

Report message to a moderator

Re: McDP: like expdp/impdp Data Pump programs and much more [message #668872 is a reply to message #668157] Tue, 20 March 2018 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65529
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

New version (2018.03.20) with the following modifications:
  • Fix bugs in ALTER/MODIFY command introduced in previous version with the "step/ABORT_STEP" option
  • GRANTS parameter of USER command now support "+" as delimiter in addition to ","
  • COPY command no more copies McDP objects if they exist in the source schema
  • Support of TRACE option (see below)
  • New COPY command: copy tables and views in the same or another schema with or without renaming (see next post)
TRACE option

Oracle introduced a new command in expdp/impdp client program: TRACE. As displayed with the HELP command of these programs:
TRACE
Set trace/debug flags for the current job.
No information on this command anywhere, it is even not mentioned in the documentation (Database Utilities) so I will explain it a little bit.
The value for this TRACE command accepts a string of 8 hexadecimal figures which is a 32-bit mask each bit representing an action or a trace.

Note: In 10g and 11g, setting trace is only allowed when defining the job that is before it is actually executing (when you create a Data Pump job (with expdp/impdp job or other client like McDP) you first create the master table and fill it with the parameters and description of operations to execute, this is the "defining" step, then you launch the execution of the actual work).
Note: A similar TRACE parameter exists but is hidden in the command line since 10g (in 10gR1 and 10gR2 up to patchset 10.2.0.3 or 10.2.0.4, only values 0 (OFF) and 1 (ON) were known which activates standard SQL trace).

This new version of McDP, let you set/clear the trace/debug flag as a parameter on all its commands and more specifically on ALTER/MODIFY command if you want to set it for a specific already started job.
This parameter can be given either with this 8 hexadecimal figure string or with more clear character strings (separated by "+" if you want to set several options, see the help of McDP in the first post of this topic for a list of them). As some bits are dangerous, if you give an hexadecimal string, McDP will clear these bits and set some others that are mandatory to actually work if you didn't set them.
For 10g and 11g versions, modifying the trace level using ALTER/MODIFY does nothing as this parameter is not forwarded to other processes of the job by the Data Pump job when it is no more in the DEFINING state.

Note: You must have the EXP_FULL_DATABASE or IMP_FULL_DATABASE role to set or change the trace/debug level of Data Pump jobs, even yours. If you do it without those roles you have no error message but the Data Pump engine ignores the setting.

This option is interesting for Oracle support to debug but it is also interesting if you want to see and learn how Data Pump works.
Here's an example with McDP DDL command we already executed above adding the traces for master and worker:
C:\> McDP michel/michel -ddl my_dir:michel.sql mylink schema michel ^
More? -opt include=user,role_grant,system_grant show=y trace=master+worker

McDP Utility by Michel Cadot: Version 2018.03.20

Copyright (c) Michel Cadot, 2016-2018. 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 myserver on 9-MARS-2018 10:23:34

09/03/2018 10:23:34.234 Getting DDL from "MYLINK"
09/03/2018 10:23:34.234 Defining the job
09/03/2018 10:23:39.890 Add the SQL file
09/03/2018 10:23:39.906 Add the object list
09/03/2018 10:23:39.937 Trace set to 00480B00
09/03/2018 10:23:40.031 Result file will be MY_DIR/michel.sql
09/03/2018 10:23:40.968 Data Pump job started successfully
09/03/2018 10:23:41.000 Starting "MICHEL"."SYS_SQL_FILE_SCHEMA_02":
  McDP MICHEL: get DDL from MYLINK for 'MICHEL'
  SQL FILE: MY_DIR/michel.sql
  SCHEMA_LIST="'MICHEL'"
  TRACE=00480B00
  INCLUDE_PATH_EXPR='USER'
  INCLUDE_PATH_EXPR='ROLE_GRANT'
  INCLUDE_PATH_EXPR='SYSTEM_GRANT'
09/03/2018 10:23:46.593 Processing object type SCHEMA_EXPORT/USER
09/03/2018 10:23:46.796 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
09/03/2018 10:23:47.296 Processing object type SCHEMA_EXPORT/ROLE_GRANT
09/03/2018 10:23:48.234 Job "MICHEL"."SYS_SQL_FILE_SCHEMA_02" successfully completed at Ven. Mars 9 10:23:47 2018 elapse
d 0 00:00:13
09/03/2018 10:23:48.281 End of data pump job, status COMPLETED
09/03/2018 10:23:48.281 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;
 GRANT "MY_READONLY" TO "MICHEL" WITH ADMIN OPTION;
 GRANT "ROLE_APP" TO "MICHEL" WITH ADMIN OPTION;
========================================================================================================================
09/03/2018 10:23:48.296 Result file MY_DIR/michel.sql kept

Master trace
Worker trace
Re: McDP: like expdp/impdp Data Pump programs and much more [message #668873 is a reply to message #668872] Tue, 20 March 2018 05:47 Go to previous message
Michel Cadot
Messages: 65529
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

COPY tables and views (new in version 2018.03.20)

This new McDP command allows you to copy tables and views from a schema to another or the same one, possibly renaming them or not (see Data Pump engine restrictions at the bottom of the page).
When copying views these ones are converted to tables (see VIEWS_AS_TABLES option in a previous post and in documentation).

The syntaxes of this command are:
McDP [<logon>] <db link> <table/view>[,...] [<source schema> [<target schema>]] [<access method>]  ^
                                            [[-opt] <filter/transform/display/trace option>]
McDP [USERID=<logon>] COMMAND=COPY NETWORK_LINK=<dblink> TABLES=<table/view>[,...]  ^
                                   [{SOURCE|FROMUSER}=<source schema>]              ^
                                   [{TARGET|TOUSER}=<target schema>]                ^
                                   [ACCESS_METHOD=<method>]                         ^
                                   [<option>=<value>]... 
Where <table/view> syntax is [<source schema.>]<table/view name>[:[<target schema.>]<new name>]
Note: Default source and target schemas are job owner one.
Note: ACCESS_METHOD is only available from version 12.2.

Note: Non-privileged user can copy only in their own schema.

Example

In this example we will copy 2 tables and a view from SCOTT to SCOTT2 and a table from MICHEL to MICHEL2 schema. tables X will be renamed to X2, view will be rename from VEMP to EMP_NEW.
Note that the original view definition refers to tables that are not copied but this does not prevent it from being converted into a table in the target schema.
First have a look at the objects to be copied:
SQL> set lines 125
SQL> col object_name     format a11
SQL> col table_name      format a10
SQL> col constraint_name format a15
SQL> col constraint_type format a15
SQL> select object_name, object_type from dba_objects
  2  where owner='SCOTT' and ( object_name like '%EMP%' or object_name like '%DEPT%' )
  3  order by 1
  4  /
OBJECT_NAME OBJECT_TYPE
----------- -----------------------
DEPT        TABLE
EMP         TABLE
EMP_ENAME   INDEX
PK_DEPT     INDEX
PK_EMP      INDEX
VEMP        VIEW

6 rows selected.

SQL> select table_name, constraint_name,
  2         decode(constraint_type,
  3                'P', 'Primary key',
  4                'R', 'Foreign key')
  5           constraint_type
  6  from dba_constraints
  7  where owner='SCOTT' and table_name in ('EMP','DEPT')
  8  order by 1, 3 desc, 2
  9  /
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE
---------- --------------- ---------------
DEPT       PK_DEPT         Primary key
EMP        PK_EMP          Primary key
EMP        FK_EMP_DEPTNO   Foreign key

3 rows selected.

SQL> select text from dba_views where owner='SCOTT' and view_name='VEMP';
TEXT
------------------------------------------------------------------------------------------------------------------------
with
  sgrade as  (
    select grade, losal, hisal from salgrade
    union all
    select 0, 0, min(losal)-1 from salgrade
    union all
    select max(grade)+1, max(hisal)+1, max(hisal)*1000000+999999 from salgrade
  )
select e.empno, e.ename, e.job, e2.ename mgr, e.hiredate, e.sal, e.comm,
       s.grade, d.dname, d.loc
from emp e, dept d, sgrade s, emp e2
where d.deptno = e.deptno
  and e.sal between s.losal and s.hisal
  and e2.empno (+) = e.mgr

1 row selected.

SQL> select * from scott.vemp;
     EMPNO ENAME      JOB       MGR        HIREDATE           SAL       COMM      GRADE DNAME          LOC
---------- ---------- --------- ---------- ----------- ---------- ---------- ---------- -------------- -------------
      7839 KING       PRESIDENT            17-NOV-1981       5000                     5 ACCOUNTING     NEW YORK
      7902 FORD       ANALYST   JONES      03-DEC-1981       3000                     4 RESEARCH       DALLAS
      7788 SCOTT      ANALYST   JONES      19-APR-1987       3000                     4 RESEARCH       DALLAS
      7566 JONES      MANAGER   KING       02-APR-1981       2975                     4 RESEARCH       DALLAS
      7698 BLAKE      MANAGER   KING       01-MAY-1981       2850                     4 SALES          CHICAGO
      7782 CLARK      MANAGER   KING       09-JUN-1981       2450                     4 ACCOUNTING     NEW YORK
      7499 ALLEN      SALESMAN  BLAKE      20-FEB-1981       1600        300          3 SALES          CHICAGO
      7844 TURNER     SALESMAN  BLAKE      08-SEP-1981       1500          0          3 SALES          CHICAGO
      7934 MILLER     CLERK     CLARK      23-JAN-1982       1300                     2 ACCOUNTING     NEW YORK
      7654 MARTIN     SALESMAN  BLAKE      28-SEP-1981       1250       1400          2 SALES          CHICAGO
      7521 WARD       SALESMAN  BLAKE      22-FEB-1981       1250        500          2 SALES          CHICAGO
      7876 ADAMS      CLERK     SCOTT      23-MAY-1987       1100                     1 RESEARCH       DALLAS
      7900 JAMES      CLERK     BLAKE      03-DEC-1981        950                     1 SALES          CHICAGO
      7369 SMITH      CLERK     FORD       17-DEC-1980        800                     1 RESEARCH       DALLAS

14 rows selected.

SQL> select * from michel.t;
DT
-------------------
19/03/2018 10:29:44

1 row selected.
Then check the target schemas:
SQL> select object_name, object_type from dba_objects
  2  where owner='SCOTT2' and ( object_name like '%EMP%' or object_name like '%DEPT%' )
  3  order by 1
  4  /

no rows selected

SQL> select * from michel2.t2;
select * from michel2.t2
                      *
ERROR at line 1:
ORA-00942: table or view does not exist
Now copy:
C:\>McDP michel/michel@mikc2db1                                     ^
More?      -cp mylink                                               ^
More?          emp:emp2,dept:dept2,vemp:emp_new,michel.t:michel2.t2 ^
More?          scott scott2

McDP Utility by Michel Cadot: Version 2018.03.20

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

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Advanced Analytics, Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKC2 pdb MIKC2DB1 instance mikc2 on myserver on 20-MARS-2018 10:26:07

20/03/2018 10:26:07.029 Copying tables using "MYLINK", default schemas: source "SCOTT", target "SCOTT2"
20/03/2018 10:26:07.029 Defining the job
20/03/2018 10:26:30.507 Data Pump job started successfully
20/03/2018 10:26:30.538 Starting "MICHEL"."SYS_IMPORT_TABLE_01":
  McDP MICHEL: copy tables using MYLINK
  REMAP_SCHEMA="MICHEL":"MICHEL2"
  REMAP_SCHEMA="SCOTT":"SCOTT2"
  TABLE_FILTER="SCOTT"."EMP","SCOTT"."DEPT","MICHEL"."T"
  REMAP_TABLE="SCOTT"."EMP":"EMP2"
  REMAP_TABLE="SCOTT"."DEPT":"DEPT2"
  REMAP_TABLE="MICHEL"."T":"T2"
  VIEWS_AS_TABLES="SCOTT"."VEMP"
  REMAP_TABLE="SCOTT"."VEMP":"EMP_NEW"
20/03/2018 10:26:41.474 Estimate in progress using BLOCKS method...
20/03/2018 10:26:43.392 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
20/03/2018 10:26:44.016 Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
20/03/2018 10:26:44.063 Total estimation using BLOCKS method: 208 KB
20/03/2018 10:26:44.640 Processing object type TABLE_EXPORT/TABLE/TABLE
20/03/2018 10:26:45.218 Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
20/03/2018 10:26:57.401 . . imported "MICHEL2"."T2"                                   1 rows
20/03/2018 10:27:09.148 . . imported "SCOTT2"."DEPT2"                                 4 rows
20/03/2018 10:27:09.148 30 percent done
20/03/2018 10:27:32.408 . . imported "SCOTT2"."EMP2"                                 14 rows
20/03/2018 10:27:32.408 61 percent done
20/03/2018 10:27:32.938 . . imported "SCOTT2"."EMP_NEW"                              14 rows
20/03/2018 10:27:32.938 92 percent done
20/03/2018 10:27:33.219 99 percent done
20/03/2018 10:27:33.219 Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
20/03/2018 10:27:34.857 Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
20/03/2018 10:27:35.122 Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
20/03/2018 10:27:35.340 Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
20/03/2018 10:27:35.434 Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
20/03/2018 10:27:35.434 Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
20/03/2018 10:27:38.211 Job "MICHEL"."SYS_IMPORT_TABLE_01" successfully completed at Mar. Mars 20 10:27:37 2018 elapsed
0 00:01:30
20/03/2018 10:27:38.211 End of data pump job, status COMPLETED

Verify:
SQL> select object_name, object_type from dba_objects
  2  where owner='SCOTT2' and ( object_name like '%EMP%' or object_name like '%DEPT%' )
  3  order by 1
  4  /
OBJECT_NAME OBJECT_TYPE
----------- -----------------------
DEPT2       TABLE
EMP2        TABLE
EMP_ENAME   INDEX
EMP_NEW     TABLE
PK_DEPT     INDEX
PK_EMP      INDEX

6 rows selected.

SQL> select table_name, constraint_name,
  2         decode(constraint_type,
  3                'P', 'Primary key',
  4                'R', 'Foreign key')
  5           constraint_type
  6  from dba_constraints
  7  where owner='SCOTT2' and table_name in ('EMP2','DEPT2')
  8  order by 1, 2
  9  /
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE
---------- --------------- ---------------
DEPT2      PK_DEPT         Primary key
EMP2       FK_EMP_DEPTNO   Foreign key
EMP2       PK_EMP          Primary key

3 rows selected.

SQL> select * from scott2.emp_new;
     EMPNO ENAME      JOB       MGR        HIREDATE           SAL       COMM      GRADE DNAME          LOC
---------- ---------- --------- ---------- ----------- ---------- ---------- ---------- -------------- -------------
      7788 SCOTT      ANALYST   JONES      19-APR-1987       3000                     4 RESEARCH       DALLAS
      7902 FORD       ANALYST   JONES      03-DEC-1981       3000                     4 RESEARCH       DALLAS
      7900 JAMES      CLERK     BLAKE      03-DEC-1981        950                     1 SALES          CHICAGO
      7521 WARD       SALESMAN  BLAKE      22-FEB-1981       1250        500          2 SALES          CHICAGO
      7654 MARTIN     SALESMAN  BLAKE      28-SEP-1981       1250       1400          2 SALES          CHICAGO
      7844 TURNER     SALESMAN  BLAKE      08-SEP-1981       1500          0          3 SALES          CHICAGO
      7934 MILLER     CLERK     CLARK      23-JAN-1982       1300                     2 ACCOUNTING     NEW YORK
      7876 ADAMS      CLERK     SCOTT      23-MAY-1987       1100                     1 RESEARCH       DALLAS
      7782 CLARK      MANAGER   KING       09-JUN-1981       2450                     4 ACCOUNTING     NEW YORK
      7566 JONES      MANAGER   KING       02-APR-1981       2975                     4 RESEARCH       DALLAS
      7698 BLAKE      MANAGER   KING       01-MAY-1981       2850                     4 SALES          CHICAGO
      7369 SMITH      CLERK     FORD       17-DEC-1980        800                     1 RESEARCH       DALLAS
      7839 KING       PRESIDENT            17-NOV-1981       5000                     5 ACCOUNTING     NEW YORK

14 rows selected.

SQL> select * from michel2.t2;
DT
-------------------
19/03/2018 10:29:44

1 row selected.

Data Pump engine restrictions:
* In 10g/11g versions you cannot rename the tables (Data Pump engine bug); you cannot copy views as VIEWS_AS_TABLES option which is internally used was introduced in 12c.
* In version 12.1, if you rename the tables the indexes and constraints cannot be recreated (Data Pump engine bug).

McDP program restrictions:
* The program does not work for tables and views containing a dot (.) in their name (even if the name is enclosed between double-quotes).
* If you use the positional syntax and copy only one table without wanting to rename it, you must either rename it with same name or explicitly name the default source and target schemas; in this case (copy of a lone table), it is better to use the keyword syntax instead.

[Updated on: Thu, 22 March 2018 15:46]

Report message to a moderator

Previous Topic: McOptions: a program which allows to know which options are used in a database
Next Topic: Required Skills for the Job of SQL Developer / PL/SQL Developer
Goto Forum:
  


Current Time: Mon Jun 18 03:24:17 CDT 2018