Oracle 使用 expdp/impdp 获取导出元数据（ 用户及表结构 ）步骤

一、步骤：

1、导出元数据：

export ORACLE_PDB_SID=pdb

expdp \'/ as sysdba\' directory=my_dir dumpfile=mydmp.dmp logfile=mylog.log content=metadata_only schemas=myuser exclude=statistics

2、导入至文本文件：

impdp \'/ as sysdba\' directory=my_dir dumpfile=mydmp.dmp logfile=myimplog.log sqlfile=mysql.sqlfile

cat /tmp/mysql.sqlfile

二、示例：

oracle@racdb1:/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 11 09:57:11 2024
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
SQL> alter session set container=pdb;

Session altered.

SQL> create directory my_dir as '/tmp';

Directory created.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
oracle@racdb1:/home/oracle>export ORACLE_PDB_SID=pdb
oracle@racdb1:/home/oracle>expdp \'/ as sysdba\' directory=my_dir dumpfile=mydmp.dmp logfile=mylog.log content=metadata_only schemas=myuser exclude=statistics

Export: Release 19.0.0.0.0 - Production on Mon Mar 11 09:50:36 2024
Version 19.14.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=my_dir dumpfile=mydmp.dmp logfile=mylog.log content=metadata_only schemas=myuser exclude=statistics 
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
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /tmp/mydmp.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Mar 11 09:51:55 2024 elapsed 0 00:01:09

oracle@racdb1:/home/oracle>impdp \'/ as sysdba\' directory=my_dir dumpfile=mydmp.dmp logfile=myimplog.log sqlfile=mysql.sqlfile

Import: Release 19.0.0.0.0 - Production on Mon Mar 11 09:53:34 2024
Version 19.14.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  "/******** AS SYSDBA" directory=my_dir dumpfile=mydmp.dmp logfile=myimplog.log sqlfile=mysql.sqlfile 
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
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Mon Mar 11 09:53:45 2024 elapsed 0 00:00:08

oracle@racdb1:/home/oracle>cd /tmp
oracle@racdb1:/tmp>cat mysql.sqlfile
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
 CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:4F73AAD23FB198710AEAE8425B0681FADD1D94F02535556621BACA336EB9;T:E0140C277AEFE5B41030C1A0FB2277148B4CE6B9A16D59B41A4FE37C74F0F6F1A6FE2D937058AD5E2B3321C4FB277CC6CAE47DAD3DAE6A661B5067B72770F28BE919431132E26DBA82A6AC4DE4BF8DD9'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "MYUSER";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "MYUSER";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "MYUSER" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT MYUSER

BEGIN 
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'PDB', inst_scn=>'5453626');
COMMIT; 
END; 
/ 
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYS
CREATE TABLE "MYUSER"."MYTABLE1" 
   (    "ID" NUMBER(*,0), 
        "ADDRS" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
CREATE TABLE "MYUSER"."MYTABLE2" 
   (    "ID" NUMBER(*,0), 
        "ADDRS" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
-- fixup virtual columns... 
-- done fixup virtual columns 
oracle@racdb1:/tmp>
oracle@racdb1:/tmp>

-- END --