Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Managing Oracle Tablespaces and Data Files

By: FYIcenter.com

Part:   1  2   3  4 

(Continued from previous part...)

How To Create a new Oracle Data File?

There is no dedicated statement to create a data file. Data files are created as part of statements that manages other data structures, like tablespace and database.

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.

How To Rename a Tablespace?

You can easily rename a tablespace by using the ALTER TABLESPACE ... RENAME TO statement as shown in the example below:

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

SQL> ALTER TABLESPACE my_space RENAME TO your_space;
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
YOUR_SPACE       ONLINE          PERMANENT

How To Drop a Tablespace?

If you have an existing tablespace and you don't want it anymore. You can delete a tablespace by using the DROP TABLESPACE statement as shown in the example below:

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

SQL> DROP TABLESPACE my_space;
Tablespace dropped.

What Happens to the Data Files If a Tablespace Is Dropped?

If a tablespace is dropped, what happens to its data files? By default, data files will remain in OS file system, even if the tablespace they are mapped is dropped. Of course, you delete the data files using OS commands, if they are no longer needed.

Another way of deleting data files is to use the INCLUDING clause in the DROP TABLESPACE statement. Here is a SQL sample script:

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

SQL> DROP TABLESPACE my_space INCLUDING CONTENTS
  2  AND DATAFILES;
Tablespace dropped.

With the INCLUDING CONTENTS AND DATAFILES clause, all contents and mapped data files are also deleted.

(Continued on next part...)

Part:   1  2   3  4