ORACLE查询数据库中有哪些表的方法
发布网友
共1个回答
热心网友
SELECT * FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE = 'FUNCTION' AND T.OWNER <> 'SYS' AND T.OBJECT_NAME LIKE 'TO_QUARTER_END_DATE';--查找以数字结尾和以ALEX结尾的数据表SELECT * FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE = 'TABLE' AND T.OWNER = 'GFMIS_ALL' AND (REGEXP_LIKE(T.OBJECT_NAME, '^.*[[:digit:]]$') OR T.OBJECT_NAME LIKE '%ALEX');SELECT T.* FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE = 'TABLE' AND T.OWNER = 'GFMIS';SELECT T.* FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE = 'VIEW' AND T.OWNER = 'GFMIS';SELECT T.* FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE = 'FUNCTION' AND T.OWNER = 'GFMIS' AND T.STATUS = 'VALID';SELECT T.* FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE = 'PROCEDURE' AND T.OWNER = 'GFMIS' AND T.STATUS = 'VALID';SELECT T.* FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE = 'SEQUENCE' AND T.OWNER = 'GFMIS';SELECT T.* FROM DBA_OBJECTS T WHERE T.OBJECT_TYPE = 'TRIGGER' AND T.OWNER = 'GFMIS' AND T.STATUS = 'VALID'; -------------------------------------------------------------------------------------------------- 查询表字段明细 select column_name,data_type,data_length,DATA_PRECISION ,DATA_SCALE from all_tab_columns where table_name='tablename' 字段注释 select * from user_col_comments; 表注释 select * from user_tab_comments; 表明细+表注释+字段明细+字段注释 select ATC.OWNER,atC.TABLE_NAME,utc.comments,ATC.COLUMN_NAME,ATC.DATA_TYPE,ATC.DATA_LENGTH,ATC.NULLABLE, ucc.comments from (select ATC.OWNER,atC.TABLE_NAME,ATC.COLUMN_NAME,ATC.DATA_TYPE,ATC.DATA_LENGTH,ATC.NULLABLE from all_tab_columns ATC where ATC.owner in (用户名1，用户名2........) ) atc left outer join user_col_comments ucc on atc.table_name=ucc.table_name and atc.column_name=ucc.column_name left outer join user_tab_comments utc on atc.table_name=utc.table_name order by atc.table_name,atc.column_name;------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Oracle查询数据库中有哪些用户：SELECT * FROMALL_USERSA WHERE A.username LIKE 'EXCHANGE%';SELECT * FROMDBA_USERSD WHERE D.username LIKE 'EXCHANGE%';------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SELECT * FROMUSER_CONSTRAINTSE WHERE E.CONSTRAINT_TYPE='R' AND E.CONSTRAINT_NAME='FK_RECEIPT_LICENCE_PRT_H_1';【注】：查询数据库中的约束关系名称，CONSTRAINT_TYPE是约束类型，CONSTRAINT_NAME是约束名字。------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --1、查找表的所有索引（包括索引名，类型，构成列）： SELECT T.*, I.INDEX_TYPE FROMUSER_IND_COLUMNST,USER_INDEXESI WHERE T.INDEX_NAME = I.INDEX_NAME AND T.TABLE_NAME = I.TABLE_NAME AND T.TABLE_NAME LIKE 'R\_%' ESCAPE '\'; --2、查找表的主键（包括名称，构成列）： SELECT CU.* FROMUSER_CONS_COLUMNSCU,USER_CONSTRAINTSAU WHERE CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME AND AU.CONSTRAINT_TYPE = 'P' AND AU.TABLE_NAME LIKE 'R\_%' ESCAPE '\'; --3、查找表的唯一性约束（包括名称，构成列）： SELECT COLUMN_NAME FROMUSER_CONS_COLUMNSCU,USER_CONSTRAINTSAU WHERE CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME AND AU.CONSTRAINT_TYPE = 'U' AND AU.TABLE_NAME LIKE 'R\_%' ESCAPE '\'; --4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）： SELECT * FROMUSER_CONSTRAINTSC WHERE C.CONSTRAINT_TYPE = 'R' AND C.TABLE_NAME LIKE 'R\_%' ESCAPE '\';--查询外键约束的列名： SELECT * FROMUSER_CONS_COLUMNSCL WHERE CL.CONSTRAINT_NAME IN (SELECT DISTINCT C.CONSTRAINT_NAME FROMUSER_CONSTRAINTSC WHERE C.CONSTRAINT_TYPE = 'R' AND C.TABLE_NAME LIKE 'R\_%' ESCAPE '\');
