Can
owner of any table be changed automatically.. if you say no, here is an
example.
SQL>
select * from v$version;
BANNER
------------------------
Oracle
Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL
Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS
for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL
Version 11.2.0.1.0 – Production
Login
through SCOTT schema and created a table but somehow table not created in
SCOTT. Let see how.
SQL>
conn scott/tiger
Connected.
SQL>
show user
USER
is "SCOTT"
SQL>
create table magic(name varchar2(9));
Table
created.
SQL>
select table_name from user_tables where table_name='MAGIC';
no
rows selected
So
where does table goes, let check. And We found that, somehow it is created in
other schema i.e. JAY..
SQL>
conn / as sysdba
Connected.
SQL>
col owner format A25
SQL>
col object_type format A25
SQL>
col object_name format A25
SQL>
select owner,object_name,object_type from dba_objects where object_name =
'MAGIC';
OWNER OBJECT_NAME OBJECT_TYPE
---------- -------- ------------
JAY MAGIC TABLE
How..
let see that..
We
have created a trigger for DB_LOGON that was playing this game. Let see its
DDL.
SQL>
Set pages 999;
SQL>
set long 90000
SQL>
select DBMS_METADATA.get_ddl('TRIGGER','DB_LOGON','SYS') txt from dual;
TXT
---------------------
CREATE OR REPLACE TRIGGER
"SYS"."DB_LOGON"
AFTER
logon ON DATABASE WHEN (USER = 'SCOTT')
BEGIN
execute
immediate 'ALTER SESSION SET CURRENT_SCHEMA = JAY';
END;
ALTER
TRIGGER "SYS"."DB_LOGON" ENABLE
Also
when we did login to SCOTT schema and check from v$session, its gives something
new out.
SQL>
conn scott/tiger
Connected.
SQL>
show user
USER
is "SCOTT"
SQL>
select username,schemaname from v$session where username='SCOTT';
USERNAME SCHEMANAME
---------- ----------
SCOTT JAY
SQL>
Alter trigger db_logon disable;
Trigger
altered.
SQL>
conn scott/tiger
Connected.
SQL>
show user
USER
is "SCOTT"
SQL>
select username,schemaname from v$session where username='SCOTT';
USERNAME SCHEMANAME
------------ ----------
SCOTT SCOTT
SQL>
create table magic1(name varchar2(9));
Table
created.
SQL>
select table_name from user_tables where table_name='MAGIC1';
TABLE_NAME
------------
MAGIC1
No comments:
Post a Comment