1 2 Previous Next 18 Replies Latest reply: Mar 22, 2010 8:55 AM by 472720 RSS

    How to create a new schema ?

    472720
      Hi,

      Can someone tell me how to create a new schema please ?
      What i need as user & rights ?

      Thanks
        • 1. Re: How to create a new schema ?
          sb92075
          Can someone tell me how to create a new schema please ?
          CREATE USER
          What i need as user & rights ?
          CREATE USER

          when all else fails Read The Fine Manual

          http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8003.htm#i2065278
          • 2. Re: How to create a new schema ?
            Hoek
            Assuming you're on 10g:
            Did you check the Oracle Documentation @ http://www.oracle.com/pls/db102/homepage ?
            create user: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8003.htm#i2065278
            create schema: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6014.htm#SQLRF01313
            • 3. Re: How to create a new schema ?
              ASM2
              Can someone tell me how to create a new schema please ?
              You create a database user with the CREATE USER statement.To create a user, you must have the CREATE USER system privilege. Because it is a powerful privilege, a DBA or security administrator is normally the only user who has the CREATE USER system privilege.

              What i need as user & rights ?
              CREATE USER jward
              IDENTIFIED BY AZ7BC2
              DEFAULT TABLESPACE data_ts
              QUOTA 100M ON test_ts
              QUOTA 500K ON data_ts
              TEMPORARY TABLESPACE temp_ts
              PROFILE clerk;
              GRANT create session TO jward;

              A newly created user cannot connect to the database until granted the CREATE SESSION system privilege.
              • 4. Re: How to create a new schema ?
                472720
                thank you for your help.
                Can you give some more informations because i 've not dealed with Oracle since 2-3 years..
                I'm not sure understanding the procedure step by step

                To create the user
                -if i don't have neither sys nor system access, can i create the user "jward" with the oracle user ?

                To create the schema
                - once the new user created and granted,do i have to run the create schema statement under sqlplus ?

                thks
                • 5. Re: How to create a new schema ?
                  Lubiez Jean-Valentin
                  Hello,
                  -if i don't have neither sys nor system access, can i create the user "jward" with the oracle user ?
                  You need to be connected to the database to a User who already has the CREATE USER Privilege.
                  - once the new user created and granted,do i have to run the create schema statement under sqlplus ?
                  It's not necessary.

                  A Schema is a collection of Objects (Tables, Index, ...) so you can create them with their own CREATE
                  Statements.

                  But, you may include these statements into a CREATE SCHEMA statement so that if any error occurs
                  all the statements are rollbacked.

                  Please, find enclosed some links about this:

                  http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/admusers.htm

                  http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_6013.htm


                  Hope this help.
                  Best regards,
                  Jean-Valentin
                  • 6. Re: How to create a new schema ?
                    472720
                    Ok so the Oracle Database automatically creates a schema with the same name when i create a user.

                    So i just have to run the create tables script with the newly created user under sqlplus and then access to the tables with "user".tables , right ?

                    thks & best regards
                    • 7. Re: How to create a new schema ?
                      Lubiez Jean-Valentin
                      Hello,


                      User/Schema have the same name and, this User will be the Owner of the Tables, ... created by your script.


                      Best regards,
                      Jean-Valentin
                      • 8. Re: How to create a new schema ?
                        EdStevens
                        user469717 wrote:
                        Ok so the Oracle Database automatically creates a schema with the same name when i create a user.

                        So i just have to run the create tables script with the newly created user under sqlplus and then access to the tables with "user".tables , right ?

                        thks & best regards
                        You seem to think that the "schema" is some sort of object in and of itself. It is not. A schema is simply the collection of objects belonging to (owned by) a single user. The CREATE SCHEMA command just gives a short-cut (and transactionally consistent) means of creating both the owning user and the owned objects in one sql statement. The end result of "CREATE SCHEMA ...." and "CREATE USER ... CREATE TABLE ..." is exactly the same and once completed, there is no distinction between the two.
                        • 9. Re: How to create a new schema ?
                          472720
                          thanks to eveybody.

                          Now i think that i finally understand the mistake...

                          And how about the tablespace ? I can create many schemas in the same tablespace, right ?
                          • 10. Re: How to create a new schema ?
                            ASM2
                            And how about the tablespace ? I can create many schemas in the same tablespace, right ?
                            Yes you can create many schemas in the same tablespace.
                            • 11. Re: How to create a new schema ?
                              Fahd.Mirza
                              Tablespace is again a logical concept. Logically its the combination of one or more segments. Physically it consists of one or more datafiles

                              Every user has a default tablespace where he can create his schema objects.

                              User may also create his schema objects in other tablespaces if he has got quota assigned on those tablespaces.

                              regards
                              • 12. Re: How to create a new schema ?
                                472720
                                Ok because i've always done things by default with Oracle, i've never had to deal with difference between theses logical & physical, vitual & concrete objects !;)

                                Thank you very much for your help and kindness.
                                • 13. Re: How to create a new schema ?
                                  EdStevens
                                  userSA wrote:
                                  And how about the tablespace ? I can create many schemas in the same tablespace, right ?
                                  Yes you can create many schemas in the same tablespace.
                                  "Schemas" are not assigned to a tablespace. A user has a default tablespace, but if that user has quota on other tablespaces he may create objects in those as well. So while a user has a default tablespace, he may actually have objects in multiple tablespaces. That does not mean it is either a good or bad idea to do so, only to point out that there is no inherent relationship between a schema and a TS.
                                  • 14. Re: How to create a new schema ?
                                    472720
                                    When i tried to run the script to create the user under sqlplus connected with sqlplus / as sysdba

                                    i got this error

                                    ERROR at line 1:
                                    ORA-01034: ORACLE not available


                                    Is the database not startup or there's other reason ?
                                    1 2 Previous Next