Step 6 - Modifying a Tablespace
Learn Oracle - Modifying a Tablespace
With Enterprise Manager, Oracle DBA can modify a tablespace, such as extend it, set it to
automatically extend, change its space thresholds, or change its status to offline.
tablespace might need extending when Oracle DBA get a critical and warning alert. Oracle DBA might
need to offline a tablespace to perform recovery operations on it.
This section shows you how to set a tablespace to automatically extend when it
reaches its limit. To enable autoextend tablespace, do the following:
1. From the Administration page, click Tablespaces under Storage. This takes Oracle DBA to
the Tablespaces page.
2. Select a tablespace to autoextend, such as USERS, and click Edit. The Edit
Tablespace:USERS General property page appears.
3. Select the datafile associated with the tablespace, and click Edit. The Edit Datafile
4. Check Automatically extend datafile when full (AUTOEXTEND). Set a suitable
increment, such as 1 M. Oracle DBA can optionally set a maximum file size or set file size
to unlimited depending on your resources.
You can add datafiles and associate them with a tablespace with SQL statements.
You can either specify the file specifications for the datafiles being created, or Oracle DBA
can use the Oracle Managed Files feature to create files that are created and
managed by the database server.
You can also use SQL statements to manually resize a datafile. This lets you add
space to your database without adding datafiles, if you are concerned about
reaching the maximum number of datafiles allowed in your database. Manually
reducing the sizes of datafiles lets Oracle DBA reclaim unused space in the database. This
is useful for correcting errors in estimates of space requirements.
5. Click Continue. This brings you back to the Edit Tablespace page. Click Apply. A
confirmation message should appear.
Similarly, you can change tablespace thresholds by selecting a tablespace on the
Tablespaces page and navigating to the Threshold property page. Here you can alter
your space thresholds that generate the warning and critical alerts.
To offline a tablespace, on the Edit Tablespace page, under Status, select Offline and