Managing Datafile-优快云博客

1.Creating Datafiles and Adding Datafiles to a Tablespace

You can create datafiles and associate them with a tablespace using any of the statements listed in the following table. In all cases, you can either specify the file specifications for the datafiles being created, or you can use the Oracle Managed Files feature to create files that are created and managed by the database server. The table includes a brief description of the statement, as used to create datafiles, and references the section of this book where use of the statement is specifically described:

SQL Statement	Description	Additional Information
CREATE TABLESPACE	Creates a tablespace and the datafiles that comprise it	"Creating Tablespaces"
CREATE TEMPORARY TABLESPACE	Creates a locally-managed temporary tablespace and the tempfiles (tempfiles are a special kind of datafile) that comprise it	"Creating a Locally Managed Temporary Tablespace"
ALTER TABLESPACE ... ADD DATAFILE	Creates and adds a datafile to a tablespace	"Altering a Locally Managed Tablespace"
ALTER TABLESPACE ... ADD TEMPFILE	Creates and adds a tempfile to a temporary tablespace	"Altering a Locally Managed Temporary Tablespace"
CREATE DATABASE	Creates a database and associated datafiles	"Creating a Database with the CREATE DATABASE Statement"
ALTER DATABASE ... CREATE DATAFILE	Creates a new empty datafile in place of an old one--useful to re-create a datafile that was lost with no backup.	See Oracle Database Backup and Recovery User's Guide.

2.Changing Datafile Size

2.1 Enabling and Disabling Automatic Extension for a Datafile

You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The file size increases in specified increments up to a specified maximum.

Setting your datafiles to extend automatically provides these advantages:

1.Reduces the need for immediate intervention when a tablespace runs out of space
2.Ensures applications will not halt or be suspended because of failures to allocate extents

To determine whether a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.

You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles using the following SQL statements:

1.CREATE DATABASE
2.ALTER DATABASE
3.CREATE TABLESPACE
4.ALTER TABLESPACE

The following example enables automatic extension for a datafile added to the users tablespace:

ALTER TABLESPACE users
    ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
      AUTOEXTEND ON
      NEXT 512K
      MAXSIZE 250M;

The next example disables the automatic extension for the datafile.

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' 
    AUTOEXTEND OFF;

3.Manually Resizing a Datafile

The following statement decreases the size of datafile /u02/oracle/rbdb1/stuff01.dbf:

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'
   RESIZE 100M;

Note:

It is not always possible to decrease the size of a file to a specific value. It could be that the file contains data beyond the specified decreased size, in which case the database will return an error.

4.Altering Datafile Availability

You can alter the availability of individual datafiles or tempfiles by taking them offline or bringing them online. Offline datafiles are unavailable to the database and cannot be accessed until they are brought back online.

To take a datafile offline or bring it online, you must have the ALTER DATABASE system privilege. To take all datafiles or tempfiles offline using the ALTER TABLESPACE statement, you must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege. In an Oracle Real Application Clusters environment, the database must be open in exclusive mode.

4.1Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode

To bring an individual datafile online, issue the ALTER DATABASE statement and include the DATAFILE clause.The following statement brings the specified datafile online:

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

To take the same file offline, issue the following statement:

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

Note:

To use this form of the ALTER DATABASE statement, the database must be in ARCHIVELOG mode. This requirement prevents you from accidentally losing the datafile, since taking the datafile offline while in NOARCHIVELOG mode is likely to result in losing the file.

4.2Taking Datafiles Offline in NOARCHIVELOG Mode

To take a datafile offline when the database is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE FOR DROP clauses.

1.The OFFLINE keyword causes the database to mark the datafile OFFLINE, whether or not it is corrupted, so that you can open the database.
2.The FOR DROP keywords mark the datafile for subsequent dropping. Such a datafile can no longer be brought back online.
Note:
This operation does not actually drop the datafile. It remains in the data dictionary, and you must drop it yourself using one of the following methods:
- An ALTER TABLESPACE ... DROP DATAFILE statement.
  
  After an OFFLINE FOR DROP, this method works for dictionary managed tablespaces only.
