Home » SQL & PL/SQL » SQL & PL/SQL » Schema Query
Schema Query [message #223966] Mon, 12 March 2007 07:52 Go to next message
Messages: 68
Registered: August 2005
Location: delhi
I just want one clarification.........

Can a schema have objects from multiple databases??????

Can a database object like table or index be owned by multiple schema's or multiple user's????

Re: Schema Query [message #223969 is a reply to message #223966] Mon, 12 March 2007 07:59 Go to previous messageGo to next message
Messages: 4623
Registered: February 2005
Location: East Coast USA
Senior Member
No, from what I am reading from your question. But I guess it depends on how one interprets it. You can have a view, synonym or materialized view which could point to another schema or database.
Re: Schema Query [message #224126 is a reply to message #223969] Tue, 13 March 2007 03:26 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
An individual database object can only have one owner.
However, a table, it's indices and triggers can all be owned by different schemas:
SQL> create user table_owner identified by table_owner;
User created.

SQL> create user index_owner identified by index_owner;
User created.

SQL> create user trigger_owner identified by trigger_owner;
User created.

SQL> grant dba to table_owner;
Grant succeeded.

SQL> grant dba to index_owner;
Grant succeeded.

SQL> grant dba to trigger_owner;
Grant succeeded.

SQL> conn table_owner/table_owner@fps_oratest

SQL> create table owner_test (col_1  number);
Table created.

SQL> conn index_owner/index_owner@fps_Oratest

SQL> create index owner_test_idx on table_owner.owner_Test(col_1);
Index created.

SQL> conn trigger_owner/trigger_owner@fps_oratest

SQL> create or replace trigger owner_test_trg
  2  after insert on table_owner.owner_Test
  3  begin
  4    null;
  5  end;
  6  /

Trigger created.

SQL> select object_name,object_type,owner
  2  from   all_objects
  3  where  object_name like 'OWNER_TEST%'
SQL> /

OBJECT_NAME                    OBJECT_TYPE        OWNER
------------------------------ ------------------ ------------------------------
OWNER_TEST                     TABLE              TABLE_OWNER
OWNER_TEST_IDX                 INDEX              INDEX_OWNER
OWNER_TEST_TRG                 TRIGGER            TRIGGER_OWNER
Previous Topic: can not insert into table
Next Topic: Coursor
Goto Forum:

Current Time: Wed Oct 26 22:19:24 CDT 2016

Total time taken to generate the page: 0.08700 seconds