resizing datafiles

[PaulH]

All, I am trying to clean things up a bit. Somebody made my datafiles huge
and I have a feeling they are way over kill. I want to resize the datafiles
in an effort to get back some space.

Is there a query I can use to show me how much space is not used in a
a datafile?

Any example would be greatly appreciated.

[DaPi]

I used this a long time ago for TableSpaces. It might be OK as is or you can use it as a starting point. Caveat emptor.

TTITLE 'Table Spaces'
----------------------------------------------
-- list tablespace Usage,MaxFree,Fragment
-- author: kerlion he
-- location:beijing,china
-- date: 15-may-2002
----------------------------------------------
set pagesize 80
set linesize 80
col tablespace_name for a18
COL total for 999,999 heading "MB|Total"
COL free for 999,999 heading "MB|Free"
COL max_free for 999,999 heading "MB|Extent|MaxFree"
col used_pct for 990.99 heading "Used|percnt"
col free_cnt for 9999 heading "Free|Extent|Count"
select a.tablespace_name,
round(100-b.free/a.total*100,2) Used_pct,
a.total,
b.free,
b.max_free,
b.free_cnt
from
(select tablespace_name,
sum(bytes)/1024/1024 total
from dba_data_files
group by tablespace_name
) a,
(select tablespace_name,
sum(bytes)/1024/1024 free,
max(bytes)/1024/1024 max_free,
count(bytes) free_cnt
from dba_free_space
group by tablespace_name
) b
where a.tablespace_name=b.tablespace_name ;

[gandolf989]

You can also use this script. It will give you the high water mark in each data_file within each tablespace. That should help you to decide if you are able to shrink any datafiles.

SET ECHO         OFF
SET SERVEROUTPUT ON
SET LINESIZE 140

DECLARE
   CURSOR cur_ts IS
      SELECT tablespace_name, file_id,
             substr(file_name, instr(file_name, '/', -1)+1) name,
             ROUND(SUM(bytes)/1024/1024) totalMB,
             TO_CHAR(ROUND(SUM(bytes)/1024/1024), '999,999') totalMBV
        FROM dba_data_files
       GROUP BY tablespace_name, file_id,
                substr(file_name, instr(file_name, '/', -1)+1)
       ORDER BY 1, 3;

   TYPE lineDetailRec IS RECORD 
      ( HighWaterMarkMB  VARCHAR2(32),
        freeMB           VARCHAR2(32),
        largestFreeSpace VARCHAR2(32),
        pctUsed          VARCHAR2(32) );

   lineDetail lineDetailRec;

   lv_fs       NUMBER := 0;
   percent_fs  NUMBER := 0;
   large_fs    NUMBER := 0;
   blocksize   NUMBER := 0;
   lv_name     v$database.name%TYPE;

BEGIN
   DBMS_OUTPUT.enable(1000000);

   SELECT value
     INTO blocksize
     FROM v$parameter
    WHERE name = 'db_block_size';

   SELECT name 
     INTO lv_name 
     FROM v$database;

   DBMS_OUTPUT.PUT_LINE('***********'||lv_name||
      ' Database -- Tablespace Summary - Freespace Report ***************');

   DBMS_OUTPUT.PUT_LINE('                    '     ||
                        '                    '     ||
                        'Total    '                ||
                        'Hwater    '               ||
                        'Free    '                 ||
                        '   LG   '                 ||
                        ' %Free ');

   DBMS_OUTPUT.PUT_LINE('Tablespace name     '     ||
                        'File name         '       ||
                        ' SpaceMB   '              ||
                        ' Mark   '                 ||
                        ' SpaceMB  '               ||
                        ' FreeMB '                 ||
                        ' Space  ');

   DBMS_OUTPUT.PUT_LINE('****************** '      ||
                        '****************** '      ||
                        ' ******** '               ||
                        ' ******* '                ||
                        ' ******* '                ||
                        ' ******* '                ||
                        ' ******');

   FOR lv_cur_ts IN cur_ts LOOP
      SELECT TO_CHAR(hwm.HighWaterMarkMB, '999,999'),
             TO_CHAR(fs.freeMB,           '999,999'),
             TO_CHAR(fs.largestFreeSpace, '999,999'),
             TO_CHAR(NVL(ROUND( (freeMB/lv_cur_ts.totalMB)*100, 1), 0), '9990.0')
        INTO lineDetail
        FROM ( SELECT ROUND(SUM(BYTES)/1024/1024) freeMB,
                      ROUND(MAX(bytes)/1024/1024) largestFreeSpace
                 FROM dba_free_space
                WHERE tablespace_name = lv_cur_ts.tablespace_name
                  AND file_id         = lv_cur_ts.file_id) fs,
             ( SELECT NVL(MAX(block_id), 0)*blocksize/1024/1024 HighWaterMarkMB
                 FROM dba_extents
                WHERE tablespace_name = lv_cur_ts.tablespace_name
                  AND file_id         = lv_cur_ts.file_id ) hwm;

      DBMS_OUTPUT.PUT_LINE(RPAD(lv_cur_ts.tablespace_name,   19) ||
                           RPAD(lv_cur_ts.name,              18) ||
                           LPAD(lv_cur_ts.totalMBV,           9) ||
                           LPAD(lineDetail.HighWaterMarkMB,   9) ||
                           LPAD(lineDetail.freeMB,            9) ||
                           LPAD(lineDetail.largestFreeSpace,  9) ||
                           LPAD(lineDetail.pctUsed,           8));
   END LOOP;
END;
/

SET ECHO ON

[mahajanakhil198]

just to ask something related to this:

Suppose I have a table that is facing lot of deletions. Extent management is local and Segment Space management is auto.

Is there any query or view that can tell me how much space I will be able to recover if I recreate this table (using any means such as export/import or by recreating it with a different name in same tablespace) or move it to other tablespace?

Oracle 10g automatic segment advisor though is of help but still I want to see it at SQL level.

[davey23uk]

just to ask something related to this:

Suppose I have a table that is facing lot of deletions. Extent management is local and Segment Space management is auto.

Is there any query or view that can tell me how much space I will be able to recover if I recreate this table (using any means such as export/import or by recreating it with a different name in same tablespace) or move it to other tablespace?

Oracle 10g automatic segment advisor though is of help but still I want to see it at SQL level.

why do you care? the space will be reused

[mahajanakhil198]

why do you care? the space will be reused

Indeed it will be. But only if there are insertions. Though segment space management is auto and PCTUSED and PCTFREE are taken care of automatically. But still if used space in many blocks is around 50 percent and default PCTUSED of 40 percent(suppose) does not make it a candidate for insertions.

I was going through this thread and this question just arose in my mind. So felt like asking it if somebody has an idea of it.