More and more we are using locally managed tablespaces. They offer a large amount of benefits, so why should we not use this new feature?
Some thoughts are needed when you decided to use Uniform Extent Allocation. With the uniform method, you specify an extent size when you create the tablespace, and all extents for all objects created within that tablespace will be that size.
The uniform method also provides an enforcement mechanism, because you can’t override the uniform extent size of locally managed tablespaces when you create a schema object such as a table or an index.
Calculate the Size of Tablespaces
The goal is to allocate as much disk space as really needed and as really used. With the uniform extent allocation you can calculate or even estimate the number of extents you want to allocate. Gaps or unused disk space within the tablespace should be avoided.
Lets assume that we create a tablespace with the uniform extent size of 1 MByte and 10 extents. Remember that locally managed tablespaces will use another 64 KBytes or the Header Bitmap:
10 * 1 * 1024K + 64K = 10304K
Note that all calculations are made in KBytes and that your chosen extent size is the multiple of your defined block size. The following statement creates this locally managed tablespace with a uniform extent size of 1 MByte:
CREATE TABLESPACE uni_test
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;
Check the Size and Number of Extents
Now every object created within the newly created tablespace gets its uniform extent size of 1 MByte:
CREATE TABLE tab_1 (
) TABLESPACE uni_test;
CREATE TABLE tab_2 (
) TABLESPACE uni_test
CREATE TABLE tab_3 (
) TABLESPACE uni_test
If you are including a STORAGE clause when you create tables or indexes, Oracle will allocate as much extents as you indicate to use. Table TAB_1 will be allocated with one extent, table TAB_2 too because you need at least 100 KBytes. Table TAB_3 will be created with two extents. This could also be done by defining an INITIAL value of 2 MBytes.
The allocated blocks and extents can be verified using the view DBA_SEGMENTS:
SELECT segment_name, segment_type, blocks, extents
WHERE owner = 'TEST'
ORDER BY EXTENTS
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
-------------------- ------------------ ---------- ----------
TAB_1 TABLE 256 1
TAB_2 TABLE 256 1
TAB_3 TABLE 512 2
The free space in the tablespace UNI_TEST can be verified using the view DBA_FREE_SPACE:
SELECT tablespace_name, bytes, blocks
WHERE tablespace_name = 'UNI_TEST'
TABLESPACE_NAME BYTES BLOCKS
------------------------------ ---------- ----------
UNI_TEST 6291456 1536
That means in the tablespace UNI_TEST are still 1536 blocks available. How many extents are these blocks? This can be calculated by multiplying the number of available blocks by the block size and divided by the extent size:
1536 * 4K / 1024K = 6 extents
That fits with our calculations and verifications: 4 extents are already used and another 6
extents could be used to fill up the whole tablespace.
Check the File Size
If you check the physical file size used for the tablespace UNI_TEST you will be surprised: Instead of the calculated 10304 KBytes (10'551'296 Bytes) you will find the disk file’s size of 10'555'392 Bytes. Oracle allocates another block which can not be used for object allocation. Some of the Oracle tools such as the Tablespace Manger shows the total number of blocks according to the disk file size. In our example this are 2577 blocks, but usable are only 2576 blocks minus 64 KBytes (for header bitmap).
Keep the following rules in mind during the sizing of tablespaces:
Each extent size is the multiple of your defined block size.
The usable tablespace size is the multiple of your estimated number of extents.
The defined tablespace size used during CREATE TABLESPACE statement adds 64
KBytes for the header bitmap (HB) to the usable tablespace size.
The physical file size adds one block (AB) to the defined tablespace size.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60571/，如需转载，请注明出处，否则将追究法律责任。