oracle 对象查询

查询Oracle自身对象的语句

2009年08月08日星期六下午 05:30

查询全部表空间 --> select * from DBA_TABLESPACES

查询用户表空间 --> select * from DBA_TABLESPACES where (tablespace_name <> 'SYSTEM' and tablespace_name <> 'SYSAUX' and tablespace_name <>

'UNDOTBS1' and tablespace_name <> 'EXAMPLE')

查询用户TEMPORARY表空间 --> select * from DBA_TABLESPACES where (tablespace_name <> 'SYSTEM' and tablespace_name <> 'SYSAUX' and tablespace_name <> 'UNDOTBS1' and tablespace_name <> 'EXAMPLE') and contents = 'TEMPORARY'

查询用户PERMANENT表空间 --> select * from DBA_TABLESPACES where (tablespace_name <> 'SYSTEM' and tablespace_name <> 'SYSAUX' and tablespace_name <> 'UNDOTBS1' and tablespace_name <> 'EXAMPLE') and contents = 'PERMANENT'

查询当前是开放的可以使用的全部用户 --> select * from DBA_USERS where account_status = 'OPEN'

查询当前是开放的可以使用的自定义用户 --> select * from DBA_USERS where account_status = 'OPEN' and username <> 'SYSTEM' and username <> 'SYS' and username <> 'SYSMAN' and username <> 'DBSNMP' and username <>

'MGMT_VIEW'

查询全部的表对象 --> select * from ALL_OBJECTS where object_type =

'TABLE'

查询开放的可以使用的全部用户的全部的表对象 --> select * from

ALL_OBJECTS where object_type = 'TABLE' and owner in (select username from DBA_USERS where account_status = 'OPEN')

查询开放的可以使用的自定义用户的全部的表对象 --> select * from

ALL_OBJECTS where object_type = 'TABLE' and owner in (select username from DBA_USERS where account_status = 'OPEN' and username <> 'SYSTEM' and username <> 'SYS' and username <> 'SYSMAN' and username <> 'DBSNMP' and username <> 'MGMT_VIEW')

查询某用户可操作的所有表对象 --> select * from ALL_OBJECTS where owner = '用户对象名称' and object_type = 'TABLE'

查询某表对象 --> select * from ALL_OBJECTS where object_name = '表对象名称' and object_type = 'TABLE'

查询全部的视图对象 --> select * from ALL_OBJECTS where object_type = 'VIEW'

查询开放的可以使用的全部用户的全部的视图对象 --> select * from

ALL_OBJECTS where object_type = 'VIEW' and owner in (select username from DBA_USERS where account_status = 'OPEN')

查询开放的可以使用的自定义用户的全部的视图对象 --> select * from

ALL_OBJECTS where object_type = 'VIEW' and owner in (select username from

DBA_USERS where account_status = 'OPEN' and username <> 'SYSTEM' and username <> 'SYS' and username <> 'SYSMAN' and username <> 'DBSNMP' and username <> 'MGMT_VIEW')

查询某用户可操作的所有视图对象 --> select * from ALL_OBJECTS where owner = '用户对象名称' and object_type = 'VIEW'

查询全部的表对象的注释对象 --> select * from ALL_TAB_COMMENTS where table_type = 'TABLE'

查询开放的可以使用的全部用户的全部的表对象的注释对象 --> select * from ALL_TAB_COMMENTS where table_type = 'TABLE' and owner in (select username from DBA_USERS where account_status = 'OPEN')

查询开放的可以使用的自定义用户的全部的表对象的注释对象 --> select * from ALL_TAB_COMMENTS where table_type = 'TABLE' and owner in (select username from DBA_USERS where account_status = 'OPEN' and username <> 'SYSTEM' and username <> 'SYS' and username <> 'SYSMAN' and username <> 'DBSNMP' and username <> 'MGMT_VIEW')

查询某用户可操作的所有表对象的注释对象 --> select * from

ALL_TAB_COMMENTS where owner = '用户对象名称' and table_type = 'TABLE' 查询某表对象的注释对象 --> select * from ALL_TAB_COMMENTS where

table_name = '表对象名称' and table_type = 'TABLE'

查询全部的视图对象的注释对象 --> select * from ALL_TAB_COMMENTS where table_type = 'VIEW'

查询开放的可以使用的全部用户的全部的视图对象的注释对象 --> select * from ALL_TAB_COMMENTS where table_type = 'VIEW'and owner in (select username from DBA_USERS where account_status = 'OPEN')

查询开放的可以使用的自定义用户的全部的视图对象的注释对象 --> select * from ALL_TAB_COMMENTS where table_type = 'VIEW'and owner in (select username from DBA_USERS where account_status = 'OPEN' and username <> 'SYSTEM' and username <> 'SYS' and username <> 'SYSMAN' and username <> 'DBSNMP' and username <> 'MGMT_VIEW')

查询某用户可操作的所有视图对象的注释对象 --> select * from

ALL_TAB_COMMENTS where owner = '用户对象名称' and table_type = 'VIEW' 查询某视图对象的注释对象 --> select * from ALL_TAB_COMMENTS where table_name = '视图对象名称' and table_type = 'VIEW'

查询开放的可以使用的全部用户的全部的字段对象 --> select * from

ALL_TAB_COLUMNS where owner in(select username from DBA_USERS where account_status = 'OPEN')

查询开放的可以使用的自定义用户的全部的字段对象 --> select * from

ALL_TAB_COLUMNS where owner in(select username from DBA_USERS where account_status = 'OPEN' and username <> 'SYSTEM' and username <> 'SYS' and username <> 'SYSMAN' and username <> 'DBSNMP' and username <>

'MGMT_VIEW')

查询某表对象所有的字段对象 --> select * from ALL_TAB_COLUMNS where table_name = '表对象名称'

查询全部的字段对象的注释对象 --> select * from ALL_TAB_COMMENTS where table_type = 'TABLE'

查询开放的可以使用的全部用户的全部的字段对象的注释对象 --> select * from ALL_TAB_COMMENTS where table_type = 'TABLE' and owner in (select username from DBA_USERS where account_status = 'OPEN')

查询开放的可以使用的自定义用户的全部的字段对象的注释对象 --> select * from ALL_TAB_COMMENTS where table_type = 'TABLE' and owner in (select username from DBA_USERS where account_status = 'OPEN' and username <> 'SYSTEM' and username <> 'SYS' and username <> 'SYSMAN' and username <> 'DBSNMP' and username <> 'MGMT_VIEW')

查询开放的可以使用的全部用户的某表对象的所有字段对象的注释对象 --> select * from ALL_TAB_COMMENTS where table_type = 'TABLE' and owner in (select username from DBA_USERS where account_status = 'OPEN') and table_name = '表对象名称'

查询开放的可以使用的自定义用户的某表对象的所有字段对象的注释对象 --> select * from ALL_TAB_COMMENTS where table_type = 'TABLE' and owner in (select username from DBA_USERS where account_status = 'OPEN' and username <> 'SYSTEM' and username <> 'SYS' and username <> 'SYSMAN' and username <> 'DBSNMP' and username <> 'MGMT_VIEW') and table_name = '表对象名称'

查询某用户可操作的某字段对象的注释对象 --> select * from

ALL_TAB_COMMENTS where owner = '用户对象名称' and table_type = 'TABLE' and coloumn_name='字段对象名称'

查询某表对象的注释对象 --> select * from ALL_TAB_COMMENTS where

table_name = '表对象名称' and table_type = 'TABLE'

查询某表对象的主键字段对象 --> select tc.* from all_cons_columns cu, ALL_CONSTRAINTS au, all_tab_columns tc where cu.constraint_name = au.constraint_name and cu.owner = tc.owner and cu.table_name =

tc.table_name and cu.column_name = tc.column_name and au.constraint_type = 'P' and au.table_name = '主表对象名称' order by cu.position

查询某表对象的外键字段对象 --> select tc.* from all_cons_columns cu, ALL_CONSTRAINTS au, all_tab_columns tc where cu.constraint_name = au.constraint_name and cu.owner = tc.owner and cu.table_name =

tc.table_name and cu.column_name = tc.column_name and au.constraint_type = 'R' and au.table_name = '主表对象名称' order by cu.constraint_name, cu.position

查询数据库链接 --> Select * from user_db_links;

查询主外键-->

select INDEX_NAME 索引名, b.TABLE_NAME 主键表名, a.TABLE_NAME 外键表名, CONSTRAINT_TYPE, CONSTRAINT_NAME 约束名

from all_indexes a, all_constraints b

where b.TABLE_NAME='AC' AND CONSTRAINT_TYPE IN('P','R')

and R_CONSTRAINT_NAME=INDEX_NAME(+)