--查看用户信息

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