ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Data Dictionary Objects
ALL TABLES
USER_TABLES
V$SQL_BIND_CAPTURE
ALL_TAB_COLS
USER_TAB_COLS
V$SQL_BIND_DATA
ALL_VIEWS
USER_VIEWS
V$SQL_BIND_METADATA
CDB_TABLES
V$SQL
V$SQL_PLAN
CDB_TAB_COLS
V$SQLAREA
V$SQL_PLAN_STATISTICS
CDB_VIEWS
V$SQLAREA_PLAN_HASH
V$SQL_PLAN_STATISTICS_ALL
DBA_TABLES
V$SQLTEXT
V$SQL_REDIRECTION
DBA_TAB_COLS
V$SQLTEXT_WITH_NEWLINES
V$SQL_WORKAREA
DBA_VIEWS
Object Privileges
-- privileges to tables and views granted through roles may not be valid within procedural code
GRANT select ON <object_name> TO <user_name>;
conn scott/tiger@pdbdev
GRANT select ON emp TO uwclass;
System Privileges
SELECT ANY DICTIONARY
SELECT ANY TABLE
Basic Select Statements
Select All Columns and All Records in a Single Table or View
SELECT *
FROM <table_name>;
SELECT *
FROM all_tables;
Select Named Columns
SELECT <column_name, column_name, ..., <column_name>
FROM <table_name>;
SELECT table_name, tablespace_name, num_rows
FROM all_tables;
Create Table As (CTAS)
Note: Redo only created when in ARCHIVE LOG mode
CREATE TABLE <table_name> AS
SELECT <column_name, column_name, ..., <column_name>
FROM <table_name>;
CREATE TABLE t AS
SELECT *
FROM all_tables;
SELECT * FROM t;
SELECTs can go anywhere
SELECT DECODE((SELECT 'x' FROM DUAL), (SELECT 'x' FROM DUAL),
(SELECT 'Morgan' FROM dual)) AS RESULT
FROM (SELECT 'm' FROM dual)
WHERE (SELECT 1 FROM dual) = (SELECT 1 FROM dual)
AND (SELECT 2 FROM dual) BETWEEN (SELECT 1 FROM dual) AND (SELECT 3 FROM dual)
AND NVL((SELECT NULL FROM dual ), (SELECT 'z' FROM dual)) = (SELECT 'z' FROM dual)
ORDER BY (SELECT 1 FROM dual);
Select Unique Values
All
"All" is the default: Specifying it is optional
SELECT [ALL | DISTINCT | UNIQUE] <column_name_list>
FROM <table_name>;
SELECT ALL object_type
FROM all_objects;
Distinct
SELECT DISTINCT <column_name_list>
FROM <table_name>;
SELECT DISTINCT object_type
FROM all_objects;
Unique
SELECT UNIQUE <column_name_list>
FROM <table_name>;
SELECT UNIQUE object_type
FROM all_objects;
Allows limiting the rows returned by a query by specifying an offset and/or the number or percentage of rows to return
SELECT <column_name_list>
FROM <schema_name.object_name>
ORDER BY <query_column_list>
[OFFSET <INTEGER> ROWS]
[FETCH <FIRST | NEXT> <INTEGER> [PERCENT] ROWS ONLY]
The following statement returns the 5 employees with the lowest employee_id values:
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
FETCH FIRST 5 ROWS ONLY;
EMPLOYEE_ID LAST_NAME
----------- -------------------------
100 King
101 Kochhar
102 De Haan
103 Hunold
104 Ernst
The following statement returns the next 5 employees with the lowest employee_id values:
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;
Because WITH TIES is specified, the following statement returns the 5 percent of employees with the lowest salaries, plus all additional employees with the same salary as the last row fetched in the previous example:
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 PERCENT ROWS WITH TIES;
row_pattern_measure_column ::=
expression AS c_alias
row_pattern_rows_per_match ::=
ONE ROW PER MATCH
| ALL ROWS PER MATCH
row_pattern_skip_to ::=
AFTER MATCH {
SKIP TO NEXT ROW
| SKIP PAST LAST ROW
| SKIP TO FIRST variable_name
| SKIP TO LAST variable_name
| SKIP TO variable_name}
Sample Clause Returning 1% Of Records and the BLOCK option.
Block sampling is possible only during full table scans or index fast full scans. If a more efficient execution path exists, then Oracle Database does not perform block sampling.
If you want to guarantee block sampling for a particular table or index, then use the FULL or INDEX_FFS hint.
SELECT *
FROM <table_name>
SAMPLE [BLOCK] (percentage_of_rows)
WHERE <where_clause>;
CREATE TABLE t AS
SELECT object_name
FROM all_objects;
CREATE TABLE children
(cid NUMBER(10),
fid NUMBER(10),
fin_level VARCHAR2(35));
DECLARE
CURSOR x_cur IS
SELECT pid
FROM parents;
x_rec x_cur%ROWTYPE;
x NUMBER(10,2) := 18000.64;
y NUMBER(10,2) := 100;
BEGIN
DELETE FROM parents;
DELETE FROM children;
FOR i IN 1..25
LOOP
INSERT INTO parents
VALUES (y, x);
x := x+1235.31;
y := y-1;
END LOOP;
y := 0;
OPEN x_cur;
LOOP
FETCH x_cur INTO x_rec;
EXIT WHEN x_cur%NOTFOUND;
y := y+1;
INSERT INTO children (cid, fid)
VALUES (y, x_rec.pid);
y := y+1;
INSERT INTO children (cid, fid)
VALUES (y, x_rec.pid);
END LOOP;
CLOSE x_cur;
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE cursor_loop3 IS
CURSOR x_cur IS
SELECT pid, cash
FROM parents
WHERE cash < 35000
FOR UPDATE;
BEGIN
FOR x_rec IN x_cur
LOOP
UPDATE parents
SET cash = FLOOR(cash)
WHERE CURRENT OF x_cur;
END LOOP;
COMMIT;
END cursor_loop3;
/
FOR UPDATE with NOWAIT
See Deadlocks Demo page
FOR UPDATE with WAIT
See Deadlocks Demo page
FOR UPDATE with SKIP LOCKED
See Deadlocks Demo page
Partition Table Select
Select From Named Partition
SELECT DISTINCT <column_name_list>
FROM <table_name> PARTITION (<partition_name>);
CREATE TABLE pt (
deptno NUMBER(10),
state VARCHAR2(2))
PARTITION BY LIST (state) (
PARTITION nw VALUES ('OR', 'WA'),
PARTITION sw VALUES ('AZ', 'CA', 'NM'));
INSERT INTO pt VALUES (1, 'WA');
INSERT INTO pt VALUES (1, 'OR');
INSERT INTO pt VALUES (1, 'CA');
SELECT COUNT(*) FROM pt;
SELECT COUNT(*) FROM pt PARTITION(nw);
SELECT COUNT(*) FROM pt PARTITION(sw);
Case Insensitive Select
Select that ignores upper/lower case characters
conn sys@pdbdev as sysdba
GRANT select ON v_$nls_parameters TO uwclass;
SELECT * FROM sys.v_$nls_parameters WHERE parameter LIKE '%SORT%';
conn uwclass/uwclass
CREATE TABLE cis_test (
col1 VARCHAR2(10));
INSERT INTO cis_test VALUES ('one');
INSERT INTO cis_test VALUES ('TWO');
INSERT INTO cis_test VALUES ('thRee');
INSERT INTO cis_test VALUES ('FouR');
INSERT INTO cis_test VALUES ('fiVE');
SELECT * FROM cis_test;
SELECT col1 FROM cis_test ORDER BY 1;
ALTER SESSION SET nls_sort=binary_ci;
SELECT * FROM sys.v_$nls_parameters WHERE parameter LIKE '%SORT%';
