Oracle日常运维常用查询

--查询当前用户有哪些权限    
 select * from user_sys_privs;               --查看直接授予用户的系统权限

 select * from user_role_privs;              --用户有哪些角色

 select * from user_tab_privs;               --检查一个用户被授予和授予其他用户的对象级权限

 select * from dba_roles;                    --查询系统中的角色

 select * from dba_sys_privs;                --查询角色的权利

 select * from dba_role_privs;               --查询角色是谁定义的

 select * from column_privileges;            --

 select * from role_role_privs;              --查询角色的授予和被授予关系

 select * from role_sys_privs;               --查询角色被授予的系统级权限

 select * from role_tab_privs;               --查询角色的对象级权限
 
 
--查询一个库下面每个用户用了多少空间
select owner,sum(bytes)/1024/1024 "used(M)" from dba_segments
 group by owner having   owner in
 (select  username from dba_users)
order by 2 desc

--查询每一个用户对数据库空间的使用情况
select distinct  t.*, ds.tablespace_name from dba_segments ds,
(select owner,sum(bytes)/1024/1024 "used(M)" from dba_segments
 group by owner having   owner in
 (select  username from dba_users))t
where ds.owner=t.owner
order by 2 desc

--修改密码的几种方式
alter user identified by ""
alter user identified by values ""  --这个是利用密文进行密码修改  

--用户解锁
alter user  username account  unlock;

--给列改名

alter table text rename column textpass to password

 

--查询高水位线以下某个表的blocks数= fs1_blocks+ fs2_blocks+ fs3_blocks+ fs4_blocks+ full_blocks 

set serveroutput on
declare
  v_unformatted_blocks number;
  v_unformatted_bytes number;
  v_fs1_blocks number;
  v_fs1_bytes number;
  v_fs2_blocks number;
  v_fs2_bytes number;
  v_fs3_blocks number;
  v_fs3_bytes number;
  v_fs4_blocks number;
  v_fs4_bytes number;
  v_full_blocks number;
  v_full_bytes number;
begin
  dbms_space.space_usage ('EPCISUDWR', 'APPLY_NOTICE_INFO', 'TABLE', v_unformatted_blocks,
  v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
  v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
  dbms_output.put_line('Unformatted Blocks    = '||v_unformatted_blocks);
  dbms_output.put_line('FS1 Blocks            = '||v_fs1_blocks);
  dbms_output.put_line('FS2 Blocks            = '||v_fs2_blocks);
  dbms_output.put_line('FS3 Blocks            = '||v_fs3_blocks);
  dbms_output.put_line('FS4 Blocks            = '||v_fs4_blocks);
  dbms_output.put_line('Full Blocks           = '||v_full_blocks);
end;