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.

Oracle Performance Related Features By Version

This post outlines What Performance Features are available by version.

Feature Name	Description	Version Introduced	License Requirements	Applies to?
Explain Plan	Show Query Execution Plan	Oracle V6	None	Tuning SQL
CBO	Cost-Based Optimizer	Oracle 7	None	Tuning SQL
Analyze / DBMS_STATS	Statistics collection for CBO	Oracle 7	None	Tuning SQL
Event 10053	Trace CBO decision making	Oracle 7	None	Tuning SQL
SQL_TRACE / Event 10046	Trace SQL Execution	Oracle 7	None	Tuning SQL
Parallel Query	Allows Parallel Execution of a single SQL	Oracle 7.1	Enterprise Edition (used to require PQO license)	Tuning SQL
Bitmap Indexes	Faster Queries of large tables with few distinct values	Oracle 7.2	Enterprise Edition	Tuning SQL
Partitioning	Allows storage large objects in multiple segments	Oracle 8i	Enterprise Edition + Partitioning Option	Tuning SQL
Automatic PQ Tuning	Oracle dynamically figures out how many Parallel Execution servers to use	Oracle 8i	Enterprise Edition	Tuning SQL
Auto PGA Memory Management	Oracle dynamically allocates workarea memory needed for SQL execution based on a systemwide target	Oracle 9i	None	Tuning Memory
Dynamic SGA Memory Management	Allows dynamic resizing of the buffer cache and shared pool, including a buffer cache size advice mechanism that predicts the performance of running with different sizes for the buffer cache.	Oracle 9iR1	None	Tuning Memory
Stored Outlines	Allows freezing of CBO execution plans	Oracle 9i	Enterprise Edition + Standard Edition (9iR2 onwards)	Tuning SQL
Oracle OLAP	More SQL options for Data Warehousing / Data Mining	Oracle 9i	Enterprise Edition + OLAP Option	Tuning SQL
User Defined Stats	Provides facility for users to define their own statistics - mostly for user defined types / objects	Oracle 9i	Enterprise Edition	Tuning SQL
Materialized View Query Rewrite	Allows reuse of pre-calculated group by / rollup type stuff	Oracle 9i	Enterprise Edition	Tuning SQL
PGA Memory Advisor	Helps to calculate an optimal pga_aggregate_target	Oracle 9iR2	None	Tuning Memory
Enhanced Statistics Gathering	This includes enhanced Query Execution Stats, DBMS_STATS Improvements and System Statistics	Oracle 9iR2	None	Tuning SQL
Dynamic Sampling of Optimizer Statistics	Dynamically gathers statistics if the existing statistics are incomplete or known to be inaccurate	Oracle 9iR2	None	Tuning SQL
Auto DBMS_STATS Collection	Finds objects with stale stats and gathers new statistics for them	Oracle 10g	None	Tuning SQL
Automatic Workload Repository (AWR)	Automatic Workload Repository (AWR) is an infrastructure that collects, processes, and maintains performance statistics	Oracle 10g	Diagnostics Pack (Enterprise Edition Only)	Tuning Database
Active Session History (ASH)	Continual sampling history of top sessions and the SQL they are executing	Oracle 10g	Diagnostics Pack (Enterprise Edition Only)	Tuning Database
ADDM	Analyzes Workload Repository and makes tuning suggestions even pointing out Top SQL	Oracle 10g	Diagnostics Pack (Enterprise Edition Only)	Tuning Database
Automatic Shared Memory Tuning	Automates the configuration of System Global Area (SGA) memory-related parameters (buffer cache, shared pool) through self-tuning algorithms.	Oracle 10gR1	None	Tuning Memory
Segment Advisor	Finds objects that are fragmented with free space and can be shrunk	Oracle 10g	None	Tuning SQL
trcsess Utility	The trcsess utility consolidates trace output from selected trace files	Oracle 10g	None	Tuning Storage
SQL Tuning Advisor (STA)/ SQL Profiles	Finds SQL where the optimizer makes bad decisions due to inaccurate usage of statistics and provides recommendations	Oracle 10g	Tuning Pack (Enterprise Edition Only)	Tuning SQL
SQL Tuning Sets	Used by Tuning Advisor (and later SQL Perf Analyzer)	Oracle 10g	Tuning Pack or  Real Application Testing (Enterprise Edition Only)	Tuning SQL
SQL Access Advisor	Makes recommendations about indexes, materialized views, and partitions to create, drop, or retain	Oracle 10g	Tuning Pack (Enterprise Edition Only)	Tuning SQL
DBMS_STATS enhancements	Lock stats, restore historical stats	Oracle 10gR1	None	Tuning SQL
DBMS_SQLDIAG	Provides an interface to the SQL Diagnosability functionality.	Oracle 10gR2	None	Universal
ASH Reporting	Summarises Active Session History information making it more useable	Oracle 10gR2	Diagnostics Pack (Enterprise Edition Only)	Tuning Database
SQL Execution History	Better reporting of how execution plans may have changed over time	Oracle 10gR2	Diagnostics Pack (Enterprise Edition Only)	Tuning SQL
Virtual Columns	Allows 'virtual' columns to be added to a table	Oracle 11gR1	None	Tuning SQL
SQL Test Case Builder	Generic package to record standard information for issue diagnosis. Builds package with SQL, associated table / index / view definitions etc.	Oracle 11gR1	None	Universal
Real Application Testing (RAT)	Real Application Testing functionality allows potential issues with system changes to be identified before they are deployed in a production environment	Oracle 11gR1	Real Application Testing option (Enterprise Edition Only)	Universal
SQL Performance Analyzer(SPA)	Tests and reports how SQL Performance can be affected by configuration / version changes	Oracle 11gR1	Real Application Testing option (Enterprise Edition Only)	Tuning SQL
Auto SQL Tuning	Identifies Top SQL and applies SQL Profiles if needed	Oracle 11gR1	Tuning Pack (Enterprise Edition Only)	Tuning SQL
SQL Plan Management (SPM)	Gives DBAs more control as to when execution plans can change	Oracle 11gR1	Enterprise Edition	Tuning SQL
Manual Plan Evolution	Allows DBAs to explicitly evolve plans	Oracle 11gR1	Enterprise Edition	Tuning SQL
Automatic Plan Evolution	Allow Auto SQL Tuning to evolve new plans without user intervention	Oracle 11gR1	Tuning Pack (Enterprise Edition Only)	Tuning SQL
Enhanced Stats Management	Test how SQL Execution Plans may be affected by newly gathered statistics before publishing them	Oracle 11gR1	None	Tuning SQL
MultiColumn Stats	Collect stats across columns that have some data dependency	Oracle 11gR1	None	Tuning SQL
Adaptive Cursor Sharing (ACS)	When bind variables or cursor_sharing is used, allows shared SQL cursors to still have multiple shared plans where data distribution is skewed	Oracle 11gR1	None	Tuning SQL
Query Result Cache	The query result cache enables explicit caching of results in database memory. Subsequent queries using the cached results will experience significant performance improvements.	Oracle 11gR1	Enterprise Edition	Universal
Stored Outlines to Plan Management Migration	Allows 9i stored outlines to be turned into SQL Plans (since outlines are deprecated)	Oracle 11gR2	Enterprise Edition	Tuning SQL
Real-Time Database Operations Monitoring	Allows database administrators to easily monitor and troubleshoot performance problems in long running jobs by composite database operation monitoring	Oracle 12cR1	None	Tuning Database
Real-Time ADDM Analysis	Real-Time ADDM runs through a set of predefined criteria to analyze the current performance of the database.	Oracle 12cR1	Diagnostics Pack (Enterprise Edition Only)	Tuning Database
Adaptive Query Optimization	Adaptive query optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics.	Oracle 12cR1	None	Tuning SQL
Enhanced Column Histogram Statistics	Two additional types of histograms have been introduced for columns which have more than 254 distinct values to improve the cardinality estimates generated using histograms.	Oracle 12cR1	None	Tuning SQL
Online Statistics Gathering for Bulk Loads	This features automatically generates statistics for data that is added as part of a bulk load operation such as a CREATE TABLE AS SELECT operation or an INSERT INTO ... SELECT operation on an empty table.	Oracle 12cR1	None	Tuning SQL
Session-Private Statistics for Global Temporary Tables	This feature allows global temporary tables to have a different set of statistics for each session.	Oracle 12cR1	None	Tuning SQL
SQL Plan Directives	SQL plan directives allow the optimizer to automatically use prior execution information to determine that extended statistics may be required for a similar queries executed later.	Oracle 12cR1	None	Tuning SQL
Adaptive SQL Plan Management	Adaptive SQL Plan Management allows the database to automatically verify, evolve and accept non-accepted plans that perform better than the existing accepted plan	Oracle 12cR1	None	Tuning SQL
Automatic Column Group Detection	Detects potential column groups based upon workload	Oracle 12cR1	None	Tuning SQL

--Asifkhan

How to flush single SQL Statement from Shared Pool?

It is well known that the entire shared pool can be flushed with a simple ALTER SYSTEM statement.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.

If a single SQL statement has to be flushed out of the object library cache, the first step is to find the address of the handle and the hash value of the cursor that has to go away. Name of the object [to be purged] is the concatenation of the ADDRESS and HASH_VALUE columns from the V$SQLAREA view. Here is an example:

SYS@mydb>SELECT ADDRESS, HASH_VALUE FROM GV$SQLAREA WHERE SQL_ID = 'gj3nt7pjvs5bw';

ADDRESS                 HASH_VALUE

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

000000034DA0E428    1673270652

SYS@mydb>exec DBMS_SHARED_POOL.PURGE ('000000034DA0E428, 1673270652', 'C');

PL/SQL procedure successfully completed.

SYS@mydb>

That set, your sql is gone from shared pool, this is very useful while to tune the query, I frequently use this technique. Enjoy.....

-Asif Pathan

Trace your session

Trace your session:

Enabling trace:
Enable the SQL Trace facility for the session by using one of the following:

1)  EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);

2)  ALTER SESSION SET SQL_TRACE = TRUE;

3) DBMS_SESSION.SET_SQL_TRACE 

Disabling trace:
EXEC DBMS_SESSION.SESSION_TRACE_DISABLE();

