topic Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size. in Operating System - HP-UX

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

Jack C. Mahaffey — Wed, 24 Apr 2002 12:50:40 GMT

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

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

Alexander M. Ermes — Wed, 24 Apr 2002 13:01:15 GMT

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

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

Volker Borowski — Wed, 24 Apr 2002 14:46:20 GMT

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

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

Volker Borowski — Wed, 24 Apr 2002 14:48:33 GMT

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

Volker

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

Jack C. Mahaffey — Wed, 24 Apr 2002 15:08:43 GMT

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

I think the second set of examples fit my needs.

Thanks... jack...

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

John Flanagan — Fri, 26 Apr 2002 07:26:21 GMT

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
/

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

Reinhard Burger — Fri, 26 Apr 2002 10:34:58 GMT

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

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

Jack C. Mahaffey — Fri, 26 Apr 2002 13:09:05 GMT

More useful scripts. Thanks a bunch...

jack...