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;
4.