14.2 Space Used in a Database

 < Day Day Up > 



There can be a lot of wasted space in datafiles because of varyingsized extents where new extents cannot fit into smaller, older empty extents. This type of behavior was common in older versions of Oracle Database where the default for PCTINCREASE for dictionary-managed tablespaces was set to 50%. Extents can be coalesced and possibly reused by joining adjacent extents together. In my experience coalescence and deallocation was not very effective.

Note 

 Oracle Database 10 Grid   Dictionary-managed tablespaces will be deprecated in a future version of Oracle Database.

The query result shown notes the full database size of my testing database as 4,489 Mb, where about 75% of that space is used. This means that 25% of currently allocated extent space is wasted or not as yet used. This space could simply be allocated space but it could also be deleted space not reused as of yet.

*****          %Free  %Used  Mb Free  Mb Used   Size -------------  -----  -----  -------  -------   ---- Database Size     22     78      998     3491   4489 

Here is the same script expanded for all tablespaces in the database. All tablespaces are locally managed.

Tablespace  %Free  %Used  Mb Free  Mb Used ----------  -----  -----  -------  ------- SYSTEM          2     98        7      403 OEM             0    100        0      749 CWMLITE        53     47       11        9 DRSYS          52     48       10       10 RBS            95      5      690       33 USERS          48     52       30       32 ODM            53     47       11        9 TOOLS          39     61        4        6 DATA           17     83      165      812 XDB             0    100        0       38 INDX            5     95       66     1381 OBJECTS        31     69        4        9 SMALLDATA      75     25        0        0

The scripts to produce these queries, along with scripts displaying sizes for all table and index segments and extents, are included in Appendix B.



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net