Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help - SYSTEM Tablespace Grow from 175M to 370M in a month

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

From: DJAROUD, Salim <sdjaroud_at_lth.sonatrach.dz>
Date: Wed, 23 Aug 2000 11:52:19 -0000
Message-Id: <10598.115339@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
Received on Wed Aug 23 2000 - 06:52:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US