查询具体的表名大写加单引号.批量不加。
SCOTT@PROD>select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') CODE from dual;
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX 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" 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 "USERS"
SCOTT@PROD>
如果要查询所有表的结构呢:
下面只选取了一部分:
SCOTT@PROD>select dbms_metadata.get_ddl('TABLE',t.table_name) from user_tables t;
CREATE TABLE "SCOTT"."OB_JEST"
( "ID" NUMBER(*,0),
"NAME" VARCHAR2(25),
"YEAR" NUMBER(*,0)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
CREATE TABLE "SCOTT"."ANIMALS"
( "NAME" VARCHAR2(10),
"SPECIES" VARCHAR2(20),
"DATE_OF_BIRTH" DATE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
CREATE TABLE "SCOTT"."OB_TEST"
( "ENAME" VARCHAR2(20)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
也可以查询sp view sequence等等。做相应的替换就可以了。
select dbms_metadata.get_ddl('view',t.view_name) from user_views t;
SCOTT@PROD>select dbms_metadata.get_ddl('PROCEDURE',t.object_name) from user_objects t where object_type='PROCEDURE';
CREATE OR REPLACE PROCEDURE "SCOTT"."CHANGE_SAL" (v_empno in number,
v_out out number )
as
v_count number;
begin
select count(1) into v_count from emp where empno = v_empno;
if v_count=0 then
v_out:=1;
else
v_out:=0;
end if;
end;
CREATE OR REPLACE PROCEDURE "SCOTT"."PROCEDURE_1" (v_1 in number)
as
type dept_n is table of emp%rowtype index by binary_integer;
v_dept dept_n;
begin
select empno,ename,job bulk collect into v_dept from emp;
for i in v_dept.first..v_dept.last loop
dbms_output.put_line(v_dept(i).empno);
end loop;
end;
SCOTT@PROD>