本文主要内容如下:
1.创建测试数据
2.方法一:DBMS_METADATA.GET_DDL获取表定义
3.DBMS_METADATA.GET_DDL去掉段属性和存储参数
4.方法二:all_tab_columns拼接
5.参考
插入测试数据:
sqlplus cjc/***
--建立表 DEPT
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
--建立表 EMP
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
---DEPT表插入数据;
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
---EMP表插入数据;
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('12-06-1987','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-06-1987','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
获取CJC.EMP表定义语句： 方法一:DBMS_METADATA.GET_DDL
SET LINE 300
SET PAGESIZE 100
SET LONG 1000
select dbms_metadata.get_ddl('TABLE','EMP','CJC') from dual;
结果如下:
CREATE TABLE "CJC"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX 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 "CJC" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "CJC"."DEPT" ("DEPTNO") ENABLE
) 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 "CJC"
可以看到，生成了CJC.EMP表定义语句，语句有些长，能否去掉建表语句以外的属性信息?方法如下: 设置转换参数
BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE); -- 添加分号 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE); -- 格式化输出 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE); -属性 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE); -- 去掉存储参数 END; /
获取表的 DDL，去掉了其他属性信息。
SET PAGESIZE 100
SET LONG 1000
SET LINE 300
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'CJC') AS DDL FROM DUAL;
DDL
--------------------------------------------------------------------------------
CREATE TABLE "CJC"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "CJC"."DEPT" ("DEPTNO") ENABLE
) ;
方法二:all_tab_columns拼接
[oracle@cjc-db-02 other]$ sqlplus cjc/a
spool table.lst
set serveroutput on size 1000000
declare
starting boolean :=true;
r_owner varchar2(30) := '&1';
r_table_name varchar2(30) := '&2';
begin
dbms_output.put_line('create table '||r_owner||'.'||r_table_name||'(');
for r in (select column_name, data_type, data_length, data_precision, data_scale, data_default, nullable
from all_tab_columns
where table_name = upper(r_table_name)
and owner=upper(r_owner)
order by column_id)
loop
if starting then
starting:=false;
else
dbms_output.put_line(',');
end if;
if r.data_type='NUMBER' then
if r.data_scale is null then
dbms_output.put(r.column_name||' NUMBER('||r.data_precision||')');
else
dbms_output.put(r.column_name||' NUMBER('||r.data_precision||','||r.data_scale||')');
end if;
else if r.data_type = 'DATE' then
dbms_output.put_line(r.column_name||' DATE');
else if instr(r.data_type, 'CHAR') >0 then
dbms_output.put(r.column_name||' '||r.data_type||'('||r.data_length||')');
else
dbms_output.put(r.column_name||' '||r.data_type);
end if;
end if;
end if;
if r.data_default is not null then
dbms_output.put(' DEFAULT '||r.data_default);
end if;
if r.nullable = 'N' then
dbms_output.put(' NOT NULL ');
end if;
end loop;
dbms_output.put_line(' ); ');
end;
/
spool off
填写用户名，表名。
Enter value for 1: CJC old 3: r_owner varchar2(30) := '&1'; new 3: r_owner varchar2(30) := 'CJC'; Enter value for 2: EMP old 4: r_table_name varchar2(30) := '&2'; new 4: r_table_name varchar2(30) := 'EMP';
生成CJC.EMP建表语句，但是没有主键等约束信息。
create table CJC.EMP( EMPNO NUMBER(4,0) NOT NULL , ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4,0), HIREDATE DATE , SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2,0) );
参考:
How to Generate 'CREATE TABLE' Scripts from Existing Tables (Doc ID 123851.1)
###chenjuchao 20250301###
欢迎关注我的公众号《IT小Chen》
