Hi Guys,
I am blogging after a long time, nevertheless here is a knol about SOA administration (from DB perceptive) what I gained from an assignment in middle-east 10g project.
For long running queries with long running operations :
col pct_comp for a10
col INST_ID for 99
col opname for a25
col SId for 99999
col message for a35
col STARTED for a25
col OPNAME for a23
SELECT INST_ID,SID,SERIAL#,
OPNAME, substr(message,1,instr(message,':',-1)) "message",
TO_CHAR(START_TIME,'DD-MON-YYYY HH12:MI:SS AM') STARTED,
sofar, totalwork, round(elapsed_seconds/60,2) "Elaps(min)", round(TIME_REMAINING/60,2) "TimeRem(min)", round(SOFAR/TOTALWORK*100,2)||'%'
"PCT_COMP" FROM GV$SESSION_LONGOPS WHERE
sofar<totalwork
ORDER BY ELAPSED_SECONDS DESC;
For Blocking locks :
col oracle_username for a10
col terminal for a14
col object_name for a30
col os_user_name for a10
col machine for a15
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l1.INST_ID=l2.INST_ID
and l2.id2 = l2.id2 ;
For finding objects that are locked but not blocked :
col oracle_username for a10
col terminal for a14
col object_name for a30
col os_user_name for a10
col machine for a15
select distinct a.inst_id,a.oracle_username,a.os_user_name,b.machine,b.terminal,c.object_name,a.session_id,b.serial#, a.process
from gv$locked_object a, gv$session b , dba_objects c
where a.session_id=b.sid AND a.object_id=c.object_id
order by 4;
Connections by username :
select stragg(decode(username,null,decode(greatest(cnt,80),cnt,'Total Connections are ',' Other'),USERNAME) ||' '||cnt ) from (select username,count(1) cnt from gv$session group by cube(username) order by cnt desc ) ;
Total Size of DB used :
select round(sum(bytes)/1024/1024/1024,0) from dba_segments;
Total space available in DB for usage :
select to_char(round(free_mb/decode(type, 'NORMAL',2, 'HIGH',3, 'EXTERN', 1)/1024,0))
from v$asm_diskgroup_stat where name = '<DISKGROUP_NAME>' and state= 'CONNECTED';
Connecting from sqlplus when tnsnames.ora is not editable:
sqlplus scott/tiger@'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <DB Host ip or hostname>) (PORT = <DB Port mostly 1521>))) (CONNECT_DATA = (SERVICE_NAME = <DB_Service_name>)))'
I am blogging after a long time, nevertheless here is a knol about SOA administration (from DB perceptive) what I gained from an assignment in middle-east 10g project.
For long running queries with long running operations :
col pct_comp for a10
col INST_ID for 99
col opname for a25
col SId for 99999
col message for a35
col STARTED for a25
col OPNAME for a23
SELECT INST_ID,SID,SERIAL#,
OPNAME, substr(message,1,instr(message,':',-1)) "message",
TO_CHAR(START_TIME,'DD-MON-YYYY HH12:MI:SS AM') STARTED,
sofar, totalwork, round(elapsed_seconds/60,2) "Elaps(min)", round(TIME_REMAINING/60,2) "TimeRem(min)", round(SOFAR/TOTALWORK*100,2)||'%'
"PCT_COMP" FROM GV$SESSION_LONGOPS WHERE
sofar<totalwork
ORDER BY ELAPSED_SECONDS DESC;
For Blocking locks :
col oracle_username for a10
col terminal for a14
col object_name for a30
col os_user_name for a10
col machine for a15
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l1.INST_ID=l2.INST_ID
and l2.id2 = l2.id2 ;
For finding objects that are locked but not blocked :
col oracle_username for a10
col terminal for a14
col object_name for a30
col os_user_name for a10
col machine for a15
select distinct a.inst_id,a.oracle_username,a.os_user_name,b.machine,b.terminal,c.object_name,a.session_id,b.serial#, a.process
from gv$locked_object a, gv$session b , dba_objects c
where a.session_id=b.sid AND a.object_id=c.object_id
order by 4;
Connections by username :
select stragg(decode(username,null,decode(greatest(cnt,80),cnt,'Total Connections are ',' Other'),USERNAME) ||' '||cnt ) from (select username,count(1) cnt from gv$session group by cube(username) order by cnt desc ) ;
Total Size of DB used :
select round(sum(bytes)/1024/1024/1024,0) from dba_segments;
Total space available in DB for usage :
select to_char(round(free_mb/decode(type, 'NORMAL',2, 'HIGH',3, 'EXTERN', 1)/1024,0))
from v$asm_diskgroup_stat where name = '<DISKGROUP_NAME>' and state= 'CONNECTED';
Connecting from sqlplus when tnsnames.ora is not editable:
sqlplus scott/tiger@'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <DB Host ip or hostname>) (PORT = <DB Port mostly 1521>))) (CONNECT_DATA = (SERVICE_NAME = <DB_Service_name>)))'
