Re: Nested table TYPE, global temp table , and rollback segment

In article <ff68d4e6.0110190216.65684f56_at_posting.google.com>, jlale.cs_at_clearstream.com says...
>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:<9qmkgb0ac6_at_drn.newsguy.com>...
>> In article <ff68d4e6.0110180157.3b6bff45_at_posting.google.com>,
>> jlale.cs_at_clearstream.com says...
>> >
>> >Hi,
>> >

[snip]

>SQL> select segment_name, initial_extent,
> 2 min_extents, max_extents, status
> 3 from sys.dba_rollback_segs;
>
>SEGMENT_NAME INITIAL_EXTENT MIN_EXTENTS MAX_EXTENTS
>STATUS
>------------------------------ -------------- ----------- -----------
>----------------
>SYSTEM 57344 2 505
>ONLINE
>R01 2097152 2 121
>ONLINE
>R02 2097152 2 121
>ONLINE
>R03 2097152 2 121
>ONLINE
>R04 2097152 2 121
>ONLINE
>R05 2097152 2 121
>OFFLINE
>R06 2097152 2 121
>OFFLINE
>
>7 rows selected.
>
>I also have this output from running a script you posted some time
>back (free.sql). Output:
>
>Tablespace Nam KBytes Used Free %Used Largest
>-------------- ----------- ----------- ----------- ------ -----------
>AUD_DAT_DCRM 1,048,576 307,208 741,368 29.3 741,368
>AUD_IDX_DCRM 262,144 56,328 205,816 21.5 205,816
>DRSYS 262,144 8,480 253,664 3.2 253,664
>DYN_DAT_DCRM 2,072,576 542,736 1,529,840 26.2 1,013,752
>DYN_IDX_DCRM 524,288 262,088 262,200 50.0 262,200
>LOB_DAT_DCRM 4,106,240 102,408 4,003,832 2.5 4,003,832
>RBS 512,000 29,128 482,872 5.7 295,672
>REF_DAT_DCRM 65,536 10,928 54,608 16.7 54,608
>REF_IDX_DCRM 65,536 10,928 54,608 16.7 54,608
>SYSTEM 1,048,576 169,032 879,544 16.1 879,488
>TEMP 262,144 93,608 168,536 35.7 2,080
>TOOLS 2,097,152 288 2,096,864 .0 2,096,864
>USERS 2,097,152 20,728 2,076,424 1.0 2,076,360
> ----------- ----------- -----------
>sum 14,424,064 1,613,888 12,810,176
>

Ok, assuming all 6 RBS's are in the RBS tablespace - and they have 2meg extents, it looks like each has about 2 extents (2*6*2 = 24, you have about 29m allocated)

The dba gave them the ABILITY to grow larger, he did not make them larger.

Tell him to allocate 12 to 20 extents to each rbs -- you've set aside 512 meg, you are using 30 of it. If you allocate 20 extents to each rbs (40m * 6 = 240), you'll still have tons of free space in that tablespace.

The 1555 comes about because your transactions are smallish, your queries are long. the rollback wraps and goes away. PRE-ALLOCATE more and it won't (go away as fast)

>Apparently, when the DBA said 'there's not alot of activity in the
>RBS', he meant there wasn't anything in the log files to suggest there
>was!
>

I think he meant "hey, they did not grow, must not be busy in there" -- if so, he has a misconception. Tell him to use a tool like statspack to monitor such things.

>You asked if we were fetching across commits. In fact, there aren't
>*any* commits in the procedures, primarily as they are inserting data
>into the temporary table, and a commit would delete the rows already
>inserted due to the "on commit delete rows".
>
>I've run a little test this morning, which by-passes my new
>procedures. I ran the original report procs, which insert data into
>temp table, and then did an 'insert into my_copyoftemptable (select *
>from temptable)'. Of course, I got the rollback 1555.
>

did you get the 1555 on the select from the TEMPTABLE??

>With that in mind, it's confusing that the "execute immediate 'select
>cast(multiset('select on temp table') as myTYPE) from dual' into
>myTYPE_1", in the new procedures seems to be OK, and that the 1555
>error message doesn't come until I'm halfway through reading *from* my
>variable myTYPE_1. I know this, because after populating myTYPE_1, I

are you saying that a collection access is raising this? Not a query ?? I'm confused, can you show us the line of code that gets the 1555.

>loop through values in another array (v2) and pass this v2 value,
>along with the myTYPE_1, into a function which returns me the
>appropriate value from myTYPE_1. It's this function which catches the
>rollback error.
>
>Any thoughts on this?
>
>Just to give you some kind of indication of volumes, the procedures
>are putting 2500 records into the temporary table (for my test
>report), and are taking nearly 3 minutes to do so, on account of alot
>of security checking.
>
>Thanks again for your help
>James

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp