Scops
Docs Download

Article

How Scops Archives Old Rows in Oracle Without Downtime

2 November 2026

Most large Oracle tables hold years of rows that nobody reads, and those rows are still paid for: in storage, in backup time, in index maintenance, and in every full scan and statistics run. Moving them out keeps production small. Doing that safely on a live system is the hard part.

Why Oracle data archiving ends up as a script

The options people usually reach for each stop short of the whole job.

So it usually comes down to a DELETE loop written in-house. That script has to answer questions nobody wants to find out about in production. What happens if it dies between the insert and the delete? Which child tables must go first? How does it avoid hurting the application at 10:00? How do you prove that every deleted row is in the archive?

Those questions are what we built Archiving in Scops to answer.

The strategy: a second database, and small transactions

Scops copies old rows into a separate Oracle archive database, where they stay queryable with plain SQL. Deleting them from the source is optional. A table that only copies never writes to the source at all.

The database link lives on the archive side, and the statements run there. Nothing is installed on either database server.

Each table runs through the same steps.

1. Check before anything runs

A table runs only after a check passes, and the check reads both databases and changes nothing. A link that does not connect, or a VPD policy that hides rows from the link, blocks the table. For a missing grant, the check gives you the GRANT. Other findings only tell you what to expect, such as a DELETE trigger that will fire for each row.

2. Decide what is old, on the source

A rule picks the rows: older than a number of days, the newest values of a number column to keep, or partitions by name. One extra condition is allowed, such as STATUS = 'CLOSED'. The cutoff is computed on the source, by its own clock.

When the rule column is the partition key, Scops copies whole partitions, and waits 7 days by default after a partition's upper bound, so late rows have time to arrive. It never copies a MAXVALUE partition. A table without partitions is worked through in key bands.

3. Cut the work into rowid chunks

Each partition or band becomes chunks: rowid ranges built from the segment's own extent map, 80 MB each by default. The range comes from DBA_EXTENTS:

SELECT ROWIDTOCHAR(DBMS_ROWID.ROWID_CREATE(
         1, o.data_object_id, e.relative_fno, e.block_id, 0)) AS lo_rowid,
       ROWIDTOCHAR(DBMS_ROWID.ROWID_CREATE(
         1, o.data_object_id, e.relative_fno,
         e.block_id + e.blocks - 1, 32767)) AS hi_rowid
FROM   dba_extents e
JOIN   dba_objects o ON o.owner = e.owner
                    AND o.object_name = e.segment_name
                    AND o.object_type = 'TABLE'
WHERE  e.owner = :owner
AND    e.segment_name = :table_name
AND    e.segment_type = 'TABLE';

Rowid ranges suit this for three reasons. They address physical blocks, so two workers can never hold the same row. WHERE ROWID BETWEEN :lo AND :hi reads just those blocks instead of scanning the table. And each range is one unit of work that can be retried alone. DBA_EXTENTS is expensive, so the map is read once per unit and saved. Workers then claim chunks one at a time.

If the segment moves underneath, for example after an ALTER TABLE MOVE, the saved rowids no longer address the same rows. Scops detects the new data object and does not trust them.

4. Copy and delete in one transaction

This is the core of it. With Delete after archiving on, one chunk moves in one distributed transaction:

  1. Copy the chunk's rows to the archive.
  2. Delete the same chunk, with the same conditions, from the source.
  3. Compare the two row counts. If they differ, roll back and try the chunk again.
  4. Commit both sides together.

A row cannot leave the source before it is in the archive. Beyond the delete's own row locks, Scops locks nothing. If the application holds a row, the delete waits, and over a link that wait ends at DISTRIBUTED_LOCK_TIMEOUT (60 seconds by default) with ORA-02049. The chunk rolls back and goes again later. That counts as a busy source, not a failure, so the application wins.

A table that only copies has another problem: not writing a row twice. A unique key on the archive table handles that. A copy in doubt, such as a retried chunk or a re-sync, leaves out the rows whose key the archive already holds.

5. Respect foreign keys

Deleting a parent row while children still point at it breaks the data model. So linked tables move as a group of up to 50, in a safe order: the deepest tables first, the leading table last. If one table in the group is stopped or blocked, the whole group waits.

A count check is not enough here. An application can move a child from one parent to another in the middle of a move, and the counts still match while a row that was never copied gets deleted. We measured that on our development databases. So linked tables move under SERIALIZABLE, where Oracle refuses the delete with ORA-08177 if such a row changed. A table that other rows point at gets a further check: a hash of the rowids it keeps must be the same before the copy and after the delete.

6. Fail small, and resume

A chunk is one transaction, so a crash leaves it either wholly done or not done at all. The next run picks up what the last one left open. A failing chunk gets up to 3 tries before its unit fails, and that failure stops at the unit: the next one still runs. Running out of space on the archive, such as ORA-01653, fails the unit at once, since retrying cannot help.

Scops never deletes, truncates or drops anything in the archive. Removing a table from Archiving drops nothing either.

Keeping it away from the application

Giving the space back

A DELETE frees blocks inside the segment for new rows. To hand the space back to the tablespace, the Compression add-on moves the table and its indexes from the same screen, at the compression level you choose, so archiving and shrinking are two clicks in one product instead of two scripts.

Questions

Does archiving need the Partitioning option or any Oracle pack?
No. It works on plain tables and needs no Diagnostics or Tuning Pack. The choices you make for the archive tables can need a license there: range partitions need Partitioning, and Advanced (OLTP) compression needs Advanced Compression.

What happens if the job dies in the middle of a chunk?
The chunk is one transaction, so Oracle rolls it back. The next run does that chunk again.

Can I only copy rows and never delete them?
Yes. Delete after archiving is off by default, and a table that only copies never writes to the source.

How do I archive tables with foreign keys?
Add the tables that point at the leading table to its group. Scops orders them with the deepest tables first and moves them together.

Can I try it for free?
The free Community edition includes a 30-day trial of Archiving on 1 table.

Try it

See the Archiving docs for every setting and the grants it needs, or look around the live demo on made-up data. Archiving is an add-on to Scops, an Oracle monitoring and management platform that needs no agents on database servers.

Sources