Estimating the cost of a migration process from Oracle to Postgres Pro is not easy. To obtain a good assessment of this migration cost, ora2pgpro will inspect all database objects, all functions and stored procedures to detect if there are still some objects and PL/SQL code that can not be automatically converted by ora2pgpro.
ora2pgpro has a content analysis mode that inspect the Oracle database to generate a text report on what the Oracle database contains and what can not be exported.
To activate the "analysis and report" mode, you have to use the export type SHOW_REPORT like in the following command:
ora2pgpro -t SHOW_REPORT
Here is a sample report obtained with this command:
--------------------------------------
Ora2Pg: Oracle Database Content Report
--------------------------------------
Version Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Schema HR
Size 880.00 MB
--------------------------------------
Object Number Invalid Comments
--------------------------------------
CLUSTER 2 0 Clusters are not supported and will not be exported.
FUNCTION 40 0 Total size of function code: 81992.
INDEX 435 0 232 index(es) are concerned by the export, others are automatically generated and will
do so on PostgreSQL. 1 bitmap index(es). 230 b-tree index(es). 1 reversed b-tree index(es)
Note that bitmap index(es) will be exported as b-tree index(es) if any. Cluster, domain,
bitmap join and IOT indexes will not be exported at all. Reverse indexes are not exported
too, you may use a trigram-based index (see pg_trgm) or a reverse() function based index
and search. You may also use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops'
operators in your indexes to improve search with the LIKE operator respectively into
varchar, text or char columns.
MATERIALIZED VIEW 1 0 All materialized view will be exported as snapshot materialized views, they
are only updated when fully refreshed.
PACKAGE BODY 2 1 Total size of package code: 20700.
PROCEDURE 7 0 Total size of procedure code: 19198.
SEQUENCE 160 0 Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL
will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
TABLE 265 0 1 external table(s) will be exported as standard table. See EXTERNAL_TO_FDW configuration
directive to export as file_fdw foreign tables or use COPY in your code if you just
want to load data from external files. 2 binary columns. 4 unknown types.
TABLE PARTITION 8 0 Partitions are exported using table inheritance and check constraint. 1 HASH partitions.
2 LIST partitions. 6 RANGE partitions. Note that Hash partitions are not supported.
TRIGGER 30 0 Total size of trigger code: 21677.
TYPE 7 1 5 type(s) are concerned by the export, others are not supported. 2 Nested Tables.
2 Object type. 1 Subtype. 1 Type Boby. 1 Type inherited. 1 Varrays. Note that Type
inherited and Subtype are converted as table, type inheritance is not supported.
TYPE BODY 0 3 Export of type with member method are not supported, they will not be exported.
VIEW 7 0 Views are fully supported, but if you have updatable views you will need to use
INSTEAD OF triggers.
DATABASE LINK 1 0 Database links will not be exported. You may try the dblink perl contrib module or use
the SQL/MED PostgreSQL features with the different Foreign Data Wrapper (FDW) extensions.
Note: Invalid code will not be exported unless the EXPORT_INVALID configuration directive is activated.
Once the database can be analysed, ora2pgpro, by its ability to convert SQL and PL/SQL code from Oracle syntax to Postgres Pro, can go further by estimating the code difficulties and estimate the time necessary to operate a full database migration.
To estimate the migration cost in man-days, ora2pgpro allows you to use a configuration directive ESTIMATE_COST that you can also enable in the command line: --estimate_cost.
This feature can only be used with the SHOW_REPORT, FUNCTION, PROCEDURE, PACKAGE, and QUERY export types.
ora2pgpro -t SHOW_REPORT --estimate_cost
The generated report is same as above but with a new Estimated cost column as follow:
--------------------------------------
Ora2Pg: Oracle Database Content Report
--------------------------------------
Version Oracle Database 10g Express Edition Release 10.2.0.1.0
Schema HR
Size 890.00 MB
--------------------------------------
Object Number Invalid Estimated cost Comments
--------------------------------------
DATABASE LINK 3 0 9 Database links will be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions
using oracle_fdw.
FUNCTION 2 0 7 Total size of function code: 369 bytes. HIGH_SALARY: 2, VALIDATE_SSN: 3.
INDEX 21 0 11 11 index(es) are concerned by the export, others are automatically generated and will do so
on PostgreSQL. 11 b-tree index(es). Note that bitmap index(es) will be exported as b-tree
index(es) if any. Cluster, domain, bitmap join and IOT indexes will not be exported at all.
Reverse indexes are not exported too, you may use a trigram-based index (see pg_trgm) or a
reverse() function based index and search. You may also use 'varchar_pattern_ops', 'text_pattern_ops'
or 'bpchar_pattern_ops' operators in your indexes to improve search with the LIKE operator
respectively into varchar, text or char columns.
JOB 0 0 0 Job are not exported. You may set external cron job with them.
MATERIALIZED VIEW 1 0 3 All materialized view will be exported as snapshot materialized views, they
are only updated when fully refreshed.
PACKAGE BODY 0 2 54 Total size of package code: 2487 bytes. Number of procedures and functions found
inside those packages: 7. two_proc.get_table: 10, emp_mgmt.create_dept: 4,
emp_mgmt.hire: 13, emp_mgmt.increase_comm: 4, emp_mgmt.increase_sal: 4,
emp_mgmt.remove_dept: 3, emp_mgmt.remove_emp: 2.
PROCEDURE 4 0 39 Total size of procedure code: 2436 bytes. TEST_COMMENTAIRE: 2, SECURE_DML: 3,
PHD_GET_TABLE: 24, ADD_JOB_HISTORY: 6.
SEQUENCE 3 0 0 Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL
will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
SYNONYM 3 0 4 SYNONYMs will be exported as views. SYNONYMs do not exists with PostgreSQL but a common workaround
is to use views or set the PostgreSQL search_path in your session to access
object outside the current schema.
user1.emp_details_view_v is an alias to hr.emp_details_view.
user1.emp_table is an alias to hr.employees@other_server.
user1.offices is an alias to hr.locations.
TABLE 17 0 8.5 1 external table(s) will be exported as standard table. See EXTERNAL_TO_FDW configuration
directive to export as file_fdw foreign tables or use COPY in your code if you just want to
load data from external files. 2 binary columns. 4 unknown types.
TRIGGER 1 1 4 Total size of trigger code: 123 bytes. UPDATE_JOB_HISTORY: 2.
TYPE 7 1 5 5 type(s) are concerned by the export, others are not supported. 2 Nested Tables. 2 Object type.
1 Subtype. 1 Type Boby. 1 Type inherited. 1 Varrays. Note that Type inherited and Subtype are
converted as table, type inheritance is not supported.
TYPE BODY 0 3 30 Export of type with member method are not supported, they will not be exported.
VIEW 1 1 1 Views are fully supported, but if you have updatable views you will need to use INSTEAD OF triggers.
--------------------------------------
Total 65 8 162.5 162.5 cost migration units means approximatively 2 man day(s).
The last line shows the total estimated migration code in man-days following the number of migration units estimated for each object. This migration unit represent around five minutes for a Postgres Pro expert. If this is your first migration, you can get it higher with the configuration directive COST_UNIT_VALUE or the --cost_unit_value command line option:
ora2pgpro -t SHOW_REPORT --estimate_cost --cost_unit_value 10
ora2pgpro is also able to give you a migration difficulty level assessment, here is a sample: Migration level: B-5
Migration levels:
A - Migration that might be run automatically
B - Migration with code rewrite and a man-days cost up to 5 days
C - Migration with code rewrite and a man-days cost above 5 days
Technical levels:
1 = trivial: no stored functions and no triggers
2 = easy: no stored functions but with triggers, no manual rewriting
3 = simple: stored functions and/or triggers, no manual rewriting
4 = manual: no stored functions but with triggers or views with code rewriting
5 = difficult: stored functions and/or triggers with code rewriting
This assessment consists of a letter (A or B) to specify if the migration needs manual rewriting or not, and a number from 1 to 5 to give you a technical difficulty level. You have an additional option --human_days_limit to specify the number of man-days limit where the migration level should be set to C to indicate that it needs a huge amount of work and a full project management with migration support. Default is 10 man-days. You can use the configuration directive HUMAN_DAYS_LIMIT to change this default value permanently.
This feature has been developed to help you decide which database to migrate first and the team that must be mobilized to operate the migration.