If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Threaded View

Alter Database Datafile Offline Drop

Hi,

One of my datafile got corrupted and one of the DATA tablespace is in danger. Unfortunately, I do not have any backup of the datafile.
I referred to metalink, and got some info. I am attaching the metalink document here.

METALINK DOCUMENT
-----------------
Displayed below are the messages of the selected thread.

Hallo
We have migrated some databases to Oracle 8.x.x and I had to drop some old users/schematas from our oracle test server, which we do not need anymore. But I removed one datafile on unix, after 'drop user xyz cascade', but before setting the tablespace offline and before removing it from the database.
The database is in noarchive mode, there are no backups of the datafiles, there is only a full export.
What can I do to solve the problem with the next database start?
Thanks in advance
Hans-Gerd
--------------------------------------------------------------------------------
From: Oracle, Harm Joris ten Napel 03-Sep-02 10:55
Subject: Re : Drop datafile before removing it from database

Hi,

you need to drop all datafiles of the tablespace this one datafile
belonged to with the following command:

ALTER DATABASE DATAFILE '' OFFLINE DROP;

Then drop the tablespace:

DROP TABLESPACE INCLUDING CONTENTS;

The 'OFFLINE DROP' syntax was especially designed for disasters like
this or practices where for instance you don't backup temp or index
tablespaces and you need this to be able to recover the database with
only the really important datafiles,

I was trying to simulate this in a test database before doing it on my devolopment DB.problem

1. Created a tablespace called “TEST” with two datafiles one with 100 MB size and othe one is 20 MB.
2. Created a user “testuser” with default tablespace “TEST”
3. Logged in with testuser and created two tables.
4. Deteled the second datafile of TEST tablespace (size 20 MB) from OS.
5. Tried shutting down the DB
6. It gave me the error and could not shutdown the database.
7. I did a shutdown abort and the DB is down now.
8. Tried starting the DB. I got me ORA-01110 and ORA-01116
9. Droped the 20 MB datafile by ALTER DATABASE DATAFILE OFFLINE DROP
10 Started the database.
11. Logged in with testuser
12 I could see all the tables which I created in “TEST tablespace!!

Now my doubt is,

Metalink document says after this exercise we need to give DROP TABLESPACE TABLESPACE_NAME

In my case I have not given this command and still able to access the tables from TEST tablespace.

Could you please tell me

1. Why am I able to access tables from TEST even after droping the datafile.
2. If people can access the data after droping the datafile (one or two , not all datafiles) why should we give DROP TABLESPACE.