Hello list
I use this script to do reorganize - redimension.
It show the use/free space of each table and index. You can change it and then
move/rebuild.
You must change USER and TABLESPACE.
Do not do it ONLINE, at least with big tables. (I do it online and get ORA-0600s).
Connect as the table´s owner.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
set serveroutput on size 1000000
set feedback off
set echo off
set linesize 30000
set trimspool on
spool c:\move_tables.sql
prompt ****** Cambiaste USER y TABLESPACE destino??? (pulsa ENTER o CRTL+C):
prompt ****** Conectate como el propietario de las tablas !!!
pause
DECLARE
var1 number;
var2 number;
var3 number;
var4 number;
var5 number;
var6 number;
var7 number;
BEGIN
dbms_output.put_line('set feedback on');
dbms_output.put_line('set echo on');
dbms_output.put_line('spool c:\move_tables.log');
dbms_output.put_line('alter session set SORT_AREA_SIZE=25000000;');
dbms_output.put_line('select to_char(sysdate, ''MM/DD/YYYY HH24:MI:SS'') from dual;');
FOR TB in (select owner, table_name, pct_free, pct_used, ini_trans, max_trans,
initial_extent, decode(next_extent,null,initial_extent,next_extent) nexte,
min_extents, max_extents, pct_increase, freelists, freelist_groups, decode(logging,
'YES', ' logging ', ' nologging ') logg
from dba_tables where owner = 'EPPESETA' order by TABLE_NAME)
LOOP
dbms_output.put_line(chr(0));
dbms_output.put_line('alter TABLE '|| TB.owner ||'.'|| TB.table_name || ' move
tablespace USERS ' || TB.logg
|| chr(10) || ' pctfree ' || TB.pct_free || ' pctused ' ||
TB.pct_used || ' initrans ' || TB.ini_trans || ' maxtrans '|| TB.max_trans
|| chr(10) || ' storage ( initial ' || TB.initial_extent || '
next ' || TB.nexte ||
' minextents ' || TB.min_extents || ' maxextents UNLIMITED '
||
' pctincrease 0 freelists ' || TB.freelists || ' freelist
groups ' || TB.freelist_groups ||');');
-- Espacio ocupado por la tabla
dbms_space.unused_space(''||TB.owner||'',''||TB.table_name||'','TABLE',VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7);
dbms_output.put_line('-- %Libre:'|| round((VAR4*100)/VAR2) ||' Total_KB:'||
VAR2/1024 || ' Usado_KB: ' || ((VAR2/1024)-(VAR4/1024)) ||' Libre_KB:'||VAR4/1024
||' -- Blk_Total:'|| VAR1 || ' Blk_Libres:'||VAR3);
-- Indices de esa tabla
FOR INDX in (select owner, index_name, pct_free, ini_trans, max_trans,
initial_extent, decode(next_extent,null,initial_extent,next_extent) nexte,
min_extents, max_extents, pct_increase, freelists, freelist_groups, decode(logging,
'YES', ' logging ', ' nologging ') logg
from dba_indexes where table_owner=TB.owner and
table_name=TB.table_name order by index_name)
LOOP
dbms_output.put_line(' alter INDEX ' || INDX.owner ||'.'|| INDX.index_name || '
rebuild online '|| INDX.logg
|| chr(10) || ' pctfree ' || INDX.pct_free || ' initrans '
|| INDX.ini_trans || ' maxtrans '|| INDX.max_trans
|| chr(10) || ' storage ( initial ' || INDX.initial_extent ||
' next ' || INDX.nexte ||
' minextents ' || INDX.min_extents || ' maxextents UNLIMITED '
||
' pctincrease 0 freelists ' || INDX.freelists || ' freelist
groups ' || INDX.freelist_groups ||');');
-- Espacio ocupado por el índice
dbms_space.unused_space(''||INDX.owner||'',''||INDX.index_name||'','INDEX',VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7);
dbms_output.put_line('-- %Libre:'|| round((VAR4*100)/VAR2) ||' Total_KB:'||
VAR2/1024 || ' Usado_KB: ' || ((VAR2/1024)-(VAR4/1024)) || ' Libre_KB:'||VAR4/1024
||' -- Blk_Total:'|| VAR1 || ' Blk_Libres:'||VAR3);
END LOOP;
END LOOP;
dbms_output.put_line('select to_char(sysdate, ''MM/DD/YYYY HH24:MI:SS'') from dual;');
dbms_output.put_line('select * from dba_indexes where status<>''VALID'';');
dbms_output.put_line('spool off');
END;
/
spool off
spool c:\extensiones_mon_cache.log
column segment_name format a20
column owner format a10
prompt ****** EXTENSIONES de TABLAS e INDICES. Cambia INITIAL de los siguientes
segmentos (pulsa ENTER):
pause
select owner, segment_name, segment_type, tablespace_name, sum(bytes)/1024 Kb,
count(*) from dba_extents where owner <>'SYS' and segment_type='INDEX' group by
segment_name,owner,segment_type,tablespace_name having count(*)>3 order by count(*);
prompt **************************************************************
select owner, segment_name, segment_type, tablespace_name, sum(bytes)/1024 Kb,
count(*) from dba_extents where owner <>'SYS' and segment_type='TABLE' group by
segment_name,owner,segment_type,tablespace_name having count(*)>3 order by count(*);
prompt ***** MONITORING y CACHE (pulsa ENTER):
pause
select table_name, monitoring, cache from dba_tables where owner='EPSILON' and (cache
not like '%N%' or monitoring<>'NO');
spool off
prompt Indices NO VALIDOS:
select * from dba_indexes where status<>'VALID';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-----Mensaje original-----
De: [EMAIL PROTECTED] [mailto:root@;fatcity.com]En nombre de Gogala, Mladen
Enviado el: jueves, 17 de octubre de 2002 21:00
Para: Multiple recipients of list ORACLE-L
Asunto: RE: how to release blocks of table?
If you are using at least 8i, "alter table move...." should do the trick.
> -----Original Message-----
> From: Yechiel Adar [mailto:adar76@;inter.net.il]
> Sent: Thursday, October 17, 2002 2:20 PM
> To: Multiple recipients of list ORACLE-L
> Subject: how to release blocks of table?
>
>
> I need to release blocks belonging to the initial extent of a table.
> CTAS is not an option.
> Optionally how can I decrease the value of initial extent
> so I can export and import into smaller size.
>
> I have a 7GB database that I need to run a script that was given by
> supplier. This script rebuild all the indexes and I want to
> make sure that
> none are forgotten (~ 700).
> I have enough space for one but not for two. So I thought to
> import with
> rows=no twice, run the scripts against one schema and use
> toad to compare
> the schemas.
> The problem is initial extents in the export file that fill
> all the new DB.
> If I can decrease the initial extents then I will export and
> import the
> whole schema and have enough space.
>
> Yechiel Adar
> Mehish
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Yechiel Adar
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gogala, Mladen
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Juan Miranda
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
