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';
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.
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
Thanks Both for the helpful tips
I want to delete the datafile just added
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
you can check for contents with dba_extents
Click Here to Expand Forum to Full Width