Easily identifying your trace files:
alter session set tracefile_identifier='mysession';

Session/Database Level Tracing

To enable tracing for a specific session ID and serial number, determine the values for the session to trace:

SELECT SID, SERIAL#, USERNAME FROM V$SESSION;

       SID    SERIAL# USERNAME

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

        120       23   ERP

To trace particular session:

EXEC dbms_monitor.SESSION_TRACE_ENABLE(session_id=> 120, serial_num=>23, waits=>TRUE, binds=>FALSE);

EXEC dbms_monitor.SESSION_TRACE_DISABLE(session_id =>120, serial_num=>23);

To trace all sessions in the database:

EXEC dbms_monitor.DATABASE_TRACE_ENABLE(TRUE,TRUE);

EXEC dbms_monitor.DATABASE_TRACE_DISABLE();

--AsifKhan

Service Level Tracing

The SERV_MOD_ACT_TRACE_ENABLE procedure enables SQL tracing for a given combination of service name, module, and action globally for a database.

To Trace all sessions that log in under the ERP service are traced. A trace file is created for each session that uses the service, regardless of the module and action.

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('ERP');

To Trace service along with particular module, and action:

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('ERP', 'PAYMENTS_MODULE', 'SALES_ACTION');

Trace a particular client identifier:
exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE (client_id=>'C1', waits => TRUE, binds => FALSE);

--AsifKhan

Identify Trace File

Different methods to identify your trace file:

     1) From v$diag_info
     2) By Joining v$session, v$process, and v$parameter
     3) Set TRACEFILE_IDENTIFIER parameter

1) Identify the current trace file.
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
VALUE
-----------------------------------------------------------------------------
d:\app\diag\rdbms\obieedb\obieedb\trace\obieedb_ora_7728.trc


2)  You can get trace file using this query:

SELECT s.sid, s.serial#, pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||   '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
           v$process p,
           v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    s.paddr = p.addr

AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');


3) You can set the TRACEFILE_IDENTIFIER parameter at session level to allow you to include some recognizable text into the trace file name:


ALTER SESSION SET TRACEFILE_IDENTIFIER = "POST_FIX_TEXT";