alexsusanu@docs:Essential Oracle READ-ONLY Commands $
alexsusanu@docs
:~$ cat Essential Oracle READ-ONLY Commands.md

HomeORACLE → Essential Oracle READ-ONLY Commands

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