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 ;
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.
    – Bobby
    Mar 30, 2011 at 7:36
  • 7
    I know it's a very very late answer, but what do you suppose that database administrators do? :-)
    – Marian
    Jul 13, 2011 at 20:50

7 Answers 7


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:


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....
    – orbfish
    Mar 30, 2011 at 17:36
  • Just google it with Bing...
    – yossico
    Mar 31, 2020 at 14:54
CREATE TABLESPACE <tablespace_name>
  DATAFILE 'datafile_name.dbf'

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


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


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 ;
  • 1
    Could you explain on this a little further?
    – jcolebrand
    Sep 17, 2011 at 13:56
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