 |
|
| Computers Forum Index » Computer - Databases - Oracle (Server) » Weird "CURRENT_SCHEMA" behavior... |
|
Page 1 of 1 |
|
| Author |
Message |
| Mladen Gogala... |
 Posted: Wed Nov 04, 2009 4:17 pm |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
| Charles Hooper... |
| Posted: Wed Nov 04, 2009 5:51 pm |
|
|
|
Guest
|
On Nov 4, 11:17 am, Mladen Gogala <n... at (no spam) email.here.invalid> wrote:
Quote: 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. |
|
|
| Back to top |
|
|
|
| Mladen Gogala... |
| Posted: Wed Nov 04, 2009 6:36 pm |
|
|
|
Guest
|
On Wed, 04 Nov 2009 19:07:29 +0100, Maxim Demenko wrote:
Quote: Mladen Gogala 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:
This is strange! I observed the same behavior in 11.2 database.
I observed the same behaviour in 8i and 9i databases as well. Maybe,
this is expected behaviour?
;-)
Best regards
Maxim
I can't point to the article, but I do remember reading that it should
only change the name resolution. I also tested it on a 9i database and it
works as above. This is, apparently, the expected behavior.
--
http://mgogala.freehostia.com |
|
|
| Back to top |
|
|
|
| Maxim Demenko... |
| Posted: Wed Nov 04, 2009 11:07 pm |
|
|
|
Guest
|
Mladen Gogala wrote:
Quote: 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:
This is strange! I observed the same behavior in 11.2 database.
I observed the same behaviour in 8i and 9i databases as well.
Maybe, this is expected behaviour?
;-)
Best regards
Maxim |
|
|
| Back to top |
|
|
|
| Maxim Demenko... |
| Posted: Wed Nov 04, 2009 11:47 pm |
|
|
|
Guest
|
Mladen Gogala wrote:
Quote: On Wed, 04 Nov 2009 19:07:29 +0100, Maxim Demenko wrote:
Mladen Gogala 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:
This is strange! I observed the same behavior in 11.2 database.
I observed the same behaviour in 8i and 9i databases as well. Maybe,
this is expected behaviour?
;-)
Best regards
Maxim
I can't point to the article, but I do remember reading that it should
only change the name resolution. I also tested it on a 9i database and it
works as above. This is, apparently, the expected behavior.
Well, for me it sounds like it changes exactly the parsing schema id
with all consequences regarding the name resolution (also, regardless,
*how* you reference the objects). The only exception from the rule is
database link
http://books.google.com/books?id=pIcgos-wwy8C&pg=PP20&dq=oracle+secrets#v=onepage&q=current%20schema&f=false
but, it isn't somehow unexpected.
Best regards
Maxim |
|
|
| Back to top |
|
|
|
| Mladen Gogala... |
| Posted: Wed Nov 25, 2009 4:36 pm |
|
|
|
Guest
|
On Wed, 25 Nov 2009 09:41:37 -0500, Serge Rielau wrote:
Quote: Mladen,
Could it be you are working with more than one DBMS vendor.
Yes, that is the situation.
Quote: What you
expect is the behavior in e.g. DB2 where USER <> SCHEMA.
Cheers
Serge
Actually, the other database I am currently working with is PostgreSQL,
where USER <> SCHEMA, too.
--
http://mgogala.byethost5.com |
|
|
| Back to top |
|
|
|
| Mladen Gogala... |
| Posted: Wed Nov 25, 2009 7:30 pm |
|
|
|
Guest
|
On Wed, 25 Nov 2009 09:41:37 -0500, Serge Rielau wrote:
Quote: Mladen,
Could it be you are working with more than one DBMS vendor. What you
expect is the behavior in e.g. DB2 where USER <> SCHEMA.
Cheers
Serge
I am working with Oracle and PostgreSQL where SCHEMA != USER. Here is an
interesting query in PostgreSQL:
mgogala at (no spam) nycwxp2622:~$ psql -U scott -h localhost
Password for user scott:
psql (8.4.1)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
scott=> with emp1 as (select ename,
scott(> deptno,
scott(> rank() over
scott(> (partition by deptno order by sal) as
salrank
scott(> from emp)
scott-> select ename,deptno,salrank from emp1 where salrank=1;
ename | deptno | salrank
--------+--------+---------
MILLER | 10 | 1
SMITH | 20 | 1
JAMES | 30 | 1
(3 rows)
You have 3 guesses to guess the password for user scott. There is also an
interesting addition to Oracle capabilities with "recursive" subquery
factoring.
--
http://mgogala.byethost5.com |
|
|
| Back to top |
|
|
|
| Serge Rielau... |
| Posted: Wed Nov 25, 2009 7:41 pm |
|
|
|
Guest
|
Mladen,
Could it be you are working with more than one DBMS vendor.
What you expect is the behavior in e.g. DB2 where USER <> SCHEMA.
Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Mon Nov 30, 2009 1:47 pm
|
|