After looking at the tag (or) title name you find that, I am discussing the same old concept. If that’s correct, then certainly it would depend on the things, how we look at. Okay, let me jump to the practical's, we all know and studied across the tablespace's (extent management and segment space management). I am not going to discuss the things from internal, just laying the path to study and understand when certain things created what things and size it does take internally.
So, Extent Management can be of Auto-allocate (Default) or Uniform. Let’s see a demo,
1: C:\Windows\system32>sqlplus /@test as sysdba
2:
3: SQL*Plus: Release 11.2.0.1.0 Production on Sun May 22 15:11:43 2011
4:
5: Copyright (c) 1982, 2010, Oracle. All rights reserved.
6:
7:
8: Connected to:
9: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
10: With the Partitioning, OLAP, Data Mining and Real Application Testing options
11:
12: SQL> create tablespace lmt_demo_uni1 datafile 'D:\lmt_uni_datafile2.dbf' size 10M
13: 2 extent management local;
14:
15: Tablespace created.
16:
17: SQL> create tablespace lmt_demo_uni3 datafile 'D:\lmt_uni_datafile4.dbf' size 10M
18: 2 extent management local uniform size 30k;
19: create tablespace lmt_demo_uni3 datafile 'D:\lmt_uni_datafile4.dbf' size 10M
20: *
21: ERROR at line 1:
22: ORA-03249: Uniform size for auto segment space managed tablespace should have
23: atleast 5 blocks
If you check across the snippet the we came across the issue “ORA-03249: Uniform size for auto segment space managed tablespace should have atleast 5 blocks”
So, we can create an tablespace of size 5 * db_block_size (minimum) with out any issues – It’s size of metadata (bitmaps – default segment type is system managed). Let’s create and check across..
Current db_block_size 8192
1: SQL> create tablespace lmt_demo_uni3 datafile 'D:\lmt_uni_datafile4.dbf' size 10M
2: 2 extent management local uniform size 35k;
3:
4: Tablespace created.
5:
6: SQL> select * from dba_free_space
7: 2 where tablespace_name='LMT_DEMO_UNI1';
8:
9: TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
10: ------------------------------ ---------- ---------- ---------- ----------
11: RELATIVE_FNO
12: ------------
13: LMT_DEMO_UNI1 8 128 9437184 1152
14: 8
15:
16:
17: SQL> select * from dba_free_space
18: 2 where tablespace_name='LMT_DEMO_UNI3';
19:
20: TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
21: ------------------------------ ---------- ---------- ---------- ----------
22: RELATIVE_FNO
23: ------------
24: LMT_DEMO_UNI3 10 8 10403840 1270
25: 10
If you check across further details, you can find some differences in size (bytes) got allocated.
1: SQL> select 10485760-9437184 diff from dual;
2:
3: DIFF
4: ----------
5: 1048576
6:
7: SQL> select 1048576/1024/1024 diff_mb from dual;
8:
9: DIFF_MB
10: ----------
11: 1
12:
13: SQL> select 10485760-10403840 diff from dual;
14:
15: DIFF
16: ----------
17: 81920
18:
19: SQL> select 81920/1024/1024 diff_mb from dual;
20:
21: DIFF_MB
22: ----------
23: .078125
24:
25: SQL> select 81920/1024 diff_kb from dual;
26:
27: DIFF_KB
28: ----------
29: 80
If you check across the differences from above snippet, be default 1MB – metadata allocation is maintained else worst case we can allocate (minimize the allocation).
Oracle Doc say’s
•If you set extent management to LOCAL UNIFORM, then you must ensure that each extent contains at least 5 database blocks.
•If you set extent management to LOCAL AUTOALLOCATE, and if the database block size is 16K or greater, then Oracle manages segment space by creating extents with a minimum size of 5 blocks rounded up to 64K.
But – certain questions stands – what could be the reason that Oracle allocates default 1MB of size – instead of 5 Blocks of memory for metadata for each tablespace.
Further – in you check the sum of size of v$type_size – which gives the information about internal structure of Oracle block level details.
Oracle doc says
V$TYPE_SIZE displays the sizes of various database components for use in estimating data block capacity.
It’s total sum of type_size – 1132.
Still we need to check across the things and reasons of metadata size – whether does it extends as the size of tablespace increases (in terms of data files).
I hope you people already knows things prior to me, as I came across some thing about this and posted across and hope it’s find useful in learning curve of every other individual.
