I'm new to Oracle and I searched for an answer for this question in the web but was unable to find one. If I understand correctly,the following command should write the rowid of migrated or chained rows to the table INVALID_ROWS.
analyze table <partitioned_table> partition <partition_name> validate structure cascade into INVALID_ROWS
But I was unable to produce the result. Here is what I tried.
CREATE TABLE scott.sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))
);
insert into scott.sales_range(salesman_id,salesman_name,sales_amount,sales_date)
Select 1,'abc',1000,TO_DATE('01/01/2000','MM/DD/YYYY') FROM DUAL UNION ALL
Select 2,'xyz',1500,TO_DATE('02/01/2000','MM/DD/YYYY') FROM DUAL UNION ALL
Select 2,'xyz',2000,TO_DATE('03/01/2000','MM/DD/YYYY') FROM DUAL ;
commit;
ALTER TABLE scott.sales_range ENABLE ROW MOVEMENT;
update scott.sales_range set sales_date=TO_DATE('04/01/2000','MM/DD/YYYY')
create table SCOTT.INVALID_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);
analyze table "SCOTT"."SALES_RANGE" partition("SALES_JAN2000") validate structure cascade complete into INVALID_ROWS;
Select * from INVALID_ROWS;
--no rows selected
I have to say that this is only for learning and I have no practical reason to intentionally produce chained rows. But if this doesn't result in row chaining, what is "into INVALID_ROWS" used for?
I'm using 11g R2.
Thanks.