![]() ![]() To solve this we run the upgrade in the root container and all PDBs using the "" Perl script. What's more, any new PDBs created from the seed will also use the old time zone file. If you follow the example of the non-CDB instance, it will only be upgraded in the root container. In a multitenant environment, the time zone file upgrade must be performed in all containers. SQL> Upgrade the Time Zone File (Multitenant) Once the upgrade is complete, check the time zone file version being used. The following examples show how they could be used for a CDB an non-CDB database. The _TSTZ_TABLES views display the tables that are processed by the time zone file upgrade, and their current upgrade status. Individual tables or schemas can be upgraded, but in this example we use UPGRADE_DATABASE procedure to upgrade all affected tables in the database.ĭo the upgrade of the database file zone file.ĭBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures) ĭBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures) When you've identified the affected tables and determined you are happy to continue, you can end the prepare phase.ĮXEC DBMS_DST.end_prepare Upgrade the Time Zone File (non-CDB) Depending on your use of TIMESTAMP WITH TIME ZONE columns, you might not have any. If you are using custom tables, created with the CREATE_AFFECTED_TABLE, CREATE_ERROR_TABLE, CREATE_TRIGGER_TABLE procedures, then empty those instead.įind tables affected by the upgrade. Notice the DST_SECONDARY_TT_VERSION column is now populated.Įmpty the default tables that hold the affected tables list and errors. ![]() ![]() We can now check the upgrade we are going to attempt. L_tz_version := DBMS_DST.get_latest_timezone_version ĭBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version) In this case we are selecting the latest version. Use the BEGIN_PREPARE procedure, passing in the file version you want to upgrade to. This phase is optional, but it makes sense to at least check what is going to happen. The prepare phase is where you check the impact of a time zone file upgrade, including the tables that will be affected by the upgrade. We can see there is a difference between the current and latest version of the time zone file, so we should consider performing an upgrade. SELECT DBMS_DST.get_latest_timezone_version The latest available version of the timezone file is displayed using the GET_LATEST_TIMEZONE_VERSION function in the DBMS_DST package. It can also be queried from the DATABASE_PROPERTIES view. ![]() SELECT tz_version FROM registry$database This can also be queried from the REGISTRY$DATABASE table. The V$TIMEZONE_FILE view displays the zone file version being used by the database. Remember, if you only deal with dates in your country, and your country has not altered its time zone or daylight saving time policy, this upgrade may not be necessary. From Oracle 11gR2 onward, new time zone files are shipped with upgrades and patches, but they are not automatically applied to the database.Īpplying a change to the database time zone file not only affects the way new data is handled, but potentially alters data stored in TIMESTAMP WITH TIME ZONE columns, so you need to consider the impact of this before upgrading the time zone file.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |