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:[EMAIL PROTECTED]]
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: [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).
--
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).
