在工作中我们会遇到从数据库中获取某些对象的定义脚本,比如:
PROCEDURE
PACKAGE
PACKAGE BODY
TRIGGER
FUNCTION
TABLE等。
这里就教大家一招获取指定对象的定义语句的方法。
获取HR用户下employees表的结构:
1.查看employees表中有哪些字段:
SQL>desc employees;
名称 是否为空? 类型
------------------------------------------------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
尝试获取建表语句:
执行效果如下:
SQL> selectDBMS_METADATA.get_ddl('TABLE','EMPLOYEES','HR') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
CREATE TABLE "HR"."EMPLOYEES"
( "EMPLOYEE_ID"NUMBER(6,0),
"FIRST_NAME"
SQL>
发现显示的不完整,看看配置参数;
SQL> show long
long 80
SQL> set long 9999
重新执行语句
SQL>select DBMS_METADATA.get_ddl('TABLE','EMPLOYEES','HR') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
CREATE TABLE"HR"."EMPLOYEES"
( "EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25)CONSTRAINT "EMP_LA
ST_NAME_NN"NOT NULL ENABLE,
"EMAIL" VARCHAR2(25)CONSTRAINT "EMP_EMAIL_NN" NO
T NULLENABLE,
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE CONSTRAINT"EMP_HIRE_DATE_NN" NOT N
ULL ENABLE,
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
"JOB_ID" VARCHAR2(10)CONSTRAINT "EMP_JOB_NN"
NOT NULLENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0),
CONSTRAINT "EMP_SALARY_MIN"CHECK (salary > 0
) ENABLE,
CONSTRAINT "EMP_EMAIL_UK"UNIQUE ("EMAIL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS
255 NOLOGGING COMPUTE STATISTICS
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTEN
TS 1MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_
POOLDEFAULT FLASH_CACHE DEFAULT CELL_FL
ASH_CACHEDEFAULT)
TABLESPACE "EXAMPLE" ENABLE,
CONSTRAINT "EMP_EMP_ID_PK"PRIMARY KEY ("EMPLO
YEE_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255NO
LOGGINGCOMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS1 MA
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
XEXTENTS2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GRO
UPS 1BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULTCELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE" ENABLE,
CONSTRAINT "EMP_MANAGER_FK"FOREIGN KEY ("MANAGER_ID"
)
REFERENCES"HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE,
CONSTRAINT "EMP_JOB_FK"FOREIGN KEY ("JO
B_ID")
REFERENCES"HR"."JOBS" ("JOB_ID") ENABLE,
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
CONSTRAINT "EMP_DEPT_FK"FOREIGN KEY ("DEPART
MENT_ID")
REFERENCES"HR"."DEPARTMENTS" ("DEPARTMENT_ID")
ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255NOCOMPRESS
NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS1
MAXEXTENTS2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL
DEFAULTFLASH_CACHE DEFAULT CELL_FLASH_C
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
ACHEDEFAULT)
TABLESPACE "EXAMPLE"
SQL>
结果就是我们获取的hr.employees的建表语句了。
除了表,dbms_metadate.get_ddl还可以获取到触发器,存储过程等的信息。
诸如:
select dbms_metadata.get_ddl ('TRIGGER','ACCOUNT','TEST')from dual;
此语句的格式为
select dbms_metadata.get_ddl (‘object_type’,’object_name’,’username’)from dual
;
转载于:https://blog.51cto.com/buertu/1749102