Friday, January 11, 2008

Change Schema "ALTER SESSION SET CURRENT_SCHEMA" (Without using synonyms)

Perhaps I want to query the data in the other schema.(no reference, no synonym)

SQL> CONNECT MGR;
SQL> GRANT SELECT ON TABLE_DATA TO APP;

SQL> CONNECT APP;
SQL> SELECT COUNT(*) FROM MGR.TABLE_DATA;

COUNT(*)
20

I WOULD LIKE TO
SQL> SELECT COUNT(*) FROM TABLE_DATA;

SELECT COUNT(*) TABLE_DATA
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


I DON'T WANT TO CREATE SYSNONYM. HOW COULD I DO?

SOLUTION: (ALTER SESSION SET CURRENT_SCHEMA)

SQL> CONNECT APP;
SQL> ALTER SESSION SET CURRENT_SCHEMA=MGR;
Session altered.

SQL> SELECT COUNT(*) FROM TABLE_DATA;
COUNT(*)
20
...

Have Fun!

2 comments:

Brent Clay said...

Many thanks!

Anonymous said...

It's run correctly. Thanks.