From Gerardo.Molina@schwab.com Wed, 31 Oct 2001 00:30:38 -0800 From: "Molina, Gerardo" Date: Wed, 31 Oct 2001 00:30:38 -0800 Subject: RE: Script for next_extent of objects <= free space available Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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).