1)获得表、索引、视图、存储过程、函数的DDL select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual; select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual; select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual; select dbms_...
selectdbms_metadata.get_ddl(u.object_type, u.object_name) from user_objects u where u.object_typein('table','index','procedure','function‘); 2、得到所有表空间的ddl语句 selectdbms_metadata.get_ddl('tablespace', ts.tablespace_name) from dba_tablespaces ts; 3、得到所有创建用户的ddl select...
SELECT DBMS_METADATA.GET_DDL('USER',U.username) FROM DBA_USERS U; 4、去除storage等多余参数 EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); 常见错误 SQL> select dbms_metadata.get_ddl('TABLE','PC','SCOTT') from dual; ERROR: ORA-19206: Invalid va...
select dbms_metadata.get_ddl(‘TABLE’,‘TABLENAME’,‘USERNAME’) from dual; 2、查看索引的SQL select dbms_metadata.get_ddl(‘INDEX’,‘INDEXNAME’,‘USERNAME’) from dual; 3、查看创建主键的SQL SELECT DBMS_METADATA.GET_DDL(‘CONSTRAINT’,‘CONSTRAINTNAME’,‘USERNAME’) FROM DUAL; 4、查看...
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语句 SET SERVEROUTPUT ON SET LINESIZE 1000 SET FEEDBACK OFF set long 999999---显示不完整 SET ...
SQL> select dbms_metadata.get_ddl('TABLE','PC','SCOTT') from dual; ERROR: ORA-19206: Invalid value for query or REF CURSOR parameter ORA-06512: at "SYS.DBMS_XMLGEN", line 83 ORA-06512: at "SYS.DBMS_METADATA", line 345 ORA-06512: at "SYS.DBMS_METADATA", line 410 ...
SQL> select dbms_metadata.get_ddl('TABLE','TABLENAME','USERNAME') from dual; ERROR: ORA-19206: Invalid value for query or REF CURSOR pa rameter ORA-06512: at "SYS.DBMS_XMLGEN", line 83 ORA-06512: at "SYS.DBMS_METADATA", line 345 ...
-- 6种:SEQUENCE、TRIGGER、TABLE、INDEX、VIEW、FUNCTIONselectobject_typefromuser_objectsgroupbyobject_type Paste_Image.png 比如查询SEQUENCE的ddl ,如下: SELECTsequence_name,DBMS_METADATA.GET_DDL('SEQUENCE',sequence_name)FROMuser_sequences; Paste_Image.png ...
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); 常见错误 SQL> select dbms_metadata.get_ddl('TABLE','PC','SCOTT') from dual; ERROR: ORA-19206: Invalid value for query or REF CURSOR parameter ...
DBMSMETADATA.GETDDL包可以得到数据库的对象的ddl脚本。如下SQLPLUS中执行:1.得到一个表的ddl语句:SET SERVEROUTPUT ONSET LINESIZE 1000SET FEEDBACK OFFset