Orace 12.1 datapump (expdp) , filesize, dumpfile use case and to knowabout

Excercise goal: Try to export a partitioned table in parallel using filesize and dumpfile with %U flag.

Identify a partitioned table:

SQL> select owner,table_name,tablespace_name,partitioned from dba_tables where table_name='TRAIN_SCHEDULE_LIST';

OWNER      TABLE_NAME                TABLESPACE_NAME                PAR
---------- ------------------------- ------------------------------ ---
SCOTT      TRAIN_SCHEDULE_LIST                                      YES



SQL> select sum(bytes)/1024/1024 from dba_segments where owner='SCOTT' and segment_name='TRAIN_SCHEDULE_LIST';

SUM(BYTES)/1024/1024
--------------------
            434.0625

SQL> select count(1) from dba_tab_partitions where table_name='TRAIN_SCHEDULE_LIST';

  COUNT(1)
----------
     11114

======

parfile: 

trnschlist.par
job_name=trnschlist
filesize=1M
parallel=12
directory=testexp_dir
dumpfile=trnschlist_1%U.dmp,trnschlist_2%U.dmp,trnschlist_3%U.dmp,trnschlist_4%U.dmp,trnschlist_5%U.dmp
logfile=trnschlist.log
exclude=statistics
tables=TRAIN_SCHEDULE_LIST

======

progress of the expdp :

[oracle@db12c DPDUMP]$ head -100 nh_trnschlist.out
The Oracle base remains unchanged with value /u01/app/oracle

Export: Release 12.1.0.1.0 - Production on Tue Oct 23 12:08:27 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SCOTT"."TRNSCHLIST":  scott/******** parfile=trnschlist.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 434.0 MB
. . exported "SCOTT"."TRAIN_SCHEDULE_LIST":"TRN_22636"   10.69 KB      10 rows
. . exported "SCOTT"."TRAIN_SCHEDULE_LIST":"TRN_12933"   10.75 KB      11 rows
. . exported "SCOTT"."TRAIN_SCHEDULE_LIST":"TRN_18108"   10.89 KB      13 rows
. . exported "SCOTT"."TRAIN_SCHEDULE_LIST":"TRN_10103"   11.54 KB      20 rows
. . exported "SCOTT"."TRAIN_SCHEDULE_LIST":"TRN_12656"   13.64 KB      42 rows
. . exported "SCOTT"."TRAIN_SCHEDULE_LIST":"TRN_22637"   12.88 KB      31 rows
. . exported "SCOTT"."TRAIN_SCHEDULE_LIST":"TRN_10104"   11.55 KB      20 rows
. . exported "SCOTT"."TRAIN_SCHEDULE_LIST":"TRN_12657"   10.78 KB       8 rows
. . exported "SCOTT"."TRAIN_SCHEDULE_LIST":"TRN_12934"   10.64 KB      10 rows
======
adding additional dumpfile on the fly...

Export> add_file=trnschlist_6%U.dmp

Export> status

Job: TRNSCHLIST
  Operation: EXPORT
  Mode: TABLE
  State: EXECUTING
  Bytes Processed: 66,157,880
  Percent Done: 22
  Current Parallelism: 12
  Job Error Count: 0
..
  Dump File: /oradata_1/RMN01TST/DPDUMP/trnschlist_601.dmp        << new dumpfile
    bytes written: 4,653,056
  Dump File: /oradata_1/RMN01TST/DPDUMP/trnschlist_511.dmp
    size: 1,048,576
    bytes written: 1,048,576

Worker 1 Status:
  Instance ID: 1
  Instance name: rmn01tst
  Host name: db12c.oracle.com
  State: EXECUTING
  Object Schema: SCOTT
  Object Name: TRAIN_SCHEDULE_LIST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: TRN_72205
  Completed Objects: 39
  Total Objects: 11,114
  Worker Parallelism: 1

Worker 2 Status:
  Instance ID: 1
  Instance name: rmn01tst
  Host name: db12c.oracle.com
  State: EXECUTING
  Object Schema: SCOTT
  Object Name: TRAIN_SCHEDULE_LIST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: TRN_64018
  Completed Objects: 624
  Total Objects: 11,114
  Worker Parallelism: 1

