Home » RDBMS Server » Performance Tuning » Doubt regarding ORA-30036: unable to extend segment by 8 in undo tablespace
Doubt regarding ORA-30036: unable to extend segment by 8 in undo tablespace [message #306730] Sun, 16 March 2008 06:11
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I am using 11g Release 1 Database .

I have to analyze the performance of two tables of different designs which serve the same purpose and come up with the design which is efficient .

SQL> desc staging_dict
 Name                                      Null?    Type
 ----------------------------------------- -------- 
 SNO                                                NUMBER
 CODE_FRAGMENTS                                     CLOB
 CODE_FRAGMENTS_U                                   CLOB
 CODE_FRAGMENTS_D                                   CLOB
 CODE_FRAGMENTS_DO                                  CLOB

SQL> desc staging_dict1
 Name                                      Null?    Type
 ----------------------------------------- -------- 
 SNO                                                NUMBER
 CODE_FRAGMENTS                                     CLOB
 CODE_FRAGMENTS_UD                                  CLOB
 CODE_TYPE                                          VARCHAR2(5 CHAR)



Initially I tried inserting a few thousand records into both the tables . Then I did some conversion on one column and I populate the result on other column of the same table . So I update the table in bulk mode and I commit for every thousand records .

I have a undo tablespace of 2G with undo_retention=900 , retention guratantee is not set for the undo tablespace .

When I tried the conversion and update on the first table (STAGING_DICT) it took more time for around 2500 records compared to other table and when I increased the number of records to 10000 it threw an error

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

But I didn't come across this error when I tried the conversion and update on the table for the same 2500 records (STAGING_DICT1) and it was also 10 times faster .

My doubt is does the error ORA-30036 occur because it is saving the undo image of all the four clob columns though I am doing conversion on one column and updating the other column (using only two columns in the update and only one column is affected by update command) ?

Also how is that having less CLOB rows prove more effective by adding one more VARCHAR column which differentiates the code_frament type in the STAGING_DICT1 table than having it as more CLOB columns as in STAGING_DICT table ?
Previous Topic: Help - Stored Outline
Next Topic: sqlplus: query takes too much time
Goto Forum:
  


Current Time: Mon Dec 05 04:50:49 CST 2016

Total time taken to generate the page: 0.10257 seconds