Oracle 常用字典表查询

查看表结构：

desc表名

查看表名：

select * from all_tables/dba_tables;

查看字段:

select * from user_tab_columns;

select * from user_tables;  

select * from tab/cat ;

查看约束关系:

select * from user_constraints;

查看约束关系字段:

select * from user_cons_columns;

查看对象:

select * from  dba_objects/all_objects/user_objects;

查看触发器:

select * from user_triggers;

查看索引:

select * from user_indexs;

Oracle查询所有函数和储存过程：

select * from user_source;

 

Oracle查看当前会话：

select * from v$Session;

Oracle查看当前会话权限：

select * from session_privs;

 

Oracle查询所有用户：

select * from all_users;

select * from dba_users;

select * from user_users;

Oracle查看用户权限：

select * from dba_sys_privs;

select * from all_sys_privs;

select * from user_sys_privs;

 

 

 

查看所有角色：

  select * from dba_roles;

查看用户所拥有的角色：

  select * from dba_role_privs;

  select * from user_role_privs;

查看角色所拥有的权限：

  select * from role_sys_privs;

  select * from role_tab_privs;

查看所有系统权限：

  select * from system_privilege_map;

查看所有对象权限：

  select * from table_privilege_map;

 

Oracle查询用户表空间：

select * from user_tablespaces;

select * from dba_tablespaces;

查看用户和默认表空间的关系：   

select   username,default_tablespace   from   dba_users;

 

Oracle查看用户表空间使用情况：

select a.a1 Tablespace_Name,
       c.c2 type_of_tablespace,
       c.c3 section_of_tablespace,
       b.b2/1024/1024 Size_of_tablespace,
       (b.b2-a.a2)/1024/1024 size_of_used,
       substr((b.b2-a.a2)/b.b2*100,1,5) percent_of_used
from (select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
     (select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
     (select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1
order by a.a1

Oracle 用户表空间文件：

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
where tablespace_name = 'USERS'
order by tablespace_name;

 

得到数据库版本

select * from product_component_version;

 

查看系统参数

显示session的参数值

select * from v$parameter2;

显示instance的参数值

select * from v$system_parameter;

select * from v$system_parameter2;

显示spfile的参数值

select * from v$spparameter;