Oracle 使用 expdp/impdp 获取导出元数据（ 用户及表结构 ）步骤_expdp获取元数据
赞
踩
一、步骤：
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 --
