Results 1 to 5 of 5

Thread: create schema

  1. #1
    Join Date
    Dec 2003
    Posts
    42

    Unanswered: create schema

    Hi

    anybody tell me how to create schemas
    what is the syntax?

  2. #2
    Join Date
    Dec 2003
    Posts
    74
    you create a schema by creating a user.

    e.g.

    create user edwin identified by password;

    there will then be an edwin schema created

  3. #3
    Join Date
    Aug 2003
    Posts
    123
    schema is nothing but user,
    the command is create user <username> identified by <password>
    but you need to do some thing more
    identify default tablespace and the temporary tablespace on which you need to create the user/schema.
    then after creating the user, you need to grant some of the min priviliege

    GRANT CREATE SESSION TO "ABC";
    GRANT UNLIMITED TABLESPACE TO "ABC";
    GRANT "CONNECT" TO "ABC";
    GRANT "RESOURCE" TO "ABC";

    further based on the requirement you need to give other privilege
    liek create table,create trigger etc.


    --Jaggu

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    CREATE SCHEMA AUTHORIZATION <Same as Username>
    CREATE TABLE <table_name>
    (col1 Datatype,
    col2 datatype)
    CREATE VIEW <view_name>
    AS SELECT <col1, col2..> FROM <tab_name> WHERE <condition>
    GRANT <something> ON <something>TO <someone>;

    For more Information,

    http://sqlzoo.napier.ac.uk/big/B1050...ments_614a.htm
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Dec 2003
    Location
    Kekaha, Kauai, Hawaii
    Posts
    22
    What do you mean by a schema. Yes, in Oracle a schema is associated with a user. Do you mean how do you get the SQL to recreate an existing schema - that is a more interesting question.

    Oracle 9i has the DBMS_METADATA package - the DBMS_METADATA.GET_DDL('TABLE', '<tablename>') will put out a LONG variable (do you have this set in SQL*Plus?) with the DDL to create a table.

    You can also do an Import with the INDEXFILE option that will generate a file with all your indexes and all your tables defined. The tables are commented out with ">" at the beginning of the line - you can easily use a test editor to remove these. When you use the INDEXFILE option with import it does not actually import any data.

Posting Permissions

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