Search This Blog

Monday, January 15, 2007

Dictionary Tablespace

Dictionary Managed Tablespace

SQL> create tablespace DICTBS
2 datafile 'c:\oracle\product\10.1.0\oradata\db02\dictbs01.dbf' size 1m
3 extent management DICTIONARY
4 DEFAULT STORAGE (
5 INITIAL 50K
6 NEXT 100K
7 MINEXTENTS 1
8 MAXEXTENTS 100
9 PCTINCREASE 0);

For Coalesce tablespace.
SQL>alter tablespace &tablespace_name COALESCE;


Note:

The ALTER TABLESPACE ... COALESCE statement does not coalesce free extents that are separated by data extents. If you observe many free extents located between data extents, you must reorganize the tablespace (for example, by exporting and importing its data) to create useful free space extents.
Monitoring Free Space
DBA_FREE_SPACE

Example:
select block_id, bytes,blocks
from dba_free_space
where tablespace_name = '&tbs_name'
order by block_id;

Statistics for coalescing activity
DBA_FREE_SPACE_COALESCED

1 comment:

Anonymous said...

I have read a few of the articles on your website in the present circumstances, and I definitely like your fashionableness of blogging. I added it to my favorites net period muster and disposition be checking promote soon. Please report register in view my orientation as ok and fail me know what you think. Thanks.