Which Oracle Features Your Schema Actually Uses

Can Do Versus Does

Migration scope is usually set by the wrong question. Someone lists what Oracle is capable of, and the plan grows to cover all of it. The question that determines the actual work is narrower and answerable: what does this schema use.

The gap between the two is most of the estimate. In practice the answers come out lopsided in both directions — a feature everyone treated as central appears in two objects, while something nobody raised in the planning meeting appears in two hundred. Both discoveries change the plan, and both are a few queries away.

Is Anything Actually Partitioned

Partitioning is frequently assumed to be in play because the edition supports it. Ask directly.

SELECT table_name, partitioning_type, subpartitioning_type,
       partition_count
FROM   all_part_tables
WHERE  owner = 'APP'
ORDER  BY partition_count DESC;

An empty result removes a workstream. A result with three tables tells you precisely which ones need a declarative partitioning design in PostgreSQL, and you can look at the partition keys rather than speculating about them. Subpartitioning is worth noting separately, because composite strategies need more thought on the target.

The PL/SQL Constructs That Drive Effort

Not all PL/SQL is equally hard to carry across. A procedure of straightforward DML is close to mechanical; one using collection types, autonomous transactions or bulk operations needs genuine thought. Search the source rather than guessing at the distribution.

SELECT type, name, COUNT(*) AS hits
FROM   all_source
WHERE  owner = 'APP'
  AND  (  UPPER(text) LIKE '%PRAGMA AUTONOMOUS_TRANSACTION%'
       OR UPPER(text) LIKE '%BULK COLLECT%'
       OR UPPER(text) LIKE '%FORALL%'
       OR UPPER(text) LIKE '%IS TABLE OF%'
       OR UPPER(text) LIKE '%PIPELINED%'
       OR UPPER(text) LIKE '%EXECUTE IMMEDIATE%' )
GROUP  BY type, name
ORDER  BY hits DESC;

This produces a ranked list of the objects that will consume your review time. It is a far better basis for an estimate than a total line count, because effort concentrates in a minority of routines and this names them.

Dynamic SQL Is an Unknown, Not a Count

The query above will find EXECUTE IMMEDIATE, and it is important to understand what that finding does and does not mean. A statement assembled at runtime from variables cannot be fully resolved by reading the source. You can see that dynamic SQL happens; you cannot see every statement it will produce.

Treat these as unknowns requiring human inspection rather than as items with an estimate attached. Any tool, including ours, is reading the same text you are, and a construct built at runtime is outside what static analysis can resolve. The right output is a list of places a person must look, and saying so is more useful than a confident number.

The Queries Whose Emptiness Is the Answer

Several checks are valuable precisely when they return nothing, because each empty result retires an assumption.

-- Advanced Queuing: needs a different design on the target
SELECT owner, name, queue_table FROM all_queues WHERE owner = 'APP';

-- Materialized views and their refresh strategy
SELECT owner, mview_name, refresh_mode, refresh_method
FROM   all_mviews WHERE owner = 'APP';

-- Scheduler jobs that have to live somewhere after the move
SELECT owner, job_name, job_type, repeat_interval, enabled
FROM   all_scheduler_jobs WHERE owner = 'APP';

-- Database links, which are often the real dependency surprise
SELECT owner, db_link, host FROM all_db_links;

-- Object types and nested table columns
SELECT owner, type_name, typecode FROM all_types WHERE owner = 'APP';

Database links deserve particular attention. They tend not to appear in planning conversations and they represent a dependency on something outside the schema you are migrating, which means a decision rather than a conversion.

Dependencies on Supplied Packages

Some built-in packages have direct PostgreSQL analogues, some have workable substitutes, and some have neither. Find out which ones this schema leans on before anyone commits to a date.

SELECT referenced_name, COUNT(DISTINCT name) AS used_by_objects
FROM   all_dependencies
WHERE  owner = 'APP'
  AND  referenced_owner = 'SYS'
  AND  referenced_type = 'PACKAGE'
GROUP  BY referenced_name
ORDER  BY used_by_objects DESC;

Common results are unremarkable — output and string utilities have easy equivalents. The ones to look for are the packages where dependency implies a design decision: queuing, scheduling, alerting, anything touching the file system, and the utilities that reach outside the database entirely.

Separate Used From Merely Present

An object existing is not evidence that anything calls it. Schemas accumulate superseded packages, tables that are no longer written to, and views nobody selects from. Every one of them inflates scope if counted.

Cross-reference your inventory against dependency data and, where it is available, against usage statistics, then take the candidates to the people who own the application. The exclusions that stick are the ones somebody confirmed; the ones that come back are the ones nobody owned.

What the Answers Change

Run these queries and the scope stops being a conversation about Oracle and becomes a list about your schema. Three partitioned tables, two materialized views, no queuing, four database links, seventy-one objects with constructs needing review, nine using dynamic SQL that a person must read.

That list is what an estimate should be built on, and it is also what makes an estimate defensible, because every line in it came from the database rather than from a conversation about what the database probably contains.