SOLVED

Resolving "Unable to extend / create" errors with non-contiguous free space below high water mark

Asked by ahmedalhedewy••45 views•oracle
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
Summary
Getting unable to extend errors like ORA-1653, ORA-1654, ORA-1658, ORA-1688, ORA-1659, ORA-1683, ORA-3233, ORA-3234, ORA-1692, ORA-1691, ORA-3238 as there is no free space found above high water mark.

EXAMPLE ERRORS

ORA-1653: unable to extend table %s.%s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1654: unable to extend index %s.%s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for an index segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1655: unable to extend cluster %s.%s by %s for tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for a cluster segment in tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1658: unable to create INITIAL extent for segment in tablespace %s
Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.
Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL

ORA-1659 unable to allocate MINEXTENTS beyond %s in tablespace %s
Cause: Failed to find sufficient contiguous space to allocate MINEXTENTS for the segment being created.
Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with smaller value for MINEXTENTS, NEXT or PCTINCREASE

ORA-1683: unable to extend index %s.%s partition %s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for index segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1688: unable to extend table %s.%s partition %s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for table segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1691: unable to extend lob segment %s.%s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for LOB segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1692: unable to extend lob segment %s.%s partition %s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for LOB segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-3233: unable to extend table %s.%s subpartition %s by %s in tablespace %s
Cause: Failed to allocate an extent for table subpartition segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-3234: unable to extend index %s.%s subpartition %s by %s in tablespace %s
Cause: Failed to allocate an extent for index subpartition segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-3238: unable to extend LOB segment %s.%s subpartition %s by %s in tablespace %s
Cause: An attempt was made to allocate an extent for LOB subpartition segment in tablespace, but the extent could not be allocated because there is not enough space in the tablespace indicated.
Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

Free space found below high water mark.

set pagesize 60
set newpage 0
set echo off
ttitle center 'Contiguous Extents Report' skip 3
break on "TABLESPACE NAME" skip page duplicate
spool contig_free_space.lis
rem
column "CONTIGUOUS BYTES" format 999,999,999
column "COUNT" format 999
column "TOTAL BYTES" format 999,999,999
column "TODAY" noprint new_value new_today format a1
rem

with a
as
(
SELECT tablespace_name,file_id,block_id,bytes,blocks,LEVEL,CONNECT_BY_ROOT block_id as root,
count(*) over(partition by tablespace_name, file_id, CONNECT_BY_ROOT block_id) as EXTENTS,
sum(bytes) over(partition by tablespace_name, file_id, CONNECT_BY_ROOT block_id) as CONTIGUOUS_BYTES
FROM dba_free_space
CONNECT BY PRIOR (block_id+blocks) = block_id
and PRIOR tablespace_name = tablespace_name
and PRIOR file_id = file_id
)
select TABLESPACE_NAME "TABLESPACE NAME",
CONTIGUOUS_BYTES "CONTIGUOUS BYTES"
from a
where (tablespace_name, file_id, block_id) in
(select tablespace_name, file_id, block_id from
a
group by tablespace_name, file_id, block_id
having count(*)=1
)
order by 1,2 desc;

with a
as
(
SELECT tablespace_name,file_id,block_id,bytes,blocks,LEVEL,CONNECT_BY_ROOT block_id as root,
count(*) over(partition by tablespace_name, file_id, CONNECT_BY_ROOT block_id) as EXTENTS,
sum(bytes) over(partition by tablespace_name, file_id, CONNECT_BY_ROOT block_id) as CONTIGUOUS_BYTES
FROM dba_free_space
CONNECT BY PRIOR (block_id+blocks) = block_id
and PRIOR tablespace_name = tablespace_name
and PRIOR file_id = file_id
)
select tablespace_name,count(*) "# OF EXTENTS",sum(CONTIGUOUS_BYTES) "TOTAL BYTES"
from a
where (tablespace_name, file_id, block_id) in
(select tablespace_name, file_id, block_id from
a
group by tablespace_name, file_id, block_id
having count(*)=1
)
group by tablespace_name;

Solutions(1)

Accepted Solution
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
164
165
166
167
168
169
170
171
172
173
174
Follow below steps to use free space below high water mark.
With below steps, you could use the available free space up to 1MB of contiguous free space below high water mark.
If contiguous free space available below high water mark is below 1 MB, then you might receive errors.

Check free space in the tablespace

Query to monitor free space
select * from dba_free_space where tablespace_name = '<tablespace_name>';

select sum(bytes)/1024/1024 BYTES_MB from dba_free_space where tablespace_name = '<tablespace_name>';

Below will display contiguous free space for all tablespaces.
set pagesize 60
set newpage 0
set echo off
ttitle center 'Contiguous Extents Report' skip 3
break on "TABLESPACE NAME" skip page duplicate
spool contig_free_space.lis
rem
column "CONTIGUOUS BYTES" format 999,999,999
column "COUNT" format 999
column "TOTAL BYTES" format 999,999,999
column "TODAY" noprint new_value new_today format a1
rem

