| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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
Received on Tue Aug 22 2000 - 14:21:20 CDT
 |
 |