early_warning_maxext.sql
Download Script
set feedback off
set verify off
set echo on
set linesize 80
set pagesize 24
define db = &&1
define outfile = &&2
column segment_name format a24 trunc head "Segment|Name"
column segment_type format a10 trunc head "Segment|Type"
drop table space_report
/
drop table max_next
/
column db new_value new_db
column time new_value new_time
select upper('&&db') db, to_char(sysdate,'DD-MON-YYYY HH24:MI') time from dual
/
spool &&outfile
prompt &&new_db Database Space Report
prompt &&new_time
create table space_report
(tablespace_name char(30),
used_bytes number,
free_bytes number,
largest number,
free_extents number)
/
insert into space_report (tablespace_name, used_bytes)
select tablespace_name, sum(dba_extents.bytes)
from dba_extents
group by tablespace_name
/
insert into space_report (tablespace_name, used_bytes)
(select TABLESPACE_NAME, 0 from dba_tablespaces
where TABLESPACE_NAME not in
(select distinct TABLESPACE_NAME from dba_extents)
)
/
update space_report sr set (free_bytes, largest, free_extents) =
(select sum(fs.bytes), max(bytes), count(*) from dba_free_space fs
where rtrim(sr.tablespace_name) = fs.tablespace_name
group by fs.tablespace_name)
/
prompt
prompt
prompt Objects which next extents WILL NOT FIT in the tablespace:
create table max_next
(tablespace_name char(30),
object_name char(30),
next number,
type char(8))
/
insert into max_next
select dba_tables.TABLESPACE_NAME,
dba_tables.table_name,
dba_tables.NEXT_EXTENT,
'TABLE'
from dba_tables, space_report
where dba_tables.TABLESPACE_NAME = space_report.TABLESPACE_NAME
and space_report.largest < dba_tables.NEXT_EXTENT
/
insert into max_next
select dba_indexes.TABLESPACE_NAME,
dba_indexes.index_name,
dba_indexes.NEXT_EXTENT,
'INDEX'
from dba_indexes, space_report
where dba_indexes.TABLESPACE_NAME = space_report.TABLESPACE_NAME
and space_report.largest < dba_indexes.NEXT_EXTENT
/
insert into max_next
select dba_rollback_segs.tablespace_name,
dba_rollback_segs.segment_name,
dba_rollback_segs.next_extent,
'ROLLBACK'
from dba_rollback_segs, space_report
where dba_rollback_segs.status = 'IN USE'
and dba_rollback_segs.tablespace_name = space_report.tablespace_name
and space_report.largest < dba_rollback_segs.next_extent
/
break on tablespace_name
column tablespace_name format a25
column object_name format a25
column next format 999,999,999
select * from max_next order by tablespace_name
/
prompt
prompt
spool off
drop table max_next
/
drop table space_report
/
exit