As we all know, Oracle allocates storage space logically using blocks, extents(contiguous block space), segments for tables, indexes, partitions, subpartitions in tablespace consisting of one of more physical datafiles.
Oracle 9i introduced LOCALLY MANAGED TABLESPACE, prior to that Oracle was using DICTIONARY MANAGED TABLESPACE. With introduction of LOCALLY MANAGED TABLESPACE , AUTOMATIC SEGMENT SPACE MANAGEMENT(ASSM) was also introduced, which removes the need for managing freelists and freelist groups by using bitmaps to describe the space usage of each block is within a segment.
Let us first of all understand how Oracle performs extent allocation for normal/non-partitioned tables.
CREATE TABLESPACE DATA
DATAFILE '+DATA001/PGTEST/DATAFILE/ipydata01.dbf' SIZE 1400M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
Now, we will create non-partitioned table without storage clause and will insert data from dba_objects dictionary table to populate data.
SQL> set line 500 echo on
SQL>
SQL> drop table t_obj;
Table dropped.
SQL>
SQL>
SQL> CREATE TABLE "T_OBJ"
2 ("OWNER" VARCHAR2(128 BYTE),
3 "OBJECT_NAME" VARCHAR2(128 BYTE),
4 "SUBOBJECT_NAME" VARCHAR2(128 BYTE),
5 "OBJECT_ID" NUMBER,
6 "DATA_OBJECT_ID" NUMBER,
7 "OBJECT_TYPE" VARCHAR2(23 BYTE),
8 "CREATED" DATE,
9 "LAST_DDL_TIME" DATE,
10 "TIMESTAMP" VARCHAR2(19 BYTE),
11 "STATUS" VARCHAR2(7 BYTE),
12 "TEMPORARY" VARCHAR2(1 BYTE),
13 "GENERATED" VARCHAR2(1 BYTE),
14 "SECONDARY" VARCHAR2(1 BYTE),
15 "NAMESPACE" NUMBER,
16 "EDITION_NAME" VARCHAR2(128 BYTE),
17 "SHARING" VARCHAR2(18 BYTE),
18 "EDITIONABLE" VARCHAR2(1 BYTE),
19 "ORACLE_MAINTAINED" VARCHAR2(1 BYTE),
20 "APPLICATION" VARCHAR2(1 BYTE),
21 "DEFAULT_COLLATION" VARCHAR2(100 BYTE),
22 "DUPLICATED" VARCHAR2(1 BYTE),
23 "SHARDED" VARCHAR2(1 BYTE),
24 "CREATED_APPID" NUMBER,
25 "CREATED_VSNID" NUMBER,
26 "MODIFIED_APPID" NUMBER,
27 "MODIFIED_VSNID" NUMBER
28 ) SEGMENT CREATION IMMEDIATE
29 PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
30 NOCOMPRESS LOGGING
31 TABLESPACE "DATA" ;
Table created.
SQL>
SQL>
SQL> insert into t_obj (select * from dba_objects);
51684 rows created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> set line 200 pagesize 50
SQL> col owner for a10
SQL> col segment_name for a20
SQL> col partition_name for a25
SQL> col segment_type for a25
SQL> col tablespace_name for a15
SQL> col extent_id for 99
SQL> col file_id for 9999
SQL> col block_id for 9999999999
SQL> col bytes for 9999999999
SQL> col blocks for 99999
SQL>
SQL> select owner,segment_name,partition_name,segment_type,tablespace_name,extent_id,file_id,block_id,bytes,blocks
2 from dba_extents where owner='ADMIN' and segment_name='T_OBJ';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ------------- ----------------- --------------- --------------- --------- ------- ----------- ----------- ------
ADMIN T_OBJ TABLE DATA
0
4619 828416 65536 8
ADMIN T_OBJ TABLE DATA
1
4619 828424 65536 8
ADMIN T_OBJ TABLE DATA
2
4619 828432 65536 8
ADMIN T_OBJ TABLE DATA
3
4619 828440 65536 8
ADMIN T_OBJ TABLE DATA
4
4619 828448 65536 8
ADMIN T_OBJ TABLE DATA
5
4619 828456 65536 8
ADMIN T_OBJ TABLE DATA
6
4619 828464 65536 8
ADMIN T_OBJ TABLE DATA
7
4619 828472 65536 8
ADMIN T_OBJ TABLE DATA
8
4619 828480 65536 8
ADMIN T_OBJ TABLE DATA
9
4619 828488 65536 8
ADMIN T_OBJ TABLE DATA
10
4619 828496 65536 8
ADMIN T_OBJ TABLE DATA
11
4619 828504 65536 8
ADMIN T_OBJ TABLE DATA
12
4619 828512 65536 8
ADMIN T_OBJ TABLE DATA
13
4619 828520 65536 8
ADMIN T_OBJ TABLE DATA
14
4619 828528 65536 8
ADMIN T_OBJ TABLE DATA
15
4619 828536 65536 8
ADMIN T_OBJ TABLE DATA
16 4619 828544 1048576
128
ADMIN T_OBJ TABLE DATA 17 4619 828672 1048576 128
ADMIN T_OBJ TABLE DATA 18 4619 828800 1048576 128
ADMIN T_OBJ TABLE DATA 19 4619 828928 1048576 128
ADMIN T_OBJ TABLE DATA 20 4619 829056 1048576 128
ADMIN T_OBJ TABLE DATA 21 4619 829184 1048576 128
ADMIN T_OBJ TABLE DATA 22 4619 829312 1048576 128
ADMIN T_OBJ TABLE DATA 23 4619 829440 1048576 128
24 rows selected.
SQL>
- First 16 extents: 64 KB (1
MB in total).
- Next 63 extents: 1 MB (total
space is 16 MB).
- Next 126 extents: 8 MB
(until total space 1 GB).
- Then extent size 64 MB is
chosen.
No comments:
Post a Comment