How to identify the Top objects in database for Particular Schema?
–Below Query to identify the Top 200 objects in schema—
SELECT owner, master_table, size_mb
FROM (SELECT owner, master_table, SUM (size_mb) size_mb
FROM (SELECT a.owner, a.segment_name, a.segment_type,
a.tablespace_name, a.size_mb,
CASE
WHEN a.segment_type = ‘INDEX’
THEN (SELECT c.table_name
FROM dba_indexes c
WHERE c.owner = a.owner
AND c.index_name = a.segment_name)
WHEN a.segment_type = ‘LOBSEGMENT’
THEN (SELECT b.table_name
FROM dba_lobs b
WHERE b.owner = a.owner
AND b.segment_name = a.segment_name)
WHEN a.segment_type = ‘LOBINDEX’
THEN (SELECT b.table_name
FROM dba_lobs b
WHERE b.owner = a.owner
AND b.index_name = a.segment_name)
ELSE a.segment_name
END master_table
FROM (SELECT /*+ no_merge cardinality(50) */
owner, segment_name, segment_type,
tablespace_name, size_mb
FROM (SELECT owner, segment_type, segment_name,
tablespace_name,
BYTES / 1024 / 1024 size_mb
FROM dba_segments
WHERE owner = ‘&OWNNAME’
AND tablespace_name IN (‘&tabname’)
ORDER BY size_mb DESC)
WHERE ROWNUM <= 200) a)
GROUP BY owner, master_table
ORDER BY size_mb DESC)
WHERE ROWNUM <= 200;
above Query, will return top 200 segments in schema, also it will related the correlated result for a Table with its associated objects like (index,lobs)..
Another query to identify TOP objects in schema,
select S.owner, S.segment_name, S.segment_type, S.bytes/1024/1024,T.NUM_ROWS,sysdate
from dba_segments s ,dba_tables t
where (S.OWNER IN (‘WM_TM_AUDIT’)
AND s.owner=t.owner
and s.segment_type IN (‘TABLE’)
AND S.SEGMENT_NAME=T.TABLE_NAME)
UNION ALL
select S.owner, S.segment_name, S.segment_type, S.bytes/1024/1024,T.NUM_ROWS,sysdate
from dba_segments s
join dba_indexes t
on (S.OWNER in(‘WM_TM_AUDIT’) AND s.owner=t.owner and s.segment_type=’INDEX’ AND S.SEGMENT_NAME=T.INDEX_NAME)
UNION ALL
select S.owner, X.TABLE_NAME||’-‘||S.segment_name, S.segment_type, S.bytes/1024/1024,x.NUM_ROWS,sysdate
from dba_segments s, DBA_LOBS T, dba_tables x
where S.OWNER in (‘WM_TM_AUDIT’)
AND s.owner=t.owner
and s.segment_type IN (‘LOBSEGMENT’)
AND S.segment_NAME=T.segment_NAME
and s.owner=x.owner
AND t.table_NAME=x.TABLE_NAME
UNION ALL
select S.owner, X.TABLE_NAME||’-‘||S.segment_name, S.segment_type, S.bytes/1024/1024,x.NUM_ROWS,sysdate
from dba_segments s, DBA_LOBS T, dba_tables x
where S.OWNER in(‘WM_TM_AUDIT’)
AND s.owner=t.owner
and s.segment_type IN (‘LOBINDEX’)
AND S.segment_NAME=T.index_NAME
and s.owner=x.owner
AND t.table_NAME=x.TABLE_NAME
How to get the indexes on the available on particular table?
SELECT z.index_owner, z.table_name, dt.num_rows, z.index_name,
LTRIM (z.column_list, ‘,’) column_list
FROM (SELECT x.index_owner, x.table_name, x.index_name,
SYS_CONNECT_BY_PATH (x.column_name, ‘,’) column_list,
lvl lvl, LEVEL list_lvl
FROM (SELECT index_owner, table_name, index_name, column_name,
column_position,
LAG (column_position) OVER (PARTITION BY index_name ORDER BY column_position)
column_position_next,
MAX (column_position) OVER (PARTITION BY index_name ORDER BY column_position ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
lvl
FROM dba_ind_columns
WHERE (index_owner, index_name, table_name) IN (
SELECT owner, index_name, table_name
FROM dba_indexes
WHERE owner = UPPER (:username)
AND table_name = UPPER (:tabname))) x
START WITH x.column_position = 1
CONNECT BY PRIOR x.index_name = x.index_name
AND PRIOR x.column_position = x.column_position_next) z,
dba_tables dt
WHERE z.lvl = z.list_lvl
AND z.index_owner = UPPER (:username)
AND dt.owner = UPPER (:username)
AND dt.table_name = z.table_name
ORDER BY z.index_owner, z.table_name, z.index_name
How To identify the HW for datafiles of given tablespace?
select a.tablespace_name, a.file_name,a.file_id, ceil( (nvl(hwm,1)*8192)/1024/1024/1024 ) “Mo”
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents WHERE TABLESPACE_NAME=’SYSTEM’
group by file_id
) b
where a.file_id = b.file_id(+) and a.tablespace_name=’SYSTEM’
order by tablespace_name, file_name,file_id
Query to identify the session blocking sessions?
select l1.sid, ‘ IS BLOCKING ‘, l2.sid
from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
Use of DBA_BLOCKERS
The DBA_BLOCKERS will share the sessions which block the other sessions.
To get the details of BLOCKING SESSION, please use DBA_BLOCKERS , V$SESSION
select inst_id,sid,serial#,username,machine,terminal,sql_id,osuser,prev_sql_id,port,status,event from gv$session where sid in (select * from dba_blockers);
Use of DBA_WAITERS
select inst_id,sid,serial#,username,machine,terminal,sql_id,osuser,prev_sql_id,port,status,event,blocking_session from gv$session where sid in (select waiting_session from dba_waiters);
Active Transactions details
SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,20) ORA_USER, SUBSTR(L.SESSION_ID,1,10) SID,
S.serial#,
SUBSTR(O.OWNER||’.’||O.OBJECT_NAME,1,40) OBJECT,o.OBJECT_ID, P.SPID OS_PID,s.machine,s.osuser,s.sql_id,s.status,s.terminal,s.module,s.program,sysdate,–sq.sql_text,
DECODE(L.LOCKED_MODE, 0,’NONE’,
1,’NULL’,
2,’ROW SHARE’,
3,’ROW EXCLUSIVE’,
4,’SHARE’,
5,’SHARE ROW EXCLUSIVE’,
6,’EXCLUSIVE’,
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P–v$sql sq
WHERE L.OBJECT_ID = O.OBJECT_ID and s.username=’&USERNAME’
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id;
–IDENTIFY THE BLOCKING SESSIONS FROM V$SESSION—
select sid,serial#,username,osuser,terminal,prev_sql_id,program from gv$session where blocking_session_status=’VALID’ and blocking_session is not null
select inst_id, sid,serial#,username,osuser,terminal,machine,prev_sql_id,program,sql_id,seconds_in_wait/60/60,state,row_wait_obj# from gv$session where sid in (select blocking_session from gv$session)
–Active Transaction–
Identify the active Trsanctions of a give Username
SELECT s.service_name, s.machine, s.event, v.status, v.start_time, s.SID,
s.serial#, s.username, s.status, s.sql_id
FROM gv$session s, gv$transaction v
WHERE s.username = ‘&USERNAME’
AND s.taddr IS NOT NULL
AND s.taddr = v.addr;
select * from gv$session where taddr in (
select addr from gv$transaction where username=’&USERNAME’);
Usage of V$SQL_MONITOR
Its Very useful View from Oracle 11gr2 , it helps identify the TOP executing queries and recently executed queries of particular Username.
select USERNAME,SERVICE_NAME,STATUS,SQL_ID,SQL_TEXT from gv$sql_monitor where username=’&username’ order by sql_exec_start desc;
If we need to identify when the particular SQLID is excuted,
select USERNAME,SERVICE_NAME,STATUS,SQL_ID,SQL_TEXT from gv$sql_monitor where sqlid=’&SQLID’ order by sql_exec_start desc;
oracledbissues 