Learning is not a spectator sport

When we’re dealing with data warehouses and large databases in general, often there are significant benefits to be had from having “older” data in a read only state. When a tablespace is read only, then it only needs to be backed up once (or maybe twice) and then the “SKIP READ ONLY” clause in RMAN backups can keep those backup times nice and snappy.

But anyone that lives in the “real world” of IT knows there is a large difference between an email, or meeting, or statement made by a business rep of:

“This data can be made read only, we will never need to change it”

and that data truly being read only, because many a DBA has had that fateful phone call which goes along the lines of:

“Hey, you know that read only data? It’s OK if we need to make some changes to it right? RIGHT?”

and suddenly we’re in a position where we need to make one (or more) of our tablespaces read write, make our modifications and then back it up again. If your tablespaces are massive, then this might become a headache fast.

I don’t have solution for this dilemma that applies to every scenario, but thought I’d present an option that might be useful for you, which lets you leave existing tablespaces read only, and (hopefully) means you only have to back up a tablespace of a size containing the data changes, not great swathes of data that has been untouched. A lot of this depends on how your partitions map to tablespaces, so your mileage may vary.

To set the scene, I’ll create a couple of tablespaces, one being read write for “active” data, and then other also initially read write, but will soon become read-only hence the suffixes on the names.

SQL> create tablespace demo1_rw datafile 'X:\ORADATA\DB21\PDB21A\DEMO1.DBF' size 500m;

Tablespace created.

SQL> create tablespace demo2_ro datafile 'X:\ORADATA\DB21\PDB21A\DEMO2.DBF' size 500m;

Tablespace created.

Here is my “massive” table, with each of its partitions in tablespace DEMO1_RW representing the initial state of the table and data.

SQL> create table t
  2  partition by list ( pkey )
  3  ( partition p1 values (1) tablespace demo1_rw,
  4    partition p2 values (2) tablespace demo1_rw,
  5    partition p3 values (3) tablespace demo1_rw
  6  ) as
  7  select 1 pkey, owner, object_name, object_id
  8  from dba_objects
  9  union all
 10  select 2 pkey, owner, object_name, object_id
 11  from dba_objects
 12  union all
 13  select 3 pkey, owner, object_name, object_id
 14  from dba_objects;

Table created.

After a few days/months/years, we get buy in from our business reps to move our old data (in partitions p2 and p3) into a tablespace that we can mark read-only.

SQL> alter table t move partition p3 tablespace demo2_ro;

Table altered.

SQL> alter table t move partition p2 tablespace demo2_ro;

Table altered.

SQL> alter tablespace demo2_ro read only;

Tablespace altered.

We backup DEMO2_RO onto some long term storage, and at this point, we’re at the state alluded to in the opening paragraph. Everything is just rosy, until we get a request in from our business reps.

“Hey, we need to fix up that data in partition P3. There is only meant to be rows where the OBJECT_ID was present, and each of the values is incorrectly offset by 1. That’s not a problem right?”

If I make tablespace DEMO_RO read write to make those changes, then once completed, I’ll need to back it up again, which means backing up the data in both partitions P2 and P3. Maybe P2 is huge? Maybe there is dozens of partitions in there, none of which needed to be touched except P3.

So here’s a different avenue of attack that might be useful for you. I’m going to leave DEMO2_RO untouched. I’ll create a new tablespace DEMO3_RO.

SQL> create tablespace demo3_ro datafile 'X:\ORADATA\DB21\PDB21A\DEMO3.DBF' size 500m;

Tablespace created.

Now I’m going to create a table T_P3_FIXUP based on the structure of table T. I’m using the FOR EXCHANGE option to make sure any hidden column definitions are included so that I wont get errors when I do the EXCHANGE.

SQL> create table t_p3_fixup tablespace demo3_ro
  2  for exchange with table t;

Table created.

I’ll populate T_P3_FIXUP with the rows from P3, correcting the data as I go. Using INSERT also means I’m not going to incur any nasty UPDATE costs in case my data was compressed or was tightly packed with a low PCTFREE.

SQL> insert into t_p3_fixup
  2  select 3 pkey, owner, lower(object_name), object_id+1
  3  from dba_objects
  4  where object_id is not null;

82451 rows created.

SQL> commit;

Commit complete.

Now I can swap that table in as a replacement for partition P3, even though P3 is still listed as being in read only tablespace DEMO2_RO, because an EXCHANGE is not moving data, it is just altering data dictionary metadata.

SQL> alter table t
  2  exchange partition p3
  3  with table t_p3_fixup;

Table altered.

SQL>
SQL> alter tablespace demo3_ro read only;

Tablespace altered.

Now I can make DEMO3_RO read only and I only need to back up this new tablespace, and the job is done.

Clearly there are lots of “IF’s” with this approach:

• What if you have global indexes?
• What if the row changes are just of couple of rows?
• How often do we need to do these activities?
• How large are my partitions and tablespaces?
• How many tablespaces do I have already, and so forth.

But hopefully, if your database “planets” align with the demo above, then using EXCHANGE might be a very efficient way of making changes to “read only” data.