In this post, I’m sharing some handy PL/SQL queries that help with monitoring and maintaining Oracle databases. As I learn new scripts, I’ll keep adding them to this list.
Tables
To check the source code
ALL_SOURCE is a data dictionary view that shows the PL/SQL source code (line by line) of procedures, functions, packages, triggers, and types that the current user has access to, even if they are not the owner.
To search any database objects:
SELECT line, text
FROM all_source
WHERE name = 'MY_PROCEDURE'
AND type = 'PROCEDURE' -- Add any data objects here
ORDER BY line;
To get the full code as a single block, you can use LISTAGG
SELECT LISTAGG(text, '') WITHIN GROUP (ORDER BY line) AS full_code
FROM all_source
WHERE name = 'MY_PROCEDURE'
AND type = 'PROCEDURE' -- Add any data objects here
AND owner = 'OWNER';
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;
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%';
To view active user session details
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';
What are your thoughts on this post?
I’d love to hear from you! Click this link to email me, I reply to every message!
Also use the share button below if you liked this post. It makes me smile, when I see it.