Archive

Posts Tagged ‘drop’

Rename a tablespace/datafile in Oracle 9i/10g

October 25, 2010 2 comments

To rename a tablespace in Oracle 10g is very easy:

SQL> alter tablespace test1 rename to test2;

The rename only works in Oracle 10, and updates the dictionary entries for user quotas (user_ts_quotas). There is no simple way to rename a tablespace in Oracle 9, you have to use exp/imp, so, you cannot issue this online.

If you want to delete a tablespace, it’s better to first unassign all quotas on that tablespace, because if you later create a tablespace with the same name, the old quotas are still active:

SQL> alter user <username> quota 0 on <tablespace_name>;
SQL> drop tablespace <tablespace_name> including contents and datafiles;

If you want to rename also its datafile(s) you have to do it via SQL*Plus and linux console:

SQL> ALTER TABLESPACE test2 OFFLINE;
linux> mv 'oldfile.dbf' 'newfile.dbf'
SQL>  ALTER TABLESPACE test2 RENAME datafile '/path_to_old_datafile/oldfile.dbf' TO '/path_to_new_datafile/newfile.dbf'

SQL>  ALTER TABLESPACE test2 ONLINE;
Advertisements
Categories: oracle, SQL*Plus Tags: , , ,

Drop a datafile in oracle 10g RAC with ASM

August 11, 2010 Leave a comment

By mistake, i added a datafile in an ASM+ at a wrong location.

ALTER TABLESPACE "TEST" ADD DATAFILE '+DG_MIKE_DATA1/mike_03.dbf' SIZE 1G

The correct ASM path would be ‘+DG_MIKE_DATA1/mike/datafile/mike_03.dbf’

Trying to solve the mistake, i took the datafile offline (not offline normal) using the Enterprise Manager console:

ALTER DATABASE DATAFILE '+DG_MIKE_DATA1/mike_03.dbf' OFFLINE;

When I tried to drop the datafile via sqlplus i get this:

SQL> ALTER TABLESPACE MIKE DROP DATAFILE '+DG_MIKE_DATA1/mike_03.dbf';
ERROR at line 1:
ORA-01113: file 76 needs media recovery
ORA-01110: data file 76:
'+DG_MIKE_DATA1/mike_03.dbf'

Thus, I’ve found this blog http://esemrick.blogspot.com/2006/03/recovery-of-offline-data-files.html and applied his knowledge to my mistake:

SQL> select status from v$datafile where file#=76;
STATUS
----------
RECOVER
SQL> recover datafile 76;

with all the suggested logs to take back offline the datafile:

SQL> select status from v$datafile where file#=76;
STATUS
----------
OFFLINE
SQL> alter database datafile 76 online;
SQL> alter tablespace TEST DROP DATAFILE 76;

And now I can re-create the datafile in it’s correct location:

ALTER TABLESPACE "TEST" ADD DATAFILE '+DG_MIKE_DATA1/mike/datafile/mike_03.dbf' SIZE 1G

[UPDATED 2010-08-17]

Be careful if you drop a datafile on your primary database (in my case a RAC database) and  you have a standby database (dataguard). Now I have the bug 5623467, “Corrupt redo from ALTER TABLESPACE DROP DATAFILE”, and I think the only workaround is to restore&recover the standby database from the primary database 😦

Categories: oracle Tags: , , , , , , ,