ORACLE 日常信息查询sql

--查看用户信息
select * from dba_users where 
--temporary_tablespace='TEMP_ETL2'
username like '%DGP_ETL%'

--查询用户权限
select * from dba_sys_privs 
where grantee like '%ZBA_CZC%' 
order by 1

--查看用户下的角色
select * from dba_role_privs 
where grantee like '%ZBA_CZC%' 
order by 1

--查看角色下的权限
select * from role_sys_privs where role=xujin1;

--查询用户使用表空间限制
select a.*,max_bytes/1024/1024/1024 GB from DBA_TS_QUOTAS a where username='DGP_ETL';

--修改用户对表空间的使用权
alter user DGP_ETL quota unlimited on TBS_DGP;
alter user DGP_ETL quota 100M on TBS_DGP; 
create user anqing identified by anqing default tablespace users temporary tablespace temp quota 10M on users;

--查询用户信息
select * from dba_users where username='UNIONMON'

--解锁用户
alter user ZB_HESPER account unlock

--创建新用户
create user unionmon identified by "unionmon" default tablespace SYSTEM temporary tablespace TEMP profile DEFAULT;

--修改用户默认表空间
ALTER USER UNIONMON DEFAULT TABLESPACE SYSTEM;

--修改用户默认临时表空间
ALTER USER ZBG_DWA temporary TABLESPACE TEMP_ETL1;

--查询dblike
SELECT * FROM DBA_DB_LINKS
where 
--host like '%132.35.224.165%' 
db_link like '%DSSDB_DFW%'

---创建dblink
create public database link DSSDB_DFW connect to oratest identified by oratest2017
using ' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.161.35.133)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orassoc)
)
)';

--删除dblink
drop public database link dblinkname;

--查看用户回话限制
select a.username,b.profile,b.limit from dba_users a,dba_profiles b
where b.profile=a.profile
and b.profile not in ( 'SYS_PROFILE','DEFAULT')
and resource_name='SESSIONS_PER_USER'
--and b.profile like '%SESS_ZBA_CZC%'
--and a.users like '%%'

--修改PROFILE
alter profile SESS_ZBA_DEV limit sessions_per_user 8

--批量创建profile
select 'create profile '||username||'_LIMIT sessions_per_user 20' from dba_users

--批量赋予用户profile
select 'alter user '||username||' profile '||username||'_LIMIT' from dba_users

--创建一个profile限制会话连接数20个名字叫sess_xx
create profile sess_bingo limit sessions_per_user 20

--将profile sess_xx给用户xxx
alter user scott profile DEFAULT;
alter user scott profile sess_bingo

--查看sga的shared poll
select bytes/1024/1024/1024 from v$sgastat where name = 'free memory' and pool='shared pool'

--12c查看内存/cpu/进程/回话
select hostname,round(100-free_mem_gb/total_mem_gb*100)||'%' as "MEM",
cpu_idle,amount_process,time_id,amount_sess
from system.daily_mon@link_12c_mon 
where to_char(time_id,'mmdd')=0410 
and to_char(time_id,'hh24') between '15' and '16'
order by time_id,1

--日志异常
select * from v$log
order by status,first_time