Worker 3 Status:
  Instance ID: 1
  Instance name: rmn01tst
  Host name: db12c.oracle.com
  State: EXECUTING
  Object Schema: SCOTT
  Object Name: TRAIN_SCHEDULE_LIST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: TRN_54760
  Completed Objects: 637
  Total Objects: 11,114
  Worker Parallelism: 1

Worker 4 Status:
  Instance ID: 1
  Instance name: rmn01tst
  Host name: db12c.oracle.com
  State: EXECUTING
  Object Schema: SCOTT
  Object Name: TRAIN_SCHEDULE_LIST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: TRN_56925
  Completed Objects: 628
  Total Objects: 11,114
  Worker Parallelism: 1

Worker 5 Status:
  Instance ID: 1
  Instance name: rmn01tst
  Host name: db12c.oracle.com
  State: EXECUTING
  Object Schema: SCOTT
  Object Name: TRAIN_SCHEDULE_LIST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: TRN_56228
  Completed Objects: 627
  Total Objects: 11,114
  Worker Parallelism: 1

Worker 6 Status:
  Instance ID: 1
  Instance name: rmn01tst
  Host name: db12c.oracle.com
  State: EXECUTING
  Object Schema: SCOTT
  Object Name: TRAIN_SCHEDULE_LIST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: TRN_58417
  Completed Objects: 624
  Total Objects: 11,114
  Worker Parallelism: 1

Worker 7 Status:
  Instance ID: 1
  Instance name: rmn01tst
  Host name: db12c.oracle.com
  State: EXECUTING
  Object Schema: SCOTT
  Object Name: TRAIN_SCHEDULE_LIST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: TRN_53021
  Completed Objects: 660
  Total Objects: 11,114
  Worker Parallelism: 1

Worker 8 Status:
  Instance ID: 1
  Instance name: rmn01tst
  Host name: db12c.oracle.com
  State: EXECUTING
  Object Schema: SCOTT
  Object Name: TRAIN_SCHEDULE_LIST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: TRN_6513
  Completed Objects: 619
  Total Objects: 11,114
  Worker Parallelism: 1

Worker 9 Status:
  Instance ID: 1
  Instance name: rmn01tst
  Host name: db12c.oracle.com
  State: EXECUTING
  Object Schema: SCOTT
  Object Name: TRAIN_SCHEDULE_LIST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: TRN_59711
  Completed Objects: 620
  Total Objects: 11,114
  Completed Bytes: 8,192
  Percent Done: 20
  Worker Parallelism: 1

Worker 10 Status:
  Instance ID: 1
  Instance name: rmn01tst
  Host name: db12c.oracle.com
  State: EXECUTING
  Object Schema: SCOTT
  Object Name: TRAIN_SCHEDULE_LIST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: TRN_55366
  Completed Objects: 637
  Total Objects: 11,114
  Worker Parallelism: 1

Worker 11 Status:
  Instance ID: 1
  Instance name: rmn01tst
  Host name: db12c.oracle.com
  State: EXECUTING
  Object Schema: SCOTT
  Object Name: TRAIN_SCHEDULE_LIST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: TRN_68690
  Completed Objects: 599
  Total Objects: 11,114
  Worker Parallelism: 1

Worker 12 Status:
  Instance ID: 1
  Instance name: rmn01tst
  Host name: db12c.oracle.com
  State: EXECUTING
  Object Schema: SCOTT
  Object Name: TRAIN_SCHEDULE_LIST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: TRN_54104
  Completed Objects: 663
  Total Objects: 11,114
  Worker Parallelism: 1


====to momitor the progress... query below...
select PROCESSING_STATE,PROCESSING_STATUS,OBJECT_TYPE,count(1) from TRNSCHLIST group by PROCESSING_STATE,PROCESSING_STATUS,OBJECT_TYPE order by 1,2

select PROCESSING_STATE,PROCESSING_STATUS,OBJECT_TYPE,count(PARTITION_NAME),count(SUBPARTITION_NAME),sum(SIZE_ESTIMATE)
from TRNSCHLIST
group by PROCESSING_STATE,PROCESSING_STATUS,OBJECT_TYPE
order by 1,2;

P P OBJECT_TYPE                      COUNT(1)
- - ------------------------------ ----------
                                         1858
    TABLE_DATA                              1
