Re: how can i shirnk your datafile file size?

Havel Zhang wrote:
> hi all:
>
> I have a tablespace have a datafile sizing 8GB, but actually, I just
> using 2GB data. But when i resize that data file, oracle will return
> an error:
> Failed to commit: ORA-03297: file contains used data beyond requested
> RESIZE value .
>
> I have search the table dba_segment, and cleared all tables/indexes on
> that datafile, but still can not shirnk this datafile.
>
> Can u help me?
>
> Thank you.
>
> Havel

You think you have but Oracle disagrees.

Start here:
SELECT file_id, file_name
FROM dba_data_files
WHERE tablespace_name = <your tablespace>;

Then using the correct file_id:

SELECT owner, segment_name, segment_type FROM dba_extents
WHERE file_id = <file_id>;

You might also want to run DBMS_SPACE.VERIFY_SHRINK_CANDIDATE or DBMS_SPACE.VERIFY_SHRINK_CANDIDATE_TBF
http://www.psoug.org/reference/dbms_space.html

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org