Hi All,
How can I able to drop tables in readonly tablespace? Will anybody explain me?
Thanks
Sameer
Printable View
Hi All,
How can I able to drop tables in readonly tablespace? Will anybody explain me?
Thanks
Sameer
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.
if it´s normal disk then just issue drop table
Ah, I disagree. DROP TABLE will only modify the data dictionary. For example:Quote:
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.
Code: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
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
Jeff my master , Good answer with classic example.
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...Quote:
Originally posted by Sameer
How Oracle allows to drop a table if it exist in read only tablespace..
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
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
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