This is cheat sheet for basic database monitoring PL SQL queries.
Tables
ALL_TABLES describes the relational tables accessible to the current user. To gather statistics for this view, use the DBMS_STATS package.
SELECT table_name FROM all_tables
WHERE tablespace_name = 'EXAMPLE' ORDER BY table_name;
DBA_TABLES describes all relational tables in the database.
SELECT * from DBA_TABLES;
USER_TABLES describes the relational tables owned by the current user. This view does not display the OWNER column.
SELECT * from USER_TABLES;
To check the table size in the current schema.
select segment_name,segment_type, sum(bytes/1024/1024/1024) GB
from dba_segments
where segment_name='&TableName'
group by segment_name,segment_type;
References
Sessions
To kill a session in Oracle
SELECT OBJECT_NAME, 'ALTER SYSTEM KILL SESSION ''' || VS.SID || ',' || VS.SERIAL# || ''' ;' ,
AO.OWNER, MACHINE
FROM V$SESSION VS
LEFT JOIN V$LOCK VL ON VS.SID = VL.SID
LEFT JOIN ALL_OBJECTS AO ON VL.ID1 = AO.OBJECT_ID
LEFT JOIN USER_TABLES UT ON AO.OBJECT_NAME = UT.TABLE_NAME
WHERE OBJECT_NAME LIKE '%TABLE_NAME%';
SELECT s.sid, s.serial#, p.spid, s.username, s.program
FROM v$session s JOIN v$process p ON p.addr = s.paddr
WHERE s.type != 'BACKGROUND';
Use the share button below if you liked it.
It makes me smile, when I see it.