Re: Max Extents question.

Answers embedded.....  

> I have a statement that monitors the extents usage of Tablespaces.

That statement in itself is misleading. You need to monitor the extent usage of segments, not of (or by) tablespaces.

When you create a tablespace, you can specify the maxextents (say 505) in the storage clause. If you then create a segment (table or index) in that tablespace without specifying the storage clause, it takes on the storage clause specified at the tablespace level. But if the table is created with a storage clause of its own (with maxextents 1024), the tablespace level storage is ignored. So a table can have 775 extents in a tablespace of maxtentents 505 because that table was created with a maxextents of 1024.

> when off-commenting the clause on the SYSTEM-tablespace:
> It appears that SYSTEMs extents-usage is far beyond 100% percent!

The script is wrong in that it is giving you the percentage of the actual number of extents in the tablespace to the "maxextents that a segment would default to, if created without one". You need the percentage of extents in a segment to the maxextents for that segment. So in your script, instead of using dba_tablespaces.max_extents, use dba_segments.max_extents and rewrite the script accordingly.

>
> SELECT e.tablespace_name "Tablespace"
> , COUNT(*) "Used extents"
> , t.max_extents "Max extents"
> , ROUND((COUNT(*) / t.max_extents), 2) * 100 "Pct extents used"
> FROM dba_extents e, dba_tablespaces t
> WHERE t.tablespace_name = e.tablespace_name
> -- AND t.tablespace_name <> 'SYSTEM'
> GROUP BY e.tablespace_name, t.max_extents;
>
>
> Tony,
> Oracle DBA
Received on Wed Jan 30 2002 - 11:21:57 CST