Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Create a New Tablespace

By: FYIcenter.com

(Continued from previous topic...)

How To Create a New Tablespace?

If you want a new dataspace, you can use the CREATE TABLESPACE ... DATAFILE statement as shown in the following script:

SQL> CREATE TABLESPACE my_space 
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
  2  FROM USER_TABLESPACES;
TABLESPACE_NAME  STATUS          CONTENTS
---------------- --------------- ---------
SYSTEM           ONLINE          PERMANENT
UNDO             ONLINE          UNDO
SYSAUX           ONLINE          PERMANENT
TEMP             ONLINE          TEMPORARY
USERS            ONLINE          PERMANENT
MY_SPACE         ONLINE          PERMANENT

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
  2  FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME                            BYTES
--------------- -------------------------------- ---------
USERS           \ORACLEXE\ORADATA\XE\USERS.DBF   104857600
SYSAUX          \ORACLEXE\ORADATA\XE\SYSAUX.DBF  461373440
UNDO            \ORACLEXE\ORADATA\XE\UNDO.DBF     94371840
SYSTEM          \ORACLEXE\ORADATA\XE\SYSTEM.DBF  356515840
MY_SPACE        \TEMP\MY_SPACE.DBF                10485760

So one statement created two structures: a tablespace and a data file. If you check your file system with Windows file explorer, you will see the data file is located in the \temp directory of. The data file size is about 10MB. Its contents should be blank and full of \x00 at this time.

(Continued on next topic...)

1. What Is an Oracle Tablespace?
2. What Is an Oracle Data File?
3. How a Tablespace Is Related to Data Files?
4. How a Database Is Related to Tablespaces?
5. How To View Tablespaces in the Current Database?
6. What Are the Predefined Tablespaces in a Database?
7. How To View Data Files in the Current Database?
8. How To Create a New Oracle Data File?
9. How To Create a New Tablespace?
10. How To Rename a Tablespace?
11. How To Drop a Tablespace?
12. What Happens to Data Files If a Tablespace Is Dropped?
13. How To Create a Table in a Specific Tablespace?
14. How To See Free Space of Each Tablespace?
15. How To Bring a Tablespace Offline?
16. How To Bring a Tablespace Online?
17. How To Add Another Datafile to a Tablespace?
18. What Happens If You Lost a Data File?
19. How Remove Data Files before Opening a Database?