Dropping a database involves removing its datafiles, redo log files, control files, and initialization parameter files. The DROP DATABASE statement deletes all control files and all other database files listed in the control file. To use the DROP DATABASE statement successfully, all of the following conditions must apply:



The database must be mounted and closed.


SQL> select status from v$instance;

STATUS
------------
MOUNTED


The database must be mounted as RESTRICTED.


SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED



The DROP DATABASE statement has no effect on archived log files, nor does it have any effect on copies or backups of the database. It is best to use RMAN to delete such files. If the database is on raw disks, the actual raw disk special files are not deleted.



SQL> drop database;

Database dropped.


Note: Manually delete below folder.
1. $ORACLE_BASE/oradata/[sid]
2. $ORACLE_BASE/admin/[sid]
3. $ORACLE_BASE/flash_recovery_area/[sid]
4. If database created through DBCA then you have to also delete ORACLE_SID through DBCA otherwise you will get error message when you create new database with same ORACLE_SID.

Posted by Mohammed Taj at Saturday, September 08, 2007 0 comments Links to this post
Email ThisBlogThis!Share to TwitterShare to Facebook
Labels: Database Reference, New Features Oracle 10.1.0.2.0
Reactions:
Renaming Tablespaces

Renaming Tablespaces



Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace.


SQL> alter tablespace users RENAME TO userts;

Tablespace altered.


When you rename a tablespace the database updates all references to the tablespace name in the data dictionary, control file, and (online) datafile headers. The database does not change the tablespace ID so if this tablespace were, for example, the default tablespace for a user, then the renamed tablespace would show as the default tablespace for the user in the DBA_USERS view.



The following affect the operation of this statement:

The COMPATIBLE parameter must be set to 10.0 or higher.

If the tablespace being renamed is the SYSTEM tablespace or the SYSAUX tablespace, then it will not be renamed and an error is raised.


SQL> alter tablespace system RENAME TO system1;
alter tablespace system RENAME TO system1
*
ERROR at line 1:
ORA-00712: cannot rename system tablespace


SQL> alter tablespace sysaux RENAME TO system1;
alter tablespace sysaux RENAME TO system1
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace



If any datafile in the tablespace is offline, or if the tablespace is offline, then the tablespace is not renamed and an error is raised.


SQL> alter tablespace userts RENAME TO users;
alter tablespace userts RENAME TO users
*
ERROR at line 1:
ORA-01135: file 4 accessed for DML/query is offline
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF '



If the tablespace is read only, then datafile headers are not updated. This should not be regarded as corruption; instead, it causes a message to be written to the alert log indicating that datafile headers have not been renamed. The data dictionary and control file are updated.


SQL> alter tablespace userts read only;

Tablespace altered.

SQL> alter tablespace userts RENAME TO users;

Tablespace altered.
alert_orcl.log
Sat Sep 08 19:52:42 2007
alter tablespace userts RENAME TO users
Tablespace 'USERTS' is renamed to 'USERS'.
Tablespace name change is not propagated to file headers because the tablespace is read only.


If the tablespace is the default temporary tablespace, then the corresponding entry in the database properties table is updated and the DATABASE_PROPERTIES view shows the new name.


SQL> select property_value
2 from database_properties
3 where property_name like '%TEMP%';

PROPERTY_VALUE
------------------------------------------------

TEMP2

SQL> alter tablespace temp2 rename to temp;

Tablespace altered.

SQL> select property_value
2 from database_properties
3 where property_name like '%TEMP%';

PROPERTY_VALUE
------------------------------------------------

TEMP



If a traditional initialization parameter file (PFILE) is being used then a message is written to the alert file stating that the initialization parameter file must be manually changed.


SQL> --when we rename undo tablespace and instance is startup with PFILE then
SQL> --rename entry is recorded in alert_sid.log file.
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> --database is startup from pfile
SQL> alter tablespace undotbs1 rename to undotbs;

Tablespace altered.

Sat Sep 08 20:14:52 2007
Tablespace 'UNDOTBS1' is renamed to 'UNDOTBS'.
PFILE is being used. It must be manually modified to reflect the new tablespace name if the old tablespace name is specified as UNDO_TABLESPACE in the PFILE.