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
PLAN_TABLE$
V$SQLAREA
V$SQLTEXT_WITH_NEWLINES
V$SQL
V$SQLTEXT
V$SQL_PLAN
Object Privileges
Privileges to tables and views granted through roles may not be valid within a PL/SQL object. See the section on AUTHID.
GRANT select ON [owner.]<object_name> TO <user_name>;
conn scott/tiger@pdbdev
GRANT select ON emp TO uwclass;
System Privileges
SELECT ANY TABLE
By Column Name
Single Column Ascending
SELECT <column_name>
FROM <table_name>
ORDER BY <column_name>;
SELECT table_name
FROM all_tables;
SELECT table_name
FROM all_tables
ORDER BY table_name;
Single Column Descending
SELECT <column_name>
FROM <table_name>
ORDER BY <column_name> DESC;
SELECT table_name
FROM all_tables
ORDER BY table_name DESC;
Order By Multiple Columns
SELECT <column_name>, <column_name
FROM <table_name>
ORDER BY <column_name>, <column_name>';
SELECT table_name, tablespace_name
FROM all_tables
ORDER BY tablespace_name, table_name;
Order By Multiple Columns With Mixed Ascending And Descending Orders
SELECT <column_name>, column_name
FROM <table_name>
ORDER BY <column_name>, <column_name> DESC;
SELECT table_name, tablespace_name
FROM all_tables
ORDER BY tablespace_name DESC, table_name;
By Column Position
Single Column Ascending
SELECT <column_name>
FROM <table_name>
ORDER BY <position_number>;
SELECT table_name
FROM all_tables
ORDER BY 1;
Single Column Descending
SELECT <column_name>
FROM <table_name>
ORDER BY <position_number> DESC;
SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 2 DESC;
Order By Multiple Columns
SELECT <column_name>
FROM <table_name>
ORDER BY <position_number>, <position_number>;
SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 2, 1;
Order By Multiple Columns With Mixed Ascending And Descending Orders
SELECT <column_name>
FROM <table_name>
ORDER BY <position_number> ASC, <position_number> DESC;
SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 1 ASC, 2 DESC;
Order Nulls
Nulls First
SELECT <column_name>
FROM <table_name>
ORDER BY <column_name_or_position_number> NULLS FIRST;
SELECT tablespace_name
FROM all_tables
ORDER BY tablespace_name NULLS FIRST;
Nulls Last
SELECT <column_name>
FROM <table_name>
ORDER BY <column_name_or_position_number> NULLS LAST;
SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 2, 1 NULLS LAST;
The Cost of Ordering
ORDER BY Overhead
conn uwclass/uwclass@pdbdev
CREATE TABLE t AS
SELECT * FROM all_objects;
EXPLAIN PLAN FOR
SELECT * FROM t;
set linesize 121
SELECT * FROM TABLE(dbms_xplan.display);
EXPLAIN PLAN FOR
SELECT * FROM t
ORDER BY 1;
SELECT * FROM TABLE(dbms_xplan.display);
Non-Default Ordering
Ordering With A Decode
spool c:\temp\recompile.sql
SELECT 'ALTER ' || object_type || ' '|| object_name ||' COMPILE;'
FROM user_objects
WHERE object_type IN ('FUNCTION', 'PACKAGE', 'PROCEDURE', 'TRIGGER', 'VIEW')
ORDER BY DECODE(object_type, 'VIEW','A', 'FUNCTION','B', 'PROCEDURE', 'C', 'PACKAGE','D', 'Z');
BEGIN
FOR i IN 1 .. 10
LOOP
INSERT INTO ob_demo
(datecol, numbcol)
VALUES
(TRUNC(SYSDATE + i), MOD(i*11,13));
END LOOP;
COMMIT;
END;
/
SELECT * FROM ob_demo;
-- invalid
SELECT datecol, numbcol
FROM ob_demo
WHERE ROWNUM = 1
ORDER BY datecol;
-- this does not work because rownum is evaluated before the order by
-- valid
SELECT datecol, numbcol
FROM (
SELECT row_number() OVER (ORDER BY datecol DESC) r,
datecol, numbcol
FROM ob_demo)
WHERE r=1;
/* the inner select orders datecol descending and assigns a a value to 'r'. This value starts with 1 and is incremented by 1 for each row. The outer select then selects the row where r = 1 */
SELECT row_number() OVER (ORDER BY datecol DESC) r, datecol, numbcol
FROM ob_demo;
