ORACLE 如何导出定义到SQL语句

原创心在梦在 2023-06-15

515

导出ORACLE 定义到SQL语句

[TOC]

一、使用exp/imp 命令

查看imp帮助文档

[oracle@ora11204 ~]$ imp -help
....
INDEXFILE              write table/index info to specified file

1. exp导出定义

[oracle@ora11204 ~]$ exp sxc/sxc file=sxc.dmp  log=exp_sxc.log rows=n
Export: Release 11.2.0.4.0 - Production on Thu Jun 15 07:03:16 2023
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
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SXC
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SXC
About to export SXC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SXC's tables via Conventional Path ...
. . exporting table                             T1
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                             T2
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

2. imp生成定义文件

-- indexfile表示导入到sql文件中
[oracle@ora11204 ~]$ imp sxc/sxc file=sxc.dmp log=imp_sxc.log indexfile=sxc.sql full=y
Import: Release 11.2.0.4.0 - Production on Thu Jun 15 07:04:16 2023
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
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
Import terminated successfully without warnings.

3. 检查定义文件

--1) 查看sxc用户下的对象
[oracle@ora11204 ~]$ sqlplus sxc/sxc
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 06:48:00 2023
Copyright (c) 1982, 2013, Oracle.  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
SQL> select object_name,object_type from user_objects order by 2;

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------------------------
T1_N                INDEX
T2_T1_ID             INDEX
T2                 TABLE
T1                 TABLE
T_V                 VIEW


