Re: Can I delete a datafile not used?

In article <56105c$35_at_cronkite.ocis.temple.edu>,

        vseth_at_thunder.temple.edu (Stephanian) writes:
>suvas (suvas_at_pacific.net.sg)
>: SQLDBA>ALTER DATABASE DATAFILE '<datafile name>' OFFLINE DROP;
 

>I think the above solution doesn't work even though oracle says
>"statement pprocessed " and ther is no error in the alert log file

You're right. The only method to get rid of it while retaining data in the tablespace remains an export of the contents of the tablespace, dropping the tablespace and its datafiles, then recreating its tablespace and re-importing the data.

However, sometimes, the problem isn't really that the datafile shouldn't exist, but rather that it was created with much too big a size. In this case, ORACLE 7.2 and above has help, providing that compatability is at 7.2 or above, and the data file was never written to ...

ALTER DATABASE DATAFILE 'pathname as in dba_data_files' SIZE nnK;

This will truncate the datafile both from ORACLE's viewpoint, and also from the external operating system view. The actual size that can be specified is related to the operating system, and ORACLE creation parameters. On sun Solaris with ORACLE blocks defined as 8K, I can't go smaller than 40K.

-- 
----------------------------------
E-Mail: Beatonb_at_cadvision.com or Bill_Beaton_at_calgary.qc-data.com
----------------------------------