Oracle-Scripts

1. 监视db link 会话信息

-- exec command on both db
select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
s2.username,
substr(
   decode(bitand(ksuseidl,11),
      1,'ACTIVE',
      0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
      2,'SNIPED',
      3,'SNIPED',
      'KILLED'
   ),1,1
) "S",
substr(w.event,1,10) "WAITING"
from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2
where  g.K2GTDXCB =t.ktcxbxba
and   g.K2GTDSES=t.ktcxbses
and  s.addr=g.K2GTDSES
and  w.sid=s.indx
and s2.sid = w.sid;

-- 2. 跟踪dblink会话 -- Tracing session created through dblink (Doc ID 258754.1)

1) On the remote site, ensure no sessions running for the required username:

SQL> select * from v$session where username = 'user_name';

Replace "user_name" with the username mentioned in the create dblink statement after "connect to " clause.

2) From "local" site, start an sqlplus session. From this session, start a remote session using same dblink using a simple select with dblink :
select count(*) from dual@dblink;
Leave this session open.

3)On the "remote" site, find out the session id of the session created by
above mentioned dblink :
select sid, username, machine from v$session;
MACHINE column will help to identify the SID.

In case of multiple outputs with same username,machine combination, use the output from step1 to identify the new session created.

And, using SID, get the Process ID of the problem session from the
V$PROCESS:
SELECT pid FROM v$process
WHERE addr =
(SELECT paddr FROM v$session
WHERE sid = sid_of_problem_session);

4) Start tracing on remote site using PID obtained above :
SQL> connect / as sysdba
SQL> oradebug setorapid
SQL> oradebug unlimit
SQL> oradebug event 10046 trace name context forever, level 12

On the local site from the same session :
Execute the Procedure / query involving dblink.

2. 查询隐含参数

set lines 168 pagesize 999
col name for a50
col value for a32
col describ for a64

-- 隐含参数 (Implicit parameter)
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND upper(x.ksppinm) LIKE upper('%&&par%');

3. 配置用户对表空间配额管理

-- 查看用户表空间的限额(max_bytes 值为-1时，代表无限制)
set lines 168 pages 99
col TABLESPACE_NAME for a18
col USERNAME for a16
col DROPPED for a10
select TABLESPACE_NAME, USERNAME, round(BYTES/1024/1024) size_mb, round(MAX_BYTES/1024/1024) max_size_mb, DROPPED 
from dba_ts_quotas 
order by TABLESPACE_NAME, USERNAME;

-- 对用户进行表空间配额配置控制
-- # 无限制
GRANT UNLIMITED TABLESPACE TO username;
alter user username quota unlimited on tablespace_name;

-- # 指定配额
alter user username quota 2048m on tablespace_name;

-- # 回收权限
revoke unlimited tablespace from username;
alter user username quota 0 on tablespace_name;

View Code

4. 查找DBA角色用户

-- 查找oracle数据库DBA角色用户 find administrator users
select username, granted_role
  from (select distinct connect_by_root grantee username, granted_role
          from dba_role_privs
        connect by prior granted_role = grantee) a
 where a.granted_role = 'DBA';