Managing Tablespaces   «Prev  Next»
Lesson 4 Adding space
Objective Allocate more space to a tablespace.

Add space to Oracle tablespace

In due course, you will need to add more space to a tablespace to accommodate the growth of objects within that tablespace. One way to add space to a tablespace is to add a data file. Another is to increase the size of an existing file. Adding data files allows you to spread I/O around to other disks. Increasing the size of an existing file may not work if there is no more room on the disk containing the file. Use the approach that you feel will be best in your situation.

Adding file to tablespace

You can add a file to a tablespace using the ALTER TABLESPACE command. Here's an example: 
ALTER TABLESPACE COIN
ADD DATAFILE 'E:\ORADATA\COIN\COIN02.DAT' 

SIZE 500M;

This example adds a 500 megabyte file to the tablespace. You can add as many data files to a tablespace as you like, and you can spread I/O around by placing the files on different disks.

Increasing size of Data file

Another way to add space to a tablespace is to increase the size of one of the existing data files. As long as there is enough space on the disk, you can do this on most platforms. First determine the name of the file that you want to resize; do this by querying the dba_data_files view. Then resize the file using an ALTER DATABASE command, like this: 
ALTER DATABASE

DATAFILE 'E:\ORADATA\COIN\COIN01.DAT' RESIZE 1000M;

The RESIZE clause specifies the new size. Oracle will attempt to shrink or grow the file to match the new size.

Storage Manager

Storage Manager can be used to create and manage tablespaces. The following simulation walks you through the process of using it to add a data file to the COIN tablespace:

Connect to the coin - system
1) Connect to the coin - system
Display the Tablespaces, Datafiles, Rollback Segments, Redo Log Groups
2) Display the Tablespaces, Datafiles, Rollback Segments, Redo Log Groups
Select Tablespaces beneath the coin - system
3) Select Tablespaces beneath the coin - system
In the context of Oracle tablespaces, RBS stands for Rollback Segment. It is a dedicated storage area within an Oracle database that is used to temporarily store undo information for data manipulation language (DML) operations, such as INSERT, UPDATE, and DELETE statements
4) In the context of Oracle tablespaces, RBS stands for Rollback Segment. It is a dedicated storage area within an Oracle database that is used to temporarily store undo information for data manipulation language (DML) operations, such as INSERT, UPDATE, and DELETE statements
Select create DataFile
5) Select create DataFile
Select --> General --> Tablespace: COIN --> Status: Online
6) Select --> General --> Tablespace: COIN --> Status: Online
Select General Online | Select Name: E:\ORACLE\ORADATA\COIN\COIN02.DAT
7) Select General Online | Select Name: E:\ORACLE\ORADATA\COIN\COIN02.DAT
Oracle Storage Manager SYSTEM : Datafile created successfully
8) Oracle Storage Manager SYSTEM : Datafile created successfully
  1. This is the opening screen that you will see after starting Storage Manager. Click on the plus sign (+) by the COIN database icon to expand the list of items that you can manage using Storage Manager.
  2. ClickCoinPlusSign,
  3. Click on the Tablespaces icon to see a list of tablespaces in the right pane.
  4. ClickTablespaces,
  1. Notice that the tablespace listing includes the status (online or offline), the size of the tablespace, and the amount of space actually used. Now click the plus sign (+) by the Tablespaces folder to expand the list of tablespaces in the left pane.
  2. Click Tablespaces PlusSign
  3. One of the tablespaces listed is named COIN. Click on COIN, and you will see information about that tablespace appear in the right pane.
  4. ClickCoin
  5. error
  6. Try clicking COIN under Tablespaces on the left hand menu.
  1. Select Add Datafile from the pop-up menu.
  2. Select AddDatafile
  3. You will be presented with this Create Datafile dialog. Type E:\ORACLE\ORADATA\COIN\COIN02.DAT in the Name field, and then click Create to add the new data file.
  4. ClickCreate
  5. Name field
  6. E:\ORACLE\ORADATA\COIN\COIN02.DAT
  7. Enter E:\ORACLE\ORADATA\COIN\COIN02.DAT in the Name box.
  8. Storage Manager will display this dialog telling you that the new file was successfully created and added to the tablespace. This is the end of the simulation. Click the Exit button.

Adding data files with Storage Manager

  1. This is the opening screen that you will see after starting Storage Manager. Click on the plus sign (+) by the COIN database icon to expand the list of items that you can manage using Storage Manager.
  2. Click on the Tablespaces icon to see a list of tablespaces in the right pane.
  3. Notice that the tablespace listing includes the status (online or offline), the size of the tablespace, and the amount of space actually used. Now click the plus sign (+) by the Tablespaces folder to expand the list of tablespaces in the left pane.
  4. One of the tablespaces listed is named COIN. Click on COIN, and you will see information about that tablespace appear in the right pane.
  5. Select Add Datafile from the pop-up menu.
  6. You will be presented with this Create Datafile dialog. Type 
    E:\ORACLE\ORADATA\COIN\COIN02.DAT

    in the Name field, and then click Create to add the new data file.
  7. Storage Manager will display this dialog telling you that the new file was successfully created and added to the tablespace.
In the next lesson, you will learn how to automate the increase of data files.

Adding Data Files - Exercise

Click the Exercise link below to practice adding data files to a database.
Adding Data Files - Exercise