From clchan@nie.edu.sg Wed, 31 Oct 2001 01:51:15 -0800 From: "CHAN Chor Ling Catherine (CSC)" Date: Wed, 31 Oct 2001 01:51:15 -0800 Subject: RE: Script for next_extent of objects <= free space available Message-ID: MIME-Version: 1.0 Content-Type: text/plain Hi Gerardo, I notice that in your script the free space is also derived from dba_free_space (same as my script). Although I issued the command alter database set autoextend on next ??M for the datafile, the table dba_free_space does not reflect this changes. Any other advice ? Thanks. Regds, New Bee -----Original Message----- From: Molina, Gerardo [mailto:Gerardo.Molina@schwab.com] Sent: Wednesday, October 31, 2001 4:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: Script for next_extent of objects <= free space available Try this query to show objects whose next extent is larger than the largest free extent in the respective tablespace. set linesize 120 col owner format a10 col segment_name format a20 col type format a7 col tablespace format a15 select a.tablespace_name tablespace, segment_type type,owner,segment_name, a.next_extent/1024 next,big_chunk from dba_segments a, (select tablespace_name, max(bytes/1024) as big_chunk from dba_free_space group by tablespace_name ) b, dba_tablespaces c where a.segment_type in ('TABLE','INDEX') and b.tablespace_name = a.tablespace_name and a.tablespace_name = c.tablespace_name and c.status != 'READ ONLY' and a.next_extent/1024 > b.big_chunk; HTH Gerardo -----Original Message----- Sent: Tuesday, October 30, 2001 11:10 PM To: Multiple recipients of list ORACLE-L Hi Gurus, I am looking for a script that shows the list of tables with next extent >= the free space available in the tablespace. Does anyone have the script ? I write a script displaying the list of table-spaces with inadequate space for the next extent of the table SELECT F.TABLESPACE_NAME,NEXT_EXTENT,BYTES FREE_SPACE FROM (SELECT TABLESPACE_NAME,MAX(NEXT_EXTENT) NEXT_EXTENT FROM DBA_TABLES GROUP BY TABLESPACE_NAME) T, (SELECT TABLESPACE_NAME,MAX(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME AND F.BYTES <= T.NEXT_EXTENT Example : TABLESPACE_NAME NEXT_EXTENT FREE_SPACE ------------------------------ ----------- ---------- XTRD 8388608 3112960 Based on the example above, although I alter database to set the data file pertaining to the tablespace_name XTRD autoextend on for the next 10M, the bytes in dba_free_space will still reflect as 3112960. My script will not work. Any advise ? Thanks. SQL> SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='XTRD'; FILE_NAME ---------------------------------------------------------------------------- ------------------------ FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES --------- ------------------------------ --------- --------- --------- ------------ --- --------- MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS --------- ------------ ---------- ----------- /dg7/app/oracle/testdata/xtrd01.dbf 29 XTRD 20971520 2560 AVAILABLE 29 NO 0 0 0 20889600 2550 I issued the command to set autoextend on for the datafile '/dg7/app/oracle/testdata/xtrd01.dbf'. SQL> SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='XTRD'; FILE_NAME ---------------------------------------------------------------------------- --------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MA --------- ------------------------------ --------- --------- --------- ------------ --- --- MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS --------- ------------ ---------- ----------- /dg7/app/oracle/testdata/xtrd01.dbf 29 XTRD 20971520 2560 AVAILABLE 29 YES 3.4 4194302 2560 20889600 2550 SQL> SELECT * FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='XTRD'; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------------------------ --------- --------- --------- --------- ------------ XTRD 29 2179 3112960 380 29 Regds, New bee -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) INET: clchan@nie.edu.sg Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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: Molina, Gerardo INET: Gerardo.Molina@schwab.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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: CHAN Chor Ling Catherine (CSC) INET: clchan@nie.edu.sg Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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).