下面给出一些其他的DDL查询方法: --获得某个schema下所有的表、索引、视图、存储过程、函数的DDLSELECTDBMS_METADATA.GET_DDL('TABLE',u.table_name,u.owner)FROMDBA_TABLES u;SELECTDBMS_METADATA.GET_DDL('VIEW',u.view_name,u.owner)FROMDBA_VIEWS u;SELECTDBMS_METADATA.GET_DDL('INDEX',u.index_name,...
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,'TEST') FROM USER_INDEXES u; select dbms_metadata.get_ddl('PROCEDURE',u.object_name, 'TEST',) from USER_objects u where u.object_type = 'PROCEDURE'; select dbms_metadata.get_ddl('FUNCTION',u.object_name, 'TEST',) from USER_objects...
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_...
-- 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 Paste_Image.png 解析 SELECT sequence_name,DBMS_...
select dbms_metadata.get_ddl('INDEX','ux_future_xx','Admin') from dual; 6、查询当前用户下视图的创建语句 select dbms_metadata.get_ddl('VIEW','ux_future') from dual; 7、查询其他用户下视图的创建语句 select dbms_metadata.get_ddl('VIEW','ux_future','Admin') from dual; ...
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,u.owner) FROM DBA_INDEXES u;select dbms_metadata.get_ddl('PROCEDURE',u.object_name, u.owner,) from dba_objects u where u.object_type = 'PROCEDURE';select dbms_metadata.get_ddl('FUNCTION',u.object_name, u.owner,) from dba_objects u...
SELECT index_name, table_name FROM all_indexes WHERE table_name = '表名'; ``` 2.查询索引的列信息: ``` SELECT column_name, table_name, index_name FROM all_ind_columns WHERE table_name = '表名'; ``` 3.查询索引的定义语句: ``` SELECT dbms_metadata.get_ddl('INDEX', '索引名', ...
get_ddl('INDEX','INDEX_NAME'[,'SCHEMA_NAME']) from dual; spool off; 4、检查分区表及索引大小 需要确定要迁移分区表的大小及SYSTEM表空间中分区个数,确保目标表空间中有足够容量 代码语言:javascript 复制 --分区表 SELECT sum(BYTES)/1024/1024/1024 FROM dba_segments t WHERE t.segment_name ='...
1.获取单个的建表、视图和建索引的语法setpagesize0setlong90000setfeedbackoffsetechooffspoolDEPT.sqlselectdbms_metadata.get_ddl('TABLE','TAB_NAME','SCOTT')fromdual;selectdbms_metadata.get_ddl('VIEW','VIEW_NAME','SCOTT')fromdual;selectdbms_metadata.get_ddl('INDEX','IDX_NAME','SCOTT')fromdual...
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual; spool off; b.获取一个SCHEMA下的所有建表和建索引的语法,以scott为例: set pagesize 0 set long 90000 set feedback off set echo off spool scott_schema.sql connect scott/tiger; ...