Renaming Tablespaces


Page 36

Renaming Tablespaces

You have been asking for it, I have been asking for it, and now it's here! Oracle Database 10g includes the ability to rename tablespaces. You use the alter tablespace command with the rename to parameter, as shown in this example:

 Alter tablespace production_tbs rename to prod_tbs; 

Note that you cannot rename the SYSTEM tablespace or the SYSAUX tablespace (refer to Chapter 1). Another nice feature is that if the tablespace is an UNDO tablespace, and you are using a server parameter file (SPFILE), Oracle will change the UNDO_TABLESPACE parameter in the SPFILE to reflect the new UNDO tablespace name.

The ability to rename tablespaces has some great practical applications with operations such as transportable tablespaces. Now, rather than having to drop the existing tablespace before you can transport it in, you only need rename that tablespace. Way to go Oracle!

Something to be aware of is that renaming a tablespace does not change the name of the datafile in any way. For example, OMF uses the name of the tablespace (or part of it) in the OMF datafile naming scheme, and frequently DBAs do the same when they manually create a tablespace datafile. Renaming the tablespace will result in the datafiles no longer reflecting the true name of the tablespace.

You should back up the control file as soon as possible after renaming tablespaces within the database. If you do not, depending on when the backup of the control file took place, a divergence may exist between the tablespace names in the control file and the actual tablespace names in the database. Refer to the Oracle Database 10g documentation for more details on specific recovery scenario responses.

Dropping Databases

The drop database command can be used to drop your database. Oracle will drop the database, deleting all control files and all datafiles listed in the control file. If you are using a SPFILE, then Oracle will remove it as well. Only a user with SYSDBA privileges can issue the statement and the database must be mounted (not open) in exclusive and restricted mode. Here is an example of the use of the drop database command:

 Drop database; 

Oracle Database 10g New Features
Oracle Database 10g New Features (Osborne ORACLE Press Series)
ISBN: 0072229470
EAN: 2147483647
Year: 2006
Pages: 80

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: