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';