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: [EMAIL PROTECTED]
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: [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: Molina, Gerardo
INET: [EMAIL PROTECTED]
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: [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).
