OraFAQ Forum: Server Administration » Object created in a different user

Home » RDBMS Server » Server Administration » Object created in a different user (10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Object created in a different user [message #465708]

Thu, 15 July 2010 06:44

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Whenever I create a table using login A, the table gets created successfully. But when I query the dba_objects, the object owner is different, login B. What might be the reason behind this?

Please help.

Regards,
Antony

Report message to a moderator

Re: Object created in a different user [message #465717 is a reply to message #465708]

Thu, 15 July 2010 06:57

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

may be if user A has the privilege to create a table in b user
so please dont say anything..
post your session details

sriram

Report message to a moderator

Re: Object created in a different user [message #465733 is a reply to message #465717]

Thu, 15 July 2010 08:15

cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester

Senior Member

Have you got a logon trigger that contains:

alter session set current_schema = <login B>

Report message to a moderator

Re: Object created in a different user [message #465734 is a reply to message #465708]

Thu, 15 July 2010 08:17

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Don't tell what you do, show us: copy and paste your SQl*Plus session.
Before, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

Report message to a moderator

Re: Object created in a different user [message #465741 is a reply to message #465734]

Thu, 15 July 2010 08:43

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Please find the requested SQL session. I had created the table as APP, but the owner is APP_ADMIN. I need to replicate this setup in the new database. Kindly help.

Note : Proxy configuration is not used.

 
M:\>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jul 15 19:10:52 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter user-name: app@SEDB
Enter password:

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> select username from user_users;

USERNAME
------------------------------
APP

SQL> create table test123 (name varchar2(10));

Table created.


SQL> select object_name,object_type,owner from dba_objects where object_name='TEST123';

OBJECT_NAME          OBJECT_TYPE         OWNER
-------------------- ------------------- ------------------------------
TEST123              TABLE               APP_ADMIN

Report message to a moderator

Re: Object created in a different user [message #465742 is a reply to message #465734]

Thu, 15 July 2010 08:44

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

1/ when login as A Query to user_tables and check.
2/ when login as B Query to user_tables and check.

if you find the table in 1/ A is ower else B

Report message to a moderator

Re: Object created in a different user [message #465749 is a reply to message #465742]

Thu, 15 July 2010 08:56

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Please find the query output

M:\>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jul 15 19:10:52 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter user-name: app@SEDB
Enter password:

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> select username from user_users;

USERNAME
------------------------------
APP

SQL> create table test123 (name varchar2(10));

Table created.


SQL> select object_name,object_type,owner from dba_objects where object_name='TEST123';

OBJECT_NAME          OBJECT_TYPE         OWNER
-------------------- ------------------- ------------------------------
TEST123              TABLE               APP_ADMIN

Report message to a moderator

Re: Object created in a different user [message #465751 is a reply to message #465749]

Thu, 15 July 2010 09:04

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select sys_context('userenv','CURRENT_SCHEMA') from dual;

Regards
Michel

Report message to a moderator

Re: Object created in a different user [message #465904 is a reply to message #465751]

Thu, 15 July 2010 23:49

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Thanks for your assistance.

We have found out the reason.

A logon trigger was there with 'ALTER SESSION SET CURRENT_SCHEMA=APP_ADMIN'.

Regards,
Antony

Report message to a moderator

Re: Object created in a different user [message #465905 is a reply to message #465733]

Thu, 15 July 2010 23:53

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

cookiemonster wrote on Thu, 15 July 2010 18:45

Have you got a logon trigger that contains:
alter session set current_schema = <login B>

Thats why we asking people to follow the instructions specified then reply with new messages.
congrats.OP & cookiemonster Smile

Report message to a moderator

Previous Topic:	How to create an ASM instance using dbca
Next Topic:	V$ACTIVE_SESSION_HISTORY not populating

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 12:33:16 CDT 2024