As many know, Oracle 12 has not reached each and every corner of many production sites. Okay, running Oracle 7 or 8 is becoming tricky…
Moving from anywhere to Oracle 11.2.0.4.0 is still a valid action as it is still the latest and rock-solid stable release out there.
I wanted to share what I ran into, upgrading 11.1.0.7.0 to 11.2.0.4.0 with in-place upgrades (catupgr.sql). As 11.1 to 11.2 is a relatively small step, in quite a few instances we have chosen an in-place upgrade over ‘ye old fashioned export/import with the main reason; saving time and reducing chances on error as you factually stay in the same database.
On the topic of “saving time”… this is what I found…
At some point during the upgrade, tailing the log, I noticed that an unreasonable and unexplainable amount of time (50% of the entire upgrade duration in my example) was spent on just this one statement:
— revoke grant with grant option privs
Note: Contact me for the actual statement…
This got me puzzled to such a state, and since The Internet didn’t hold any of the answers, I decided to turn to MOS and raise an SR.
And after just a few messages to and fro, the issue was found!
The time this bit of program spends, is spend on ORDIM and SDO, better known as Oracle Intermedia and Oracle Spatial. Bringing me to the task (which I knew that answer on already, as a matter of fact) of finding out if both of these technologies were used, and that was easy enough:
connect / as sysdba
— ORDIM
select owner, table_name, column_name
from dba_tab_cols
where data_type in (‘ORDAUDIO’,’ORDDOC’,’ORDIMAGE’,’ORDSOURCE’,’ORDVIDEO’)
order by 1,2,3;
— SDO
select owner,index_name from dba_indexes
where ityp_name = ‘SPATIAL_INDEX’;
select owner, table_name, column_name
from dba_tab_columns
where data_type = ‘SDO_GEOMETRY’
and owner != ‘MDSYS’
order by 1,2,3;
Which in my case returned “no rows selected” as expected.
With this knowledge “in pocket”, it was a matter of removing the unwanted matter
- Deinstall Oracle Spatial (SDO) following the steps listed in Note.179472.1.
- Deinstall ORDIM per $ORACLE_HOME/ord/im/admin/README.txt, see Note.337415.1.
And do the upgrade in half the time.
Well, hope this helps to save you some time.