Featured Post

The great debacle of healthcare.gov

This is the first time in history when the president of the United States of America, or probably for any head of state around the world,...

Thursday, November 12, 2009

ORA-01655: unable to extend ...

ORA-01655: unable to extend cluster SYS.C_FILE#_BLOCK# by 128 in tablespace

I encountered the above error while creating the partitioned tables. In my partitioned table I was creating LOCAL indexes for the partitioned tables. While creating the indexes, Oracle was throwing the above error. The script was organized to create a partitioned tables and then create associated indexes (Local or Global) and that repeats for all of the table and partition creation statements.

The error confused me initially as the tables were creating okay with GLOBAL indexes but all the LOCAL indexes were getting failed. I checked the SYSTEM tablespace and that really went out of space, it had just 1 MB free space. As there are no enough spaces it makes sense that while creating tables or indexes, Oracle was not able to get enough spaces to insert the data dictionary entries. But the question came to my mind that in that case how come my table and and GLOBAL indexes were creating fine but soon after the LOCAL indexes creation were getting failed. I checked the LOCAL indexes so far created with no issue and found that they're residing in the schema's default tablespace not in the SYSTEM tablespace. Then after some research and R&D with SYSTEM tablespace I came to the understanding that partitioned table's metadata are stored in the SYSTEM tablespace and which was not available out off 1 MB free SYSTEM tablespace. I know this is not a great discovery and expert people already know about that but as I've started to play around with partitioned table recently, for me it's a new exploration.

No comments: