db alert log
2020-09-01T12:55:08.177311+08:00
ORA-1688: unable to extend table TEST.TABLE_ACTION partition PART_100_202009 by 512 in tablespace DATALOG100
ORA-1688: unable to extend table TEST.TABLE_ACTION partition PART_100_202009 by 512 in tablespace DATALOG100
ORA-1688: unable to extend table TEST.TABLE_ACTION partition PART_100_202009 by 512 in tablespace DATALOG100
ORA-1688: unable to extend table TEST.TABLE_ACTION partition PART_100_202009 by 512 in tablespace DATALOG100 <<<<<<
表空间
SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
------------------------------ ---------- ---------- ---------- ------ --- ----------------------
DATALOG100 1669119 1489465 179654 90% NO |################## |<<<<<<< 增加了两个数据文件30G*2,加之前也是free100G以上
表segment信息
SQL> @seg TEST.TABLE_ACTION
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
6777 TEST TABLE_ACTION PART_100_201908 TABLE PARTITION DATALOG100 433728 594 931008
7329 TEST TABLE_ACTION PART_100_201909 TABLE PARTITION DATALOG100 469056 594 563213
5791 TEST TABLE_ACTION PART_100_201910 TABLE PARTITION DATALOG100 370624 594 574349
5458 TEST TABLE_ACTION PART_100_201911 TABLE PARTITION DATALOG100 349312 594 618061
6610 TEST TABLE_ACTION PART_100_201912 TABLE PARTITION DATALOG100 423040 560 1797906
5339 TEST TABLE_ACTION PART_100_202001 TABLE PARTITION DATALOG100 341696 582 1042962
4666 TEST TABLE_ACTION PART_100_202002 TABLE PARTITION DATALOG100 298624 544 846217
6455 TEST TABLE_ACTION PART_100_202003 TABLE PARTITION DATALOG100 413120 593 783115
6228 TEST TABLE_ACTION PART_100_202004 TABLE PARTITION DATALOG100 398592 593 1976594
6657 TEST TABLE_ACTION PART_100_202005 TABLE PARTITION DATALOG100 426048 572 1973522
9425 TEST TABLE_ACTION PART_100_202006 TABLE PARTITION DATALOG100 603200 576 1759250
9012 TEST TABLE_ACTION PART_100_202007 TABLE PARTITION DATALOG100 576768 576 1755666
7873 TEST TABLE_ACTION PART_100_202008 TABLE PARTITION DATALOG100 503872 568 1110542
236 TEST TABLE_ACTION PART_100_202009 TABLE PARTITION DATALOG100 15104 568 1058574
SQL>
TABLE SEGMENT EXTENTS
SQL> select extent_id,FILE_ID,BLOCK_ID, BLOCKS from dba_extents where tablespace_name=‘DATALOG100’ and owner=‘TBCS’ and segment_name='TABLE_ACTION ’ and partition_name=‘PART_100_202009’ order by EXTENT_ID;
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
0 568 1058569 256
1 567 1954057 512
2 566 1704969 512
3 1164 1870592 512
4 1164 1941248 512
5 1164 1942272 512
6 1164 1943296 512
7 1164 1944320 512
8 1164 1945344 512
9 1164 1947392 512
10 1164 1948416 512
11 1164 1949440 512
12 1164 1950976 512
13 1164 1953024 512
14 1164 1954048 512
15 1164 1955072 512
16 1164 1957120 512
17 1164 1958656 512
18 1164 1959680 512
19 1164 1961728 512
20 1164 1962752 512
21 1164 1963264 512
22 1164 1965312 512
23 1164 22272 512
24 1164 23808 512
25 1164 24320 512
26 1176 256 512
27 1176 2816 512
28 1176 3840 512
29 1176 4864 512
30 1177 256 512<<<扩展的区都是512 blocks
31 rows selected.
表空间FREE EXTENTS
select FILE_ID,RELATIVE_FNO,count(*),max(BLOCKS),min(BLOCKS),sum(BLOCKS),avg(blocks) from DBA_FREE_SPACE where tablespace_name='DATALOG100'
group by FILE_ID,RELATIVE_FNO order by FILE_ID;
FILE_ID RELATIVE_FNO COUNT(*) MAX(BLOCKS) MIN(BLOCKS) SUM(BLOCKS) AVG(BLOCKS)
---------- ------------ ---------- ----------- ----------- ----------- -----------
540 943 283 192 64 32704 115.561837
541 942 261 192 64 29696 113.777778
542 926 450 192 64 49280 109.511111
543 923 518 192 4 53756 103.776062
544 911 406 216 4 50140 123.497537
545 897 267 192 64 30848 115.535581
546 883 305 192 4 35428 116.157377
547 882 328 192 4 42820 130.54878
548 875 320 192 4 39588 123.7125
549 839 183 192 4 23964 130.95082
550 830 278 192 64 29440 105.899281
551 821 646 192 64 82048 127.009288
552 813 449 192 64 54144 120.587973
553 785 458 192 64 54272 118.497817
554 779 458 192 4 53260 116.28821
555 755 532 384 64 62336 117.172932
556 754 461 320 64 53248 115.505423
557 753 466 320 64 54784 117.562232
558 738 1082 320 64 124544 115.10536
559 734 1006 320 64 118720 118.011928
560 727 1174 384 4 126788 107.996593
561 717 1143 320 8 137224 120.055993
562 715 1154 384 4 140596 121.833622
563 681 697 320 64 81216 116.522238
564 671 713 320 64 82552 115.781206
565 661 664 384 64 78144 117.686747
566 646 1101 384 64 125504 113.990917
567 604 1113 384 4 127132 114.224618
568 603 1126 384 4 114704 101.868561
569 582 1179 384 4 136608 115.867684
570 549 864 384 64 96448 111.62963
571 548 881 384 56 94904 107.723042
572 515 1378 320 4 135324 98.201003
573 505 1085 384 4 106052 97.7437788
574 496 1123 384 4 125312 111.586821
575 277 1398 384 4 164460 117.639485
576 276 1461 384 4 163876 112.167009
577 275 1293 384 4 145992 112.909513
578 274 1152 384 4 128768 111.777778
579 273 1158 384 4 139968 120.870466
580 272 1184 384 56 143096 120.858108
581 271 1368 384 4 136416 99.7192982
582 270 1348 384 4 145324 107.807122
583 269 1293 384 4 148524 114.867749
584 268 1254 384 4 137640 109.760766
585 267 1083 384 4 125340 115.734072
586 266 1361 384 4 143068 105.119765
587 265 1363 384 4 145912 107.052091
588 264 1312 384 4 144760 110.335366
589 263 1100 384 4 139092 105.452616
590 262 1445 384 4 147868 102.330796
591 261 1470 384 4 145452 98.9469388
592 142 1625 384 4 178632 109.927385
593 141 1833 384 4 199624 108.905619
594 140 1525 384 4 155928 102.247869
595 139 1534 384 4 163756 106.750978
596 138 1496 384 4 159212 106.425134
597 137 1416 384 4 152880 107.966102
598 136 1569 392 4 148688 94.7660931
599 135 1466 384 4 154028 105.066849
600 134 1508 384 4 161628 107.180371
601 133 1646 416 4 155060 94.2041312
602 132 1566 384 4 156832 100.148148
603 131 1240 384 4 135992 109.670968
604 130 1204 384 4 141672 117.667774
605 129 1477 384 4 148356 100.444144
606 30 1297 384 4 129888 100.14495
1150 127 3 128 64 256 85.3333333
1164 143 3 192 64 384 128<<<在新加下面文件之前找不到连续的512 BLOCKS的空间，碎片化严重
1176 153 5 516096 192 1959360 391872<<<ORA-1688后新加的数据文件
1177 154 5 516096 192 1964992 392998.4<<<ORA-1688后新加的数据文件
71 rows selected.
最后修改时间：2024-11-13 10:14:23
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
