LLI Wiki — My Digital Knowledge Base

Useful Oracle SQL queries: dates, data dictionary, administration and performance

Cheat sheet of everyday Oracle SQL queries for developers and DBAs.

A compact cheat sheet of everyday Oracle SQL queries for developers and DBAs: dates, data dictionary, administration, performance and utilities. Replace placeholder names (TABLE_NAME, USER_NAME, MYSCHEMA...) with your own.

Date and time

-- First day of the current month (replace SYSDATE with any date)
SELECT TRUNC (SYSDATE, 'MONTH') FROM DUAL;

-- Last day of the current month (leap years handled)
SELECT TRUNC (LAST_DAY (SYSDATE)) FROM DUAL;

-- First and last day of the current year
SELECT TRUNC (SYSDATE, 'YEAR') FROM DUAL;
SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 FROM DUAL;

-- Number of days in the current month
SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days FROM DUAL;

-- Days left in the current month
SELECT LAST_DAY (SYSDATE) - SYSDATE "Days left" FROM DUAL;

-- Days since a given date (here: employee hire date)
SELECT TRUNC (SYSDATE) - TRUNC (e.hire_date) FROM employees e;

-- Seconds elapsed since midnight / seconds left today
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;

Start and end date of each remaining month of the current year:

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

Data dictionary

-- Does a table exist in the current schema? (makes create scripts re-runnable)
SELECT table_name FROM user_tables WHERE table_name = 'TABLE_NAME';

-- Does a column exist?
SELECT column_name AS FOUND
  FROM user_tab_cols
 WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';

-- DDL of an object (use 'VIEW', 'INDEX'... as first argument for other types)
SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;

-- Current schema, and changing it
SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;
ALTER SESSION SET CURRENT_SCHEMA = new_schema;

Database administration

-- Version and compatibility
SELECT * FROM v$version;
SELECT VALUE FROM v$system_parameter WHERE name = 'compatible';

-- Default profile and tablespaces per user
SELECT username, profile, default_tablespace, temporary_tablespace FROM dba_users;

-- Database character set
SELECT * FROM nls_database_parameters;

-- Case-insensitive index on case-sensitive data
CREATE TABLE tab (col1 VARCHAR2 (10));
CREATE INDEX idx1 ON tab (UPPER (col1));
ANALYZE TABLE tab COMPUTE STATISTICS;

Datafiles and tablespaces:

-- Resize a datafile
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 2G;

-- Add a datafile to a tablespace
ALTER TABLESPACE data01 ADD DATAFILE '/u01/oradata/ORCL/data01.dbf' SIZE 1000M AUTOEXTEND OFF;

-- Autoextend status
SELECT tablespace_name, AUTOEXTENSIBLE FROM dba_data_files;

Sizes:

-- Allocated size of the database (GB)
SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

-- Space used by data (GB)
SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;

-- Size of a schema (MB)
SELECT SUM (bytes / 1024 / 1024) "size" FROM dba_segments WHERE owner = '&owner';

-- Number of objects per owner
SELECT owner, COUNT (owner) number_of_objects
  FROM dba_objects
 GROUP BY owner
 ORDER BY number_of_objects DESC;

Sessions and performance

Last SQL text fired by each session:

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;

CPU used by active user sessions:

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;

Progress of long-running operations:

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;

Session, server process and client process ids of the current session:

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 SERIAL# identify the database session, V$PROCESS.SPID is the server process id, and V$SESSION.PROCESS is the client process id (on Windows, process:thread).

Last DDL time of a table:

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

Top SQL by disk reads per execution:

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;

Connections:

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

-- Sessions per program
SELECT program application, COUNT (program) number_of_sessions
  FROM v$session GROUP BY program ORDER BY number_of_sessions DESC;

-- Sessions per user
SELECT username, COUNT (username) number_of_sessions
  FROM v$session GROUP BY username ORDER BY number_of_sessions DESC;

Utilities

-- Number to words
SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL;   -- one thousand five hundred twenty-six

-- Search a string in PL/SQL source code
SELECT * FROM dba_source
 WHERE UPPER (text) LIKE '%FOO_SOMETHING%' AND owner = 'USER_NAME';

-- Comma-separated string to rows
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;

-- Last row of a table without a reliable key
SELECT * FROM employees WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);

-- Does a table contain any row? (cheaper than COUNT(*))
SELECT 1 FROM TABLE_NAME WHERE ROWNUM = 1;

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

Multiply the values of all rows (sign handled separately, magnitude through EXP(SUM(LN()))):

WITH tbl AS (SELECT -2 num FROM DUAL UNION SELECT -3 FROM DUAL UNION SELECT -4 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;

Generate random test data:

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;

Notes

Mastodon LLIMastodon SocialFosstodon