SELECT DBMS_METADATA.GET_DEPENDENT_DDL ('INDEX', TABLE_NAME, TABLE_OWNER) FROM (SELECT table_name, table_owner FROM Dba_indexes WHERE table_owner = UPPER (TRIM('&TABLE_OWNER')) AND table_name = UPPER (TRIM('&TABLE_NAME')) AND index_name NOT IN (SELECT constraint_name FROM DBA_CONST...
首先查询建表DDL语句,返回的是一个clob字段,给出的建表SQL: selectdbms_metadata.get_ddl('TABLE','DB_OPERATE_RECORD','ERUPT')fromdual; 1. CREATETABLE"ERUPT"."DB_OPERATE_RECORD"("OPERATE_ID"NUMBER(30,0)NOTNULLENABLE,"TABLE_NAME"VARCHAR2(200)NOTNULLENABLE,"ORIGINAL_DATA"CLOB,"OPERATE_SCRIPT"...
select dbms_metadata.get_ddl('TABLE','T_BANK_ORDER') from dual;
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE, NAME, SCHEMA) FROM DUAL; --例子: 获取表的ddl语句,表名为 BAOYW SELECT DBMS_METADATA.GET_DDL('TABLE', ‘CITY’, 'BAOYW') FROM DUAL; 1. 2. 3. 4. 另一个例子:获取某个SCHEMA下所有表的DDL创建语句 select owner,table_name,num_rows,blocks,avg_...
1.得到⼀个表的ddl语句：SET SERVEROUTPUT ON SET LINESIZE 1000 SET FEEDBACK OFF set long 99999 set pagesize 4000 ---去除storage等多余参数(可选)SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); -- 获取ddl ##（'TABLE','表名','⽤户名...
2.得到一个用户下的所有表,索引,存储过程的ddl SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name) FROM USER_OBJECTS u where U.OBJECT_TYPE IN (TABLE,INDEX,PROCEDURE); 3.得到所有表空间的ddl语句 SELECT DBMS_METADATA.GET_DDL(TABLESPACE, TS.tablespace_name) FROM DBA_TABLESPACES TS; ...
_METADATA.GET_DDL('TABLE','DEPT','SCOTT')FROMDUAL;-- 查看所需表的索引selectINDEX_NAME,INDEX_TYPE,TABLE_NAMEfromuser_indexesWHEREtable_name='EMP';--查看当前用户索引的SQLselectdbms_metadata.get_ddl('INDEX','PK_DEPT')fromdual;-- 查看其他用户索引的SQLselectdbms_metadata.get_ddl('INDEX','...
抓取oracle建表语句及获取建表ddl语句 1.抓取代码如下： 1.1.产⽣表的语法资料 DECLARE -- v_notPartTable VARCHAR2(1000):= '&2';--v_sql CLOB;v_partType VARCHAR2(20);v_partColumn VARCHAR2(50);v_subPartType VARCHAR2(50);v_subPartSql VARCHAR2(4000);v_seq NUMBER := 10;BEGIN FOR...
2.得到一个用户下的所有表,索引,存储过程的ddl SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name) FROM USER_OBJECTS u where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE'); 3.得到所有表空间的ddl语句 SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name) FROM DBA_TABLESPAC...
dbms_metadata.get_ddl('TABLE','TAB1','USER1') 三个参数中,第一个指定导出DDL定义的对象类型(此例中为表类型),第二个是对象名(此例中即表名),第三个是对象所在的用户名。 参考: ZDNet China《轻松取得Oracle结构描述句法》 Oracle的官方文档