Coskan’s Approach to Oracle

March 15, 2007

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

About these ads

12 Comments »

  1. Hi,

    Grt work,thanks for sharing broad steps.

    REgards,
    Viswa

    Comment by viswa08 — April 20, 2009 @ 10:20 am

  2. but at the last how to change my current schema settings back after doing my operations on other schemas?

    Comment by chandu — March 19, 2012 @ 6:15 pm

    • Why don’t you run the sae command for your user to revert it back

      Sent from my tablet device. Sorry for all typo

      Comment by coskan — March 19, 2012 @ 6:30 pm

  3. but this is for the user who has dba privilege .what about the normal users?? do they need the required permission to select the objects from the other schema.

    Comment by vaibhav sharma — April 27, 2012 @ 12:28 pm

    • Obviously you need select role for that particular schema objecs

      Sent from my mobile device.

      Comment by coskan — April 27, 2012 @ 12:41 pm

  4. it is possoble to see plan after changing current schema ?
    for example
    11:58:55 xxx@xxx> alter session set current_schema=PARUS;

    Session altered.

    Elapsed: 00:00:00.00
    11:59:04 xxx@xxx> explain plan for select * from dual;

    Explained.

    Elapsed: 00:00:00.03
    11:59:14 xxx@xxx> @utlxpls

    PLAN_TABLE_OUTPUT
    ———————————————————————————————————————————————-
    Plan hash value: 3269596396

    ————————————————————————————————————————————-
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
    ————————————————————————————————————————————-
    | 0 | SELECT STATEMENT | | 1 | 405 | | 2393K (4)| 02:28:22 |
    | 1 | SORT ORDER BY | | 1 | 405 | | 2393K (4)| 02:28:22 |
    |* 2 | FILTER | | | | | | |
    | 3 | NESTED LOOPS | | 1 | 405 | | 2393K (4)| 02:28:22 |
    | 4 | NESTED LOOPS OUTER | | 1 | 366 | | 2393K (4)| 02:28:22 |
    | 5 | NESTED LOOPS | | 1 | 356 | | 2393K (4)| 02:28:22 |
    | 6 | NESTED LOOPS | | 1 | 346 | | 2393K (4)| 02:28:22 |
    | 7 | NESTED LOOPS | | 1 | 316 | | 2392K (4)| 02:28:22 |
    | 8 | NESTED LOOPS | | 1 | 302 | | 2392K (4)| 02:28:22 |
    | 9 | NESTED LOOPS | | 12 | 3372 | | 2392K (4)| 02:28:22 |
    | 10 | NESTED LOOPS | | 12 | 3252 | | 2392K (4)| 02:28:22 |
    | 11 | NESTED LOOPS | | 12 | 2868 | | 2392K (4)| 02:28:22 |
    | 12 | NESTED LOOPS | | 423 | 76986 | | 2390K (4)| 02:28:14 |
    |* 13 | HASH JOIN ANTI | | 100 | 13900 | | 2390K (4)| 02:28:13 |
    |* 14 | TABLE ACCESS BY INDEX ROWID | TRANSINVDEPT | 100 | 12500 | | 421 (1)| 00:00:02 |
    |* 15 | INDEX RANGE SCAN | I_TRANSINVDEPT_IN_WORK_DATE | 1378 | | | 12 (0)| 00:00:01 |
    | 16 | VIEW | VW_SQ_1 | 18M| 252M| | 2389K (4)| 02:28:11 |
    |* 17 | HASH JOIN | | 18M| 2739M| | 2389K (4)| 02:28:11 |
    |* 18 | TABLE ACCESS FULL | AZSGSMWAYSTYPES | 143 | 1430 | | 123 (1)| 00:00:01 |
    |* 19 | HASH JOIN | | 18M| 2558M| 2344K| 2389K (4)| 02:28:09 |
    | 20 | TABLE ACCESS BY INDEX ROWID | DOCLINKS | 34695 | 1931K| | 286K (1)| 00:17:45 |
    |* 21 | INDEX RANGE SCAN | I_DOCLINKS_OUT_IN_CODE | 746K| | | 7197 (1)| 00:00:27 |
    | 22 | MERGE JOIN CARTESIAN | | 18M| 1531M| | 2015K (5)| 02:04:59 |
    | 23 | NESTED LOOPS | | 34695 | 2405K| | 324K (1)| 00:20:09 |
    | 24 | TABLE ACCESS BY INDEX ROWID| DOCLINKS | 34695 | 1931K| | 286K (1)| 00:17:45 |
    |* 25 | INDEX RANGE SCAN | I_DOCLINKS_OUT_IN_CODE | 746K| | | 7197 (1)| 00:00:27 |
    | 26 | TABLE ACCESS BY INDEX ROWID| STOREOPERJOURN | 1 | 14 | | 2 (0)| 00:00:01 |
    |* 27 | INDEX UNIQUE SCAN | C_STOREOPERJOURN_PK | 1 | | | 1 (0)| 00:00:01 |
    | 28 | BUFFER SORT | | 545 | 7630 | | 2015K (5)| 02:04:59 |
    |* 29 | INDEX FAST FULL SCAN | I_SALESRTMAIN_RPT_UNIT | 545 | 7630 | | 49 (7)| 00:00:01 |
    | 30 | TABLE ACCESS BY INDEX ROWID | TRANSINVDEPTSPECS | 4 | 172 | | 3 (0)| 00:00:01 |
    |* 31 | INDEX RANGE SCAN | C_TRANSINVDEPTSPECS_UK | 4 | | | 2 (0)| 00:00:01 |
    |* 32 | TABLE ACCESS BY INDEX ROWID | DOCLINKS | 1 | 57 | | 5 (0)| 00:00:01 |
    |* 33 | INDEX RANGE SCAN | I_DOCLINKS_IN_DOCOUTCODE | 2 | | | 3 (0)| 00:00:01 |
    |* 34 | TABLE ACCESS BY INDEX ROWID | STOREOPERJOURN | 1 | 32 | | 2 (0)| 00:00:01 |
    |* 35 | INDEX UNIQUE SCAN | C_STOREOPERJOURN_PK | 1 | | | 1 (0)| 00:00:01 |
    |* 36 | TABLE ACCESS BY INDEX ROWID | AZSGSMWAYSTYPES | 1 | 10 | | 1 (0)| 00:00:01 |
    |* 37 | INDEX UNIQUE SCAN | C_AZSGSMWAYSTYPES_RN_PK | 1 | | | 0 (0)| 00:00:01 |
    |* 38 | TABLE ACCESS BY INDEX ROWID | GOODSSUPPLY | 1 | 21 | | 2 (0)| 00:00:01 |
    |* 39 | INDEX UNIQUE SCAN | C_GOODSSUPPLY_PK | 1 | | | 1 (0)| 00:00:01 |
    | 40 | TABLE ACCESS BY INDEX ROWID | NOMMODIF | 1 | 14 | | 1 (0)| 00:00:01 |
    |* 41 | INDEX UNIQUE SCAN | C_NOMMODIF_RN_PK | 1 | | | 0 (0)| 00:00:01 |
    | 42 | TABLE ACCESS BY INDEX ROWID | DICNOMNS | 1 | 30 | | 1 (0)| 00:00:01 |
    |* 43 | INDEX UNIQUE SCAN | C_DICNOMNS_RN_PK | 1 | | | 0 (0)| 00:00:01 |
    | 44 | TABLE ACCESS BY INDEX ROWID | DICMUNTS | 1 | 10 | | 1 (0)| 00:00:01 |
    |* 45 | INDEX UNIQUE SCAN | C_DICMUNTS_PK | 1 | | | 0 (0)| 00:00:01 |
    | 46 | TABLE ACCESS BY INDEX ROWID | DICMUNTS | 1 | 10 | | 1 (0)| 00:00:01 |
    |* 47 | INDEX UNIQUE SCAN | C_DICMUNTS_PK | 1 | | | 0 (0)| 00:00:01 |
    | 48 | TABLE ACCESS BY INDEX ROWID | GOODSPARTIES | 1 | 39 | | 2 (0)| 00:00:01 |
    |* 49 | INDEX UNIQUE SCAN | C_GOODSPARTIES_PK | 1 | | | 1 (0)| 00:00:01 |
    ————————————————————————————————————————————-

    Predicate Information (identified by operation id):
    —————————————————

    2 – filter(TO_DATE(:B5)=:B5 AND “TR”.”IN_WORK_DATE”<=:B4)
    17 – access("SOJ1"."STOPER"="SO1"."RN")
    18 – filter("SO1"."GSMWAYS_TYPE"=1)
    19 – access("DL1"."OUT_DOCUMENT"="TRP"."RN")
    21 – access("DL1"."OUT_UNITCODE"='TradeReports' AND "DL1"."IN_UNITCODE"='GoodsTransInvoicesToDepts')
    25 – access("DL2"."OUT_UNITCODE"='StoreOpersJournal' AND "DL2"."IN_UNITCODE"='GoodsTransInvoicesToDepts')
    27 – access("DL2"."OUT_DOCUMENT"="SOJ1"."RN")
    29 – filter("TRP"."RPT_UNIT"=TO_NUMBER(:B1))
    31 – access("TR"."RN"="TRS"."PRN")
    32 – filter("DL"."IN_UNITCODE"='GoodsTransInvoicesToDeptsSpecs')
    33 – access("DL"."IN_DOCUMENT"="TRS"."RN" AND "DL"."OUT_UNITCODE"='StoreOpersJournal')
    34 – filter(:B2 IS NULL OR "SOJ"."STOPER"=TO_NUMBER(:B2))
    35 – access("DL"."OUT_DOCUMENT"="SOJ"."RN")
    36 – filter("SO"."GSMWAYS_TYPE"=1)
    37 – access("SOJ"."STOPER"="SO"."RN")
    38 – filter("S"."STORE"=TO_NUMBER(:B1))
    39 – access("SOJ"."GOODSSUPPLY"="S"."RN")
    41 – access("TRS"."NOMMODIF"="M"."RN")
    43 – access("M"."PRN"="N"."RN")
    45 – access("N"."UMEAS_MAIN"="DMM"."RN")
    47 – access("N"."UMEAS_ALT"="DMA"."RN"(+))
    49 – access("S"."PRN"="GP"."RN")

    86 rows selected.

    Elapsed: 00:00:00.11

    and that is not my plan =(

    Comment by djeday84 — May 21, 2012 @ 8:02 am

  5. PLEASE REPLY ME
    I’ve Created DEV , UCM and PORTAL schema from RCU. and Then try to install UCM ,when schema passwrd screen appear for LOGIN and Create weblogic domain , It tell me INCORRECT PASSWORD, SO , I’ve DROP these 3 schema using RCU , during DROP schema, different errors appeared , and I’ve DELETED the middleware\rcu_Home folder and Unzip the rcu.rar fresh file to the same directory
    NOW
    when I RUN rcu.bat and selectec CREATE SCHEMA then RCU WOUNDNT ALLOW ME TO CREATE SCHEMAS FOR PORTAL , DEV , UCM ..and also they appear in combo box to select

    BUT I’ve ALREDY DROP THAT
    I think it only droped not DELETED
    tell me the sqlplus commands fro DELETE schema. and also tell if it is DANGEROUS to delete some schema(example DEV schema) as I’m developing portal via WEBCENTER -Jdeveloper(integrated weblogic) , Xe , RCU, UCM )

    Comment by MUHAMMAD Junaid — October 18, 2012 @ 2:03 pm

  6. You should take part in a contest for one of the most useful sites online.
    I am going to highly recommend this blog!

    Comment by Recover Twitter Password Tutorial — July 25, 2013 @ 2:46 am

  7. Thanks phylor I still have these days myself. I tend for
    being a little of an “emotional sponge,” and when hubby is feeling miserable and negative I
    actually possess a lot of trouble with not sharing his
    attitude. It really is even worse on “payday,” when I see how little is left immediately after the rent is paid and I comprehend its likely to be an additional “hungry” week.

    Comment by zenni optical 224815 — August 22, 2013 @ 3:58 am

  8. I really like your blog.. very nice colors & theme. Did youu make this website
    yourself or did you hijre someone to do it for you?
    Plz answer back as I’m looking to construct my own blog and would like to know where u got this from.
    appreciate it

    Comment by Ramon — September 11, 2013 @ 4:31 pm

  9. Hi there, for all time i used to check blog posts here early in the dawn, because i love to gain knowledge of more
    and more.

    Comment by Axl Hazarika VEVO — September 23, 2013 @ 1:57 am

  10. Hey There. I found your blog using msn. This is a very well written article.
    I will be sure to bookmark it and return to read
    more of your useful info. Thanks for the post.

    I’ll certainly comeback.

    Comment by http://mediasoftware.in — September 26, 2013 @ 9:05 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: