On Oracle 11.2.0.4.1, we tried to run a simple ALTER TABLE PARTITIONED_LARGE SET UNUSED COLUMN XXXXX;
on a large ref-partitioned child table to decommission a column.
The table has hundreds of millions of rows across thousands of partitions. Most partitions have been truncated and no segments left; probably about 220 of them have data. As per standard practice the point of the SET UNUSED COLUMN was to avoid redo generation and long downtime, while allowing us to regain space moving forward.
Something seems to have gone horribly awry; it started generating dozens of GB of redo logs; eventually filling up UNDOTBS; then failed with the below and started rolling back.
Fri Apr 24 23:48:13 2015
ORA-01555 caused by SQL statement below (SQL ID: 1ksq8yfdxv3ys, Query Duration=13500 sec, SCN: 0x0b5b.6392295a):
update "SCHEMA"."PARTITIONED_LARGE" set "XXXXX" = "XXXXX"
Seeing the statement above it's pretty obvious why it's generating undo/redo, but why did Oracle decide internally to do such an update during a SET UNUSED COLUMN?!
Other points that may or may not be relevant
- at the time the command was started, a restore point had been created for flashback purposes. This restore point was dropped part way through when flashback log size became a problem
- the column is defined as BINARY_DOUBLE DEFAULT 0 CONSTRAINT XXXXX_NN NOT NULL
- the SET UNUSED had been earlier tested on a test DB with about 75% of the data of the DB in question (but not exactly the same data) and had no issues; ran within seconds.
Can anyone explain this behaviour, either in terms of expected behaviour or known issues?
set unused
statement there?NOT NULL
columns with aDEFAULT
to avoid hitting the disk during theADD COLUMN
. As I understand, the test DB was created with a direct RMAN load from a prod backup. Unfortunately, the test DB is 11.2.0.4.3 c.f prod on 11.2.0.4.1 so there could be a clue there. We will probably need to do a fresh restore into the test DB as see if we can replicate though; with restore point and all.