-
resizing datafiles
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.
-
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.
Code:
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 ;
Last edited by DaPi; 09-22-2009 at 05:23 AM.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
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.
Code:
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
-
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.
lucky
-
Originally Posted by 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.
why do you care? the space will be reused
-
Originally Posted by davey23uk
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.
lucky
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|