EODA@PROD1> create table lob(a clob)tablespace registration;
Table created.
EODA@PROD1> insert into lob select rownum from all_objects;
72927 rows created.
EODA@PROD1> exec dbms_stats.gather_table_stats('EODA','LOB');
PL/SQL procedure successfully completed.
EODA@PROD1> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='LOB';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
LOB 72927 622 0
EODA@PROD1> truncate table lob;
Table truncated.
EODA@PROD1> analyze table lob compute statistics; --EMPTY_BLOCKS需要analyze才会得出数据
Table analyzed.
EODA@PROD1> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='LOB';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
LOB 0 0 8
EODA@PROD1> insert into lob values ('123'); --插入一条数据
1 row created.
EODA@PROD1> analyze table lob compute statistics;
Table analyzed.
EODA@PROD1> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='LOB'; --高水位增长5个数据块
--查看数据占用空间
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
LOB 1 5 3
EODA@PROD1> SELECT rowid, --查看数据存储的位置
2 DBMS_ROWID.Rowid_object(ROWID) "OBJECT",
3 DBMS_ROWID.Rowid_relative_fno(ROWID) "FILE",
4 DBMS_ROWID.Rowid_block_number(ROWID) "BLOCK",
5 DBMS_ROWID.Rowid_row_number(ROWID) "ROW"
6 FROM lob;
ROWID OBJECT FILE BLOCK ROW
------------------ ---------- ---------- ---------- ----------
AAAVFsAAHAAAACDAAA 86380 7 131 0
EODA@PROD1> insert into lob values ('123'); --在插入一条数据
1 row created.
EODA@PROD1> analyze table lob compute statistics;
Table analyzed.
EODA@PROD1> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='LOB';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
LOB 2 5 3
EODA@PROD1> SELECT rowid, --数据在同样块存储在第二行（ROW1）
2 DBMS_ROWID.Rowid_object(ROWID) "OBJECT",
3 DBMS_ROWID.Rowid_relative_fno(ROWID) "FILE",
4 DBMS_ROWID.Rowid_block_number(ROWID) "BLOCK",
5 DBMS_ROWID.Rowid_row_number(ROWID) "ROW"
6 FROM lob;
ROWID OBJECT FILE BLOCK ROW
------------------ ---------- ---------- ---------- ----------
AAAVFsAAHAAAACDAAA 86380 7 131 0
AAAVFsAAHAAAACDAAB 86380 7 131 1
EODA@PROD1> insert into lob select rownum from all_objects where rownum<150; --插入大量数据且至少需要两个块存储
149 rows created.
EODA@PROD1> analyze table lob compute statistics;
Table analyzed.
EODA@PROD1> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name='LOB';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
LOB 163 5 3
EODA@PROD1> SELECT rowid, --数据存储在131和134块中
2 DBMS_ROWID.Rowid_object(ROWID) "OBJECT",
3 DBMS_ROWID.Rowid_relative_fno(ROWID) "FILE",
4 DBMS_ROWID.Rowid_block_number(ROWID) "BLOCK",
5 DBMS_ROWID.Rowid_row_number(ROWID) "ROW"
6 FROM lob;
ROWID OBJECT FILE BLOCK ROW
------------------ ---------- ---------- ---------- ----------
AAAVFsAAHAAAACDAAA 86380 7 131 0
AAAVFsAAHAAAACDAAB 86380 7 131 1
AAAVFsAAHAAAACDAAC 86380 7 131 2
AAAVFsAAHAAAACDAAD 86380 7 131 3
AAAVFsAAHAAAACDAAE 86380 7 131 4
AAAVFsAAHAAAACDAAF 86380 7 131 5
AAAVFsAAHAAAACDAAG 86380 7 131 6
AAAVFsAAHAAAACDAAH 86380 7 131 7
AAAVFsAAHAAAACDAAI 86380 7 131 8
AAAVFsAAHAAAACDAAJ 86380 7 131 9
AAAVFsAAHAAAACDAAK 86380 7 131 10
AAAVFsAAHAAAACDAAL 86380 7 131 11
AAAVFsAAHAAAACDAAM 86380 7 131 12
AAAVFsAAHAAAACDAAN 86380 7 131 13
AAAVFsAAHAAAACDAAO 86380 7 131 14
AAAVFsAAHAAAACDAAP 86380 7 131 15
AAAVFsAAHAAAACDAAQ 86380 7 131 16
AAAVFsAAHAAAACDAAR 86380 7 131 17
AAAVFsAAHAAAACDAAS 86380 7 131 18
AAAVFsAAHAAAACDAAT 86380 7 131 19
AAAVFsAAHAAAACDAAU 86380 7 131 20
AAAVFsAAHAAAACDAAV 86380 7 131 21
AAAVFsAAHAAAACDAAW 86380 7 131 22
AAAVFsAAHAAAACDAAX 86380 7 131 23
AAAVFsAAHAAAACDAAY 86380 7 131 24
AAAVFsAAHAAAACDAAZ 86380 7 131 25
AAAVFsAAHAAAACDAAa 86380 7 131 26
AAAVFsAAHAAAACDAAb 86380 7 131 27
AAAVFsAAHAAAACDAAc 86380 7 131 28
AAAVFsAAHAAAACDAAd 86380 7 131 29
AAAVFsAAHAAAACDAAe 86380 7 131 30
AAAVFsAAHAAAACDAAf 86380 7 131 31
AAAVFsAAHAAAACDAAg 86380 7 131 32
AAAVFsAAHAAAACDAAh 86380 7 131 33
AAAVFsAAHAAAACDAAi 86380 7 131 34
AAAVFsAAHAAAACDAAj 86380 7 131 35
AAAVFsAAHAAAACDAAk 86380 7 131 36
AAAVFsAAHAAAACDAAl 86380 7 131 37
AAAVFsAAHAAAACDAAm 86380 7 131 38
AAAVFsAAHAAAACDAAn 86380 7 131 39
AAAVFsAAHAAAACDAAo 86380 7 131 40
AAAVFsAAHAAAACDAAp 86380 7 131 41
AAAVFsAAHAAAACDAAq 86380 7 131 42
AAAVFsAAHAAAACDAAr 86380 7 131 43
AAAVFsAAHAAAACDAAs 86380 7 131 44
AAAVFsAAHAAAACDAAt 86380 7 131 45
AAAVFsAAHAAAACDAAu 86380 7 131 46
AAAVFsAAHAAAACDAAv 86380 7 131 47
AAAVFsAAHAAAACDAAw 86380 7 131 48
AAAVFsAAHAAAACDAAx 86380 7 131 49
AAAVFsAAHAAAACDAAy 86380 7 131 50
AAAVFsAAHAAAACDAAz 86380 7 131 51
AAAVFsAAHAAAACDAA0 86380 7 131 52
AAAVFsAAHAAAACDAA1 86380 7 131 53
AAAVFsAAHAAAACDAA2 86380 7 131 54
AAAVFsAAHAAAACDAA3 86380 7 131 55
AAAVFsAAHAAAACDAA4 86380 7 131 56
AAAVFsAAHAAAACDAA5 86380 7 131 57
AAAVFsAAHAAAACDAA6 86380 7 131 58
AAAVFsAAHAAAACDAA7 86380 7 131 59
AAAVFsAAHAAAACDAA8 86380 7 131 60
AAAVFsAAHAAAACDAA9 86380 7 131 61
AAAVFsAAHAAAACDAA+ 86380 7 131 62
AAAVFsAAHAAAACDAA/ 86380 7 131 63
AAAVFsAAHAAAACDABA 86380 7 131 64
AAAVFsAAHAAAACDABB 86380 7 131 65
AAAVFsAAHAAAACDABC 86380 7 131 66
AAAVFsAAHAAAACDABD 86380 7 131 67
AAAVFsAAHAAAACDABE 86380 7 131 68
AAAVFsAAHAAAACDABF 86380 7 131 69
AAAVFsAAHAAAACDABG 86380 7 131 70
AAAVFsAAHAAAACDABH 86380 7 131 71
AAAVFsAAHAAAACDABI 86380 7 131 72
AAAVFsAAHAAAACDABJ 86380 7 131 73
AAAVFsAAHAAAACDABK 86380 7 131 74
AAAVFsAAHAAAACDABL 86380 7 131 75
AAAVFsAAHAAAACDABM 86380 7 131 76
AAAVFsAAHAAAACDABN 86380 7 131 77
AAAVFsAAHAAAACDABO 86380 7 131 78
AAAVFsAAHAAAACDABP 86380 7 131 79
AAAVFsAAHAAAACDABQ 86380 7 131 80
AAAVFsAAHAAAACDABR 86380 7 131 81
AAAVFsAAHAAAACDABS 86380 7 131 82
AAAVFsAAHAAAACDABT 86380 7 131 83
AAAVFsAAHAAAACDABU 86380 7 131 84
AAAVFsAAHAAAACDABV 86380 7 131 85
AAAVFsAAHAAAACDABW 86380 7 131 86
AAAVFsAAHAAAACDABX 86380 7 131 87
AAAVFsAAHAAAACDABY 86380 7 131 88
AAAVFsAAHAAAACDABZ 86380 7 131 89
AAAVFsAAHAAAACDABa 86380 7 131 90
AAAVFsAAHAAAACDABb 86380 7 131 91
AAAVFsAAHAAAACDABc 86380 7 131 92
AAAVFsAAHAAAACDABd 86380 7 131 93
AAAVFsAAHAAAACDABe 86380 7 131 94
AAAVFsAAHAAAACDABf 86380 7 131 95
AAAVFsAAHAAAACDABg 86380 7 131 96
AAAVFsAAHAAAACDABh 86380 7 131 97
AAAVFsAAHAAAACDABi 86380 7 131 98
AAAVFsAAHAAAACDABj 86380 7 131 99
AAAVFsAAHAAAACDABk 86380 7 131 100
AAAVFsAAHAAAACDABl 86380 7 131 101
AAAVFsAAHAAAACDABm 86380 7 131 102
AAAVFsAAHAAAACDABn 86380 7 131 103
AAAVFsAAHAAAACDABo 86380 7 131 104
AAAVFsAAHAAAACDABp 86380 7 131 105
AAAVFsAAHAAAACDABq 86380 7 131 106
AAAVFsAAHAAAACDABr 86380 7 131 107
AAAVFsAAHAAAACDABs 86380 7 131 108
AAAVFsAAHAAAACDABt 86380 7 131 109
AAAVFsAAHAAAACDABu 86380 7 131 110
AAAVFsAAHAAAACDABv 86380 7 131 111
AAAVFsAAHAAAACDABw 86380 7 131 112
AAAVFsAAHAAAACDABx 86380 7 131 113
AAAVFsAAHAAAACDABy 86380 7 131 114
AAAVFsAAHAAAACDABz 86380 7 131 115
AAAVFsAAHAAAACDAB0 86380 7 131 116
AAAVFsAAHAAAACDAB1 86380 7 131 117
AAAVFsAAHAAAACDAB2 86380 7 131 118
AAAVFsAAHAAAACDAB3 86380 7 131 119
AAAVFsAAHAAAACDAB4 86380 7 131 120
AAAVFsAAHAAAACDAB5 86380 7 131 121
AAAVFsAAHAAAACDAB6 86380 7 131 122
AAAVFsAAHAAAACDAB7 86380 7 131 123
AAAVFsAAHAAAACDAB8 86380 7 131 124
AAAVFsAAHAAAACDAB9 86380 7 131 125
AAAVFsAAHAAAACDAB+ 86380 7 131 126
AAAVFsAAHAAAACDAB/ 86380 7 131 127
AAAVFsAAHAAAACDACA 86380 7 131 128
AAAVFsAAHAAAACDACB 86380 7 131 129
AAAVFsAAHAAAACDACC 86380 7 131 130
AAAVFsAAHAAAACDACD 86380 7 131 131
AAAVFsAAHAAAACDACE 86380 7 131 132
AAAVFsAAHAAAACDACF 86380 7 131 133
AAAVFsAAHAAAACDACG 86380 7 131 134
AAAVFsAAHAAAACDACH 86380 7 131 135
AAAVFsAAHAAAACDACI 86380 7 131 136
AAAVFsAAHAAAACDACJ 86380 7 131 137
AAAVFsAAHAAAACDACK 86380 7 131 138
AAAVFsAAHAAAACDACL 86380 7 131 139
AAAVFsAAHAAAACDACM 86380 7 131 140
AAAVFsAAHAAAACDACN 86380 7 131 141
AAAVFsAAHAAAACDACO 86380 7 131 142
AAAVFsAAHAAAACDACP 86380 7 131 143
AAAVFsAAHAAAACDACQ 86380 7 131 144
AAAVFsAAHAAAACDACR 86380 7 131 145
AAAVFsAAHAAAACDACS 86380 7 131 146
AAAVFsAAHAAAACDACT 86380 7 131 147
AAAVFsAAHAAAACDACU 86380 7 131 148
AAAVFsAAHAAAACDACV 86380 7 131 149
AAAVFsAAHAAAACDACW 86380 7 131 150
AAAVFsAAHAAAACDACX 86380 7 131 151
AAAVFsAAHAAAACDACY 86380 7 131 152
AAAVFsAAHAAAACDACZ 86380 7 131 153
AAAVFsAAHAAAACDACa 86380 7 131 154
AAAVFsAAHAAAACGAAA 86380 7 134 0
AAAVFsAAHAAAACGAAB 86380 7 134 1
AAAVFsAAHAAAACGAAC 86380 7 134 2
AAAVFsAAHAAAACGAAD 86380 7 134 3
AAAVFsAAHAAAACGAAE 86380 7 134 4
AAAVFsAAHAAAACGAAF 86380 7 134 5
AAAVFsAAHAAAACGAAG 86380 7 134 6
AAAVFsAAHAAAACGAAH 86380 7 134 7
163 rows selected.
EODA@PROD1> delete from lob where rownum<100; --删除前一百行
99 rows deleted.
EODA@PROD1> analyze table lob compute statistics;
Table analyzed.
EODA@PROD1> SELECT rowid, --查看块存储，ROW<99记录都不见了
2 DBMS_ROWID.Rowid_object(ROWID) "OBJECT",
3 DBMS_ROWID.Rowid_relative_fno(ROWID) "FILE",
4 DBMS_ROWID.Rowid_block_number(ROWID) "BLOCK",
5 DBMS_ROWID.Rowid_row_number(ROWID) "ROW"
6 FROM lob;
ROWID OBJECT FILE BLOCK ROW
------------------ ---------- ---------- ---------- ----------
AAAVFsAAHAAAACDABj 86380 7 131 99
AAAVFsAAHAAAACDABk 86380 7 131 100
AAAVFsAAHAAAACDABl 86380 7 131 101
AAAVFsAAHAAAACDABm 86380 7 131 102
AAAVFsAAHAAAACDABn 86380 7 131 103
AAAVFsAAHAAAACDABo 86380 7 131 104
AAAVFsAAHAAAACDABp 86380 7 131 105
AAAVFsAAHAAAACDABq 86380 7 131 106
AAAVFsAAHAAAACDABr 86380 7 131 107
AAAVFsAAHAAAACDABs 86380 7 131 108
AAAVFsAAHAAAACDABt 86380 7 131 109
AAAVFsAAHAAAACDABu 86380 7 131 110
AAAVFsAAHAAAACDABv 86380 7 131 111
AAAVFsAAHAAAACDABw 86380 7 131 112
AAAVFsAAHAAAACDABx 86380 7 131 113
AAAVFsAAHAAAACDABy 86380 7 131 114
AAAVFsAAHAAAACDABz 86380 7 131 115
AAAVFsAAHAAAACDAB0 86380 7 131 116
AAAVFsAAHAAAACDAB1 86380 7 131 117
AAAVFsAAHAAAACDAB2 86380 7 131 118
AAAVFsAAHAAAACDAB3 86380 7 131 119
AAAVFsAAHAAAACDAB4 86380 7 131 120
AAAVFsAAHAAAACDAB5 86380 7 131 121
AAAVFsAAHAAAACDAB6 86380 7 131 122
AAAVFsAAHAAAACDAB7 86380 7 131 123
AAAVFsAAHAAAACDAB8 86380 7 131 124
AAAVFsAAHAAAACDAB9 86380 7 131 125
AAAVFsAAHAAAACDAB+ 86380 7 131 126
AAAVFsAAHAAAACDAB/ 86380 7 131 127
AAAVFsAAHAAAACDACA 86380 7 131 128
AAAVFsAAHAAAACDACB 86380 7 131 129
AAAVFsAAHAAAACDACC 86380 7 131 130
AAAVFsAAHAAAACDACD 86380 7 131 131
AAAVFsAAHAAAACDACE 86380 7 131 132
AAAVFsAAHAAAACDACF 86380 7 131 133
AAAVFsAAHAAAACDACG 86380 7 131 134
AAAVFsAAHAAAACDACH 86380 7 131 135
AAAVFsAAHAAAACDACI 86380 7 131 136
AAAVFsAAHAAAACDACJ 86380 7 131 137
AAAVFsAAHAAAACDACK 86380 7 131 138
AAAVFsAAHAAAACDACL 86380 7 131 139
AAAVFsAAHAAAACDACM 86380 7 131 140
AAAVFsAAHAAAACDACN 86380 7 131 141
AAAVFsAAHAAAACDACO 86380 7 131 142
AAAVFsAAHAAAACDACP 86380 7 131 143
AAAVFsAAHAAAACDACQ 86380 7 131 144
AAAVFsAAHAAAACDACR 86380 7 131 145
AAAVFsAAHAAAACDACS 86380 7 131 146
AAAVFsAAHAAAACDACT 86380 7 131 147
AAAVFsAAHAAAACDACU 86380 7 131 148
AAAVFsAAHAAAACDACV 86380 7 131 149
AAAVFsAAHAAAACDACW 86380 7 131 150
AAAVFsAAHAAAACDACX 86380 7 131 151
AAAVFsAAHAAAACDACY 86380 7 131 152
AAAVFsAAHAAAACDACZ 86380 7 131 153
AAAVFsAAHAAAACDACa 86380 7 131 154
AAAVFsAAHAAAACGAAA 86380 7 134 0
AAAVFsAAHAAAACGAAB 86380 7 134 1
AAAVFsAAHAAAACGAAC 86380 7 134 2
AAAVFsAAHAAAACGAAD 86380 7 134 3
AAAVFsAAHAAAACGAAE 86380 7 134 4
AAAVFsAAHAAAACGAAF 86380 7 134 5
AAAVFsAAHAAAACGAAG 86380 7 134 6
AAAVFsAAHAAAACGAAH 86380 7 134 7
64 rows selected.
EODA@PROD1> insert into lob values ('123'); --插入一条数据
1 row created.
EODA@PROD1> analyze table lob compute statistics;
Table analyzed.
EODA@PROD1> SELECT rowid, --数据插入在了ROW0的地方，所以当然在HWM以下
2 DBMS_ROWID.Rowid_object(ROWID) "OBJECT",
3 DBMS_ROWID.Rowid_relative_fno(ROWID) "FILE",
4 DBMS_ROWID.Rowid_block_number(ROWID) "BLOCK",
5 DBMS_ROWID.Rowid_row_number(ROWID) "ROW"
6 FROM lob;
ROWID OBJECT FILE BLOCK ROW
------------------ ---------- ---------- ---------- ----------
AAAVFsAAHAAAACDAAA 86380 7 131 0
AAAVFsAAHAAAACDABj 86380 7 131 99
AAAVFsAAHAAAACDABk 86380 7 131 100
AAAVFsAAHAAAACDABl 86380 7 131 101
AAAVFsAAHAAAACDABm 86380 7 131 102
AAAVFsAAHAAAACDABn 86380 7 131 103
AAAVFsAAHAAAACDABo 86380 7 131 104
AAAVFsAAHAAAACDABp 86380 7 131 105
AAAVFsAAHAAAACDABq 86380 7 131 106
AAAVFsAAHAAAACDABr 86380 7 131 107
AAAVFsAAHAAAACDABs 86380 7 131 108
AAAVFsAAHAAAACDABt 86380 7 131 109
AAAVFsAAHAAAACDABu 86380 7 131 110
AAAVFsAAHAAAACDABv 86380 7 131 111
AAAVFsAAHAAAACDABw 86380 7 131 112
AAAVFsAAHAAAACDABx 86380 7 131 113
AAAVFsAAHAAAACDABy 86380 7 131 114
AAAVFsAAHAAAACDABz 86380 7 131 115
AAAVFsAAHAAAACDAB0 86380 7 131 116
AAAVFsAAHAAAACDAB1 86380 7 131 117
AAAVFsAAHAAAACDAB2 86380 7 131 118
AAAVFsAAHAAAACDAB3 86380 7 131 119
AAAVFsAAHAAAACDAB4 86380 7 131 120
AAAVFsAAHAAAACDAB5 86380 7 131 121
AAAVFsAAHAAAACDAB6 86380 7 131 122
AAAVFsAAHAAAACDAB7 86380 7 131 123
AAAVFsAAHAAAACDAB8 86380 7 131 124
AAAVFsAAHAAAACDAB9 86380 7 131 125
AAAVFsAAHAAAACDAB+ 86380 7 131 126
AAAVFsAAHAAAACDAB/ 86380 7 131 127
AAAVFsAAHAAAACDACA 86380 7 131 128
AAAVFsAAHAAAACDACB 86380 7 131 129
AAAVFsAAHAAAACDACC 86380 7 131 130
AAAVFsAAHAAAACDACD 86380 7 131 131
AAAVFsAAHAAAACDACE 86380 7 131 132
AAAVFsAAHAAAACDACF 86380 7 131 133
AAAVFsAAHAAAACDACG 86380 7 131 134
AAAVFsAAHAAAACDACH 86380 7 131 135
AAAVFsAAHAAAACDACI 86380 7 131 136
AAAVFsAAHAAAACDACJ 86380 7 131 137
AAAVFsAAHAAAACDACK 86380 7 131 138
AAAVFsAAHAAAACDACL 86380 7 131 139
AAAVFsAAHAAAACDACM 86380 7 131 140
AAAVFsAAHAAAACDACN 86380 7 131 141
AAAVFsAAHAAAACDACO 86380 7 131 142
AAAVFsAAHAAAACDACP 86380 7 131 143
AAAVFsAAHAAAACDACQ 86380 7 131 144
AAAVFsAAHAAAACDACR 86380 7 131 145
AAAVFsAAHAAAACDACS 86380 7 131 146
AAAVFsAAHAAAACDACT 86380 7 131 147
AAAVFsAAHAAAACDACU 86380 7 131 148
AAAVFsAAHAAAACDACV 86380 7 131 149
AAAVFsAAHAAAACDACW 86380 7 131 150
AAAVFsAAHAAAACDACX 86380 7 131 151
AAAVFsAAHAAAACDACY 86380 7 131 152
AAAVFsAAHAAAACDACZ 86380 7 131 153
AAAVFsAAHAAAACDACa 86380 7 131 154
AAAVFsAAHAAAACGAAA 86380 7 134 0
AAAVFsAAHAAAACGAAB 86380 7 134 1
AAAVFsAAHAAAACGAAC 86380 7 134 2
AAAVFsAAHAAAACGAAD 86380 7 134 3
AAAVFsAAHAAAACGAAE 86380 7 134 4
AAAVFsAAHAAAACGAAF 86380 7 134 5
AAAVFsAAHAAAACGAAG 86380 7 134 6
AAAVFsAAHAAAACGAAH 86380 7 134 7
65 rows selected.
虽然高水位线在日常的增删操作中只会上涨，不会下跌。而且可以通过实验看出HWM通常增长的幅度为一次5个数据块，但是数据还是可以插入到HWM以下的空间的。
1140
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
