Home » SQL & PL/SQL » SQL & PL/SQL » Using online option during index rebuild.
Using online option during index rebuild. [message #420084] Thu, 27 August 2009 00:09 Go to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Hi Experts,

While rebuilding the indexes , I am getting the following oracle error:

Error starting at line 1 in command:
alter table BL_DOWNLOAD drop subpartition DWN_LASTMOD2_FULFLD_PART1 UPDATE GLOBAL INDEXES online
Error report:
SQL Error: ORA-14126: only a <parallel clause> may follow description(s) of resulting partitions
14126. 00000 -  "only a <parallel clause> may follow description(s) of resulting partitions"



My query for rebuild is:
alter table BL_DOWNLOAD drop subpartition DWN_LASTMOD2_FULFLD_PART1 UPDATE GLOBAL INDEXES online;



Please help me to resolve the issue.

Thanks and Regards,
Sunil
Re: Using online option during index rebuild. [message #420085 is a reply to message #420084] Thu, 27 August 2009 00:15 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>While rebuilding the indexes
WHY?
An exercise in futility.

Post Operating System (OS) name & version for DB server system.
Post results of SELECT * from v$version.
Re: Using online option during index rebuild. [message #420086 is a reply to message #420085] Thu, 27 August 2009 00:22 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
I want to rebuild the indexes for partition management. By using ONLINE option, I am trying that DML operation and index rebuild will happen concurrently.

SQL> SELECT * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production



Thanks,
Sunil
Re: Using online option during index rebuild. [message #420089 is a reply to message #420084] Thu, 27 August 2009 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove ONLINE this is not allowed in your statement.

Regards
Michel
Re: Using online option during index rebuild. [message #420091 is a reply to message #420089] Thu, 27 August 2009 00:37 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Hi Michel,

Hope you have understand my requirement by the last mail.
I will be very grateful , if you tell me alternate for the requirement.

Thanks and regards,
Sunil
Re: Using online option during index rebuild. [message #420094 is a reply to message #420091] Thu, 27 August 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No alternate, you just can't put online in this case, so remove it.

Regards
Michel
Re: Using online option during index rebuild. [message #420096 is a reply to message #420094] Thu, 27 August 2009 00:46 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Thanks for the quick response. I want to add some more in my requirement.

treat BL_DOWNLOAD as live partitioned table , frequent DML operation are happening to it.
In the mean time if I will drop some old subpartition, it will give error:
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


I want to avoid this deadlock situation . Please help me how to do it without using ONLINE.

Thanks and Regards,
Sunil
Re: Using online option during index rebuild. [message #420097 is a reply to message #420096] Thu, 27 August 2009 00:51 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
Maintenance operation on objects MUST NOT be done at the same time than DML when you have global indexes.
You have to plan a maintenance window.

Regards
Michel
Previous Topic: Fixing Jobs with Time
Next Topic: delete conditional rows
Goto Forum:
  


Current Time: Tue Dec 06 12:30:26 CST 2016

Total time taken to generate the page: 0.08394 seconds