1. 查询表的索引名称
select index_name from user_indexes where table_name='SYSFUNCREGISTER'
2. 获取索引的DDL
select dbms_metadata.get_ddl('INDEX','PK_COMPERSON','DSKNOW') from dual;
3. 获取索引对应表的字段
select user_ind_columns.index_name,user_ind_columns.column_name,
user_ind_columns.column_position,user_indexes.uniqueness
from user_ind_columns,user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = 'RQT_JOBQUALITY';
4 获取单个表的DDL
SELECT DBMS_METADATA.GET_DDL('TABLE','COMBILLTYPE','DSKNOW') FROM DUAL;
得到一个用户下的所有表,索引,存储过程,函数的ddl
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE,U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE','FUNCTION‘);
5.查询结果转成insert
select 'INSERT INTO combdversion (pk_bdversion,vbdcode,vbdname,vbdversion,vmemo,dr,ts) VALUES(
' || '''' || PK_BDVERSION ||'''' || ','
|| '''' || vbdcode || '''' || ','
|| '''' || vbdname || '''' || ','
|| '''' || vbdversion || '''' || ','
|| '''' || vmemo || '''' || ','
|| '''' || dr || '''' || ','
|| '''' || ts || '''' || ');'
From combdversion
6. 获取表的所有字段
select column_name from user_tab_columns
where Table_Name='COMBDVERSION'
7.查询所有用户表
select * from user_tables;