Oracle-L: RE: alter session

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 08 Oct 2003 19:24:25 -0800
Message-ID: <F001.005D26FB.20031008192425@fatcity.com>

Well my blood pressure has been raised by alter session set current_schema = ... today.

Here are a couple of interesting things I have found about that "feature":

Case A)
If you sign on as userA, set current_schema to userB, then use dbms_sql to 'create table t': in 7.3.4 and 8.0 - table will be created belonging to userA in 8.1 and higher - table will be created belonging to userB

Case B)
In 8.1.7
If you set current_schema to userA and try to add a constraint to a table belonging to userB, it works if you are signed in as a DBA user, but you get ORA-01031 if you are signed on as SYSDBA. How does that make sense?!?!

Case A) sample script
connect userA/userA
alter session set current_schema = userB ; declare

ignore pls_integer ;
c_dynsql pls_integer ;
begin

   c_dynsql := dbms_sql.open_cursor ;
   dbms_sql.parse (c_dynsql, 'create table my_test_table (n number)', dbms_sql.native) ;    ignore := dbms_sql.execute (c_dynsql) ;    dbms_sql.close_cursor (c_dynsql) ;
end ;
/
select owner from dba_tables
  where table_name = 'MY_TEST_TABLE' ;
-- +++++++++++++++++++++++++++++

in 7.3 and 8.0: table owner will be userA
in 8.1 and higher: table owner will be userB

Case B) sample script
-- +++++++++++++++++++++++++++++

jrk, a, b are all users with DBA role
+++++++++++++++++++++++++++++ connect jrk/&password create table a.t (n1 number, n2 number) ; alter table a.t add (constraint t_uq1 unique (n1)) ; alter session set current_schema = b ;
statement below: no error when signed on as JRK alter table a.t add (constraint t_uq2 unique (n2)) ; drop table a.t ;
+++++++++++++++++++++++++++++ connect jrk/&password as sysdba create table a.t (n1 number, n2 number) ; alter table a.t add (constraint t_uq1 unique (n1)) ; alter session set current_schema = b ;
statement below: fails when signed on as SYSDBA alter table a.t add (constraint t_uq2 unique (n2)) ; drop table a.t ;

results:

SQL> -- +++++++++++++++++++++++++++++
SQL> --  jrk, a, b are all users with DBA role
SQL> -- +++++++++++++++++++++++++++++

SQL> connect jrk/&password
Connecté.
SQL> create table a.t (n1 number, n2 number) ; Table créée.
SQL> alter table a.t add (constraint t_uq1 unique (n1)) ; Table modifiée.
SQL> alter session set current_schema = b ; Session modifiée.
SQL> -- statement below: no error when signed on as JRK SQL> alter table a.t add (constraint t_uq2 unique (n2)) ; Table modifiée.
SQL> drop table a.t ;
Table supprimée.

SQL> -- +++++++++++++++++++++++++++++

SQL> connect jrk/&password as sysdba
Connecté.
SQL> create table a.t (n1 number, n2 number) ; Table créée.
SQL> alter table a.t add (constraint t_uq1 unique (n1)) ; Table modifiée.
SQL> alter session set current_schema = b ; Session modifiée.
SQL> -- statement below: fails when signed on as SYSDBA SQL> alter table a.t add (constraint t_uq2 unique (n2)) ; alter table a.t add (constraint t_uq2 unique (n2)) *
ERREUR à la ligne 1 :
ORA-01031: privilèges insuffisants
SQL> drop table a.t ;
Table supprimée.

-----Original Message-----
Paul Drake

it raises the DBA's blood pressure by 50 mm Hg. if found, it prevents the user from having an unlocked account. if found, it is possible that it gets the user a termination notice.

you're in the sys schema for what purpose? testing recovery from dictionary corruption?

bulbultyagi_at_now-india.net.in wrote:
List, what does the following do ?

alter session set current_schema=sys;
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jacques Kilchoer
INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Oct 08 2003 - 22:24:25 CDT