Showing posts with label DATAPUMP. Show all posts
Showing posts with label DATAPUMP. Show all posts

Monday 20 November 2023

Datapump FILESIZE parameter

FILESIZE :

  • It specifies the maximum size of each dump file. If the size is reached for any member of the dump file set, that file is closed and an attempt is made to create a new file, if the file specification contains a substitution variable.
  • You can set filesize parameter limits for the dumpfile size same as below,[FILESIZE=integer[B | K | M | G] where B is bytes, K is KB, M is MB and G is GB]
  • The below command will create each dumpfile of size 50ok.If the export data is more than 500k then you specify dumpfile as wildcard “%U” to create multiple files.

[oracle@srv1 ~]$ expdp amit/oracle directory=test_dir dumpfile=amit_data%U.dmp logfile=amit_data.log schemas=amit filesize=500k Export: Release 18.0.0.0.0 - Production on Fri Aug 10 11:54:40 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "AMIT"."SYS_EXPORT_SCHEMA_03": amit/******** directory=test_dir dumpfile=amit_data%U.dmp logfile=amit_data.log schemas=amit filesize=500k 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/PRE_SCHEMA/PROCACT_SCHEMA 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 "AMIT"."EMP" 873.1 KB 100000 rows . . exported "AMIT"."SYS_EXPORT_SCHEMA_01" 0 KB 0 rows . . exported "AMIT"."SYS_EXPORT_SCHEMA_02" 434.3 KB 1445 rows Master table "AMIT"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded ****************************************************************************** Dump file set for amit.SYS_EXPORT_SCHEMA_03 is: /u01/shared_datapump/amit_data01.dmp /u01/shared_datapump/amit_data02.dmp /u01/shared_datapump/amit_data03.dmp /u01/shared_datapump/amit_data04.dmp Job "amit"."SYS_EXPORT_SCHEMA_03" successfully completed at Fri Nov 10 11:55:21 2018 elapsed 0 00:00:41










I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!


Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011




Posted by at No comments:
Label

Wednesday 1 April 2015

ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, ORA-29283: invalid file operation

When doing export occur above error, let’s check directories:
SQL> select directory_name, directory_path from dba_directories
DIRECTORY_NAME     DIRECTORY_PATH
——————————————————————————–
DUMP_DIR       /u02/dump_dir


Two thing’s that hadn’t done is:

1) given correct permissions for that user to acccess the logical directory for export:
SQL> GRANT read, write on directory dump_dir TO ika; <-- username
2) create the physical directory
[oracle@oel6]$ mkdir -p /u02/dump_dir
After that our export run successfully;



I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!
Posted by at 1 comment:
Label ,

Wednesday 26 November 2014

Datapump compression parameter


Here I just like to show How compression datapump parameter working in Oracle 11g R2 ( see following demonstration how size vary from others.)

Default: METADATA_ONLY

Purpose

Specifies which data to compress before writing to the dump file set

Syntax and Description

COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}
  • ALL enables compression for the entire export operation. The ALL option requires that the Oracle Advanced Compression option be enabled.
  • DATA_ONLY results in all data being written to the dump file in compressed format. The DATA_ONLY option requires that the Oracle Advanced Compression option be enabled.
  • METADATA_ONLY results in all metadata being written to the dump file in compressed format. This is the default.
  • NONE disables compression for the entire export operation.
Restrictions
  • To make full use of all these compression options, the COMPATIBLE initialization parameter must be set to at least 11.0.0.
  • The METADATA_ONLY option can be used even if the COMPATIBLE initialization parameter is set to 10.2.
Compression of data (using values ALL or DATA_ONLY) is valid only in the Enterprise Edition of Oracle Database 11g

For Example, See Below Screenshot:

Compression =METADATA_ONLY

[oracle@TESTDB datapump]$ expdp test/test@TESTDB dumpfile=compressmeta.dmp directory=DATAPUMP_TEST compression=metadata_only

Export: Release 11.2.0.3.0 - Production on Wed Nov 26 12:32:31 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@TESTDB dumpfile=compressmeta.dmp directory=DATAPUMP_TEST compression=metadata_only

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."DEPT"                               5.007 KB       1 rows
. . exported "TEST"."EMP"                                5.007 KB       1 rows
. . exported "TEST"."CLASS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /data1/datapump/compressmeta.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:32:36

Compression =ALL


[oracle@TESTDB datapump]$ expdp test/test@TESTDB dumpfile=compressall.dmp directory=DATAPUMP_TEST compression=all

Export: Release 11.2.0.3.0 - Production on Wed Nov 26 12:33:09 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@TESTDB dumpfile=compressall.dmp directory=DATAPUMP_TEST compression=all
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."DEPT"                               4.687 KB       1 rows
. . exported "TEST"."EMP"                                4.687 KB       1 rows
. . exported "TEST"."CLASS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /data1/datapump/compressall.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:33:11

Compression =DATA_ONLY

[oracle@TESTDB datapump]$ expdp test/test@TESTDB dumpfile=compressdata.dmp directory=DATAPUMP_TEST compression=data_only

 Export: Release 11.2.0.3.0 - Production on Wed Nov 26 12:33:30 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPOR_SCHEMA_01":  test/********@TESTDB dumpfile=compressdata.dmpdirectory=DATAPUMP_TEST compression=data_only
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."DEPT"                               4.687 KB       1 rows
. . exported "TEST"."EMP"                                4.687 KB       1 rows
. . exported "TEST"."CLASS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /data1/datapump/compressdata.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:33:34

Compression =NONE

[oracle@TESTDB datapump]$ expdp test/test@TESTDB dumpfile=compressnone.dmp directory=DATAPUMP_TEST compression=none

 Export: Release 11.2.0.3.0 - Production on Wed Nov 26 12:33:59 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@TESTDB dumpfile=compressnone.dmp directory=DATAPUMP_TEST compression=none
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."DEPT"                               5.007 KB       1 rows
. . exported "TEST"."EMP"                                5.007 KB       1 rows
. . exported "TEST"."CLASS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /data1/datapump/compressnone.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:34:02

Detail File size comparison

Compare how size vary from every compression parameter.

[oracle@NVMBD1BZY150D00 datapump]$ ls -lrth

-rw-r-----. 1 oracle oinstall 192K Nov 26 12:32 compressmeta.dmp
-rw-r-----. 1 oracle oinstall  68K Nov 26 12:33 compressall.dmp
-rw-r-----. 1 oracle oinstall  72K Nov 26 12:33 compressdata.dmp
-rw-r-----. 1 oracle oinstall 196K Nov 26 12:34 compressnone.dmp



I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!
Posted by at 1 comment:
Label ,
Subscribe to: Posts (Atom)