[Building Sakai] Regarding SAK-16463 - 2.6.x oracle database conversion needed

[Kevin Chan]

Thanks Drew! Our DBA also suggested a rebuild of the indexes and that 
took care of the problem.

This is a lesson for us non-Oracle DBAs to leave "alter table" 
statements to the experts...

It would be a good idea to add a note to the 2.5->2.6 SQL reference file 
that this task will be needed as part of the alter table statement.

-- 
  Kevin Chan

  Operations Team
  Educational Technology Services
  University of California, Berkeley



Drew Zhu wrote:
> Kevin Chan wrote:
>> Thanks for looking into this.
>>
>> I just ran the "alter table" statement against our QA Oracle DB (post 
>> 2.6 upgrade and mailarchive binary conversion).
>>
>> Unfortunately, it looks like we are now getting an error with an 
>> index when we try to send an email to mailarchive:
>>
>> java.sql.SQLException: ORA-01502: index 'SAKAI_QA.SYS_C00122053' or 
>> partition of such index is in unusable state
>>
>>         at 
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) 
>>
>>         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
>>         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
>>         at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
>>
>>
>> Did you see this problem after you converted? I will check in with 
>> our Oracle DBAs and see if they have any insight.
>>   
>
>
> select 'alter index '||index_name||' rebuild online;' from 
> user_indexes where table_name='/your_table_name/';
>
> Run all the rebuilding index commands generated from the above query 
> in sqlplus.  If your indexes are in a separate tablespace, you need to 
> add "tablespace /your_index_tablespace_name/" after the "online 
> followed by a space" and before the ";"
>
> Then collect stats using "analyze table /your_table_name/ compute 
> statistics;' or DBMS_STATS.gather_tables_stats();
>
> That's what we have done during the LONG - CLOB conversion.
>
> Hope that helps.
>
> Regards,
> Drew Zhu
>
>
>> Thanks,
>>
>>   
>