| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> FW: Help - SYSTEM Tablespace Grow from 175M to 370M in a month
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
TABLE 656 SOURCE$ TABLE 154
Thanks very much.
Lucia
-----Original Message-----
From: sdjaroud_at_lth.sonatrach.dz [mailto:sdjaroud_at_lth.sonatrach.dz]
Sent: Wednesday, August 23, 2000 5:28 AM
To: ORACLE-L_at_fatcity.com
Subject: RE: Help - SYSTEM Tablespace Grow from 175M to 370M in a month
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
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$
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 ListsReceived on Wed Aug 23 2000 - 14:29:32 CDT
--------------------------------------------------------------------
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
 |
 |