Dropping a tablespace using INCLUDING CONTENTS AND DATAFILES permanently removes the tablespace and any of its datafiles. Make certain the tablespace doesn’t contain any data you want to keep before you drop it.
If you attempt to drop a tablespace that contains a primary key that is referenced by a foreign key associated with a table in a different tablespace (than the one you’re trying to drop), you receive this error:
ORA-02449: unique/primary keys in table referenced by foreign keys
78
CHAPTER 4 ■ TABLESPACES AND DATAFILES
Run this query first to determine whether any foreign-key constraints will be affected: select p.owner,
p.table_name,
p.constraint_name,
f.table_name referencing_table,
f.constraint_name foreign_key_name,
f.status fk_status
from dba_constraints P,
dba_constraints F,
dba_tables T
where p.constraint_name = f.r_constraint_name
and f.constraint_type = 'R'
and p.table_name = t.table_name
and t.tablespace_name = UPPER('&tablespace_name')
order by 1,2,3,4,5;
If there are referenced constraints, you need to first drop the constraints or use the CASCADE
CONSTRAINTS clause of the DROP TABLESPACE statement. This statement uses CASCADE CONSTRAINTS to automatically drop any affected constraints:
SQL> drop tablespace inv_data including contents and datafiles cascade constraints; This statement drops any referential-integrity constraints from tables outside the tablespace being dropped that reference tables within the dropped tablespace.
If you drop a tablespace that has required objects in a production system, the results can be catastrophic. You must perform some sort of recovery to get the tablespace and its objects back.
Needless to say, be very careful when dropping a tablespace. Table 4–2 lists recommendations to consider when you do this.
Table 4–2.
Best Practices for Dropping Tablespaces
Best Practice
Reasoning
Before dropping a tablespace, run a script similar to
Doing this ensures that no tables or indexes
this to determine if any objects exist in the tablespace:
exist in the tablespace before you drop it.
select owner, segment_name, segment_type
from dba_segments
where tablespace_name=upper('&&tbsp_name');
Consider renaming tables in a tablespace before you
If any applications are using tables within the
drop the tablespace.
tablespace to be dropped, the application
throws an error when a required table is
renamed.
If there are no objects in the tablespace, resize the
Reducing the size of the datafiles to a
associated datafiles to a very small number like 10MB.
miniscule amount of space quickly shows
whether any applications are trying to access
objects that require space in a tablespace.
Make a backup of your database before dropping a
This ensures that you have a way to recover
tablespace.
objects that are discovered to be in use after
you drop the tablespace.
79
CHAPTER 4 ■ TABLESPACES AND DATAFILES
Best Practice
Reasoning
Take the tablespace and datafiles offline before you
This helps determine if any applications or
drop the tablespace. Use the ALTER TABLESPACE
users are using objects in the tablespaces.
statement to take the tablespace offline.
They can’t access the objects if the tablespace
and datafiles are offline.
When you’re sure a tablespace isn't in use, use the DROP This removes the tablespace and physically TABLESPACE ... INCLUDING CONTENTS AND DATAFILES
removes any datafiles associated with the
statement.
tablespace. Some DBAs don’t like this
approach, but you should be fine if you’ve
taken the necessary precautions.
Using Oracle Managed Files
The Oracle Managed File (OMF) feature automates many aspects of tablespace management, such as file placement, naming, and sizing. You control OMF by setting the following initialization parameters:
• DB_CREATE_FILE_DEST
• DB_CREATE_ONLINE_LOG_DEST_N
• DB_RECOVERY_FILE_DEST
If you set these parameters before you create the database, Oracle uses them for the placement of the datafiles, control files, and online redo logs. You can also enable OMF after your database has been created. Oracle uses the values of the initialization parameters for the locations of any newly added datafiles and online redo-log files. Oracle also determines the name of the newly added file.
The advantage of using OMF is that creating tablespaces is simplified. For example, the CREATE
TABLESPACE statement doesn’t need to specify anything other than the tablespace name. First, enable the OMF feature by setting the DB_CREATE_FILE_DEST parameter:
SQL> alter system set db_create_file_dest='/ora01/OMF';
Now, issue the CREATE TABLESPACE statement:
SQL> create tablespace inv1;
This statement creates a tablespace named INV1 with a default datafile size of 100MB. You can override the default by specifying a size:
SQL> create tablespace inv2 datafile size 20m;
One limitation of OMF is that you’re limited to one directory for the placement of datafiles. If you want to add datafiles to a different directory, you can alter the location dynamically: SQL> alter system set db_create_file_dest='/ora02/OMF';
Although this procedure isn’t a huge deal, I find it easier not to use OMF. Most of the environments I’ve worked in have many mount points assigned for database use. You don’t want to have to modify an initialization parameter every time you need a datafile added to a directory that isn’t in the current definition of DB_CREATE_FILE_DEST. It’s easier to issue a CREATE TABLESPACE statement or ALTER
TABLESPACE statement that has the file-location and storage parameters in the script. It isn’t cumbersome to provide directory names and filenames to the tablespace-management statements.
80
CHAPTER 4 ■ TABLESPACES AND DATAFILES
Creating a Bigfile Tablespace
The bigfile feature allows you to create a tablespace with a potentially very large datafile assigned to it.
The advantage of using the bigfile feature is that you can create very large files. With an 8KB block size, you can create a datafile as large as 32TB. With a 32KB blocksize, you can create a datafile up to 128TB.
Use the BIGFILE clause to create a bigfile tablespace:
create bigfile tablespace inv_big_data
datafile '/ora02/dbfile/O11R2/inv_big_data01.dbf'
size 10g
extent management local
uniform size 128k
segment space management auto;
As long as you have plenty of space associated with the filesystem supporting the bigfile tablespace datafile, you can store massive amounts of data in a tablespace.
One potential disadvantage of using a bigfile tablespace is that if for any reason you run out of space on a filesystem that supports the datafile associated with the bigfile, you can’t expand the size of the tablespace (unless you can add space to the filesystem). You can’t add more datafiles to a bigfile tablespace if they’re placed on separate mount points. A bigfile tablespace allows only one datafile to be associated with it.
You can make the bigfile tablespace the default type of tablespace for a database using the ALTER
DATABASE SET DEFAULT BIGFILE TABLESPACE statement. However, I don’t recommend doing that. You could potentially create a tablespace, not know it was a bigfile tablespace (because you forgot it was the default, or you’re a new DBA on the project and didn’t realize it), and create a tablespace on a mount point. Then, when you discovered that you needed more space, you wouldn’t know that you couldn’t add another datafile on a different mount point for this tablespace because it was bigfile constrained.
Displaying Tablespace Size
DBAs often use monitoring scripts to alert them when they need to increase the space allocated to a tablespace. The following script displays the percentage of free space left in a tablespace and datafile: SET PAGESIZE 100 LINES 132 ECHO OFF VERIFY OFF FEEDB OFF SPACE 1 TRIMSP ON
COMPUTE SUM OF a_byt t_byt f_byt ON REPORT
BREAK ON REPORT ON tablespace_name ON pf
COL tablespace_name FOR A17 TRU HEAD 'Tablespace|Name'
COL file_name FOR A40 TRU HEAD 'Filename'
COL a_byt FOR 9,990.999 HEAD 'Allocated|GB'
COL t_byt FOR 9,990.999 HEAD 'Current|Used GB'
COL f_byt FOR 9,990.999 HEAD 'Current|Free GB'
COL pct_free FOR 990.0 HEAD 'File %|Free'
COL pf FOR 990.0 HEAD 'Tbsp %|Free'
COL seq NOPRINT
DEFINE b_div=1073741824
--
SELECT 1 seq, b.tablespace_name, nvl(x.fs,0)/y.ap*100 pf, b.file_name file_name, b.bytes/&&b_div a_byt, NVL((b.bytes-SUM(f.bytes))/&&b_div,b.bytes/&&b_div) t_byt, NVL(SUM(f.bytes)/&&b_div,0) f_byt, NVL(SUM(f.bytes)/b.bytes*100,0) pct_free FROM dba_free_space f, dba_data_files b
,(SELECT y.tablespace_name, SUM(y.bytes) fs
FROM dba_free_space y GROUP BY y.tablespace_name) x
81
CHAPTER 4 ■ TABLESPACES AND DATAFILES
,(SELECT x.tablespace_name, SUM(x.bytes) ap
FROM dba_data_files x GROUP BY x.tablespace_name) y
WHERE f.file_id(+) = b.file_id
AND x.tablespace_name(+) = y.tablespace_name
and y.tablespace_name = b.tablespace_name
AND f.tablespace_name(+) = b.tablespace_name
GROUP BY b.tablespace_name, nvl(x.fs,0)/y.ap*100, b.file_name, b.bytes UNION
SELECT 2 seq, tablespace_name,
j.bf/k.bb*100 pf, b.name file_name, b.bytes/&&b_div a_byt,
a.bytes_used/&&b_div t_byt, a.bytes_free/&&b_div f_byt, a.bytes_free/b.bytes*100 pct_free
FROM v$temp_space_header a, v$tempfile b
,(SELECT SUM(bytes_free) bf FROM v$temp_space_header) j
,(SELECT SUM(bytes) bb FROM v$tempfile) k
WHERE a.file_id = b.file#
ORDER BY 1,2,4,3;
If you don’t have any monitoring in place, you’re alerted via the SQL statement that is attempting to perform an insert or update operation that requires more space but isn’t able to allocate more. For example:
ORA-01653: unable to extend table INVENTORY by 128 in tablespace INV_IDX
After you determine that a tablespace needs more space, you need to either increase the size of a datafile or add a datafile to a tablespace. These topics are discussed in the next section.
Altering Tablespace Size
When you’ve determined which datafile you want to resize, first make sure you have enough disk space to increase the size of the datafile on the mount point on which the datafile exists: $ df -h | sort
Use the ALTER DATABASE DATAFILE ... RESIZE command to increase the datafile’s size. This example resizes the datafile to 5GB:
SQL> alter database datafile '/ora01/oradata/INVREP/reg_data01.dbf' resize 5g; If you don’t have space on an existing mount point to increase the size of a datafile, then you must add a datafile. To add a datafile to an existing tablespace, use the ALTER TABLESPACE ... ADD DATAFILE
statement:
SQL> alter tablespace reg_data
add datafile '/ora01/dbfile/INVREP/reg_data02.dbf' size 100m;
If you have bigfile tablespaces, then you can’t use the ALTER DATABASE ... DATAFILE statement to change the size of a tablespace’s datafile. To resize the single datafile associated with a bigfile tablespace, you must use the ALTER TABLESPACE clause:
SQL> alter tablespace bigstuff resize 1T;
Resizing datafiles can be a daily task when you’re managing databases with heavy transaction loads.
Increasing the size of an existing datafile allows you to add space to a tablespace without adding more datafiles. If there isn’t enough disk space left on the storage device that contains an existing datafile, you can add a datafile in a different location to an existing tablespace.
82
CHAPTER 4 ■ TABLESPACES AND DATAFILES
If you want to add space to a temporary tablespace, first query the V$TEMPFILE view to verify the current size and location of temporary datafiles:
SQL> select name, bytes from v$tempfile;
Next, use the TEMPFILE option of the ALTER DATABASE statement:
SQL> alter database tempfile '/ora01/oradata/INVREP/temp01.dbf' resize 500m; You can also add a file to a temporary tablespace via the ALTER TABLESPACE statement: SQL> alter tablespace temp add tempfile '/ora01/oradata/INVREP/temp02.dbf' size 5000m; Toggling Datafiles Offline and Online
Sometimes, when you’re performing maintenance operations (such as renaming datafiles), you may need to first take a datafile offline. You can use either the ALTER TABLESPACE or the ALTER DATABASE
DATAFILE statement to toggle datafiles offline and online.
Use the ALTER TABLESPACE ... OFFLINE NORMAL statement to take a tablespace and its associated datafiles offline. You don’t need to specify NORMAL, because it’s the default: SQL> alter tablespace users offline;
When you place a tablespace offline in normal mode, Oracle checkpoints the datafiles associated with the tablespace. This ensures that all modified blocks in memory that are associated with the tablespace are flushed and written to the datafiles. You don’t need to perform media recovery when you bring the tablespace and its associated datafiles back online.
You can’t use the ALTER TABLESPACE statement to place tablespaces offline when the database is in mount mode. If you attempt to take a tablespace offline while the database is mounted (but not open), you receive the following error:
ORA-01190: database not open
■
Note
When in mount mode, you must use the ALTER DATABASE DATAFILE statement to take a datafile offline.
When taking a tablespace offline, you can also specify ALTER TABLESPACE ... OFFLINE TEMPORARY. In this scenario, Oracle checkpoints all datafiles associated with the tablespace that are online. Oracle doesn’t checkpoint offline datafiles associated with the tablespace.