- A DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES statement
- If the preceding methods fail, an operating system command to delete the datafile. This is the least desirable method, as it leaves references to the datafile in the data dictionary and control files.

The following statement takes the specified datafile offline and marks it to be dropped:

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;

5.Renaming and Relocating Datafiles

5.1Procedures for Renaming and Relocating Datafiles in a Single Tablespace

Procedure for Renaming Datafiles in a Single Tablespace

To rename datafiles in a single tablespace, complete the following steps:

Take the tablespace that contains the datafiles offline. The database must be open.

For example:
```
ALTER TABLESPACE users OFFLINE NORMAL;
```
Rename the datafiles using the operating system.
Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

For example, the following statement renames the datafiles /u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf, respectively:
```
ALTER TABLESPACE users
    RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
                    '/u02/oracle/rbdb1/user2.dbf'
                 TO '/u02/oracle/rbdb1/users01.dbf', 
                    '/u02/oracle/rbdb1/users02.dbf';
```
Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.
Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

5.2Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces

You can rename and relocate datafiles in one or more tablespaces using the ALTER DATABASE RENAME FILE statement. This method is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation. You must have the ALTER DATABASE system privilege.

Note:

To rename or relocate datafiles of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.

To rename datafiles in multiple tablespaces, follow these steps.

Ensure that the database is mounted but closed.

Note:
Optionally, the database does not have to be closed, but the datafiles (or tempfiles) must be offline.
Copy the datafiles to be renamed to their new locations and new names, using the operating system. You can copy the files using the DBMS_FILE_TRANSFER package
Use ALTER DATABASE to rename the file pointers in the database control file.

For example, the following statement renames the datafiles/u02/oracle/rbdb1/sort01.dbf and /u02/oracle/rbdb1/user3.dbf to /u02/oracle/rbdb1/temp01.dbf and /u02/oracle/rbdb1/users03.dbf, respectively:
```
ALTER DATABASE
    RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
                '/u02/oracle/rbdb1/user3.dbf'
             TO '/u02/oracle/rbdb1/temp01.dbf',
                '/u02/oracle/rbdb1/users03.dbf;
```
Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile names exactly as they appear in the DBA_DATA_FILES view.
Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

6.Dropping Datafiles

You use the DROP DATAFILE and DROP TEMPFILE clauses of the ALTER TABLESPACE command to drop a single datafile or tempfile. The datafile must be empty. (A datafile is considered to be empty when no extents remain allocated from it.) When you drop a datafile or tempfile, references to the datafile or tempfile are removed from the data dictionary and control files, and the physical file is deleted from the file system or Oracle Automatic Storage Management (Oracle ASM) disk group.

The following example drops the datafile identified by the alias example_df3.f in the Oracle ASM disk group DGROUP1. The datafile belongs to the example tablespace.
```
ALTER TABLESPACE example DROP DATAFILE '+DGROUP1/example_df3.f';
```
The next example drops the tempfile lmtemp02.dbf, which belongs to the lmtemp tablespace.
```
ALTER TABLESPACE lmtemp DROP TEMPFILE '/u02/oracle/data/lmtemp02.dbf';
```
This is equivalent to the following statement:
```
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP 
     INCLUDING DATAFILES;
```
Note:
If there are sessions using a tempfile, and you attempt to drop the tempfile, then an error is returned, and the tempfile is not dropped. In this case, the tempfile is taken offline, and queries that attempt to use the tempfile will fail while the tempfile is offline.

See Oracle Database SQL Language Reference for ALTER TABLESPACE syntax details.

Restrictions for Dropping Datafiles

The following are restrictions for dropping datafiles and tempfiles:
- The database must be open.
- If a datafile is not empty, it cannot be dropped.
  
  If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.
- You cannot drop the first or only datafile in a tablespace.
  
  Therefore, DROP DATAFILE cannot be used with a bigfile tablespace.
- You cannot drop datafiles in a read-only tablespace that was migrated from dictionary managed to locally managed.
- You cannot drop datafiles in the SYSTEM tablespace.
- If a datafile in a locally managed tablespace is offline, it cannot be dropped.