How do I Create Tablespace in Oracle 11g?

  • I am not too savvy in Oracle, and I have been trying to create a TABLESPACE, which keep on giving me an error message of invalid file name. Below is the syntax that I have used:

    SQL> create Tablespace HRMT
    2 datafile
    3 size
    4 ;
    size
    *
    ERROR at line 3:
    ORA-02236: invalid file name
    

    My book and all websites that I have been to suggest that I specify the file path for the DATAFILE, and when I specify a path, I get another error message.

    SQL> create tablespace vania
    2 datafile 'home/oracle/hait/'
    3 size 10M
    4 ;
    create tablespace vania
    *
    ERROR at line 1:
    ORA-01119: error in creating database file 'home/oracle/hait/'
    ORA-27040: file create error, unable to create file
    Linux Error: 2: No such file or directory
    

    Do I need to create the datafile first before I can create the TABLESPACE? Are there pre-existing datafiles in the database that I can use? What would be the proper syntax to create a tablespace and/or a datafile? How and where do I create a .dbf file to create the tablespace?

    Voting for migration to ServerFault, because this is about administration of a database.

    I know it's a very very late answer, but what do you suppose that database administrators do? :-)

  • The answer to your question can be found in the SQL Language Reference (excerpt follows).

    Creating Basic Tablespaces: Examples

    This statement creates a tablespace named tbs_01 with one data file:

    CREATE TABLESPACE tbs_01 DATAFILE 'tbs_f2.dbf' SIZE 40M ONLINE;

    It sound like you are new to Oracle databases. Oracle provides a wealth of documentation at http://docs.oracle.com/en/database. Specifically, I recommend reading their excellent introduction to the database called the Concepts Guide.

  • Obviously, if you want a datafile, you have to give it a name:

    SQL> create tablespace vania
    2 datafile '/home/oracle/hait/vania01.dbf'
    3 size 10M
    4 ;
    

    There are loads of examples of this if you do a very simple search.

    rtfg eh? hey I will search that....

    Just google it with Bing...

  • For this kind of very basic questions like "How to create a tablespace in Oracle?" I would strongly recommend the very good Online Documentation.

    In the particular case, you get from there to Oracle® Database Administrator's Guide 11g Release 2 (11.2) - Creating Tablespaces

  • CREATE TABLESPACE <tablespace_name>
      DATAFILE 'datafile_name.dbf'
      SIZE 20M AUTOEXTEND ON;
    

    where 20M is size of your datafile. AutoExtend On => size is automatically extended as the datafile is filled.

  • Be sure that your datafile folder exists. On UNIX do the following:

    ls /home/oracle/hait  #if you get an error here, it means you need to create the folder first
    
    mkdir -p /home/oracle/hait
    

    Finally, if you are in Oracle 11g where tablespace management is purely local, you can do the following:

    create tablespace vania datafile '/home/oracle/hait/vania01.dbf' size 10M;
    

    This will not work in Oracle 7 or 8i; you have to clearly specify how your extents are going to be managed.

  • SQL> create tablespace vania
    2 datafile 'D:/home/oracle/hait/vania01.dbf'
    3 size 10M
    4 ;
    

    Could you explain on this a little further?

  • create tablespace MY_TB
    datafile 'D:\app\Administrator\oradata\MYORA\MY_TB.dbf'
    uniform size 33K
    max size unlimited
    extend management local
    segmentspace management manual
    auto extend on next 40K
    disable logging
    /
    

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM