Inventorying an Oracle Estate Before You Migrate

Remembered Inventories Are Wrong

Every migration estimate rests on an inventory of what has to move, and most inventories start as an act of recall — someone who knows the system lists the important parts from memory. That list is always shorter than reality and the difference is usually enough to invalidate whatever estimate was built on it.

Schemas accumulate. Tables stop being written to but keep their rows. Packages get superseded and never dropped. Indexes outlive the report they were built for. None of it is in anybody's mental model, and all of it is in the data dictionary, which will answer properly if asked.

The Mistake That Matters Most

Before any of the queries, one warning that is worth more than the rest of this article. The row counts in ALL_TABLES.NUM_ROWS are optimiser statistics. They are not counts.

They are as fresh as the last statistics gather, which may have been months ago or may never have happened — in which case the column is simply null, or zero. A table holding millions of rows can report nothing at all. Quote that figure as fact in a plan and you have introduced a number no one can defend, in the place where the whole estimate is anchored.

-- Fine for triage: shows scale, and when it was last believable
SELECT table_name, num_rows, last_analyzed
FROM   all_tables
WHERE  owner = 'APP'
ORDER  BY num_rows DESC NULLS LAST;

-- What you quote. Slower, and correct.
SELECT COUNT(*) FROM app.orders;

Use the estimate to rank and the count to commit. For a large estate, count the tables that carry the volume and the ones whose numbers will appear in the plan; the long tail of small tables can stay estimated as long as the document says so.

Objects by Type

Start with the shape of the estate. One query gives you the distribution, which is the first thing any reader of your plan will want.

SELECT object_type, COUNT(*) AS n,
       SUM(CASE WHEN status = 'INVALID' THEN 1 ELSE 0 END) AS invalid
FROM   all_objects
WHERE  owner = 'APP'
GROUP  BY object_type
ORDER  BY n DESC;

Pay attention to the invalid column. Objects that have been invalid for a long time are strong candidates for things nobody uses, and every one you can legitimately exclude is work that disappears from the project rather than work that gets done faster.

Volume, and Where It Sits

Data volume determines load time, and it is almost never evenly distributed. A handful of tables normally hold most of the bytes, and those are the ones whose load strategy matters.

SELECT segment_name, segment_type,
       ROUND(SUM(bytes)/1024/1024/1024, 2) AS gb
FROM   dba_segments
WHERE  owner = 'APP'
GROUP  BY segment_name, segment_type
ORDER  BY SUM(bytes) DESC
FETCH  FIRST 25 ROWS ONLY;

If you lack DBA_SEGMENTS, USER_SEGMENTS covers your own schema. Either way, note that index segments appear here too — useful, because index rebuild time on the target is a real part of the window and frequently omitted from it.

Count LOB Columns Separately

LOB columns deserve their own line because they behave differently from everything else during a load. They move slowly, they dominate memory per row, and they are the usual reason a batch size that works everywhere else has to come down.

SELECT l.table_name, l.column_name, c.data_type,
       ROUND(SUM(s.bytes)/1024/1024, 1) AS lob_mb
FROM   all_lobs l
JOIN   all_tab_columns c
       ON c.owner = l.owner AND c.table_name = l.table_name
      AND c.column_name = l.column_name
LEFT   JOIN dba_segments s
       ON s.segment_name = l.segment_name AND s.owner = l.owner
WHERE  l.owner = 'APP'
GROUP  BY l.table_name, l.column_name, c.data_type
ORDER  BY SUM(s.bytes) DESC NULLS LAST;

A table with a 400GB LOB column is a different planning problem from a 400GB table of scalars, and knowing which you have before you schedule the window is considerably better than learning it during one.

PL/SQL Volume, by Routine

A total line count is a weak input because effort concentrates unevenly. Break it down so you can see the distribution and identify the routines that will take real review time.

SELECT type, name, COUNT(*) AS lines
FROM   all_source
WHERE  owner = 'APP'
GROUP  BY type, name
ORDER  BY lines DESC
FETCH  FIRST 30 ROWS ONLY;

Typically a few packages dominate. Those are where your senior time goes, and naming them early is what lets you sequence the work so the risky part happens while there is still room in the schedule to react.

The Edges People Forget

Four more counts, each of which has derailed a plan by being discovered late.

-- Triggers: conversion effort, and a load-order problem
SELECT table_name, trigger_name, trigger_type, status
FROM   all_triggers WHERE owner = 'APP';

-- Scheduled jobs: they have to live somewhere afterwards
SELECT job_name, job_type, repeat_interval, enabled
FROM   all_scheduler_jobs WHERE owner = 'APP';

-- Sequences: including any no column default references
SELECT sequence_name, last_number, increment_by
FROM   all_sequences WHERE sequence_owner = 'APP';

-- Database links: a dependency outside what you are migrating
SELECT db_link, host, username FROM all_db_links;

Database links are the one to look at hardest. They rarely come up in planning and each one is a dependency on something outside the schema, which makes it a decision rather than a conversion. Sequences matter for a subtler reason: one that no column default names cannot be attributed to a table by a per-table process, so it has to be found at schema level or it will simply be absent afterwards.

Grants Are Part of the Estate

Roles, grants and synonyms are consistently left out of inventories and consistently needed on the first day of testing, when an application connects and cannot see what it expects.

SELECT grantee, privilege, table_name
FROM   all_tab_privs WHERE table_schema = 'APP'
ORDER  BY grantee, table_name;

SELECT owner, synonym_name, table_owner, table_name
FROM   all_synonyms WHERE owner = 'APP';

What the Inventory Is For

Three things, and the first is the most valuable. It shows you what you can decline to migrate — dead tables, invalid packages, archive data that belongs in cold storage. Scope reduction is the largest lever in any migration and the least discussed, because an object you do not carry costs nothing to convert, nothing to test and nothing to maintain.

Second, it separates volume from difficulty. Twelve hundred simple tables are a load-time problem; forty packages using collection types and dynamic SQL are a review-time problem. They consume different resources and they belong in different parts of the schedule.

Third, it gives you numbers somebody else can re-run. When a sceptical reader asks where the object count came from, the answer being a query they can execute themselves is the difference between a plan that holds up and a plan that gets re-litigated every month until it is abandoned.