Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.

SOLVED
Go to solution
Jack C. Mahaffey
Super Advisor

Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.

I was hoping someone would have a sql script that will list objects that cannot extend due to insufficient datafile/tablespace size.

I'm not looking for something that checks that extents = max_extents in dba_segments.

I want to be able to catch potential problems when indexes or tables cannot extend because there isn't sufficient space.

Thanks in advance...


7 REPLIES
Alexander M. Ermes
Honored Contributor

Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.

Hi there.
First try this for free space in tablespace :
---------------------------------------
set termout off
set feedback off
set pages 65
set lines 60
ttitle " Free space report "
column "TS Name" format a20
column "Sum MB" format 99999.99
column "Max MB" format 99999.99
spool /var/tmp/free1&&2..lst.&&1
select tablespace_name "TS Name",
sum(bytes) / 1048576 "Sum MB",
max(bytes) / 1048576 "Max MB"
from sys.dba_free_space
group by tablespace_name
;
spool off
set termout on
set feedback on
exit
----------------------------------------

for extents check this one :



set heading on
set newpage 0
set pages 69
set feed off
set linesize 78
set verify off

column owner format a10
column tspaces format a7
column object format a32
column type format a5
REM +=======================================================================+
REM next column set to 9999 from 99
REM A. Ermes May 15th 2000
REM +=======================================================================+
column extents format 9999
column maxexts format 9999

break on owner skip 1 on tspaces skip 1

ttitle 'Objects with Extents Allocated Close to Maxextents'

select owner, tablespace_name tspaces, segment_name object,
segment_type type, extents, max_extents maxexts
from dba_segments
where segment_type in ('INDEX','TABLE')
and max_extents - extents < &&1
and segment_name != 'SPACES'
order by owner, tablespace_name, segment_name;

EXIT

------------------
Hope these hints can help
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Volker Borowski
Honored Contributor
Solution

Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.

Hi,
this might need formatting:

SVRMGR> select a.tablespace_name, a.MAXF as MAXFREE, b.MAXS as MAXSIZE
2> from
3> ( select tablespace_name, max(bytes) as MAXF
4> from dba_free_space group by tablespace_name ) a,
5> ( select tablespace_name, max(NEXT_EXTENT) as MAXS
6> from dba_segments group by tablespace_name ) b
7>
8> where
9> a.tablespace_name = b.tablespace_name
10> and
11> a.maxf < b.maxs;

TABLESPACE_NAME MAXFREE MAXSIZE
------------------------------ ---------- ----------
PSAPSOURCED 10313728 10485760
1 row selected.

SVRMGR> -- Which segment is it (quite expensive)
SVRMGR> select c.tablespace_name, c.segment_name, c.bytes, c.next_extent
2> from dba_segments c,
3> (
4> select a.tablespace_name, a.MAXF as MAXFREE, b.MAXS as MAXSIZE
5> from
6> ( select tablespace_name, max(bytes) as MAXF
7> from dba_free_space group by tablespace_name ) a,
8> ( select tablespace_name, max(NEXT_EXTENT) as MAXS
9> from dba_segments group by tablespace_name ) b
10> where
11> a.tablespace_name = b.tablespace_name
12> and
13> a.maxf < b.maxs
14> ) d
15>
16> where c.tablespace_name = d.tablespace_name
17> and c.next_extent = d.maxsize;
TABLESPACE_NAME SEGMENT_NAME BYTES NEXT_EXTEN
-------------------- ---------------------------- ---------- ----------
PSAPSOURCED CROSS 54558720 10485760
PSAPSOURCED EUINFOLI 16384 10485760
PSAPSOURCED EUF4VALUES 24576 10485760
3 rows selected.

Volker
Volker Borowski
Honored Contributor

Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.

yes, this needs formatting....
get attachment with notepad for monospaced layout

Volker
Jack C. Mahaffey
Super Advisor

Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.

First set of SQL examples will be useful when increasing the tablespaces.


I think the second set of examples fit my needs.


Thanks... jack...
John Flanagan
Regular Advisor

Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.

You could try this one. All you need is the select statement.

set serveroutput on
set pagesize 60
set linesize 130
DECLARE
namedb VARCHAR2(50) := '';
BEGIN
select name into namedb from v$database;
dbms_output.put_line(namedb);
dbms_output.put_line('________');
FOR large IN (SELECT owner, bytes, extents, max_extents, segment_name
FROM dba_segments
WHERE extents >= trunc(max_extents * 0.8))
LOOP
dbms_output.put_line(
large.owner||' '||
large.bytes||' '||
large.extents||' '||
large.max_extents||' '||
large.segment_name);
END LOOP;
END;
/
EXIT
/
Reinhard Burger
Frequent Advisor

Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.

Hi
I have added a sql script that will exactly show the information you want to look for (Hopefully)

To run it logon to sqlplus as
db administrator ( you need db admin rights to get the information)
on the sqlplus prompt run the scruipt by typing @db_monitor.sql
or use any other tool where you can copy the script into it and run it

This script has been made for application managers of a n application that uses ORACLE as db engineand where the application managers not personally maintains ORACLE

Cheers Reinhar
keep it simple
Jack C. Mahaffey
Super Advisor

Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.

More useful scripts. Thanks a bunch...

jack...