ORACLE DATABASE COMPREHENSIVE CHEATSHEET

Comprehensive Oracle Database cheatsheet covering connections, security, DDL/DML, queries, functions, PL/SQL, procedures, triggers, indexes, performance, backup, and data dictionary views. Includes syntax examples and quick references for developers and DBAs.

Table of Contents

1. Connecting & Session Management
2. User & Security Management
3. Data Definition Language (DDL)
4. Data Manipulation Language (DML)
5. Transactions & Locking
6. Querying – Basic SELECT
7. Querying – Joins & Set Operators
8. Functions (String, Numeric, Date, Conversion, Aggregation)
9. PL/SQL Basics
10. Procedures, Functions, Packages
11. Triggers
12. Indexes & Constraints
13. Views, Sequences, Synonyms
14. Performance & Execution Plans
15. Backup & Recovery (Basic)
16. Useful Data Dictionary Views

---

1. Connecting & Session Management

Command	Description
sqlplus username/password@//host:port/SID	Connect via SQL*Plus
sqlplus / as sysdba	Connect as SYSDBA (OS authentication)
CONN username/password	Switch connection
DISCONN	Disconnect
SHOW USER	Show current schema
SET LINESIZE 200	Output width
SET PAGESIZE 100	Page height
SPOOL file.log	Start logging output
SPOOL OFF	Stop logging
DESC table_name	Describe table structure

---

2. User & Security Management

Command	Description
CREATE USER user IDENTIFIED BY pass;	Create user
ALTER USER user IDENTIFIED BY newpass;	Change password
DROP USER user CASCADE;	Drop user (with objects)
GRANT privilege ON object TO user;	Grant privilege
REVOKE privilege ON object FROM user;	Revoke privilege
GRANT CONNECT, RESOURCE TO user;	Standard roles
GRANT DBA TO user;	Full admin
GRANT SELECT ON emp TO public;	Grant to all
GRANT CREATE SESSION TO user;	Allow login
ALTER USER user ACCOUNT LOCK/UNLOCK;	Lock/unlock

Common privileges: CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, SELECT, INSERT, UPDATE, DELETE, EXECUTE

---

3. Data Definition Language (DDL)

Command	Example / Description
CREATE	CREATE TABLE t (id NUMBER(10), name VARCHAR2(50));
ALTER	ALTER TABLE t ADD (date_col DATE);
	ALTER TABLE t MODIFY (name VARCHAR2(100));
	ALTER TABLE t DROP COLUMN date_col;
	ALTER TABLE t RENAME COLUMN name TO fullname;
DROP	DROP TABLE t CASCADE CONSTRAINTS;
TRUNCATE	TRUNCATE TABLE t; (DDL – cannot rollback)
RENAME	RENAME old TO new;
COMMENT	COMMENT ON TABLE t IS 'Employee table';

Data Types: NUMBER(p,s), VARCHAR2(n), CHAR(n), DATE, TIMESTAMP, CLOB, BLOB, RAW(n)

---

4. Data Manipulation Language (DML)

Command	Example
INSERT	INSERT INTO t (id, name) VALUES (1, 'John');
	INSERT INTO t SELECT id, name FROM other_t;
UPDATE	UPDATE t SET name = 'Jane' WHERE id = 1;
DELETE	DELETE FROM t WHERE id = 1;
MERGE	MERGE INTO t USING src ON (t.id=src.id) WHEN MATCHED THEN UPDATE SET t.name=src.name WHEN NOT MATCHED THEN INSERT VALUES(src.id, src.name);

---

5. Transactions & Locking

Command	Description
COMMIT;	Make changes permanent
ROLLBACK;	Undo uncommitted changes
SAVEPOINT sp;	Create savepoint
ROLLBACK TO sp;	Rollback to savepoint
SET TRANSACTION READ ONLY;	Read-only transaction
LOCK TABLE t IN EXCLUSIVE MODE;	Manual lock
SELECT ... FOR UPDATE;	Row-level lock

---

6. Querying – Basic SELECT

SELECT DISTINCT col1, col2
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE condition
GROUP BY col1
HAVING aggregate_condition
ORDER BY col1 DESC, col2 ASC
FETCH FIRST 10 ROWS ONLY;   -- Oracle 12c+

