Home » SQL » 5 SQL Queries Every Oracle Developer Needs

5 SQL Queries Every Oracle Developer Needs

By

In this post, I am sharing 5 SQL queries which are most needed by the Oracle developers, 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

134

1566

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 '134,1566';

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 CSV_DIR with your Oracle directory object name to view its PATH.

SELECT directory_path
  FROM dba_directories
 WHERE directory_name = 'CSV_DIR';

See also: