Often an Oracle database, even being say 19c, was initially created as 10g and upgraded with scripts over the years. From v$database and v$instance, we can find out the current version, the platform name and when the database was created but how to view the version history of that database? Often simple SQL statements can extract important data that we need. We should just know what table or view to query.
Two important views, which actually came with Oracle 10g, can shed some light on the question above: DBA_HIGH_WATER_MARK_STATISTICS and DBA_REGISTRY_HISTORY.
Let us see what information they provide:
SELECT * from DBA_REGISTRY_HISTORY where version is not null order by 4 desc;
I said above “shed some light” and not “answer the question” as the view came only with 10gR2. So we can see the history from 10.2.0.3 until now (19.13) but whether the database was created initially as 10g, 9i or even as v7 is an open question.
Note that since since 12.1.0.1 Oracle use DBA_REGISTRY_SQLPATCH instead of DBA_REGISTRY_HISTORY to track PSUs and BPs applied to the database. Check the post by Mike Dietrich called DBA_REGISTRY_HISTORY vs DBA_REGISTRY_SQLPATCH.
The view DBA_HIGH_WATER_MARK_STATISTICS is based on the table WRI$_DBU_HIGH_WATER_MARK:
create table WRI$_DBU_HIGH_WATER_MARK
(name varchar2(64) not null,
dbid number not null,
version varchar2(17) not null,
highwater number,
last_value number,
error_count number,
constraint WRI$_DBU_HIGH_WATER_MARK_PK primary key
(name, dbid, version)
using index tablespace SYSAUX
) tablespace SYSAUX
/
Next, we run the following query which shows us along with the database size the database version as well:
SELECT * from DBA_HIGH_WATER_MARK_STATISTICS where name = ‘DB_SIZE’ order by 3 desc;
From DBA_HIGH_WATER_MARK_STATISTICS, we can view several other historical stats about the database: number of user tables, size of the largest segment, maximum number of partitions belonging to an user table, maximum number of partitions belonging to an user index, number of user indexes, maximum number of concurrent sessions seen in the database, maximum number of datafiles, maximum number of tablespaces, maximum number of CPUs and maximum query length.
If the high-water mark statistics are not populated, then execute manually DBMS_FEATURE_USAGE_INTERNAL.SAMPLE_ONE_HWM. The internal package looks like this:
PROCEDURE CLEANUP_DATABASE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
CLEANUP_LOCAL PL/SQL BOOLEAN IN DEFAULT
PROCEDURE EXEC_DB_USAGE_SAMPLING
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
CURR_DATE DATE IN
PROCEDURE SAMPLE_ONE_FEATURE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
FEAT_NAME VARCHAR2 IN
PROCEDURE SAMPLE_ONE_HWM
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
HWM_NAME VARCHAR2 IN
If you would like to update the HWM statistics manually as they are gathered once a week, here is an example:
SQL> SELECT name, highwater, last_value FROM dba_high_water_mark_statistics WHERE name = ‘USER_TABLES’;
NAME HIGHWATER LAST_VALUE
—————————— ———- ———-
USER_TABLES 533 533
SQL> CREATE TABLE JMD (c1 json);
Table created.
SQL> exec dbms_feature_usage_internal.sample_one_hwm(‘USER_TABLES’);
PL/SQL procedure successfully completed.
SQL> SELECT name, highwater, last_value FROM dba_high_water_mark_statistics WHERE name = ‘USER_TABLES’;
NAME HIGHWATER LAST_VALUE
—————————— ———- ———-
USER_TABLES 534 534
As post scriptum, I can say without ever being able to prove it scientifically, a better performing database is one created as a fresh database and not upgraded with scripts. I would always advise to create a new database and transfer the data and all objects from the previous version than just upgrade the database (the data dictionary) with scripts. But with current DB sizes and limited downtime, this is getting more and more difficult to achieve.
Read MoreJulian Dontcheff’s Database Blog
