Analyzing Oracle database complexity

The first step in an Oracle database migration is to analyze the source database’s complexity. Much of the information required to do this can be obtained by querying the Oracle data dictionary. The Oracle database dictionary is a set of system catalog views which contain metadata about the database. This metadata includes the number and type of different schema objects used, the source code for stored procedures, the server configuration and more.

Visulate for Oracle includes a series of predefined database analysis queries. They run automatically when the database or schema selection changes in the UI.

Database Analysis

Select a database on the homepage of the application to run the database analysis queries

Database instance report

This will run the following queries:

Database Version

List the database version

select banner as "Version" from v$version

Oracle Cloud Autonomous Database Instance

Is the database running on Oracle Cloud infrastructure?

select decode( count(*), 0, 'No',
                            'Yes') as "Autonomous Database"
from dba_objects
where object_name = 'DBMS_CLOUD'

E-Business Suite Schema Detected

Is it an E-Business suite instance?

select decode(count(*), 1, 'Yes',
                           'No') as "EBS Schema"
from dba_tables
where owner='APPLSYS'
and table_name = 'FND_APPLICATION'

Patch History

What patches have been applied to this instance?

select to_char(action_time, 'Mon dd, yyyy hh24:mi') as "Time"
,      action as "Action"
,      namespace as "Namespace"
,      version as "Version"
,      id as "ID"
,      comments as "Comments"
from  sys.REGISTRY$HISTORY
order by action_time

List database links

select owner as "Schema",
db_link as "Database Link",
username as "Username",
host as "Connect String"
from dba_db_links
order by 1, 2

Invalid Objects

Count invalid objects by schema

select owner as "Owner"
,      object_type as "Object Type"
,      count(*) as "Count"
from dba_objects
where status = 'INVALID'
group by owner, object_type
order by owner, object_type

SGA Size

List the total size of the system global area

select round(sum(value/1024/1024/1024), 2)as  "Total Size (GB)"
from v$sga

SGA Free

List the total free space

select round(sum(bytes/1024/1024), 2) as "Free Memory (MB)"
from v$sgastat
where name like '%free memory%'

Database Size

List the size of each tablespace

select nvl(tablespace_name, 'Total') as "Tablespace",
round(sum(bytes)/1024/1024/1024, 2) as "Size (GB)"
from dba_data_files
group by grouping sets((), (tablespace_name))
order by 2 desc

Space Used

List the storage allocation for each schema

select nvl(owner, 'Total') as "Schema",
round(sum(bytes)/1024/1024/1024, 2) as "Size (GB)"
from dba_segments
group by grouping sets((), (owner))
order by 2 desc

System Utilization Statistics

Display system utilization statistics from the operating system

select comments as "Statistic"
,      value
,      to_char(value, 'FM999,999,999,999') as "Value"
from v$osstat

Database Feature Usage

Displays database feature usage statistics

select f.name as "Feature"
,      f.detected_usages as "Times Used"
,      to_char(f.first_usage_date, 'Mon DD, YYYY') as "First Used"
,      to_char(f.last_usage_date, 'Mon DD, YYYY') as "Last Used"
,      f.currently_used as "Used Now"
from dba_feature_usage_statistics f
,    v$database d
where f.detected_usages > 0
and d.dbid = f.dbid
order by f.name

Schema Analysis

Select a database user from the schema drop down to run schema reports.

Schema Status

List the account status (e.g. open, locked or expired) along with the default tablespaces for the schema.

select account_status as "Status"
,      default_tablespace as "Default Tablespace"
,      temporary_tablespace as "Temporary Tablespace"
from dba_users
where username = :owner

Data Types

Count the column data type usage in the schema’s tables and views. Look for Oracle specific data types if you are planning to migrate from Oracle to Postgres or MySQL.

select data_type as "Data Type"
,      count(*) as "Count"
from dba_tab_columns
where owner = :owner
group by data_type
order by data_type

Spatial

List and link to tables and views with spatial columns

select c.table_name as "Object Name"
,      o.object_type as "Type"
,      c.column_name as "Column"
from dba_tab_columns c
,    dba_objects o
where c.owner = :owner
and c.data_type= 'SDO_GEOMETRY'
and o.owner = c.owner
and o.object_name = c.table_name
order by c.table_name, o.object_type, c.column_name

Non Standard Indexes

List non standard indexes

select index_type as "Type"
,      index_name as "Index"
from dba_indexes
where owner = :owner
and index_name like :object_name ESCAPE :esc
and index_type not in ('NORMAL', 'LOB')
order by index_name, index_type

DBMS and UTL Usage

List and link to stored procedures that reference Oracle’s UTL and DBMS packages. These will need to be re-written if you are planning to migrate from Oracle to Postgres or MySQL.

select name as "Name"
,      type as "Type"
,      owner||'/'||type||'/'||name as link
,      count(*) as "Count"
from dba_source
where owner = :owner
and name like :object_name ESCAPE :esc
group by name, type, owner||'/'||type||'/'||name
order by name, type

API Access

The database and schema analysis reports can be initiated via API calls

Database analysis

Call the /api endpoint passing the registered database as a path parameter /api/{database} to run the database reports. Example:

curl -X GET "https://my-domain.com/api/my-db" -H  "accept: application/json" | json_pp

 % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  9354  100  9354    0     0  10653      0 --:--:-- --:--:-- --:--:-- 10641
[
   {
      "description" : "",
      "display" : [
         "Version"
      ],
      "rows" : [
         {
            "Version" : "Oracle Database 11g Release 11.2.0.4.0 - 64bit Production"
         },
         {
            "Version" : "PL/SQL Release 11.2.0.4.0 - Production"
         },

... etc

Schema analysis

Call the /api endpoint passing the registered database and schema as path parameters /api/{database}/{schema} to run the schema reports. Note the schema name is case sensitive. Example:

curl -X GET "https://my-domain.com/api/my-db/MY-SCHEMA" -H  "accept: application/json"| json+pp

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  1621  100  1621    0     0   2315      0 --:--:-- --:--:-- --:--:--  2312
[
   {
      "description" : "",
      "display" : [
         "Status",
         "Default Tablespace",
         "Temporary Tablespace"
      ],
      "rows" : [
         {
            "Default Tablespace" : "RNT_DATA2",
            "Status" : "OPEN",
            "Temporary Tablespace" : "TEMP"
         }
      ],
      "title" : "Schema Status"
   },

... etc