Re: Wish to delete empty datafile

you can drop a tablespace but not individual datafile on that tablespace.

check out this, this builds a .sql file and the corresponding rm(unix) for the datafiles for tablespaces that have NO objects in them.

dont run it blindly :)

joe

select 'drop tablespace '||a.tablespace_name||';' from sys.dba_data_files a, sys.dba_segments b where a.tablespace_name = b.tablespace_name(+) and b.tablespace_name is null
order by 1;

select '# rm -f '||a.file_name
from sys.dba_data_files a, sys.dba_segments b where a.tablespace_name = b.tablespace_name(+) and b.tablespace_name is null
order by 1;

Linda Hagedorn wrote:

> Hello,
>
> I want to confirm a dataset is empty, and drop it from the database. Does
> someone have a query showing a dataset is unused, that is to say, contains
> no data whatsoever.
>
> I have just finished reducing a production database to a test/development
> size (many thanks Joe!, Eric, and the list). I reduced it so much, I'm able
> to delete a user02.dbf dataset (after I confirm it is empty).
>
> Any assistance is appreciated.
Received on Wed Jun 21 2000 - 12:42:40 CDT