Re: Weird "CURRENT_SCHEMA" behavior
Date: Wed, 4 Nov 2009 09:51:52 -0800 (PST)
Message-ID: <4d89b51a-dba2-416d-8575-03c19dcc51b1_at_d5g2000yqm.googlegroups.com>
On Nov 4, 11:17 am, Mladen Gogala <n..._at_email.here.invalid> wrote:
> 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.
Mladen,
Isn't this expected behavior, it seems to be the case that it is expected behavior according to my interpretation of the documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_2013.htm
"CURRENT_SCHEMA
Syntax:
CURRENT_SCHEMA = schema
The CURRENT_SCHEMA parameter changes the current schema of the session
to the specified schema. Subsequent unqualified references to schema
objects during the session will resolve to objects in the specified
schema. The setting persists for the duration of the session or until
you issue another ALTER SESSION SET CURRENT_SCHEMA statement.
This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session."
When you execute the command you are essentially accessing objects in that user's schema without needing to provide the schema alias.
I created a test and found that on 10.2.0.2 changing the
CURRENT_SCHEMA parameter even caused Oracle to create the table in the
default tablespace specified for the other user - I was not expecting
that. I have a generic user with no permissions named LOGGER (a fake
name):
ALTER SESSION SET CURRENT_SCHEMA=LOGGER;
I then executed the following:
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TABLE_TEST';
EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(WAITS=>TRUE,BINDS=>TRUE)
CREATE TABLE DELETE_ME AS SELECT 1 N FROM DUAL;
SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME='DELETE_ME';
no rows selected
SELECT * FROM DELETE_ME; N
1
EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(WAITS=>FALSE,BINDS=>FALSE)
I wanted to see the definition of the table, so I executed the
following:
SET PAGESIZE 0
SET LONG 90000
SPOOL 'C:\GETMETA.SQL'
SELECT DBMS_METADATA.GET_DDL('TABLE','DELETE_ME','LOGGER') FROM DUAL;
SPOOL OFF
The output is the following:
CREATE TABLE "LOGGER"."DELETE_ME"
( "N" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "LOGGER"
Notice that the tablespace is "LOGGER", which is not the name of the
default tablespace for the user who logged into the database. This is
a bit interesting as the table definition differs quite a bit if I
create a table in that user's schema without executing the ALTER
SESSION SET CURRENT_SCHEMA command:
CREATE TABLE "LOGGER"."DELETE_ME2"
( "N" NUMBER
) PCTFREE 10 PCTUSED
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Nov 04 2009 - 11:51:52 CST