WB空间大小分析
--表分析
select T.segment_name,T.tablespace_name,T.BYTES/1024/1024/1024 from User_Segments T WHERE T.segment_type='TABLE' ORDER BY T.BYTES DESC;
| REWB |
SYSTEM |
110.6982421875G |
| USWB |
TBS_USWB |
80.0625G |
| RWB |
SYSTEM |
74.140625G |
--索引分析
select T.segment_name,T2.TABLE_NAME,T.tablespace_name,T.BYTES/1024/1024/1024||'G' from User_Segments T,USER_INDEXES T2 WHERE T.segment_type='INDEX' AND T.segment_name=T2.INDEX_NAME ORDER BY T.BYTES DESC;
| PRI_ID |
REWB |
SYSTEM |
49.1611328125G |
| IND_REWB_RKSJ |
REWB |
SYSTEM |
13.791015625G |
| RWB_ID |
RWB |
SYSTEM |
13.115234375G |
| I_USWB11_ENTERDB_TIME_BARID |
USWB |
TBS_USWB_INDEX |
10.8125G |
| I_USERINFO11_ID_NUM |
USWB |
TBS_USWB_INDEX |
8.9921875G |
| INDEX_RWB_RYRKSJ |
RWB |
SYSTEM |
7.740234375G |
| |
|
|
|
考虑到索引对数据抽取的必要性，建议不对主键，时间戳索引进行修改或者删除
查看undo表空间情况
表空间 空间大小（M） 使用空间大小(M)
| TBS_USWB |
92000 |
11776000 |
81984.56 |
89.11 |
10015.44 |
| TBS_USWB_INDEX |
51060 |
6535680 |
44555.5 |
87.26 |
6504.5 |
| SYSAUX |
520 |
66560 |
449.25 |
86.39 |
70.75 |
| SYSTEM |
320404 |
41011712 |
275688.19 |
86.04 |
44715.81 |
| USERS |
1729 |
221312 |
701.06 |
40.55 |
1027.94 |
| UNDOTBS1 |
11235 |
1438080 |
30.31 |
0.27 |
11204.69 |
两种删除方式
1、delete
测试用例
insert into usWB
select * from USWB@dbl_jwb t where rownum < 100001;
commit;
--查看数据大小
select T.segment_name,T.BYTES/1024/1024/1024||'G' from user_segments t where t.segment_name='USWB';
| USWB |
.033203125G |
--查看undo大小
SELECT SUM(T.BYTES)/10245/1024/1024||'G' FROM DBA_SEGMENTS T WHERE T.tablespace_name LIKE 'UNDOTBS1%'
| .008217423133235724743777452415812591508G |
--执行删除数据
DELETE FROM USWB;
COMMIT;
SELECT * FROM USWB;
数据为空
--查看数据空间大小
select T.segment_name,T.BYTES/1024/1024/1024||'G' from user_segments t where t.segment_name='USWB';
| USWB |
.033203125G |
发现delete对于数据空间未释放
--查看undo情况
SELECT SUM(T.BYTES)/10245/1024/1024||'G' FROM DBA_SEGMENTS T WHERE T.tablespace_name LIKE 'UNDOTBS1%'
| .019418008784773060029282576866764275256G |
UNDO使用率
select (0.019418008784773060029282576866764275256-0.008217423133235724743777452415812591508)/0.033203125 from dual;
0.337335285505124
大约估计删除数据需要本身空间的1/3左右，甚至更大。。。
执行
alter table usWB enable row movement;
alter table usWB shrink space;
注释：shrink space支持自动段空间管理(ASSM)，就可以使用这个特性缩小段
select T.segment_name,T.BYTES/1024/1024/1024||'G' from user_segments t where t.segment_name='USWB';
| USWB |
.00006103515625G |
可以发现释放空间了
这样查看表空间是否为自动段空间管理
表空间 管理方式
| SYSTEM |
MANUAL（手动的） |
| UNDOTBS1 |
MANUAL |
| SYSAUX |
AUTO（自动的） |
| TEMP |
MANUAL |
| USERS |
AUTO |
| EXAMPLE |
AUTO |
| TS_ZYK_TEMP |
MANUAL |
| TS_ZYK_DATA |
AUTO |
| TBS_USERINFO |
AUTO |
| TBS_USWB_INDEX |
AUTO |
那么另外两张表删除数据无法无法降低空间
-- Create table
create table REWB_OLD
(
id VARCHAR2(40) not null,
rksj DATE default sysdate
)
tablespace SYSTEM
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
--查看数据占用空间
SELECT T.segment_name,T.BYTES/1024/1024/1024||'G',SYSDATE DQSJ FROM USER_SEGMENTS T WHERE T.segment_name ='REWB_OLD';
| REWB_OLD |
.00006103515625G |
2015-8-25 10:05:02 |
--插入数据
INSERT INTO REWB_OLD
select * from REWB@DBL_JWB t WHERE ROWNUM < 100001;
COMMIT;
SELECT T.segment_name,T.BYTES/1024/1024/1024||'G',SYSDATE DQSJ FROM USER_SEGMENTS T WHERE T.segment_name ='REWB_OLD';
| REWB_OLD |
.015625G |
2015-8-25 10:07:15 |
delete from REWB_OLD;
commit;
2.156s删除成功
SELECT T.segment_name,T.BYTES/1024/1024/1024||'G',SYSDATE DQSJ FROM USER_SEGMENTS T WHERE T.segment_name ='REWB_OLD';
| REWB_OLD |
.015625G |
2015-8-25 10:09:35 |
DELETE不会改变表的高水位标记，数据块还被占用着
SELECT T.segment_name,T.BYTES/1024/1024/1024||'G',SYSDATE DQSJ FROM USER_SEGMENTS T WHERE T.segment_name ='REWB_OLD';
查看之前的操作
INSERT INTO REWB_NEW SELECT * FROM REWB_OLD;
COMMIT;
INSERT INTO REWB_BAK
select * from REWB@DBL_JWB t WHERE ROWNUM < 200001;
COMMIT;
发生在删除数据之前，保证数据备份成功
SELECT T.segment_name,T.BYTES/1024/1024/1024||'G',SYSDATE DQSJ FROM USER_SEGMENTS T WHERE T.segment_name ='REWB_BAK';
| REWB_BAK |
.029296875G |
2015-8-25 10:25:36 |
SELECT T.segment_name,T.BYTES/1024/1024/1024||'G',SYSDATE DQSJ FROM USER_SEGMENTS T WHERE T.segment_name ='REWB_OLD';
| REWB_OLD |
.015625G |
2015-8-25 10:26:21 |
重新插入不同的数据，发现数据占用空间还是0.015625g,说明delete释放掉存储空间可以被重新使用
DELETE释放的存储空间一定会被利用吗？
-- Create table
create table REWB_0825
(
id VARCHAR2(40) not null,
rksj DATE default sysdate
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
INSERT INTO REWB_0825
SELECT * FROM REWB_NEW T WHERE ROWNUM <301;
COMMIT;
select dbms_rowid.rowid_block_number(rowid) rn, COUNT(1)
from REWB_0825 t
GROUP BY dbms_rowid.rowid_block_number(rowid)
| 3752316 |
51 |
| 3752314 |
54 |
| 3752318 |
54 |
| 3752319 |
38 |
| 3752315 |
52 |
| 3752317 |
51 |
DELETE from REWB_0825 t WHERE ROWNUM < 101;
COMMIT;
select dbms_rowid.rowid_block_number(rowid) rn, COUNT(1)
from REWB_0825 t
GROUP BY dbms_rowid.rowid_block_number(rowid)
| 3752316 |
51 |
| 3752318 |
54 |
| 3752319 |
38 |
| 3752315 |
6 |
| 3752317 |
51 |
INSERT INTO REWB_0825
SELECT * FROM REWB_NEW T WHERE ROWNUM <11;
COMMIT;
select dbms_rowid.rowid_block_number(rowid) rn, COUNT(1)
from REWB_0825 t
GROUP BY dbms_rowid.rowid_block_number(rowid)
| 3752316 |
51 |
| 3752318 |
54 |
| 3752319 |
48 |
| 3752315 |
6 |
| 3752317 |
51 |
发现3752314未被使用，插入大量数据查看是否出现3752314
INSERT INTO REWB_0825
SELECT * FROM REWB_NEW T WHERE ROWNUM <10001;
COMMIT;
select dbms_rowid.rowid_block_number(rowid) rn, COUNT(1)
from REWB_0825 t
GROUP BY dbms_rowid.rowid_block_number(rowid)
| 3752314 |
51 |
| 3752315 |
52 |
| 3752316 |
51 |
| 3752317 |
51 |
| 3752318 |
54 |
| 3752319 |
54 |
| 3752320 |
54 |
| 3752321 |
51 |
| 3752322 |
54 |
| 3752323 |
57 |
| 3752324 |
53 |
| 3752325 |
54 |
| 3752326 |
53 |
| 3752327 |
51 |
| 3752328 |
51 |
| 3756041 |
51 |
| 3756042 |
51 |
| 3756043 |
51 |
| 3756044 |
51 |
| 3756045 |
51 |
| 3756046 |
57 |
| 3756047 |
57 |
| 3756048 |
51 |
| 3756049 |
50 |
| 3756050 |
50 |
| 3756051 |
50 |
| 3756052 |
50 |
| 3756053 |
52 |
| 3756054 |
55 |
| 3756055 |
52 |
| 3756056 |
51 |
发现3752314确实出现了
那么何时使用被删除数据的空间呢？
在Oracle编程艺术第十章节中介绍中pctfree pctused
在手动管理控制中
pctfree :保留多少空间完成更新
pctused:不自由的块到多少才自动加入到freelist中
有画图软件就好了
1、insert的数据在90%左右的是，此块脱离freelist，不在进行数据移入
2、delete这块数据，遗留这些数据占这块数据块的40%,就可以加入到freelist，重新加入数据
结论：如果删除数据，可以使用delete，但是不会释放数据所占空间
2、truncate
Truncate删除时，本身表不被(insert(插入),update（更新）,delete(删除))使用，
考虑到WB库时时在插入，且truncate 数据空间较高，truncate时间较长，不利于数据同步
之前一次对技侦数据truncate ,数据量为60g,truncate 时间为70min,
无法对其进行有效测试。
个人感觉：数据删除时，可以使用delete,如果释放数据空间的话，建议truncate
