Home » SQL & PL/SQL » SQL & PL/SQL » alter index problem
alter index problem [message #376163] Tue, 16 December 2008 06:16 Go to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
'ALTER INDEX ' || C_Indices.INDEX_NAME ||' REBUILD ';

'ALTER INDEX ' || C_Indices.INDEX_NAME || ' REBUILD ONLINE COMPUTE STATISTICS';

In my stored procedure , i am reconstructing or you can say re-building the tables and table name is the input parameter to the procedure.

In middle of the code , i am using execute immediate while running the second alter index (as shown above)..., Its not working and going into exception block, But when i am running the first one , code is moving smoothly.

What can be the possible reasons , Please suggest.

Re: alter index problem [message #376168 is a reply to message #376163] Tue, 16 December 2008 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The reason is in the error you got.
In addition, I bet the error depends on the version you didn't post (as for the error).

Regards
Michel
Re: alter index problem [message #376171 is a reply to message #376163] Tue, 16 December 2008 06:57 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Ohh Sorry for that , Its Oracle Release 2 (9.2.0.1.0).

Re: alter index problem [message #376174 is a reply to message #376163] Tue, 16 December 2008 07:17 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
It is not giving any error when it reaches the line

'ALTER INDEX ' || C_Indices.INDEX_NAME || ' REBUILD ONLINE COMPUTE STATISTICS';


Actually i want this to run but as it reaches the execute immediate for this query, control moves out and goes to exception block where i am just doing this

'ALTER INDEX ' || C_Indices.INDEX_NAME ||' REBUILD ';
.

What can be the reason that the first block is not running.
Re: alter index problem [message #376179 is a reply to message #376174] Tue, 16 December 2008 07:29 Go to previous messageGo to next message
atric
Messages: 3
Registered: November 2008
Junior Member
Since the control goes to the exception block, it means there must be an oracle error encountered while processing this statement

Try to retrieve the error code for the error(for example by using dbms_output.put_line)

Unless, the Oracle Error Code/Message is known, it will be difficult to resolve this
Re: alter index problem [message #376180 is a reply to message #376179] Tue, 16 December 2008 07:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In addition - why are you rebuilding everything.

If you're doing it because you believe it is neccesary to ensure good performance, then you are wrong.

There are a handful of circumstances in which it's neccessary, and those are very rarely routine occurrences.

So - why are you rebuilding everything?
Re: alter index problem [message #376185 is a reply to message #376163] Tue, 16 December 2008 07:46 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Actually JRowbottom

Its just a requirement given to me , so i was working on that. Sad
Re: alter index problem [message #376194 is a reply to message #376185] Tue, 16 December 2008 07:59 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
shaksing wrote on Tue, 16 December 2008 08:46

Its just a requirement given to me , so i was working on that.


And you don't question why you were told to do this? You're a good underling. You'll do whatever you are told without understanding why.
Re: alter index problem [message #376195 is a reply to message #376174] Tue, 16 December 2008 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove WHEN OTHERS.

Regards
Michel
Re: alter index problem [message #376198 is a reply to message #376163] Tue, 16 December 2008 08:14 Go to previous message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Thanks all , I am able to see the required output now.

Previous Topic: Oracle default directory
Next Topic: calling another procedure in a stored procedure
Goto Forum:
  


Current Time: Thu Dec 08 14:16:42 CST 2016

Total time taken to generate the page: 0.08152 seconds