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

[hoctro]

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

[dragon99]

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

[pando]

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

[hoctro]

Thanks Both for the helpful tips

I want to delete the datafile just added

[hoctro]

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

[pando]

you can check for contents with dba_extents