Results 1 to 7 of 7

Thread: Create Schema

  1. #1
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Unanswered: Create Schema

    How can I create a schema WITHOUT having to create the user?

    I know I can approximate the above with a user account and not give it 'create session' privilege but that's not what I want to do.

    I really want to create schema ABC without having to create user ABC.

    Is there a way to do that? I'll have to do this on Oracle 8i and 9i.

    Thanks in advance!

  2. #2
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204

    Re: Create Schema

    There is no difference between a schema and a user.

    I am really interested to hear why you want a schema without a user? How do objects get created? What is the schema used for?

    HTH,
    Patrick

    Originally posted by bstjean
    How can I create a schema WITHOUT having to create the user?

    I know I can approximate the above with a user account and not give it 'create session' privilege but that's not what I want to do.

    I really want to create schema ABC without having to create user ABC.

    Is there a way to do that? I'll have to do this on Oracle 8i and 9i.

    Thanks in advance!

  3. #3
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Create Schema

    Originally posted by buckeye234
    There is no difference between a schema and a user.

    I am really interested to hear why you want a schema without a user? How do objects get created? What is the schema used for?

    HTH,
    Patrick
    Well, there is one. A schema is just a way of regrouping some database objects while a user is... a user.

    I can easily create schemas on DB2... I seem to need to have a user on Oracle.

    For instance, let's say I need to have a *production* table and a *development* table in the exact same database, I could have schema1.tablename and schema2.tablename coexisting in the same database on DB2, even if schema1 and schema2 aren't users.

    I would need to use different schemas for multiple reasons, for instance trying to reproduce a bug or performance problem that happens only in a production environment. I wouldn't want to create another database on another server but instead create a replica of the problematic table under a different schema.

    So back to my orginal post, is that possible?

  4. #4
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Smile

    The only one to do that in Oracle is to create another user to hold the development objects.

    Objects belong to user - NO USER, NO FANTOM OBJECTS . That's how it works in Oracle.

    Another way of doing it (much better for your job safety !!!) is to create another instance on the same box and export/import the production schema & data into this "development" db. Whatever testing or "mistake" take place - the production db will be safe... and running !!



    Hope that helps,

    clio_usa - OCP - DBA


  5. #5
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204

    Re: Create Schema

    I should have qualified my statement. In Oracle, there is no difference between a schema and a user. You can create the user (or schema if you prefer) and then disable the user. To create objects owned by the disabled user, you will need to have permissions to create objects in another schema, but it can be done.

    As for your example, I agree with CLIO_USA's response. It's interesting to see how things are different between DB2 and Oracle.

    Patrick

    Originally posted by bstjean
    Well, there is one. A schema is just a way of regrouping some database objects while a user is... a user.

    I can easily create schemas on DB2... I seem to need to have a user on Oracle.

    For instance, let's say I need to have a *production* table and a *development* table in the exact same database, I could have schema1.tablename and schema2.tablename coexisting in the same database on DB2, even if schema1 and schema2 aren't users.

    I would need to use different schemas for multiple reasons, for instance trying to reproduce a bug or performance problem that happens only in a production environment. I wouldn't want to create another database on another server but instead create a replica of the problematic table under a different schema.

    So back to my orginal post, is that possible?

  6. #6
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by clio_usa
    The only one to do that in Oracle is to create another user to hold the development objects.

    Objects belong to user - NO USER, NO FANTOM OBJECTS . That's how it works in Oracle.

    Another way of doing it (much better for your job safety !!!) is to create another instance on the same box and export/import the production schema & data into this "development" db. Whatever testing or "mistake" take place - the production db will be safe... and running !!
    I know... Playing with production stuff is a No-No! My example was probably bad. I was just stunned since I'm used to DB/2 way of doing things... And don't get me started on Oracle' weak points... ;-)

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by bstjean
    And don't get me started on Oracle' weak points... ;-)
    No, please share them with us! I'd like to know for one.

Posting Permissions

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