Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Add Another Datafile to a Tablespace

By: FYIcenter.com

(Continued from previous topic...)

How To Add Another Datafile to a Tablespace?

If you created a tablespace with a data file a month ago, now 80% of the data file is used, you should add another data file to the tablespace. This can be done by using the ALTER TABLESPACE ... ADD DATAFILE statement. See the following sample script:

SQL> connect HR/fyicenter

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

SQL> ALTER TABLESPACE my_space 
  2  DATAFILE '/temp/my_space_2.dbf' SIZE 5M;
Tablespace altered.

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

SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES 
  2  FROM USER_FREE_SPACE 
  3  WHERE TABLESPAE_NAME IN ('MY_SPACE');
TABLESPACE_NAME                   FILE_ID      BYTES
------------------------------ ---------- ----------
MY_SPACE                                6    5177344
MY_SPACE                                5   10354688

This script created one tablespace with two data files.

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

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...