| < 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 > |
|