Temp Tablespace Group, Adding Tempfile and Resumable Transaction Issue?
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: oracle Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 6 Jan 2010 08:59:57 -0800 (PST)
Hello Folks,
I had a large adhoc parallel process running in one of my older marts. The
database is set up with resumable transactions and this process' slaves went
into timeout when it ran out of temp tablespace striped in my temp tablespace
group.
I added datafiles first to the temp_sample1, which it was complaining about,
but knew that it was striped across and would need space in the others or I
would have more parallel slaves going into timeout. On the third tablespace of
the group, I added the file bolded below and then one of the processes aborted,
sending the user this error:
ERROR at line 1:
ORA-12801: error signaled in parallel query server P024
ORA-01157: cannot identify/lock data file 1042 - see DBWR trace file
ORA-01110: data file 1042: '/r07/oradata/martb/temp_sample3_06.dbf'
Anyone seen this before? Is there a known issue with
parallel processes/resumable transactions and temp tablespace groups, (all, or
some of these combined) that I'm unaware of and can't fine out there?
The output from the alert log, the dbwr trace file seems nowhere to be found!:
Wed Jan 6 06:54:16 2010
alter tablespace temp_sample1 add tempfile
'/r07/oradata/martb/temp_sample1_06.dbf' size 10000M autoextend on next 5M
maxsize 51200M
Wed Jan 6 06:54:17 2010
Completed: alter tablespace temp_sample1 add tempfile
'/r07/oradata/martb/temp_sample1_06.dbf' size 10000M autoextend on next 5M
maxsize 51200M
Wed Jan 6 06:54:18 2010
statement in resumable session 'User DM_OWNER(194), Session 693, Instance 1'
was resumed
Wed Jan 6 06:54:18 2010
statement in resumable session 'User DM_OWNER(194), Session 572, Instance 1'
was resumed
Wed Jan 6 06:54:18 2010
statement in resumable session 'User DM_OWNER(194), Session 704, Instance 1'
was resumed
Wed Jan 6 06:54:18 2010
statement in resumable session 'User DM_OWNER(194), Session 726, Instance 1'
was resumed
Wed Jan 6 06:55:15 2010
alter tablespace temp_sample2 add tempfile
'r07/oradata/martb/temp_sample2_06.dbf' size 10000M autoextend on next 5M
maxsize 51200M
Wed Jan 6 06:55:15 2010
alter tablespace temp_sample2 add tempfile
'/r07/oradata/martb/temp_sample2_06.dbf' size 10000M autoextend on next 5M
maxsize 51200M
Wed Jan 6 06:55:43 2010
Completed: alter tablespace temp_sample2 add tempfile
'/r07/oradata/martb/temp_sample2_06.dbf' size 10000M autoextend on next 5M
maxsize 51200M
Wed Jan 6 06:56:21 2010
alter tablespace temp_sample3 add tempfile
'/r07/oradata/martb/temp_sample3_06.dbf' size 10000M autoextend on next 5M
maxsize 51200M
Wed Jan 6 06:56:21 2010
Completed: alter tablespace temp_sample3 add tempfile
'/r07/oradata/martb/temp_sample3_06.dbf' size 10000M autoextend on next 5M
maxsize 51200M
Wed Jan 6 06:56:21 2010
statement in resumable session 'User DM_OWNER(194), Session 771, Instance 1'
was aborted
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
"Go away before I replace you with a very small and efficient shell script..."
Other related posts: