Rem Rem $Header: rdbms/admin/e1001000.sql /st_rdbms_11.2.0/1 2010/12/02 02:20:42 ineall Exp $ Rem Rem e1001000.sql Rem Rem Copyright (c) 2000, 2010, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem e1001000.sql - downgrade Oracle RDBMS from current release to 10.1.0 Rem Rem DESCRIPTION Rem Rem Rem This script performs the downgrade in the following stages: Rem STAGE 1: downgrade from the current release to 10.2; Rem this stage is a no-op for 10.2 since the current release Rem is 10.2. Rem STAGE 2: downgrade base data dictionary objects from 10.2 to 10.1.0 Rem a. remove new 10.2 system/object privileges Rem b. remove new 10.2 catalog views/synonyms Rem (previous release views will be recreated after) Rem c. remove program units referring to new 10.2 fixed views Rem or non-compiling in 10.1.0 Rem d. update new 10.2 columns to NULL or other values, Rem delete rows from new 10.2 tables, and drop new Rem 10.2 type attributes, methods, etc. Rem e. downgrade system types from 10.2 to 10.1.0 Rem Rem NOTES Rem * This script needs to be run in the current release's environment Rem (before installing the release to which you want to downgrade). Rem * This script must be run using SQL*PLUS. Rem * You must be connected AS SYSDBA to run this script. Rem Rem MODIFIED (MM/DD/YY) Rem ineall 12/02/10 - Bug 10128898: backport added new dependency Rem achoi 04/30/09 - remove alter edition Rem dvoss 11/07/08 - bug 7553884 - clear logminer chunk_supress bit Rem for logical standby Rem achoi 09/02/08 - drop orabase Rem rgmani 01/18/08 - Drop types job_definiton and job_definition_array Rem cdilling 11/29/07 - move dbms_assert calls to f1001000.sql Rem cdilling 09/25/07 - move drop_transformation to f1001000.sql Rem araghava 07/06/07 - 6144565: drop unique indexes on partitioning Rem tables and recreate them as non-unique indexes Rem cdilling 07/11/07 - move .downgraded calls to f scripts Rem bkuchibh 05/31/07 - fix bug #6071289 Rem pbelknap 05/07/07 - #5999827 - set attr3 of SQLSET object to NULL Rem rburns 04/30/07 - fix execute immediates Rem akociube 03/13/07 - grant execute on olapimpl_t Rem mlfeng 12/15/06 - partitioning enabling Rem gviswana 12/05/06 - Flush shared pool after props$ delete Rem achoi 10/03/06 - drop "_BASE_USER" Rem jsoule 10/13/06 - suppress bsln 942s on downgrade from 11.1 Rem cdilling 09/14/06 - restructure to move pl/sql to f1001000.sql Rem rburns 07/21/06 - reset session Rem rramkiss 07/11/06 - drop more 11g scheduler types Rem achoi 06/30/06 - remove EDITION_OBJ view Rem achoi 06/08/06 - Rem rgmani 05/30/06 - Rem kyagoub 05/04/06 - drop new package dbms_sqltune_util0 Rem kyagoub 05/04/06 - fix lrg#2182885 Rem cdilling 10/24/05 - add semicolon Rem cdilling 10/03/05 - move session_history changes to e1002000.sql Rem pbelknap 08/17/05 - add dbms_sqltune dependencies to drop here Rem dsampath 06/28/05 - fix bug 4449955, upgrade SQL profiles dont match Rem adagarwa 04/25/05 - null out l/sql stack cols in wrh$_active_sess_history Rem cdilling 06/08/05 - invoke e1002000.sql Rem rburns 06/07/05 - drop stats package Rem dsampath 05/26/05 - revert sqltext signature in dictionary tables Rem mlfeng 05/25/05 - bug 4393879: disable metrics constraints Rem kyagoub 04/06/05 - add sqltune resume_filter Rem mlfeng 05/09/05 - drop public synonym Rem evoss 05/16/05 - remove calendar downgrade Rem preilly 05/05/05 - Empty LOGMNRP_CTAS_PART_MAP during downgrade Rem pbelknap 05/02/05 - wri$_adv_sqlt_plan constraint name change Rem pbelknap 04/28/05 - add sqlset plan temp table Rem svshah 05/11/05 - Rem qyu 04/28/05 - #4280015: change name back to array_index for octs Rem weiwang 04/28/05 - remove 10.2 attribute in msg_prop_t Rem abrown 04/26/05 - bug 4219586: revert logmnr_log primary key Rem ksurlake 04/22/05 - drop constructor for aq$_reg_info Rem mxu 04/14/05 - Drop dbms_sqlhash package Rem mlfeng 04/11/05 - null out column for wrh$_sqlstat Rem alakshmi 03/31/05 - drop dbms_streams_mt Rem rburns 04/02/05 - fix lrg problems Rem adagarwa 03/24/05 - null out new columns in wrh$_active_sess_history Rem weiwang 03/25/05 - invalidate rules engine objects in downgrade Rem tfyu 03/17/05 - Bug 4262763 Rem lkaplan 03/18/05 - bug 4112826 - upgrade and downgrade of Rem catqueue.sql needed Rem alakshmi 03/09/05 - recoverable scripts Rem wyang 03/02/05 - null out columns in wrh$undostat Rem nshodhan 03/04/05 - drop v$streams_transaction Rem sylin 02/25/05 - drop dbms_sqlplus_script Rem alakshmi 02/23/05 - error recovery for maintain_ apis Rem twtong 02/21/05 - bug-4168683 Rem rburns 02/21/05 - drop dbms_assert last Rem gviswana 02/08/05 - Remove UTL_RECOMP views Rem weiwang 02/09/05 - Rem hxlin 02/07/05 - Drop sql response view Rem htran 02/01/05 - drop dba_streams_apply_spill_txn view Rem alakshmi 01/28/05 - streams$_apply_spill_msgs_part Rem mture 01/26/05 - add AggXMLInputType during downgrade. Rem grant public exec privs back to xmlagg, xmlseq Rem evoss 01/25/05 - drop type scheduler int array Rem evoss 01/18/05 - scheduler_run_details cpu_used datatype change Rem ddas 01/11/05 - #(4052436) downgrade for outln.ol$hints Rem mcusson 01/04/05 - drop logmnr_parameter$ during downgrade Rem elu 01/03/05 - apply spilling Rem rramkiss 01/06/05 - drop dbms_isched_chain_condition package Rem rburns 12/22/04 - fix dbms_assert drop Rem kumamage 12/13/04 - remove parameter views Rem dbronnik 12/13/04 - drop package dbms_assert Rem ksurlake 12/07/04 - drop type sys.re$rule_list Rem adagarwa 11/23/04 - Remove SQL Report related tables Rem htran 11/16/04 - downgrade spare2 and flags in streams$_prepare_* Rem clei 11/15/04 - lrg 1796684 delete reused privs before restore it Rem kmeiyyap 11/12/04 - drop prvtaqiu.sql package Rem mbastawa 11/12/04 - drop 10.2 TSMSYS schema for 10.1 Rem kneel 11/09/04 - lrg 1795206 upgrade/downgrade issues Rem kyagoub 11/03/04 - remove sqltune R2 new parameter Rem kyagoub 10/04/04 - use bind_data vs bind_list is sts Rem weiwang 11/04/04 - fix downgrade script Rem lvbcheng 11/03/04 - Drop UTL_NLA types Rem apareek 10/21/04 - drop library dbms_extended_tts_checks_lib Rem apadmana 11/02/04 - bug3986609: drop dbms_transform_eximp_internal Rem weiwang 10/12/04 - drop deq by condition view Rem rvissapr 10/28/04 - rvissapr_bug-3802440 Rem jmzhang 10/15/04 - add gv/v$standby_apply_snapshot Rem - drop logstdby_thread Rem mlfeng 10/07/04 - awr sqlstat changes Rem rgmani 10/26/04 - scheduler attributes table has new columns Rem clei 10/28/04 - remove merge [any] view permissions Rem pthornto 10/07/04 - drop DBA_CONNECT_ROLE_GRANTEES view Rem apadmana 10/05/04 - bug3607838: manage any queue Rem abrumm 09/29/04 - LRG 1745604: drop dbms_errlog Rem banand 09/27/04 - drop rman_backup_type Rem qiwang 08/26/04 - truncate logmnr_filter$ table Rem mtakahar 09/03/04 - downgrade mon_mods$/mon_mods_all$ Rem nbhatt 08/30/04 - make aq downgrande idempotent Rem mhho 08/31/04 - drop DPCR package for downgrade Rem mlfeng 07/29/04 - truncate new AWR tables Rem ssvemuri 08/30/04 - drop change notification views Rem xuhuali 08/11/04 - audit java Rem jnarasin 08/02/04 - EUS Proxy auditing changes Rem jmzhang 08/26/04 - drop v$logstdby_status Rem change v$pstdby_status to v$datagurd_stats Rem mxyang 07/30/04 - drop package dbms_preprocessor Rem jwwarner 08/19/04 - drop new xquery objects Rem pbelknap 07/16/04 - AWR report types Rem jmzhang 08/17/04 - drop gv$logstdby_thread, v$logstdby_status Rem drop v$phystdby_status Rem kyagoub 08/16/04 - remove public synonym sql_object Rem kyagoub 07/28/04 - drop sql tuning all_views_xxx Rem jsoule 08/03/04 - downgrade from dbsnmp baselines Rem jmzhang 07/25/04 - drop v$/gv$rfs_thread Rem gmulagun 08/02/04 - drop DBA_COMMON_AUDIT_TRAIL view Rem gssmith 07/29/04 - Add DROP of PRVT_WORKLOAD_NOPRIV Rem rburns 07/29/04 - move some actions to catdwgrd.sql Rem nshodhan 07/29/04 - remove _DBA_STREAMS_NEWLY_SUPTED_10_2 Rem htran 06/18/04 - remove commit time qtab export support Rem bemeng 07/26/04 - drop dbms_space: reference to new fixed table Rem hxlin 07/20/04 - Update SQL Response Time downgrade Rem kdias 07/21/04 - privs for OUTLN user Rem mhho 07/20/04 - drop synonyms own downgrade Rem nmanappa 07/20/04 - bug 3690876 - add privs 194-199,239,240 Rem dmwong 07/21/04 - grant priv. back to connect role Rem skaluska 07/09/04 - split tsm_hist$ into tsm_src$, tsm_dst$ Rem clei 06/29/04 - drop Transparent Column Encryption dict objects Rem twtong 07/02/04 - drop package dbms_cdc_sys_ipublish Rem kyagoub 06/30/04 - remove plan filter parameter Rem pbelknap 06/28/04 - add mask to join Rem nbhatt 06/30/04 - downgrade invalidation bug Rem sbalaram 05/25/04 - AQ: recreate base view, alter primary key Rem hxlin 06/28/04 - downgrade sql response time Rem htran 06/18/04 - remove file group export registrations Rem svshah 06/28/04 - Drop synonyms for v$sqlstats Rem ssvemuri 06/25/04 - drop change notification dictionary objs Rem bhabeck 06/14/04 - drop v$process_memory_detail Rem rburns 06/18/04 - drop dbms_rcvman Rem veeve 06/16/04 - drop package dbms_ash_internal Rem ajadams 06/15/04 - drop index on logstdby events table Rem suelee 06/04/04 - add new view resource manager views Rem mramache 06/01/04 - AWR report types Rem kneel 06/25/04 - instance down alert reliability work Rem aahluwal 06/23/04 - drop DBMS_HAEVENTNOT_PRVT_LIB Rem kneel 06/10/04 - drop DBMS_SVRALRT_PRVT_LIB Rem kneel 06/08/04 - drop package dbms_ha_alerts_prvt Rem pbelknap 06/25/04 - drop sql plan types Rem bdagevil 06/19/04 - add [g]v$sqlstats and [g]v$sqlarea_plan_hash Rem pbelknap 06/04/04 - remap sts names Rem pbelknap 05/14/04 - SQLSET_ROW change Rem kyagoub 05/14/04 - replace sql_binds_nt/sql_binds_ntab Rem pbelknap 05/07/04 - sqltune bind types, STS schema Rem sridsubr 06/04/04 - Drop new tables Rem weili 06/07/04 - drop decision tree internal function Rem rvissapr 06/10/04 - drop exp views Rem ahwang 06/10/04 - remov restore point audit_actions rows Rem mlfeng 05/21/04 - downgrade wr_control for topnsql Rem veeve 05/28/04 - NULL out WRH$_SEG_STAT_OBJ.[INDEX_TYPE,BASE*] Rem narora 05/20/04 - truncate wrh$_sess_time_stats, streams tables Rem narora 05/20/04 - drop sess_time_stats, streams views Rem ushaft 05/15/04 - truncate WRH$_COMP_IOSTAT, WRH$_SGA_TARGET_ADVICE Rem and drop views Rem rburns 06/04/04 - truncate registry schemas Rem ksurlake 06/01/04 - downgrade aq$_reg_info Rem jnarasin 05/27/04 - Alter User changes for EUS Proxy project Rem gmulagun 05/26/04 - drop v$xml_audit_trail related views Rem lvbcheng 06/02/04 - drop utl_nla Rem evoss 06/14/04 - fix downgrade of calendars Rem rramkiss 06/14/04 - lrg 1708395-don't drop scheduler types for 9.2 Rem downgrade Rem kpatel 05/18/04 - drop v$asm_diskgroup_stat and v$asm_disk_stat Rem dcassine 05/13/04 - Streams Dependency downgrade Rem ahwang 05/26/04 - drop restore point related objects Rem molagapp 05/22/04 - drop v$flash_recovery_area_usage view Rem bhabeck 05/23/04 - drop v$process_memory on downgrades Rem rvissapr 05/24/04 - drop dbms_dblink Rem evoss 05/28/04 - downgrade calendar schedules Rem vakrishn 06/01/04 - add downgrade for status column of WRH$_UNDOSTAT Rem banand 05/21/04 - drop RMAN job views Rem mlfeng 05/18/04 - update swrf_version in wr_control Rem kneel 05/26/04 - remove new package dbms_server_alert_prvt Rem ushaft 05/26/04 - drop views for wrh$_streams_pool_advice Rem narora 05/19/04 - drop new v$streams_pool_advice views Rem mxyang 06/02/04 - drop package DBMS_DB_VERSION Rem dcassine 06/11/04 - drop DBMS_APPLY_USER_AGENT package Rem liwong 06/09/04 - Add get_source_time Rem liwong 06/08/04 - Add oldest_transaction_id Rem dcassine 05/27/04 - change streams$_apply_process Rem wesmith 05/24/04 - add online redefinition downgrade Rem rdecker 05/24/04 - drop package UTL_MATCH Rem tcruanes 05/29/04 - add downgrade support for SQL_JOIN_FILTER Rem dsemler 05/14/04 - add dtp support Rem dsemler 04/26/04 - add downgrade for goal column in service$. Rem bdagevil 05/26/04 - generalize timestamp column in explain plan Rem bdagevil 05/24/04 - add code to downgrade other_xml column Rem nfolkert 05/25/04 - drop index rebuild list objects Rem veeve 05/06/04 - blocking_session,xid columns in ASH Rem skaluska 05/05/04 - merge to MAIN Rem skaluska 04/28/04 - sync with RDBMS_MAIN_SOLARIS_040426 Rem jciminsk 04/28/04 - merge from RDBMS_MAIN_SOLARIS_040426 Rem skaluska 04/15/04 - TSM modifications Rem jciminsk 04/09/04 - merge from RDBMS_MAIN_SOLARIS_040405 Rem lchidamb 03/23/04 - drop director history/reason table Rem skaluska 03/18/04 - move TSM changes from e0902000.sql to Rem jstamos 03/16/04 - drop dbms_db_director_policy Rem rramkiss 03/08/04 - drop scheduler chains Rem rramkiss 03/08/04 - remove scheduler chain views Rem jciminsk 03/05/04 - move drop of dbms_tsm Rem jciminsk 03/04/04 - move grid from e0902000 Rem ckantarj 02/27/04 - add cardinality columns to service$ Rem mxiao 05/13/04 - set chdlevid# in dimjoinkey$ Rem bpwang 03/10/04 - internal lcr transformation downgrade Rem weiwang 03/09/04 - rules downgrade change Rem mbrey 05/03/04 - CDC new views removal Rem mbrey 04/08/04 - CDC meta changes for sequences Rem mbrey 03/30/04 - CDC change sources/propagations Rem lkaplan 04/01/04 - generic lob assembly Rem nmukherj 05/12/04 - delete rows from wri$_alert_threshold Rem corresponding to bytes based thresholds Rem with metric id 9001 Rem wyang 03/12/04 - transportable database Rem smuthuli 05/21/04 - auto advisor changes Rem smuthuli 05/18/04 - one more stat to seg_stat Rem weiwang 03/12/04 - truncate new rules engine tables Rem liwong 02/21/04 - Fast column value evaluation Rem pokumar 05/12/04 - remove sga_target_advice views Rem rgmani 05/19/04 - Downgrade for scheduler Rem mxyang 05/10/04 - drop plsql_ccflags Rem atsukerm 04/27/04 - remove the database-level trace Rem htran 04/19/04 - drop file group packages/views. truncate tables Rem alakshmi 04/19/04 - system privilege READ_ANY_FILE_GROUP Rem alakshmi 02/24/04 - delete new system privileges for file groups Rem alakshmi 02/16/04 - Drop dbms_file_group packages Rem sbodagal 04/27/04 - set flags to 0 in dimlevel$ Rem ajadams 05/13/04 - drop logstdby_transaction Rem jmzhang 05/12/04 - alter column datatype in system.logstdby$events Rem - nullify new columns in logstdby$apply_milestone Rem - nullify new column in logstdby$apply_progress Rem smangala 04/21/04 - drop logmnr_dictionary_load view Rem mlfeng 04/27/04 - set values to NULL for AWR table Rem nshodhan 04/05/04 - remove downstream capture hotmining Rem rramkiss 04/21/04 - remove 10.2 CREATE EXTERNAL JOB system privilege Rem bgarin 04/08/04 - Add LogMiner downgrade section Rem mxiao 03/30/04 - set values to NULL for MV metadata Rem gssmith 04/07/04 - SQL Access Advisor adjustments Rem jgalanes 03/16/04 - Follow up for 3467567 - drop new views on Dgrade Rem clei 03/02/04 - put back encryption profile privileges Rem rdecker 03/03/04 - support procedureplsql$ Rem bpwang 02/19/04 - downgrade error creation time in apply$_error Rem mjaeger 02/09/04 - bug 3369744: drop support views for ALL_SYNONYMS Rem pbelknap 02/12/04 - case-sensitive sqlset definitions Rem bmccarth 02/16/04 - drop export partition template views Rem mlfeng 02/03/04 - AWR segstat and rac changes Rem sbalaram 02/03/04 - truncate apply$_error_txn Rem gssmith 02/11/04 - Advisor Framework changes Rem rburns 01/16/04 - rburns_add_10_1_updw_scripts Rem rburns 01/08/04 - Rem rburns 01/07/04 - Created Rem ========================================================================= Rem BEGIN STAGE 1: downgrade from the current release to 10.2 Rem ========================================================================= @@e1002000 Rem ========================================================================= Rem END STAGE 1: downgrade from the current release to 10.2 Rem ========================================================================= Rem ========================================================================= Rem BEGIN STAGE 2: downgrade dictionary from 10.2 to 10.1 Rem ========================================================================= Rem========================================================================= Rem Delete new system privileges here Rem========================================================================= -- delete merge any view system privilege delete from SYSAUTH$ where privilege# = -233; delete from SYSTEM_PRIVILEGE_MAP where privilege = -233; insert into SYSTEM_PRIVILEGE_MAP values (-194, 'WRITEDOWN DBLOW', 0); insert into SYSTEM_PRIVILEGE_MAP values (-195, 'READUP DBHIGH', 0); insert into SYSTEM_PRIVILEGE_MAP values (-196, 'WRITEUP DBHIGH', 0); insert into SYSTEM_PRIVILEGE_MAP values (-197, 'WRITEDOWN', 0); insert into SYSTEM_PRIVILEGE_MAP values (-198, 'READUP', 0); insert into SYSTEM_PRIVILEGE_MAP values (-199, 'WRITEUP', 0); insert into SYSTEM_PRIVILEGE_MAP values (-229, 'CREATE SECURITY PROFILE',0); insert into SYSTEM_PRIVILEGE_MAP values (-230, 'CREATE ANY SECURITY PROFILE',0); insert into SYSTEM_PRIVILEGE_MAP values (-231, 'DROP ANY SECURITY PROFILE',0); insert into SYSTEM_PRIVILEGE_MAP values (-232, 'ALTER ANY SECURITY PROFILE',0); insert into SYSTEM_PRIVILEGE_MAP values (-233, 'ADMINISTER SECURITY', 0); insert into SYSTEM_PRIVILEGE_MAP values (-239, 'DEBUG CONNECT USER', 0); insert into SYSTEM_PRIVILEGE_MAP values (-240, 'DEBUG CONNECT ANY', 0); insert into STMT_AUDIT_OPTION_MAP values (194, 'WRITEDOWN DBLOW', 0); insert into STMT_AUDIT_OPTION_MAP values (195, 'READUP DBHIGH', 0); insert into STMT_AUDIT_OPTION_MAP values (196, 'WRITEUP DBHIGH', 0); insert into STMT_AUDIT_OPTION_MAP values (197, 'WRITEDOWN', 0); insert into STMT_AUDIT_OPTION_MAP values (198, 'READUP', 0); insert into STMT_AUDIT_OPTION_MAP values (199, 'WRITEUP', 0); insert into STMT_AUDIT_OPTION_MAP values (239, 'DEBUG CONNECT USER', 0); insert into STMT_AUDIT_OPTION_MAP values (240, 'DEBUG CONNECT ANY', 0); -- delete from SYSAUTH$ where privilege# in (); -- delete from SYSTEM_PRIVILEGE_MAP where privilege in (); -- commit; -- delete file group system privileges delete from SYSAUTH$ where privilege# in (-276, -277, -278); delete from SYSTEM_PRIVILEGE_MAP where privilege in (-276, -277, -278); -- delete change notification and create external job privilege delete from SYSAUTH$ where privilege# in (-279, -280); delete from SYSTEM_PRIVILEGE_MAP where privilege in (-279, -280); commit; Rem========================================================================= Rem Delete new object privileges here Rem========================================================================= --delete from OBJAUTH$ where privilege# in (); --delete from TABLE_PRIVILEGE_MAP where privilege in (); -- delete merge view object privilege delete from OBJAUTH$ where privilege# in (28); delete from TABLE_PRIVILEGE_MAP where privilege in (28); commit; Rem========================================================================= Rem Undo removal of privileges from CONNECT role here Rem========================================================================= -- grant the privileges back grant alter session,create synonym,create view, create database link,create table,create cluster,create sequence to connect; commit; Rem========================================================================= Rem Delete new audit options here Rem========================================================================= -- delete from AUDIT$ where option# in (); -- delete from STMT_AUDIT_OPTION_MAP where option# in (); -- commit; delete from AUDIT$ where option# in (276, 277, 278); delete from STMT_AUDIT_OPTION_MAP where option# in (276, 277, 278); delete from AUDIT$ where option# in (279, 280); delete from STMT_AUDIT_OPTION_MAP where option# in (279, 280); delete from AUDIT$ where option# in (93, 94, 95, 96, 97, 98, 99, 100, 101); delete from STMT_AUDIT_OPTION_MAP where option# in (93, 94, 95, 96, 97, 98, 99, 100, 101); delete from AUDIT$ where option# in (233); delete from STMT_AUDIT_OPTION_MAP where option# in (233); -- AQ system privileges update STMT_AUDIT_OPTION_MAP set property = 1 where option# = 218 and name = 'MANAGE ANY QUEUE'; update STMT_AUDIT_OPTION_MAP set property = 1 where option# = 219 and name = 'ENQUEUE ANY QUEUE'; update STMT_AUDIT_OPTION_MAP set property = 1 where option# = 220 and name = 'DEQUEUE ANY QUEUE'; delete from AUDIT$ where option# in (218, 219, 220); -- Rules Engine system privileges delete from STMT_AUDIT_OPTION_MAP where option# = 245 and name = 'CREATE EVALUATION CONTEXT'; delete from STMT_AUDIT_OPTION_MAP where option# = 246 and name = 'CREATE ANY EVALUATION CONTEXT'; delete from STMT_AUDIT_OPTION_MAP where option# = 247 and name = 'ALTER ANY EVALUATION CONTEXT'; delete from STMT_AUDIT_OPTION_MAP where option# = 248 and name = 'DROP ANY EVALUATION CONTEXT'; delete from STMT_AUDIT_OPTION_MAP where option# = 249 and name = 'EXECUTE ANY EVALUATION CONTEXT'; delete from STMT_AUDIT_OPTION_MAP where option# = 250 and name = 'CREATE RULE SET'; delete from STMT_AUDIT_OPTION_MAP where option# = 251 and name = 'CREATE ANY RULE SET'; delete from STMT_AUDIT_OPTION_MAP where option# = 252 and name = 'ALTER ANY RULE SET'; delete from STMT_AUDIT_OPTION_MAP where option# = 253 and name = 'DROP ANY RULE SET'; delete from STMT_AUDIT_OPTION_MAP where option# = 254 and name = 'EXECUTE ANY RULE SET'; delete from STMT_AUDIT_OPTION_MAP where option# = 257 and name = 'CREATE RULE'; delete from STMT_AUDIT_OPTION_MAP where option# = 258 and name = 'CREATE ANY RULE'; delete from STMT_AUDIT_OPTION_MAP where option# = 259 and name = 'ALTER ANY RULE'; delete from STMT_AUDIT_OPTION_MAP where option# = 260 and name = 'DROP ANY RULE'; delete from STMT_AUDIT_OPTION_MAP where option# = 261 and name = 'EXECUTE ANY RULE'; delete from AUDIT$ where option# in (245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 257, 258, 259, 260, 261); commit; Rem Delete new audit_actions rows Rem========================================================================= -- delete restore point related rows delete from AUDIT_ACTIONS where action = 206 or action = 207; -- delete single session proxy related rows DELETE FROM audit_actions WHERE action = 208 ; commit; Rem ========================================================================= Rem Begin Downgrade Advisor Framework items Rem ========================================================================= alter type wri$_adv_sqlaccess_adv drop overriding member procedure sub_implement(task_id in number, rec_id in number, exit_on_error number) cascade; alter type wri$_adv_tunemview_adv drop overriding member procedure sub_implement(task_id in number, rec_id in number, exit_on_error number) cascade; alter type wri$_adv_abstract_t drop member procedure sub_implement(task_id in number, rec_id in number, exit_on_error number) cascade; Rem Rem change the journaling flags back to prior settings Rem declare dtype binary_integer; begin select datatype into dtype from sys.wri$_adv_def_parameters where name = 'JOURNALING'; if dtype = 2 then update sys.wri$_adv_journal set type = decode(type,4,1,3,2,2,3,1,4,type); update sys.wri$_adv_def_parameters set value = decode(value,'UNUSED',0,'FATAL',4,'ERROR',3,'WARNING',2, 'INFORMATION',1, 'INFORMATION2',5,'INFORMATION3',6,'INFORMATION4',6,'INFORMATION5', 8,'INFORMATION6',9,0), datatype = 1 where name = 'JOURNALING'; update sys.wri$_adv_parameters set value = decode(value,'UNUSED',0,'FATAL',4,'ERROR',3,'WARNING',2, 'INFORMATION',1, 'INFORMATION2',5,'INFORMATION3',6,'INFORMATION4',6,'INFORMATION5', 8,'INFORMATION6',9,0), datatype = 1 where name = 'JOURNALING'; end if; end; / Rem Rem Remove new default task parameters for advisors Rem create or replace procedure advfmwk_delete_def_parameter (l_id number, l_name varchar2) is l_cnt binary_integer; begin select count(*) into l_cnt from wri$_adv_def_parameters where advisor_id = l_id and name = l_name; if l_cnt > 0 then delete from wri$_adv_def_parameters where advisor_id = l_id and name = l_name; end if; end advfmwk_delete_def_parameter; / Rem Rem Remove the new advisor-specified default parameters Rem begin advfmwk_delete_def_parameter (2, '_IMP_ERROR_LIST'); advfmwk_delete_def_parameter (2, '_INCLUDE_SCRIPT_HEADER'); advfmwk_delete_def_parameter (2, '_INVALID_SQLCOMMENTS_LIST'); advfmwk_delete_def_parameter (2, '_INVALID_USERNAME_LIST'); advfmwk_delete_def_parameter (2, '_SHOW_ADV_PARAMETERS'); advfmwk_delete_def_parameter (2, '_TEST_MODE'); advfmwk_delete_def_parameter (2, 'DISABLE_FILTERS'); advfmwk_delete_def_parameter (2, 'IMPLEMENT_EXIT_ON_ERROR'); advfmwk_delete_def_parameter (2, 'INVALID_ACTION_LIST'); advfmwk_delete_def_parameter (2, 'INVALID_MODULE_LIST'); advfmwk_delete_def_parameter (2, 'INVALID_SQLSTRING_LIST'); advfmwk_delete_def_parameter (2, 'INVALID_USERNAME_LIST'); advfmwk_delete_def_parameter (2, 'RECOMMEND_MV_EXACT_TEXT_MATCH'); advfmwk_delete_def_parameter (2, 'SHOW_RETAINS'); advfmwk_delete_def_parameter (2, 'STORAGE_MODE'); advfmwk_delete_def_parameter (2, 'VALID_ACTION_LIST'); advfmwk_delete_def_parameter (2, 'VALID_MODULE_LIST'); advfmwk_delete_def_parameter (2, 'VALID_SQLSTRING_LIST'); advfmwk_delete_def_parameter (2, 'VALID_USERNAME_LIST'); advfmwk_delete_def_parameter (4, 'PLAN_FILTER'); advfmwk_delete_def_parameter (4, 'LOCAL_TIME_LIMIT'); advfmwk_delete_def_parameter (4, 'COMMIT_ROWS'); advfmwk_delete_def_parameter (4, '_SQLTUNE_TRACE'); advfmwk_delete_def_parameter (4, 'RESUME_FILTER'); advfmwk_delete_def_parameter (6, '_INVALID_SQLCOMMENTS_LIST'); advfmwk_delete_def_parameter (6, '_INVALID_USERNAME_LIST'); advfmwk_delete_def_parameter (6, '_SHOW_ADV_PARAMETERS'); advfmwk_delete_def_parameter (6, '_TEST_MODE'); advfmwk_delete_def_parameter (6, 'DISABLE_FILTERS'); advfmwk_delete_def_parameter (6, 'INVALID_ACTION_LIST'); advfmwk_delete_def_parameter (6, 'INVALID_MODULE_LIST'); advfmwk_delete_def_parameter (6, 'INVALID_SQLSTRING_LIST'); advfmwk_delete_def_parameter (6, 'INVALID_USERNAME_LIST'); advfmwk_delete_def_parameter (6, 'VALID_ACTION_LIST'); advfmwk_delete_def_parameter (6, 'VALID_MODULE_LIST'); advfmwk_delete_def_parameter (6, 'VALID_SQLSTRING_LIST'); advfmwk_delete_def_parameter (6, 'VALID_USERNAME_LIST'); advfmwk_delete_def_parameter (7, '_IMP_ERROR_LIST'); advfmwk_delete_def_parameter (7, '_INCLUDE_SCRIPT_HEADER'); advfmwk_delete_def_parameter (7, '_INVALID_SQLCOMMENTS_LIST'); advfmwk_delete_def_parameter (7, '_INVALID_USERNAME_LIST'); advfmwk_delete_def_parameter (7, '_SHOW_ADV_PARAMETERS'); advfmwk_delete_def_parameter (7, '_TEST_MODE'); advfmwk_delete_def_parameter (7, 'DISABLE_FILTERS'); advfmwk_delete_def_parameter (7, 'IMPLEMENT_EXIT_ON_ERROR'); advfmwk_delete_def_parameter (7, 'INVALID_ACTION_LIST'); advfmwk_delete_def_parameter (7, 'INVALID_MODULE_LIST'); advfmwk_delete_def_parameter (7, 'INVALID_SQLSTRING_LIST'); advfmwk_delete_def_parameter (7, 'INVALID_USERNAME_LIST'); advfmwk_delete_def_parameter (7, 'RECOMMEND_MV_EXACT_TEXT_MATCH'); advfmwk_delete_def_parameter (7, 'SHOW_RETAINS'); advfmwk_delete_def_parameter (7, 'STORAGE_MODE'); advfmwk_delete_def_parameter (7, 'VALID_ACTION_LIST'); advfmwk_delete_def_parameter (7, 'VALID_MODULE_LIST'); advfmwk_delete_def_parameter (7, 'VALID_SQLSTRING_LIST'); advfmwk_delete_def_parameter (7, 'VALID_USERNAME_LIST'); end; / drop procedure advfmwk_delete_def_parameter; Rem Rem Remove new default task parameters from existing tasks Rem declare cursor param_cur IS SELECT a.task_id,a.name FROM sys.wri$_adv_parameters a,sys.wri$_adv_tasks b WHERE a.task_id = b.id and not exists (select c.name from sys.wri$_adv_def_parameters c where (c.advisor_id = b.advisor_id or c.advisor_id = 0) and c.name = a.name); l_task_id binary_integer; l_name VARCHAR2(30); begin open param_cur; loop fetch param_cur into l_task_id,l_name; exit when param_cur%NOTFOUND; delete from sys.wri$_adv_parameters where name = l_name and task_id = l_task_id; end loop; close param_cur; end; / Rem Rem Simple updates Rem update sys.wri$_adv_def_parameters set flags = 0 where name = 'TIME_LIMIT'; update sys.wri$_adv_parameters set flags = 0 where name = 'TIME_LIMIT'; update sys.wri$_adv_def_parameters set flags = 0 where name = 'EM_DATA'; update sys.wri$_adv_parameters set flags = bitand(flags,14) where name = 'EM_DATA'; update sys.wri$_adv_def_parameters set description = null; update sys.wri$_adv_parameters set description = null; update sys.wri$_adv_recommendations set flags = 0; update sys.wri$_adv_sqlt_plans set other_xml = null; update sys.wri$_adv_objects set other = null; Rem Rem Changes in advisor definitions: make sqltune task not resumable Rem update wri$_adv_definitions set property = 3 where id = 4; Rem Rem Delete 9001 metric rows from wri$_alert_threshold Rem delete from wri$_alert_threshold where t_metrics_id = 9001; commit; drop package prvt_workload_nopriv; Rem ========================================================================= Rem End Downgrade Advisor Framework items Rem ========================================================================= Rem Downgrade mon_mods$/mon_mods_all$: Copy the entries back to mon_mods$ Rem except for the ones that are non-physical fragments. begin merge into mon_mods$ m using (select mma.obj#, mma.inserts, mma.updates, mma.deletes, mma.timestamp, mma.flags, mma.drop_segments from mon_mods_all$ mma where not exists (select * from (select obj# from tab$ where bitand(property, 32) != 0 union all select obj# from tabcompart$ tp) t where t.obj# = mma.obj#) ) v on (m.obj# = v.obj#) when matched then update set m.inserts = m.inserts + v.inserts, m.updates = m.updates + v.updates, m.deletes = m.deletes + v.deletes, m.flags = m.flags + v.flags - bitand(m.flags,v.flags) /* bitor(m.flags,v.flags) */, m.drop_segments = m.drop_segments + v.drop_segments when NOT matched then insert values (v.obj#, v.inserts, v.updates, v.deletes, sysdate, v.flags, v.drop_segments); commit; execute immediate 'truncate table mon_mods_all$'; end; / Rem remove plsql metadata table delete from procedureplsql$; Rem Rem update sys_nc_array_index$ col name back to array_index for octs update col$ c set c.name='ARRAY_INDEX' where c.name = 'SYS_NC_ARRAY_INDEX$' and c.col#=0 and c.intcol#=1 and bitand(c.property, 32) = 32 and c.obj# in (select n.ntab# from ntab$ n) / commit; Rem========================================================================= Rem Drop new fixed views here Rem========================================================================= Rem Rem STREAMS SGA ADVISORY view Rem drop public synonym V$STREAMS_POOL_ADVICE; drop view V_$STREAMS_POOL_ADVICE; drop public synonym GV$STREAMS_POOL_ADVICE; drop view GV_$STREAMS_POOL_ADVICE; Rem Rem STREAMS Transaction view Rem drop public synonym V$STREAMS_TRANSACTION; drop view V_$STREAMS_TRANSACTION; drop public synonym GV$STREAMS_TRANSACTION; drop view GV_$STREAMS_TRANSACTION; Rem Rem Drop SQL_JOIN_FILTER views Rem DROP VIEW gv_$sql_join_filter; DROP PUBLIC SYNONYM gv$sql_join_filter; DROP VIEW v_$sql_join_filter; DROP PUBLIC SYNONYM v$sql_join_filter; Rem Rem Drop TSM views Rem DROP VIEW gv_$tsm_sessions; DROP PUBLIC SYNONYM gv$tsm_sessions; DROP VIEW v_$tsm_sessions; DROP PUBLIC SYNONYM v$tsm_sessions; Rem Rem RMAN views dealing with RMAN backup jobs Rem drop public synonym v$rman_backup_subjob_details; drop view v_$rman_backup_subjob_details; drop public synonym v$rman_backup_job_details; drop view v_$rman_backup_job_details; drop public synonym v$backup_set_details; drop view v_$backup_set_details; drop public synonym v$backup_piece_details; drop view v_$backup_piece_details; drop public synonym v$backup_copy_details; drop view v_$backup_copy_details; drop public synonym v$proxy_copy_details; drop view v_$proxy_copy_details; drop public synonym v$proxy_archivelog_details; drop view v_$proxy_archivelog_details; drop public synonym v$backup_datafile_details; drop view v_$backup_datafile_details; drop public synonym v$backup_controlfile_details; drop view v_$backup_controlfile_details; drop public synonym v$backup_archivelog_details; drop view v_$backup_archivelog_details; drop public synonym v$backup_spfile_details; drop view v_$backup_spfile_details; drop public synonym v$backup_set_summary; drop view v_$backup_set_summary; drop public synonym v$backup_datafile_summary; drop view v_$backup_datafile_summary; drop public synonym v$backup_controlfile_summary; drop view v_$backup_controlfile_summary; drop public synonym v$backup_archivelog_summary; drop view v_$backup_archivelog_summary; drop public synonym v$backup_spfile_summary; drop view v_$backup_spfile_summary; drop public synonym v$backup_copy_summary; drop view v_$backup_copy_summary; drop public synonym v$proxy_copy_summary; drop view v_$proxy_copy_summary; drop public synonym v$proxy_archivelog_summary; drop view v_$proxy_archivelog_summary; drop public synonym v$unusable_backupfile_details; drop view v_$unusable_backupfile_details; drop public synonym v$rman_backup_type; drop view v_$rman_backup_type; drop function v_listBackupPipe; drop type v_lbRecSetImpl_t; drop type v_lbRecSet_t; drop type v_lbRec_t; Rem Rem exp views dealing with table subpartition templates Rem drop view sys.exptabsubpart; drop view sys.exptabsubpartdata_view; drop view sys.exptabsubpartlobfrag; drop view sys.exptabsubpartlob_view; Rem Rem imp views dealing with LOBs having triggers or NOT NULL constraints Rem drop view sys.imp_tab_trig; drop view sys.imp_lob_notnull; Rem Rem LogMiner dictionary load view Rem drop public synonym v$logmnr_dictionary_load; drop view v_$logmnr_dictionary_load; drop public synonym gv$logmnr_dictionary_load; drop view gv_$logmnr_dictionary_load; Rem Rem RFS fixed views Rem drop public synonym gv$rfs_thread; drop view gv_$rfs_thread; drop public synonym v$rfs_thread; drop view v_$rfs_thread; Rem Rem asm fixed views Rem drop public synonym v$asm_diskgroup_stat; drop view v_$asm_diskgroup_stat; drop public synonym gv$asm_diskgroup_stat; drop view gv_$asm_diskgroup_stat; drop public synonym v$asm_disk_stat; drop view v_$asm_disk_stat; drop public synonym gv$asm_disk_stat; drop view gv_$asm_disk_stat; Rem Rem flash_recovery_area_usage view Rem drop public synonym v$flash_recovery_area_usage; drop view v_$flash_recovery_area_usage; Rem Rem restore_point view Rem drop public synonym v$restore_point; drop view v_$restore_point; drop public synonym gv$restore_point; drop view gv_$restore_point; Rem Rem Remove SGA_TARGET_ADVICE views Rem drop public synonym v$sga_target_advice; drop view v_$sga_target_advice; drop public synonym gv$sga_target_advice; drop view gv_$sga_target_advice; Rem Rem Remove Transparent Database Encryption related views Rem drop public synonym v$wallet; drop view v_$wallet; drop public synonym gv$wallet; drop view gv_$wallet; Rem Rem Remove resource manager-related views Rem drop public synonym v$rsrc_cons_group_history; drop view v_$rsrc_cons_group_history; drop public synonym gv$rsrc_cons_group_history; drop view gv_$rsrc_cons_group_history; drop public synonym v$rsrc_plan_history; drop view v_$rsrc_plan_history; drop public synonym gv$rsrc_plan_history; drop view gv_$rsrc_plan_history; Rem Rem Remove interconnect views Rem drop public synonym v$cluster_interconnects; drop view v_$cluster_interconnects; drop public synonym gv$cluster_interconnects; drop view gv_$cluster_interconnects; drop public synonym v$configured_interconnects; drop view v_$configured_interconnects; drop public synonym gv$configured_interconnects; drop view gv_$configured_interconnects; Rem Rem Remove parameter views Rem drop public synonym v$parameter_valid_values; drop view v_$parameter_valid_values; drop public synonym gv$parameter_valid_values; drop view gv_$parameter_valid_values; Rem Rem Remove RSRC_SESSION_INFO Rem drop public synonym v$rsrc_session_info; drop view v_$rsrc_session_info; drop public synonym gv$rsrc_session_info; drop view gv_$rsrc_session_info; Rem Rem Remove BLOCKING_QUIESCE Rem drop public synonym v$blocking_quiesce; drop view v_$blocking_quiesce; drop public synonym gv$blocking_quiesce; drop view gv_$blocking_quiesce; Rem Rem Remove SQLAREA_PLAN_HASH Rem drop public synonym v$sqlarea_plan_hash; drop view v_$sqlarea_plan_hash; drop public synonym gv$sqlarea_plan_hash; drop view gv_$sqlarea_plan_hash; Rem Rem Remove SQLSTATS Rem drop public synonym v$sqlstats; drop view v_$sqlstats; drop public synonym gv$sqlstats; drop view gv_$sqlstats; Rem Rem Logstdby views Rem drop public synonym v$logstdby_state; drop view v_$logstdby_state; drop public synonym v$logstdby_progress; drop view v_$logstdby_progress; drop public synonym v$logstdby_process; drop view v_$logstdby_process; drop public synonym v$logstdby_transaction; drop view v_$logstdby_transaction; drop public synonym gv$logstdby_state; drop view gv_$logstdby_state; drop public synonym gv$logstdby_progress; drop view gv_$logstdby_progress; drop public synonym gv$logstdby_process; drop view gv_$logstdby_process; drop public synonym gv$logstdby_transaction; drop view gv_$logstdby_transaction; Rem Dataguard views Rem drop public synonym v$dataguard_stats; drop view v_$dataguard_stats; drop public synonym v$standby_apply_snapshot; drop view v_$standby_apply_snapshot; drop public synonym gv$standby_apply_snapshot; drop view gv_$standby_apply_snapshot; Rem Rem xml format audit trail view Rem drop public synonym v$xml_audit_trail; drop view v_$xml_audit_trail; drop public synonym gv$xml_audit_trail; drop view gv_$xml_audit_trail; Rem Rem Process memory named-category view Rem drop public synonym v$process_memory; drop view v_$process_memory; drop public synonym gv$process_memory; drop view gv_$process_memory; Rem Rem Process memory heap scan totals and progress views Rem drop public synonym v$process_memory_detail; drop view v_$process_memory_detail; drop public synonym gv$process_memory_detail; drop view gv_$process_memory_detail; drop public synonym v$process_memory_detail_prog; drop view v_$process_memory_detail_prog; drop public synonym gv$process_memory_detail_prog; drop view gv_$process_memory_detail_prog; Rem Rem SQL statistics view Rem DROP VIEW gv_$sqlstats; DROP PUBLIC SYNONYM gv$sqlstats; DROP VIEW v_$sqlstats; DROP PUBLIC SYNONYM v$sqlstats; Rem Rem MUTEX view Rem drop public synonym v$mutex_sleep; drop view v_$mutex_sleep; drop public synonym gv$mutex_sleep; drop view gv_$mutex_sleep; drop public synonym v$mutex_sleep_history; drop view v_$mutex_sleep_history; drop public synonym gv$mutex_sleep_history; drop view gv_$mutex_sleep_history; Rem========================================================================= Rem Drop all new ALL/DBA/USER views here Rem========================================================================= drop view "_ALL_SYNONYMS_FOR_SYNONYMS"; drop view "_ALL_SYNONYMS_FOR_AUTH_OBJECTS"; drop view "_ALL_SYNONYMS_TREE"; drop view V_$DB_TRANSPORTABLE_PLATFORM; drop public synonym V$DB_TRANSPORTABLE_PLATFORM; drop view GV_$DB_TRANSPORTABLE_PLATFORM; drop public synonym GV$DB_TRANSPORTABLE_PLATFORM; Rem BEGIN drop file group views drop view dba_file_groups; drop view dba_file_group_versions; drop view dba_file_group_files; drop view dba_file_group_export_info; drop view dba_file_group_tables; drop view dba_file_group_tablespaces; drop public synonym dba_file_groups; drop public synonym dba_file_group_versions; drop public synonym dba_file_group_files; drop public synonym dba_file_group_export_info; drop public synonym dba_file_group_tables; drop public synonym dba_file_group_tablespaces; drop view all_file_groups; drop view all_file_group_versions; drop view all_file_group_files; drop view all_file_group_export_info; drop view all_file_group_tables; drop view all_file_group_tablespaces; drop view "_ALL_FILE_GROUPS"; drop view "_ALL_FILE_GROUP_VERSIONS"; drop view "_ALL_FILE_GROUP_FILES"; drop view "_ALL_FILE_GROUP_EXPORT_INFO"; drop view "_ALL_FILE_GROUP_TABLES"; drop view "_ALL_FILE_GROUP_TABLESPACES"; drop public synonym all_file_groups; drop public synonym all_file_group_versions; drop public synonym all_file_group_files; drop public synonym all_file_group_export_info; drop public synonym all_file_group_tables; drop public synonym all_file_group_tablespaces; drop view user_file_groups; drop view user_file_group_versions; drop view user_file_group_files; drop view user_file_group_export_info; drop view user_file_group_tables; drop view user_file_group_tablespaces; drop view "_USER_FILE_GROUPS"; drop public synonym user_file_groups; drop public synonym user_file_group_versions; drop public synonym user_file_group_files; drop public synonym user_file_group_export_info; drop public synonym user_file_group_tables; drop public synonym user_file_group_tablespaces; Rem END drop file group views Rem BEGIN drop recoverable script views drop view DBA_RECOVERABLE_SCRIPT_PARAMS; drop view DBA_RECOVERABLE_SCRIPT_BLOCKS; drop view DBA_RECOVERABLE_SCRIPT_ERRORS; drop view DBA_RECOVERABLE_SCRIPT; Rem END drop recoverable script views Rem BEGIN drop catalog views and synonyms for Transparent Data Encryption drop public synonym USER_ENCRYPTED_COLUMNS; drop view USER_ENCRYPTED_COLUMNS; drop public synonym ALL_ENCRYPTED_COLUMNS; drop view ALL_ENCRYPTED_COLUMNS; drop public synonym DBA_ENCRYPTED_COLUMNS; drop view DBA_ENCRYPTED_COLUMNS; Rem END Transparent Data Encryption Rem====================== Rem Begin sqltune Changes Rem====================== update wri$_adv_sqlt_statistics set direct_writes = NULL; -- -- in R1 the wri$_adv_sqlt_rtn_plan table had the same name as its constraint -- ALTER TABLE wri$_adv_sqlt_rtn_plan RENAME CONSTRAINT wri$_adv_sqlt_rtn_plan_pk TO wri$_adv_sqlt_rtn_plan / ALTER INDEX wri$_adv_sqlt_rtn_plan_pk RENAME TO wri$_adv_sqlt_rtn_plan / -- -- bug#5999827 - after upgrade sts could not be found -- -- Starting in 10.2 STS names are unique per-owner, where in 10.1 they were -- globally unique. So we need to remove attr3 from the object in 10.1. -- UPDATE wri$_adv_objects o SET attr3 = null WHERE type = 8 /* SQLSET */ and EXISTS (SELECT 1 FROM wri$_adv_tasks t WHERE t.id = o.task_id and t.advisor_id = 4); commit; Rem====================== Rem End sqltune Changes Rem====================== Rem drop the view so that there will be no references to dropped fixed table drop view dba_common_audit_trail; Rem drop view DBA_CONNECT_ROLE_GRANTEES for completeness drop view DBA_CONNECT_ROLE_GRANTEES; drop public synonym DBA_CONNECT_ROLE_GRANTEES; Rem========================== Rem DB Feature Usage Changes Rem========================== Rem Rem Truncate the new DB Feature Usage (WRI$_DBU) Tables and View Rem truncate table WRI$_DBU_CPU_USAGE; truncate table WRI$_DBU_CPU_USAGE_SAMPLE; drop view DBA_CPU_USAGE_STATISTICS; drop public SYNONYM DBA_CPU_USAGE_STATISTICS; Rem Rem Drop newly created DB Feature Usage report public synonym and package Rem drop public synonym DBMS_FEATURE_USAGE_REPORT; drop package DBMS_FEATURE_USAGE_REPORT; Rem============= Rem AWR Changes Rem============= -- Turn ON the event to disable the partition check alter session set events '14524 trace name context forever, level 1'; drop view DBA_HIST_INST_CACHE_TRANSFER; drop public synonym DBA_HIST_INST_CACHE_TRANSFER; drop view DBA_HIST_STREAMS_POOL_ADVICE; drop public synonym DBA_HIST_STREAMS_POOL_ADVICE; drop view DBA_HIST_COMP_IOSTAT; drop public synonym DBA_HIST_COMP_IOSTAT; drop view DBA_HIST_SGA_TARGET_ADVICE; drop public synonym DBA_HIST_SGA_TARGET_ADVICE; drop view DBA_HIST_SESS_TIME_STATS; drop public synonym DBA_HIST_SESS_TIME_STATS; drop view DBA_HIST_STREAMS_CAPTURE; drop public synonym DBA_HIST_STREAMS_CAPTURE; drop view DBA_HIST_STREAMS_APPLY_SUM; drop public synonym DBA_HIST_STREAMS_APPLY_SUM; drop view DBA_HIST_BUFFERED_QUEUES; drop public synonym DBA_HIST_BUFFERED_QUEUES; drop view DBA_HIST_BUFFERED_SUBSCRIBERS; drop public synonym DBA_HIST_BUFFERED_SUBSCRIBERS; drop view DBA_HIST_RULE_SET; drop public synonym DBA_HIST_RULE_SET; drop view DBA_HIST_PROCESS_MEM_SUMMARY; drop public synonym DBA_HIST_PROCESS_MEM_SUMMARY; drop view DBA_HIST_SQL_BIND_METADATA; drop public synonym DBA_HIST_SQL_BIND_METADATA; truncate table WRH$_INST_CACHE_TRANSFER; truncate table WRH$_INST_CACHE_TRANSFER_BL; truncate table WRH$_STREAMS_POOL_ADVICE; truncate table WRH$_COMP_IOSTAT; truncate table WRH$_SGA_TARGET_ADVICE; truncate table WRH$_SESS_TIME_STATS; truncate table WRH$_STREAMS_CAPTURE; truncate table WRH$_STREAMS_APPLY_SUM; truncate table WRH$_BUFFERED_QUEUES; truncate table WRH$_BUFFERED_SUBSCRIBERS; truncate table WRH$_RULE_SET; truncate table WRH$_PROCESS_MEMORY_SUMMARY; truncate table WRH$_SQL_BIND_METADATA; Rem === Rem Drop the new dbms_ash_internal package Rem === drop package dbms_ash_internal; -- Drop these AWR report types so they will be recreated when catalog is run drop type AWRRPT_ROW_TYPE force; drop type AWRRPT_NUM_ARY force; drop type AWRRPT_VCH_ARY force; drop type AWRRPT_CLB_ARY force; drop type AWRRPT_TEXT_TYPE_TABLE force; drop type AWRRPT_TEXT_TYPE force; drop type AWRRPT_HTML_TYPE_TABLE force; drop type AWRRPT_HTML_TYPE force; drop type AWRDRPT_TEXT_TYPE_TABLE force; drop type AWRDRPT_TEXT_TYPE force; drop type AWRSQRPT_TEXT_TYPE_TABLE force; drop type AWRSQRPT_TEXT_TYPE force; Rem Rem Recreate tables dropped in 10gR2 Rem create table WRH$_SQLBIND (snap_id NUMBER NOT NULL ,dbid NUMBER NOT NULL ,instance_number NUMBER NOT NULL ,sql_id VARCHAR2(13) NOT NULL ,child_number NUMBER ,name VARCHAR2(30) ,position NUMBER ,dup_position NUMBER ,datatype NUMBER ,datatype_string VARCHAR2(15) ,character_sid NUMBER ,precision NUMBER ,scale NUMBER ,max_length NUMBER ,was_captured VARCHAR2(3) ,last_captured DATE ,value_string VARCHAR2(4000) ,value_anydata anydata ,constraint WRH$_SQLBIND_PK primary key (dbid, snap_id, instance_number, sql_id, position) using index local tablespace SYSAUX ) partition by range (dbid, snap_id) (partition WRH$_SQLBIND_MXDB_MXSN values less than (MAXVALUE, MAXVALUE) tablespace SYSAUX) pctfree 1 enable row movement / create table WRH$_SQLBIND_BL (snap_id NUMBER NOT NULL ,dbid NUMBER NOT NULL ,instance_number NUMBER NOT NULL ,sql_id VARCHAR2(13) NOT NULL ,child_number NUMBER ,name VARCHAR2(30) ,position NUMBER ,dup_position NUMBER ,datatype NUMBER ,datatype_string VARCHAR2(15) ,character_sid NUMBER ,precision NUMBER ,scale NUMBER ,max_length NUMBER ,was_captured VARCHAR2(3) ,last_captured DATE ,value_string VARCHAR2(4000) ,value_anydata anydata ,constraint WRH$_SQLBIND_BL_PK primary key (dbid, snap_id, instance_number, sql_id, position) using index tablespace SYSAUX ) tablespace SYSAUX / create table WRH$_CLASS_CACHE_TRANSFER (snap_id number not null ,dbid number not null ,instance_number number not null ,class varchar2(18) not null ,cr_transfer number ,current_transfer number ,x_2_null number ,x_2_null_forced_write number ,x_2_null_forced_stale number ,x_2_s number ,x_2_s_forced_write number ,s_2_null number ,s_2_null_forced_stale number ,null_2_x number ,s_2_x number ,null_2_s number ,constraint WRH$_CLASS_CACHE_TRANSFER_PK primary key (snap_id, dbid, instance_number, class) using index local tablespace SYSAUX ) partition by range (dbid, snap_id) (partition WRH$_CLASS_CACH_MXDB_MXSN values less than (MAXVALUE, MAXVALUE) tablespace SYSAUX) enable row movement / create table WRH$_CLASS_CACHE_TRANSFER_BL (snap_id number not null ,dbid number not null ,instance_number number not null ,class varchar2(18) not null ,cr_transfer number ,current_transfer number ,x_2_null number ,x_2_null_forced_write number ,x_2_null_forced_stale number ,x_2_s number ,x_2_s_forced_write number ,s_2_null number ,s_2_null_forced_stale number ,null_2_x number ,s_2_x number ,null_2_s number ,constraint WRH$_CLASS_CACHE_TRANS_BL_PK primary key (snap_id, dbid, instance_number, class) using index tablespace SYSAUX ) tablespace SYSAUX / Rem Rem Remove the indexes created in 10gR2 Rem for the Metrics Tables Rem drop index WRH$_SYSMETRIC_HISTORY_INDEX; drop index WRH$_SYSMETRIC_SUMMARY_INDEX; drop index WRH$_SESSMETRIC_HISTORY_INDEX; drop index WRH$_FILEMETRIC_HISTORY_INDEX; drop index WRH$_WAITCLASSMETRIC_HIST_IND; Rem Rem WRH$_SQLSTAT changes Rem -- set the new columns in WRH$_SQLSTAT to NULL update WRH$_SQLSTAT set PX_SERVERS_EXECS_TOTAL = NULL; update WRH$_SQLSTAT set PX_SERVERS_EXECS_DELTA = NULL; update WRH$_SQLSTAT set FORCE_MATCHING_SIGNATURE = NULL; update WRH$_SQLSTAT set PARSING_SCHEMA_NAME = NULL; update WRH$_SQLSTAT set BIND_DATA = NULL; update WRH$_SQLSTAT set FLAG = NULL; update WRH$_SQLSTAT_BL set PX_SERVERS_EXECS_TOTAL = NULL; update WRH$_SQLSTAT_BL set PX_SERVERS_EXECS_DELTA = NULL; update WRH$_SQLSTAT_BL set FORCE_MATCHING_SIGNATURE = NULL; update WRH$_SQLSTAT_BL set PARSING_SCHEMA_NAME = NULL; update WRH$_SQLSTAT_BL set BIND_DATA = NULL; update WRH$_SQLSTAT_BL set FLAG = NULL; commit; Rem Rem WRH$_SEG_STAT changes Rem -- set the new column values to NULL update WRH$_SEG_STAT set GC_BUFFER_BUSY_TOTAL = NULL; update WRH$_SEG_STAT set GC_BUFFER_BUSY_DELTA = NULL; update WRH$_SEG_STAT set CHAIN_ROW_EXCESS_TOTAL = null; update WRH$_SEG_STAT set CHAIN_ROW_EXCESS_DELTA = null; update WRH$_SEG_STAT_BL set GC_BUFFER_BUSY_TOTAL = NULL; update WRH$_SEG_STAT_BL set GC_BUFFER_BUSY_DELTA = NULL; update WRH$_SEG_STAT_BL set CHAIN_ROW_EXCESS_DELTA = null; update WRH$_SEG_STAT_BL set CHAIN_ROW_EXCESS_TOTAL = null; commit; -- rename columns to previous values alter table WRH$_SEG_STAT rename column GC_CR_BLOCKS_RECEIVED_TOTAL to GC_CR_BLOCKS_SERVED_TOTAL; alter table WRH$_SEG_STAT rename column GC_CR_BLOCKS_RECEIVED_DELTA to GC_CR_BLOCKS_SERVED_DELTA; alter table WRH$_SEG_STAT rename column GC_CU_BLOCKS_RECEIVED_TOTAL to GC_CU_BLOCKS_SERVED_TOTAL; alter table WRH$_SEG_STAT rename column GC_CU_BLOCKS_RECEIVED_DELTA to GC_CU_BLOCKS_SERVED_DELTA; alter table WRH$_SEG_STAT_BL rename column GC_CR_BLOCKS_RECEIVED_TOTAL to GC_CR_BLOCKS_SERVED_TOTAL; alter table WRH$_SEG_STAT_BL rename column GC_CR_BLOCKS_RECEIVED_DELTA to GC_CR_BLOCKS_SERVED_DELTA; alter table WRH$_SEG_STAT_BL rename column GC_CU_BLOCKS_RECEIVED_TOTAL to GC_CU_BLOCKS_SERVED_TOTAL; alter table WRH$_SEG_STAT_BL rename column GC_CU_BLOCKS_RECEIVED_DELTA to GC_CU_BLOCKS_SERVED_DELTA; Rem Rem WRH$_SEG_STAT_OBJ changes Rem Rem Set new columns in WRH$_SEG_STAT_OBJ to NULL update WRH$_SEG_STAT_OBJ set index_type = NULL, base_obj# = NULL, base_object_name = NULL, base_object_owner = NULL; Rem Rem WRH$_SQL_PLAN changes Rem -- clear new other_xml column in wrh$_sql_plan update sys.wrh$_sql_plan set other_xml = null; commit; update sys.wrh$_sql_plan set timestamp = null; commit; Rem Rem WRH$_UNDOSTAT changes Rem update WRH$_UNDOSTAT set status = 0; update WRH$_UNDOSTAT set spcprs_retention = 0; update WRH$_UNDOSTAT set runawayquerysqlid = NULL; commit; Rem Rem WRH$_EVENT_NAME changes Rem update WRH$_EVENT_NAME set PARAMETER1 = NULL; update WRH$_EVENT_NAME set PARAMETER2 = NULL; update WRH$_EVENT_NAME set PARAMETER3 = NULL; commit; Rem Rem WRH$_ACTIVE_SESSION_HISTORY changes Rem - NULL out the 10gR2 new columns in ASH Rem update WRH$_ACTIVE_SESSION_HISTORY set force_matching_signature = NULL, blocking_session = NULL, blocking_session_serial# = NULL, xid = NULL; update WRH$_ACTIVE_SESSION_HISTORY_BL set force_matching_signature = NULL, blocking_session = NULL, blocking_session_serial# = NULL, xid = NULL; commit; Rem Rem WRM$_WR_CONTROL changes Rem - Set the new Top N SQL column to NULL Rem update WRM$_WR_CONTROL set TOPNSQL = NULL; commit; Rem ======================================================= Rem == Update the SWRF_VERSION to the current version. == Rem == (10gR1 = SWRF Version 1) == Rem == This step must be the last step for the AWR == Rem == downgrade changes. Place all other AWR == Rem == downgrade changes above this. == Rem ======================================================= BEGIN UPDATE wrm$_wr_control SET swrf_version = 1; COMMIT; EXCEPTION WHEN OTHERS THEN IF (SQLCODE = -942) THEN NULL; ELSE RAISE; END IF; END; / -- Turn OFF the event to disable the partition check alter session set events '14524 trace name context off'; Rem Rem BEGIN of dropping STREAMS views Rem drop public synonym DBA_STREAMS_TRANSFORMATIONS; drop view DBA_STREAMS_TRANSFORMATIONS; drop public synonym DBA_STREAMS_DELETE_COLUMN; drop view DBA_STREAMS_DELETE_COLUMN; drop public synonym DBA_STREAMS_RENAME_COLUMN; drop view DBA_STREAMS_RENAME_COLUMN; drop public synonym DBA_STREAMS_ADD_COLUMN; drop view DBA_STREAMS_ADD_COLUMN; drop public synonym DBA_STREAMS_RENAME_TABLE; drop view DBA_STREAMS_RENAME_TABLE; drop public synonym DBA_STREAMS_RENAME_SCHEMA; drop view DBA_STREAMS_RENAME_SCHEMA; DROP VIEW "_DBA_STREAMS_TRANSFORMATIONS"; DROP VIEW "_DBA_STREAMS_TRANSFM_FUNCTION"; DROP VIEW "_DBA_STREAMS_NEWLY_SUPTED_10_2"; drop public synonym DBA_APPLY_VALUE_DEPENDENCIES; drop public synonym DBA_APPLY_OBJECT_DEPENDENCIES; Rem Rem END of dropping STREAMS views Rem Rem Drop change Notification Views drop public synonym DBA_CHANGE_NOTIFICATION_REGS; drop view DBA_CHANGE_NOTIFICATION_REGS; drop public synonym USER_CHANGE_NOTIFICATION_REGS; drop view USER_CHANGE_NOTIFICATION_REGS; Rem === Rem NULL out the 10gR2 new columns in ASH Rem === update WRH$_ACTIVE_SESSION_HISTORY set blocking_session = NULL, xid = NULL; update WRH$_ACTIVE_SESSION_HISTORY_BL set blocking_session = NULL, xid = NULL; commit; Rem Drop TSM user DROP USER TSMSYS CASCADE; Rem========================================================================= Rem Drop all new types here Rem========================================================================= Rem Rem BEGIN of dropping STREAMS types Rem -- The following type must be conditionally dropped because it will -- invalidate dbms_streams_adm, which e0902000.sql relies upon. Thus -- if the version we upgraded from is 9.2, do not drop this type DECLARE previous_version varchar2(30); BEGIN SELECT prv_version INTO previous_version FROM registry$ WHERE cid = 'CATPROC'; IF previous_version NOT LIKE '9.2.0%' THEN execute immediate 'drop type sys.streams$transformation_info force'; END IF; END; / DROP TYPE streams$_anydata_array FORCE / Rem Rem END of dropping STREAMS types Rem -- Drop MV refresh index rebuild types DROP TYPE SYS.IndexRebuildList FORCE; DROP TYPE SYS.IndexRebuildRecord FORCE; Rem Rem Rules changes Rem drop type sys.re$rule_list force / Rem Rem AQ changes Rem ALTER TYPE sys.aq$_reg_info DROP CONSTRUCTOR FUNCTION aq$_reg_info( name VARCHAR2, namespace NUMBER, callback VARCHAR2, context RAW, qosflags NUMBER, timeout NUMBER) RETURN SELF AS RESULT CASCADE / ALTER TYPE sys.aq$_reg_info DROP CONSTRUCTOR FUNCTION aq$_reg_info( name VARCHAR2, namespace NUMBER, callback VARCHAR2, context RAW, anyctx SYS.ANYDATA, ctxtype NUMBER) RETURN SELF AS RESULT CASCADE / ALTER TYPE sys.aq$_reg_info DROP ATTRIBUTE (qosflags, payloadcbk, timeout) CASCADE / -- drop attribute from sys.aq$_descriptor and then drop the type ALTER TYPE sys.aq$_descriptor DROP ATTRIBUTE(gen_desc) CASCADE / DROP TYPE sys.aq$_ntfn_descriptor force; ALTER TYPE sys.msg_prop_t DROP ATTRIBUTE (delivery_mode) CASCADE / ALTER TYPE sys.aq$_srvntfn_message DROP ATTRIBUTE (delivery_mode, ntfn_flags) CASCADE / Rem Avoid ORA-04068 for DBMS_AQADM_SYS execute DBMS_SESSION.RESET_PACKAGE; -- Drop change notification types; DROP TYPE sys.chnf$_reg_info_oc4j FORCE; DROP TYPE sys.chnf$_reg_info FORCE; DROP TYPE chnf$_desc FORCE; DROP TYPE chnf$_tdesc_array FORCE; DROP TYPE chnf$_tdesc FORCE; DROP TYPE chnf$_rdesc_array FORCE; DROP TYPE chnf$_rdesc FORCE; Rem========================================================================= Rem ALTER types to drop 10.2 attributes, methods here Rem========================================================================= Rem Rem Alter types and associated functions related to lcr$_{row,ddl}_record Rem ALTER TYPE lcr$_row_record DROP MEMBER FUNCTION get_source_time RETURN DATE CASCADE; ALTER TYPE lcr$_ddl_record DROP MEMBER FUNCTION get_source_time RETURN DATE CASCADE; Rem========================================================================= Rem Drop all new packages here Rem========================================================================= Rem Drop dbms_errlog package drop package sys.dbms_errlog; drop public synonym dbms_errlog; Rem Drop utl_nla package drop library sys.utl_mat_lib; drop package sys.utl_nla; drop type SYS.UTL_NLA_ARRAY_DBL; drop type SYS.UTL_NLA_ARRAY_FLT; drop type SYS.UTL_NLA_ARRAY_INT; Rem Rem Drop dbms_tsm package Rem drop library sys.dbms_tsm_lib; drop package sys.dbms_tsm; drop package sys.dbms_tsm_prvt; Rem Rem Drop dbms_extended_tts_checks_lib Rem drop library sys.dbms_extended_tts_checks_lib; Rem Drop dbms_space (reference to new fixed table) drop package dbms_space; Rem Rem Drop tde_crypto_tookit_library Rem drop library tde_library; drop package dbms_tde_toolkit; drop package dbms_tde_toolkit_ffi; drop public synonym dbms_tde_toolkit; Rem Drop dbms_sqlhash package drop package sys.dbms_sqlhash; drop public synonym dbms_sqlhash; Rem Drop dbms_sqlplus_script package drop library sys.dbms_sqlplus_script_lib; drop package sys.dbms_sqlplus_script; drop public synonym dbms_sqlplus_script; Rem ======================================================================== Rem Downgrade PL/SQL compiler parameters Rem ======================================================================== DELETE FROM settings$ WHERE param IN ('plsql_ccflags'); commit; REm ============================================================== REm Drop dblink stuff - project 5523 REm ============================================================= DROP VIEW exu10lnku; DROP VIEW exu10lnk; Rem Drop dbms_dblink drop library sys.dbms_dblink_lib; drop package sys.dbms_dblink; REM =============================================== REM End dblink REM =============================================== DROP LIBRARY dbms_file_group_lib; DROP PACKAGE dbms_file_group; DROP PUBLIC SYNONYM dbms_file_group; DROP PACKAGE dbms_file_group_utl; DROP PACKAGE dbms_file_group_utl_invok; DROP PACKAGE dbms_file_group_internal_invok; DROP PACKAGE dbms_file_group_decl; DROP PACKAGE dbms_file_group_exp; DROP PACKAGE dbms_file_group_exp_internal; DROP PACKAGE dbms_file_group_imp; DROP PACKAGE dbms_file_group_imp_internal; -- drop file group sequence DROP SEQUENCE fgr$_names_s; Rem END dropping file group packages Rem BEGIN dropping recoverable scripts packages DROP PACKAGE dbms_reco_script_int; DROP PACKAGE dbms_reco_script_invok; DROP PACKAGE dbms_recoverable_script; Rem END dropping recoverable scripts packages Rem BEGIN dropping commit-time queue packages DROP PACKAGE dbms_aq_exp_cmt_time_tables; DROP PUBLIC SYNONYM dbms_aq_exp_cmt_time_tables; Rem END dropping commit-time queue packages Rem drop dbms_tdb package DROP PACKAGE dbms_tdb; Rem drop server alerts private package and library DROP PACKAGE dbms_server_alert_prvt; DROP LIBRARY dbms_svralrt_prvt_lib; Rem drop dbms_ha_alerts packages and library DROP PACKAGE dbms_ha_alerts; DROP PACKAGE dbms_ha_alerts_prvt; DROP LIBRARY dbms_ha_alert_lib; Rem BEGIN drop ha event notification objects DROP LIBRARY dbms_haeventnot_prvt_lib; DROP FUNCTION haen_txfm_text; Rem END drop ha event notification objects -- drop DBMS_APPLY_USER_AGENT -- note this is an internal package no synonym DROP PACKAGE DBMS_APPLY_USER_AGENT; Rem drop dbms_change_notification DROP PACKAGE dbms_change_notification; DROP PUBLIC SYNONYM dbms_change_notification; Rem========================================================================= Rem Revert 10.2 changes to SYSTEM dictionary tables here Rem========================================================================= Rem Rem Logminer Downgrade Rem Rem Update session attributes for logical standby sessions. update system.logmnr_session$ set session_attr = session_attr - 4 where client# = 2 and bitand(session_attr, 4) = 4; commit; drop table system.logmnr_parameter$ purge; Rem -------------------------- Rem Begin STS schema changes Rem -------------------------- -- -- Change back to our old SQL tuning set schema. This is done in two steps: -- 1. Rebuild the R1 format of the _statements and _binds tables from their -- R2 equivalents. We convert from parsing schema names back to IDs along -- the way, and then drop the R2 tables -- 2. Normilze sql tuning set names -- 3. Drop sql tuning set types -- 4. Drop the old _plans, _mask, and _statistics tables -- 5. Drop the old sequences -- 6. drop all new sql tuning set synonyms and views -- 7. Drop statements, binds views (they are invalid now) -- 8. drop the table and index created for sqlset workspace -- Rem =========================================================== Rem 1. Rebuild R1 versions of the _statements and _binds tables Rem =========================================================== -- We will rename these tables and indexes/constraints when we're done create table wri$_sqlset_statements_tmp ( sqlset_id NUMBER NOT NULL, sql_id VARCHAR(13) NOT NULL, parsing_schema_id NUMBER, module VARCHAR2(48), action VARCHAR2(32), elapsed_time NUMBER, cpu_time NUMBER, buffer_gets NUMBER, disk_reads NUMBER, rows_processed NUMBER, fetches NUMBER, executions NUMBER, end_of_fetch_count NUMBER, optimizer_cost NUMBER, optimizer_env RAW(1000), priority NUMBER, command_type NUMBER, stat_period NUMBER, active_stat_period NUMBER, constraint wri$_sqlset_statements_tmp_pk primary key (sqlset_id, sql_id) using INDEX tablespace SYSAUX ) tablespace SYSAUX / create table wri$_sqlset_binds_tmp ( sqlset_id NUMBER NOT NULL, sql_id VARCHAR(13) NOT NULL, position NUMBER NOT NULL, VALUE ANYDATA, constraint wri$_sqlset_binds_tmp_pk primary key (sqlset_id, sql_id, position) using INDEX tablespace SYSAUX ) tablespace SYSAUX / -- -- A. Copy the binds We do the actual merge/copy in a PL/SQL block to -- avoid doing it twice. Notice please that if we fail in the following -- block (probably because of errors in the dbms_sqltune package after -- invalidating it by for example dropping one of the tables or views it -- is using) the binds (i.e., bind_data) will not be converted. This is -- a worse case where sql tuning sets will be downgraded without any bind -- information. -- DECLARE already_r1 NUMBER; BEGIN already_r1 := 0; -- Migration already done? Check to see if binds table still has old -- sql_id column. select DECODE(count(*), 0, 0, 1) into already_r1 from dba_tab_columns where owner = 'SYS' and table_name = 'WRI$_SQLSET_BINDS' and column_name = 'SQL_ID'; IF (already_r1 = 0) THEN -- Move the binds from _binds to _binds_tmp -- We just take the binds from one hash value -- 1. move bind_data first -- DO NOT ADD APPEND hint to the two following queries otherwise you -- will get error ORA-12838. EXECUTE IMMEDIATE 'INSERT INTO wri$_sqlset_binds_tmp ' || 'SELECT stmt.sqlset_id sqlset_id, stmt.sql_id sql_id, ' || ' b.position position, b.value_anydata as value ' || 'FROM wri$_sqlset_statements stmt, ' || ' wri$_sqlset_plans p, ' || ' TABLE(dbms_sqltune_util0.extract_binds(p.bind_data)) b ' || 'WHERE stmt.id = p.stmt_id AND p.bind_data IS NOT NULL AND ' || ' p.plan_hash_value = ' || ' (SELECT min(plan_hash_value) ' || ' FROM wri$_sqlset_plans p_2 ' || ' WHERE p.stmt_id = p_2.stmt_id) '; -- 2. move the user sepecified binds for the reset of statements if any EXECUTE IMMEDIATE 'INSERT INTO wri$_sqlset_binds_tmp ' || 'SELECT s.sqlset_id sqlset_id, s.sql_id sql_id, ' || ' b_1.position position, b_1.value value ' || 'FROM wri$_sqlset_statements s, wri$_sqlset_binds b_1 ' || 'WHERE s.id = b_1.stmt_id AND ' || ' b_1.plan_hash_value = ' || ' (SELECT min(plan_hash_value) ' || ' FROM wri$_sqlset_binds b_2 ' || ' WHERE b_1.stmt_id = b_2.stmt_id) AND ' || ' NOT EXISTS ( ' || ' SELECT 1 ' || ' FROM wri$_sqlset_binds_tmp t ' || ' WHERE t.sqlset_id = s.sqlset_id and s.sql_id = t.sql_id)'; COMMIT; END IF; END; / -- -- b. Copy statements and stats. We do the actual merge/copy in a PL/SQL block -- to avoid doing it twice -- DECLARE already_r1 NUMBER; BEGIN already_r1 := 0; -- Migration already done? Check to see if statements table still has old -- buffer_gets column. select DECODE(count(*), 0, 0, 1) into already_r1 from dba_tab_columns where owner = 'SYS' and table_name = 'WRI$_SQLSET_STATEMENTS' and column_name = 'BUFFER_GETS'; IF (already_r1 = 1) THEN -- Migration is done. Drop the temp tables we just created and continue EXECUTE IMMEDIATE 'DROP TABLE WRI$_SQLSET_STATEMENTS_TMP'; EXECUTE IMMEDIATE 'DROP TABLE WRI$_SQLSET_BINDS_TMP'; ELSE -- Merge the _statistics, _plans, _statements, and _mask tables into the -- _statements_tmp table. We sum statistics over all their plans. -- -- Note that we do an outer join in the DBA_USERS view so that if a -- statement has a parsing schema name that does not join it will still -- get a row in the R1 version of the table EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO wri$_sqlset_statements_tmp ' || 'SELECT stmt.sqlset_id sqlset_id, stmt.sql_id sql_id, ' || ' min(u.user_id) parsing_schema_id, min(stmt.module) module, ' || ' min(stmt.action) action, sum(stats.elapsed_time) elapsed_time, '|| ' sum(stats.cpu_time) cpu_time, ' || ' sum(stats.buffer_gets) buffer_gets, ' || ' sum(stats.disk_reads) disk_reads, ' || ' sum(stats.rows_processed) rows_processed, ' || ' sum(stats.fetches) fetches, sum(stats.executions) executions, ' || ' sum(stats.end_of_fetch_count) end_of_fetch_count, ' || ' sum(stats.optimizer_cost) optimizer_cost, ' || ' min(plans.optimizer_env) optimizer_env, ' || ' min(mask.priority) priority, ' || ' min(stmt.command_type) command_type, ' || ' min(stats.stat_period) stat_period, ' || ' min(stats.active_stat_period) active_stat_period ' || 'FROM wri$_sqlset_statements stmt, wri$_sqlset_statistics stats, ' || ' wri$_sqlset_mask mask, wri$_sqlset_plans plans, dba_users u ' || 'WHERE stmt.id = stats.stmt_id and stmt.id = mask.stmt_id and ' || ' stmt.id = plans.stmt_id and ' || ' plans.plan_hash_value = stats.plan_hash_value and ' || ' plans.plan_hash_value = mask.plan_hash_value and ' || ' u.username (+) = stmt.parsing_schema_name ' || 'GROUP BY stmt.sqlset_id, stmt.sql_id'; COMMIT; -- Drop the R2 _statements and _binds tables EXECUTE IMMEDIATE 'DROP TABLE wri$_sqlset_statements'; EXECUTE IMMEDIATE 'DROP TABLE wri$_sqlset_binds'; -- Rename _statements_tmp and _binds_tmp tables and constraints EXECUTE IMMEDIATE 'ALTER TABLE wri$_sqlset_statements_tmp RENAME TO ' || 'wri$_sqlset_statements'; EXECUTE IMMEDIATE 'ALTER TABLE wri$_sqlset_statements RENAME ' || 'CONSTRAINT wri$_sqlset_statements_tmp_pk TO ' || 'wri$_sqlset_statements_pk'; EXECUTE IMMEDIATE 'ALTER INDEX wri$_sqlset_statements_tmp_pk RENAME TO ' || 'wri$_sqlset_statements_pk'; -- Binds table EXECUTE IMMEDIATE 'ALTER TABLE wri$_sqlset_binds_tmp RENAME TO ' || 'wri$_sqlset_binds'; EXECUTE IMMEDIATE 'ALTER TABLE wri$_sqlset_binds RENAME ' || 'CONSTRAINT wri$_sqlset_binds_tmp_pk TO ' || 'wri$_sqlset_binds_pk'; EXECUTE IMMEDIATE 'ALTER INDEX wri$_sqlset_binds_tmp_pk RENAME TO ' || 'wri$_sqlset_binds_pk'; END IF; END; / Rem ===================================================================== Rem 2. Normalize sql tuning set names Rem in R2, wri$_sqlset_definitions stores owner names case-sensitively Rem re-set all names to be stored case-insenstively Rem ===================================================================== BEGIN EXECUTE IMMEDIATE 'update wri$_sqlset_definitions set owner = upper(owner)'; EXCEPTION WHEN OTHERS THEN IF (SQLCODE = -942) THEN NULL; ELSE RAISE; END IF; END; / -- in R2 sql tuning sets are unique by (name,owner) but in R1 they are just -- unique by (name) -- New index will be created in the catproc drop index wri$_sqlset_definitions_idx_01 / -- Patch names of SQL tuning sets -- If in R2 two STS had the same name, in R1 they must have different names DECLARE -- all sts names we have to rename CURSOR r2_names IS select name, owner from wri$_sqlset_definitions ds where (select count(*) from wri$_sqlset_definitions ds2 where ds2.name = ds.name) > 1; cnt NUMBER; postfix NUMBER; new_name VARCHAR2(30); old_name VARCHAR2(30); old_owner VARCHAR2(30); BEGIN FOR name_rec IN r2_names LOOP old_name := name_rec.name; old_owner := name_rec.owner; new_name := SUBSTRB(name_rec.name,1,9) || '_' || SUBSTRB(name_rec.owner,1,9); postfix := 0; -- Try several possibilities for remapping name LOOP SELECT count(*) INTO cnt FROM wri$_sqlset_definitions WHERE name = new_name; EXIT WHEN (cnt = 0); postfix := postfix + 1; new_name := SUBSTRB(name_rec.name,1,9) || '_' || SUBSTRB(name_rec.owner,1,9) || '_' || postfix; END LOOP; UPDATE wri$_sqlset_definitions SET name = new_name WHERE name = old_name AND owner = old_owner; END LOOP; commit; END; / Rem =========================================================== Rem 3. SQL Tuning Set type changes Rem Notice that sql tuning set are droped in this place Rem Instead of the type change type section beacause Rem we need these types do downgrade the bind values Rem bind_data (RAW) into bind_list (list of bind values) Rem =========================================================== -- We now store plans in the SQL tuning set, which means they are also -- in the SQLSET_ROW. We drop the type here and recreate it later in catsqlt. DROP TYPE sqlset_row FORCE / DROP TYPE sqlset FORCE / DROP PUBLIC SYNONYM sqlset_row / DROP PUBLIC SYNONYM sqlset / -- sqltune/sqlset new bind (single and set) types DROP TYPE sql_bind_set / DROP PUBLIC SYNONYM sql_bind_set / DROP TYPE sql_bind / DROP PUBLIC SYNONYM sql_bind / -- -- Plan types -- NOTE: sql_plan_allstat_row_type is new to 11g. We drop it here because -- for downgrades from 11g->10g and beyond, we could not drop it in -- e1002000 for fear of invalidating dbms_sqltune -- DROP TYPE sql_plan_table_type FORCE / DROP PUBLIC SYNONYM sql_plan_table_type / DROP TYPE sql_plan_stat_row_type FORCE / DROP PUBLIC SYNONYM sql_plan_stat_row_type / DROP TYPE sql_plan_allstat_row_type FORCE / DROP PUBLIC SYNONYM sql_plan_allstat_row_type / DROP TYPE sql_plan_row_type FORCE / DROP PUBLIC SYNONYM sql_plan_row_type / Rem ================================================= Rem 4. Drop the _plan_, _mask, and _statistics tables Rem ================================================= DROP TABLE wri$_sqlset_plan_lines / DROP TABLE wri$_sqlset_plans / DROP TABLE wri$_sqlset_mask / DROP TABLE wri$_sqlset_statistics / Rem ================= Rem 5. drop sequences Rem ================= DROP SEQUENCE wri$_sqlset_stmt_id_seq / Rem ================================================= Rem 6. drop all new sql tuning set synonyms and views Rem ================================================= drop public synonym dba_sqlset_plans; drop public synonym user_sqlset_plans; drop public synonym all_sqlset; drop public synonym all_sqlset_statements; drop public synonym all_sqlset_references; drop public synonym all_sqlset_binds; drop public synonym all_sqlset_plans; drop public synonym "_ALL_SQLSET_STATEMENTS_ONLY"; drop public synonym "_ALL_SQLSET_STATEMENTS_PHV"; drop view dba_sqlset_plans; drop view user_sqlset_plans; drop view all_sqlset; drop view all_sqlset_statements; drop view all_sqlset_references; drop view all_sqlset_binds; drop view all_sqlset_plans; drop view "_ALL_SQLSET_STATEMENTS_ONLY"; drop view "_ALL_SQLSET_STATEMENTS_PHV"; drop view "_ALL_SQLSET_STATISTICS_ONLY"; Rem ====================================================== Rem 7. Drop statements, binds views (they are invalid now) Rem======================================================= drop public synonym user_sqlset_statements; drop public synonym user_sqlset_binds; drop public synonym dba_sqlset_statements; drop public synonym dba_sqlset_binds; DROP VIEW dba_sqlset_binds / DROP VIEW user_sqlset_binds / DROP VIEW dba_sqlset_statements / DROP VIEW user_sqlset_binds / DROP VIEW user_sqlset_statements / Rem ======================================================== Rem 8. drop the table and index created for sqlset workspace Rem ======================================================== DROP TABLE wri$_sqlset_workspace / Rem ======================================================== Rem 9. drop the temp table we use in capture Rem ======================================================== DROP TABLE wri$_sqlset_plans_tocap / Rem -------------------------- Rem End STS schema changes Rem -------------------------- Rem -------------------------- Rem Begin Outline changes Rem -------------------------- revoke create session from outln; grant connect to outln; Rem ================================================== Rem drop the dbms_sqltune_util0. This is needed only if we are downgrading Rem from an 11g release or higher. The following command should fail is we Rem are downgrading directly from 10gR2 to 10gR1 because the package is Rem created in 11g. Rem ================================================== DROP PACKAGE DBMS_SQLTUNE_UTIL0; Rem -------------------------- Rem End Outline changes Rem -------------------------- begin execute immediate 'drop index system.logstdby$events_ind'; exception when others then if sqlcode = -1418 then null; else raise; end if; end; / -- change the datatype of date column in logstdby$events alter table system.logstdby$events modify (event_time date); -- nullify new columns in logstdby$apply_milestone update system.logstdby$apply_milestone set commit_time = NULL; update system.logstdby$apply_milestone set processed_time = NULL; -- nullify new column in logstdby$apply_progress update system.logstdby$apply_progress set commit_time = NULL; commit; Rem========================================================================= Rem downgrade rules engine objects Rem========================================================================= update rule$ set uactx_client = NULL; commit; Rem========================================================================= Rem Begin Scheduler Downgrade Rem========================================================================= drop type scheduler$_event_info; drop sequence sys.scheduler$_evtseq; Rem NULL out the two new columns of the global attribute table update sys.scheduler$_global_attribute set attr_tstamp = NULL, attr_intv = NULL; -- Drop new 10gR2 Scheduler types. Dropping these types will invalidate -- DBMS_SCHEDULER so for downgrade to 9.2 do them later. Also do not call -- dbms_scheduler after this. DECLARE previous_version varchar2(30); BEGIN SELECT prv_version INTO previous_version FROM registry$ WHERE cid = 'CATPROC'; IF previous_version NOT LIKE '9.2.0%' THEN EXECUTE IMMEDIATE 'DROP TYPE sys.scheduler$_int_array_type FORCE'; EXECUTE IMMEDIATE 'DROP TYPE sys.scheduler$_chain_link_list FORCE'; EXECUTE IMMEDIATE 'DROP TYPE sys.scheduler$_chain_link FORCE'; EXECUTE IMMEDIATE 'DROP TYPE sys.scheduler$_step_type_list FORCE'; EXECUTE IMMEDIATE 'DROP TYPE sys.scheduler$_step_type FORCE'; EXECUTE IMMEDIATE 'DROP TYPE sys.scheduler$_rule_list FORCE'; EXECUTE IMMEDIATE 'DROP TYPE sys.scheduler$_rule FORCE'; -- this type has changed in 10gR2, drop it so catsch.sql will recreate it EXECUTE IMMEDIATE 'DROP TYPE sys.scheduler$_job_step_type FORCE'; EXECUTE IMMEDIATE 'DROP VIEW dba_scheduler_chains'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM dba_scheduler_chains'; EXECUTE IMMEDIATE 'DROP VIEW user_scheduler_chains'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM user_scheduler_chains'; EXECUTE IMMEDIATE 'DROP VIEW all_scheduler_chains'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM all_scheduler_chains'; EXECUTE IMMEDIATE 'DROP VIEW dba_scheduler_chain_rules'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM dba_scheduler_chain_rules'; EXECUTE IMMEDIATE 'DROP VIEW user_scheduler_chain_rules'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM user_scheduler_chain_rules'; EXECUTE IMMEDIATE 'DROP VIEW all_scheduler_chain_rules'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM all_scheduler_chain_rules'; EXECUTE IMMEDIATE 'DROP VIEW dba_scheduler_chain_steps'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM dba_scheduler_chain_steps'; EXECUTE IMMEDIATE 'DROP VIEW user_scheduler_chain_steps'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM user_scheduler_chain_steps'; EXECUTE IMMEDIATE 'DROP VIEW all_scheduler_chain_steps'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM all_scheduler_chain_steps'; EXECUTE IMMEDIATE 'DROP VIEW dba_scheduler_running_chains'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM dba_scheduler_running_chains'; EXECUTE IMMEDIATE 'DROP VIEW user_scheduler_running_chains'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM user_scheduler_running_chains'; EXECUTE IMMEDIATE 'DROP VIEW all_scheduler_running_chains'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM all_scheduler_running_chains'; END IF; END; / -- Job run details table column cpu_used datatype changed in 10R2 from number -- to interval day second. Here we change it back to number. -- Note this is an exception to our backward compatibillity policies update scheduler$_job_run_details set cpu_used = null; commit; -- modifed in execute immediate later in script -- remove chains export support DROP PACKAGE dbms_sched_chain_export; DROP PUBLIC SYNONYM dbms_sched_chain_export; -- create tables no longer created in 10gR2 CREATE TABLE sys.scheduler$_job_chain ( obj# number NOT NULL /* job chain identifier */ CONSTRAINT scheduler$_job_chain_pk PRIMARY KEY, rule_set varchar2(30), /* rule set assoc with this chain */ rule_set_owner varchar2(30), /* schema of the rule set */ comments varchar2(240), /* schedule comments */ flags number /* schedule type */ ) / CREATE TABLE sys.scheduler$_chain_varlist ( oid number NOT NULL, /* job chain identifier */ var_name varchar2(30) NOT NULL, /* job identifier */ program_name varchar2(98) NOT NULL ) / CREATE INDEX sys.i_scheduler_chain_varlist1 ON sys.scheduler$_chain_varlist (oid) / CREATE TABLE sys.scheduler$_job_step_state ( job_name varchar2(100) NOT NULL CONSTRAINT scheduler$_job_step_state_pk PRIMARY KEY USING INDEX TABLESPACE sysaux, status char, error_code number ) TABLESPACE sysaux / grant select on sys.scheduler$_job_step_state to public with grant option; CREATE TABLE sys.scheduler$_job_step ( master_job_oid number NOT NULL, variable_name varchar2(30) NOT NULL, CONSTRAINT scheduler$_job_step_pk PRIMARY KEY (master_job_oid, variable_name), job_step_oid number NOT NULL ) / Rem These are types we added for 11g. If we were downgrading from 11g Rem to 10.1, we could not drop them in the 10.2 downgrade script since Rem that would invalidate dbms_scheduler. So, we wait till the downgrade Rem to 10.1 is done and then drop the types. drop type body sys.scheduler$_batcherr_view_t; drop type body sys.jobattr; drop type body sys.job; drop type body sys.job_definition; drop type body sys.jobarg; drop type sys.scheduler$_batcherr_array force; drop type sys.jobattr_array force; drop type sys.job_array force; drop type sys.job_definition_array force; drop type sys.jobarg_array force; drop type sys.scheduler$_batcherr_view_t force; drop type sys.jobattr force; drop type sys.job force; drop type sys.job_definition force; drop type sys.jobarg force; drop type sys.scheduler$_batcherr force; drop package sys.dbms_isched_remdb_job; Rem The new internal dbms_isched_chain_condition package is dropped lower down Rem in a PL/SQL conditional block. Rem========================================================================= Rem End Scheduler Downgrade Rem========================================================================= Rem========================================================================= Rem Revert 10.2 changes to sql.bsq dictionary tables here Rem========================================================================= Rem Rem Delete TSM table rows Rem truncate table tsm_src$; truncate table tsm_dst$; Rem Rem Create TSM table tsm_hist$ that existed in 10.1 Rem create table tsm_hist$ ( source_sid number, /* session id on source instance */ source_serial# number, /* serial# on source instance */ state number, /* migration state */ cost number, /* estimated migration cost */ source varchar2(4000), /* source instance */ destination varchar2(4000), /* destination instance */ connect_string varchar2(4000), /* destination connect string */ failure_reason number, /* reason for failure of migration */ destination_sid number, /* session id on destination instance */ destination_serial# number, /* serial# on destination instance */ start_time date, /* migration start time */ end_time date /* migration end time */ ) tablespace SYSAUX / create index i_tsm_hist1 on tsm_hist$(source_sid, source_serial#) tablespace SYSAUX / truncate table dir$alert_history; truncate table dir$reason_strings; Rem Rem downgrade service$ cardinality columns Rem UPDATE service$ set min_cardinality = NULL, max_cardinality = NULL, flags = NULL; Rem Rem High Availability Alerts Downgrade Rem truncate table recent_resource_incarnations$; drop public synonym DBA_RESOURCE_INCARNATIONS; drop view DBA_RESOURCE_INCARNATIONS; Rem Rem Delete all hints for outlines created in 10.2 and then delete Rem those outlines Rem delete outln.ol$hints where ol_name in (select ol_name from outln.ol$ where version = '10.2.0.0.0'); delete outln.ol$nodes where ol_name in (select ol_name from outln.ol$ where version = '10.2.0.0.0'); delete outln.ol$ where version = '10.2.0.0.0'; Rem Set hint_string column in outln.ol$hints to NULL update outln.ol$hints set hint_string = null; Rem Rem Do the same as above for private outlines Rem delete system.ol$hints where ol_name in (select ol_name from system.ol$ where version = '10.2.0.0.0'); delete system.ol$nodes where ol_name in (select ol_name from system.ol$ where version = '10.2.0.0.0'); delete system.ol$ where version = '10.2.0.0.0'; Rem Set hint_string column in system.ol$hints to NULL update system.ol$hints set hint_string = null; Rem========================================================================= Rem Begin Streams Downgrade Rem========================================================================= TRUNCATE TABLE apply$_error_txn; UPDATE sys.apply$_dest_obj_ops SET assemble_lobs = 'N'; commit; BEGIN UPDATE apply$_error SET error_creation_time = NULL; COMMIT; END; / TRUNCATE TABLE sys.streams$_internal_transform; -- apply spill TRUNCATE TABLE sys.streams$_apply_spill_txn; TRUNCATE TABLE sys.streams$_apply_spill_messages; TRUNCATE TABLE sys.streams$_apply_spill_msgs_part; TRUNCATE TABLE sys.streams$_apply_spill_txn_list; drop sequence sys.streams$_apply_spill_txnkey_s; DROP view DBA_APPLY_SPILL_TXN; DROP view "_DBA_APPLY_SPILL_TXN"; UPDATE streams$_prepare_ddl SET flags = NULL, spare2 = NULL; UPDATE streams$_prepare_object SET flags = NULL, spare2 = NULL; COMMIT; Rem BEGIN file groups tables downgrade truncate table fgr$_file_groups; truncate table fgr$_file_group_versions; truncate table fgr$_file_group_files; truncate table fgr$_file_group_export_info; truncate table fgr$_table_info; truncate table fgr$_tablespace_info; Rem END file groups tables downgrade -- DOWNSTREAM_REAL_TIME_MINE option introduced in 10gR2 BEGIN DELETE FROM streams$_process_params WHERE name = 'DOWNSTREAM_REAL_TIME_MINE' AND process_type = 2; COMMIT; END; / -- Dependency Computation features introduced in 10gR2 TRUNCATE TABLE sys.apply$_constraint_columns; TRUNCATE TABLE sys.apply$_virtual_obj_cons; Rem reset oldest_transaction_id in streams$_apply_milestone UPDATE streams$_apply_milestone SET oldest_transaction_id = NULL; COMMIT; Rem remove UA_NOTIFICATION_HANDLER from streams$_apply_process Rem remove any value stored in the column UPDATE streams$_apply_process SET UA_NOTIFICATION_HANDLER = NULL, UA_RULESET_OWNER = NULL, UA_RULESET_NAME = NULL; COMMIT; DECLARE vt sys.re$variable_type_list; BEGIN vt := sys.re$variable_type_list( sys.re$variable_type('DML', 'SYS.LCR$_ROW_RECORD', 'SYS.DBMS_STREAMS_INTERNAL.ROW_VARIABLE_VALUE_FUNCTION', 'SYS.DBMS_STREAMS_INTERNAL.ROW_FAST_EVALUATION_FUNCTION'), sys.re$variable_type('DDL', 'SYS.LCR$_DDL_RECORD', 'SYS.DBMS_STREAMS_INTERNAL.DDL_VARIABLE_VALUE_FUNCTION', 'SYS.DBMS_STREAMS_INTERNAL.DDL_FAST_EVALUATION_FUNCTION')); dbms_rule_adm.alter_evaluation_context( evaluation_context_name=>'SYS.STREAMS$_EVALUATION_CONTEXT', variable_types=>vt); EXCEPTION WHEN OTHERS THEN IF SQLCODE = -24150 THEN -- suppress evaluation context does not exist error to minimize -- unwanted noise during downgrade. NULL; ELSE RAISE; END IF; END; / BEGIN UPDATE sys.reg$ SET qosflags = 0, payload_callback = null, timeout = null, subscription_name = REPLACE(subscription_name, '"'); COMMIT; END; / -- -- Recoverable scripts downgrade -- TRUNCATE TABLE sys.reco_script_params$; TRUNCATE TABLE sys.reco_script_block$; TRUNCATE TABLE sys.reco_script_error$; TRUNCATE TABLE sys.reco_script$; Rem========================================================================= Rem End Streams Downgrade Rem========================================================================= Rem Rem Transparent Data Encryption Rem truncate table enc$; Rem Rem Delete Scheduler Chain rows Rem truncate table scheduler$_chain; truncate table scheduler$_step; truncate table scheduler$_step_state; Rem Rem Begin CDC changes here Rem Rem set columns added in 10.2 to null UPDATE cdc_change_sources$ SET capture_name = NULL; UPDATE cdc_change_sources$ SET capqueue_name = NULL; UPDATE cdc_change_sources$ SET capqueue_tabname = NULL; UPDATE cdc_change_sources$ SET source_enabled = NULL; UPDATE cdc_change_sets$ SET set_sequence = NULL; COMMIT; Rem truncate tables added in 10.2 TRUNCATE table cdc_propagations$; TRUNCATE table cdc_propagated_sets$; Rem return pre-defined changes sources to original values Rem return autolog change sources back to a value of 2 . BEGIN UPDATE cdc_change_sources$ SET source_type = 3 WHERE source_name = 'HOTLOG_SOURCE'; UPDATE cdc_change_sources$ SET source_type = 4 WHERE source_name = 'SYNC_SOURCE'; UPDATE cdc_change_sources$ SET source_type = 2 WHERE BITAND(source_type,2) = 2; COMMIT; END; / Rem drop the views added DROP view change_propagations; DROP view change_propagation_sets; Rem drop package added in 10.2 DROP package dbms_cdc_sys_ipublish; Rem Rem End CDC changes Rem Rem========================================================================= Rem Begin Advanced Queuing downgrade Rem========================================================================= execute DBMS_SESSION.RESET_PACKAGE; REM drop views that exists only in 10.2 drop public synonym all_dequeue_queues; drop view all_dequeue_queues; drop public synonym user_queue_subscribers; drop view user_queue_subscribers; drop public synonym all_queue_subscribers; drop view all_queue_subscribers; drop public synonym dba_queue_subscribers; drop view dba_queue_subscribers; drop function aq$_get_subscribers; drop type sys.aq$_subscriber_t force; drop type sys.aq$_subscriber force; drop package sys.dbms_transform_eximp_internal; drop package sys.dbms_aq_inv; Rem========================================================================= Rem End Advanced Queuing downgrade Rem========================================================================= Rem========================================================================= Rem Beging Rules engine downgrade Rem========================================================================= truncate table rule_set_nl$; truncate table rule_set_pr$; Rem========================================================================= Rem End Rules engine downgrade Rem========================================================================= Rem========================================================================= Rem Begin SQL Response Time Downgrade Rem========================================================================= drop table dbsnmp.mgmt_snapshot; drop table dbsnmp.mgmt_snapshot_sql; drop table dbsnmp.mgmt_baseline; drop table dbsnmp.mgmt_baseline_sql; drop table dbsnmp.mgmt_capture; drop table dbsnmp.mgmt_capture_sql; drop table dbsnmp.mgmt_response_config; drop table dbsnmp.mgmt_latest; drop table dbsnmp.mgmt_latest_sql; drop table dbsnmp.mgmt_history; drop table dbsnmp.mgmt_history_sql; drop table dbsnmp.mgmt_tempt_sql; drop sequence dbsnmp.mgmt_response_capture_id; drop sequence dbsnmp.mgmt_response_snapshot_id; drop view dbsnmp.mgmt_response_baseline; Rem========================================================================= Rem End SQL Response Time Downgrade Rem========================================================================= Rem========================================================================= Rem Begin Dynamic Baselines Downgrade Rem========================================================================= drop package dbsnmp.mgmt_bsln; drop package dbsnmp.mgmt_bsln_internal; drop type dbsnmp.bsln_interval_set force; drop type dbsnmp.bsln_interval_t force; drop type dbsnmp.bsln_observation_set force; drop type dbsnmp.bsln_observation_t force; drop type dbsnmp.bsln_statistics_set force; drop type dbsnmp.bsln_statistics_t force; alter table dbsnmp.mgmt_bsln_intervals disable constraint bsln_intervals_fk1; alter table dbsnmp.mgmt_bsln_statistics disable constraint bsln_statistics_fk1; alter table dbsnmp.mgmt_bsln_statistics disable constraint bsln_statistics_fk2; alter table dbsnmp.mgmt_bsln_threshold_parms disable constraint bsln_thresholds_fk1; alter table dbsnmp.mgmt_bsln_threshold_parms disable constraint bsln_thresholds_fk2; DECLARE PROCEDURE execute_truncate(table_name IN VARCHAR2) IS no_such_table EXCEPTION; PRAGMA exception_init(no_such_table, -942); BEGIN execute immediate 'truncate table ' || table_name; EXCEPTION WHEN no_such_table THEN null; END; BEGIN -- execute_truncation procedure invoked only with literal constants execute_truncate('dbsnmp.mgmt_bsln_intervals'); execute_truncate('dbsnmp.mgmt_bsln_metrics'); execute_truncate('dbsnmp.mgmt_bsln_statistics'); execute_truncate('dbsnmp.mgmt_bsln_threshold_parms'); execute_truncate('dbsnmp.mgmt_bsln_baselines'); execute_truncate('dbsnmp.mgmt_bsln_datasources'); execute_truncate('dbsnmp.mgmt_bsln_rawdata'); END; / alter table dbsnmp.mgmt_bsln_intervals enable constraint bsln_intervals_fk1; alter table dbsnmp.mgmt_bsln_statistics enable constraint bsln_statistics_fk1; alter table dbsnmp.mgmt_bsln_statistics enable constraint bsln_statistics_fk2; alter table dbsnmp.mgmt_bsln_threshold_parms enable constraint bsln_thresholds_fk1; alter table dbsnmp.mgmt_bsln_threshold_parms enable constraint bsln_thresholds_fk2; Rem========================================================================= Rem End Dynamic Baselines Downgrade Rem========================================================================= Rem========================================================================= Rem EUS Proxy downgrade - Begin Rem========================================================================= DELETE FROM proxy_info$ WHERE bitand(flags, 16) = 16 ; Rem========================================================================= Rem EUS Proxy downgrade - End Rem========================================================================= Rem Remove the database-level trace entries from wri$_tracing_enabled delete from wri$_tracing_enabled where trace_type = 6; commit; Rem set Materialized View metadata UPDATE snap$ set syn_count = NULL; UPDATE sumdep$ set syn_own = NULL; UPDATE sumdep$ set syn_name = NULL; UPDATE sumdep$ set syn_master = NULL; UPDATE sumdep$ set vw_query = NULL; UPDATE sumdep$ set vw_query_len = NULL; Rem remove all 10.2 online redefinition metadata DECLARE CURSOR redef10gR2 IS SELECT id FROM sys.redef$ r WHERE bitand(r.flag, 4096) = 4096; BEGIN FOR r2 IN redef10gR2 LOOP DELETE FROM sys.redef_object$ r WHERE r.redef_id = r2.id; DELETE FROM sys.redef_dep_error$ r WHERE r.redef_id = r2.id; DELETE FROM sys.redef$ r WHERE r.id = r2.id; END LOOP; COMMIT; END; / Rem set the new column (flags) value to 0 in dimlevel$ UPDATE sys.dimlevel$ SET flags = 0; Rem set the new column (chdlevid#) value to 0 in dimjoinkey$ UPDATE sys.dimjoinkey$ SET chdlevid# = 0; Rem Set service$.goal and service$.flags columns to null UPDATE service$ set goal = NULL; UPDATE service$ set flags = NULL; Rem drop the utl_match package drop package utl_match; Rem Component Registry downgrade truncate table registry$schemas; Rem drop the dbms_db_version package and its public synonym drop public synonym dbms_db_version; drop package dbms_db_version; Rem drop the dbms_preprocessor package and its public synonym drop public synonym dbms_preprocessor; drop package dbms_preprocessor; Rem Drop Decision Tree Internal types and functions drop PUBLIC SYNONYM ORA_DM_Tree_Nodes; drop PUBLIC SYNONYM ORA_FI_DECISION_TREE_HORIZ; drop function ORA_FI_DECISION_TREE_HORIZ; drop type ORA_DM_Tree_Nodes force; drop type ORA_DM_Tree_Node force; Rem change notification tables truncate table invalidation_registry$; Rem========================================================================= Rem XQuery related downgrade - begin Rem========================================================================= drop public synonym SYS_IXQAGG; drop function SYS_IXQAGG; drop type AggXQImp; drop public synonym XQSequence; drop operator XQSequence; drop function XQSequenceFromXMLType; drop type XQSeq_Imp_t; Rem========================================================================= Rem XQuery related downgrade - end Rem========================================================================= Rem========================================================================= Rem XMLAgg and XMLSequence related downgrade - begin Rem========================================================================= Rem ** Type Frozen. Any additions must be through alter type add. create or replace type AggXMLInputType OID '00000000000000000000000000020103' authid current_user as object ( input sys.XMLType, format sys.XMLGenFormatType ); / grant execute on AggXMLInputType to public with grant option; -- the following 5 privileges were not in 8.17 or 9.01 grant execute on XMLSeq_Imp_t to public with grant option; grant execute on XMLSeqCur_Imp_t to public with grant option; grant execute on XMLSeqCur2_Imp_t to public with grant option; grant execute on AggXMLImp to public with grant option; grant execute on XMLAGG to public with grant option; Rem========================================================================= Rem XMLAgg and XMLSequence related downgrade - end Rem========================================================================= Rem========================================================================= Rem OLAP related downgrade - begin Rem========================================================================= grant execute on OLAPImpl_t to public; grant execute on OLAPRanCurImpl_t to public; Rem========================================================================= Rem OLAP related downgrade - end Rem========================================================================= -- Drop new UTL_RECOMP views drop view utl_recomp_all_objects; Rem========================================================================= Rem Java Dictionary javaobj$ downgrade Rem========================================================================= truncate table javaobj$; Rem bug 6144565: in 10.2 we created some indexes as unique. the 10.1 code Rem does not handle unique indexes correctly. recreate them as non unique drop index i_tabpart_bopart$ / create index i_tabpart$ on tabpart$(bo#, obj#) / drop index i_tabpart_obj$ / create index i_tabpart_obj$ on tabpart$(obj#) / drop index i_indpart_bopart$ / create index i_indpart$ on indpart$(bo#, obj#) / drop index i_indpart_obj$ / create index i_indpart_obj$ on indpart$(obj#) / drop index i_tabsubpart_pobjsubpart$ / create index i_tabsubpart$ on tabsubpart$(pobj#, obj#) / drop index i_tabsubpart$_obj$ / create index i_tabsubpart$_obj$ on tabsubpart$(obj#) / drop index i_indsubpart_pobjsubpart$ / create index i_indsubpart$ on indsubpart$(pobj#, obj#) / drop index i_indsubpart_obj$ / create index i_indsubpart_obj$ on indsubpart$(obj#) / drop index i_tabcompart_bopart$ / drop index i_indcompart_bopart$ / drop index i_lobfrag$_parentobj$ / create index i_lobfrag$_parentobj$ on lobfrag$(parentobj#) / drop index i_lobfrag$_fragobj$ / create index i_lobfrag$_fragobj$ on lobfrag$(fragobj#) / drop index i_lobcomppart$_lobjpart$ / create index i_lobcomppart$_partlobj$ on lobcomppart$(lobj#) / drop index i_lobcomppart$_partobj$ / create index i_lobcomppart$_partobj$ on lobcomppart$(partobj#) / drop index i_defsubpart$ / create index i_defsubpart$ on defsubpart$(bo#, spart_position) / drop index i_defsubpartlob$ / create index i_defsubpartlob$ on defsubpartlob$ (bo#, intcol#, spart_position) / Rem ========================================================================= Rem END STAGE 2: downgrade dictionary from 10.2 to 10.1 Rem ========================================================================= Rem drop the dbms_assert package and its public synonym -- if the version we upgraded from is 9.2, do not drop these packages yet -- DBMS_UTILITY and DBMS_REGISTRY need it in the 9.2 downgrade -- dbms_isched_chain_condition cannot be dropped during downgrade to 9.2 -- either since it is required by the 10.1->9.2 downgrade (e0902000.sql) DECLARE previous_version varchar2(30); BEGIN SELECT prv_version INTO previous_version FROM registry$ WHERE cid = 'CATPROC'; IF previous_version NOT LIKE '9.2.0%' THEN execute immediate 'drop public synonym dbms_assert'; execute immediate 'drop package dbms_assert'; execute immediate 'drop package dbms_isched_chain_condition'; execute immediate 'drop package dbms_propagation_internal'; -- The following package must be conditionally dropped because it will -- invalidate dbms_streams_adm, which e0902000.sql relies upon. Thus -- if the version we upgraded from is 9.2, do not drop this package. execute immediate 'DROP PACKAGE dbms_streams_mt'; -- drop the dba_queue_schedules and user_queue_schedules views -- These will be recreated by catqueue again execute immediate 'drop public synonym dba_queue_schedules'; execute immediate 'drop view dba_queue_schedules'; execute immediate 'drop public synonym user_queue_schedules'; execute immediate 'drop view user_queue_schedules'; -- dbms_isched has dependency on this table execute immediate 'alter table scheduler$_job_run_details modify (cpu_used number)'; -- Drop package with dependency on X$KQFOPT execute immediate 'drop package dbms_stats_internal'; END IF; END; / Rem========================================================================= Rem invalidate rules engine objects Rem========================================================================= UPDATE sys.obj$ SET status = 5 where obj# in ((select obj# from obj$ where type# = 62 or type# = 46 or type# = 59) union all (select /*+ index (dependency$ i_dependency2) */ d_obj# from dependency$ connect by prior d_obj# = p_obj# start with p_obj# in (select obj# from obj$ where type# = 62 or type# = 46 or type# = 59))) / commit / Rem========================================================================= Rem drop _*_EDITION_OBJ view family Rem========================================================================= drop view "_CURRENT_EDITION_OBJ"; drop view "_ACTUAL_EDITION_OBJ"; drop view "_BASE_USER"; Rem========================================================================= Rem drop edition ORA$BASE Rem========================================================================= drop edition ORA$BASE CASCADE; delete from sys.props$ where name = 'DEFAULT_EDITION'; commit; alter system flush shared_pool; Rem ************************************************************************* Rem END e1001000.sql Rem *************************************************************************