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

[Matthew Jones]

Thanks for the info Drew & Keven. I'm working on cleaning up the
update script so there's less variables to define and it works a
little better, as I was having problems getting it going a few times.

I'll include something that will either advise or take care of these indexes.

Thanks!

-Matthew

On Fri, Jun 12, 2009 at 12:36 PM, Kevin Chan<kevin at media.berkeley.edu> wrote:
> 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,
>>>
>>>
>>
>
>