Urgent

[sumit]

Hello Gurus,

Unintentionally the users data file got deleted. I did not have archive logs.

What I did
------------

1. alter database datafile '.....' offline drop;

2. alter database open;

Database is ow up.

3. Then I tried to drop the tablespace USERS;
But before doing so I relocate all users who were associated with USERS tablespace by default.

4. Then I tried to drop the tablespace

drop tablespace users including contents;

I am getting this error...
ORA 02429 Can not drop index used for enforcement of unique/primary key

5. Then I checked
select username from dba_users where default_tablespace='USERS';

NO ROWS SELECTED.

6. Then I fired

select index_name from dba_indexes where tablespace='USERS';


it shows few primakey indexes with few lob type indexes.

7. Then I tried to drop those indexes

It is not allowing for reference constraint ....

8. Then I tried to rebuild the index but it is not allowing....

9. Then I tried to find out the reference key details with

select r_constarint_name,r_owner,table_name fromdba_constraints where constraint_name in (.....);

for r_owner,table_name the result is totally BLANK.


*** NOW BOTTOMLINE IS I HAVE TO DROP THAT TABLESPACE USERS... [ WHICH (datafile) IS PRESENTLY OFFLINE AND DROPED] WHERE FEW CONSTRAINTS / INDEXES ARE ASSOCIATED ....

HOW TO DO THAT??????????????

I will appreciate ur help

Regards
Sumit

[sumit]

Hello


Any help please?????????

[gandolf989]

Do you have either a recent export or a full backup?
If you have a full backup you may want to rename your oradata directory and restore from tape. If you don't have a backup but have an export. You might consider renaming your oradata directory and then recreating your database and doing a full import making sure that you create a new oradata directory. If you don't have either a backup or an export you might want to think about URLT - update resume leave town.
If you only had indexes in that folder then you can recreate the indexes. But if you had tables in that folder then where would you get the data back from. Also is the data file sitting in the reccylcle bin?

[sumit]

Is there no other way?

Sumit

[gandolf989]

From your reply I am guessing that you do not have any backup, and that you can't find the file anywhere. At this point you should find out what tables were in that tablespace and decide if you can manually add the information back in. If you had a copy of the users data from yesterday and restored that. Depending on the amount of DML that happened today you might have enough information in the log files to recover the datafile and open the database. but with out the datafile, a backup of the datafile or an export, there is nothing that can be done but to try and rebuild whatever you had. And of course to make sure that every database gets backed up end exported everyday, and if possible turn on archive log. Unless of course you don't care about the data. Even an old backup is going to give you more than you have which is nothing.
Good Luck!

[pando]

rebuild the index
if not possible disable constraint and drop the indexes
enable the constraints specifying tablespace

[gandolf989]

If Sumit lost tables and not just indexes then fixing the indexes won't be enough. It really depends on what was in that tablespace.

When he wrote the following:

I am getting this error...
ORA 02429 Can not drop index used for enforcement of unique/primary key

That tells me that he had tables in that tablespace. Which according to what he said are unrecoverable.

Sumit should try:
SELECT Owner, Table_Name, Index_Name FROM DBA_INDEXES WHERE Tablespace_Name = 'USERS';
anything that show up here can be dropped and recreated in another tablespace.

SELECT Owner, Table_Name FROM DBA_TABLES WHERE Tablespace_Name = 'USERS';
Anything that shows up here is lost forever unless Sumit happens accross a backup.

[sumit]

Hi guys,

Due to loss of Users tablepsace datafile, I fired

1. Alter database datafile 'xxxxxxx' offline drop;

Then I tried to drop users indexes , but it was giving error for unique/primary constraints or indexes. Then I tried to query about all informations.

And I got 5 indexes , including lob indexes are there. But they are not allowed me to drop or rebuild . Then I tried to know the reference table name and constraints , but that also resulting me BLANK.

Basically , my mistake someone created few objects in USERS tablespace which is unusual.

When that guy asked me, I instructed that if there is no imp data u can easily .... drop offline and recreate the same.

But then all these...........

But suprisingly, when there is no user schema, or table objects are there, then how only those indexes are there? And most interesting is ... When that datafile is offline and droped where from database is flushing those index informations???????????


Thanks for ur views....

I am looking forward for few more supports.

I was expecting something from Julian!!!

Regards

Sumit

[gandolf989]

If you know what tables were in Users you can do the following for each table in Users:

Drop table TName cascade constraints;

This will remove the missing tables. When you have removed all of the tables that are invalid then try to recreate the tablespace and recreate the tables.

I'm guessing that the way you know which tables that you need to drop and rename is to do a

select distinct table_name from all_indexes where tablespace_name is null OR tablespace_name = 'USERS';

[pando]

Originally posted by gandolf989
I am getting this error...
ORA 02429 Can not drop index used for enforcement of unique/primary key

That tells me that he had tables in that tablespace.

that means the primary/unique keys indexes is in the tablespace he is going to drop not the tables are in that tablespace

he gets that error because instead of dropping/disabling the constraints he tried to drop the indexes