30

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?

Improve this question
2
  • Voting for migration to ServerFault, because this is about administration of a database.
    – Bobby
    Commented 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
    Commented Jul 13, 2011 at 20:50

7 Answers 7

Reset to default
28

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.

Improve this answer
9

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.

Improve this answer
2
  • rtfg eh? hey I will search that....
    – orbfish
    Commented Mar 30, 2011 at 17:36
  • Just google it with Bing...
    – yossico
    Commented Mar 31, 2020 at 14:54
8 
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.

Improve this answer
8

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

Improve this answer
5

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.

Improve this answer
3 
SQL> create tablespace vania
2 datafile 'D:/home/oracle/hait/vania01.dbf'
3 size 10M
4 ;
Improve this answer
1
  • 1
    Could you explain on this a little further?
    – jcolebrand ♦
    Commented Sep 17, 2011 at 13:56
1 
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
/
Improve this answer