- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Looking for Oracle sql that will list objects...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-24-2002 05:50 AM
тАО04-24-2002 05:50 AM
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...
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-24-2002 06:01 AM
тАО04-24-2002 06:01 AM
Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-24-2002 07:46 AM
тАО04-24-2002 07:46 AM
Solutionthis 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-24-2002 07:48 AM
тАО04-24-2002 07:48 AM
Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.
get attachment with notepad for monospaced layout
Volker
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-24-2002 08:08 AM
тАО04-24-2002 08:08 AM
Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.
I think the second set of examples fit my needs.
Thanks... jack...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2002 12:26 AM
тАО04-26-2002 12:26 AM
Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.
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
/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2002 03:34 AM
тАО04-26-2002 03:34 AM
Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2002 06:09 AM
тАО04-26-2002 06:09 AM
Re: Looking for Oracle sql that will list objects that cannot extend due to ftablespace size.
jack...