| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: how to release blocks of table?
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
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 ||');');
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: root_at_fatcity.com [mailto:root_at_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_at_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: adar76_at_inter.net.il > > 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: ListGuru_at_fatcity.com (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: MGogala_at_oxhp.com 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: ListGuru_at_fatcity.com (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: j.miranda_at_sermatica.es 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: ListGuru_at_fatcity.com (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).Received on Fri Oct 18 2002 - 07:43:37 CDT
 |
 |