Clause	Notes
WHERE	Filters rows before grouping
HAVING	Filters groups after GROUP BY
FETCH FIRST n ROWS ONLY	LIMIT equivalent
ROWNUM <= n	Older Oracle row limit

---

7. Querying – Joins & Set Operators

Joins:

-- Inner
SELECT * FROM a JOIN b ON a.id = b.id;

-- Left/Right/Full Outer
SELECT * FROM a LEFT JOIN b ON a.id = b.id;

-- Cross join
SELECT * FROM a CROSS JOIN b;

-- Natural join (same column names)
SELECT * FROM a NATURAL JOIN b;

-- Self join
SELECT e1.name, e2.name FROM emp e1 JOIN emp e2 ON e1.mgr_id = e2.emp_id;

Set Operators:

Operator	Description
UNION	All distinct rows
UNION ALL	All rows including duplicates
INTERSECT	Rows in both
MINUS	Rows in first not in second

---

8. Functions

String: UPPER, LOWER, INITCAP, LENGTH, SUBSTR(str, start, len), INSTR(str, sub), TRIM, LTRIM, RTRIM, REPLACE, CONCAT, LPAD/RPAD

Numeric: ABS, ROUND(n, dec), TRUNC(n, dec), CEIL, FLOOR, MOD, POWER, SQRT, SIGN

Date/Time: SYSDATE, CURRENT_DATE, ADD_MONTHS(date, n), MONTHS_BETWEEN(d1,d2), LAST_DAY(date), NEXT_DAY(date, 'MON'), EXTRACT(YEAR FROM date), TRUNC(date, 'MM')

Conversion: TO_CHAR(date, 'YYYY-MM-DD'), TO_DATE('2025-01-01', 'YYYY-MM-DD'), TO_NUMBER('123'), CAST(col AS VARCHAR2(10))

Aggregate: COUNT(*), SUM(col), AVG(col), MIN(col), MAX(col), MEDIAN(col), STDDEV(col), VARIANCE(col)

Conditional:

CASE WHEN condition THEN result ELSE default END
NVL(expr, default)           -- Replace NULL
NVL2(expr, not_null, null)   -- If not null/null
NULLIF(a,b)                  -- NULL if a=b
COALESCE(a,b,c)              -- First non-null
DECODE(col, val1, res1, val2, res2, default)

Analytic (Window) Functions:

ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)
RANK() OVER (ORDER BY salary)
DENSE_RANK()
LAG(col, 1) OVER (ORDER BY date)
LEAD(col, 1) OVER (ORDER BY date)
SUM(salary) OVER (PARTITION BY dept)

---

9. PL/SQL Basics

Block structure:

DECLARE
   v_var VARCHAR2(50) := 'Hello';
   CURSOR c_emp IS SELECT name FROM emp;
BEGIN
   SELECT salary INTO v_sal FROM emp WHERE id = 10;
   DBMS_OUTPUT.PUT_LINE('Salary: ' || v_sal);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Not found');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Variables & Types:

v_num NUMBER(10) := 100;
v_text emp.name%TYPE;
v_row emp%ROWTYPE;
v_cursor SYS_REFCURSOR;

Conditional:

IF cond THEN ... ELSIF cond2 THEN ... ELSE ... END IF;

Loops:

FOR i IN 1..10 LOOP ... END LOOP;
WHILE cond LOOP ... END LOOP;
LOOP EXIT WHEN cond; ... END LOOP;

---

10. Procedures, Functions, Packages

Procedure:

CREATE OR REPLACE PROCEDURE proc_name(p_id IN NUMBER, p_out OUT VARCHAR2) AS
BEGIN
   SELECT name INTO p_out FROM emp WHERE id = p_id;
END;
/
-- Execute
EXEC proc_name(10, :res);

Function:

CREATE OR REPLACE FUNCTION func_name(p_id NUMBER) RETURN VARCHAR2 AS
   v_name VARCHAR2(50);
BEGIN
   SELECT name INTO v_name FROM emp WHERE id = p_id;
   RETURN v_name;
END;
/
-- Use
SELECT func_name(10) FROM dual;

Package:

CREATE OR REPLACE PACKAGE pkg AS
   PROCEDURE proc1;
   FUNCTION func1 RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
   PROCEDURE proc1 IS BEGIN NULL; END;
   FUNCTION func1 RETURN NUMBER IS BEGIN RETURN 1; END;
END;
/

