Friday May 28, 2010

Oracle RDBMS : Flushing a Single SQL Statement out of the Object Library Cache

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.

What if the execution plan of a single SQL statement has to be invalidated or flushed out of the shared pool so the subsequent query execution forces a hard parse on that SQL statement. Oracle 11g introduced a new procedure called PURGE in the DBMS_SHARED_POOL package to flush a specific object such as a cursor, package, sequence, trigger, .. out of the object library cache.

The syntax for the PURGE procedure is shown below.


procedure PURGE (
        name VARCHAR2, 
        flag CHAR DEFAULT 'P', 
        heaps NUMBER DEFAULT 1)

Explanation for each of the arguments is documented in detail in $ORACLE_HOME/rdbms/admin/dbmspool.sql file.

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:


SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

ADDRESS 	 HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

no rows selected

Note to Oracle 10g R2 Customers

The enhanced DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release.

10.2.0.2 and 10.2.0.3 customers can download and install RDBMS patch 5614566 to get access to these enhancements in DBMS_SHARED_POOL package.

Also see:

Thursday Dec 10, 2009

Oracle RDBMS: Extracting the Table, Index & View Definitions (DDL) and Indexed Columns

(Reproducing a 30 month old blog post from my other blog at blogger. Source URL:
    http://technopark02.blogspot.com/2007/05/oracle-how-to-get-tableview-definition.html
)

Q: How to extract the table definition (DDL statement) from an Oracle database without having to go through a stack of dictionary views?

A: By calling the GET_DDL() function of metadata package DBMS_METADATA.

Syntax:
select DBMS_METADATA.GET_DDL('TABLE','<table_name>') from DUAL;

eg.,
SQL> set long 1000
SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL('TABLE','PERSON') from DUAL;

  CREATE TABLE "FS890"."PERSON"
   (    "SSN" VARCHAR2(12),
        "FIRST_NAME" VARCHAR2(25),
        "LAST_NAME" VARCHAR2(25),
        "STREET" VARCHAR2(40),
        "CITY" VARCHAR2(30),
        "STATE" VARCHAR2(30),
        "ZIP" VARCHAR2(15),
        "COUNTRY" VARCHAR2(35)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)  TABLESPACE "PSDEFAULT"



Q: How to extract the index definition (DDL statement) from an Oracle database for a given index nam?

A: By calling the GET_DDL() function of metadata package DBMS_METADATA.

Syntax:
select DBMS_METADATA.GET_DDL('INDEX','<index_name>') from DUAL;

eg.,
SQL> create index PERSON_IDX on PERSON ( SSN ); 

Index created.

SQL> set long 1000
SQL> set pagesize 0

SQL> select  DBMS_METADATA.GET_DDL('INDEX','PERSON_IDX') from DUAL;

  CREATE INDEX "FS890"."PERSON_IDX" ON "FS890"."PERSON" ("SSN")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PSDEFAULT"

Note:

If the interest is only to get the indexed column names for an index, simply query COLUMN_NAME of table USER_IND_COLUMNS.

Syntax:
select COLUMN_NAME from USER_IND_COLUMNS where INDEX_NAME = '<index_name>';

eg.,
SQL> column COLUMN_NAME format A15
SQL> select COLUMN_NAME from USER_IND_COLUMNS where INDEX_NAME = 'PERSON_IDX';

COLUMN_NAME
---------------
SSN



Q: Given a view name, how do we get the definition of the view? i.e., how to get the corresponding DDL statement that was used to create the view?

A: Query the TEXT column of table DBA_VIEWS.

Syntax:
SQL> set long 10000

SQL> select TEXT
  2  FROM DBA_VIEWS
  3  where OWNER = '<owner_name>'
  4  and VIEW_NAME  = '<view_name>';
Here is an example:
% sqlplus fs890/fs890@fs890

SQL> create table PERSON (
  2  SSN        VARCHAR2(12),
  3  FIRST_NAME VARCHAR2(25),
  4  LAST_NAME  VARCHAR2(25),
  5  STREET     VARCHAR2(40),
  6  CITY       VARCHAR2(30),
  7  STATE      VARCHAR2(30),
  8  ZIP        VARCHAR2(15),
  9  COUNTRY    VARCHAR2(35));

Table created.

SQL> create view PERSON_VW as
  2  select SSN, FIRST_NAME, LAST_NAME from PERSON;

View created.

SQL> set long 1000
SQL> select TEXT
  2  from DBA_VIEWS
  3  where OWNER = 'FS890'
  4  and VIEW_NAME  = 'PERSON_VW';

TEXT
--------------------------------------------------------------------------------
SELECT SSN, FIRST_NAME, LAST_NAME FROM PERSON



Q: How to find the schema name and the DB user name from an active session?

A: Run the following query:

select sys_context('USERENV', 'SESSION_USER') SESSION_USER, sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual;

Alternatively run select USER from DUAL; to find the current {session} user name.

sys_context() function returns the value of parameter associated with the context namespace. USERENV is an Oracle provided namespace that describes the current session. Check the table Predefined Parameters of Namespace USERENV for the list of parameters and the expected return values.

eg.,
SQL> column SESSION_USER format A15
SQL> column CURRENT_SEHEMA format A15

SQL> select sys_context('USERENV', 'SESSION_USER') SESSION_USER, 
  2  sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual;

SESSION_USER    CURRENT_SCHEMA
--------------- ---------------
FS890           FS890

SQL> column USER format A6

SQL> select USER from DUAL;

USER
------
FS890



Note:

Be aware that there are multiple ways of extracting the same piece of information from an Oracle database. I just provided the ones that I frequently use as part of my work.

About

Benchmark announcements, HOW-TOs, Tips and Troubleshooting

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today