Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Defragmentation
Hi
Before you import/export and/or RTFM, I would like you to try this on one or two tablespaces.
The following SQL*Plus script report will tell you whether you have fragmentation that is contiguous or not. If it is contiguous then you can use the following command to COALESCE the free space.
Run the script, check the fragmentation.
alter tablespace t_name coalesce;
Run the script and check if it has helped.
If it has helped then,do the following for all tablspaces ( not the tables ) except Oracle created tablespaces such as SYSTEM, RBS, TOOLS etc.
alter tablespace t_name default storage ( pctincrease 1 );
What this does is sets the pctincrease for tablespaces to other than 0, in which case the Oracle background process SMON, COALESCEs the contiguous free space autoamtically every time it wakes up which is about every couple of minutes or so.
I would like to know if it works for you, because I spent a lot of time on this research and I would like confirmation.
I cannot yet say for sure whether many extents or one extent is the way to go. Until I experiment with it, I prefer 1 extent.
Thanks.
Good luck !!!
Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com
set dof off pause off
/************************************************************************
* *
* AUTHOR : Oracleguru *
* FILE NAME : analyze_tablespace_usage.sql *
* LANGUAGE : SQL/SQL*Plus/PL-SQL *
* INPUT PARAM : This script expects 1 parameter: Tablespace_name *
* OUTPUT PARAM: None. *
* INPUT FILES : None. *
* OUTPUT FILES: $HOME/rep/analyze_tablespace_names_TNAME.lst *
* where TNAME = Tablespace_name entered on *
* command line to run this script *
* *
* DESCRIPTION : This script generates tablespace usage for a *
* TABLESPACE name entered on the command line. *
* *
************************************************************************/
set pause off termout off verify off wrap on serveroutput on size 1000000 set newpage 0 pagesize 58 linesize 80
/*
Generate tablespace analysis report only on Wednesdays. Uncomment the following statements if this is what you want to do.
whenever sqlerror exit
select 1/0
from dual
where to_char(sysdate, 'day') not in ('wednesday');
*/
clear breaks
column today new_value today noprint column time new_value time noprint
/*
Remove report file if exists, otherwise in case of problem, you may
receive a print out of an old file.
*/
host rm -f $HOME/rep/analyze_tablespace_usage_&1..lst
spool $HOME/rep/analyze_tablespace_usage_&1..lst
set feedback off
ttitle today center 'TABLE AND INDEX SPACE USAGE IN TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 1 -
analyze_tablespace_usage.sql
select to_char(sysdate, 'DD-MON-YYYY') today,
to_char(sysdate, 'HH:MI:SS AM') time from dual;
set feedback on
/*
Compute total and unused disk space in tablespace. */
DECLARE
total_blocks number; total_bytes number; unused_blocks number; unused_bytes number;/* Above High water mark */ last_used_extent_file_id number; last_used_extent_block_id number; last_used_block number; free_space number;/* Below High water mark */ user_id all_tables.owner%type; object_name all_tables.table_name%type; object_type varchar2 ( 5);
-- cursor c1 is select owner, table_name, 'TABLE' from all_tables where tablespace_name = upper('&1') union select owner, index_name, 'INDEX' from all_indexes where tablespace_name = upper('&1'); BEGIN open c1; LOOP fetch c1 into user_id, object_name, object_type; exit when c1%notfound; dbms_space.unused_space ( user_id, object_name, object_type, total_blocks, total_bytes, unused_blocks, unused_bytes, last_used_extent_file_id, last_used_extent_block_id, last_used_block ); dbms_space.free_blocks ( user_id, object_name, object_type, 0, free_space); -- dbms_output.put_line ( object_type||' Name = '||user_id||'.'||object_name ); dbms_output.put_line ( '**********' ); dbms_output.put_line ( 'Total Bytes = '||to_char(total_bytes, '999,999,990')|| ' Free Bytes above Water Mark = '|| to_char(unused_bytes,'999,999,990') ); dbms_output.put_line ( 'Bytes to W.Mark= '||to_char(total_bytes - unused_bytes,'999,999,990')|| ' Free Bytes below Water Mark = '|| to_char(free_space*8192, '999,999,990') ); dbms_output.put_line ( '------------------------------------------------------------------------- -------'); END LOOP; END; .. / column object format a26 heading 'OBJECT' column file_id format 99990 heading 'FILE|ID ' column block_id format 999990 heading 'BLOCK|ID ' column blocks format 99990 heading 'BLOCKS' column bytes format 9,999,999,999 heading 'BYTES' ttitle today center 'POSSIBLE FRAGMENTATION IN TABLESPACE &1' - right 'Page ' format 990 sql.pno skip 1 - time - skip 2 - analyze_tablespace_usage.sql skip 2 select 'freespace' owner, ' ' object, file_id, block_id, blocks, bytes bytes from sys.dba_free_space where tablespace_name = upper('&1') union select substr(owner, 1, 15), substr(segment_name, 1, 31), file_id, block_id, blocks, bytes bytes from sys.dba_extents where tablespace_name = upper('&1') order by 3, 4 / break on bytes on report compute sum of bytes on report column segment_name format a26 column tablespace_name format a15 ttitle today center 'SPACE USAGE BY OBJECTS IN TABLESPACE &1' - right 'Page ' format 990 sql.pno skip 1 - time - skip 2 - analyze_tablespace_usage.sql skip 2 select segment_name, tablespace_name, sum(bytes) bytes from sys.dba_extents where tablespace_name = '&1' group by segment_name, tablespace_name / set feedback off column space heading ' SPACE' column name heading ' INDEX NAME' column btree_space format 9,999,999,999 heading 'TOTAL SPACE' column used_space format 9,999,999,999 heading 'USED SPACE' ttitle today center 'USED AND FREE SPACE USAGE FOR TABLESPACE &1' - right 'Page ' format 990 sql.pno skip 1 - time - skip 2 - analyze_tablespace_usage.sql skip 2 select 'Free Space ' space, sum(bytes ) bytes from sys.dba_free_space where tablespace_name = upper('&1') union select 'Used Space ' space, sum(bytes ) bytes from sys.dba_extents where tablespace_name = upper('&1') / prompt prompt prompt. ********** END OF REPORT ********* spool off exit Robert Jungerius <rjungerius_at_death-to-spam.cetecint.com> wrote in article <01bde2d9$a51a8080$270164bf_at_wks139>... > Hello, I'm a Oracle DBA working with Oracle 7.3.4 on the NT4 platform. > The only way of defragmentation I know of is to export, drop user, create > user and import. > This is impossible with databases of 10Gb or more, because the downtime is > unacceptable and the > diskspace needed for this operation is outrageous! > I'm having serious problems keeping the databases of all customers running > smoothly! > > How do you defragment a very large database? > And does anybody have some tips concerning performance tuning? > > Thanx in advance. > > Robert Jungerius, > DBA, Software Engineer @ CETEC BV > rjungerius_at_yahoo.com >Received on Fri Sep 18 1998 - 00:00:00 CDT