6 Replies Latest reply on Mar 3, 2010 4:15 PM by EdStevens

    Create tablespace in Oracle 10g Express

    751317
      Hi,

      I've been playing around over the last few days with Oracle 10g Express and I need to create my own tablespace and therefore create user associated with my custom table space.

      I tried with the command create tablespace, but I am not successful with configuring the parameters such as the datafile location and such. I did go through the Oracle Concepts Guide which was very helpful, but I must be missing something.

      Also assuming that my db scheme will be part of my application and therefore released to my client as script so they can re-create my database how would the table space integration work on their end.

      I would I have to place my first line of the script to create the table space and ask them for database file location?

      Any advice will be appreciated.

      Thank you in advance.
        • 1. Re: Create tablespace in Oracle 10g Express
          Robert Geier
          "I tried with the command create tablespace, but I am not successful"

          What command did you run, what error did you get ?
          • 2. Re: Create tablespace in Oracle 10g Express
            sb92075
            I need to create my own tablespace
            I seriously doubt this is true.
            Please elaborate why you think this is necessary & exactly what you hope it will accomplish.
            • 3. Re: Create tablespace in Oracle 10g Express
              751317
              The reason why I think I should create my own tablespace is because I am creating a database scheme and thus I think it is appropriate to place my db scheme in its own tablespace instead of simply using the Users tablespace default.


              This is my command:

              create tablespace MYTBLSPACE;

              Error:


              Error starting at line 1 in command:
              create tablespace MYTBLSPACE
              Error at Command Line:1 Column:32
              Error report:
              SQL Error: ORA-02199: missing DATAFILE/TEMPFILE clause
              02199. 00000 - "missing DATAFILE/TEMPFILE clause"
              *Cause:    A CREATE TABLESPACE statement has no DATAFILE/TEMPFILE clause.
              *Action:   Specify a DATAFILE/TEMPFILE clause.

              I am also not sure how to specify the datafile.

              Thank you.
              • 4. Re: Create tablespace in Oracle 10g Express
                Robert Geier
                show parameter db_create_file_dest

                select name from v$datafile;
                • 5. Re: Create tablespace in Oracle 10g Express
                  sb92075
                  The reason why I think I should create my own tablespace is because I am creating a database scheme and thus I think it is appropriate to place my db scheme in its own tablespace instead of simply using the Users tablespace default.
                  You are free to do so, but it really gains you nothing.
                  Tablespace is really transparent to any application.
                  It is just a logical repository.
                  • 6. Re: Create tablespace in Oracle 10g Express
                    EdStevens
                    user12359577 wrote:
                    The reason why I think I should create my own tablespace is because I am creating a database scheme and thus I think it is appropriate to place my db scheme in its own tablespace instead of simply using the Users tablespace default.


                    This is my command:

                    create tablespace MYTBLSPACE;

                    Error:


                    Error starting at line 1 in command:
                    create tablespace MYTBLSPACE
                    Error at Command Line:1 Column:32
                    Error report:
                    SQL Error: ORA-02199: missing DATAFILE/TEMPFILE clause
                    02199. 00000 - "missing DATAFILE/TEMPFILE clause"
                    *Cause:    A CREATE TABLESPACE statement has no DATAFILE/TEMPFILE clause.
                    *Action:   Specify a DATAFILE/TEMPFILE clause.

                    I am also not sure how to specify the datafile.

                    Thank you.
                    How about checking with the fine SQL Reference manual for the syntax of the CREATE TABLESPACE command?

                    Learning where to look things up in the documentation is time well spent investing in your career. To that end, you should drop everything else you are doing and do the following:

                    Go to tahiti.oracle.com. Drill down to your product and version.

                    Spend a few minutes just getting familiar with what is available here. Take special note of the "books" and "search" tabs. Under the "books" tab you will find the complete documentation library.

                    Spend a few minutes just getting familiar with what kind of documentation is available there by simply browsing the titles under the "Books" tab.

                    Open the Reference Manual and spend a few minutes looking through the table of contents to get familiar with what kind of information is available there.

                    Do the same with the SQL Reference Manual.

                    Do the same with the Utilities manual.

                    You don't have to read the above in depth. They are reference manuals. Just get familiar with what is there to be referenced. Ninety percent of the questions asked on this forum can be answered in less than 5 minutes by simply searching one of the above manuals.

                    Then set yourself a plan to dig deeper.
                    - Read a chapter a day from the Concepts Manual.
                    - Take a look in your alert log. One of the first things listed at startup is the initialization parms with non-default values. Read up on each one of them in the Reference Manual.
                    - Take a look at your listener.ora, tnsnames.ora, and sqlnet.ora files. Go to the Network Administrators manual and read up on everything you see in those files.
                    - When you have finished reading the Concepts Manual, do it again.

                    Give a man a fish and he eats for a day. Teach a man to fish and he eats for a lifetime.

                    Edited by: EdStevens on Mar 3, 2010 10:13 AM

                    Edited by: EdStevens on Mar 3, 2010 10:15 AM
                    1 person found this helpful