Rem Rem $Header: rdbms/admin/c1102000.sql /st_rdbms_11.2.0/22 2011/08/07 11:52:34 jomcdon Exp $ Rem Rem c1102000.sql Rem Rem Copyright (c) 2009, 2011, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem c1102000.sql - Script to apply current release patch release Rem Rem DESCRIPTION Rem This script encapsulates the "post install" steps necessary Rem to upgrade the SERVER dictionary to the new patchset version. Rem It runs the new patchset versions of catalog.sql and catproc.sql Rem and calls the component patch scripts. Rem Rem NOTES Rem Use SQLPLUS and connect AS SYSDBA to run this script. Rem The database must be open for UPGRADE. Rem Rem MODIFIED (MM/DD/YY) Rem jomcdon 08/03/11 - lrg 5758311: fix resource manager upgrade Rem yurxu 07/18/11 - Backport yurxu_bug-12701917 from main Rem sanagara 07/13/11 - 12326358: add spare columns to sqlerror$ Rem shiyadav 07/08/11 - Backport shiyadav_bug-12317689 from main Rem bhammers 07/01/11 - Backport bhammers_bug-12674093 from main Rem elu 06/06/11 - Backport elu_bug-12592488 from main Rem yurxu 05/04/11 - Backport yurxu_bug-12391440 from main Rem schakkap 12/23/10 - #(10410249) create col_group_usage$ as a non iot Rem table Rem rramkiss 05/03/11 - Backport rramkiss_bug-12319196 from main Rem yurxu 04/12/11 - Backport yurxu_bug-11922716 from main Rem bpwang 04/14/11 - Backport bpwang_bug-11815316 from main Rem abrown 04/11/11 - Backport abrown_bug-11737200 from main Rem elu 03/23/11 - Backport elu_bug-9690366 from main Rem elu 03/03/11 - Backport elu_bug-11725453 from main Rem sdavidso 03/08/11 - add columns to metascript$ for full export Rem elu 02/19/11 - lcr changes Rem msakayed 02/18/11 - Bug #11787333: upsert STMT_AUDIT_OPTION_MAP Rem gclaborn 02/17/11 - Add new flag defs in impcalloutreg$ Rem elu 05/25/11 - remove xml schema Rem huntran 01/13/11 - conflict, error, and collision handlers Rem elu 01/12/11 - error queue Rem ilistvin 01/06/11 - Backport ilistvin_bug-10427840 from main Rem gclaborn 12/14/10 - add tgt_type to impcalloutreg$ Rem gclaborn 09/15/10 - add impcalloutreg$ Rem hosu 08/09/10 - lrg 4817143 Rem elu 02/16/11 - modify eager_size Rem qiwang 06/04/10 - add logmnr integrated spill table Rem - (gkulkarn) Set logmnr_session$.spare1 to zero Rem on upgrade Rem nalamand 06/03/10 - Bug-9765326, 9747794: Add missing default Rem passwords Rem rangrish 06/02/10 - revoke grant on urifactory on upgrade Rem thoang 06/01/10 - handle lowercased user name Rem schakkap 05/23/10 - #(9577300) add table to record column group usage Rem hosu 05/10/10 - upgrade wri$_optstat_synopsis$ Rem tbhosle 05/04/10 - 8670389: increase regid seq cache size, move Rem session key into reg$ Rem thoang 04/27/10 - change Streams parameter names Rem hosu 04/09/10 - reduce subpartition number in wri$_optstat_synopsis$ Rem hosu 03/30/10 - 4545922: disable partitioning check Rem bdagevil 03/14/10 - add px_flags column to Rem WRH$_ACTIVE_SESSION_HISTORY Rem yurxu 03/05/10 - Bug-9469148: modify goldengate$_privileges Rem dongfwan 03/01/10 - Bug 9266913: add snap_timezone to wrm$_snapshot Rem apsrivas 01/12/10 - Bug 9148218, add def pwds for APR_USER and Rem ARGUSUSER Rem jomcdon 02/10/10 - bug 9368895: add parallel_queue_timeout Rem hosu 02/15/10 - 9038395: wri$_optstat_synopsis$ schema change Rem jomcdon 02/10/10 - Bug 9207475: allow end_time to be null Rem sburanaw 02/04/10 - Add DB Replay callid to ASH Rem juyuan 02/03/10 - add lcr$_row_record.get_object_id Rem akociube 02/02/10 - Fix OLAP revoke order Rem sburanaw 01/08/10 - add filter_set to wrr$_replays Rem add default_action to wrr$_replay_filter_set Rem juyuan 01/21/10 - remove all_streams_stmt_handlers and Rem all_streams_stmts Rem jomcdon 12/31/09 - bug 9212250: add PQQ fields to AWR tables Rem juyuan 12/27/09 - create goldengate$_privileges Rem akociube 12/21/09 - Bug 9226807 revoke permissions Rem amadan 11/19/09 - Bug 9115881 Add new columns to PERSISTENT_QUEUES Rem arbalakr 11/12/09 - increase length of module and action columns Rem akruglik 11/18/09 - 31113 (SCHEMA SYNONYMS): adding support for Rem auditing CREATE/DROP SCHEMA SYNONYM Rem jomcdon 12/03/09 - project 24605: clear max_active_sess_target_p1 Rem xingjin 11/14/09 - Bug 9086576: modify construct in lcr$_row_record Rem akruglik 11/10/09 - add/remove new audit_actions rows Rem gravipat 10/27/09 - Add sqlerror$ creation Rem hayu 10/01/09 - change the advisor/spa Rem msakayed 10/28/09 - Bug #5842629: direct path load auditing Rem praghun 11/03/09 - Added some spare columns to milestone table Rem thoang 10/13/09 - support uncommitted data mode Rem tianli 10/11/09 - add xstream$_parameters table Rem elu 10/06/09 - stmt lcr Rem praghuna 10/19/09 - Added start_scn_time, first_scn_time Rem msakayed 10/22/09 - Bug #8862486: AUDIT_ACTION for directory execute Rem lgalanis 10/20/09 - STS capture for DB Replay Rem achoi 09/21/09 - edition as a service attribute Rem shbose 09/18/09 - Bug 8764375: add destq column to aq$_schedules Rem cdilling 07/31/09 - Patch upgrade script for 11.2.0 Rem cdilling 07/31/09 - Created Rem Rem ************************************************************************* Rem BEGIN c1102000.sql Rem ************************************************************************* Rem ======================================================================= Rem Begin Changes for XStream Rem ======================================================================= Rem Rem Add some spare columns for apply optimization purpose Rem alter table streams$_apply_milestone add (spare8 number, spare9 number, spare10 timestamp, spare11 timestamp); alter type lcr$_row_record add member function is_statement_lcr return varchar2 cascade; alter type lcr$_row_record add member procedure set_row_text(self in out nocopy lcr$_row_record, row_text IN CLOB, variable_list IN sys.lcr$_row_list DEFAULT NULL, bind_by_position in varchar2 DEFAULT 'N') cascade; alter type lcr$_row_record drop static function construct( source_database_name in varchar2, command_type in varchar2, object_owner in varchar2, object_name in varchar2, tag in raw DEFAULT NULL, transaction_id in varchar2 DEFAULT NULL, scn in number DEFAULT NULL, old_values in sys.lcr$_row_list DEFAULT NULL, new_values in sys.lcr$_row_list DEFAULT NULL, position in raw DEFAULT NULL ) RETURN lcr$_row_record cascade; alter type lcr$_row_record drop static function construct( source_database_name in varchar2, command_type in varchar2, object_owner in varchar2, object_name in varchar2, tag in raw DEFAULT NULL, transaction_id in varchar2 DEFAULT NULL, scn in number DEFAULT NULL, old_values in sys.lcr$_row_list DEFAULT NULL, new_values in sys.lcr$_row_list DEFAULT NULL, position in raw DEFAULT NULL, statement in varchar2 DEFAULT NULL, bind_variables in sys.lcr$_row_list DEFAULT NULL, bind_by_position in varchar2 DEFAULT 'N' ) RETURN lcr$_row_record cascade; alter type lcr$_row_record add static function construct( source_database_name in varchar2, command_type in varchar2, object_owner in varchar2, object_name in varchar2, tag in raw DEFAULT NULL, transaction_id in varchar2 DEFAULT NULL, scn in number DEFAULT NULL, old_values in sys.lcr$_row_list DEFAULT NULL, new_values in sys.lcr$_row_list DEFAULT NULL, position in raw DEFAULT NULL, statement in varchar2 DEFAULT NULL, bind_variables in sys.lcr$_row_list DEFAULT NULL, bind_by_position in varchar2 DEFAULT 'N' ) RETURN lcr$_row_record cascade; alter type lcr$_row_record add member function get_base_object_id return number cascade; alter type lcr$_row_record add member function get_object_id return number cascade; alter type lcr$_ddl_record drop STATIC FUNCTION construct( source_database_name in varchar2, command_type in varchar2, object_owner in varchar2, object_name in varchar2, object_type in varchar2, ddl_text in clob, logon_user in varchar2, current_schema in varchar2, base_table_owner in varchar2, base_table_name in varchar2, tag in raw DEFAULT NULL, transaction_id in varchar2 DEFAULT NULL, scn in number DEFAULT NULL, position in raw DEFAULT NULL, edition_name in varchar2 DEFAULT NULL ) RETURN lcr$_ddl_record cascade; alter type lcr$_ddl_record drop STATIC FUNCTION construct( source_database_name in varchar2, command_type in varchar2, object_owner in varchar2, object_name in varchar2, object_type in varchar2, ddl_text in clob, logon_user in varchar2, current_schema in varchar2, base_table_owner in varchar2, base_table_name in varchar2, tag in raw DEFAULT NULL, transaction_id in varchar2 DEFAULT NULL, scn in number DEFAULT NULL, position in raw DEFAULT NULL, edition_name in varchar2 DEFAULT NULL, current_user in varchar2 DEFAULT NULL ) RETURN lcr$_ddl_record cascade; alter type lcr$_ddl_record add STATIC FUNCTION construct( source_database_name in varchar2, command_type in varchar2, object_owner in varchar2, object_name in varchar2, object_type in varchar2, ddl_text in clob, logon_user in varchar2, current_schema in varchar2, base_table_owner in varchar2, base_table_name in varchar2, tag in raw DEFAULT NULL, transaction_id in varchar2 DEFAULT NULL, scn in number DEFAULT NULL, position in raw DEFAULT NULL, edition_name in varchar2 DEFAULT NULL, current_user in varchar2 DEFAULT NULL ) RETURN lcr$_ddl_record cascade; alter type lcr$_ddl_record add MEMBER FUNCTION get_current_user RETURN varchar2 cascade; alter type lcr$_ddl_record add MEMBER PROCEDURE set_current_user (self in out nocopy lcr$_ddl_record, current_user IN VARCHAR2) cascade; alter table sys.streams$_apply_milestone add ( eager_error_retry number /* number of retries for eager error */ ); alter table sys.apply$_error add ( retry_count number, /* number of times to retry an error */ flags number /* flags */ ); alter table sys.apply$_error_txn add ( error_number number, /* error number reported */ error_message varchar2(4000), /* explanation of error */ flags number, /* flags */ spare1 number, spare2 number, spare3 varchar2(4000), spare4 varchar2(4000), spare5 raw(2000), spare6 timestamp ); alter table sys.apply$_error_txn add ( source_object_owner varchar2(30), /* source database object owner */ source_object_name varchar2(30), /* source database object name */ dest_object_owner varchar2(30), /* dest database object owner */ dest_object_name varchar2(30), /* dest database object name */ primary_key varchar2(4000), /* primary key information */ position raw(64), /* LCR position */ message_flags number, /* knlqdqm flags */ operation varchar2(100) /* LCR operation */ ); -- -- Table to for ddl conflict handlers -- create table xstream$_ddl_conflict_handler ( apply_name varchar2(30) not null, /* apply name */ conflict_type varchar2(4000) not null, /* conflict type */ include clob, /* inclusion clause */ exclude clob, /* exclusion clause */ method clob, /* method for resolving conflict */ spare1 number, spare2 number, spare3 number, spare4 timestamp, spare5 varchar2(4000), spare6 varchar2(4000), spare7 clob, spare8 clob, spare9 raw(100) ) / create index i_xstream_ddl_conflict_hdlr1 on xstream$_ddl_conflict_handler(apply_name) / -- -- Table to for ddl conflict handlers -- create table xstream$_map ( apply_name varchar2(30) not null, /* apply name */ src_obj_owner varchar2(30), /*source object owner */ src_obj_name varchar2(100) not null, /* source object name */ tgt_obj_owner varchar2(30), /* target object owner */ tgt_obj_name varchar2(100) not null, /* target object name */ colmap clob, /* column mapping definition */ sqlexec clob, /* SQLEXEC definition */ sequence number, /* order of mapping clauses */ spare1 number, spare2 number, spare3 number, spare4 timestamp, spare5 varchar2(4000), spare6 varchar2(4000), spare7 clob, spare8 clob, spare9 raw(100) ) / create index i_xstream_map1 on xstream$_map(apply_name) / Rem Rem Add time parameters for scn Rem ALTER TABLE streams$_capture_process ADD ( start_scn_time date , first_scn_time date ); Rem Rem Table for xstream parameters Rem create table xstream$_parameters ( server_name varchar2(30) not null, /* XStream server name */ server_type number not null, /* 0 for outbond, 1 for inbound */ position number not null, /* total ordering for the parameters */ param_key varchar2(100), /* keyword in the parameter */ schema_name varchar2(30), /* optional, no wildcard */ object_name varchar2(30), /* optional, can do wildcard */ user_name varchar2(30), /* creation user */ creation_time timestamp, /* creation time */ modification_time timestamp, /* modification time */ flags number, /* unused right now */ details clob, /* the parameter details */ spare1 number, spare2 number, spare3 number, spare4 timestamp, spare5 varchar2(4000), spare6 varchar2(4000), spare7 raw(64), spare8 date, spare9 clob ) / create unique index i_xstream_parameters on xstream$_parameters(server_name, server_type, position) / Rem Rem Sequence for conflict handler id Rem create sequence conflict_handler_id_seq$ /* conflict handler id sequence */ start with 1 increment by 1 minvalue 1 maxvalue 4294967295 /* max portable value of UB4 */ nocycle nocache / Rem Rem Table for xstream dml_conflict_handler Rem create table xstream$_dml_conflict_handler ( object_name varchar2(30), /* object name */ schema_name varchar2(30), /* schema name */ apply_name varchar2(30), /* apply name */ conflict_type number, /* conflict type definition */ /* 1 row exists */ /* 2 row missing */ user_error number, /* unused */ opnum number, /* 1 insert, 2 update, 3 delete */ method_txt clob, /* unused */ method_name varchar2(4000), /* unused */ old_object varchar2(30), /* original object name */ old_schema varchar2(30), /* original schema name */ method_num number, /* resolution method * 1 RECORD, 2 IGNORE, 3 OVERWRITE, * 4 MAXIMUM, 5 MINIMUM, 6 DELTA */ conflict_handler_name varchar2(30), /* Name of the conflict handler */ resolution_column varchar2(30), /* column to evaluate */ conflict_handler_id number, /* ID of the conflict handler */ spare1 number, spare2 number, spare3 number, spare4 timestamp, spare5 varchar2(4000), spare6 varchar2(4000), spare7 raw(64), spare8 date, spare9 clob ) / Rem add new columns for 11.2.0.3 alter table xstream$_dml_conflict_handler add ( method_num number, /* resolution method * 1 RECORD, 2 IGNORE, 3 OVERWRITE, * 4 MAXIMUM, 5 MINIMUM, 6 DELTA */ conflict_handler_name varchar2(30), /* Name of the conflict handler */ resolution_column varchar2(30), /* column to evaluate */ conflict_handler_id number /* ID of the conflict handler */ ) / Rem this index may exist for an old version of this table drop index i_xstream_dml_conflict_handler / create index i_xstream_dml_conf_handler1 on xstream$_dml_conflict_handler(apply_name, schema_name, object_name, old_schema, old_object, opnum, conflict_type, method_num) / create unique index i_xstream_dml_conf_handler2 on xstream$_dml_conflict_handler(apply_name, conflict_handler_name) / Rem Rem Table to store the conflict resolution group for Rem xstream$_dml_conflict_handler Rem create table xstream$_dml_conflict_columns ( conflict_handler_id number not null, /* handler id */ column_name varchar2(30) not null, /* column */ spare1 number, spare2 number, spare3 number, spare4 timestamp, spare5 varchar2(4000), spare6 varchar2(4000), spare7 raw(64), spare8 date, spare9 clob ) / create index i_xstream_dml_conflict_cols1 on xstream$_dml_conflict_columns(conflict_handler_id) / Rem Rem table for reperror handlers Rem create table xstream$_reperror_handler ( apply_name varchar2(30) not null, /* Apply name */ schema_name varchar2(30) not null, /* dest schema */ table_name varchar2(30) not null, /* dest table */ source_schema_name varchar2(30) not null, /* src schema */ source_table_name varchar2(30) not null, /* src table */ error_number number not null, /* error number */ method number not null, /* 1 ABEND, 2 RECORD, * 3 RECORD_TRANSACTION, 4 IGNORE, * 5 RETRY, 6 RETRY_TRANSACTION */ max_retries number, /* max retries */ delay_msecs number, /* retry delay miliseconds */ spare1 number, spare2 number, spare3 number, spare4 timestamp, spare5 varchar2(4000), spare6 varchar2(4000), spare7 raw(64), spare8 date, spare9 clob ) / create unique index i_xstream_reperror_handler1 on xstream$_reperror_handler(apply_name, schema_name, table_name, source_schema_name, source_table_name, error_number) / Rem Rem table for collision handlers Rem create table xstream$_handle_collisions ( apply_name varchar2(30) not null, /* apply name */ schema_name varchar2(30) not null, /* dest schema */ table_name varchar2(30) not null, /* dest table */ source_schema_name varchar2(30) not null, /* src schema */ source_table_name varchar2(30) not null, /* src table */ handle_collisions varchar2(1) not null, /* Handle collisions? Y/N */ spare1 number, spare2 number, spare3 number, spare4 timestamp, spare5 varchar2(4000), spare6 varchar2(4000), spare7 raw(64), spare8 date, spare9 clob ) / create unique index i_xstream_handle_collisions1 on xstream$_handle_collisions(apply_name, schema_name, table_name, source_schema_name, source_table_name) / alter table streams$_apply_process add ( spare4 number, spare5 number, spare6 varchar2(4000), spare7 varchar2(4000), spare8 date, spare9 date ); alter table streams$_privileged_user add ( flags number, spare1 number, spare2 number, spare3 varchar2(1000), spare4 varchar2(1000) ); alter table xstream$_server add ( status_change_time date /* the time that the status column changed */ ); alter table xstream$_server add ( connect_user varchar2(30) ); create table xstream$_server_connection ( outbound_server varchar2(30) not null, inbound_server varchar2(30) not null, inbound_server_dblink varchar2(128), outbound_queue_owner varchar2(30), outbound_queue_name varchar2(30), inbound_queue_owner varchar2(30), inbound_queue_name varchar2(30), rule_set_owner varchar2(30), rule_set_name varchar2(30), negative_rule_set_owner varchar2(30), negative_rule_set_name varchar2(30), flags number, status number, create_date date, error_message varchar2(4000), error_date date, acked_scn number, auto_merge_threshold number, spare1 number, spare2 number, spare3 varchar2(4000), spare4 varchar2(4000), spare5 varchar2(4000), spare6 varchar2(4000), spare7 date, spare8 date, spare9 raw(2000), spare10 raw(2000) ) / create index i_xstream_server_connection1 on xstream$_server_connection (outbound_server, inbound_server, inbound_server_dblink) / create table goldengate$_privileges ( username varchar2(30) not null, privilege_type number not null, /* 1: capture; 2: apply; 3:* */ privilege_level number not null, /* 0: NONE; 1: select privilege */ create_time timestamp, spare1 number, spare2 number, spare3 timestamp, spare4 varchar2(4000), spare5 varchar2(4000)) / create unique index goldengate$_privileges_i on goldengate$_privileges(username, privilege_type, privilege_level) / create table xstream$_privileges ( username varchar2(300) not null, privilege_type number not null, /* 1: capture; 2: apply; 3:* */ privilege_level number not null, /* 1: administrator; 2: user */ create_time timestamp, spare1 number, spare2 number, spare3 timestamp, spare4 varchar2(4000), spare5 varchar2(4000)) / create unique index i_xstream_privileges on xstream$_privileges(username, privilege_type, privilege_level) / drop public synonym all_streams_stmt_handlers; drop view all_streams_stmt_handlers; drop public synonym all_streams_stmts; drop view all_streams_stmts; Rem Modify Streams parameter names update sys.streams$_process_params set name = 'COMPARE_KEY_ONLY', internal_flag=0 where name = '_CMPKEY_ONLY'; update sys.streams$_process_params set name = 'IGNORE_TRANSACTION', internal_flag=0 where name = '_IGNORE_TRANSACTION'; update sys.streams$_process_params set name = 'IGNORE_UNSUPPORTED_TABLE', internal_flag=0 where name = '_IGNORE_UNSUPERR_TABLE'; update sys.streams$_process_params set name = 'MAX_PARALLELISM', internal_flag=0 where name = '_MAX_PARALLELISM'; update sys.streams$_process_params set name = 'EAGER_SIZE', internal_flag=0 where name = '_EAGER_SIZE'; update sys.streams$_process_params set value = '9500' where name = 'EAGER_SIZE' and user_changed_flag=0 and value = '1000'; commit; Rem Grant SELECT ANY TRANSACTION to all Streams admin users DECLARE user_names dbms_sql.varchar2s; i PLS_INTEGER; BEGIN -- grant select any transaction to username from dba_streams_administrator. SELECT u.name BULK COLLECT INTO user_names FROM user$ u, sys.streams$_privileged_user pu WHERE u.user# = pu.user# AND pu.privs != 0 and (pu.flags IS NULL or pu.flags = 0 or (bitand(pu.flags, 1) = 1)); FOR i IN 1 .. user_names.count LOOP -- Don't uppercase username during enquote_name IF (user_names(i) <> 'SYS' AND user_names(i) <> 'SYSTEM') THEN EXECUTE IMMEDIATE 'GRANT SELECT ANY TRANSACTION TO ' || dbms_assert.enquote_name(user_names(i), FALSE); END IF; END LOOP; END; / rem ======================================================================= Rem End Changes for XStream Rem ======================================================================= Rem ======================================================================= Rem Begin Changes for LogMiner Rem ======================================================================= Rem Set system.logmnr_session$.spare1 to zero update system.logmnr_session$ set spare1 = 0; commit; CREATE TABLE SYSTEM.logmnr_integrated_spill$ ( session# number, xidusn number, xidslt number, xidsqn number, chunk number, flag number, ctime date, mtime date, spill_data blob, spare1 number, spare2 number, spare3 number, spare4 date, spare5 date, CONSTRAINT LOGMNR_INTEG_SPILL$_PK PRIMARY KEY (session#, xidusn, xidslt, xidsqn, chunk, flag) USING INDEX TABLESPACE SYSAUX LOGGING) LOB (spill_data) STORE AS (TABLESPACE SYSAUX CACHE PCTVERSION 0 CHUNK 32k STORAGE (INITIAL 4M NEXT 2M)) TABLESPACE SYSAUX LOGGING / -- -- For Logminer support of GG mining across a redo gap. -- The tables LOGMNRGGC_GTLO and LOGMNRGGC_GTCS are are -- identical to their counterparts LOGMNRC_GTLOG and LOGMNRC_GTCS. -- Though it would have been simpler to use designated paritions -- of the original tables, this could have led to unacceptable locking -- issues when the DDL trigger that maintains these tables fires. -- CREATE TABLE SYSTEM.LOGMNRGGC_GTLO( LOGMNR_UID NUMBER NOT NULL, KEYOBJ# NUMBER NOT NULL, LVLCNT NUMBER NOT NULL, /* level count */ BASEOBJ# NUMBER NOT NULL, /* base object number */ BASEOBJV# NUMBER NOT NULL, /* base object version */ LVL1OBJ# NUMBER, /* level 1 object number */ LVL2OBJ# NUMBER, /* level 2 object number */ LVL0TYPE# NUMBER NOT NULL, /* level 0 (base obj) type # */ LVL1TYPE# NUMBER, /* level 1 type # */ LVL2TYPE# NUMBER, /* level 2 type # */ OWNER# NUMBER, /* owner number */ OWNERNAME VARCHAR2(30) NOT NULL, LVL0NAME VARCHAR2(30) NOT NULL, /* name of level 0 (base obj) */ LVL1NAME VARCHAR2(30), /* name of level 1 object */ LVL2NAME VARCHAR2(30), /* name of level 2 object */ INTCOLS NUMBER NOT NULL, /* for table object, number of all types cols */ COLS NUMBER, /* for table object, number of user visable cols */ KERNELCOLS NUMBER, /* for table object, number of non zero secol# cols */ TAB_FLAGS NUMBER, /* TAB$.FLAGS */ TRIGFLAG NUMBER, /* TAB$.TRIGFLAG */ ASSOC# NUMBER, /* IOT/OF Associated object */ OBJ_FLAGS NUMBER, /* OBJ$.FLAGS */ TS# NUMBER, /* table space number */ TSNAME VARCHAR2(30), /* table space name */ PROPERTY NUMBER, /* Replication Dictionary Specific Columns */ START_SCN NUMBER NOT NULL, /* SCN at which existence begins */ DROP_SCN NUMBER, /* SCN at which existence ends */ XIDUSN NUMBER, /* src txn which created this object */ XIDSLT NUMBER, XIDSQN NUMBER, FLAGS NUMBER, LOGMNR_SPARE1 NUMBER, LOGMNR_SPARE2 NUMBER, LOGMNR_SPARE3 VARCHAR2(1000), LOGMNR_SPARE4 DATE, LOGMNR_SPARE5 NUMBER, LOGMNR_SPARE6 NUMBER, LOGMNR_SPARE7 NUMBER, LOGMNR_SPARE8 NUMBER, LOGMNR_SPARE9 NUMBER, /* New in V11 */ PARTTYPE NUMBER, SUBPARTTYPE NUMBER, UNSUPPORTEDCOLS NUMBER, COMPLEXTYPECOLS NUMBER, NTPARENTOBJNUM NUMBER, NTPARENTOBJVERSION NUMBER, NTPARENTINTCOLNUM NUMBER, LOGMNRTLOFLAGS NUMBER, LOGMNRMCV VARCHAR2(30), CONSTRAINT LOGMNRGGC_GTLO_PK PRIMARY KEY(LOGMNR_UID, KEYOBJ#, BASEOBJV#) ) TABLESPACE SYSTEM LOGGING / CREATE INDEX SYSTEM.LOGMNRGGC_I2GTLO ON SYSTEM.LOGMNRGGC_GTLO (logmnr_uid, baseobj#, baseobjv#) TABLESPACE SYSTEM LOGGING / CREATE INDEX SYSTEM.LOGMNRGGC_I3GTLO ON SYSTEM.LOGMNRGGC_GTLO (logmnr_uid, drop_scn) TABLESPACE SYSTEM LOGGING / CREATE TABLE SYSTEM.LOGMNRGGC_GTCS( LOGMNR_UID NUMBER NOT NULL, OBJ# NUMBER NOT NULL, /* table (base) object number */ OBJV# NUMBER NOT NULL, /* table object version */ SEGCOL# NUMBER NOT NULL, /* segcol# of column */ INTCOL# NUMBER NOT NULL, /* intcol# of column */ COLNAME VARCHAR2(30) NOT NULL, /* name of column */ TYPE# NUMBER NOT NULL, /* column type */ LENGTH NUMBER, /* data length */ PRECISION NUMBER, /* data precision */ SCALE NUMBER, /* data scale */ INTERVAL_LEADING_PRECISION NUMBER, /* Interval Leading Precision, if any */ INTERVAL_TRAILING_PRECISION NUMBER, /* Interval trailing precision, if any */ PROPERTY NUMBER, TOID RAW(16), CHARSETID NUMBER, CHARSETFORM NUMBER, TYPENAME VARCHAR2(30), FQCOLNAME VARCHAR2(4000), /* fully-qualified column name */ NUMINTCOLS NUMBER, /* Number of Int Cols */ NUMATTRS NUMBER, ADTORDER NUMBER, LOGMNR_SPARE1 NUMBER, LOGMNR_SPARE2 NUMBER, LOGMNR_SPARE3 VARCHAR2(1000), LOGMNR_SPARE4 DATE, LOGMNR_SPARE5 NUMBER, LOGMNR_SPARE6 NUMBER, LOGMNR_SPARE7 NUMBER, LOGMNR_SPARE8 NUMBER, LOGMNR_SPARE9 NUMBER, /* New for V11. */ COL# NUMBER, XTYPESCHEMANAME VARCHAR2(30), XTYPENAME VARCHAR2(4000), XFQCOLNAME VARCHAR2(4000), XTOPINTCOL NUMBER, XREFFEDTABLEOBJN NUMBER, XREFFEDTABLEOBJV NUMBER, XCOLTYPEFLAGS NUMBER, XOPQTYPETYPE NUMBER, XOPQTYPEFLAGS NUMBER, XOPQLOBINTCOL NUMBER, XOPQOBJINTCOL NUMBER, XXMLINTCOL NUMBER, EAOWNER# NUMBER, EAMKEYID VARCHAR2(64), EAENCALG NUMBER, EAINTALG NUMBER, EACOLKLC RAW(2000), EAKLCLEN NUMBER, EAFLAGS NUMBER, constraint logmnrggc_gtcs_pk primary key(logmnr_uid, obj#, objv#,intcol#) ) TABLESPACE SYSTEM LOGGING / CREATE INDEX SYSTEM.LOGMNRGGC_I2GTCS ON SYSTEM.LOGMNRGGC_GTCS (logmnr_uid, obj#, objv#, segcol#, intcol#) TABLESPACE SYSTEM LOGGING / Rem ======================================================================= Rem End Changes for LogMiner Rem ======================================================================= Rem Rem Add edition column for Service Rem alter table SERVICE$ add (edition varchar2(30)); Rem ================= Rem Begin AQ changes Rem ================= ALTER TABLE sys.aq$_schedules ADD (destq NUMBER); alter table sys.reg$ add (session_key VARCHAR2(1024)); alter sequence invalidation_reg_id$ cache 300 / Rem ================= Rem End AQ changes Rem ================= REM create a table to store the sql errors that occur during parsing so that REM the next time the same bad sql is issued we can look up from this table REM and throw the same error instead of doing a hard parse create table sqlerror$ ( sqlhash varchar(32) not null, error# number not null, errpos# number not null, flags number not null); Rem ======================================================================= Rem Bug 12326358: add spare columns to sqlerror$ Rem ======================================================================= Rem alter table sqlerror$ add ( spare1 number default 0 not null ); alter table sqlerror$ add ( spare2 number default 0 not null ); alter table sqlerror$ add ( spare3 number default 0 not null ); Rem ======================================================================= Rem End of changes for bug 12326358 Rem ======================================================================= Rem ======================================================================= Rem Bug #5842629 : direct path load and direct path export Rem ======================================================================= Rem Rem Bug #11787333: delete records first if they already exist. Rem delete from STMT_AUDIT_OPTION_MAP where option# = 330; delete from STMT_AUDIT_OPTION_MAP where option# = 331; insert into STMT_AUDIT_OPTION_MAP values (330, 'DIRECT_PATH LOAD', 0); insert into STMT_AUDIT_OPTION_MAP values (331, 'DIRECT_PATH UNLOAD', 0); Rem ======================================================================= Rem End Changes for Bug #5842629 Rem ======================================================================= Rem ======================================================================= Rem Begin Changes for Database Replay Rem ======================================================================= Rem Rem add columns to WRR$_CAPTURES and WRR$_REPLAYS Rem Rem wrr$_captures alter table WRR$_CAPTURES add (sqlset_owner varchar2(30)); alter table WRR$_CAPTURES add (sqlset_name varchar2(30)); Rem wrr$_replays alter table WRR$_REPLAYS add (sqlset_owner varchar2(30)); alter table WRR$_REPLAYS add (sqlset_name varchar2(30)); alter table WRR$_REPLAYS add (sqlset_cap_interval number); alter table WRR$_REPLAYS add (filter_set_name varchar2(1000)); alter table WRR$_REPLAY_FILTER_SET add (default_action varchar2(20)); Rem ======================================================================= Rem End Changes for Database Replay Rem ======================================================================= Rem ========================== Rem Begin Bug #8862486 changes Rem ========================== Rem Directory EXECUTE auditing (action #135) Rem Bug #11787333: delete record first if it already exists. Rem delete from audit_actions where action = 135; insert into audit_actions values (135, 'DIRECTORY EXECUTE'); Rem ======================== Rem End Bug #8862486 changes Rem ======================== Rem =========================================================================== Rem add new columns to WRH$_PERSISTENT_QUEUES and WRH$_PERSISTENT_SUBSCRIBERS Rem =========================================================================== alter table WRH$_PERSISTENT_QUEUES add (browsed_msgs number); alter table WRH$_PERSISTENT_QUEUES add (enqueue_cpu_time number); alter table WRH$_PERSISTENT_QUEUES add (dequeue_cpu_time number); alter table WRH$_PERSISTENT_QUEUES add (avg_msg_age number); alter table WRH$_PERSISTENT_QUEUES add (dequeued_msg_latency number); alter table WRH$_PERSISTENT_QUEUES add (enqueue_transactions number); alter table WRH$_PERSISTENT_QUEUES add (dequeue_transactions number); alter table WRH$_PERSISTENT_QUEUES add (execution_count number); alter table WRH$_PERSISTENT_SUBSCRIBERS add (avg_msg_age number); alter table WRH$_PERSISTENT_SUBSCRIBERS add (browsed_msgs number); alter table WRH$_PERSISTENT_SUBSCRIBERS add (elapsed_dequeue_time number); alter table WRH$_PERSISTENT_SUBSCRIBERS add (dequeue_cpu_time number); alter table WRH$_PERSISTENT_SUBSCRIBERS add (dequeue_transactions number); alter table WRH$_PERSISTENT_SUBSCRIBERS add (execution_count number); Rem =========================================================================== Rem End changes to WRH$_PERSISTENT_QUEUES and WRH$_PERSISTENT_SUBSCRIBERS Rem =========================================================================== Rem =========================================================================== Rem add new columns to WRH$_BUFFERED_QUEUES and WRH$_BUFFERED_SUBSCRIBERS Rem =========================================================================== alter table WRH$_BUFFERED_QUEUES add (expired_msgs number); alter table WRH$_BUFFERED_QUEUES add (oldest_msgid raw(16)); alter table WRH$_BUFFERED_QUEUES add (oldest_msg_enqtm timestamp(3)); alter table WRH$_BUFFERED_QUEUES add (queue_state varchar2(25)); alter table WRH$_BUFFERED_QUEUES add (elapsed_enqueue_time number); alter table WRH$_BUFFERED_QUEUES add (elapsed_dequeue_time number); alter table WRH$_BUFFERED_QUEUES add (elapsed_transformation_time number); alter table WRH$_BUFFERED_QUEUES add (elapsed_rule_evaluation_time number); alter table WRH$_BUFFERED_QUEUES add (enqueue_cpu_time number); alter table WRH$_BUFFERED_QUEUES add (dequeue_cpu_time number); alter table WRH$_BUFFERED_QUEUES add (last_enqueue_time timestamp(3)); alter table WRH$_BUFFERED_QUEUES add (last_dequeue_time timestamp(3)); alter table WRH$_BUFFERED_SUBSCRIBERS add (last_browsed_seq number); alter table WRH$_BUFFERED_SUBSCRIBERS add (last_browsed_num number); alter table WRH$_BUFFERED_SUBSCRIBERS add (last_dequeued_seq number); alter table WRH$_BUFFERED_SUBSCRIBERS add (last_dequeued_num number); alter table WRH$_BUFFERED_SUBSCRIBERS add (current_enq_seq number); alter table WRH$_BUFFERED_SUBSCRIBERS add (total_dequeued_msg number); alter table WRH$_BUFFERED_SUBSCRIBERS add (expired_msgs number); alter table WRH$_BUFFERED_SUBSCRIBERS add (message_lag number); alter table WRH$_BUFFERED_SUBSCRIBERS add (elapsed_dequeue_time number); alter table WRH$_BUFFERED_SUBSCRIBERS add (dequeue_cpu_time number); alter table WRH$_BUFFERED_SUBSCRIBERS add (last_dequeue_time timestamp(3)); alter table WRH$_BUFFERED_SUBSCRIBERS add (oldest_msgid raw(16)); alter table WRH$_BUFFERED_SUBSCRIBERS add (oldest_msg_enqtm timestamp(3)); Rem =========================================================================== Rem End changes to WRH$_BUFFERED_QUEUES and WRH$_BUFFERED_SUBSCRIBERS Rem =========================================================================== Rem ========================================================================== Rem Begin advisor framework / SPA changes Rem ========================================================================== Rem in 11.2, we added a new parameter EXECUTE_COUNT to control the Rem execution count in the sql analyze when doing SPA. BEGIN -- add new parameters to existing tasks. Note that the definition -- of these two parameters will be added later during upgrade -- when dbms_advisor.setup_repository is called. EXECUTE IMMEDIATE q'#INSERT INTO wri$_adv_parameters (task_id, name, value, datatype, flags) (SELECT t.id, 'EXECUTE_COUNT', 'UNUSED', 1, 8 FROM wri$_adv_tasks t WHERE t.advisor_name = 'SQL Performance Analyzer' AND NOT EXISTS (SELECT 0 FROM wri$_adv_parameters p WHERE p.task_id = t.id and p.name = 'EXECUTE_COUNT'))#'; -- handle exception when upgrading from 9i. The advisor tables do not exist EXCEPTION WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL; ELSE RAISE; END IF; END; / Rem ========================================================================== Rem End advisor framework / SPA changes Rem ========================================================================== Rem ************************************************************************* Rem Resource Manager related changes - BEGIN Rem ************************************************************************* DECLARE stmt VARCHAR2(200); BEGIN -- if this column already exists, then the following updates are unnecessary stmt := 'alter table resource_plan_directive$ ' || 'add (parallel_queue_timeout number)'; execute immediate stmt; update resource_plan_directive$ set max_active_sess_target_p1 = 4294967295; -- This part of the procedure relies on the success of the alter -- table above. Unless this is done as an execute immediate, -- the PL/SQL will not compile, as parallel_queue_timeout does -- not exist. stmt := 'update resource_plan_directive$ set ' || 'parallel_queue_timeout = 4294967295'; execute immediate stmt; commit; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -1430 THEN RETURN; ELSE RAISE; END IF; END; / Rem Update WRH$_RSRC_CONSUMER_GROUP (basis for dba_hist_rsrc_consumer_group) alter table WRH$_RSRC_CONSUMER_GROUP add (pqs_queued number); alter table WRH$_RSRC_CONSUMER_GROUP add (pq_queued_time number); alter table WRH$_RSRC_CONSUMER_GROUP add (pq_queue_time_outs number); alter table WRH$_RSRC_CONSUMER_GROUP add (pqs_completed number); alter table WRH$_RSRC_CONSUMER_GROUP add (pq_servers_used number); alter table WRH$_RSRC_CONSUMER_GROUP add (pq_active_time number); Rem Update WRH$_RSRC_PLAN (basis for dba_hist_rsrc_plan) alter table WRH$_RSRC_PLAN add (parallel_execution_managed varchar2(4)); Rem This is needed for bug #9207475 to allow AWR snapshots to include Rem the currently active plan alter table WRH$_RSRC_PLAN modify (end_time date null); Rem ************************************************************************* Rem Resource Manager related changes - END Rem ************************************************************************* Rem ************************************************************************* Rem Change the lengths of module and action Rem ************************************************************************* alter table SQLOBJ$AUXDATA modify (module varchar2(64)); alter table SQLOBJ$AUXDATA modify (action varchar2(64)); alter table WRH$_ACTIVE_SESSION_HISTORY_BL modify (module varchar2(64)); alter table WRH$_ACTIVE_SESSION_HISTORY_BL modify (action varchar2(64)); alter table WRH$_ACTIVE_SESSION_HISTORY modify (module varchar2(64)); alter table WRH$_ACTIVE_SESSION_HISTORY modify (action varchar2(64)); alter table WRI$_ADV_SQLT_STATISTICS modify (module varchar2(64)); alter table WRI$_ADV_SQLT_STATISTICS modify (action varchar2(64)); alter table WRI$_SQLSET_STATEMENTS modify (module varchar2(64)); alter table WRI$_SQLSET_STATEMENTS modify (action varchar2(64)); alter table WRR$_REPLAY_DIVERGENCE modify (module varchar2(64)); alter table WRR$_REPLAY_DIVERGENCE modify (action varchar2(64)); alter table WRH$_SQLSTAT modify (module varchar2(64)); alter table WRH$_SQLSTAT modify (action varchar2(64)); alter table WRH$_SQLSTAT_BL modify (module varchar2(64)); alter table WRH$_SQLSTAT_BL modify (action varchar2(64)); alter table STREAMS$_COMPONENT_EVENT_IN modify (module_name varchar2(64)); alter table STREAMS$_COMPONENT_EVENT_IN modify (action_name varchar2(64)); alter table STREAMS$_PATH_BOTTLENECK_OUT modify (module_name varchar2(64)); alter table STREAMS$_PATH_BOTTLENECK_OUT modify (action_name varchar2(64)); alter type SQLSET_ROW modify attribute module varchar2(64) cascade; alter type SQLSET_ROW modify attribute action varchar2(64) cascade; -- type alert_type is used for AQ messages -- Turn ON the event to enable DDL on AQ tables alter session set events '10851 trace name context forever, level 1'; alter type sys.alert_type modify attribute module_id varchar2(64) cascade; -- Turn OFF the event to disable DDL on AQ tables alter session set events '10851 trace name context off'; Rem ************************************************************************* Rem END Change the lengths of module and action Rem ************************************************************************* Rem ************************************************************************* Rem WRH$_ACTIVE_SESSION_HISTORY changes Rem - Add columns to ASH Rem ************************************************************************* alter table WRH$_ACTIVE_SESSION_HISTORY add (dbreplay_file_id NUMBER); alter table WRH$_ACTIVE_SESSION_HISTORY add (dbreplay_call_counter NUMBER); alter table WRH$_ACTIVE_SESSION_HISTORY add (px_flags NUMBER); alter table WRH$_ACTIVE_SESSION_HISTORY_BL add (dbreplay_file_id NUMBER); alter table WRH$_ACTIVE_SESSION_HISTORY_BL add (dbreplay_call_counter NUMBER); alter table WRH$_ACTIVE_SESSION_HISTORY_BL add (px_flags NUMBER); Rem ************************************************************************* Rem END WRH$_ACTIVE_SESSION_HISTORY Rem ************************************************************************* Rem ************************************************************************* Rem Bug 9766219 - BEGIN Rem ************************************************************************* BEGIN EXECUTE IMMEDIATE 'REVOKE EXECUTE ON SYS.URIFACTORY FROM PUBLIC'; EXECUTE IMMEDIATE 'GRANT EXECUTE ON SYS.URIFACTORY TO PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / Rem ************************************************************************* Rem Bug 9766219 - END Rem ************************************************************************* Rem ************************************************************************* Rem OLAP changes - BEGIN Rem ************************************************************************* -- More limited grants will occur in olaptf.sql BEGIN EXECUTE IMMEDIATE 'REVOKE ALL ON OLAP_TABLE FROM PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'REVOKE ALL ON CUBE_TABLE FROM PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'REVOKE ALL ON OLAPRC_TABLE FROM PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'REVOKE ALL ON OLAP_SRF_T FROM PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'REVOKE ALL ON OLAP_NUMBER_SRF FROM PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'REVOKE ALL ON OLAP_EXPRESSION FROM PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'REVOKE ALL ON OLAP_TEXT_SRF FROM PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'REVOKE ALL ON OLAP_EXPRESSION_TEXT FROM PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'REVOKE ALL ON OLAP_DATE_SRF FROM PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'REVOKE ALL ON OLAP_EXPRESSION_DATE FROM PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'REVOKE ALL ON OLAP_BOOL_SRF FROM PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'REVOKE ALL ON OLAP_EXPRESSION_BOOL FROM PUBLIC'; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -04042, -1927, -942, -4045 ) THEN NULL; ELSE RAISE; END IF; END; / Rem ************************************************************************* Rem OLAP changes - END Rem ************************************************************************* Rem ************************************************************************* Rem WRM$_SNAPSHOT changes - BEGIN Rem ************************************************************************* Rem add snap_timezone column to wrm$_snapshot alter table WRM$_SNAPSHOT add (snap_timezone interval day(0) to second(0)); Rem ************************************************************************* Rem WRM$_SNAPSHOT changes - END Rem ************************************************************************* Rem ************************************************************************* Rem Remove password from Remote Scheduler User - BEGIN Rem ************************************************************************* UPDATE user$ set password=null,spare4=null where name='REMOTE_SCHEDULER_AGENT'; commit; Rem ************************************************************************* Rem Remove password from Remote Scheduler User - END Rem ************************************************************************* Rem ************************************************************************* Rem BEGIN: Insert users in default_pwd$ Rem ************************************************************************* Rem Created Procedure for inserting into SYS.DEFAULT_PWD$ CREATE OR REPLACE PROCEDURE insert_into_defpwd (tuser_name IN VARCHAR2, tpwd_verifier IN VARCHAR2, tpv_type IN PLS_INTEGER DEFAULT 0 ) AUTHID CURRENT_USER IS m_sql_stmt VARCHAR2(4000); BEGIN m_sql_stmt := 'insert into SYS.DEFAULT_PWD$ values(:1, :2, :3)'; EXECUTE IMMEDIATE m_sql_stmt USING tuser_name, tpwd_verifier, tpv_type; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN ( -00001) THEN NULL; --ignore unique constraint violation DBMS_OUTPUT.PUT_LINE('User: '||tuser_name||' already exists'); ELSE RAISE; END IF; END; / Rem Insert values into SYS.DEFAULT_PWD$ exec insert_into_defpwd('ADLDEMO', '147215F51929A6E8'); exec insert_into_defpwd('APR_USER', '0E0840494721500A'); exec insert_into_defpwd('ARGUSUSER', 'AB1079A1727006AD'); exec insert_into_defpwd('AUDIOUSER', 'CB4F2CEC5A352488'); exec insert_into_defpwd('CATALOG', '397129246919E8DA'); exec insert_into_defpwd('CDEMO82', '7299A5E2A5A05820'); exec insert_into_defpwd('CDEMOCOR', '3A34F0B26B951F3F'); exec insert_into_defpwd('CDEMORID', 'E39CEFE64B73B308'); exec insert_into_defpwd('CDEMOUCB', 'CEAE780F25D556F8'); exec insert_into_defpwd('CFLUENTDEV', 'D930962979E34C47'); exec insert_into_defpwd('COMPANY', '402B659C15EAF6CB'); exec insert_into_defpwd('DEMO', '4646116A123897CF'); exec insert_into_defpwd('EMP', 'B40C23C6E2B4EA3D'); exec insert_into_defpwd('EVENT', '7CA0A42DA768F96D'); exec insert_into_defpwd('FINANCE', '6CBBF17292A1B9AA'); exec insert_into_defpwd('FND', '0C0832F8B6897321'); exec insert_into_defpwd('GPFD', 'BA787E988F8BC424'); exec insert_into_defpwd('GPLD', '9D561E4D6585824B'); exec insert_into_defpwd('HLW', '855296220C095810'); exec insert_into_defpwd('IMAGEUSER', 'E079BF5E433F0B89'); exec insert_into_defpwd('IMEDIA', '8FB1DC9A6F8CE827'); exec insert_into_defpwd('JMUSER', '063BA85BF749DF8E'); exec insert_into_defpwd('MGMT_VIEW', '919E8A172B2AAB87'); exec insert_into_defpwd('MIGRATE', '5A88CE52084E9700'); exec insert_into_defpwd('MILLER', 'D0EFCD03C95DF106'); exec insert_into_defpwd('MMO2', 'AE128772645F6709'); exec insert_into_defpwd('MODTEST', 'BBFF58334CDEF86D'); exec insert_into_defpwd('MOREAU', 'CF5A081E7585936B'); exec insert_into_defpwd('MTSSYS', '6465913FF5FF1831'); exec insert_into_defpwd('MXAGENT', 'C5F0512A64EB0E7F'); exec insert_into_defpwd('NAMES', '9B95D28A979CC5C4'); exec insert_into_defpwd('OCITEST', 'C09011CB0205B347'); exec insert_into_defpwd('OEMADM', '9DCE98CCF541AAE6'); exec insert_into_defpwd('OLAPDBA', '1AF71599EDACFB00'); exec insert_into_defpwd('OLAPSVR', 'AF52CFD036E8F425'); exec insert_into_defpwd('PERFSTAT', 'AC98877DE1297365'); exec insert_into_defpwd('PO8', '7E15FBACA7CDEBEC'); exec insert_into_defpwd('PORTAL30_ADMIN', '7AF870D89CABF1C7'); exec insert_into_defpwd('PORTAL30_PS', '333B8121593F96FB'); exec insert_into_defpwd('PORTAL30_SSO_ADMIN', 'BDE248D4CCCD015D'); exec insert_into_defpwd('POWERCARTUSER', '2C5ECE3BEC35CE69'); exec insert_into_defpwd('PRIMARY', '70C3248DFFB90152'); exec insert_into_defpwd('PUBSUB', '80294AE45A46E77B'); exec insert_into_defpwd('RE', '933B9A9475E882A6'); exec insert_into_defpwd('RMAIL', 'DA4435BBF8CAE54C'); exec insert_into_defpwd('SAMPLE', 'E74B15A3F7A19CA8'); exec insert_into_defpwd('SDOS_ICSAP', 'C789210ACC24DA16'); exec insert_into_defpwd('TAHITI', 'F339612C73D27861'); exec insert_into_defpwd('TSDEV', '29268859446F5A8C'); exec insert_into_defpwd('TSUSER', '90C4F894E2972F08'); exec insert_into_defpwd('USER0', '8A0760E2710AB0B4'); exec insert_into_defpwd('USER1', 'BBE7786A584F9103'); exec insert_into_defpwd('USER2', '1718E5DBB8F89784'); exec insert_into_defpwd('USER3', '94152F9F5B35B103'); exec insert_into_defpwd('USER4', '2907B1BFA9DA5091'); exec insert_into_defpwd('USER5', '6E97FCEA92BAA4CB'); exec insert_into_defpwd('USER6', 'F73E1A76B1E57F3D'); exec insert_into_defpwd('USER7', '3E9C94488C1A3908'); exec insert_into_defpwd('USER8', 'D148049C2780B869'); exec insert_into_defpwd('USER9', '0487AFEE55ECEE66'); exec insert_into_defpwd('UTLBSTATU', 'C42D1FA3231AB025'); exec insert_into_defpwd('VIDEOUSER', '29ECA1F239B0F7DF'); exec insert_into_defpwd('VIF_DEVELOPER', '9A7DCB0C1D84C488'); exec insert_into_defpwd('VIRUSER', '404B03707BF5CEA3'); exec insert_into_defpwd('VRR1', '811C49394C921D66'); exec insert_into_defpwd('WKPROXY', 'B97545C4DD2ABE54'); commit; drop procedure insert_into_defpwd; Rem ************************************************************************* Rem END: Insert users in default_pwd$ Rem ************************************************************************* Rem ************************************************************************* Rem Optimizer changes - BEGIN Rem ************************************************************************* -- lrg 4545922: Turn ON the event to disable the partition check alter session set events '14524 trace name context forever, level 1'; declare type numtab is table of number; tobjns numtab; tobjn number; property number := 0; sqltxt varchar2(32767); tmp_created boolean := FALSE; begin -- check whether we are already in new schema -- if we have upgraded it, this will throw 904 error that will be caught begin execute immediate q'#select synopsis# from wri$_optstat_synopsis$ where rownum < 2 #'; exception when others then if (sqlcode = -904) then -- ORA-904 during reupgrde: "S"."SYNOPSIS#": invalid identifier -- has been upgraded successfully before, do nothing return; elsif (sqlcode = -942) then -- ORA-00942: table or view does not exist -- wri$_optstat_synopsis$ does not exist. this may be caused by -- errors in last upgrade: wri$_optstat_synopsis$ is dropped but -- tmp_wri$_optstat_synopsis$ is failed to replace it. -- recreate wri$_optstat_synopsis$ (we might lose old data) execute immediate q'#create table wri$_optstat_synopsis$ ( bo# number not null, group# number not null, intcol# number not null, hashvalue number not null ) partition by range(bo#) subpartition by hash(group#) subpartitions 32 ( partition p0 values less than (0) ) tablespace sysaux pctfree 1 enable row movement #'; return; else raise; end if; end; -- there exists synopsis table in old schema execute immediate q'#create table tmp_wri$_optstat_synopsis$ ( bo# number not null, group# number not null, intcol# number not null, hashvalue number not null ) partition by range(bo#) subpartition by hash(group#) subpartitions 32 ( partition p0 values less than (0) ) tablespace sysaux pctfree 1 enable row movement #'; tmp_created := TRUE; -- get all the partitioned tables that have synopses -- must order by bo# because we are going to create partitions -- using "add partition" statement select distinct bo# bulk collect into tobjns from sys.wri$_optstat_synopsis_head$ order by bo#; -- create range partition for each partitioned table for i in 1..tobjns.count loop tobjn := tobjns(i); -- check whethre low boundary has been created if (i = 1 or tobjns(i-1) <> tobjn - 1) then -- we haven't created a partition with highvalue tobjn yet -- check whether objn-1 is a partitioned table begin select bitand(t.property, 32) into property from sys.obj$ o, sys.tab$ t where o.obj# = tobjn-1 and o.type# = 2 and o.obj# = t.obj#; exception when no_data_found then property := 0; end; if (property = 32) then -- tobjn-1 is a partitioned table sqltxt := 'alter table tmp_wri$_optstat_synopsis$' || ' add partition p_' || to_char(tobjn - 1) || ' values less than (' || to_char(tobjn) || ')'; else sqltxt := 'alter table tmp_wri$_optstat_synopsis$' || ' add partition p_' || to_char(tobjn - 1) || ' values less than (' || to_char(tobjn) || ')' || ' subpartitions 1'; end if; execute immediate sqltxt; end if; -- high boundary sqltxt := 'alter table tmp_wri$_optstat_synopsis$' || ' add partition p_' || tobjn || ' values less than (' || to_char(tobjn + 1) || ')'; execute immediate sqltxt; end loop; execute immediate q'#insert /*+ append */ into tmp_wri$_optstat_synopsis$ select /*+ full(h) full(s) leading(h s) use_hash(h s) */ h.bo#, h.group#, h.intcol#, s.hashvalue from wri$_optstat_synopsis_head$ h, wri$_optstat_synopsis$ s where h.synopsis# = s.synopsis# #'; execute immediate q'# drop table wri$_optstat_synopsis$ #'; execute immediate q'# rename tmp_wri$_optstat_synopsis$ to wri$_optstat_synopsis$ #'; exception when others then if (tmp_created) then execute immediate 'drop table tmp_wri$_optstat_synopsis$'; end if; raise; end; / -- Turn OFF the event to disable the partition check alter session set events '14524 trace name context off'; -- #(9577300) Column group usage Rem #(10410249) create col_group_usage$ as a non iot table variable found_iot number; -- Check if col_group_usage$ is an iot begin select count(*) into :found_iot from user_tables where table_name ='COL_GROUP_USAGE$' and iot_type = 'IOT'; end; / -- Save the contents if it is an iot begin if (:found_iot != 0) then execute immediate q'# create table col_group_usage$_sav as select * from col_group_usage$ #'; end if; end; / -- Drop the iot begin if (:found_iot != 0) then execute immediate q'# drop table col_group_usage$ purge #'; end if; end; / -- Create it as non iot. It may fail if it already exists and if it not an iot. -- Upgrade ignores the error create table col_group_usage$ ( obj# number, /* object number */ /* * We store intcol# separated by comma in the following column. * We allow upto 32 (CKYMAX) columns in the group. intcol# can be * upto 1000 (or can be 64K in future or with some xml virtual columns?). * Assume 5 digits for intcol# and one byte for comma. * So max length would be 32 * (5+1) = 192 */ cols varchar2(192 char), /* columns in the group */ timestamp date, /* timestamp of last time this row was changed */ flags number, /* various flags */ constraint pk_col_group_usage$ primary key (obj#, cols)) storage (initial 200K next 100k maxextents unlimited pctincrease 0) / -- Restore the contents. Fails if col_group_usage$ was not an iot -- (col_group_usage$_sav will not be created in this case) -- upgrade does not ignore 942 errors during insert and hence explicitly -- ignore it. begin execute immediate q'# insert into col_group_usage$ select * from col_group_usage$_sav #'; exception when others then if (sqlcode = -942) then null; else raise; end if; end; / -- Drop the staging table. Fails if col_group_usage$ was not an iot drop table col_group_usage$_sav; Rem ************************************************************************* Rem Optimizer changes - END Rem ************************************************************************* Rem Rem Register import callouts for metadata tables and views Rem create table impcalloutreg$ /* register import callouts */ ( package varchar2(30) not null, /* pkg implementing callouts */ schema varchar2(30) not null, /* pkg's owning schema */ tag varchar2(30) not null, /* mandatory component identifier */ class number not null, /* 1=system, 3=object instance */ /* (2=schema support deferred) */ level# number default 1000 not null, /* determines calling order for */ /* multiple pkgs registered at same callout pt: lower first */ flags number not null, /* Only used when class=3 */ /* See dbmsdp.sql for flags definitions */ /* 0x01: KU$_ICRFLAGS_IS_EXPR: tgt_object is an expression to be evaluated */ /* with LIKE operator. Only valid for tables (not views) */ /* 0x02: KU$_ICRFLAGS_EARLY_IMPORT: tgt_object will be imported and its */ /* post-instance callout executed before import of user tables */ /* 0x04: KU$_ICRFLAGS_GET_DEPENDENTS: child dependents of tgt_object (eg, */ /* indexes, grants, constraints, etc) will be fetched at export time */ /* Only valid for tables (not views) */ /* 0x08: KU$_ICRFLAGS_EXCLUDE: tgt_object should not be exported when it */ /* matches a wildcard registration via flag KU$_ICRFLAGS_IS_EXPR */ /* 0x10: KU$_ICRFLAGS_XDB_NO_TTS: tgt_object is exported only if the XDB */ /* tablespace is not transportable (xdb use only) */ tgt_schema varchar2(30), /* for class 2/3, the target schema or */ /* schema of the target object respectively */ tgt_object varchar2(30), /* for class 3, the name of the tgt obj. */ tgt_type number, /* type of obj as defined in KQD.H. Must be */ /* table, view, type, pkg or proc */ cmnt varchar2(2000) not null /* mandatory component description */ ) / Rem Rem Metadata API changes Rem alter table metascript$ add ( r1seq# number, /* sequence number prerequisite step */ r2seq# number /* sequence number prerequisite step */ ) / Rem ************************************************************************* Rem Increment AWR version for 11.2.0.2 Rem ************************************************************************* Rem ======================================================= Rem == Update the SWRF_VERSION to the current version. == Rem == (11gR202 = SWRF Version 5) == Rem == This step must be the last step for the AWR == Rem == upgrade changes. Place all other AWR upgrade == Rem == changes above this. == Rem ======================================================= BEGIN EXECUTE IMMEDIATE 'UPDATE wrm$_wr_control SET swrf_version = 5'; COMMIT; EXCEPTION WHEN OTHERS THEN IF (SQLCODE = -942) THEN NULL; ELSE RAISE; END IF; END; / Rem ************************************************************************* Rem End Increment AWR version Rem ************************************************************************* Rem ************************************************************************* Rem AWR report accessibility changes - BEGIN Rem ************************************************************************* alter type AWRRPT_HTML_TYPE modify attribute output varchar2(8000 CHAR) cascade; Rem ************************************************************************* Rem AWR report accessibility changes - END Rem ************************************************************************* Rem ************************************************************************* Rem END c1102000.sql Rem *************************************************************************