5 SQL Queries Every Oracle Developer Needs

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

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

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

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:

• How to Install Oracle SQL Developer 18.2 on Windows 10?