DROP TABLESPACE

Before using the DROP TABLESPACE INCLUDING CONTENTS command, take the tablespace offline to ensure that no temporary segments are in use.

B.3.2 Adding Files

When specifying files to be added to the database, logical names are fully translated to either physical device names or system-level concealed logical names (if defined) and then written to the control file.

B.3.3 Renaming Files

If the name of the physical device is somehow dissociated from the database file locations, then the database cannot access these files. Use the ALTER DATABASE command to rename the file to its current location. After renaming the files, shut down the database and then back up the control files as in the following example:

SQL> ALTER DATABASE RENAME FILE

2> ’DISK$1:[ORACLE10g.oradata.V10TEST]SYSTEM01.DBF’ TO 3> ’MY$DISK:[ORACLE10g.oradata.V10TEST]SYSTEM01.DBF’

SQL> EXIT

$

BACKUP/LOG/VERIFY/DISK$1:[ORACLE10g.oradata.V10TEST]*.CTL

-See Also: Oracle Database Administrator’s Guide

Managing the Database B-3 MY$DISK:[ORACLE10g.oradata.V10TEST]*.CTL

B.3.4 Moving Tablespace Files

To move a tablespace file to a new location:

1. Identify and write down the exact, fully qualified file name from the data dictionary view, and shut down the database. The physical device name and the file location must appear exactly as in the control file and the data dictionary view, DBA_DATA_FILES or V$LOGFILE.

$ SQLPLUS/NOLOG

SQL> CONNECT / AS SYSDBA SQL> SELECT * from V$DBFILE;

SQL> SELECT * from V$LOGFILE;

SQL> SHUTDOWN SQL> EXIT

2. Back up the tablespace and control files that you want to move.

3. Copy or move the file to a new location.

Use BACKUP/VERIFY/DELETE to move the file.

$ BACKUP/IGNORE=NOBACK/DELETE/VERIFY device:[dir]filename.ext

-new_device:[new_dir]new_filename.ext

4. Without opening it, mount the database in Exclusive mode.

$ SQLPLUS/NOLOG

SQL> CONNECT / AS SYSDBA

SQL> STARTUP EXCLUSIVE MOUNT dbname

5. Rename the file in the database using the exact string taken from V$DBFILE.

SQL> ALTER DATABASE

2> RENAME FILE ’device:[dir]filename.ext’

3> to ’new_device:[new_dir]new_filename.ext’;

SQL> ALTER DATABASE dbname OPEN;

SQL> EXIT

6. Back up the control files.

B.3.5 Moving Redo Log Files

Perform the following steps to move a redo log file to a new location:

1. Identify the fully qualified file name of the redo log files that you want to move by using one of the following methods:

Note: The physical device name and the file location must appear exactly as in the control file. Enter the following commands to get the physical device name and the database file locations:

$ SQLPLUS/NOLOG

SQL> CONNECT / AS SYSDBA SQL> SELECT * FROM V$DBFILE;

SQL> DISCONNECT

Managing Database Files

B-4 Oracle Database Administrator’s Reference a. Start the database.

b. Run the following query:

SQL> SELECT * FROM V$LOGFILE;

2. Shut down the database, create a backup of the redo log files in the new location, and mount the database in Exclusive mode (not opened).

$ SQLPLUS/NOLOG

SQL> CONNECT / AS SYSDBA SQL> SHUTDOWN

SQL> EXIT

$ BACKUP/IGNORE=NOBACK old_device:[dir]filename.ext -new_device:[new_dir]new_filename.ext

$ SQLPLUS/NOLOG

SQL> CONNECT / AS SYSDBA

SQL> STARTUP EXCLUSIVE MOUNT dbname

3. From SQL*Plus, rename the files in the database using the ALTER DATABASE command. Specify the full file path.

SQL> CONNECT / AS SYSDBA SQL> ALTER DATABASE RENAME FILE 2> ’device:[dir]old_redofile1.RDO’, 3> ’device:[dir]old_redofile2.RDO’ to 4> ’device:[dir]new_redofile1.RDO’, 5> ’device:[dir]new_redofile2.RDO’;

The file names specified must be correct and the new files must already exist. If either of these requirements is not met, then the statement fails.

4. Shut down the database by using the following command:

SQL> SHUTDOWN

5. Back up the control files for safety.

6. Restart the database using the following commands.

SQL> CONNECT / AS SYSDBA SQL> STARTUP OPEN dbname SQL> EXIT

Note: After the database is shut down, make copies of all database, control, and redo log files as a precaution against any problems that can arise during this procedure.

Note: Having the database mounted and closed is essential when working with the redo log files. This prevents any log files from becoming online or marked as current by the LGWR.

Managing the Database B-5

B.4 Database Verification Utility

The database verification utility (DBV) is the preferred technique for verifying the integrity of the database. Run this utility with the DBV symbol. Since Oracle Database 10g release 1, DBV can b e used on an open database.

To verify data in an Oracle Database 10g release 2 (10.2) database, point to the data files from the Oracle Database 10g release 2 (10.2) installation.

B.5 Important Note on Changes to Data File Formats for OpenVMS

In Oracle Database 10g, the transportable tablespace feature has been extended to enable tablespaces to be transported across different platforms. To make this feature available on OpenVMS has necessitated a change in the Oracle file format. Specifically, data files, created while the database is running in 10g compatibility mode, are created with a new header block. This is called the OSD header (also referred to as block zero) at the beginning of the file. There are several important points to note regarding support for this new feature on OpenVMS:

■ Oracle Database 10g retains full backward compatibility with 9.2 data files, which on OpenVMS prior to 10g did not contain a block 0. That is, a database can be started in 10g compatibility mode with a mix of 9.2 and 10g data files and is fully operable (all updates, writes, read, all operations available on a 10g data file are available on the 9.2 data file).

■ Data files created while the database is running in the 9.2 compatibility mode will continue to be created without the new header block.

■ The cross-platform transportable tablespace feature can only be used on files which have a block 0. For 10g data files, this is the case by default. For 9.2 data files, an explicit transformation must be applied to the file to create a new data file, which will contain the required block zero. Oracle Database 10g recommends the use of the RMAN backup as copy datafile command for the process of creating a new 10g format data file, that is:

RMAN> backup as copy datafile 'tbs_31.f' format '10g_tbs_31.f';

This creates a new data file, which is a copy of the original data file but with the new block 0 header and a 10g format generic file header.

See Also: Oracle Database Utilities for information about using SQL*Plus to verify the database

See Also: For more information about the rman backup command, refer to the relevant sections in the Oracle documentation set

Important Note on Changes to Data File Formats for OpenVMS

B-6 Oracle Database Administrator’s Reference