Weird "CURRENT_SCHEMA" behavior

From: Mladen Gogala <no_at_email.here.invalid>
Date: Wed, 4 Nov 2009 16:17:55 +0000 (UTC)
Message-ID: <pan.2009.11.04.16.17.54_at_email.here.invalid>

The "ALTER SESSION SET CURRENT_SCHEMA" used to only influence the name resolution. In version 10.2, it also influences the owner of the newly created objects:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYSTEM"
SQL> alter session set current_schema=scott;

Session altered.

Elapsed: 00:00:00.07
SQL> select owner from dba_tables where table_name='TEST_EMP';

no rows selected

Elapsed: 00:00:00.29
SQL> create table test_emp as select * from emp;

Table created.

Elapsed: 00:00:00.54
SQL> select owner from dba_tables where table_name='TEST_EMP';

OWNER

---

SCOTT Elapsed: 00:00:00.15
SQL> This is strange! I observed the same behavior in 11.2 database.

-- 
http://mgogala.freehostia.com

Received on Wed Nov 04 2009 - 10:17:55 CST