RE: how to release blocks of table?
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).
Re: how to release blocks of table?
Thanks Mladen and Lee I am always amazed by the ability and knowledge of the people on this list. You guys (all the list members) always come up with many options to do something so if some option does not work you have others. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Thursday, October 17, 2002 9:13 PM > > > > -Original Message- > > > > The way to decrease the initial size is 'alter table xxx > > deallocate unused > > keep nnn'. > > I found it in the doc after sending the previous msg. > > > > Another possibility: If the storage specifications for the tables in the > database are acceptable, and you are the one doing the export, you can > specify "compress=n" when you do the export. Then the initial extent will > be as specified when the original table was created. Some shops create > large tables with a small initial extent for this reason: When they only > want the schema structure in someplace else, they can get it without the big > initial extents. > > You can also use emacs to directly modify the export file to change the > storage parameters therein. > > Something similar to the following: > > Create the file fix-extents.el with the following: > (beginning-of-buffer) > (while (re-search-forward "INITIAL[ 0-9]*NEXT[ 0-9]*MINEXTENTS" nil t) > (replace-match "INITIAL 1M NEXT 1M MINEXTENTS" nil nil)) > (save-buffer) > Run the following to fix the dump file test.dmp: > emacs -batch test.dmp -l fix-extents.el > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Stephen Lee > 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: 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).
RE: how to release blocks of table?
> -Original Message- > > The way to decrease the initial size is 'alter table xxx > deallocate unused > keep nnn'. > I found it in the doc after sending the previous msg. > Another possibility: If the storage specifications for the tables in the database are acceptable, and you are the one doing the export, you can specify "compress=n" when you do the export. Then the initial extent will be as specified when the original table was created. Some shops create large tables with a small initial extent for this reason: When they only want the schema structure in someplace else, they can get it without the big initial extents. You can also use emacs to directly modify the export file to change the storage parameters therein. Something similar to the following: Create the file fix-extents.el with the following: (beginning-of-buffer) (while (re-search-forward "INITIAL[ 0-9]*NEXT[ 0-9]*MINEXTENTS" nil t) (replace-match "INITIAL 1M NEXT 1M MINEXTENTS" nil nil)) (save-buffer) Run the following to fix the dump file test.dmp: emacs -batch test.dmp -l fix-extents.el -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee 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).
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).
