Oracle查看表, 存储过程, 触发器, 函数, 等对象定义语句(二)
) to specify that storage clauses are not to be returned in the SQL DDL. Afterwards, the example resets the session-level parameters to their defaults.
To generate complete, uninterrupted output, set the PAGESIZE to 0 and set LONG to some large number, as shown, before executing your query.
SQL> SET LONG 2000000
SQL> SET PAGESIZE 0
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
SQL> select dbms_metadata.get_ddl('TABLE','BBS','NEWCCS') from dual;
CREATE TABLE "NEWCCS"."BBS"
( "BBSID" NUMBER(16,0),
"SENDER" VARCHAR2(20) NOT NULL ENABLE,
"INCEPT" VARCHAR2(20) NOT NULL ENABLE,
"MSGTITLE" VARCHAR2(40) NOT NULL ENABLE,
"MSG" VARCHAR2(500),
"SENDTIME" DATE DEFAULT sysdate NOT NULL ENABLE,
"MSGTOP" VARCHAR2(1) DEFAULT 0,
"MSGFILENAME" VARCHAR2(100),
"MSGFILE" LONG RAW,
"EDITTIME" DATE DEFAULT sysdate,
CONSTRAINT "PK_BBS" PRIMARY KEY ("BBSID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "NEWCCS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "NEWCCS"
DEFAULT:
SQL>
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');
2. 查看 存储过程 的定义语句
SQL> SET LONG 9999
SQL> select dbms_metadata.get_ddl('PROCEDURE','ANALYZEDB','NEWCCS') from dual;
SQL> select dbms_metadata.get_ddl('INDEX','IDX_ENAME','SCOTT') from dual;