Drop table.. allowed in read only tablespaces
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Drop table.. allowed in read only tablespaces

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Hi All,

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

    Thanks

    Sameer

  2. #2
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    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.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if it´s normal disk then just issue drop table

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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:
    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
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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

  6. #6
    Join Date
    Dec 2001
    Location
    SAN FRANCISCO, CA
    Posts
    306
    Jeff my master , Good answer with classic example.
    Eat , Drink & Enjoy life -

    pravin_kini@hotmail.com

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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...
    Reddy,Sam

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  9. #9
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width