40+ Essential Oracle SQL Queries Every DBA Should Know

Date/Time Related Queries

These queries help retrieve or calculate various date values. Replace SYSDATE with any date to customize the results.

SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month" FROM DUAL;

SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month" FROM DUAL;

SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL;

SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days FROM DUAL;

SELECT SYSDATE, LAST_DAY (SYSDATE) "Last", LAST_DAY (SYSDATE) - SYSDATE "Days left" FROM DUAL;

SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0) num_of_days FROM DUAL;

SELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees;

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date, TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date FROM XMLTABLE ('for $i in 0 to xs:int(D) return $i' PASSING XMLELEMENT (d, FLOOR (MONTHS_BETWEEN (ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12), SYSDATE))) COLUMNS i INTEGER PATH '.');

SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning FROM DUAL;

SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left FROM DUAL;

Data Dictionary Queries

Useful for inspecting schema objects and metadata.

SELECT table_name FROM user_tables WHERE table_name = 'TABLE_NAME';

SELECT column_name AS FOUND FROM user_tab_cols WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';

SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;

SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;

ALTER SESSION SET CURRENT_SCHEMA = new_schema;

Database Management Queries

Commands to retrieve version, configuration, size, and tablespace information.

SELECT * FROM v$version;

SELECT username, profile, default_tablespace, temporary_tablespace FROM dba_users;

SELECT * FROM nls_database_parameters;

SELECT VALUE FROM v$system_parameter WHERE name = 'compatible';

CREATE TABLE tab (col1 VARCHAR2 (10)); CREATE INDEX idx1 ON tab (UPPER (col1)); ANALYZE TABLE a COMPUTE STATISTICS;

ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;

SELECT SUBSTR (file_name, 1, 50), AUTOEXTENSIBLE FROM dba_data_files;

SELECT tablespace_name, AUTOEXTENSIBLE FROM dba_data_files;

ALTER TABLESPACE data01 ADD DATAFILE '/work/oradata/STARTST/data01.dbf' SIZE 1000M AUTOEXTEND OFF;

ALTER DATABASE DATAFILE '/u01/app/Test_data_01.dbf' RESIZE 2G;

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;

SELECT SUM (bytes / 1024 / 1024) "size" FROM dba_segments WHERE owner = '&owner';

SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME, s.program || '-' || s.terminal || '(' || s.machine || ')' PROG, s.sid || '/' || s.serial# sid, s.status "Status", p.spid, sql_text sqltext FROM v$sqltext_with_newlines t, V$SESSION s, v$process p WHERE t.address = s.sql_address AND p.addr = s.paddr(+) AND t.hash_value = s.sql_hash_value ORDER BY s.sid, t.piece;

Performance Related Queries

Insights into CPU usage, long-running operations, and session details.

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds FROM v$session ss, v$sesstat se, v$statname sn WHERE se.STATISTIC# = sn.STATISTIC# AND NAME LIKE '%CPU used by this session%' AND se.SID = ss.SID AND ss.status = 'ACTIVE' AND ss.username IS NOT NULL ORDER BY VALUE DESC;

SELECT a.sid, a.serial#, b.username, opname OPERATION, target OBJECT, TRUNC (elapsed_seconds, 5) "ET (s)", TO_CHAR (start_time, 'HH24:MI:SS') start_time, ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)" FROM v$session_longops a, v$session b WHERE a.sid = b.sid AND b.username NOT IN ('SYS', 'SYSTEM') AND totalwork > 0 ORDER BY elapsed_seconds;

SELECT b.sid, b.serial#, a.spid processid, b.process clientpid FROM v$process a, v$session b WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');

V$SESSION.SID and V$SESSION.SERIAL# are database process IDs.

V$PROCESS.SPID is the server‑side process ID.

V$SESSION.PROCESS is the client‑side process ID (or thread ID on Windows).

SELECT CREATED, TIMESTAMP, last_ddl_time FROM all_objects WHERE OWNER = 'MYSCHEMA' AND OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'EMPLOYEE_TABLE';

SELECT * FROM (SELECT ROWNUM, SUBSTR (a.sql_text, 1, 200) sql_text, TRUNC (a.disk_reads / DECODE (a.executions, 0, 1, a.executions)) reads_per_execution, a.buffer_gets, a.disk_reads, a.executions, a.sorts, a.address FROM v$sqlarea a ORDER BY 3 DESC) WHERE ROWNUM < 10;

SELECT osuser, username, machine, program FROM v$session ORDER BY osuser;

SELECT program application, COUNT (program) Numero_Sesiones FROM v$session GROUP BY program ORDER BY Numero_Sesiones DESC;

SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones FROM v$session GROUP BY username ORDER BY Numero_Sesiones DESC;

SELECT owner, COUNT (owner) number_of_objects FROM dba_objects GROUP BY owner ORDER BY number_of_objects DESC;

Utility / Math Queries

Miscellaneous functions for conversion, random data, and simple checks.

SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL; -- returns "one thousand five hundred twenty‑six"

SELECT * FROM dba_source WHERE UPPER (text) LIKE '%FOO_SOMETHING%' AND owner = 'USER_NAME';

WITH csv AS (SELECT 'AA,BB,CC,DD,EE,FF' AS csvdata FROM DUAL) SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char FROM DUAL, csv CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+', 1, LEVEL) IS NOT NULL;

SELECT * FROM employees WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);

SELECT * FROM employees MINUS SELECT * FROM employees WHERE ROWNUM < (SELECT COUNT (*) FROM employees);

WITH tbl AS (SELECT -2 num FROM DUAL UNION SELECT -3 num FROM DUAL UNION SELECT -4 num FROM DUAL), sign_val AS (SELECT CASE MOD (COUNT (*), 2) WHEN 0 THEN 1 ELSE -1 END val FROM tbl WHERE num < 0) SELECT EXP (SUM (LN (ABS (num)))) * val FROM tbl, sign_val GROUP BY val;

SELECT LEVEL empl_id, MOD (ROWNUM, 50000) dept_id, TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary, DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F') gender, TO_DATE (ROUND (DBMS_RANDOM.VALUE (1, 28)) || '-' || ROUND (DBMS_RANDOM.VALUE (1, 12)) || '-' || ROUND (DBMS_RANDOM.VALUE (1900, 2010)), 'DD-MM-YYYY') dob, DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address FROM DUAL CONNECT BY LEVEL < 10000;

SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL; -- generates a number between 1 and 100

SELECT 1 FROM TABLE_NAME WHERE ROWNUM = 1; -- fast existence check