情景：ORACLE11g中，表空间使用率在60%左右，数据入库不了。报
ORA-01658: unable to create INITIAL extent for segment in tablespace DATA_0000
对数据库做查询：
SQL> ttitle -
SQL> center'Database Freespace Summary'skip 2
2
SQL> comp sum of nfrags totsiz avasiz on report
SQL> break on report
SQL> set pages 999
SQL> col tsnameformat a16 justify c heading 'Tablespace'
SQL> col nfragsformat 999,990 justify c heading 'Free|Frags'
SQL> col mxfragformat 999,999 justify c heading 'Largest|Frag (MB)'
SQL> col totsizformat 999,999 justify c heading 'Total|(MB)'
SQL> col avasizformat 999,999 justify c heading 'Available|(MB)'
SQL> col pctusdformat 990 justify c heading 'Pct|Used'
SQL>
SQL> select total.TABLESPACE_NAME tsname,
2 D nfrags,
3
C/1024/1024 mxfrag,
4
A/1024/1024 totsiz,
5 B/1024/1024 avasiz,
6
(1-nvl(B,0)/A)*100 pctusd
7 from
8(select sum(bytes) A,
9
tablespace_name
10
from dba_data_files
11
group by tablespace_name) TOTAL,
12 (select sum(bytes) B,
13
max(bytes) C,
14
count(bytes) D,
15
tablespace_name
16
from dba_free_space
17
group by tablespace_name) FREE
18where
19 total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)
20 ;
TSNAME
NFRAGS MXFRAG TOTSIZ AVASIZ PCT
---------------- ---------- ---------- ---------- ---------- ---
SYSTEM
816256 130056 97986.9375 24.
657
887
756
043
6
USERS
1
4
5
420
IDX_0000
156769162569282560 7393205.93 20.
353
803
934
475
ZXT_DATA
6339216256 291840 278364.312 4.6
174
916
049
890
4
ZXT_IDX
1816256 291840 291837 0.0
010
279
605
263
157
9
TS_DSA
4206416256 10543104 5801029.87 44.
977
969
723
147
9
SYSAUX
105 195.8125 5660 1494.875 73.
588
780
918
727
9
UNDOTBS1
3057970.437522405 21573.1875 3.7
126
199
509
038
2
DATA_0000 320306516256 14730240 7626063.25 48.
228
520
037
691
2
9 rows selected
SQL>
SQL> SELECT t.tablespace_name,
2 CASE
3 WHEN t.contents = 'TEMPORARY' AND t.extent_management = 'LOCAL' THEN
4
u.bytes
5 ELSE
6
df.user_bytes - NVL(fs.bytes, 0)
7 END / 1024 / 1024 used_mb,
8 CASE
9 WHEN t.contents = 'TEMPORARY' AND t.extent_management = 'LOCAL' THEN
10
df.user_bytes - NVL(u.bytes, 0)
11 ELSE
12
NVL(fs.bytes, 0)
13 END / 1024 / 1024 free_mb,
14 fs.min_fragment / 1024 / 1024 min_fragment_mb,
15 fs.max_fragment / 1024 / 1024 max_fragment_mb,
16 (fs.bytes / 1024 / 1024) / fs.fragments avg_fragment_mb,
17 fs.fragments,
18 t.status,
19 t.contents,
20 t.logging,
21 t.extent_management,
22 t.allocation_type,
23 t.force_logging,
24 t.segment_space_management,
25 t.def_tab_compression,
26 t.retention,
27 t.bigfile
28FROM dba_tablespaces t,
29 (SELECT tablespace_name,
30
SUM(bytes) bytes,
31
MIN(min_fragment) min_fragment,
32
MAX(max_fragment) max_fragment,
33
SUM(fragments) fragments
34
FROM (SELECT tablespace_name,
35
SUM(bytes) bytes,
36
MIN(bytes) min_fragment,
37
MAX(bytes) max_fragment,
38
COUNT(*) fragments
39
FROM dba_free_space
40
GROUP BY tablespace_name
41
UNION ALL
42
SELECT tablespace_name,
43
SUM(bytes) bytes,
44
MIN(bytes) min_fragment,
45
MAX(bytes) max_fragment,
46
COUNT(*) fragments
47
FROM dba_undo_extents
48
WHERE status = 'EXPIRED'
49
GROUP BY tablespace_name)
50 GROUP BY tablespace_name) fs,
51 (SELECT tablespace_name, SUM(user_bytes) user_bytes
52
FROM dba_data_files
53 GROUP BY tablespace_name
54
UNION ALL
55
SELECT tablespace_name, SUM(user_bytes) user_bytes
56
FROM dba_temp_files
57 GROUP BY tablespace_name) df,
58 (SELECT tablespace_name, SUM(bytes_used) bytes
59
FROM gv$temp_extent_pool
60 GROUP BY tablespace_name) u
61WHERE t.tablespace_name = df.tablespace_name(+)
62 AND t.tablespace_name = fs.tablespace_name(+)
63 AND t.tablespace_name = u.tablespace_name(+)
64;
TABLESPACE_NAME
USED_MBFREE_MB MIN_FRAGMENT_MB MAX_FRAGMENT_MB AVG_FRAGMENT_MBFRAGMENTS STATUSCONTENTSLOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE FORCE_LOGGING SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION BIGFILE
------------------------------ ---------- ---------- --------------- --------------- --------------- ---------- --------- --------- --------- ----------------- --------------- ------------- ------------------------ ------------------- ----------- -------
SYSTEM
32067.0625 97986.9375
0.9375 16256 12248.3671875
8 ONLINEPERMANENT LOGGING LOCAL
SYSTEM
NO
MANUAL
DISABLED
NOT APPLY NO
USERS
0
4
4
4
4
1 ONLINEPERMANENT LOGGING LOCAL
SYSTEM
NO
AUTO
DISABLED
NOT APPLY NO
IDX_0000
1889259.06 7393205.93
0.0625 16256 47.159871769929 156769 ONLINEPERMANENT LOGGING LOCAL
SYSTEM
NO
AUTO
DISABLED
NOT APPLY NO
ZXT_DATA
13472.6875 278364.312
0.0625 16256 4.391158387493663392 ONLINEPERMANENT LOGGING LOCAL
SYSTEM
NO
AUTO
DISABLED
NOT APPLY NO
ZXT_IDX
0 291837 15999 16256 16213.166666666 18 ONLINEPERMANENT LOGGING LOCAL
SYSTEM
NO
AUTO
DISABLED
NOT APPLY NO
TS_DSA
4741993.06 5801006.93
0.1875 16256 137.9123442811942063 ONLINEPERMANENT LOGGING LOCAL
SYSTEM
NO
AUTO
DISABLED
NOT APPLY NO
SYSAUX
4164.125 1494.875
0.0625195.8125 14.236904761904105 ONLINEPERMANENT LOGGING LOCAL
SYSTEM
NO
AUTO
DISABLED
NOT APPLY NO
UNDOTBS1
331.1875 22072.8125
0.0625 7970.4375 22.755476804123970 ONLINEUNDOLOGGING LOCAL
SYSTEM
NO
MANUAL
DISABLED
NOGUARANTEE NO
DATA_0000
7104444.37 7625644.62
0.0625 16256 2.38060730012003203235 ONLINEPERMANENT LOGGING LOCAL
SYSTEM
NO
AUTO
DISABLED
NOT APPLY NO
TEMP
020478
ONLINETEMPORARY NOLOGGING LOCAL
UNIFORM NO
MANUAL
DISABLED
NOT APPLY NO
10 rows selected
SQL>
SQL> select count(distinct segment_name) from dba_extents where tablespace_name='DATA_0000';
COUNT(DISTINCTSEGMENT_NAME)
---------------------------
1234
SQL> select blocks,count(*) from dba_extents where tablespace_name='DATA_0000' group by blocks;
BLOCKS COUNT(*)
---------- ----------
61966740
832 55
736 50
1216 33
1472 35
1664 44
544 78
1120 49
1248 37
1728 29
1536 97
12 144090
178 81
82 82
156 87
138 67
70 98
184 87
212 72
114 92
86106
244 71
110 67
200 57
210 80
228 78
52 83
44104
512456
576 54
384 58
1760 35
992 31
896 45
672 38
416 50
448 49
352 44
214 68
54 99
124 79
204 78
218 62
60 86
144 90
48100
158 82
146 77
206 65
204813466
1792107
1152 33
1056 52
768268
1984 32
1088 33
1888 29
1920 24
3062983
8 201296
16 116868
74 90
238 77
66 99
172 79
190 95
216 74
50 94
58 98
76 78
232 69
226 76
222346
64 6439
1344 44
320 51
864 46
1376 38
640 36
1632 33
56 88
202 80
108 92
148 80
84 92
130 91
94 84
152 79
140 79
102 87
42 73
104 74
186 90
162 84
72 81
136 87
154 89
288147
96 4092
704 34
1824 29
608 49
2463519
78 74
46 85
132 79
116 86
236 77
98 86
40 89
208 77
323746884
256 158090
1696 36
224 2045
128 3256
1952 25
2016 33
1568 40
1440 28
1888794
2267822
2075922
198 87
164 89
68 71
220100
230 72
118 96
240 90
222 68
62 90
248 72
170 95
134 81
254 73
188 92
38 90
480 34
160 2762
1024189
1312 47
1504 22
1408 26
1600 36
1184 32
2663060
14 144175
126107
34 94
88 88
250 81
176 78
122 91
196 98
112100
142100
168 76
252 91
36 76
1280121
1856 46
192 2496
960 39
928 47
800 58
455485
10 142841
182 81
2864518
246 72
242 75
166 83
194 78
80 90
234 85
106 91
90 80
100 94
120 72
92 88
150 88
174 83
180 79
184 rows selected
free extent 过碎满足不了要求。
是运营商大数据业务的OLAP分析系统，每天新建1000张表，数据保存1--3个月，数据不会做更新删除操作，直接DROP。
IMP/EXP重建表空间的方案，因数据量太大可操作性比较低，还有其他的解决方案吗？
后续其他地方打算重新规划表空间，分出多个表空间，一个表空间存储一个月份或是更少时间的数据，直接删除表空间的方法避免同样的问题？希望大家多给我提一些解决方案。谢谢！
|
