Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from superuser.com Mar 30 '11 at 7:45

This question came from our site for computer enthusiasts and power users.

    
Voting for migration to ServerFault, because this is about administration of a database. –  Bobby Mar 30 '11 at 7:36
5  
I know it's a very very late answer, but what do you suppose that database administrators do? :-) –  Marian Jul 13 '11 at 20:50
add comment

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:

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://tahiti.oracle.com/. Specifically, I recommend reading their excellent introduction to the database called the Concepts Guide.

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
    
rtfg eh? hey I will search that.... –  orbfish Mar 30 '11 at 17:36
add comment

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.

share|improve this answer
add comment
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.

share|improve this answer
add comment
SQL> create tablespace vania
2 datafile 'D:/home/oracle/hait/vania01.dbf'
3 size 10M
4 ;
share|improve this answer
    
Could you explain on this a little further? –  jcolebrand Sep 17 '11 at 13:56
add comment
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
/
share|improve this answer
add comment

protected by Mark Storey-Smith yesterday

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?