Rem
Rem $Header: rdbms/admin/spup816.sql /main/7 2010/04/20 10:50:41 kchou Exp $
Rem
Rem spup816.sql
Rem
Rem Copyright (c) 2000, 2010, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem spup816.sql - 8.1.6 to 8.1.7 upgrade script
Rem
Rem DESCRIPTION
Rem Upgrades the Statspack schema to the 8.1.7 schema format
Rem
Rem USAGE
Rem Export the Statspack schema before running this upgrade,
Rem as this is the only way to restore the existing data.
Rem A downgrade script is not provided.
Rem
Rem Disable any scripts which use Statspack while the upgrade script
Rem is running.
Rem
Rem If you have significant amount of data in the PERFSTAT schema,
Rem consider altering the session to use a large rollback segment.
Rem
Rem Ensure there is plenty of free space in the tablespace
Rem where the schema resides.
Rem
Rem This script should be run when connected as SYSDBA
Rem
Rem This upgrade script should only be run once.
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem kchou 04/20/10 - BUG# 9559470 Possible SQL Injection
Rem kchou 04/08/10 - Security Fix: 2nd Order SQL Injections: Bug#
Rem 9559470
Rem cdialeri 03/02/04 - 3513994: 3473979, 3483751
Rem cdialeri 04/21/01 - Split log files
Rem cdialeri 04/06/00 - 1261813
Rem cdialeri 03/30/00 - Created
Rem
set verify off
/* ------------------------------------------------------------------------- */
prompt
prompt Warning
prompt ~~~~~~~
prompt Converting existing Statspack data to 8.1.7 format may result in
prompt irregularities when reporting on pre-8.1.7 snapshot data.
prompt This script is provided for convenience, and is not guaranteed to
prompt work on all installations. To ensure you will not lose any existing
prompt Statspack data, export the schema before upgrading. A downgrade
prompt script is not provided. Please see spdoc.txt for more details.
prompt
prompt
prompt Usage Recommendations
prompt ~~~~~~~~~~~~~~~~~~~~~
prompt Disable any programs which run Statspack (including any dbms_jobs),
prompt or this upgrade will fail.
prompt
prompt If you have a significant amount of data in the PERFSTAT schema,
prompt consider using a large rollback segment, and specifying a large
prompt sort_area_size - you will be prompted for both below.
prompt
prompt You will also be prompted for the PERFSTAT password, and for the
prompt tablespace to create any new PERFSTAT tables/indexes.
prompt
prompt You must be connected as a user with SYSDBA privilege to successfully
prompt run this script.
prompt
accept confirmation prompt "Press return before continuing ";
prompt
prompt Please specify the PERFSTAT password
prompt &&perfstat_password
spool spup816a.lis
prompt
prompt Specify the tablespace to create any new PERFSTAT tables and indexes
prompt Tablespace specified &&tablespace_name
prompt
prompt
prompt If you would like to use a large sort_area_size, specify the size in BYTES
prompt (e.g. 1048576), or press return to use the default sort_area_size.
prompt sort_area_size of &&sort_area_size specified
prompt
prompt If you would like to use a large rollback segment, ensure this rollback
prompt segment is online. Specify the segment name, or press return to use any
prompt rollback segment.
prompt Rollback segment &&large_rollback_segment specified
prompt
/* ------------------------------------------------------------------------- */
--
-- Add support for tempfiles - create view
create view V_$TEMPSTATXS as
select ts.name tsname
, tf.name filename
, tm.phyrds
, tm.phywrts
, tm.readtim
, tm.writetim
, tm.phyblkrd
, tm.phyblkwrt
, fw.count wait_count
, fw.time time
from x$kcbfwait fw
, v$tempstat tm
, v$tablespace ts
, v$tempfile tf
where ts.ts# = tf.ts#
and tm.file# = tf.file#
and fw.indx+1 = (tf.file# + (select value from v$parameter where name='db_files'));
create public synonym V$TEMPSTATXS for V_$TEMPSTATXS;
grant select on V$TEMPSTATXS to PERFSTAT;
/* ------------------------------------------------------------------------- */
--
-- Create V_$SQLXS
create view V_$SQLXS as
select max(sql_text) sql_text
, sum(sharable_mem) sharable_mem
, sum(sorts) sorts
, min(module) module
, sum(loaded_versions) loaded_versions
, sum(executions) executions
, sum(loads) loads
, sum(invalidations) invalidations
, sum(parse_calls) parse_calls
, sum(disk_reads) disk_reads
, sum(buffer_gets) buffer_gets
, sum(rows_processed) rows_processed
, address address
, hash_value hash_value
, count(1) version_count
from v$sql
group by hash_value, address;
create public synonym V$SQLXS for V_$SQLXS;
grant select on V$SQLXS to PERFSTAT;
/* ------------------------------------------------------------------------- */
-- Grant PERFSTAT select on V$ views
grant select on V_$SQLTEXT to PERFSTAT;
grant select on V_$PARAMETER to PERFSTAT;
grant select on V_$SYSTEM_PARAMETER to PERFSTAT;
grant select on V_$LATCH_PARENT to PERFSTAT;
/* ------------------------------------------------------------------------- */
prompt Note:
prompt Please check remainder of upgrade log file, which is continued in
prompt the file spup816b.lis
spool off
connect perfstat/&&perfstat_password
spool spup816b.lis
show user
alter session set sort_area_size = &&sort_area_size;
/* ------------------------------------------------------------------------- */
--
-- Add check constraints - stats$statspack_parameter
update STATS$STATSPACK_PARAMETER set snap_level = 0
where snap_level < 5
and snap_level != 0;
update STATS$STATSPACK_PARAMETER set snap_level = 5
where snap_level < 10
and snap_level != 5;
update STATS$STATSPACK_PARAMETER set snap_level = 10
where snap_level > 10;
alter table STATS$STATSPACK_PARAMETER add
constraint STATS$STATSPACK_LVL_CK
check (snap_level in (0, 5, 10));
--
-- Add check constraints - stats$snapshot
update STATS$SNAPSHOT set snap_level = 0
where snap_level < 5
and snap_level != 0;
update STATS$SNAPSHOT set snap_level = 5
where snap_level < 10
and snap_level not in (0, 5);
update STATS$SNAPSHOT set snap_level = 10
where snap_level > 10;
alter table STATS$SNAPSHOT add
constraint STATS$SNAPSHOT_LVL_CK
check (snap_level in (0, 5, 10));
/* ------------------------------------------------------------------------- */
--
-- Add a new idle event
insert into STATS$IDLE_EVENT (event) values ('wakeup time manager');
/* ------------------------------------------------------------------------- */
--
-- Create latch parent
create table STATS$LATCH_PARENT
(snap_id number(6) not null
,dbid number not null
,instance_number number not null
,latch# number not null
,level# number not null
,gets number
,misses number
,sleeps number
,immediate_gets number
,immediate_misses number
,spin_gets number
,sleep1 number
,sleep2 number
,sleep3 number
,constraint STATS$LATCH_PARENT_PK primary key
(snap_id, dbid, instance_number, latch#)
using index tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0)
,constraint STATS$LATCH_PARENT_FK foreign key
(snap_id, dbid, instance_number)
references STATS$SNAPSHOT on delete cascade
) tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
create public synonym STATS$LATCH_PARENT for STATS$LATCH_PARENT;
/* ------------------------------------------------------------------------- */
--
-- Create the TEMPSTATXS table for Statspack - continuation of tempfile
-- support
create table STATS$TEMPSTATXS
(snap_id number(6) not null
,dbid number not null
,instance_number number not null
,tsname varchar2(30) not null
,filename varchar2(513) not null
,phyrds number
,phywrts number
,readtim number
,writetim number
,phyblkrd number
,phyblkwrt number
,wait_count number
,time number
,constraint STATS$TEMPSTATXS_PK primary key
(snap_id, dbid, instance_number, tsname, filename)
using index tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0)
,constraint STATS$TEMPSTATXS_FK foreign key (snap_id, dbid, instance_number)
references STATS$SNAPSHOT on delete cascade
) tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
create public synonym STATS$TEMPSTATXS for STATS$TEMPSTATXS;
/* ------------------------------------------------------------------------- */
--
-- Increase field size
alter table STATS$FILESTATXS modify
(filename varchar2 (513)
);
/* ------------------------------------------------------------------------- */
--
-- Add wtr_slp_count to latch_misses
alter table STATS$LATCH_MISSES_SUMMARY add
(wtr_slp_count number
);
/* ------------------------------------------------------------------------- */
--
-- Add max wait time to session_event
alter table STATS$SESSION_EVENT add
(max_wait number
);
/* ------------------------------------------------------------------------- */
--
-- Add sharable_mem and version_count threshold to stats$snapshot
alter table STATS$SNAPSHOT add
(sharable_mem_th number
,version_count_th number
);
set transaction use rollback segment &&large_rollback_segment;
update stats$snapshot
set sharable_mem_th = 0
, version_count_th = 0
where sharable_mem_th is null
and version_count_th is null;
/* ------------------------------------------------------------------------- */
--
-- Add sharable_mem and version_count threshold to stats$statspack_parameter
alter table STATS$STATSPACK_PARAMETER add
(sharable_mem_th number
,version_count_th number
);
update stats$statspack_parameter
set sharable_mem_th = 1048576
, version_count_th = 20
where sharable_mem_th is null
and version_count_th is null;
alter table STATS$STATSPACK_PARAMETER modify
(sharable_mem_th not null
,version_count_th not null
);
/* ------------------------------------------------------------------------- */
--
-- SGASTAT - rename, and add pool
rename STATS$SGASTAT_SUMMARY to STATS$SGASTAT;
drop public synonym STATS$SGASTAT_SUMMARY;
create public synonym STATS$SGASTAT for STATS$SGASTAT;
alter table STATS$SGASTAT drop primary key drop index;
alter table STATS$SGASTAT drop constraint STATS$SGASTAT_SUMMARY_FK;
alter table STATS$SGASTAT add
(pool varchar2(11)
);
update stats$sgastat
set pool = 'all pools'
where pool is null
and not exists (select 1
from dba_constraints
where constraint_name = 'STATS$SGASTAT_U'
and owner='PERFSTAT');
alter table STATS$SGASTAT add constraint STATS$SGASTAT_U unique
(snap_id, dbid, instance_number, name, pool)
using index tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0);
alter table STATS$SGASTAT add constraint STATS$SGASTAT_FK foreign key
(snap_id, dbid, instance_number)
references STATS$SNAPSHOT on delete cascade;
/* ------------------------------------------------------------------------- */
--
-- SQL Statistics
create table STATS$SQL_STATISTICS
(snap_id number(6) not null
,dbid number not null
,instance_number number not null
,total_sql number not null
,total_sql_mem number not null
,single_use_sql number not null
,single_use_sql_mem number not null
,constraint STATS$SQL_STATISTICS_PK primary key
(snap_id, dbid, instance_number)
using index tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0)
,constraint STATS$SQL_STATISTICS_FK foreign key
(snap_id, dbid, instance_number)
references STATS$SNAPSHOT on delete cascade
) tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
create public synonym STATS$SQL_STATISTICS for STATS$SQL_STATISTICS;
/* ------------------------------------------------------------------------- */
--
-- Provide support for snapping all or just non-default parameters
-- stats$statspack_parameter table
alter table STATS$STATSPACK_PARAMETER add
(all_init varchar2(5)
,constraint STATS$STATSPACK_ALL_INIT_CK
check (all_init in ('true','false','TRUE','FALSE'))
);
update stats$statspack_parameter
set all_init = 'FALSE'
where all_init is null;
alter table STATS$STATSPACK_PARAMETER modify
(all_init not null
);
/* ------------------------------------------------------------------------- */
--
-- Add support for snapping all parameters or visible parameters
-- to stats$snapshot table
alter table STATS$SNAPSHOT add
(all_init varchar2(5)
);
update stats$snapshot
set all_init = 'TRUE'
where all_init is null;
/* ------------------------------------------------------------------------- */
--
-- Normalize the SQLTEXT
-- Create the SQL Text table. The PK will be created during the upgrade,
-- after the table is populated
create table STATS$SQLTEXT
(hash_value number not null
,text_subset varchar2(31) not null
,piece number not null
,sql_text varchar2(64)
,address raw(8)
,command_type number
,last_snap_id number
) tablespace &&tablespace_name
storage (initial 5m next 5m pctincrease 0) pctfree 5 pctused 40;
create public synonym STATS$SQLTEXT for STATS$SQLTEXT;
-- Add the new column to the SQL Summary table
alter table STATS$SQL_SUMMARY add (text_subset varchar2(31));
-- Count & Convert existing data
column num_dist_sql heading 'Num Distinct|SQL statements' format 999,999,999
column num_dist_hash heading 'Num Distinct|Hash Values' format 999,999,999
column tot_sql heading 'Total Num SQL statements' format 999,999,999,999
select count(distinct(hash_value)) num_dist_hash
, count(distinct(hash_value||substr(sql_text,1,31))) num_dist_sql
, count(1) tot_sql
from stats$sql_summary;
prompt
prompt Normalizing SQL data - this may take a while
prompt
/* We use ROWId's for the first part of the convert - lock the table */
lock table stats$sql_summary in exclusive mode;
set transaction use rollback segment &&large_rollback_segment;
declare
l_snap_id number(6);
l_address raw(8);
l_hash_value number;
l_text_length number;
l_text_subset varchar2(31);
l_dbid number;
l_instance_number number;
l_rowid rowid;
l_sql varchar2(1000);
l_cursor integer;
l_text_piece_0 varchar2(64);
l_text_piece_1 varchar2(64);
l_text_piece_2 varchar2(64);
l_text_piece_3 varchar2(64);
l_text_piece_4 varchar2(64);
l_text_piece_5 varchar2(64);
l_text_piece_6 varchar2(64);
l_text_piece_7 varchar2(64);
l_text_piece_8 varchar2(64);
l_text_piece_9 varchar2(64);
l_text_piece_10 varchar2(64);
l_text_piece_11 varchar2(64);
l_text_piece_12 varchar2(64);
l_text_piece_13 varchar2(64);
l_text_piece_14 varchar2(64);
l_text_piece_15 varchar2(64);
cursor distinct_hash_values is
select hash_value
, substr(sql_text,1,31)
, min(rowid)
from stats$sql_summary ss
group by hash_value, substr(sql_text,1,31);
begin
open distinct_hash_values;
loop
/* Get a hash value */
fetch distinct_hash_values into
l_hash_value, l_text_subset, l_rowid;
exit when distinct_hash_values%notfound;
/* Lookup the SQL */
select snap_id, address, length(sql_text)
, substr(sql_text, 1, 64)
, substr(sql_text, 65, 64)
, substr(sql_text, 129, 64)
, substr(sql_text, 193, 64)
, substr(sql_text, 257, 64)
, substr(sql_text, 321, 64)
, substr(sql_text, 385, 64)
, substr(sql_text, 449, 64)
, substr(sql_text, 513, 64)
, substr(sql_text, 577, 64)
, substr(sql_text, 641, 64)
, substr(sql_text, 705, 64)
, substr(sql_text, 769, 64)
, substr(sql_text, 833, 64)
, substr(sql_text, 897, 64)
, substr(sql_text, 961, 39)
into l_snap_id, l_address, l_text_length
, l_text_piece_0, l_text_piece_1, l_text_piece_2, l_text_piece_3
, l_text_piece_4, l_text_piece_5, l_text_piece_6, l_text_piece_7
, l_text_piece_8, l_text_piece_9, l_text_piece_10, l_text_piece_11
, l_text_piece_12, l_text_piece_13, l_text_piece_14, l_text_piece_15
from stats$sql_summary ss
where rowid = l_rowid;
/* Insert the SQL text rows into stats$sqltext */
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 0, l_text_piece_0, l_address, null, l_snap_id );
if l_text_length >= 65 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 1, l_text_piece_1, l_address, null, l_snap_id );
end if;
if l_text_length >= 129 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 2, l_text_piece_2, l_address, null, l_snap_id );
end if;
if l_text_length >= 193 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 3, l_text_piece_3, l_address, null, l_snap_id );
end if;
if l_text_length >= 257 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 4, l_text_piece_4, l_address, null, l_snap_id );
end if;
if l_text_length >= 321 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 5, l_text_piece_5, l_address, null, l_snap_id );
end if;
if l_text_length >= 385 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 6, l_text_piece_6, l_address, null, l_snap_id );
end if;
if l_text_length >= 449 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 7, l_text_piece_7, l_address, null, l_snap_id );
end if;
if l_text_length >= 513 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 8, l_text_piece_8, l_address, null, l_snap_id );
end if;
if l_text_length >= 577 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 9, l_text_piece_9, l_address, null, l_snap_id );
end if;
if l_text_length >= 641 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 10, l_text_piece_10, l_address, null, l_snap_id );
end if;
if l_text_length >= 705 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 11, l_text_piece_11, l_address, null, l_snap_id );
end if;
if l_text_length >= 769 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 12, l_text_piece_12, l_address, null, l_snap_id );
end if;
if l_text_length >= 833 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 13, l_text_piece_13, l_address, null, l_snap_id );
end if;
if l_text_length >= 897 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 14, l_text_piece_14, l_address, null, l_snap_id );
end if;
if l_text_length >= 961 then
insert into stats$sqltext
( hash_value, text_subset, piece, sql_text, address, command_type, last_snap_id )
values
( l_hash_value, l_text_subset, 15, l_text_piece_15, l_address, null, l_snap_id );
end if;
end loop;
close distinct_hash_values;
/* Build the PK index on the newly populated stats$sqltext */
l_cursor := dbms_sql.open_cursor;
l_sql := 'alter table stats$sqltext add constraint stats$sqltext_pk primary key
(hash_value, text_subset, piece)
using index tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0)';
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
/* Reformat old SQL text in stats$sql_summary
It is faster to CTAS than to just update the text_subset and nullify
the sql_text; CTAS also generates less redo and rollback, and
reclaims a lot of disk space
*/
l_sql := 'create table stats$sql_summary_conv
( snap_id, dbid, instance_number
, text_subset
, sharable_mem, sorts, module, loaded_versions, executions
, loads, invalidations, parse_calls, disk_reads, buffer_gets
, rows_processed, address, hash_value, version_count
)
tablespace &&tablespace_name
pctfree 5 pctused 40
storage (initial 5m next 5m pctincrease 0)
as select
snap_id, dbid, instance_number
, substr(sql_text,1,31)
, sharable_mem, sorts, module, loaded_versions, executions
, loads, invalidations, parse_calls, disk_reads, buffer_gets
, rows_processed, address, hash_value, version_count
from stats$sql_summary';
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
/* Make the new text_subset column not null */
l_sql := 'alter table stats$sql_summary_conv modify
(text_subset not null)';
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
/* Re-add the SQL Text column for tidyness */
l_sql := 'alter table stats$sql_summary_conv add
(sql_text varchar2(2000))';
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
/* Drop the old SQL Summary table */
l_sql := 'drop table stats$sql_summary';
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
/* Rename the new/converted table to SQL Summary */
l_sql := 'rename stats$sql_summary_conv to stats$sql_summary';
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
/* Add the primary and foreign keys to the new SQL Summary */
l_sql := 'alter table stats$sql_summary add constraint stats$sql_summary_pk
primary key
(snap_id, dbid, instance_number, hash_value, address)
using index tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0)';
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
l_sql := 'alter table stats$sql_summary add constraint stats$sql_summary_fk
foreign key (snap_id, dbid, instance_number)
references stats$snapshot on delete cascade';
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
dbms_sql.close_cursor(l_cursor);
commit;
exception
when others then
rollback;
raise;
end;
/
select count(distinct(hash_value)) num_dist_hash
, count(distinct(hash_value||text_subset)) num_dist_sql
, count(1) tot_sql
from stats$sqltext
where piece = 0;
/* ------------------------------------------------------------------------- */
--
-- Create rows in stats$database_instance for each instance startup
--
-- Drop existing constraints
alter table STATS$SNAPSHOT
drop constraint STATS$SNAPSHOT_FK;
alter table STATS$STATSPACK_PARAMETER
drop constraint STATS$STATSPACK_PARAMETER_FK;
alter table STATS$DATABASE_INSTANCE
drop constraint STATS$DATABASE_INSTANCE_PK;
--
-- Modify stats$database_instance to add new columns
alter table STATS$DATABASE_INSTANCE add
(snap_id number(6)
,startup_time date
,parallel varchar2(3)
,version varchar2(17)
);
--
-- Update new columns in stats$database_instance and delete pre-existing rows
set transaction use rollback segment &&large_rollback_segment;
update stats$database_instance set
version = 'OLD RECORD'
where version is null;
insert into stats$database_instance
( snap_id
, dbid
, instance_number
, startup_time
, parallel
, version
, db_name
, instance_name
, host_name
)
select sga.snap_id
, sga.dbid
, sga.instance_number
, sga.startup_time
, sga.parallel
, sga.version
, di.db_name
, di.instance_name
, di.host_name
from stats$sgaxs sga
, stats$database_instance di
where sga.name = 'Fixed Size'
and di.instance_number = sga.instance_number
and di.dbid = sga.dbid
and di.version = 'OLD RECORD'
and not exists (select 1
from stats$database_instance di2
where di2.dbid = sga.dbid
and di2.instance_number = sga.instance_number
and nvl(di2.startup_time,
to_date('01-Jan-1960', 'DD-Mon-YYYY'))
= sga.startup_time);
-- Delete old rows from stats$database_instance if they have been
-- converted successfully
delete from stats$database_instance di1
where di1.version = 'OLD RECORD'
and not exists (select dbid, instance_number, startup_time
from stats$sgaxs sga
where sga.dbid = di1.dbid
and sga.instance_number = di1.instance_number
and sga.name = 'Fixed Size'
minus
select dbid, instance_number, startup_time
from stats$database_instance di2
where di2.dbid = di1.dbid
and di2.instance_number = di1.instance_number
and di2.version <> 'OLD RECORD'
);
commit;
--
-- Make the new columns not null
alter table STATS$DATABASE_INSTANCE modify
(snap_id not null
,startup_time not null
,parallel not null
,version not null
);
--
-- Create the new primary key, and a related foreign key
alter table STATS$DATABASE_INSTANCE add constraint STATS$DATABASE_INSTANCE_PK
primary key (dbid, instance_number, startup_time);
alter table STATS$SNAPSHOT add constraint STATS$SNAPSHOT_FK
foreign key (dbid, instance_number, startup_time)
references STATS$DATABASE_INSTANCE on delete cascade;
-- Foreign key for STATS$STATSPACK_PARAMETER is no longer required
/* ------------------------------------------------------------------------- */
--
-- Insert new rows in stats$sga - one set of rows per snapshot
--
-- Rename STATS$SGAXS to STATS$SGA, and change column characteristics
rename STATS$SGAXS to STATS$SGA;
alter table STATS$SGA drop primary key drop index;
alter table STATS$SGA drop constraint STATS$SGAXS_FK;
drop public synonym STATS$SGAXS;
create public synonym STATS$SGA for STATS$SGA;
alter table STATS$SGA modify
(startup_time null
,parallel null
);
set transaction use rollback segment &&large_rollback_segment;
insert into stats$sga
( snap_id
, dbid
, instance_number
, name
, value
)
select s.snap_id
, s.dbid
, s.instance_number
, name
, value
from stats$sga sga
, stats$snapshot s
where sga.dbid = s.dbid
and sga.instance_number = s.instance_number
and sga.startup_time = s.startup_time
and sga.snap_id != s.snap_id
and not exists (select 1
from stats$sga sga
where s.dbid = sga.dbid
and s.instance_number = sga.instance_number
and s.snap_id = sga.snap_id
);
commit;
alter table STATS$SGA add constraint STATS$SGA_PK primary key
(snap_id, dbid, instance_number, name);
alter table STATS$SGA add constraint STATS$SGA_FK foreign key
(snap_id, dbid, instance_number)
references STATS$SNAPSHOT on delete cascade;
alter table STATS$SGA modify
(value not null
);
/* ------------------------------------------------------------------------- */
--
-- Buffer pool table no longer needed
truncate table STATS$BUFFER_POOL;
drop table STATS$BUFFER_POOL;
drop public synonym STATS$BUFFER_POOL;
/* ------------------------------------------------------------------------- */
--
-- Revoke select privileges on statspack objects granted to PUBLIC
declare
sqlstr varchar2(128);
begin
for tbnam in (select atp.table_name
from all_tab_privs atp
, all_tables at
where atp.privilege = 'SELECT'
and atp.table_schema = 'PERFSTAT'
and atp.grantee = 'PUBLIC'
and at.table_name = atp.table_name
and at.owner = atp.table_schema
and at.dropped = 'NO')
loop
-- XXX kchou 4/20/2010 BUG# 9559470 POSSIBLE SQL INJECTION
-- sqlstr := 'revoke select on perfstat.'||tbnam.table_name||' from public';
sqlstr :=
'revoke select on perfstat.' || dbms_assert.enquote_name(tbnam.table_name, FALSE) || ' FROM PUBLIC';
execute immediate sqlstr;
end loop;
end;
/
/* ------------------------------------------------------------------------- */
prompt Note:
prompt Please check the log file of the package recreation, which is
prompt in the file spcpkg.lis
spool off
/* ------------------------------------------------------------------------- */
--
-- Upgrade the package
@@spcpkg
-- End of Upgrade script