Business Challenge
The reason for the project is the licence. Move off Oracle, onto Aurora PostgreSQL, stop paying. The assessment comes back saying most of the schema converts automatically, and that number becomes the plan: some weeks of automated conversion, a backlog of manual fixes, done.
The number is accurate. What it does not tell you is what converted means, and the answer is three different things wearing one label.
Some Oracle constructs have a PostgreSQL equivalent and are genuinely translated.
Some have none, and AWS SCT reproduces the Oracle behaviour through an
extension pack installed into the target database — the
aws_oracle_ext schema. Oracle's V$VERSION and
V$INSTANCE views become aws_oracle_ext.v$version and
aws_oracle_ext.v$instance.
Both count as converted. Only the first has actually left Oracle behind. The second has moved Oracle behaviour into a compatibility layer that is now part of your production database and that somebody has to own.
Oracle's TO_CHAR, TO_DATE and TO_NUMBER accept
formatting parameters PostgreSQL does not support. By default SCT
“emulates the usage of these parameters in the converted code”.
There is a setting to use the native PostgreSQL functions instead, and AWS states the consequence plainly: “In this case, the converted code works faster.” So the default is the slower path, chosen for safety, and it applies to every converted call unless somebody decides otherwise before conversion runs.
Oracle's NUMBER has no exact PostgreSQL counterpart. SCT offers to convert
NUMBER primary and foreign key columns to BIGINT, which
“improves the performance of your converted code”.
The caveat is one sentence: “Make sure that your source doesn't include floating point values in these columns to avoid data loss.” Nothing checks it. It is a manual assertion about data you may never have inspected, attached to an option presented as a performance improvement.
ROWID is a physical row address and is “unique to
Oracle”. SCT preserves the information by turning it into a real data
column — and “If no primary key exists, AWS SCT sets the ROWID column
as the primary key.”
That is a sensible rescue for a table that had no key. It is also a schema decision made by a tool, on exactly the tables least likely to have been reviewed, and it is not a translation of anything — it is a new column that did not exist in the source.
Architecture
Three outcomes hide behind one word in the report. Separating them before the project is scoped is most of the work of making the estimate real.
Native conversion
Where PostgreSQL has the same concept, the conversion is genuine. Sequences convert, with an option to populate them with the last value generated on the source so migrated values do not overlap existing ones. Partitioning converts across range, list, multicolumn range, hash and the composite combinations. This is what people assume the whole report means.
Emulation, which is a dependency
Where PostgreSQL has no equivalent, Oracle behaviour is reproduced. System views become
extension-pack objects. Date and number formatting parameters are emulated.
SYSDATE is emulated, and that emulation is time-zone sensitive: if source and
target run in different zones the emulating function “returns different values
compared to the source function” unless you set a default time zone for it.
There is also an option to use the third-party
orafce extension for some functions.
Each of those is a correct engineering decision and a long-term obligation. You have moved off the Oracle licence and onto an Oracle compatibility surface, and only the first of those two has a line item.
Decisions the tool makes on your behalf
Several conversion settings change the shape of the result rather than its correctness, and they are chosen once, before conversion, for the whole schema:
| Setting | What it decides |
|---|---|
| Materialized view conversion | Whether a materialized view stays one or becomes a plain table |
| Convert procedures to functions | Offered because PostgreSQL 10 and earlier had no procedures; changes every call site |
| Ignore disabled triggers and constraints | Whether deactivated source objects are carried across at all |
| Convert system generated constraint names using source names | Whether auto-named constraints keep names your code may reference |
| Generate row ID as identity or character domain | The data type of a column that did not exist in the source |
The materialized view setting is the one worth pausing on. A materialized view converted to a table holds the same rows on day one and never refreshes, so the difference shows up as data quietly going stale rather than as an error.
Why This Architecture Holds Up
None of this is a defect. The extension pack is the right answer to constructs PostgreSQL genuinely lacks, and emulating rather than silently changing semantics is the conservative and correct choice. AWS documents all of it.
The problem is that the conversion percentage gets read as a measure of how much Oracle you have escaped, and it is not. It measures how much did not need a human that week. A schema reported as almost fully converted can arrive on Aurora carrying an Oracle compatibility schema, a slower default path through every date and number format call, a primary key invented by a tool, and one setting that trades correctness for speed on an assertion nobody verified.
None of that fails a test. It runs, and the results match. The bill arrives years later,
when somebody asks why a PostgreSQL database contains an aws_oracle_ext schema
and whether it can be removed.
Key Architecture Decisions
| Decision | Take this | Because |
|---|---|---|
| Reading the assessment | Split the converted total into native, emulated and manual before scoping | Only the first has left Oracle; the second is a dependency with no line item |
| Date and number formatting | Check whether the source uses Oracle-specific format strings; if not, turn emulation off | The default is emulation, and AWS states the native path is faster |
| NUMBER keys to BIGINT | Query the source for non-integer values in those columns before enabling it | The guard is a sentence in the docs, not a check in the tool, and the failure is data loss |
| Tables with no primary key | Choose the key yourself before conversion | Otherwise SCT makes the generated ROWID column the primary key |
| Materialized views | Decide per workload, and record which became plain tables | A view converted to a table stops refreshing; the symptom is stale data, not an error |
| SYSDATE and time zones | Set the default time zone for SYSDATE emulation unless source and target match | The emulating function otherwise returns different values from the source |
| The extension pack | Record it as an owned component with a removal plan, or accept it permanently and say so | It is invisible at runtime, so the decision defaults to permanent by silence |
Closing Thought
The point of leaving Oracle is usually to stop paying for Oracle. A conversion that reproduces Oracle semantics inside PostgreSQL achieves that, and it is a legitimate outcome — the licence is gone and the behaviour is preserved, which is often exactly what the business asked for.
It only goes wrong when nobody names it. Decide, before the conversion runs, whether you are migrating to PostgreSQL or to PostgreSQL emulating Oracle. Both are defensible. The one that hurts is arriving at the second while believing you chose the first.
Comments