--2) 查看indexfile文件，只有table & index 
[oracle@ora11204 ~]$ cat sxc.sql
REM  CREATE TABLE "SXC"."T1" ("ID" NUMBER NOT NULL ENABLE, "N" NUMBER,
REM  "CONTENTS" VARCHAR2(4000)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
REM  255 STORAGE(INITIAL 851968 NEXT 1048576 MINEXTENTS 1 FREELISTS 1
REM  FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING
REM  NOCOMPRESS ;
CONNECT SXC;
CREATE INDEX "SXC"."T1_N" ON "T1" ("N" ) PCTFREE 10 INITRANS 2 MAXTRANS
255 STORAGE(INITIAL 327680 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ;
REM  CREATE TABLE "SXC"."T2" ("ID" NUMBER NOT NULL ENABLE, "T1_ID" NUMBER
REM  NOT NULL ENABLE, "N" NUMBER, "CONTENTS" VARCHAR2(4000)) PCTFREE 10
REM  PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 8388608 NEXT
REM  1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM  DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
CREATE INDEX "SXC"."T2_T1_ID" ON "T2" ("T1_ID" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ;

二、使用expdp\impdp命令

查看impdp帮助文档

[oracle@ora11204 ~]$ impdp -help
....
SQLFILE
Write all the SQL DDL to a specified file.

1. expdp导出定义

[oracle@ora11204 ~]$ expdp sxc/sxc directory=DIR_EXP dumpfile=expdp_sxc.dmp  logfile=expdp_sxc.log content=metadata_only

Export: Release 11.2.0.4.0 - Production on Thu Jun 15 06:57:39 2023

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SXC"."SYS_EXPORT_SCHEMA_01":  sxc/******** directory=DIR_EXP dumpfile=expdp_sxc.dmp logfile=expdp_sxc.log content=metadata_only
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
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SXC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SXC.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/dmp/expdp_sxc.dmp
Job "SXC"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 15 06:58:14 2023 elapsed 0 00:00:32

2. impdp生成定义文件

-- sqlfile表示导入到sql文件中，注意：文件生成在directory目录下
[oracle@ora11204 ~]$ impdp sxc/sxc directory=DIR_EXP dumpfile=expdp_sxc.dmp logfile=impdp_sxc.log sqlfile=sxc_ddl.sql

Import: Release 11.2.0.4.0 - Production on Thu Jun 15 06:59:19 2023

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
Master table "SXC"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SXC"."SYS_SQL_FILE_FULL_01":  sxc/******** directory=DIR_EXP dumpfile=expdp_sxc.dmp logfile=impdp_sxc.log sqlfile=sxc_ddl.sql
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
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SXC"."SYS_SQL_FILE_FULL_01" successfully completed at Thu Jun 15 06:59:22 2023 elapsed 0 00:00:02

3. 检查定义文件

--1) 再次查看sxc_ddl.sql文件，可以看到，包含创建用户、表、索引、视图，也就是所有的DDL语句都包含了。
[oracle@ora11204 ~]$ cat sxc.sql
REM  CREATE TABLE "SXC"."T1" ("ID" NUMBER NOT NULL ENABLE, "N" NUMBER,
REM  "CONTENTS" VARCHAR2(4000)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
REM  255 STORAGE(INITIAL 851968 NEXT 1048576 MINEXTENTS 1 FREELISTS 1
REM  FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING
REM  NOCOMPRESS ;
CONNECT SXC;
CREATE INDEX "SXC"."T1_N" ON "T1" ("N" ) PCTFREE 10 INITRANS 2 MAXTRANS
255 STORAGE(INITIAL 327680 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ;
REM  CREATE TABLE "SXC"."T2" ("ID" NUMBER NOT NULL ENABLE, "T1_ID" NUMBER
REM  NOT NULL ENABLE, "N" NUMBER, "CONTENTS" VA[oracle@ora11204 dmp]$ cat sxc_ddl.sql
-- CONNECT SXC
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
-- CONNECT SYSTEM
 CREATE USER "SXC" IDENTIFIED BY VALUES 'S:365B1B3753C77F77975DFF355461CDA07A6E35878C7E49403FFA527C9CD5;91312E59F50E2595'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "SXC";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "CONNECT" TO "SXC";
 GRANT "RESOURCE" TO "SXC";
 GRANT "DBA" TO "SXC";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "SXC" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT SXC

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'LHR11G', inst_scn=>'7722888');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "SXC"."T1"
   (    "ID" NUMBER NOT NULL ENABLE,
        "N" NUMBER,
        "CONTENTS" VARCHAR2(4000 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "SXC"."T2"
   (    "ID" NUMBER NOT NULL ENABLE,
        "T1_ID" NUMBER NOT NULL ENABLE,
        "N" NUMBER,
        "CONTENTS" VARCHAR2(4000 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX "SXC"."T1_N" ON "SXC"."T1" ("N")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" PARALLEL 1 ;

  ALTER INDEX "SXC"."T1_N" NOPARALLEL;
CREATE INDEX "SXC"."T2_T1_ID" ON "SXC"."T2" ("T1_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" PARALLEL 1 ;

  ALTER INDEX "SXC"."T2_T1_ID" NOPARALLEL;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE I_N VARCHAR2(60);
  I_O VARCHAR2(60);
  NV VARCHAR2(1);
  c DBMS_METADATA.T_VAR_COLL;
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
 stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  i_n := 'T1_N';
  i_o := 'SXC';
  EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,0,0,0,0,0,0,0,0,NV,NV,TO_DATE('2023-06-05 03:42:47',df),NV;

  DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/
DECLARE I_N VARCHAR2(60);
  I_O VARCHAR2(60);
  NV VARCHAR2(1);
  c DBMS_METADATA.T_VAR_COLL;
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
 stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  i_n := 'T2_T1_ID';
  i_o := 'SXC';
  EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,0,0,0,0,0,0,0,0,NV,NV,TO_DATE('2023-06-05 03:42:47',df),NV;

  DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/
-- new object type path: SCHEMA_EXPORT/VIEW/VIEW
CREATE  FORCE VIEW "SXC"."T_V" ("ID", "N", "CONTENTS") AS
  select "ID","N","CONTENTS" from sxc.t1;
-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
DECLARE
  c varchar2(60);
  nv varchar2(1);
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
  s varchar2(60) := 'SXC';
  t varchar2(60) := 'T1';
  p varchar2(1);
  sp varchar2(1);
  stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n9,n10,n11,n12,d1) VALUES ('T',6,0,t,p,sp,s,
               20000000,1000000,100,2000,0,NULL,NULL,NULL,
               TO_DATE('2023-06-05 03:43:55',df));

  DBMS_STATS.IMPORT_TABLE_STATS('"SXC"','"T1"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/

DECLARE
  c varchar2(60);
  nv varchar2(1);
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
  s varchar2(60) := 'SXC';
  t varchar2(60) := 'T2';
  p varchar2(1);
  sp varchar2(1);
  stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n9,n10,n11,n12,d1) VALUES ('T',6,0,t,p,sp,s,
               1,1,100,2000,0,NULL,NULL,NULL,
               TO_DATE('2023-06-05 03:43:55',df));

  DBMS_STATS.IMPORT_TABLE_STATS('"SXC"','"T2"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/
RCHAR2(4000)) PCTFREE 10
REM  PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 8388608 NEXT
REM  1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM  DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
CREATE INDEX "SXC"."T2_T1_ID" ON "T2" ("T1_ID" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ;

三、使用DBMS_METADATA包

1. 生成用户下所有对象的DDL语句

SQL> connect sxc /sxc;
Connected.
SQL> set heading off
SQL> set pagesize 0
SQL> set long 2000
SQL> spool all_dll.sql
SQL> select dbms_metadata.get_ddl(object_type,object_name) from user_objects;

  CREATE INDEX "SXC"."T2_T1_ID" ON "SXC"."T2" ("T1_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

  CREATE TABLE "SXC"."T2"
   (    "ID" NUMBER NOT NULL ENABLE,
        "T1_ID" NUMBER NOT NULL ENABLE,
        "N" NUMBER,
        "CONTENTS" VARCHAR2(4000)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

  CREATE INDEX "SXC"."T1_N" ON "SXC"."T1" ("N")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

  CREATE TABLE "SXC"."T1"
   (    "ID" NUMBER NOT NULL ENABLE,
        "N" NUMBER,
        "CONTENTS" VARCHAR2(4000)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

  CREATE OR REPLACE FORCE VIEW "SXC"."T_V" ("ID", "N", "CONTENTS") AS
  select "ID","N","CONTENTS" from sxc.t1

SQL> spool off;

2. 只生成表、索引等

已表为例，只生成表的创建语句，其他索引等语法一样。

SQL> connect sxc /sxc;
Connected.
SQL> set heading off
SQL> set pagesize 0
SQL> set long 2000
SQL> spool table_dll.sql
SQL> select dbms_metadata.get_ddl('TABLE',object_name) from user_objects where object_type='TABLE';

  CREATE TABLE "SXC"."T1"
   (    "ID" NUMBER NOT NULL ENABLE,
        "N" NUMBER,
        "CONTENTS" VARCHAR2(4000)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

  CREATE TABLE "SXC"."T2"
   (    "ID" NUMBER NOT NULL ENABLE,
        "T1_ID" NUMBER NOT NULL ENABLE,
        "N" NUMBER,
        "CONTENTS" VARCHAR2(4000)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  
SQL> spool off;

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

ORACLE 如何导出定义到SQL语句

导出ORACLE 定义到SQL语句

一、 使用exp/imp 命令

1. exp导出定义

2. imp生成定义文件

3. 检查定义文件

二、使用expdp\impdp命令

1. expdp导出定义

2. impdp生成定义文件

3. 检查定义文件

三、使用DBMS_METADATA包

1. 生成用户下所有对象的DDL语句

2. 只生成表、索引等

评论

一、使用exp/imp 命令