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