Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

    Unanswered: are objects owned by schemas or users in oracle?

    Forgive the simple question, I am very new when it comes to oracle. I am coming from the world of sql server.

    My question is, what sort of object (schema or user) can be the owner of another object? Is the owner of a schema-scoped object (such as a table) always the schema that the object lives in? Or are objects owned by users?

    For example, in the sys.all_tables data dictionary view, does the OWNER column contain a schema name, or a user name? The oracle docs I found aren't clear on this - it says the column contains "The owner of the table" and not whether it's a user or a schema - http://download-west.oracle.com/docs...htm#sthref1732

    My feeling is that the owner must be a schema, but then I found the thread below and was confused by this statment: "A schema is the set of objects owned by a particular user" (Justin Cave) which seems to indicate objects are owned by users, not schemas:
    http://forums.oracle.com/forums/thre...ageID=1497278&

    Follow up: if indeed sys.all_tables.owner is a user and not a schema, then how would I find all the tables within a particular schema using the data dictionary views?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    USER owns objects. SCHEMA = user + its objects. In Oracle, "user name" = "schema name".

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    ok, then what about my followup question? that is, is it possible for a user A to own objects in schema B? I hope not, because then how would one determine what tables are in schema B from the data dictionary views such as sys.all_tables? (since only an OWNER column is provided there, which is a USER), and no SCHEMA column.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, user A can not own objects in schema B.

    I thought my previous post was clear enough.
    USER NAME = SCHEMA NAME.
    all_tables.owner = user (= schema).

    So, if you
    Code:
    SQL> select table_name from all_tables where owner = 'SCOTT';
    
    TABLE_NAME
    ------------------------------
    DUMMY
    SALGRADE
    BONUS
    EMP
    DEPT
    TEST
    PLAN_TABLE
    TOAD_PLAN_TABLE
    
    8 rows selected.
    
    SQL>
    you'll get all tables owned by user 'SCOTT' (and which are in Scott's schema).

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    k, got it now. thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •