no. of extent in tablespace

[feroz]

Hi,

I need some help regarding the no. of extents in tablespace that has been allocated. In my dba_tablespace, few tablespaces was set to maxextent of 1024. But when I query dba_extents or dba_segments for no. of extents group by tablespace I got a result of bigger than 1024.

Am I missing something here? Anybody could explain to me, please? What I want to do is to get total no. of extent for all tablespaces available in my database.

regards,
feroz

[Shestakov]

SQL> select tablespace_name, count(*)
from dba_extents
group by tablespace_name;

TABLESPACE_NAME COUNT(*)
------------------------------ ----------
INDX 207
SYSTEM 754
TEMP 94
TOOLS 232
USERS 192

...
About 1024 extents :
Don't worry about that, this is "INTERNAL" extents for keep db objects,
U have to worry about "EXTERNAL" extents in AUTOEXTENT clause
for commad alter tablespace add datafile ...,
but u can just set this parameter to UNLIMITED.
----------------------------------------------------------------------------
NOTE:
parameter maxextent --> is parameter of DATAFILE (not tablespase),
and doesn't matter where u set this parameter:
-- in create tablespace command or
-- in alter tablespace add datafile.

[Edited by Shestakov on 07-09-2002 at 11:07 AM]

[sreddy]

Probably, you are mistaken with DBA_SEGMENTS column MAX_EXTENTS which represents the 'Maximum # of extents of segment '

[feroz]

Hi,

I used the same query to get the no. of extents for each tablespace. What I'm confuse here is the maxextent set to 1024 in dba_tablespaces but the query result is more than 1024.

Anybody could explain why it goes more than 1024?

regards,
feroz

[Shestakov]

See more detail infomation about tablespaces, extents ...

http://download-east.oracle.com/otnd...4space.htm#924
http://download-east.oracle.com/otnd...block.htm#2528

This is answer for ur question.

[sreddy]

Try this query and see the results match your max_extents defined in tablespace storage clause or not.

select tablespace_name, max_extents
from dba_tablespaces
group by tablespace_name ,max_extents

[sreddy]

I think you are confused with the same/similar column name of different views. The definition of column of same/similar name is different from one view to another view.

Check out Oracle8i Reference for descriptions of the DBA views.

[Shestakov]

I think u see 1024 as maxextents in dba_tablespaces:

in dba_tablespaces this parameter describe DEFAULT VALUE of STORAGE clause
and !!! this parameter act FOR EACH DB OBJECT SEPARETLY (not for whole tablespace)
when u create new db object (table, index ...), that has extents,
and u don't describe value of this parameter (in STORAGE clause) espesialy for
new object in create statment.

Then if u want to check # of extents for each object in tablespases u can use:
SQL> select tablespace_name, owner, segment_name, segment_type, count(*)
from dba_extents
group by tablespace_name, owner, segment_name, segment_type
having count(*) > 50;
TABLESPACE_NAME OWNER SEGMENT_NAME SEGMENT_TYPE COUNT(*)
------------------ ---------------------- ------------------------------------------------- ----------- ----------
SYSTEM SYS SOURCE$ TABLE 64
TEMP SYS 4.2 TEMPORARY 94

[feroz]

Hi,

Thank you for pointing me to correct way.


regards,
feroz