By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,321 Members | 1,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,321 IT Pros & Developers. It's quick & easy.

drop table from readonly TS

P: n/a
Hi,

* Question background :

My DB is in ArchiveLog mode.
I have TS in ReadOnly mode.
As i know,
- Oracle allows to DROP table resides in ReadOnly TS (& other objects
also) even though that TS is in ReadOnly mode.
- Oracle will not recover ReadOnly TS during Oracle startup.

I have backup of db files "after" making TS readOnly but "before"
dropping tables from ReadOnly TS.
That means...
1st, made TS as ReadOnly
2nd, took backup of ReadOnly TS
3rd, dropped few tables from ReadOnly TS
Assume now media failure occurred.

* Question :
After restore + recovery operation, those dropped table will be
available or not ? WHY ?
I am in doubt because
1) there is chance of having those tables BACK after restore +
recovery operation because restored files are before dropping tables.
2) there is chance of NOT having those tables back after restore +
recovery operation because Oracle doesn't perform recovery for
ReadOnly TS.

Which one is correct ???
Thanks,
Darshak
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
da******@coreobjects.com (Darshak Shah) wrote in message news:<92**************************@posting.google. com>...
Hi,

* Question background :

My DB is in ArchiveLog mode.
I have TS in ReadOnly mode.
As i know,
- Oracle allows to DROP table resides in ReadOnly TS (& other objects
also) even though that TS is in ReadOnly mode.
- Oracle will not recover ReadOnly TS during Oracle startup.

I have backup of db files "after" making TS readOnly but "before"
dropping tables from ReadOnly TS.
That means...
1st, made TS as ReadOnly
2nd, took backup of ReadOnly TS
3rd, dropped few tables from ReadOnly TS
Assume now media failure occurred.

* Question :
After restore + recovery operation, those dropped table will be
available or not ? WHY ?
I am in doubt because
1) there is chance of having those tables BACK after restore +
recovery operation because restored files are before dropping tables.
2) there is chance of NOT having those tables back after restore +
recovery operation because Oracle doesn't perform recovery for
ReadOnly TS.

Which one is correct ???
Thanks,
Darshak


Information about tables is maintained in the datadictionary in the
system tablespace. The affected tables have been deleted from the
dictionary.
And NO you will not get them back EVER. Not recovering read only
tablespaces has NOTHING to do with it.

Sybrand Bakker
Senior Oracle DBA
Jul 19 '05 #2

P: n/a
sy******@yahoo.com wrote in message news:<a1**************************@posting.google. com>...
da******@coreobjects.com (Darshak Shah) wrote in message news:<92**************************@posting.google. com>...
Hi,

* Question background :

My DB is in ArchiveLog mode.
I have TS in ReadOnly mode.
As i know,
- Oracle allows to DROP table resides in ReadOnly TS (& other objects
also) even though that TS is in ReadOnly mode.
- Oracle will not recover ReadOnly TS during Oracle startup.

I have backup of db files "after" making TS readOnly but "before"
dropping tables from ReadOnly TS.
That means...
1st, made TS as ReadOnly
2nd, took backup of ReadOnly TS
3rd, dropped few tables from ReadOnly TS
Assume now media failure occurred.

* Question :
After restore + recovery operation, those dropped table will be
available or not ? WHY ?
I am in doubt because
1) there is chance of having those tables BACK after restore +
recovery operation because restored files are before dropping tables.
2) there is chance of NOT having those tables back after restore +
recovery operation because Oracle doesn't perform recovery for
ReadOnly TS.

Which one is correct ???
Thanks,
Darshak


Information about tables is maintained in the datadictionary in the
system tablespace. The affected tables have been deleted from the
dictionary.
And NO you will not get them back EVER. Not recovering read only
tablespaces has NOTHING to do with it.

Sybrand Bakker
Senior Oracle DBA


Hi,
actually this is kind of interesting - I think.
If this tablespace is local managed, Oracle would have to update the
free space bitmaps in the 'read only' datafiles, i.e. they change.
Thus a new backup would be required.

I did nt't test this, but i doubt that it would be allowed to drop
tables from read-only local managed tablespaces.

Karsten
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.