| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Help - SYSTEM Tablespace Grow from 175M to 370M in a month
Maybe you should look if auditing is on. The audit-table is default in the
system-tablespace.
Or try this statement;
Select segment_name,segment_type,count(*)
from dba_extents
where tablespace_name = 'SYSTEM'
having count(*) > 100
group by segment_name,segment_type
/
At least you should be able to see if there is a table going wild....
Eric Lansu
> 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
> ----------- ----------------- ------- ------- ------- ------ ------- -----
--Received on Wed Aug 23 2000 - 08:02:19 CDT
> 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
 |
 |