Script to Obtain Datafile Space free and Block Size in a Database

For high watarmark (check how much we reduce the size of datafile in actual size)

 **************************************************************************************************************************************

set verify off

set pages 2000

set lines 200

column file_name format a80 word_wrapped

column smallest format 999,990 heading "Smallest|Size|Poss."

column currsize format 999,990 heading "Current|Size"

column savings  format 999,990 heading "Poss.|Savings"

break on report

compute sum of savings on report

column value new_val blksize

select value from v$parameter where name = 'db_block_size'

/

VALUE

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

8192

select file_name,

      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

       ceil( blocks*&&blksize/1024/1024) currsize,

       ceil( blocks*&&blksize/1024/1024) -

       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

     ( select file_id, max(block_id+blocks-1) hwm

         from dba_extents

        group by file_id )b

where a.file_id = b.file_id(+)

order by savings desc;

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 Smallest
                                                                                   Size  Current    Poss.

FILE_NAME                                                                           Poss.     Size  Savings

-------------------------------------------------------------------------------- -------- -------- --------

/app/alien040/02/oradata/alien040C/dbf/trans_data_alien040C_002.dbf                        1
/app/alien040/02/oradata/alien040a/dbf/shop_area_alien040a_006.dbf                     9,729   29,696   19,967
/app/alien040/03/oradata/alien040a/dbf/trans_indexes_alien040a_001.dbf                12,597   31,000   18,403
/app/alien040/02/oradata/alien040a/dbf/shop_area_alien040a_005.dbf                     7,228   23,718   16,490
/app/alien040/03/oradata/alien040a/dbf/indexes_alien040a_001.dbf                       4,586    8,134    3,548
/app/alien040/02/oradata/alien040a/dbf/trans_data_alien040a_001.dbf                   12,123   15,348    3,225
/app/alien040/03/oradata/alien040a/dbf/data_alien040a_001.dbf                          6,003    8,919    2,916
/app/alien040/02/oradata/alien040a/dbf/shop_area_alien040a_001.dbf                    29,825   32,430    2,605
/app/alien040/03/oradata/alien040a/dbf/sherpa_gsr_alien040a_001.dbf                    2,322    4,096    1,774
/app/alien040/02/oradata/alien040a/dbf/tools_alien040a_001.dbf                           786    2,048    1,262
/app/alien040/02/oradata/alien040a/dbf/sysaux_alien040a_001.dbf                          228    1,024      796
/app/alien040/03/oradata/alien040a/dbf/hold_area_alien040a_001.dbf                       561    1,200      639
/app/alien040/02/oradata/alien040a/dbf/system_alien040a_001.dbf                          424    1,024      600
/app/alien040/02/oradata/alien040a/dbf/shop_area_alien040a_002.dbf                    30,362   30,900      538
/app/alien040/02/oradata/alien040a/dbf/statspack_alien040a_001.dbf                         1      250      249
/app/alien040/02/oradata/alien040a/dbf/users_alien040a_001.dbf                             2      250      248
/app/alien040/03/oradata/alien040a/dbf/exrate_staging_alien040a_001.dbf                    9      200      191
/app/alien040/02/oradata/alien040a/dbf/xdb_alien040a_001.dbf                              89      200      111
/app/alien040/02/oradata/alien040a/dbf/shop_area_alien040a_003.dbf                    30,900   30,900        0
/app/alien040/02/oradata/alien040a/dbf/shop_area_alien040a_004.dbf                    29,318   29,318        0
/app/alien040/03/oradata/alien040a/dbf/undo_alien040a_001.dbf                          1,465    1,465        0
/app/alien040/03/oradata/alien040a/dbf/trans_indexes_alien040a_002.dbf                 2,048    2,048        0

                                                                                                   --------

sum                                                                                                  73,562

22 rows selected.

alter database datafile '/WP05CDD1/WP05CDD1_export/home/oracle/cdot/datafile02/CDTWPH1/datafile2/cnms03.dbf' resize 4097m;

alter database datafile '/WP05CDD1/WP05CDD1_export/home/oracle/cdot/datafile02/CDTWPH1/datafile2/cnms03.dbf' autoextend off;
                                                                               

****************************************************************************************************************************************