Drop table.. allowed in read only tablespaces

[Sameer]

Hi All,

How can I able to drop tables in readonly tablespace? Will anybody explain me?

Thanks

Sameer

[jrpm]

You can't.

Make the tablespace read write first, then drop the table. You can set it to read only again.

If you are in read only media, such as CD, then SOL.

[pando]

if it´s normal disk then just issue drop table

[marist89]

Originally posted by jrpm
You can't.

Make the tablespace read write first, then drop the table. You can set it to read only again.

If you are in read only media, such as CD, then SOL.

Ah, I disagree. DROP TABLE will only modify the data dictionary. For example:

SQL> connect a/a
Connected.

SQL> create table xyz (x number(10), y date, z varchar2(20))
  2  tablespace users;

Table created.

SQL> connect system
Enter password: ******
Connected.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> connect a/a
Connected.
SQL> drop table xyz;

Table dropped.

SQL> connect system
Enter password: ******
Connected.
SQL> alter tablespace users read write;

Tablespace altered.

SQL> desc a.xyz
ERROR:
ORA-04043: object a.xyz does not exist

[Sameer]

Yes It allows you to drop even if the tablespace is read only.

My question is How Oracle allows to drop a table if it exist in read only tablespace..

Sameer

[pravin]

Jeff my master , Good answer with classic example.

[sreddy]

Originally posted by Sameer
How Oracle allows to drop a table if it exist in read only tablespace..

I don't understand... dropping ready only object isn't different from dropping read and write object... You don't want that object in the schema and you are dropping it as schema owner...

[pando]

the table is dropped from data dictionary but physically it is still in that tablespace, you can see some funny names like

9.51
9.59

for those tables you dropped

[Sameer]

Ok.. Thanks for the answers..

So it allows all operations except

DML because I could able to add a column to table which is in read only tablespace as well as I able to COALESCE table readonly tablespace..

Sameer

[pando]

FYI the tables you dropped still uses space but they become temporary segments. In order to free them you have to make tablespace read write and bounce the database so smon can free them

regarding adding columns, its just a data dictionary operation, in fact nothing has been done in the read only tablespace that´s why it does not gives you error, obviously to use that column you would have to enable tablespace in read write mode again

coalesce again does not alter the objects in the tablespace so it is allowed

DML is not allowed because you are physically writing to the datafiles