Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Wish to delete empty datafile

Re: Wish to delete empty datafile

From: Joseph S. Testa <teci_at_oracle-dba.com>
Date: Wed, 21 Jun 2000 13:42:40 -0400
Message-Id: <10535.110037@fatcity.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US