Coskan’s Approach to Oracle

March 15, 2007

Viewing plans of old SQLs

Filed under: How To — coskan @ 4:37 pm

Previously I wrote about how to view a plan of a sql. Today I will tell you about a good feature DBMS_XPLAN.DISPLAY_AWR function comes with Oracle 10G which helps you to view plan of an old sql. . If you have license for tuning pack and diagnostics pack you can get historical information about the old SQLs which ran on your database. For more info about licensing feature of these packs refer to the Oracle® Database Licensing Information 10g Release 1 (10.1) manual

DBMS_XPLAN.DISPLAY_AWR displays the contents of an execution plan stored in the AWR.

Syntax is;

DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);

If db_id paramater is not specified the function will use the id of the local db.

If you don’t specify plan_hash_value parameter, function will bring all the stored execution plans for the given sql_id

Format parameter have so many capabilities you can get the list from the manual.

Simple demonstration ; (all tests are done with 10.2.0.1 Express Edition)

code listing 6a

You can also use DBA_HIST_SQL_PLAN table for viewing the historic plan info.

code listing 6b

References Used ;

Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)

Oracle® Database Reference 10g Release 2 (10.2)

Changing the Current Schema

Filed under: Tips — coskan @ 8:32 am

It is  time consuming process for me to put the schema name in front of the objects from that schema when i am doing maintenance processes on the objects of the schema. DBA ‘s usually work with system or sys users and they have to do this alias process for not to face with ORA-00942 error (table or view does not exist). But if the connected user doesn’t have permission on the current schema object you will still get ORA-00942.

To solve this problem you can use current_schema session parameter.

alter session set current_schema=SCHEMA_NAME /*without quotes*/

Usage;

idle> connect scott/tiger@xe;
Connected.
scott@XE> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

scott@XE> create table t1 as select * from user_sys_privs;

Table created.

scott@XE> select * from t1;

USERNAME PRIVILEGE ADM
—————————— —————————————- —
SCOTT CREATE VIEW NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT ALTER SESSION NO
SCOTT CREATE SESSION NO

scott@XE> grant select on t1 to hr; –for testing with hr

Grant succeeded.

scott@XE> connect hr/hr

Connected.

hr@XE> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

hr@XE> create table t1 as select table_name from user_tables;

Table created.

hr@XE> select * from t1; –its own table

TABLE_NAME
——————————
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
MUNEVVER
COSKAN
FLASHBACK_TEST
COUNTRIES

10 rows selected.

hr@XE> alter session set current_schema=scott;

Session altered.

hr@XE> select * from t1; –t1 from the scott schema

USERNAME PRIVILEGE ADM
—————————— —————————————- —
SCOTT CREATE VIEW NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT ALTER SESSION NO
SCOTT CREATE SESSION NO

hr@XE> connect system/*****
Connected.
system@XE> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

system@XE> alter session set current_schema=scott;

Session altered.

system@XE> select * from t1;

USERNAME PRIVILEGE ADM
—————————— —————————————- —
SCOTT CREATE VIEW NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT ALTER SESSION NO
SCOTT CREATE SESSION NO

system@XE> alter session set current_schema=hr;

Session altered.

system@XE> select * from t1;

TABLE_NAME
——————————
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
MUNEVVER
COSKAN
FLASHBACK_TEST
COUNTRIES

10 rows selected.

!!!!! Don’t forget to change your current schema settings after your operations on other schemas.

Use the query below to get your current_schema info

system@XE> SELECT SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’) from DUAL;

SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’)

—————————————————————————————————-

HR

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 203 other followers