Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: sys.IDL_UB1$

RE: sys.IDL_UB1$

From: Jamadagni, Rajendra <>
Date: Mon, 14 May 2001 12:52:23 -0700
Message-ID: <>


I think these tables deal with p-code chunks for pl/sql objects ... do you have lots of server side code?
It is possible that you can truncate these tables, but then you'll have to recompile ALL (including SYS/SYSTEM) owned packages and user packages. Not recommended without help from OWS ...

Here is Oracle Technical note .....


Doc ID: Note:43018.1
Type: FAQ
 Content Type: TEXT/PLAIN
Creation Date: 30-APR-1998
Last Revision Date: 30-APR-1998  


The Designer/2000 User Installation Guide (UIG) describes an 'optional' step to
alter the storage parameters of SYS tables: IDL_UB1$ and IDL_UB2$. Occasionally, we take calls from customers who misunderstand why this step is
optional and why it is mentioned in the UIG at all. Sometimes we also have to
check these tables during certain installation problems, and this article is

intended to give a brief overview of what these tables are used for and how you
can check the current storage parameters of them.  

Alter Table Storage Parameters

     alter table IDL_UB1$ pctfree 0 pctused 50
     storage (next 500K pctincrease 0);
     alter table IDL_UB2$ pctfree 0 pctused 50
     storage (next 500K pctincrease 0);

Overview Of Tables

These alter storage statements should reduce the amount of space being used to store the Designer/2000 PL/SQL packages in the SYSTEM tablespace. However, if the users are also going to create their own PL/SQL packages and

procedures on the database, then these storage values may have to be reviewed
to ensure there is sufficient space for them.

NOTE: these tables and their corresponding views are meant for kernel internal
usage only and should NOT be queried by users!    

Checking Storage Values


   col segment_name format a8
   col sum(bytes) format 9999999999
   col sum(blocks) format 99999999
   col extents format 9999
   col next_extent format 99999999
   col max_extents format 9999
   col pct_increase format 999  

   select segment_name, sum(bytes), sum(blocks),

            extents, next_extent, max_extents, pct_increase 
   from     dba_segments 

   where segment_name like 'IDL_UB%'    group by segment_name,extents, initial_extent,

            next_extent, max_extents, pct_increase;    

Example Output


SEGMENT SUM(BYTES) SUM(BLOCKS) EXTENTS NEXT_EXTENT MAX_EXTENTS PCT_INCREASE ------- ---------- ----------- ------- ----------- ----------- ------------

IDL_UB1$ 105256960       51395      28     5242880         121            0 
IDL_UB2$  26808320       13090      14     5242880         121            0


Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art !

This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you.

Please see the official ORACLE-L FAQ:
Author: Jamadagni, Rajendra

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon May 14 2001 - 14:52:23 CDT

Original text of this message