Pro Oracle Database 11g Administration (20 page)

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.