oracle日常运维巡检,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;