Home » RDBMS Server » Server Administration » Create table (10.2.0.4.0)
Create table [message #548327] Wed, 21 March 2012 07:39 Go to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Hello Expert,

I got query to investigate, how table owner is changed. Please suggest.

SQL> conn test/test
Connected.
SQL> create table guri(name varchar2(9));

Table created.

SQL> select table_name from user_tables;

no rows selected

SQL> conn /as sysdba
Connected.
SQL> select owner,object_name,object_type from dba_objects where object_name = 'GURI';

OWNER OBJECT_NAME OBJECT_TYPE
---------- ----------- ------------
CISREPLICA GURI TABLE


[EDITED by LF: fixed topic title typo; was "craete"]

[Updated on: Wed, 21 March 2012 08:54] by Moderator

Report message to a moderator

Re: craete table [message #548329 is a reply to message #548327] Wed, 21 March 2012 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot "change" the owner, you can copy the table into another schema.

Regards
Michel
Re: craete table [message #548331 is a reply to message #548327] Wed, 21 March 2012 07:55 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Probably a logon trigger that sets your current_schema.

select username,schemaname from v$session;
Re: craete table [message #548360 is a reply to message #548329] Wed, 21 March 2012 09:46 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Thanks Michel,

Even i'm don't know how owner change from test to GURI ..

Thanks again.
Re: craete table [message #548361 is a reply to message #548360] Wed, 21 March 2012 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
export/import with schema remap or fromuser/touser option?

Regards
Michel
Re: craete table [message #548362 is a reply to message #548361] Wed, 21 March 2012 09:52 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Assuming you did everything exactly as typed in the first post then John's explanation is almost certainly correct.
Look for a logon trigger.
Re: craete table [message #548529 is a reply to message #548362] Thu, 22 March 2012 12:23 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Hello Michel,

It was happening because of DB_LOGON tirgger. We traced and disabled it. Now all seems to be fine.

Thanks for great help.

CREATE OR REPLACE TRIGGER "SYS"."DB_LOGON"
AFTER logon ON DATABASE  WHEN (USER = 'TEST') BEGIN
execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = GURI'; 
END;
ALTER TRIGGER "SYS"."DB_LOGON" ENABLE
Re: craete table [message #548530 is a reply to message #548529] Thu, 22 March 2012 12:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This idea comes from John not from me, anyway thanks for the feedback, it is appreciated.

Regards
Michel
Re: craete table [message #548552 is a reply to message #548530] Thu, 22 March 2012 17:58 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It also means the Test user has the CREATE ANY TABLE privilege. Which they really shouldn't have. No user should need that priv.
Previous Topic: Method to execute schema stats
Next Topic: db link issue
Goto Forum:
  


Current Time: Fri Mar 29 03:29:51 CDT 2024