with a
as
(
SELECT tablespace_name,file_id,block_id,bytes,blocks,LEVEL,CONNECT_BY_ROOT block_id as root,
count(*) over(partition by tablespace_name, file_id, CONNECT_BY_ROOT block_id) as EXTENTS,
sum(bytes) over(partition by tablespace_name, file_id, CONNECT_BY_ROOT block_id) as CONTIGUOUS_BYTES
FROM dba_free_space
CONNECT BY PRIOR (block_id+blocks) = block_id
and PRIOR tablespace_name = tablespace_name
and PRIOR file_id = file_id
)
select TABLESPACE_NAME "TABLESPACE NAME",
CONTIGUOUS_BYTES "CONTIGUOUS BYTES"
from a
where (tablespace_name, file_id, block_id) in
(select tablespace_name, file_id, block_id from
a
group by tablespace_name, file_id, block_id
having count(*)=1
)
order by 1,2 desc;

with a
as
(
SELECT tablespace_name,file_id,block_id,bytes,blocks,LEVEL,CONNECT_BY_ROOT block_id as root,
count(*) over(partition by tablespace_name, file_id, CONNECT_BY_ROOT block_id) as EXTENTS,
sum(bytes) over(partition by tablespace_name, file_id, CONNECT_BY_ROOT block_id) as CONTIGUOUS_BYTES
FROM dba_free_space
CONNECT BY PRIOR (block_id+blocks) = block_id
and PRIOR tablespace_name = tablespace_name
and PRIOR file_id = file_id
)
select tablespace_name,count(*) "# OF EXTENTS",sum(CONTIGUOUS_BYTES) "TOTAL BYTES"
from a
where (tablespace_name, file_id, block_id) in
(select tablespace_name, file_id, block_id from
a
group by tablespace_name, file_id, block_id
having count(*)=1
)
group by tablespace_name;

Example - Query to monitor free space
select * from dba_free_space where tablespace_name = 'TEST_TABLESPACE';

select sum(bytes)/1024/1024 BYTES_MB from dba_free_space where tablespace_name = 'TEST_TABLESPACE';

Please find below command to use free space.

Allocate space as much is required for the data load and available below high water mark.

Command to allocate available free space
alter <object_type> <object_name> allocate extent [(size n[k|m|g|t] )];

alter table <table_name> allocate extent;

alter table <table_name> allocate extent ( size n );

alter table <table_name> modify partition <table_partition_name> allocate extent;

alter table <table_name> modify partition <table_partition_name> allocate extent ( SIZE n );

alter index <index_name> allocate extent;

alter index <index_name> modify partition <index_partition_name> allocate extent;

alter index <index_name> modify partition <index_partition_name> allocate extent(size n);

Note: Size clause can have different metrics like k|m|g|t as per size clause in the alter statement.
Example - Allocating available free space
SQL> alter table TEST allocate extent;

Table altered.

SQL> alter table TEST allocate extent(size 61865984);

Table altered.

SQL> ALTER TABLE TEST MODIFY PARTITION TEST_Q1 ALLOCATE EXTENT;

Table altered.

SQL> ALTER TABLE TEST MODIFY PARTITION TEST_Q1 ALLOCATE EXTENT(SIZE 1M);

Table altered.

SQL> ALTER TABLE SYS.TEST MODIFY PARTITION TEST_Q1 ALLOCATE EXTENT(SIZE 1G);

Table altered.

SQL> alter index test_idx allocate extent;

Index altered.

SQL> alter index test_idx allocate extent(size 1k);

Index altered.

SQL> alter index test_idx modify partition TEST_Q1 allocate extent;

Index altered.

SQL> alter index test_idx modify partition TEST_Q1 allocate extent(size 1m);

Index altered.

SQL> alter index sys.test_idx modify partition TEST_Q1 allocate extent(size 1g);

Index altered.

SQL>

Test Case
SQL> select * from dba_free_space where tablespace_name = 'TEST';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
--------------- ---------- ---------- ---------- ---------- ------------
TEST 18 904 983040 120 1024
TEST 18 12160 5242880 640 1024

2 rows selected.

SQL> alter table TEST allocate extent;

Table altered.

SQL>
SQL> select * from dba_free_space where tablespace_name = 'TEST';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
--------------- --------- ---------- ---------- ---------- ------------
TEST 18 904 983040 120 1024
TEST 18 12288 4194304 512 1024 >>>>> block count is reduced from 640 to 512

2 rows selected.

SQL> alter table TEST allocate extent (size 1m);

Table altered.

SQL> select * from dba_free_space where tablespace_name = 'TEST';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
--------------- ------- ----------- ---------- ---------- ------------
TEST 18 904 983040 120 1024
TEST 18 12416 3145728 384 1024 >>>>> block count is reduced from 512 to 384

2 rows selected.
ahmedalhedewy•

Post Your Solution