Dear Readers, Please check the below articles to get good knowledge on Data Pump (Export & Import). SNO Article Name Article Link 1 How to Kill & Trace Datapunp Job https://www.ktexperts.com/how-to-kill-trace-datapunp-job/ 2 How to Generate DDL from existing dumpfile https://www.ktexperts.com/how-to-generate-ddl-from-existing-dumpfile/ 3 How to utilize PARFILE… Read More
Hello everyone, In this article, we will learn Generating DDL using existing dumpfile. To generate DDL of a schema or table from a dumpfile we use a parameter called SQLFILE. Purpose: SQLFILE is a paramater which can be used in IMPDP utility to generate DDL… Read More
Hello Everyone, In this article , we will learn how to utilize PARFILE parameter in DATAPUMP Exports and Imports. what is parfile ? Parfile is a parameter in datapump exports and imports . The name of the parfile can give in any format but extension… Read More
Hello Everyone, In this article , we will learn Excluding tables while performing datapump import from full schema backup. Database : AVPROD Schema : OT Check objects count in OT Schema.
|
1 2 3 4 5 6 |
SQL> SELECT COUNT(*), OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OWNER='&USERNAME' group by object_type,status; COUNT(*) OBJECT_TYPE STATUS ---------- ----------------------- ------- 7 SEQUENCE VALID 12 TABLE VALID 12 INDEX VALID |
Take full schema backup using schema=OT
|
1 |
[oracle@ip-172-31-6-16 ~]$ expdp directory=pump dumpfile=OT_tables.dmp logfile=OT_tables.log schemas=OT parallel=3 job_name=OT |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
Export: Release 12.1.0.2.0 - Production on Sun Jan 24 16:14:21 2021 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba 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 "SYS"."OT": /******** AS SYSDBA directory=pump dumpfile=OT_tables.dmp logfile=OT_tables.log schemas=OT parallel=3 job_name=OT Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 768 KB Processing object type SCHEMA_EXPORT/USER . . exported "OT"."CONTACTS" 28.70 KB 319 rows . . exported "OT"."COUNTRIES" 6.375 KB 25 rows . . exported "OT"."CUSTOMERS" 32.25 KB 319 rows . . exported "OT"."EMPLOYEES" 17.35 KB 107 rows . . exported "OT"."INVENTORIES" 21.40 KB 1112 rows . . exported "OT"."LOCATIONS" 8.382 KB 23 rows . . exported "OT"."ORDERS" 9.773 KB 105 rows . . exported "OT"."ORDER_ITEMS" 21.20 KB 665 rows . . exported "OT"."PRODUCTS" 33.67 KB 288 rows . . exported "OT"."PRODUCT_CATEGORIES" 5.585 KB 5 rows . . exported "OT"."REGIONS" 5.554 KB 4 rows . . exported "OT"."WAREHOUSES" 6.164 KB 9 rows 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/IDENTITY_COLUMN 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/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Master table "SYS"."OT" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.OT is: /u01/oradata/AVPROD/pump/OT_tables.dmp Job "SYS"."OT" successfully completed at Sun Jan 24 16:15:04 2021 elapsed 0 00:00:27 |
Full schema backup completed ,… Read More
Hello Everyone, In this article , we will learn how to Attach and de-attach the expdp/impdp datapump jobs. EXPDP and IMPDP is used to take the logical backup of full database, tablespace ,schema,table and query level. we can attach or deattach the datapump jobs using… Read More
Dear Readers, How all of you are doing well & safe. Today I was reading some documents referring new DB enhancements in Oracle 18C & found we can do Oracle’s AUDIT table exports using datapump. I remember some of links in Oracle MOS/Docs mentioning, EXPDP… Read More
There is no excerpt because this is a protected post.
HOW TO EXPORT DATA FROM REMOTE DATABASE USING DATAPUMP. In this article, we will see how to export data from remote database and import in source database. Source Database : iP address : 192.168.0.51 Instance Name : primary Remote Database : iP address : 192.168.0.61… Read More
How to perform DATAPUMP operations on standby database by converting SNAPSHOT standby. Data Guard is the name for Oracle’s standby database solution, used for disaster recovery and high availability. This article contains an updated version of 12cR1 Converting physical standby into snapshot standby and… Read More
Different options in Export and Imports Schema export and import using remap_schemas. 1) create directory dpump as ‘/u02/dpump’; 2) grant read,write on directory dpump to system;
|
1 |
expdp user/password directory=dpump dumpfile=export.dup logfile=export.log schemas=scott |
For importing schema : 1) Cretae directory dpump as ‘/u02/DATAPUMP’; 2) grant read,write on directory… Read More
