Oracle space puzzlement

[suhaimi]

Hi all,
I am very confused with oracle extent. So please help me out. I got this error ORA-1653: unable to extend table MYtable by 500 in tablespace MYTBS.
The initial and next extent of the table is 400MB
The free space in the tablespace is 4 GB
The largest free space in one of the datafiles of MYTBS is 3GB

Why did I get this error despite the large freespace in the datafile?

Thanks,
Suhaimi

 

[SantaMufasa]

Suhaimi,

Your problem is a bit puzzling at this point. The "500" to which the error refers means database blocks. If your database blocks are 8192 chrs. each, then it is looking for 4MB of contiguous free blocks. If your db blocks are 16k each, then Oracle is looking for 8MB of space.

If your initial and next extents are 400MB, I am puzzled as to why it is looking for only 4-16MB of space for this next extent. Could you please run these queries and post the results:
1) "select initial_extent, next_extent, pct_increase
from user_tables
where table_name = 'MYTABLE';"

2)"select max(bytes) from dba_free_space where tablespace_name = 'MYTBS';"

These queries will confirm definitively important characteristics of both the table and tablespace in question. It appears you may have already done these queries, but I just want to be sure of the results.

Looking forward to your results posting.

Dave
Sandy, Utah, USA @ 06:14 GMT, 23:14 Mountain Time

 

[suhaimi]

Hi Dave,
I can't reproduce the error since I've added some datafiles to the tablespace.
Fortunately, I have a new case.

1) select initial_extent, next_extent, pct_increase
from user_tables
where table_name = 'FWHDBLOTATTRINSTANCE';
INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
471859200 471859200 0

2) select max(bytes) from dba_free_space where tablespace_name = 'HDB';
MAX(BYTES)
364847104

3) ts#=4 is HDB tbs
select a.file#, b.file_id, a.ts#, a.name, a.bytes, sum(b.bytes) s from v$datafile a,dba_free_space b
where a.file#=b.file_id and a.ts#=4
group by a.file#,b.file_id,a.ts#,a.name,a.bytes
order by s desc
TS# NAME BYTES SUM
4 "/ora7/oradata/SILHDB/HDB02.dbf" 1993342976 569237504
4 "/ora1/oradata/SILHDB/HDB15.dbf" 1993342976 364847104
4 "/ora1/oradata/SILHDB/HDB11.dbf" 1993342976 354361344
4 "/ora7/oradata/SILHDB/HDB08.dbf" 1993342976 354361344
.......
I have the biggest chunk is 569237504 but still it gives me error ORA-1653. The datafile "/ora7/oradata/SILHDB/HDB02.dbf" is active/online.

Rgds,
Suhaimi

 

[sem]

I see that the biggest chunk is 364847104, that is less than required 471859200. 569237504 is the whole free space, not a size of chunk. Unfortunately extent needs a single slice, that of course can not be allocated.

Regards, Dima

 

[suhaimi]

Hi,
How do I combine the small chunks into a single chunk?

 

[SantaMufasa]

Suhaimi,

Instead of trying to combine free-space fragments into a gigantic 472MB extent, you are much better off (in my opinion) altering the table to accept a smaller NEXT EXTENT with code such as:

ALTER TABLE MYtable STORAGE (NEXT 10M);

Dave
Sandy, Utah, USA @ 05:19 GMT, 22:19 Mountain Time

 

[parbhani]

Hi,

If you want to build a free extent of that size, only way is to RE-BUILD few tables in that tablespace .
Get the syntex from the manual.
No other way .
Re-build will be a massive operation in your case as it will as good as re-create the tables.
After that coalease the tablespace .

Cheers

 

[SantaMufasa]

Parbhani,

I disagree that unfragging a tablespace is "a massive operation". If I have a badly fragmented tablespace and fragmented tables (from swiss cheese holes amongst the blocks), we reorganize and defragment at a rate of about 360MB/minute (depending upon processor).

