花了比较长的时间一点点做实验，总算初步搞清楚了数据文件和extent扩展的关系。下面详细地记录extent区间是如何扩展填充数据文件的。
先建立三个不同的tablespace
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLESPACE xwiz01
DATAFILE '/u01/app/oracle/oradata/orcl/xwiz01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE xwiz02
DATAFILE '/u01/app/oracle/oradata/orcl/xwiz02.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO UNIFORM SIZE 128K;
CREATE TABLESPACE xwiz03
DATAFILE '/u01/app/oracle/oradata/orcl/xwiz03.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO UNIFORM SIZE 512K;
Extent 选择AUTOALLOCATE 、UNIFORM 128K和UNIFORM 512K三种情况。
三个tablespace创建后，检查他们的空间大小。
1
2
3
4
5
6
7
8
9
10
11
12
SET LINESIZE 300;
COLUMN file_name FORMAT a40;
COLUMN db_block_size FORMAT a15;
SELECT file_id, file_name, blocks, bytes, user_blocks, user_bytes, increment_by, (SELECT value FROM v$parameter WHERE name='db_block_size') as db_block_size
FROM dba_data_files WHERE tablespace_name in ('XWIZ01', 'XWIZ02', 'XWIZ03');
FILE_ID FILE_NAME BLOCKS BYTES USER_BLOCKS USER_BYTES INCREMENT_BY DB_BLOCK_SIZE
---------- ------------------------------- ---------- ---------- ----------- ---------- ------------ -------------
5 /u01/app/oracle/oradata/orcl/xwiz01.dbf 128 1048576 120 983040 128 8192
6 /u01/app/oracle/oradata/orcl/xwiz02.dbf 128 1048576 112 917504 128 8192
7 /u01/app/oracle/oradata/orcl/xwiz03.dbf 128 1048576 64 524288 128 8192
这里的字段的 计算公式如下：
- BYTES = CREATE TABLESPACE 声明的SIZE （这里是1M） = BLOCKS * DB_BLOCK_SIZE -> datafile文件所占的空间
- USER_BYTES = USER_BLOCKS * DB_BLOCK_SIZE ->用户可用的数据空间
- INCREMENT_BY = AUTOEXTEND ON NEXT xx -> 这里的例子是表空间自动增长 1M = 1024*1024 = 128 * DB_BLOCK_SIZE
为什么 xwiz01，xwiz02和xwiz03 的初始的user_blocks是120，112，64块呢？因为BYTES和USER_BYTES的差，按照Oracle官方说法是保存的datafile related metadata。
- File# 5: metadata = 1048576 - 983040 = 65536 = 64K = 8 * db_block_size = 1 extent (AUTOALLOCATE)
- File# 6: metadata = 1048576 - 917504 = 131072 = 128K = 16 * db_block_size = 1 extent (UNIFORM SIZE 128k)
- File# 7: metadata = 1048576 - 524288 = 524288 = 512K = 64 * db_block_size = 1 extent (UNIFORM SIZE 512k)
所以，这三个文件的初始可用空间分别少了1个extent，只是他们的extent大小不等，分别是8，16和64个db_block_size。
BYTES的大小在这里都是1048576字节。而datafile的真正的物理文件的大小都是1056768，数量上相差一个DB_BLOCK_SIZE 8192个字节。
1
2
3
4
[oracle@localhost orcl]$ ls -l xwiz*
-rw-r-----. 1 oracle oracle 1056768 Nov 20 12:10 xwiz01.dbf
-rw-r-----. 1 oracle oracle 1056768 Nov 20 12:10 xwiz02.dbf
-rw-r-----. 1 oracle oracle 1056768 Nov 20 12:10 xwiz03.dbf
按照v$datafile的视图说明，有一个BLOCK1_OFFSET偏移量，保存的是OS系统信息。Oracle数据写物理文件的时候需要往后偏移BLOCK1_OFFSET数量才真正写入数据。这个BLOCK1_OFFSET的大小是一个物理快DB_BLOCK_SIZE=8192 bytes。所以实际物理文件大小比create tablespace声明的size要大一个偏移量数量(8192)。
公式为：
physical filesize = bytes + block1_offset
相应第，查询v$datafile时，自定义个real_file_size就能反映出区别。
1
2
3
4
5
6
7
SELECT file#, CREATE_BYTES, blocks, bytes, block1_offset, bytes + block1_offset AS real_file_size FROM v$datafile WHERE file# IN (5, 6, 7);
FILE# CREATE_BYTES BLOCKS BYTES BLOCK1_OFFSET REAL_FILE_SIZE
---------- ------------ ---------- ---------- ------------- --------------
5 1048576 128 1048576 8192 1056768
6 1048576 128 1048576 8192 1056768
7 1048576 128 1048576 8192 1056768
由于这个时候，还没有在segment上建立对象（比如表、索引等），所以dba_extents视图中的extent没有任何扩展信息。
1
2
3
select tablespace_name, EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where tablespace_name in ('XWIZ01', 'XWIZ02', 'XWIZ03');
no rows selected
接下来分别给三个tablespace各自创建一个表插入一百万行数据，看一下extent的扩展。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE T1(
name VARCHAR2(50)
)
TABLESPACE xwiz01
/
CREATE TABLE T2(
name VARCHAR2(50)
)
TABLESPACE xwiz02
/
CREATE TABLE T3(
name VARCHAR2(50)
)
TABLESPACE xwiz03
/
BEGIN
FOR i in 1..1000000
LOOP
INSERT INTO T1 VALUES ('value_' || i);
INSERT INTO T2 VALUES ('value_' || i);
INSERT INTO T3 VALUES ('value_' || i);
END LOOP;
COMMIT;
END;
/
数据文件信息相应增大
1
2
3
4
5
6
7
8
SELECT file_id, file_name, blocks, bytes, user_blocks, user_bytes, increment_by, (SELECT value FROM v$parameter WHERE name='db_block_size') as db_block_size
FROM dba_data_files WHERE tablespace_name IN ('XWIZ01', 'XWIZ02', 'XWIZ03');
FILE_ID FILE_NAME BLOCKS BYTES USER_BLOCKS USER_BYTES INCREMENT_BY DB_BLOCK_SIZE
---------- ------------------------------- ---------- ---------- ----------- ---------- ------------ -------------
5 /u01/app/oracle/oradata/orcl/xwiz01.dbf 2816 23068672 2808 23003136 128 8192
6 /u01/app/oracle/oradata/orcl/xwiz02.dbf 2688 22020096 2672 21889024 128 8192
7 /u01/app/oracle/oradata/orcl/xwiz03.dbf 2816 23068672 2752 22544384 128 8192
datafile的metadata没有所变化（？）
- File# 5: metadata = 23068672 - 23003136 = 65536
- File# 6: metadata = 20971520 - 20840448 = 131072
- File# 7: metadata = 23068672 - 22544384 = 524288
查看物理文件的实际大小，和BYTES还是对应不上。因为他们之间差了一个DB_BLOCK_SIZE。
1
2
3
4
5
ls -l xwiz*
-rw-r-----. 1 oracle oracle 23076864 Nov 20 12:33 xwiz01.dbf
-rw-r-----. 1 oracle oracle 22028288 Nov 20 12:33 xwiz02.dbf
-rw-r-----. 1 oracle oracle 23076864 Nov 20 12:30 xwiz03.dbf
SQL确认
1
2
3
4
5
6
7
SELECT file#, CREATE_BYTES, blocks, bytes, block1_offset, bytes + block1_offset AS real_file_size FROM v$datafile WHERE file# IN (5, 6, 7);
FILE# CREATE_BYTES BLOCKS BYTES BLOCK1_OFFSET REAL_FILE_SIZE
---------- ------------ ---------- ---------- ------------- --------------
5 1048576 2816 23068672 8192 23076864
6 1048576 2688 22020096 8192 22028288
7 1048576 2816 23068672 8192 23076864
接下来看每个tablespace的extent的分配。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
select tablespace_name, EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where tablespace_name in ('XWIZ01');
TABLESPACE EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ---------- ----------
XWIZ01 0 5 8 65536 8
XWIZ01 1 5 16 65536 8
XWIZ01 2 5 24 65536 8
XWIZ01 3 5 32 65536 8
XWIZ01 4 5 40 65536 8
XWIZ01 5 5 48 65536 8
XWIZ01 6 5 56 65536 8
XWIZ01 7 5 64 65536 8
XWIZ01 8 5 72 65536 8
XWIZ01 9 5 80 65536 8
XWIZ01 10 5 88 65536 8
XWIZ01 11 5 96 65536 8
XWIZ01 12 5 104 65536 8
XWIZ01 13 5 112 65536 8
XWIZ01 14 5 120 65536 8
XWIZ01 15 5 128 65536 8
XWIZ01 16 5 256 1048576 128
XWIZ01 17 5 384 1048576 128
XWIZ01 18 5 512 1048576 128
XWIZ01 19 5 640 1048576 128
XWIZ01 20 5 768 1048576 128
XWIZ01 21 5 896 1048576 128
XWIZ01 22 5 1024 1048576 128
XWIZ01 23 5 1152 1048576 128
XWIZ01 24 5 1280 1048576 128
XWIZ01 25 5 1408 1048576 128
XWIZ01 26 5 1536 1048576 128
XWIZ01 27 5 1664 1048576 128
XWIZ01 28 5 1792 1048576 128
XWIZ01 29 5 1920 1048576 128
XWIZ01 30 5 2048 1048576 128
XWIZ01 31 5 2176 1048576 128
XWIZ01 32 5 2304 1048576 128
XWIZ01 33 5 2432 1048576 128
XWIZ01 34 5 2560 1048576 128
35 rows selected.
XWIZ01文件初始大小为1M，extent采用AUTOALLOCATE自动分配，初始为65536字节，所以extent每次增长64K(默认，8个DB_BLOCK_SIZE)，等bytes增长到65536 * 16=1M（初始文件大小）后，extent根据算法调整为1M，按照128个DB_BLOCK_SIZE（=1M）增长。所以总的BYTES大小是
- 65536 * 16 + 1048576 * 19 = 20971520
总的blocks数是2560：
1
2
3
4
5
select tablespace_name, sum(BYTES), sum(BLOCKS) from dba_extents where tablespace_name in ('XWIZ01') group by tablespace_name;
TABLESPACE SUM(BYTES) SUM(BLOCKS)
---------- ---------- -----------
XWIZ01 20971520 2560
但是会发现总的extent所占用的sum(bytes)是 20971520 ，而不是 23068672，也不是 23003136 。
实际上：
- 操作系统的datafile filesize: 实际的物理文件大小 = BLOCK1_OFFSET(存放OS Info) + dba_data_files.bytes
- dba_data_files.bytes是datafile数据文件的大小 = dba_data_files.user_bytes + 1 * extent (存放metadata)
- dba_data_files.user_bytes是datafile中可以供用户写入的空间大小 = sum(dba_extents.bytes) + freesize
- sum(dba_extents.bytes)是datafile中实际被使用的大小
- freesize = dba_data_files.user_bytes - sum(dba_extents.bytes)
使用下面这个视图，结合dba_data_files，dba_extents，dba_free_space查询extent和表空间的使用情况。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
select df.tablespace_name, df.file_name, df.bytes totalSize, usedBytes usedSize, USER_BYTES userfullSize, freeBytes freeSize, df.autoextensible
from dba_data_files df
left join (
select file_id, sum(bytes) usedBytes
from dba_extents
group by file_id
) ext on df.file_id = ext.file_id
left join (
select file_id, sum(bytes) freeBytes
from dba_free_space
group by file_id
) free on df.file_id = free.file_id
where df.tablespace_name in ('XWIZ01')
order by df.tablespace_name, df.file_name;
数据文件空余空间为：
1
2
3
TABLESPACE FILE_NAME TOTALSIZE USEDSIZE USERFULLSIZE FREESIZE AUTOEXTEN
---------- ---------------------------------------- ---------- ---------- ------------ ---------- ---------
XWIZ01 /u01/app/oracle/oradata/orcl/xwiz01.dbf 23068672 20971520 23003136 2031616 YES
- FREESIZE = dba_data_files.user_bytes - sum(dba_extents.bytes) = 23003136 - 20971520 = 2031616
再看第二个表空间
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
select tablespace_name, EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where tablespace_name in ('XWIZ02');
TABLESPACE EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ---------- ----------
XWIZ02 0 6 8 131072 16
XWIZ02 1 6 24 131072 16
XWIZ02 2 6 40 131072 16
XWIZ02 3 6 56 131072 16
XWIZ02 4 6 72 131072 16
XWIZ02 5 6 88 131072 16
XWIZ02 6 6 104 131072 16
XWIZ02 7 6 120 131072 16
XWIZ02 8 6 136 131072 16
XWIZ02 9 6 152 131072 16
XWIZ02 10 6 168 131072 16
XWIZ02 11 6 184 131072 16
XWIZ02 12 6 200 131072 16
XWIZ02 13 6 216 131072 16
XWIZ02 14 6 232 131072 16
XWIZ02 15 6 248 131072 16
XWIZ02 16 6 264 131072 16
XWIZ02 17 6 280 131072 16
XWIZ02 18 6 296 131072 16
XWIZ02 19 6 312 131072 16
XWIZ02 20 6 328 131072 16
XWIZ02 21 6 344 131072 16
XWIZ02 22 6 360 131072 16
XWIZ02 23 6 376 131072 16
XWIZ02 24 6 392 131072 16
XWIZ02 25 6 408 131072 16
XWIZ02 26 6 424 131072 16
XWIZ02 27 6 440 131072 16
XWIZ02 28 6 456 131072 16
XWIZ02 29 6 472 131072 16
XWIZ02 30 6 488 131072 16
XWIZ02 31 6 504 131072 16
XWIZ02 32 6 520 131072 16
XWIZ02 33 6 536 131072 16
XWIZ02 34 6 552 131072 16
XWIZ02 35 6 568 131072 16
XWIZ02 36 6 584 131072 16
XWIZ02 37 6 600 131072 16
XWIZ02 38 6 616 131072 16
XWIZ02 39 6 632 131072 16
XWIZ02 40 6 648 131072 16
XWIZ02 41 6 664 131072 16
XWIZ02 42 6 680 131072 16
XWIZ02 43 6 696 131072 16
XWIZ02 44 6 712 131072 16
XWIZ02 45 6 728 131072 16
XWIZ02 46 6 744 131072 16
XWIZ02 47 6 760 131072 16
XWIZ02 48 6 776 131072 16
XWIZ02 49 6 792 131072 16
XWIZ02 50 6 808 131072 16
XWIZ02 51 6 824 131072 16
XWIZ02 52 6 840 131072 16
XWIZ02 53 6 856 131072 16
XWIZ02 54 6 872 131072 16
XWIZ02 55 6 888 131072 16
XWIZ02 56 6 904 131072 16
XWIZ02 57 6 920 131072 16
XWIZ02 58 6 936 131072 16
XWIZ02 59 6 952 131072 16
XWIZ02 60 6 968 131072 16
XWIZ02 61 6 984 131072 16
XWIZ02 62 6 1000 131072 16
XWIZ02 63 6 1016 131072 16
XWIZ02 64 6 1032 131072 16
XWIZ02 65 6 1048 131072 16
XWIZ02 66 6 1064 131072 16
XWIZ02 67 6 1080 131072 16
XWIZ02 68 6 1096 131072 16
XWIZ02 69 6 1112 131072 16
XWIZ02 70 6 1128 131072 16
XWIZ02 71 6 1144 131072 16
XWIZ02 72 6 1160 131072 16
XWIZ02 73 6 1176 131072 16
XWIZ02 74 6 1192 131072 16
XWIZ02 75 6 1208 131072 16
XWIZ02 76 6 1224 131072 16
XWIZ02 77 6 1240 131072 16
XWIZ02 78 6 1256 131072 16
XWIZ02 79 6 1272 131072 16
XWIZ02 80 6 1288 131072 16
XWIZ02 81 6 1304 131072 16
XWIZ02 82 6 1320 131072 16
XWIZ02 83 6 1336 131072 16
XWIZ02 84 6 1352 131072 16
XWIZ02 85 6 1368 131072 16
XWIZ02 86 6 1384 131072 16
XWIZ02 87 6 1400 131072 16
XWIZ02 88 6 1416 131072 16
XWIZ02 89 6 1432 131072 16
XWIZ02 90 6 1448 131072 16
XWIZ02 91 6 1464 131072 16
XWIZ02 92 6 1480 131072 16
XWIZ02 93 6 1496 131072 16
XWIZ02 94 6 1512 131072 16
XWIZ02 95 6 1528 131072 16
XWIZ02 96 6 1544 131072 16
XWIZ02 97 6 1560 131072 16
XWIZ02 98 6 1576 131072 16
XWIZ02 99 6 1592 131072 16
XWIZ02 100 6 1608 131072 16
XWIZ02 101 6 1624 131072 16
XWIZ02 102 6 1640 131072 16
XWIZ02 103 6 1656 131072 16
XWIZ02 104 6 1672 131072 16
XWIZ02 105 6 1688 131072 16
XWIZ02 106 6 1704 131072 16
XWIZ02 107 6 1720 131072 16
XWIZ02 108 6 1736 131072 16
XWIZ02 109 6 1752 131072 16
XWIZ02 110 6 1768 131072 16
XWIZ02 111 6 1784 131072 16
XWIZ02 112 6 1800 131072 16
XWIZ02 113 6 1816 131072 16
XWIZ02 114 6 1832 131072 16
XWIZ02 115 6 1848 131072 16
XWIZ02 116 6 1864 131072 16
XWIZ02 117 6 1880 131072 16
XWIZ02 118 6 1896 131072 16
XWIZ02 119 6 1912 131072 16
XWIZ02 120 6 1928 131072 16
XWIZ02 121 6 1944 131072 16
XWIZ02 122 6 1960 131072 16
XWIZ02 123 6 1976 131072 16
XWIZ02 124 6 1992 131072 16
XWIZ02 125 6 2008 131072 16
XWIZ02 126 6 2024 131072 16
XWIZ02 127 6 2040 131072 16
XWIZ02 128 6 2056 131072 16
XWIZ02 129 6 2072 131072 16
XWIZ02 130 6 2088 131072 16
XWIZ02 131 6 2104 131072 16
XWIZ02 132 6 2120 131072 16
XWIZ02 133 6 2136 131072 16
XWIZ02 134 6 2152 131072 16
XWIZ02 135 6 2168 131072 16
XWIZ02 136 6 2184 131072 16
XWIZ02 137 6 2200 131072 16
XWIZ02 138 6 2216 131072 16
XWIZ02 139 6 2232 131072 16
XWIZ02 140 6 2248 131072 16
XWIZ02 141 6 2264 131072 16
XWIZ02 142 6 2280 131072 16
XWIZ02 143 6 2296 131072 16
XWIZ02 144 6 2312 131072 16
XWIZ02 145 6 2328 131072 16
XWIZ02 146 6 2344 131072 16
XWIZ02 147 6 2360 131072 16
XWIZ02 148 6 2376 131072 16
XWIZ02 149 6 2392 131072 16
XWIZ02 150 6 2408 131072 16
XWIZ02 151 6 2424 131072 16
XWIZ02 152 6 2440 131072 16
XWIZ02 153 6 2456 131072 16
XWIZ02 154 6 2472 131072 16
XWIZ02 155 6 2488 131072 16
XWIZ02 156 6 2504 131072 16
XWIZ02 157 6 2520 131072 16
158 rows selected.
因为extent采用UNIFORM SIZE增长，每次为128K = 131072 bytes = 16 * DB_BLOCK_SIZE
1
2
3
4
5
6
select tablespace_name, sum(BYTES), sum(BLOCKS) from dba_extents where tablespace_name in ('XWIZ02') group by tablespace_name;
TABLESPACE SUM(BYTES) SUM(BLOCKS)
---------- ---------- -----------
XWIZ02 20709376 2528
数据文件给部分空间为：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select df.tablespace_name, df.file_name, df.bytes totalSize, usedBytes usedSize, USER_BYTES userfullSize, freeBytes freeSize, df.autoextensible
from dba_data_files df
left join (
select file_id, sum(bytes) usedBytes
from dba_extents
group by file_id
) ext on df.file_id = ext.file_id
left join (
select file_id, sum(bytes) freeBytes
from dba_free_space
group by file_id
) free on df.file_id = free.file_id
where df.tablespace_name in ('XWIZ02')
order by df.tablespace_name, df.file_name;
TABLESPACE FILE_NAME TOTALSIZE USEDSIZE USERFULLSIZE FREESIZE AUTOEXTEN
---------- ---------------------------------------- ---------- ---------- ------------ ---------- ---------
XWIZ02 /u01/app/oracle/oradata/orcl/xwiz02.dbf 22020096 20709376 21889024 1179648 YES
- FREESIZE = dba_data_files.user_bytes - sum(dba_extents.bytes) = 21889024 - 20709376 = 1179648
最后看xwiz03表空间
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
select tablespace_name, EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where tablespace_name in ('XWIZ03');
TABLESPACE EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ---------- ----------
XWIZ03 0 7 8 524288 64
XWIZ03 1 7 72 524288 64
XWIZ03 2 7 136 524288 64
XWIZ03 3 7 200 524288 64
XWIZ03 4 7 264 524288 64
XWIZ03 5 7 328 524288 64
XWIZ03 6 7 392 524288 64
XWIZ03 7 7 456 524288 64
XWIZ03 8 7 520 524288 64
XWIZ03 9 7 584 524288 64
XWIZ03 10 7 648 524288 64
XWIZ03 11 7 712 524288 64
XWIZ03 12 7 776 524288 64
XWIZ03 13 7 840 524288 64
XWIZ03 14 7 904 524288 64
XWIZ03 15 7 968 524288 64
XWIZ03 16 7 1032 524288 64
XWIZ03 17 7 1096 524288 64
XWIZ03 18 7 1160 524288 64
XWIZ03 19 7 1224 524288 64
XWIZ03 20 7 1288 524288 64
XWIZ03 21 7 1352 524288 64
XWIZ03 22 7 1416 524288 64
XWIZ03 23 7 1480 524288 64
XWIZ03 24 7 1544 524288 64
XWIZ03 25 7 1608 524288 64
XWIZ03 26 7 1672 524288 64
XWIZ03 27 7 1736 524288 64
XWIZ03 28 7 1800 524288 64
XWIZ03 29 7 1864 524288 64
XWIZ03 30 7 1928 524288 64
XWIZ03 31 7 1992 524288 64
XWIZ03 32 7 2056 524288 64
XWIZ03 33 7 2120 524288 64
XWIZ03 34 7 2184 524288 64
XWIZ03 35 7 2248 524288 64
XWIZ03 36 7 2312 524288 64
XWIZ03 37 7 2376 524288 64
XWIZ03 38 7 2440 524288 64
XWIZ03 39 7 2504 524288 64
40 rows selected.
extent采用UNIFORM SIZE增长，每次为512K = 524288 bytes = 64 * DB_BLOCK_SIZE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
select tablespace_name, sum(BYTES), sum(BLOCKS) from dba_extents where tablespace_name in ('XWIZ03') group by tablespace_name;
TABLESPACE SUM(BYTES) SUM(BLOCKS)
---------- ---------- -----------
XWIZ03 20971520 2560
select df.tablespace_name, df.file_name, df.bytes totalSize, usedBytes usedSize, USER_BYTES userfullSize, freeBytes freeSize, df.autoextensible
from dba_data_files df
left join (
select file_id, sum(bytes) usedBytes
from dba_extents
group by file_id
) ext on df.file_id = ext.file_id
left join (
select file_id, sum(bytes) freeBytes
from dba_free_space
group by file_id
) free on df.file_id = free.file_id
where df.tablespace_name in ('XWIZ03')
order by df.tablespace_name, df.file_name;
TABLESPACE FILE_NAME TOTALSIZE USEDSIZE USERFULLSIZE FREESIZE AUTOEXTEN
---------- ---------------------------------------- ---------- ---------- ------------ ---------- ---------
XWIZ03 /u01/app/oracle/oradata/orcl/xwiz03.dbf 23068672 20971520 22544384 1572864 YES
- FREESIZE = dba_data_files.user_bytes - sum(dba_extents.bytes) = 22544384 - 20971520 = 1572864
总结 datafile与extent的关系：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--> 实际的物理文件大小
physical datafile filesize = BLOCK1_OFFSET + dba_data_files.bytes
--> 视图中datafile数据文件的大小
dba_data_files.bytes = dba_data_files.user_bytes + 1 * extent
-->datafile中可以供用户写入的空间大小
dba_data_files.user_bytes = sum(dba_extents.bytes) + freesize
-->datafile中实际被使用的大小
sum(dba_extents.bytes)
--> datafile中空闲的空间
freesize = ba_data_files.user_bytes - sum(dba_extents.bytes)
--> datafile增长时，
autoallocation: extent extend = 64K/1M/... 根据内部算法逐步增大
uniform: extent extend = uniform size