posted Mar 31, 2013, 12:22 PM by Jageshwar Tripathi
Log in to oracle with DBA user (user having dba rights) How to list sessions to a database?SELECT MACHINE, STATE, STATUS, USERNAME, SCHEMANAME, OSUSER, PROGRAM, LOGON_TIME from V$SESSIONHow to monitor used temp tablespace?SELECT TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER; select TABLESPACE_NAME, BYTES_USED/(1024*1024*1024), (BYTES_FREE)/(1024*1024*1024) from V$TEMP_SPACE_HEADER;
Temporary segmentsSELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;Sort space use by sessionSELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial;Sort space usage by statementSELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.sqladdr address, Q.hash_value, Q.sql_text FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name ORDER BY S.sid;How to see what DB/Schema is doing?SELECT S.USERNAME||'('||s.sid||')-'||s.osuser UNAM ,s.program||'-'||s.terminal||'('||s.machine||')' PROG ,s.sid||'/'||s.serial# sid ,s.status "Status",p.spid ,sql_text sqltext from v$sqltext_with_newlines t,V$SESSION s , v$process p where t.address =s.sql_address and p.addr=s.paddr(+) and t.hash_value = s.sql_hash_value and s.machine like '%VMCAST-ANA-ST-4%' order by s.sid,t.piece
|
|