Essential Oracle READ-ONLY Commands
CONNECTION & SESSION
SHOW USER; -- Oracle keywords
SELECT * FROM V$VERSION; -- V$VERSION = Oracle system view
SELECT * FROM V$SESSION WHERE username = USER; -- V$SESSION = Oracle system view, username = Oracle column, USER = Oracle keyword
DISCOVERY
SELECT * FROM USER_TABLES; -- USER_TABLES = Oracle system view
SELECT * FROM USER_VIEWS; -- USER_VIEWS = Oracle system view
SELECT * FROM ALL_TABLES; -- ALL_TABLES = Oracle system view
SELECT * FROM ALL_VIEWS; -- ALL_VIEWS = Oracle system view
SELECT * FROM ALL_USERS; -- ALL_USERS = Oracle system view
SELECT username FROM ALL_USERS; -- username = Oracle column name
SELECT * FROM DBA_USERS; -- DBA_USERS = Oracle system view
DESC [table_name]; -- DESC = Oracle keyword, [table_name] = PLACEHOLDER
SELECT * FROM USER_TAB_COLUMNS WHERE table_name = '[TABLE_NAME]'; -- USER_TAB_COLUMNS = Oracle view, table_name = Oracle column, [TABLE_NAME] = PLACEHOLDER
SELECT * FROM USER_INDEXES WHERE table_name = '[TABLE_NAME]'; -- USER_INDEXES = Oracle view, table_name = Oracle column, [TABLE_NAME] = PLACEHOLDER
SELECT * FROM USER_OBJECTS; -- USER_OBJECTS = Oracle system view
SELECT object_name, object_type FROM USER_OBJECTS; -- object_name, object_type = Oracle column names
BASIC QUERIES
SELECT * FROM [table_name]; -- SELECT/FROM = Oracle keywords, [table_name] = PLACEHOLDER
SELECT [col1], [col2] FROM [table_name]; -- [col1], [col2], [table_name] = PLACEHOLDERS
SELECT DISTINCT [col1] FROM [table_name]; -- DISTINCT = Oracle keyword, [col1], [table_name] = PLACEHOLDERS
SELECT COUNT(*) FROM [table_name]; -- COUNT = Oracle function, [table_name] = PLACEHOLDER
SELECT COUNT(DISTINCT [col1]) FROM [table_name]; -- COUNT/DISTINCT = Oracle keywords, [col1], [table_name] = PLACEHOLDERS
FILTERING & SORTING
WHERE [col1] = '[value]' -- WHERE = Oracle keyword, [col1], [value] = PLACEHOLDERS
WHERE [col1] LIKE '%[text]%' -- LIKE = Oracle keyword, % = Oracle wildcards, [col1], [text] = PLACEHOLDERS
WHERE UPPER([col1]) LIKE UPPER('%[text]%') -- UPPER = Oracle function, [col1], [text] = PLACEHOLDERS
WHERE REGEXP_LIKE([col1], '[text]', 'i') -- REGEXP_LIKE = Oracle function, 'i' = Oracle flag, [col1], [text] = PLACEHOLDERS
WHERE [col1] IS NULL -- IS NULL = Oracle keywords, [col1] = PLACEHOLDER
WHERE [col1] IS NOT NULL -- IS NOT NULL = Oracle keywords, [col1] = PLACEHOLDER
WHERE [col1] IN ('[A]', '[B]', '[C]') -- IN = Oracle keyword, [A], [B], [C] = PLACEHOLDERS
WHERE [col1] BETWEEN [10] AND [50] -- BETWEEN/AND = Oracle keywords, [col1], [10], [50] = PLACEHOLDERS
WHERE [date_col] >= DATE '[2024-01-01]' -- DATE = Oracle keyword, [date_col], [2024-01-01] = PLACEHOLDERS
ORDER BY [col1] DESC -- ORDER BY/DESC = Oracle keywords, [col1] = PLACEHOLDER
WHERE ROWNUM <= [10] -- ROWNUM = Oracle keyword, [10] = PLACEHOLDER
JOINS
SELECT * FROM [table1] t1 JOIN [table2] t2 ON t1.[id] = t2.[id]; -- JOIN/ON = Oracle keywords, [table1], [table2], [id] = PLACEHOLDERS, t1/t2 = aliases
SELECT * FROM [table1] t1 LEFT JOIN [table2] t2 ON t1.[id] = t2.[id]; -- LEFT JOIN = Oracle keywords, [table1], [table2], [id] = PLACEHOLDERS
AGGREGATION
SELECT [col1], COUNT(*) FROM [table_name] GROUP BY [col1]; -- GROUP BY = Oracle keywords, COUNT = Oracle function, [col1], [table_name] = PLACEHOLDERS
SELECT [col1], SUM([col2]), AVG([col2]), MAX([col2]), MIN([col2]) FROM [table_name] GROUP BY [col1]; -- SUM/AVG/MAX/MIN = Oracle functions, [col1], [col2], [table_name] = PLACEHOLDERS
HAVING COUNT(*) > [5] -- HAVING = Oracle keyword, COUNT = Oracle function, [5] = PLACEHOLDER
DATE/TIME
SELECT SYSDATE FROM DUAL; -- SYSDATE = Oracle function, DUAL = Oracle dummy table
SELECT TO_CHAR([date_col], 'YYYY-MM-DD') FROM [table_name]; -- TO_CHAR = Oracle function, 'YYYY-MM-DD' = Oracle format pattern, [date_col], [table_name] = PLACEHOLDERS
SELECT TO_DATE('[2024-01-01]', 'YYYY-MM-DD') FROM DUAL; -- TO_DATE = Oracle function, 'YYYY-MM-DD' = Oracle format pattern, [2024-01-01] = PLACEHOLDER
SELECT USER FROM DUAL; -- USER = Oracle keyword, DUAL = Oracle system table
STRING FUNCTIONS
SELECT SUBSTR([col1], [1], [10]) FROM [table_name]; -- SUBSTR = Oracle function, [col1], [1], [10], [table_name] = PLACEHOLDERS
SELECT TRIM([col1]) FROM [table_name]; -- TRIM = Oracle function, [col1], [table_name] = PLACEHOLDERS
SELECT LENGTH([col1]) FROM [table_name]; -- LENGTH = Oracle function, [col1], [table_name] = PLACEHOLDERS
SELECT REPLACE([col1], '[old]', '[new]') FROM [table_name]; -- REPLACE = Oracle function, [col1], [old], [new], [table_name] = PLACEHOLDERS
SYSTEM VIEWS
SELECT * FROM V$TABLESPACE; -- V$TABLESPACE = Oracle system view
SELECT * FROM USER_CONSTRAINTS; -- USER_CONSTRAINTS = Oracle system view
SELECT * FROM USER_CONS_COLUMNS; -- USER_CONS_COLUMNS = Oracle system view
SELECT * FROM V$SQL WHERE sql_text LIKE '%[your_table]%'; -- V$SQL = Oracle system view, sql_text = Oracle column, LIKE = Oracle keyword, [your_table] = PLACEHOLDER
Legend
- Oracle keywords/functions/views: Built-in Oracle commands - use as-is
- [PLACEHOLDERS]: Replace with your actual table names, column names, values, etc.
Last updated: 2025-08-26 20:00 UTC