RA-01653: UNABLE TO EXTEND TABLE SYS.IDL_UB1 $

[rodfbar]

Environment Information:
* Oracle version: Oracle9I Release 9.2.0.5.0
* Operating system: Windows Server 2003

OLA PERSONAL.


by kindness was running a command
exec dbms_java.revoke_permission and gave the error cited

Someone knows what can Be done?

Thanks

[victorhugomuniz]

Select all

 
The Oracle docs note this on the ora-01653 error: 
  
ORA-01653: unable to extend table string.string by string in tablespace string 
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. 
 
Oracle MOSC Documents offer great resources on resolving ORA-01653,  namely the nature of the problem as it relates to space availability: 
 
Explanation: 
------------ 
This error does not necessarily indicate whether or not you have enough space  
in the tablespace, it merely indicates that Oracle could not find a large enough area of free 
contiguous space in which to fit the next extent. 
 
 
Diagnostic Steps: 
----------------- 
1. In order to see the free space available for a particular tablespace, you must 
use the view DBA_FREE_SPACE. Within this view, each record represents one 
fragment of space. How the view DBA_FREE_SPACE can be used to determine  
the space available in the database is described in Note 121259.1 Using DBA_FREE_SPACE 
 
Furthermore, MOSC offers these choices in resolving ORA-01653 (as quoted below): 
 
Manually COALESCE Adjacent Free Extents 
Add a DATAFILE 
Resize DATAFILE 
Enable AUTOEXTEND 
Defragment tablespace 
View temporary segments for space 

[rodfbar]

Thanks for the return ...

Would you have any tips?
already passed this situation ???

could be any process that is running that is making Tablespace System burst?

I already guaranteed that there are only objects of the Oracle own ..

ABRCS

[victorhugomuniz]

This error does not necessarily indicate whether or not you have enough space on the table, it just indicates that Oracle could not find a large enough area.

Have you ever done the recommended steps?

Select all

 
Furthermore, MOSC offers these choices in resolving ORA-01653 (as quoted below):  
 
Manually COALESCE Adjacent Free Extents  
Add a DATAFILE  
Resize DATAFILE  
Enable AUTOEXTEND  
Defragment tablespace  
View temporary segments for space  

[rodfbar]

Really the tablespace system space was using the limit.

What I found strange is because this procee requires space on System ...

Select all

 DBMS_JAVA.revoke_permission

I'm not creating anything ... You know this Built in?

Abrçs

[diegolenhardt]

Maybe you are with the set UAXIT_TRAIL PARAMETRO, by default The audit table is in

Select all

SYSTEM, AUD$

may be this ..

If not The case increases the Datafile or add another.

[rodfbar]

Ola Diego ...

The DBA did a job in the bank here last week, then this table sys.idl_ub1 $ doubled from such work .... (ie In a week he doubled in size 5 years)

Do you think q; and this parameter that you passed me can be interfering ??

What is this parameter?


Abrçs ...

[diegolenhardt]

What I mentioned is a bank audit parameter, if it is active will record a lot in sys.aud $, which could be increasing your tablespace,

thus verifies:

Select all

 
select segment_name, sum(bytes) / 1024 / 1024 mega 
from dba_segments a 
where tablespace_name = 'SYSTEM' 
group by a.segment_name 
order by 2 desc 

here in my case has 144 mega sys.idl_ub1 $ ...

See if this is ...

[rodfbar]

our here the idl_ub1 is with 700 mb ....

why would it be so big ???

What is stored in this table ???


Thanks

[diegolenhardt]

I think the metadata gets ... would give a look at a look,

now it's kind of complicated as soon as I do a search ...

=]

[rodfbar]

Okay .. quiet .. anyway thanks for the attention ...

[diegolenhardt]

From what I saw is relating to Java, did you say that a load of objects was made there? triggers, etc. etc.

If this bank is very critical man, add more space to System and good, in any case, I would make a backup of the metadata,
[[0]


and then you can try to make a move from this table to another tablespace, if the table has empty space in the middle, the move will reorganize,

As SYS,

Select all

 
alter table IDL_UB1$ move tablespace USERS; 
alter table IDL_UB1$ move tablespace system; 

As he said, it may decrease, this may interfere in performance,

unfortunately more than that I do not know what do ...

= /

Hugs

[rodfbar]

Following the diego's tip the error was corrected ...

These days I'vê been doing a performacle tests and I saw that these Grants Java, after running some he switched to get extremely heavy ...

For example, it turned 10 times for 1.30 sec each time
the next 10 took 2.30 ...
soon after this time was for 20 seconds. And so worse ..

This Taxpo de Grant weighs in the bank this way? I found it extremely heavy ... We are thinking until migrating these routines ...

Abrçs ...