5 SQL Queries Every Oracle DBA Needs

5 SQL Queries Every Oracle DBA Needs

In this post, I am sharing 5 SQL queries which are most needed, especially when we have only SQL*PLUS to work with Oracle Database and no any other database tool such as Toad and Oracle SQL Developer, etc. The following are the SQL queries:

1. SQL Query to View Session With Locked Objects (Tables)

The following SQL query will list out all the sessions of Oracle database having lock objects by other users.

SELECT c.owner,

       c.object_name,

       c.object_type,

       b.sid,

       b.serial#,

       b.status,

       b.osuser,

       b.machine

  FROM v$locked_object a, v$session b, dba_objects c

 WHERE b.sid = a.session_id AND a.object_id = c.object_id;

Output

OWNER	OBJECT_NAME	OBJECT_TYPE	SID	SERIAL#	STATUS	OSUSER	MACHINE
HR	EMP	TABLE	136	1670	INACTIVE	OUSER	VIN-SYSTEM

After getting the result, you can now use the SID and SERIAL information to Kill the session, for example:

ALTER SYSTEM KILL SESSION '136,1670';

2. SQL Query to View the Primary Key Columns for a Table

With the following query, you can view the Primary Key columns defined for a Table. Change the EMPLOYEES table name with your table name.

SELECT cols.table_name,

         cols.column_name,

         cols.position,

         cons.status,

         cons.owner

    FROM all_constraints cons, all_cons_columns cols

   WHERE     cols.table_name = 'EMPLOYEES'

         AND cons.constraint_type = 'P'

         AND cons.constraint_name = cols.constraint_name

         AND cons.owner = cols.owner ORDER BY cols.table_name, cols.position;

Output

TABLE_NAME	COLUMN_NAME	POSITION	STATUS	OWNER
EMPLOYEES	EMPLOYEE_ID	1	ENABLED	HR

3. SQL Query to View All Source Code in Oracle Having a Particular String

The following query will list out all the procedures, functions, and packages, etc. of the current user having the string INSERT INTO EMP in it.

SELECT *  FROM all_source

WHERE LOWER (text) LIKE ('%insert into emp%') AND owner = USER;

Output

OWNER	NAME	TYPE	LINE	TEXT
SCOTT	PRC_EMP	PROCEDURE	57	INSERT INTO emp (ename,

4. SQL Query to View Dependencies of an Object in Oracle

The following query will list out all the objects depend on the EMPLOYEES table.

SELECT *

  FROM ALL_DEPENDENCIES

 WHERE REFERENCED_NAME = 'EMPLOYEES';

5. SQL Query to View the PATH Information of a Directory Object

Change the 'EXPDP_1' with your Oracle directory object name to view its PATH.

SELECT directory_path

  FROM dba_directories

 WHERE directory_name = 'EXPDP_1';