| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Unable to insert records (ORA-1653)
Hi
I am sorry that I fired of my first response without reading he entire problem statement.
However, that response is still valid to avoid other problems.
Yes, as the other respondant wrote, you may have fragmentation, and there may not be enough contiguous disk space available for the next extent. You can modify and try the script given below.
Two things you can do is try to lower the size of the next extent:
alter table revenues storage (next 25M);
and
Secondly, set the pctincrease on the tablespace to 1. What this will do is it will automatically COALESCE the fragmented space into contiguous space if possible via the Oracle process SMON.
alter tablespace rel_tab default storage (pctincrease 1);
Good luck !!!
Oracleguru
oracleguru_at_mailcity.com
set dof off pause off
/************************************************************************
* *
* AUTHOR : Oracleguru *
* FILE NAME : analyze_tablespace_usage.sql *
* LANGUAGE : SQL/SQL*Plus/PL-SQL *
* INPUT PARAM : This script expects 1 parameter: Tablespace_name *
* OUTPUT PARAM: None. *
* INPUT FILES : None. *
* OUTPUT FILES: $HOME/rep/analyze_tablespace_names_TNAME.lst *
* where TNAME = Tablespace_name entered on *
* command line to run this script *
* *
* DESCRIPTION : This script generates tablespace usage for a *
* TABLESPACE name entered on the command line. *
* *
************************************************************************/
set pause off termout off verify off wrap on serveroutput on size 1000000 set newpage 0 pagesize 58 linesize 80
/*
Generate tablespace analysis report only on Wednesdays. Uncomment the following statements if this is what you want to do.
whenever sqlerror exit
select 1/0
from dual
where to_char(sysdate, 'day') not in ('wednesday');
*/
clear breaks
column today new_value today noprint column time new_value time noprint
/*
Remove report file if exists, otherwise in case of problem, you may
receive a print out of an old file.
*/
host rm -f $HOME/rep/analyze_tablespace_usage_&1..lst
spool $HOME/rep/analyze_tablespace_usage_&1..lst
set feedback off
ttitle today center 'TABLE AND INDEX SPACE USAGE IN TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 1 -
analyze_tablespace_usage.sql
select to_char(sysdate, 'DD-MON-YYYY') today,
to_char(sysdate, 'HH:MI:SS AM') time from dual;
set feedback on
/*
Compute total and unused disk space in tablespace. */
DECLARE
total_blocks number; total_bytes number; unused_blocks number; unused_bytes number;/* Above High water mark */ last_used_extent_file_id number; last_used_extent_block_id number; last_used_block number; free_space number;/* Below High water mark */ user_id all_tables.owner%type; object_name all_tables.table_name%type; object_type varchar2 ( 5);--
cursor c1
is
select owner, table_name, 'TABLE'
from all_tables
where tablespace_name = upper('&1')
union
select owner, index_name, 'INDEX'
from all_indexes
where tablespace_name = upper('&1');
BEGIN
open c1;
LOOP
fetch c1
into user_id, object_name, object_type;
exit when c1%notfound;
dbms_space.unused_space
( user_id,
object_name,
object_type,
total_blocks,
total_bytes,
unused_blocks,
unused_bytes,
last_used_extent_file_id,
last_used_extent_block_id,
last_used_block
);
dbms_space.free_blocks
( user_id,
object_name,
object_type,
0,
free_space);
dbms_output.put_line
( object_type||' Name = '||user_id||'.'||object_name ); dbms_output.put_line
( '**********' );
dbms_output.put_line
( 'Total Bytes = '||to_char(total_bytes, '999,999,990')||
' Free Bytes above Water Mark = '||
to_char(unused_bytes,'999,999,990') );
dbms_output.put_line
( 'Bytes to W.Mark= '||to_char(total_bytes -
unused_bytes,'999,999,990')||
' Free Bytes below Water Mark = '||
to_char(free_space*8192, '999,999,990') );
dbms_output.put_line
(
'-------------------------------------------------------------------------
-------');
END LOOP;
END;
..
/
column object format a26 heading 'OBJECT' column file_id format 99990 heading 'FILE|ID ' column block_id format 999990 heading 'BLOCK|ID ' column blocks format 99990 heading 'BLOCKS' column bytes format 9,999,999,999 heading 'BYTES'
ttitle today center 'POSSIBLE FRAGMENTATION IN TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_tablespace_usage.sql skip 2
select 'freespace' owner, ' ' object,
file_id, block_id, blocks, bytes bytes
from sys.dba_free_space
substr(segment_name, 1, 31),
file_id, block_id, blocks, bytes bytes
from sys.dba_extents
break on bytes on report
compute sum of bytes on report
column segment_name format a26
column tablespace_name format a15
ttitle today center 'SPACE USAGE BY OBJECTS IN TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_tablespace_usage.sql skip 2
select segment_name, tablespace_name, sum(bytes) bytes
from sys.dba_extents
where tablespace_name = '&1'
group by segment_name, tablespace_name
/
set feedback off
column space heading ' SPACE' column name heading ' INDEX NAME' column btree_space format 9,999,999,999 heading 'TOTAL SPACE' column used_space format 9,999,999,999 heading 'USED SPACE'
ttitle today center 'USED AND FREE SPACE USAGE FOR TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_tablespace_usage.sql skip 2
select 'Free Space ' space, sum(bytes ) bytes
from sys.dba_free_space
where tablespace_name = upper('&1')
union
select 'Used Space ' space, sum(bytes ) bytes
from sys.dba_extents
where tablespace_name = upper('&1')
/
prompt
prompt
prompt. ********** END OF REPORT *********
spool off
exit
Sanjay Hans <sanhans_at_mbox5.singnet.com.sg> wrote in article <01be0411$5a57a100$94cc15a5_at_oempre-install>...
> Would appreciate your help on the following problem which I am facing: > > I am trying to insert around 80000 records in a table having around1521058
> records, after > inserting 65914 records, the process hangs and alertDEV.log shows the > following error message:- > > ORA-1653: unable to extend table/cluster CGO.REVENUES > by 9360 in tablespace REL_TAB > > The table REVENUES was created with following parameters:- > CREATE TABLE REVENUES ( ) > PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 230031360 > NEXT 76677120 MINEXTENTS 1 MAXEXTENTS 249 > PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "REL_TAB" > > The tablespace REL_TAB is having lot of space as shown by the result ofthe
> following query: > > select sum(bytes),tablespace_name from dba_free_space group by > tablespace_name; > > SUM(BYTES) TABLESPACE_NAME > ---------- ------------------------------ > 312926208 RBS > 2943057920 REL_IND > 832356352 REL_TAB > 187834368 SYSTEM > 629129216 TEMP > > The other useful info you may need is :- > > The result of the following query:- > select segment_name, pct_increase, sum(bytes),initial_extent,next_extent,
> max_extents, count(*) from dba_extents a, dba_tables b where
> segment_name = b.table_name and segment_type = 'TABLE' and a.owner='CGO'
> and
> segment_name in ('REVENUES') group by
> segment_name, pct_increase, max_extents, initial_extent, next_extent
> order by segment_name, max_extents
>
> is
>
> SEGMENT_NAME
>
---------------------------------------------------------------------------
> -----
> PCT_INCREASE SUM(BYTES) INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS COUNT(*)
> ------------ ---------- -------------- ----------- ----------- ----------
> REVENUES
> 0 230031360 76668928 76677120 249 3
>
> Thanks in advance.
 |
 |