How to restrict to DROP/ALTER a table using Triggers

You can restrict users to drop/alter any table within the schema using system triggers.

Connect as sysdba and create following trigger

C:\Users>sqlplus / as sysdba
SQL> CREATE OR REPLACE TRIGGER SCHMEA_TRIGGER
  2  BEFORE  DROP
  3  ON SCOTT.SCHEMA
  4  DECLARE
  5  evt VARCHAR2(1000);
  6  BEGIN
  7  SELECT ora_sysevent INTO evt FROM DUAL;
  8  IF evt = 'DROP' THEN
  9  RAISE_APPLICATION_ERROR(-20900,'UNABLE TO DROP TABLE');
 10  END IF;
 11  END;
 12  /
Trigger created.
SQL>

Connect as scott and try to drop EMP2 table and see the error.

C:\Users>sqlplus scott/tiger
SQL> show user
USER is "SCOTT"
SQL> SELECT COUNT(*) FROM EMP2;
  COUNT(*)
----------
        14
SQL> DROP TABLE EMP2;
DROP TABLE EMP2
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20900: UNABLE TO DROP TABLE
ORA-06512: at line 6

SQL> SELECT COUNT(*) FROM EMP2;
  COUNT(*)
----------
        14
SQL>

-Asifkhan P.

User has no SELECT privilege on V$SESSION

The table function DISPLAY_CURSOR requires to have select privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL. Otherwise you will end up with below error.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
SQL> 

Solution:

If you are getting this error from scott , then grant following permission to scott.

SQL> GRANT SELECT ON v_$session TO scott;
Grant succeeded.

SQL> grant select on v_$sql_plan_statistics_all to scott;
Grant succeeded.

SQL> grant select on v_$sql_plan to scott;
Grant succeeded.

SQL> grant select on v_$sql to scott;
Grant succeeded.

-Asifkhan P.

Advanced Query Rewrite in 11g

As you know we have hardly below options to tune the query which actually we don't have control on it, might be generating third party pools like OBIEE etc.

# Modify Statistics # Add or Remove indexes # Modify Instance Parameter # Modify degree of parallelism of a table/index. # Use Stored Outlines. # Create Materialized Views. # Create View with embedded hints. # Advanced Query Rewrite (DBMS_ADVANCED_REWRITE )

The last one – advanced query rewrite – is the topic today. This technique is especially useful under following situations.

• Oracle 10g+
• When stored outline and sql profile do not help – they use hints to control the execution plan, but there are cases when hints are useless.
• Select, not DML
• With no bind variables

Advanced query rewrite is designed as an assistance to mview query rewrite, but with above conditions met, we can enjoy it’s power with non-mview queries.

Prof of concept:

I have below 2 queries...

#1. SELECT ENAME FROM EMP WHERE JOB='CLERK' UNION SELECT ENAME FROM EMP WHERE JOB='SALESMAN';

#2. SELECT ENAME FROM EMP WHERE JOB IN ('CLERK','SALESMAN');

I wanted to execute #2 query whenever I execute #1 query.

SQL> BEGIN

  2    SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (

  3       name             => 'test_rewrite',

  4       source_stmt      => 'SELECT ENAME FROM EMP WHERE JOB=''CLERK''

  5                               UNION

  6                               SELECT ENAME FROM EMP WHERE JOB=''SALESMAN''',

  7       destination_stmt => 'SELECT ENAME FROM EMP WHERE JOB IN (''CLERK'',''SALESMAN'')',

  8       validate         => FALSE,

  9       rewrite_mode     => 'TEXT_MATCH');

 10  END;

 11  /

PL/SQL procedure successfully completed.

SQL> SELECT ENAME FROM EMP WHERE JOB='CLERK' UNION SELECT ENAME FROM EMP WHERE JOB='SALESMAN';

ENAME

----------

ADAMS

ALLEN

JAMES

MARTIN

MILLER

SMITH

TURNER

WARD

8 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  0arr3v0yafy3c, child number 0

-------------------------------------

SELECT ENAME FROM EMP WHERE JOB='CLERK' UNION SELECT ENAME FROM EMP

WHERE JOB='SALESMAN'

Plan hash value: 3774834881

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |       |       |     8 (100)|          |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   1 |  SORT UNIQUE        |      |     6 |    84 |     8  (63)| 00:00:01 |

|   2 |   UNION-ALL         |      |       |       |            |          |

|*  3 |    TABLE ACCESS FULL| EMP  |     3 |    42 |     3   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS FULL| EMP  |     3 |    42 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("JOB"='CLERK')

   4 - filter("JOB"='SALESMAN')

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

23 rows selected.

SQL> ALTER SESSION SET query_rewrite_integrity = trusted;

Session altered.

SQL> SELECT ENAME FROM EMP WHERE JOB='CLERK' UNION SELECT ENAME FROM EMP WHERE JOB='SALESMAN';

ENAME

----------

SMITH

ALLEN

WARD

MARTIN

TURNER

ADAMS

JAMES

MILLER

8 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  0arr3v0yafy3c, child number 1

-------------------------------------

SELECT ENAME FROM EMP WHERE JOB='CLERK' UNION SELECT ENAME FROM EMP

WHERE JOB='SALESMAN'

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|*  1 |  TABLE ACCESS FULL| EMP  |     6 |    84 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(("JOB"='CLERK' OR "JOB"='SALESMAN'))

19 rows selected.

SQL> EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence (name => 'test_rewrite');

PL/SQL procedure successfully completed.

SQL>

This is how you can get control on non-modified queries. Enjoy:)

Youtube Link : https://www.youtube.com/watch?v=-94LemLzIfs&index=9&list=PLUeF6A_iPYw3yE1VwmiF-tZJFJUZINgim

-Asifkhan P.

AWR Formatter

This utility will give you formated output of AWR report, which is very useful to analyze it.

Link : http://tylermuth.wordpress.com/2011/04/20/awr-formatter/

-Asifkhan P.