Re: More questions on tablespaces

On Thu, 20 Apr 2000 18:25:29 GMT, argosy22_at_my-deja.com wrote:

>HI everyone,
>
>Some more questions on tablespaces.
>
>I'm reverse engineering a database on our production
>server, for use on a new staging server. I queried
>the data dictionary and found:
>
>
> 1 Select ts.TABLESPACE_NAME ,
> 2 df.file_name ,
> 3 ts.INITIAL_EXTENT ,
> 4 ts.NEXT_EXTENT ,
> 5 ts.MIN_EXTENTS ,
> 6 ts.MAX_EXTENTS ,
> 7 ts.PCT_INCREASE ,
> 8 vdf.BYTES,
> 9 vdf.CREATE_BYTES
> 10 from dba_tablespaces ts,
> 11 dba_data_files df,
> 12 v$datafile vdf
> 13 where ts.TABLESPACE_NAME = df.TABLESPACE_NAME
> 14 and vdf.name = df.FILE_NAME
> 15* order by 1, 2
>
>
>TABLESPACE_NAME
>------------------------------
>FILE_NAME
>------------------------------------------------------------------------
>--------
>INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
>BYTES
>-------------- ----------- ----------- ----------- ------------
>----------
>CREATE_BYTES
>------------
>INDEX_TBS
>/index/???.dbf
> 16384 16384 1 121 10
>209715200
> 209715200
>
>RBS
>/rollback/???.dbf
> 26214400 1048576 1 505 0
>209715200
> 209715200
>
>
>ROLLBACK_TBS
>/rollback/???.dbf
> 16384 16384 1 121 10
>209715200
> 209715200
>
>...
>
>TEMP
>/index/???.dbf
> 10485760 1048576 1 505 0
>209715200
> 209715200
>
>...
>
>
>What strikes me as odd is for some tablespaces (ROLLBACK_TBS, and
>INDEX_TBS):
>- some extents are only 16K (1024*16=16384)
> (Yet I think the default is 160K)
>- the max extents are only 121
>(indicating max 1982464 bytes that will be used, or 1.89M)
>- but the file is given a whole 200M for storage
>
>
>Is only 1.89M being used in some of these tables of 200M?
>Or, is 16384, not in fact 16K, but something else?
>
>Or, am I missing something here?
>
>
>Thanks,
>
>Argosy
>
>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

No,
you seem to confuse the storage clause and the default storage clause which is defined on tablespace level.
If I don't give an object an explicit storage, the default storage clause is used. In that case the default storage clause is copied to the storage clause of the object and can always be altered afterwards. So, if you don't specify and never change anything, yes: objects created without a storage clause in the tablespaces you mention will reach a maximum on 1.89M. You need to look at dba_segments and dba_extents to see what is actually used, the calculation you make is most likely incorrect.

Hth,

Sybrand Bakker, Oracle DBA Received on Thu Apr 20 2000 - 00:00:00 CDT