Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
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;
