Re: calculate unused space in a datafile

The query like in Patrick's post is going to give you amount of unused space but will not help
you resize your datafiles. Something like this:

        select a.file_name, max( b.block_id + b.blocks - 1 ) * db_block_size#

        from   dba_data_files a, dba_extents b
        where  a.file_id = b.file_id
        group  by a.file_name, a.blocks

will give you the position to cut datafiles. Use ALTER DATABASE DATAFILE .. RESIZE
commands to do it. You dont want to do it with your rollback, system and temporary tablespaces.

Regards
Karen Abgarian.

buckeye714 wrote:

> The following script will calculate the free space for all
> datafiles in your database:
>
> COLUMN file_name FORMAT a60;
> SELECT SUM(b.bytes)/(1024*1024)||'Mb' "Free Space", a.file_name
> FROM dba_data_files a,
> dba_free_space b
> WHERE b.file_id = a.file_id
> GROUP BY a.file_name;
>
> HTH,
> Patrick
>
> -----------------------------------------------------------
>
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com
Received on Wed Aug 09 2000 - 00:00:00 CDT