DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: how to undo "alter tablespace table_name add datafile.......

  1. #1
    Join Date
    Apr 2001
    Location
    santa clara
    Posts
    41
    HI

    Is there a way to drop tablespace after it's been created as shown below:

    alter tablespace users
    add datafile '/xxxx/xxxxx/xx.dbf' size 1M autoextend on next 500M maxsize 600M';

    Thanks

  2. #2
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    just do
    SQL> alter tablespace TABLESPACE_NAME offline;
    SQL> drop tablespace TABLESPACE_NAME;(if there is content in there you have to add including contents clause)
    Then go to directory remove the datafile.

    Dragon

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well u want to drop the whole tablespace or just delete the new datafile you added? If you want to delete you cannot, the only way is export the contents and recreate the tablespace.

    It's easy to add datafile but difficult to get rid of it

  4. #4
    Join Date
    Apr 2001
    Location
    santa clara
    Posts
    41
    Thanks Both for the helpful tips

    I want to delete the datafile just added

  5. #5
    Join Date
    Apr 2001
    Location
    santa clara
    Posts
    41
    how do I know if there's contents in the datafile that I added to the tablespace?
    I am not sure how to query for this data
    Thanks

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you can check for contents with dba_extents

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