Re: FW: Help - SYSTEM Tablespace Grow from 175M to 370M in a month

So it's looking like you have added a great deal of stored procedures to the DB in the interim, and these have taken up the room in the data dictionary and thus in the SYSTEM space source$ and idl_ub1$ are both code storage tables within the dictionary. For a description of idl_ub1$ see:

  http://www.ixora.com.au/q+a/dd.htm

On 23 Aug 2000, at 12:36, Lucia DeMeester wrote:

> I don't remember what I did. It seems that I did via alter database
> datafile xxx autoextend on maxisize 400M; I thought this will autoextend to
> max. 400M since I specifiy the datafile is system01.dbf.
>
> Now, please explain if I need autoextend the system tablespace which
> originally was allocated to 200M on system01.dbf, do I have to add another
> 200M of datafile system02.dbf to achieve the autoextend? I thought I just
> turn on the autoextend and set up the maxsize to acomplish this.
>
> By the way, the audit is not on. I ran
> Select segment_name,segment_type,count(*)
> from dba_extents
> where tablespace_name = 'SYSTEM'
> having count(*) > 100
> group by segment_name,segment_type
> /
>
> This is the result:
> SEGMENT_NAME
> ----------------------------------------------------------------------------
> ----
> SEGMENT_TYPE COUNT(*)
> ------------------ ----------
> IDL_UB1$
> TABLE 656
>
> SOURCE$
> TABLE 154
>
>
> Thanks very much.
>
> Lucia
>
> -----Original Message-----
> Sent: Wednesday, August 23, 2000 5:28 AM
> To: ORACLE-L_at_fatcity.com
>
>
> Sorry, can you explain me one think:
> when you say "I immediately added another 200M with autoexend on and
> max autoextend to 400M" does it mean:
> ALTER DATABASE DATAFILE xxx AUTOEXTEND ON NEXT 200M MAXSIZE 400M; .
> in this case I think the actual size of your system tablespace is
> always 200M.
> hope this help.
>
>
> ----------
> De : Lucia DeMeester[SMTP:ldemeester_at_nm2.com]
> Repondre a : ORACLE-L_at_fatcity.com
> Date : mardi 22 aout 2000 21:12
> A : Multiple recipients of list ORACLE-L
> Objet : Help - SYSTEM Tablespace Grow from 175M to 370M in a month
>
> List,
>
> I just took over the support of a new web site. It uses ATG Dymos
> application. Before we launch the site, we were out of the system
> tablespace. I checked the size of the system tablespace, it was allocated
> 200M and 85% full. I immediately added another 200M with autoexend on and
> max autoextend to 400M.
>
> Thanks to Kip Bryant's script, I just found out that it is 30M left from
> the
> 200M that I just added a month ago.
>
> This is my show_space script which does not list the usage from autoextend.
> bash-2.03$ cat show_space.sql
> set pagesize 9999
> col tablespace_name format a20
> select TABLESPACE_NAME,
> to_char(sum(USED_KB),'99,999,990') "USED-Kb",
> to_char(sum(ALLOC_KB),'99,999,990') "ALLOC-Kb",
> to_char(((sum(USED_KB) / sum(ALLOC_KB)) * 100),'999.9') "USED%",
> to_char(max(SEGCOUNT),'9990') "SEGS",
> to_char(max(MAXEXT),'9990') ">EXT",
> to_char(max(MAXNEXT),'999,990') ">NEXTEXT"
> from (select TABLESPACE_NAME, sum(BYTES)/1024 USED_KB, 0 ALLOC_KB,
> count(*) SEGCOUNT, max(extents) MAXEXT, max(NEXT_EXTENT/1024)
> MAXNEXT
> from SYS.DBA_SEGMENTS group by TABLESPACE_NAME
> union all
> select TABLESPACE_NAME, 0 USED_KB, sum(BYTES)/1024 ALLOC_KB,
> 0 SEGCOUNT, 0 MAXEXT, 0 MAXNEXT
> from SYS.DBA_DATA_FILES group by TABLESPACE_NAME)
> group by TABLESPACE_NAME;
>
> TABLESPACE_NAME USED-Kb ALLOC-Kb USED% SEGS >EXT >NEXTEXT
> -------------------- ----------- ----------- ------ ----- ----- --------
> ATG_DATA 14,840 153,600 9.7 41 2 1,120
> ATG_INDX 8,320 102,400 8.1 52 1 16
> BOOK_SMART_DATA 5,344 512,000 1.0 57 1 40
> BOOK_SMART_INDX 128 307,200 .0 8 1 40
> DPS_PROFILE_DATA 6,680 307,200 2.2 41 1 128
> DPS_PROFILE_INDX 7,840 153,600 5.1 49 1 128
> DRSYS 0 20,480 .0 0 0 0
> RBS 135,360 512,000 26.4 12 2 1,024
> SYSTEM 174,384 204,800 85.1 361 656 744
> TEMP 0 409,600 .0 0 0 0
> TOOLS 5,680 15,360 37.0 142 1 40
> USERS 0 15,360 .0 0 0 0
>
>
> The following is the output from Kip Bryant's script and it indicates that
> only 30M of free system tablespace left.
>
> Data Max Auto No Of High Tot
> File Auto Extend Free Free Free
> TABLESPACE FILENAME Size Extend Increm Extnts Extent Space
> ----------- ----------------- ------- ------- ------- ------ -------
> -------
> SYSTEM 2/system01.dbf 200M 400M 1M 2 30M
> 30M
>
> I have no idea why the system tablespace grows so fast. Only sys, system
> and outln's default tablespace is system.
> The following is the result from running the high water mark script.
>
> Question:
> 1) How can I find out what causes the system tablespace grow so rapidly.
> 2) What are those tables that has more than 10 blocks used for? (example:
> IDL_UB1$, OJB$, SOURCE$ tables) All these tables are owned by SYS.
>
> SQL> @ck_system_hwmark.sql
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> ACCESS$
> 242
> AQ$_MESSAGE_TYPES
> 2
> AQ$_PENDING_MESSAGES
> 2
> AQ$_PROPAGATION_STATUS
> 2
> AQ$_QUEUE_STATISTICS
> 2
> AQ$_QUEUE_TABLE_AFFINITIES
> 2
> AQ$_SCHEDULES
> 2
> AQ$_SCHEDULES
> 2
> ARGUMENT$
> 167
> ASSOCIATION$
> 2
> AUD$
> 2
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> AUDIT$
> 2
> AUDIT_ACTIONS
> 2
> AURORA$IIOP$SYSTEM$PROPERTIES
> 2
> AURORA$SHUTDOWN$CLASSES$
> 2
> AURORA$SNS$ATTRIBUTES$
> 2
> AURORA$SNS$BINDINGS$
> 2
> AURORA$SNS$INODE$
> 2
> AURORA$SNS$PERMISSIONS$
> 2
> AURORA$SNS$REFADDR$
> 2
> AURORA$STARTUP$CLASSES$
> 2
> BOOTSTRAP$
> 10
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> COM$
> 47
> CON$
> 7
> CONTEXT$
> 2
> DBMS_ALERT_INFO
> 2
> DBMS_LOCK_ALLOCATED
> 6
> DEFROLE$
> 2
> DEPENDENCY$
> 512
> DIM$
> 2
> DIMATTR$
> 2
> DIMJOINKEY$
> 2
> DIMLEVEL$
> 2
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> DIMLEVELKEY$
> 2
> DIR$
> 2
> DUAL
> 2
> DUC$
> 2
> ERROR$
> 2
> EXPACT$
> 2
> EXPDEPACT$
> 2
> EXPDEPOBJ$
> 2
> EXPPKGACT$
> 2
> EXPPKGOBJ$
> 2
> FILE$
> 2
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> HIER$
> 2
> HIERLEVEL$
> 2
> HIST_HEAD$
> 12
> IDL_CHAR$
> 257
> IDL_SB4$
> 302
> IDL_UB1$
> 9827
> IDL_UB2$
> 482
> ID_GENS$
> 2
> INCEXP
> 2
> INCFIL
> 2
> INCVID
> 2
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> INDCOMPART$
> 2
> INDOP$
> 2
> INDPART$
> 2
> INDSUBPART$
> 2
> INDTYPES$
> 2
> JAVA$POLICY$
> 2
> JAVA$RMJVM$AUX
> 2
> JAVA$RMJVM$AUX2
> 2
> JAVASNM$
> 132
> JOB$
> 2
> KOPM$
> 2
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> LINK$
> 2
> LOBCOMPPART$
> 2
> LOBFRAG$
> 2
> LOC$
> 2
> MIGRATE$
> 2
> MLOG_REFCOL$
> 2
> MON_MODS$
> 2
> NOEXP$
> 2
> OBJ$
> 242
> OBJAUTH$
> 37
> OBJPRIV$
> 2
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> OID$
> 2
> OL$
> 2
> OL$HINTS
> 2
> OPANCILLARY$
> 2
> OPARG$
> 2
> OPBINDING$
> 2
> OPERATOR$
> 2
> PARTCOL$
> 2
> PARTLOB$
> 2
> PARTOBJ$
> 2
> PENDING_SESSIONS$
> 2
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> PENDING_SUB_SESSIONS$
> 2
> PENDING_TRANS$
> 2
> PROCEDURE$
> 4
>
> PROFILE$
> 2
> PROFNAME$
> 2
> PROPS$
> 2
> PROXY$
> 2
> PSTUBTBL
> 2
> REG$
> 2
> REG_SNAP$
> 2
> RESOURCE_CONSUMER_GROUP$
> 2
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> RESOURCE_COST$
> 2
> RESOURCE_MAP
> 2
> RESOURCE_PLAN$
> 2
> RESOURCE_PLAN_DIRECTIVE$
> 2
> RLS$
> 2
> RULESET$
> 2
> SEQ$
> 4
> SNAP$
> 2
> SNAP_COLMAP$
> 2
> SNAP_LOADERTIME$
> 2
> SNAP_LOGDEP$
> 2
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> SNAP_REFOP$
> 2
> SNAP_REFTIME$
> 2
> SNAP_SITE$
> 2
> SOURCE$
> 3827
> SQL_VERSION$
> 2
> STMT_AUDIT_OPTION_MAP
> 2
> SUBPARTCOL$
> 2
> SUM$
> 2
> SUMAGG$
> 2
> SUMDELTA$
> 2
> SUMDEP$
> 2
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> SUMDETAIL$
> 2
> SUMJOIN$
> 2
> SUMKEY$
> 2
> SYN$
> 57
> SYSAUTH$
> 2
> SYSTEM_PRIVILEGE_MAP
> 2
> TABCOMPART$
> 2
> TABLE_PRIVILEGE_MAP
> 2
> TABPART$
> 2
> TABSUBPART$
> 2
> TRIGGER$
> 7
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> TRIGGERCOL$
> 2
> TRIGGERJAVAC$
> 2
> TRIGGERJAVAF$
> 2
> TRIGGERJAVAM$
> 2
> TRIGGERJAVAS$
> 2
> TRUSTED_LIST$
> 2
> TYPED_VIEW$
> 2
> UGROUP$
> 2
> UNDO$
> 2
> USER_ASTATUS_MAP
> 2
> USER_HISTORY$
> 2
>
> HWM EMPTY_BLOCKS BLOCKS TABLE_NAME
> BLOCKS
> ---------- ------------ ---------- ------------------------------
> ----------
> USTATS$
> 2
> VIEW$
> 137
> _default_auditing_options_
> 2
>
> 146 rows selected.
>
> Thanks very much in advance.
>
> Regards,
> Lucia
>
>
>
> --
> Author: Lucia DeMeester
> INET: ldemeester_at_nm2.com
>
> 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: ListGuru_at_fatcity.com (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).
>
>
> --
> Author: DJAROUD, Salim
> INET: sdjaroud_at_lth.sonatrach.dz
>
> 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: ListGuru_at_fatcity.com (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).
>
>
> --- Internet Message Header Follows ---
> Received: from newsfeed.cts.com (209.68.192.199)
> by prophet.com (FirstClass Mail Server v5.11)
> transient id 123; 5:29:31 AM -0800
> Received: from fatcity.UUCP (uucp_at_localhost)
> by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id FAA37403;
> Wed, 23 Aug 2000 05:19:52 -0700 (PDT)
> Received: by fatcity.com (04-May-2000/v1.0f-b69/bab) via UUCP id 001AFA8E;
> Wed, 23 Aug 2000 04:27:59 -0800
> Message-ID: <F001.001AFA8E.20000823042759_at_fatcity.com>
> Date: Wed, 23 Aug 2000 04:27:59 -0800
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> X-Comment: Oracle RDBMS Community Forum
> X-Sender: "DJAROUD, Salim" <sdjaroud_at_lth.sonatrach.dz>
> Sender: root_at_fatcity.com
> Reply-To: ORACLE-L_at_fatcity.com
> Errors-To: ML-ERRORS_at_fatcity.com
> Organization: Fat City Network Services, San Diego, California
> X-ListServer: v1.0f, build 69; ListGuru (c) 1996-2000 Bruce A. Bergman
> Precedence: bulk
> Mime-Version: 1.0
> Content-Type: text/plain; charset="us-ascii"
> Content-Transfer-Encoding: 7bit
>
> --
> Author: Lucia DeMeester
> INET: ldemeester_at_nm2.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
Received on Wed Aug 23 2000 - 19:53:21 CDT