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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
6  
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

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.

share|improve this answer

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

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
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

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

protected by Mark Storey-Smith Jul 22 '14 at 13:24

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

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