No question at this time
DBA Top 10
1 M. Cadot 15900
2 B. Vroman 15500
3 A. Kavsek 10000
4 T. Boles 6800
5 J. Schnackenberg 5800
6 M. Hidayathullah ... 3900
7 P. Wisse 1800
8 T. P 700
9 B. Derous 500
9 R. Wauben 500
9 H. Steijntjes 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48208
Total active users1645
Act. users last 24h7
Act. users last hour0
Registered user hits last week163
Registered user hits last month788
Go up

SLLoader direct path and unusable indices
Next thread: invalid month
Prev thread: Impact of parallelism on session_per_user limit

Message Score Author Date
Hi all. We currently have the following scenari...... Gerard Vaz Feb 28, 2018, 21:23
Ask your DBA to set an alarm on resumable errors...... Michel Cadot Feb 28, 2018, 21:55
Thanks Michel. Will try that out.... Gerard Vaz Mar 01, 2018, 14:48
Parameter has current setting of 60min. Sqlloader ...... Gerard Vaz Mar 05, 2018, 23:20
The same one. What is the purpose to set a para...... Michel Cadot Mar 06, 2018, 06:08
Hello Gérard, the scenario is OK, but for some ...... Bruno Vroman Mar 06, 2018, 14:58
Thank you Bruno for your feedback - appreciate it.... Gerard Vaz Mar 07, 2018, 02:02

Follow up by mail Click here


Subject: SLLoader direct path and unusable indices
Author: Gerard Vaz, Canada
Date: Feb 28, 2018, 21:23, 112 days ago
Os info: Linux
Oracle info: Oracle12c
Error info: ORA-30032: the suspended (resumable) statement has timed out
ORA-01658 unable to create INITIAL extent for segment in tablespace <tablespace>
Message: Hi all.

We currently have the following scenario

a. Table with current record count of 6million
b. New records/data being loaded via SAS macro using SQLLoader direct path
c. Some of the indices on the table being loaded are made unusable due to:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01658: unable to create INITIAL extent for segment in tablespace <tablespace>

What is(are) the best approach(es) to rectify this? Apart from increasing/adding tablespace.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SLLoader direct path and unusable indices
Author: Michel Cadot, France
Date: Feb 28, 2018, 21:55, 112 days ago
Message:
Ask your DBA to set an alarm on resumable errors so he handles them before they time out.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SLLoader direct path and unusable indices
Author: Gerard Vaz, Canada
Date: Mar 01, 2018, 14:48, 111 days ago
Message: Thanks Michel. Will try that out.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SLLoader direct path and unusable indices
Author: Gerard Vaz, Canada
Date: Mar 05, 2018, 23:20, 106 days ago
Message: Parameter has current setting of 60min. Sqlloader is being invoked thru a sas routine whereby it disables the indices before the load and then attempts a rebuild. Any other suggestions?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SLLoader direct path and unusable indices
Author: Michel Cadot, France
Date: Mar 06, 2018, 06:08, 106 days ago
Message:
The same one.
What is the purpose to set a parameter if you don't take it into account? (this is a question for your DBA).


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SLLoader direct path and unusable indices
Author: Bruno Vroman, Belgium
Date: Mar 06, 2018, 14:58, 106 days ago
Message: Hello Gérard,

the scenario is OK, but for some reason an error happens when trying to rebuild an index (ORA-01658 unable to create INITIAL extent for segment in tablespace <tablespace>). At this point the statement is "resumable": if space is made available soon enough, the statement will be able to continue its work.

The first keypoint is the "soon enough"... resumable_timeout is expressed in seconds; it can be modified at session level.

The second keypoint is that you have to be aware that there is a statement waiting for your action, so that you can act before it times out.
So as written by Michel: you must liaise with your DBA so that it can notify you quickly if a "resumable error" message appears in the alert logfile.

When a resumable statement is blocked, there is a message in the alert logfile, like:
Tue Mar 06 14:46:43 2018

statement in resumable session 'User SCOTT, Session 1615, Instance 1' was suspended due to
ORA-01652: unable to extend temp segment by 128 in tablespace MYTABLESPACE
Then if you don't react quickly enough, you end up in a timeout, like:
Tue Mar 06 14:46:55 2018

statement in resumable session 'User SCOTT, Session 1615, Instance 1' was timed out
But if you react promptly enough (in this case by allowing the tablespace to extend), you will see things like
Tue Mar 06 14:48:18 2018

statement in resumable session 'User SCOTT, Session 1615, Instance 1' was resumed
and in the session the message in the first case (timeout) is
ERROR at line 1:

ORA-30032: the suspended (resumable) statement has timed out
ORA-01652: unable to extend temp segment by 128 in tablespace MYTABLESPACE
(and in the second case there is no message, the statement has simply "taken more time than expected").

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SLLoader direct path and unusable indices
Author: Gerard Vaz, Canada
Date: Mar 07, 2018, 02:02, 105 days ago
Message: Thank you Bruno for your feedback - appreciate it.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here