Скачать с ютуб How to Export and Import table in Oracle Database using Datapump (expdp,impdp) в хорошем качестве

How to Export and Import table in Oracle Database using Datapump (expdp,impdp)

Oracle Data Pump is a newer, faster and more flexible alternative to the  traditional "exp" and "imp" utilities used in previous Oracle versions. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables. Before going to export/import in oracle database make sure directory exist, if not we will have to create it first. The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file system of the database server. CREATE OR REPLACE DIRECTORY test_dir AS '/oracle/oradata/'; GRANT READ, WRITE ON DIRECTORY APPS_DIR TO apps; Query ALL_DIRECTORIES/dba_directories view to display all existing direcotiries. select owner,directory_name,directory_path from all_directories; Table Exports/Imports:- TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax. Parameter tables allow to mention more than one table.. expdp apps/apps@UAT tables=EMP_POLICY_MASTER directory=APPS_DIR dumpfile=EMP_POLICY_MASTER.dmp logfile=expdpEMP_POLICY_MASTER.log impdp apps/apps@UAT tables=EMP_POLICY_MASTER directory=APPS_DIR dumpfile=EMP_POLICY_MASTER.dmp logfile=impdpEMP_POLICY_MASTER.log The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables. Schema Exports/Imports:- The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax. expdp apps/apps@UAT schemas=apps directory=APPS_DIR dumpfile=apps.dmp logfile=expdpAPPS.log impdp apps/apps@UAT schemas=apps directory=APPS_DIR dumpfile=apps.dmp logfile=impdpAPPS.log Database Exports/Imports:- The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax. expdp system/password@UAT full=Y directory=APPS_DIR dumpfile=DB11g.dmp logfile=expdpDB11g.log impdp system/password@UAT full=Y directory=APPS_DIR dumpfile=DB11g.dmp logfile=impdpDB11g.log How import table in oracle database... Before import the table . we will drop the table EMP_POLICY_MASTER. drop table EMP_POLICY_MASTER. $impdp apps/apps tables=EMP_POLICY_MASTER directory=apps_dir dumpfile=EMP_POLICY_MASTER1.dmp file=expdpEMP_POLICY_MASTER1.log Import: Release 11.2.0.4.0 - Production on Sat Feb 20 15:43:15 2021 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "file=expdpEMP_POLICY_MASTER1.log" Location: Command Line, Replaced with: "dumpfile=expdpEMP_POLICY_MASTER1.log" Master table "APPS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "APPS"."SYS_IMPORT_TABLE_01":  apps/******** tables=EMP_POLICY_MASTER directory=apps_dir dumpfile=EMP_POLICY_MASTER1.dmp dumpfile=expdpEMP_POLICY_MASTER1.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "APPS"."EMP_POLICY_MASTER"                  146.5 KB    1310 rows Job "APPS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Feb 20 15:43:16 2021 elapsed 0 00:00:01 We can see successfully imported the table.... select count(*) from EMP_POLICY_MASTER;   COUNT(*) ----------       1310 #oracle #oracledatabase #datapump #exportdata #export #import #exp #imp #exporttable