Re: How to remove a missing datafile record from controlfile
- From: sundar mahadevan <sundarmahadevan82@xxxxxxxxx>
- To: afatkulin@xxxxxxxxx
- Date: Thu, 6 May 2010 13:14:43 -0400
Hi Alex,
Many Thanks for your help. It worked and saved me lot of hassle. Also with
the link provided, I did understand the use of the alter tablespace offline
drop command. Thanks to one and all for the support.
On Thu, May 6, 2010 at 10:20 AM, Alex Fatkulin <afatkulin@xxxxxxxxx> wrote:
> Offline drop not removing a datafile is in fact a beneficial behavior
> as it allows you to do a partial restore of your database and bring it
> up ahead of time, see
>
> http://afatkulin.blogspot.com/2008/12/alter-database-datafile-offline-drop.html
>
> Regarding your question -- you can not drop a tablespace because it
> has an index which is being used to enforce a primary/unique key (the
> error is sort of self-explanatory, no?).
>
> SQL> create table t (n number);
>
> Table created.
>
> SQL> alter table t add constraint pk_t primary key (n) using index
> tablespace dummy;
>
> Table altered.
>
> SQL> drop tablespace dummy including contents and datafiles;
> drop tablespace dummy including contents and datafiles
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-02429: cannot drop index used for enforcement of unique/primary key
>
> SQL> alter index pk_t rebuild tablespace users;
>
> Index altered.
>
> SQL> drop tablespace dummy including contents and datafiles;
>
> Tablespace dropped.
>
>
> --
> Alex Fatkulin,
> http://afatkulin.blogspot.com
> http://www.linkedin.com/in/alexfatkulin
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
