Showing posts with label exp. Show all posts
Showing posts with label exp. Show all posts

Thursday, March 24, 2016

ORACLE 12C STILL SUPPORTS TRADITIONAL EXPORT EXP

************************************
SCHEMA EXPORT
************************************
[oracle@Linux03 dpump]$ exp pdborcl1_usr2/XXXXXXXXXX@pdborcl1  file=/u01/app/oracle/dpump/exp_test.dmp log=/u01/app/oracle/dpump/exp_test.log

Export: Release 12.1.0.2.0 - Production on Wed Mar 23 09:02:30 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user PDBORCL1_USR2
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user PDBORCL1_USR2
About to export PDBORCL1_USR2's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export PDBORCL1_USR2's tables via Conventional Path ...
. . exporting table                           TEST          1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                        TEST_ID          2 rows exported
EXP-00056: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
.
.
.
.
.
. exporting statistics
Export terminated successfully with warnings.

************************************
EXPORT TABLE :
************************************

[oracle@Linux03 dpump]$ exp pdborcl1_usr2/XXXXXXXX@pdborcl1  file=/u01/app/oracle/dpump/exp_test.dmp log=/u01/app/oracle/dpump/exp_test.log tables=PDBORCL1_USR2.TEST

Export: Release 12.1.0.2.0 - Production on Wed Mar 23 09:25:04 2016

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


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           TEST          1 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

Wednesday, February 24, 2016

Datapump enhancements and new features in 12c

*************************************************************************
Datapump enhancements and new features in  12c 

*************************************************************************

Login as sys dba and create user & data pump directory as below:


sqlplus /"as sysdba"


SQL> create directory dpump as '/u01/app/oracle/dpump';

Directory created.


SQL> grant read,write on directory dpump to pdborcl1_usr2;

Grant succeeded.

***************************************************
EXPORTING TABLES FROM PDB :
***************************************************

[oracle@Linux03 oracle]$ expdp pdborcl1_usr2/xxxxxxxx@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID

Export: Release 12.1.0.2.0 - Production on Fri Mar 11 14:54:24 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01":  pdborcl1_usr2/********@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "PDBORCL1_USR2"."TEST"                      5.078 KB       1 rows
. . exported "PDBORCL1_USR2"."TEST_ID"                   5.937 KB       2 rows
Master table "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for PDBORCL1_USR2.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dpump/pdborc1_usr2_tables.dmp
Job "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 11 14:54:39 2016 elapsed 0 00:00:13



***************************************************
LOGTIME=[NONE | STATUS | LOGFILE | ALL]
NONE : The default value, no timestamps to be included in output, same as previous versions.
STATUS : Timestamps are included in output console, but not in log file.
LOGFILE : Timestamps are included in log file, but not on console.
ALL : Timestamps are included in output to the log file and console.
***************************************************

Simple example : 

expdp pdborcl1_usr2/********@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables1 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=all

Export: Release 12.1.0.2.0 - Production on Fri Mar 11 14:56:54 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
11-MAR-16 14:56:56.798: Starting "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01":  pdborcl1_usr2/********@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables1 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=all 
11-MAR-16 14:56:57.303: Estimate in progress using BLOCKS method...
11-MAR-16 14:56:58.213: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
11-MAR-16 14:56:58.257: Total estimation using BLOCKS method: 128 KB
11-MAR-16 14:57:01.225: Processing object type TABLE_EXPORT/TABLE/TABLE
11-MAR-16 14:57:01.700: Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
11-MAR-16 14:57:02.212: Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
11-MAR-16 14:57:03.340: Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
11-MAR-16 14:57:03.385: Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
11-MAR-16 14:57:03.978: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
11-MAR-16 14:57:03.994: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
11-MAR-16 14:57:08.998: . . exported "PDBORCL1_USR2"."TEST"                      5.078 KB       1 rows
11-MAR-16 14:57:09.013: . . exported "PDBORCL1_USR2"."TEST_ID"                   5.937 KB       2 rows
11-MAR-16 14:57:09.235: Master table "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
11-MAR-16 14:57:09.240: ******************************************************************************
11-MAR-16 14:57:09.241: Dump file set for PDBORCL1_USR2.SYS_EXPORT_TABLE_01 is:
11-MAR-16 14:57:09.246:   /u01/app/oracle/dpump/pdborc1_usr2_tables1.dmp
11-MAR-16 14:57:09.260: Job "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 11 14:57:09 2016 elapsed 0 00:00:13

***************************************************
DISABLE ARCHIVE LOGGINGTHE. DEFAULT SETTING OF "N" HAS NO AFFECT ON LOGGING BEHAVIOUR.USING A VALUE "Y" REDUCES THE LOGGING ASSOCIATED WITH TABLES :
***************************************************

impdp pdborcl1_usr2/pdborcl1_usr2@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables3 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=status TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

The effect can be limited to a specific type of object (TABLE or INDEX) as below.

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX