SYSTEM Tablespace hitting same error
Shailesh Saraff, September 07, 2004 - 5:13 am UTC
Hi Tom,
Our customer is on Oracle 8.1.7.4 on HP/UX. Recently we came across following error...
ORA-00604: error occurred at recursive SQL level 1
ORA-01654: unable to extend index SYS.I_COL1 by 13 in tablespace SYSTEM
When we analyzed this, found that SYSTEM tablespace size = maxsize. Then try to check why all of sudden SYSTEM tablespace is full and found that with some bug in an application created 10000 views causing this problem. Application creates view on log on and drops view on exit but some bug prevented it. We droped around 9000 views and then problem was solved.
But when queried DBA_DATA_FILES Bytes, MaxBytes it was same. We queried DBA_Free_Space there was no record for SYSTEM tablespace. To be on safer side added one extra data file for <SYSTEM> tablespace.
For reference below giving you command of SYSTEM tablespace and create view statement.
ALTER DATABASE DATAFILE '%ORACLE_HOME%\oradata\«DB»\System\system01.dat' AUTOEXTEND ON NEXT 10M MAXSIZE 300M;
Application creates following view on logon and drop on exit.
CREATE OR REPLACE VIEW Viw'||SomePassedValue||' AS SELECT F.* FROM TblCase F
WHERE F.CaseId IN ( SELECT F3.CaseId
FROM TBlCase F3 WHERE Nvl(F3.IsLockedFromOE, 'N') = 'N' AND F3.CaseId = F.CaseId
UNION ALL SELECT F1.CaseId
FROM TblCase F1, MainTable1 Q
WHERE F.CaseId = Q.CaseId AND F1.CaseId = F.CaseId
AND F1.IsLockedFromOE = 'J' AND Q.OEContacted In ( Values...) --- Around 400 VALUES
UNION ALL Select Q2.ToCaseId
FROM TblCase F2, MainTable1 Q1, MainTable2 Q2
WHERE F2.CaseId = Q2.ToCaseId AND Q1.CaseId = Q2.FromCaseId AND F2.CaseId = F.CaseId )';
Inour case 10000 views occupy around 200 MB, So we may have redefine MAXSIZE.
What else can be done in such situation?
Why even after dropping views we don't see free space for SYSTEM tablespace?
Please guide us.
Thanks & Regards,
Shailesh
September 07, 2004 - 9:15 am UTC
you filled up your system tablespace. I_COL1 is an index on COL$
sys@ORA9IR2> select table_name from user_indexes where index_name = 'I_COL1';
TABLE_NAME
------------------------------
COL$
In fact it is:
I_COL1 Yes OBJ#, NAME
on that table. So, for every view you had (some 9,000 of them) you had multiple entries to make in that index (say each view has 5 columns, say the average length of the name in the index is 10 characters -- that would be about 1/2meg just in NAMES alone -- and that is on the very very very low side)
So, you came in and dropped these views. Space -- once given -- is not "taken away", the index -- I_COL1 cannot "give back space", so dba_free_space would never "grow" because of a delete (it takes a REORG, REBUILD, or in 10g an online segment shrink to give space "back"). However, the index I_COL1 now has lots and lots of free space in it that can be used, albeit only by that index (but that was the index that was having an "issue" so...)
The space is there, it is allocated to the index, it is usable only by the index, it'll never appear in dba_free_space again in this case.
I would ask the developers of this application "what the HECK were you thinking! stop doing this really bad thing!"
but as long as they clean up their garbage, SYSTEM will hit a steady state size. SYSTEM grows as needed, and if you mistakenly leave 10's of thousands of views in there -- well, caveat emptor. We store what we are asked to store. if you drop the views, the space is there, it is available -- it is part of the segment that originally allocated it all.
This is possible for fresh inserts but we experience same in DELETE
Umesh, May 12, 2006 - 9:26 am UTC
Hi Tom,
I am facing a strange problem.
I have a tablespace of size is 100 MB
There is one table in it with a CLOB column in it. The table has occupied size 72 MB for 11000 records.
There is no record of this tablespace in DBA_FREE_SPACE view. Means no free space in this tablspace.
When I try to delete 10000 records from this table ,
operation throws following error :
ORA-01654: unable to extend index BBLLREL3.SYS_IL0000027644C00002$$ by 8 in
tablespace LLU_XML_DATA
Can you please suggest what is problem in this?
Why it is trying to extend the index when I am trying to delete the records?
Thanks in advance!
May 12, 2006 - 4:13 pm UTC
no one ever said indexes "shrink" during a delete - in fact, it is not unusual for them to grow a bit.
This index must be right on the edge of needing to "grow" and the index maintanence taking place is causing it to do so.
oh! I never thought this.
Umesh, May 15, 2006 - 4:04 am UTC
Tom,
Many Thanks!!
I never thought that index maintenance would require some space with is not available in this case.
Regards,
Umesh
unable to extend index sys.i_col1 by 13 in tablespace SYSTEM
abu, January 25, 2012 - 9:26 pm UTC
AutoPatch error:
The following ORACLE error:
ORA-00604: error occurred at recursive SQL level 1
ORA-01654: unable to extend index SYS.I_COL1 by 13 in tablespace SYSTEM
occurred while executing the SQL statement:
CREATE TABLE ad_util_params( worker_id number not null,
utility_name varchar2(100) not null, task
varchar2(30) not null, status varchar2(100) not null,
arguments varchar2(2000) not null, rowid1
varchar2(2000), utility_status number not null, symbolic_arguments
varchar2(2560) not null)
AutoPatch error:
Error running SQL and EXEC commands in parallel
Freeing includes hash table
Freeing fixes hash table
Freeing basedons hash table
Freeing entities hash table
You should check the file
/d01/prod/apps/apps_st/appl/admin/PROD/log/adpatch.log
for errors.
January 26, 2012 - 10:31 am UTC
and? you have a question?
if the question is "what do I do about this", the answer is "add space to system"
this is excellent
abu, January 26, 2012 - 9:44 pm UTC
hi ,
i got autopatch error ....
system tablespace is already defined tablespace.and i got error message is index in system tablespace cann't inserting because of lowsize?
Serhat CELIK, November 09, 2022 - 9:59 am UTC
November 09, 2022 - 2:40 pm UTC
Thanks for putting this together