Results 1 to 8 of 8

Thread: Create schema

  1. #1
    Join Date
    Feb 2004
    Posts
    79

    Unanswered: Create schema

    How to create new schema under scott or sys....
    Thanks

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    A schema is associated with a particular user. If scott or sys already have a schema, you cannot create another one beneath it. A schema is just a conceptual structure used to collect objects owned by a particular user. You can have users in your database without a schema, as they work with objects contained within other users schemas.

    To check this out, create a new user:

    create user test identified by test;
    grant create table to test;

    Then go and look at your Schema browser. You won't see a schema for test. Then issue the statement:

    connect test/test@<instance_name>;
    create table test_table (myname varchar2(30));

    Now look back at your schema browser, and you'll see a schema for test with the test_table inside.

    -Chuck

  3. #3
    Join Date
    Feb 2004
    Posts
    79
    Thanks
    Will try it now

  4. #4
    Join Date
    Feb 2004
    Posts
    79

    instance_name

    instance_name- please let me know what it is...
    If my schema name is TOM, do I mention schema name in command above?

    how do I get instance name...
    Thanks!

  5. #5
    Join Date
    Feb 2004
    Posts
    79

    Grant create schema

    what is the command to grant create schema permission/priviledge to the user...
    Thanks

  6. #6
    Join Date
    Feb 2004
    Posts
    79

    schema creation error

    CREATE SCHEMA AUTHORIZATION schema STUDENTS
    *
    ERROR at line 1:
    ORA-02422: missing or invalid schema element

    My command was:
    CREATE SCHEMA AUTHORIZATION schema STUDENTS
    { GRANT create table to STUDENTS
    }

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Schema name will be the same as the username which owns the objects.

    A schema owner does not grant rights to the schema, but rather to objects within their schema.

    /* as a dba user, create the user which will own the test_table, and the user which will access that table. grant a right to the to_be_schema_owner which will allow them to create tables within the database instance */

    create user to_be_schema_owner identified by their_pwd;
    create user select_table_user identified by their_pwd;
    grant create table to test;

    /* connect as to_be_schema_owner and create a table, then grant the right to access the table to select_table_user */

    connect to_be_schema_owner /their_pwd@<instance_name>;
    create table test_table (myname varchar2(30));
    grant select on test_table to test_select;

    /* test that the select_table_user can select from test_table, prefixing the table name with the name of it's owning schema */
    connect select_table_user/their_pwd@<instance_name>;
    select * from test.test_table;

    -cf

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    And you don't create a schema with a command like:

    CREATE SCHEMA ...

    It is automatically created when a user creates an object using a statment like:

    CREATE TABLE ...

Posting Permissions

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