Oracle数据库一些信息查询

----用户
--查看当前用户缺省表空间
select uu.UserName,uu.Default_Tablespace from user_users uu;
--查看当前用户角色
select * from user_role_privs;
--查看当前用户系统权限
select * from user_sys_privs;
--查看当前用户表级权限
select * from user_tab_privs;
--查看当前回话所具有的权限
select * from session_privs;
--显示指定用户所具有的系统权限
select * from dba_sys_privs where grantee = 'SPDC'

----表
--查看用户下的所有表
select * from user_tables where instr(table_name,'_201')>0
--查看名称包含log字符的表
select * from user_objects uo where instr(uo.OBJECT_NAME,'CLASS') > 0;
--查看某表创建时间
select object_name,created from user_objects where object_name = upper('business_apply');
--查看某表大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments us where us.segment_name = upper('business_apply');
--查看存放在oracle的内存区里的表
select table_name,cache from user_tables where instr(cache,'Y')>0;

----索引
--查看索引个数和类别
select index_name,index_type,table_name from user_indexes order by table_name;
--查看索引被索引的字段
select * from user_ind_columns where table_name = upper('business_apply');
--查看索引大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments us where us.segment_name = upper('PK_BUSINESS_APPLY');

----序列号
--查看序列号，last_number是当前值
select * from user_sequences;

----视图
--查看视图名称
select * from user_views;

----同义词
--查看同义词的名称
select * from user_synonyms;

----约束条件
--查看某表的约束条件
select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper('&table_name');
select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c,user_cons_columns cc where c.owner = upper('&table_owner') and c.table_name = upper('&table_name') and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position;

----存储过程和函数
--查看过程和函数状态
select * from user_objects where object_type = upper('function');
select * from user_objects where object_type = upper('procedure');
--查看过程和函数源代码
select * from all_source where owner = upper('spdc') and name = 'PROC_BATCH_LCBILL'

--在V$process动态性能视图中可以查询到每个Oracle进程的PGA分配的内存和已使用的内存情况，
--其中PGA_used_mem表示已使用的，pag_alloc_mem表示已分配的，pga_max_men表示PGA的最大值。
select pid,pga_used_mem,pga_alloc_mem,pga_max_mem from v$process;
--查看后台进程：
select * from v$bgprocess where paddr <> '00';
--查看所有的表空间；
select tablespace_name from dba_data_files order by tablespace_name;
--查看表空间的名字及大小:
select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_size
  from dba_tablespaces t, dba_data_files d
 where t.tablespace_name = d.tablespace_name
 group by t.tablespace_name;
--查看表空间中数据文件存放的路径：
select tablespace_name, bytes/1024/1024 file_size_mb, file_name from dba_data_files;