conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on
-- check if previous
prepare window is ended
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30)
value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
purge dba_recyclebin;
-- clean used tables
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
-- this alter
session might speed up DBMS_DST on some db's
-- see Bug 10209691
/ Bug 12658443
alter session set
"_with_subquery"=materialize;
-- to
avoid the issue in note 1407273.1
alter session set
"_simple_view_merging"=TRUE;
-- start upgrade
window
EXEC DBMS_DST.BEGIN_UPGRADE();
-- the message
-- "An upgrade
window has been successfully started."
-- will be seen
-- check if this
select give no rows, if it does something went wrong
SELECT * FROM sys.dst$error_table;
-- check if this
select gives
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30)
value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
-- gives this
output:
SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM
ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
-- now restart the
database
shutdown immediate
startup
-- at this point the
database can actually be used note however that the
alter session set
"_with_subquery"=materialize;
alter session set
"_simple_view_merging"=TRUE;
-- now upgrade the
tables who need action
set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
-- ouput of this
will be a list of tables like:
SELECT * FROM sys.dst$error_table;
-- if there where no
failures then end the upgrade.
VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'||
:fail);
END;
/
SELECT
PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM
DATABASE_PROPERTIES
WHERE PROPERTY_NAME
LIKE 'DST_%'
ORDER BY
PROPERTY_NAME;
SELECT * FROM
v$timezone_file;
exit;
conn / as sysdba
SELECT VERSION FROM
v$timezone_file;
select TZ_VERSION
from registry$database;
If select TZ_VERSION
from registry$database still shows 4 , then please perform
conn / as sysdba
update
registry$database set TZ_VERSION = (select version FROM v$timezone_file);
commit;
