Script for next_extent of objects <= free space available

From: CHAN Chor Ling Catherine (CSC) <clchan_at_nie.edu.sg>
Date: Tue, 30 Oct 2001 23:06:16 -0800
Message-ID: <F001.003B8D35.20011030231020@fatcity.com>

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).

Received on Wed Oct 31 2001 - 01:06:16 CST