RE: Script for next_extent of objects <= free space available

From: Molina, Gerardo <Gerardo.Molina_at_schwab.com>
Date: Wed, 31 Oct 2001 00:30:38 -0800
Message-ID: <F001.003B8DB9.20011031002020@fatcity.com>

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

Regds,
New bee

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: CHAN Chor Ling Catherine (CSC)
  INET: clchan_at_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_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: Molina, Gerardo
  INET: Gerardo.Molina_at_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_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 Wed Oct 31 2001 - 02:30:38 CST