R C TABLE_DATA                           1341        << processed partition/subpartition count.
    TABLE                                   1
E                                           1
E C TABLE_DATA                           9773        << yet to process partition/subpartition count.


P P OBJECT_TYPE                    COUNT(PARTITION_NAME) COUNT(SUBPARTITION_NAME) SUM(SIZE_ESTIMATE)
- - ------------------------------ --------------------- ------------------------ ------------------
E                                                      0                        0
R C TABLE                                              0                        0                736
R C TABLE_DATA                                     11114                        0          455147520    << all done
T C TABLE                                          11114                        0            8179904
    TABLE                                              0                        0
    TABLE_DATA                                         0                        0
                                                       0                        0         -327138432

7 rows selected.

[1]+  Done                    nohup sh trnschlist.sh > nh_trnschlist.out

[oracle@db12c DPDUMP]$ grep -i job nh_trnschlist.out
Job "SCOTT"."TRNSCHLIST" successfully completed at Tue Oct 23 12:28:56 2018 elapsed 0 00:20:08
[oracle@db12c DPDUMP]$

Observation:

1) We are able to add dumpfile on the fly without any hiccup.
2) The dumpfile format thus added isnt controlled by the filesize limitation we have in the parfile. It is wise to note that the newly added files arent controlled by the filesize defined in parfile, may be it needs a new direction again by using FILESIZE parameter on the fly like dumpfile.
3) # of dumpfiles are 104 and the size of the dumpfile is 184M, which means we have few dumpfiles of not the size we set by using filesize parameter. They are...

[oracle@db12c DPDUMP]$ ls -altr trnschlist_6*
-rw-rw----. 1 oracle oracle  8327168 Oct 23 12:28 trnschlist_602.dmp
-rw-rw----. 1 oracle oracle  9392128 Oct 23 12:28 trnschlist_601.dmp
-rw-rw----. 1 oracle oracle 49102848 Oct 23 12:28 trnschlist_603.dmp
-rw-rw----. 1 oracle oracle  6909952 Oct 23 12:28 trnschlist_604.dmp
-rw-rw----. 1 oracle oracle  1503232 Oct 23 12:29 trnschlist_609.dmp
-rw-rw----. 1 oracle oracle  5103616 Oct 23 12:29 trnschlist_606.dmp
-rw-rw----. 1 oracle oracle  2965504 Oct 23 12:29 trnschlist_608.dmp
-rw-rw----. 1 oracle oracle  4165632 Oct 23 12:29 trnschlist_607.dmp
-rw-rw----. 1 oracle oracle  6004736 Oct 23 12:29 trnschlist_605.dmp

[oracle@db12c DPDUMP]$ du -ch trnschlist_6*
9.0M    trnschlist_601.dmp
8.0M    trnschlist_602.dmp
47M     trnschlist_603.dmp
6.6M    trnschlist_604.dmp
5.8M    trnschlist_605.dmp
4.9M    trnschlist_606.dmp
4.0M    trnschlist_607.dmp
2.9M    trnschlist_608.dmp
1.5M    trnschlist_609.dmp
90M     total


[oracle@db12c DPDUMP]$ ls -altr trnschlist_1*|wc
     19     171    1292
[oracle@db12c DPDUMP]$ ls -altr trnschlist_2*|wc
     19     171    1292
[oracle@db12c DPDUMP]$ ls -altr trnschlist_3*|wc
     19     171    1292
[oracle@db12c DPDUMP]$ ls -altr trnschlist_4*|wc
     19     171    1292
[oracle@db12c DPDUMP]$ ls -altr trnschlist_5*|wc
     19     171    1292
[oracle@db12c DPDUMP]$ ls -altr trnschlist_6*|wc
      9      81     621
[oracle@db12c DPDUMP]$ ls -altr *.dmp|wc
    104     936    7176

so 95*1M + 90M = 185M as expected.

[oracle@db12c DPDUMP]$ pwd
/oradata_1/RMN01TST/DPDUMP
[oracle@db12c DPDUMP]$ du -sh .
184M    .

Result: 

We are able to add dumpfile on the fly without any hiccup. We need to take take care of the filesize param along with the dumpfile param.