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? [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
