Oracle查询数据库对象所属用户

Oracle查询数据库对象所属用户--查询某用户下的表、视图
SELECT TABLE_NAME,OWNER,TABLE_TYPE FROM ALL_TAB_COMMENTS WHERE OWNER = "VASS";--查询某用户下的视图
SELECT VIEW_NAME,OWNER FROM ALL_VIEWS WHERE OWNER = "VASS";--查询某用下的索引
SELECT INDEX_NAME,OWNER,TABLE_NAME FROM ALL_INDEXES WHERE OWNER = "VASS";--查询某用户下的触发器
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ALL_PROCEDURES
WHERE OWNER = "VASS" AND OBJECT_TYPE = "TRIGGER";
--或
SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER = "VASS";--查询某用户下的函数
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ALL_PROCEDURES
WHERE OWNER = "VASS" AND OBJECT_TYPE = "FUNCTION";--查询某用户下的存储过程
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ALL_PROCEDURES
WHERE OWNER = "VASS" AND OBJECT_TYPE = "PROCEDURE";
--或
SELECT PROCEDURE_NAME,OBJECT_NAME,OBJECT_TYPE FROM ALL_PROCEDURES WHERE OWNER = "VASS";--查询某用户下的序列
SELECT SEQUENCE_NAME,SEQUENCE_OWNER FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = "VASS"; -- 查询所有用户的表,视图等
SELECT * FROM ALL_TAB_COMMENTS;
-- 查询本用户的表,视图等
SELECT * FROM USER_TAB_COMMENTS;
--查询所有用户的表的列名和注释.
SELECT * FROM ALL_COL_COMMENTS;

-- 查询本用户的表的列名和注释
SELECT * FROM USER_COL_COMMENTS;
--查询所有用户的表的列名等信息（详细但是没有备注）.
SELECT * FROM ALL_TAB_COLUMNS;
--查询本用户的表的列名等信息（详细但是没有备注）.
SELECT * FROM USER_TAB_COLUMNS;

SELECT T.TABLE_NAME,T.COMMENTS FROM USER_TAB_COMMENTS T;
SELECT R1,R2,R3,R5
FROM （SELECT A.TABLE_NAME R1,A.COLUMN_NAME R2,A.COMMENTS R3 FROM USER_COL_COMMENTS A）,
（SELECT T.TABLE_NAME R4, T.COMMENTS R5 FROM USER_TAB_COMMENTS T）
WHERE R4 = R1;

--如何在ORACLE中查询所有用户表的表名、主键名称、索引、外键等

--1、查找表的所有索引（包括索引名，类型，构成列）：
SELECT T.*,I.INDEX_TYPE FROM USER_IND_COLUMNS T,USER_INDEXES I
WHERE T.INDEX_NAME = I.INDEX_NAME AND T.TABLE_NAME = I.TABLE_NAME AND T.TABLE_NAME = "tablename";
--2、查找表的主键（包括名称，构成列）：
SELECT CU.* FROM USER_CONS_COLUMNS CU, USER_CONSTRAINTS AU
WHERE CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME AND AU.CONSTRAINT_TYPE = "P" AND AU.TABLE_NAME = "tablename";

--3、查找表的唯一性约束（包括名称，构成列）：
SELECT COLUMN_NAME FROM USER_CONS_COLUMNS CU, USER_CONSTRAINTS AU
WHERE CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME AND AU.CONSTRAINT_TYPE = "U" AND AU.TABLE_NAME = "tablename";
--4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）：
SELECT * FROM USER_CONSTRAINTS C WHERE C.CONSTRAINT_TYPE = "R" AND C.TABLE_NAME = "tablename";
--查询外键约束的列名：
SELECT * FROM USER_CONS_COLUMNS CL WHERE CL.CONSTRAINT_NAME = "pkname";
--查询引用表的键的列名：
SELECT * FROM USER_CONS_COLUMNS CL WHERE CL.CONSTRAINT_NAME = "外键引用表的键名";
--5、查询表的所有列及其属性
SELECT T.*,C.COMMENTS FROM USER_TAB_COLUMNS T,USER_COL_COMMENTS C
WHERE T.TABLE_NAME = C.TABLE_NAME AND T.COLUMN_NAME = C.COLUMN_NAME AND T.TABLE_NAME = "tablename";更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址