---

11. Triggers

CREATE OR REPLACE TRIGGER trig_name
BEFORE INSERT OR UPDATE OF salary ON emp
FOR EACH ROW
DECLARE
   v_msg VARCHAR2(100);
BEGIN
   IF :NEW.salary < 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Negative salary');
   END IF;
   :NEW.last_modified := SYSDATE;
END;
/

Trigger types: BEFORE/AFTER, INSERT/UPDATE/DELETE, FOR EACH ROW (row-level), INSTEAD OF (views)

Special variables: :OLD.column (old value), :NEW.column (new value)

---

12. Indexes & Constraints

Constraints:

CREATE TABLE t (
   id NUMBER PRIMARY KEY,
   name VARCHAR2(50) NOT NULL,
   email UNIQUE,
   dept_id REFERENCES dept(id),
   salary CHECK (salary > 0)
);
-- Add later
ALTER TABLE t ADD CONSTRAINT pk_id PRIMARY KEY (id);
ALTER TABLE t ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES dept(id) ON DELETE CASCADE;
ALTER TABLE t DROP CONSTRAINT pk_id;

Indexes:

CREATE INDEX idx_name ON t(column);
CREATE UNIQUE INDEX idx_unique ON t(email);
CREATE BITMAP INDEX idx_bit ON t(status);
CREATE INDEX idx_func ON t(UPPER(name));
DROP INDEX idx_name;

---

13. Views, Sequences, Synonyms

View:

CREATE VIEW v_high_sal AS SELECT * FROM emp WHERE salary > 5000 WITH CHECK OPTION;
CREATE OR REPLACE VIEW v_join AS SELECT e.name, d.name FROM emp e JOIN dept d ON e.dept_id=d.id;
DROP VIEW v_name;

Sequence:

CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1 CACHE 10;
seq_name.NEXTVAL   -- next value
seq_name.CURRVAL   -- current value
DROP SEQUENCE seq_name;

Synonym:

CREATE SYNONYM emp FOR hr.employees;
CREATE PUBLIC SYNONYM emp FOR hr.employees;
DROP SYNONYM emp;

---

14. Performance & Execution Plans

-- Explain plan
EXPLAIN PLAN FOR SELECT * FROM emp WHERE id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Generate plan with cost
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_FORMAT('all'));

-- Monitor real-time (Oracle 12c+)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', 0, 'ALLSTATS LAST'));

-- Gather statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE');

-- Hints
SELECT /*+ INDEX(emp idx_emp_id) */ * FROM emp WHERE id = 10;
SELECT /*+ PARALLEL(4) */ COUNT(*) FROM large_table;

-- Show active sessions
SELECT sid, serial#, username, status FROM v$session;

---

15. Backup & Recovery (Basic)

-- Export (Data Pump)
expdp username/password DIRECTORY=dp_dir DUMPFILE=backup.dmp SCHEMAS=schema_name

-- Import
impdp username/password DIRECTORY=dp_dir DUMPFILE=backup.dmp

-- Tablespace backup (RMAN)
RMAN> BACKUP DATABASE;
RMAN> BACKUP TABLESPACE users;

-- Recovery
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;

-- Flashback query (see past data)
SELECT * FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

---

16. Useful Data Dictionary Views

View	Description
USER_TABLES	Tables owned by current user
ALL_TABLES	Tables accessible to current user
DBA_TABLES	All tables (DBA only)
USER_INDEXES	Indexes
USER_CONSTRAINTS	Constraints
USER_CONS_COLUMNS	Constraint columns
USER_VIEWS	Views
USER_SEQUENCES	Sequences
USER_SYNONYMS	Synonyms
USER_SOURCE	PL/SQL source code
USER_TAB_COLUMNS	Column metadata
V$SESSION	Active sessions
V$SQL	SQL statements in shared pool
V$PARAMETER	Instance parameters
DUAL	Dummy table for SELECT without table

---

Quick System Queries:

-- Version
SELECT * FROM v$version;

-- Current date
SELECT SYSDATE FROM dual;

-- All tables in schema
SELECT table_name FROM user_tables;

-- Table size
SELECT segment_name, bytes/1024/1024 MB FROM user_segments WHERE segment_type='TABLE';

-- Running queries
SELECT sql_id, sql_text FROM v$sql WHERE command_type = 2; -- DML

---