We use this process:

1) Starting with smaller tablespaces, create a second tablespace (named "xyz2" for old tablespace "xyz&quot.
2) Run the following script that creates the SQL to rebuild tables and indexes for an entire tablespace:

set echo off
set feedback off
accept tsname prompt "Which tablespace do you want to reorganize?: "
set pagesize 0
set verify off
set trimspool on
spool TempSwissCheese&tsname..sql
prompt Set echo on
prompt set feedback off
select 'alter table '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
' */ move parallel nologging tablespace &tsname'||
'2;'
from dba_extents
where tablespace_name = upper('&tsname') and segment_type = 'TABLE'
group by owner,segment_name
order by sum(bytes) desc,owner, segment_name
/
select 'alter index '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
' */ rebuild parallel tablespace &tsname'||'2;'
from dba_extents
where tablespace_name = upper('&tsname') and segment_type = 'INDEX'
group by owner,segment_name
order by sum(bytes) desc,owner, segment_name
/
prompt set feedback on
spool off
prompt
prompt Wrote 'TempSwissCheese&tsname..sql'
prompt
set pagesize 35

3) During a time when there are no users accessing the tablespace under reorganization, run the 'TempSwissCheese&tsname..sql' to MOVE table and indexes to the new tablespace.
4) Since the Oracle "MOVE" statement does not work for tables with a LONG column, run the following script that creates the EXP and IMP statements for tables with a LONG column, and script the DROP commands for those tables from the old tablespace:
set pagesize 0
set trimspool on
set linesize 500
set feedback off
set verify off
set echo off
accept tsname prompt "Enter the tablespace name that holds the tables with LONGs: "
accept syspw prompt "Enter the password for SYS: "
spool TempExp&tsname..bat
select 'exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y '
||'file='
||owner||'.'||segment_name||'.dump tables='||owner||'.'||segment_name
||' userid=sys/&syspw@'||instance_name
from dba_extents, v$instance
where tablespace_name = upper('&tsname') and segment_type = 'TABLE'
group by owner,segment_name,instance_name
order by sum(bytes),owner,segment_name
/
spool off
prompt
prompt Wrote "TempExp&tsname..bat"
prompt
spool TempDropLongs&tsname..sql
prompt set echo on
prompt set feedback off
select 'drop table '||owner||'.'||table_name||' cascade constraints;'
from dba_tables
where tablespace_name = upper('&tsname')
order by owner,table_name
/
select 'alter user '||username||' default tablespace '||default_tablespace||'2;'
from dba_users
where default_tablespace = upper('&tsname')
order by username
/
spool off
prompt
prompt Wrote "TempDropLongs&tsname..sql"
prompt
spool TempImp&tsname..bat
select 'imp buffer=15000000 grants=y feedback=1000 full=Y file='
||owner||'.'||segment_name||'.dump '
||' userid=sys/&syspw@'||instance_name
from dba_extents, v$instance
where tablespace_name = upper('&tsname') and segment_type = 'TABLE'
group by owner,segment_name,instance_name
order by sum(bytes),owner,segment_name
/
spool off
set feedback on
set linesize 180
prompt
prompt 'Wrote "TempImp&tsname..bat"'
prompt
5) At the o/s prompt run the "TempExp&tsname..bat" script for the tablespace.
6) Run the "TempDropLongs&tsname..sql" script to get rid of the tablespace's tables with a LONG column.
7) At the o/s prompt run the "TempImp&tsname..bat" to re- create the tables with a LONG column.
8) At this point, the old, fragmented tablespace should be empty. "DROP TABLESPACE old_tablespace_name;"
9) At the o/s prompt erase or rm the files that supported the old tablespace, reclaiming the space for the o/s.
10) Repeat steps 1-9 for other fragmented tablespaces.

Cheers,

Dave,
Sandy, Utah, USA @ 03:49 GMT, 20:49 Mountain Time