Row Movement in oracle...Oops what is this?

Row Movement in oracle...Oops what is this?

This is actually relocation of a row from a table to another place. Now the question arises when this can be done in oracle.

Let’s take the edge of this topic…

Below are the situations where row movement is comes into play

Partitioned Tables

Many partitioned table examples use regions, cities and states as list examples. What happens if you use a city as a partition key and an office in that city moves elsewhere? Or two offices in the same city (from different groups or business units within the same company) merge into one location? You could split the default partition and add the new location name. How would you move records from the old partitioned into the new one? Short of deleting from one partition and inserting same into a new one, wouldn’t it be easier to be able to perform a single update?

Example:

SQL> CREATE TABLE CITY_OFFICES

  2    (

  3       OFFICE_NUMBER NUMBER       NOT NULL,

  4        CITY_ID       VARCHAR2(12) NOT NULL,

  5     OFFICE_NAME   VARCHAR2(30) NOT NULL

  6      )

  7     PARTITION BY LIST (CITY_ID)

  8   (

  9     PARTITION P282 VALUES ('282'),

 10     PARTITION P283 VALUES ('283'),

 11     PARTITION P284 VALUES ('284'));

Table created.

SQL> insert into city_offices values(1,282,'NOIDA');

1 row created.

SQL> insert into city_offices values(2,282,'NOIDA_TECH');

1 row created.

SQL> insert into city_offices values(3,282,'NOIDA_SOFTWARE');

1 row created.

SQL> insert into city_offices values(4,282,'NOIDA_SOFTECH');

1 row created.

SQL> SELECT * FROM CITY_OFFICES;

OFFICE_NUMBER CITY_ID      OFFICE_NAME

------------- ------------ ------------------------------

            1 282          NOIDA

            2 282          NOIDA_TECH

            3 282          NOIDA_SOFTWARE

            4 282          NOIDA_SOFTECH

SQL> update city_offices set city_id=283 where office_number=4;

update city_offices set city_id=283 where office_number=4

       *

ERROR at line 1:

ORA-14402: updating partition key column would cause a partition change

SQL> alter table city_offices enable row movement;

Table altered.

SQL> update city_offices set city_id=283 where office_number=4;

1 row updated.

SQL> SELECT * FROM CITY_OFFICES;

OFFICE_NUMBER CITY_ID      OFFICE_NAME

------------- ------------ ------------------------------

            1 282          NOIDA

            2 282          NOIDA_TECH

            3 282          NOIDA_SOFTWARE

            4 283          NOIDA_SOFTECH

Flashback

The first step to enabling table flashback is to enable row movement on the tables.

This is a flag set in the data dictionary that informs Oracle that rowids may change. A

rowid can never actually change, but a flashback operation may make it appear as

though it has. For instance, in the case of a row that is deleted, the flashback

operation will insert it back into the table: it will have the same primary key value,

but a different rowid.

Example:

In the example that follows, there are two tables: EMP and DEPT. There is a

foreign key relationship between them, stating that every employee in EMP must be

a member of a department in DEPT.

First, insert a new department and an employee in that department, and note the

time:

sql> insert into dept values(50,'SUPPORT','LONDON');

1 row created.

sql> insert into emp values(8000,'WATSON','ANALYST',7566,'27-DEC-

04',3000,null,50);

1  row created.

sql> commit;

Commit complete.

sql> select sysdate from dual;

SYSDATE

-----------------

27-12-04 18:30:11

Next delete the department and the employee, taking care to delete the employee

first to avoid a constraint violation:

sql> delete from emp where empno=8000;

1 row deleted.

sql> delete from dept where deptno=50;

1 row deleted.

sql> commit;

Commit complete.

Now attempt to flash back the tables to the time when the department and

employee existed:

sql> flashback table emp to timestamp to_timestamp('27-12-04

18:30:11','dd-mm-yy hh24:mi:ss');

flashback table emp to timestamp to_timestamp('27-12-04 18:30:11','dd-mm-yy

hh24:mi:ss')

*                

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

This fails because by default row movement, which is a prerequisite for table

flashback, is not enabled for any table, so enable it, for both tables:

sql> alter table dept enable row movement;

Table altered.

sql> alter table emp enable row movement;

Table altered.

and now try the flashback again:

sql> flashback table emp to timestamp to_timestamp('27-12-04

18:30:11','dd-mm-yy hh24:mi:ss');

flashback table emp to timestamp to_timestamp('27-12-04 18:30:11','dd-mm-yy

hh24:mi:ss')

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not

found

This time the flashback fails for a more subtle reason. The flashback is attempting

to reverse the deletion of employee 8000 by inserting him, but employee 8000 was in

department 50, which has been deleted and so does not exist. Hence the foreign key

violation. You could avoid this problem by flashing back the DEPT table first, which

would insert department 50. But if your flashback involves many tables and many

DML statements, it may be logically impossible to find a sequence that will work.

The answer is to flash back both tables together:

sql> flashback table emp,dept to timestamp to_timestamp('27-12-04

18:30:11','dd-mm-yy hh24:mi:ss');

Flashback complete.

This succeeds because both the tables are flashed back in one transaction, and the

constraints are only checked at the end of that transaction, by which time, the data is

logically consistent.

Space Management

Row movement comes into play can be found in shrink operations.

Example:

SQL> SELECT ROWID, OFFICE_NUMBER FROM CITY_OFFICES;

ROWID              OFFICE_NUMBER

------------------ -------------

AAANSfAAEAAAEAnAAA             1

AAANSfAAEAAAEAnAAD             2

AAANSfAAEAAAEAnAAE             3

AAANSgAAEAAAEAvAAA             4

SQL> DELETE FROM CITY_OFFICES WHERE OFFICE_NUMBER IN (2,3);

2 rows deleted.

SQL> COMMIT;

Commit complete.

Even in this small table, we should be able to reclaim some space, so let’s try shrinking the table.

SQL> ALTER TABLE CITY_OFFICES SHRINK SPACE;

ALTER TABLE CITY_OFFICES SHRINK SPACE

*

ERROR at line 1:

ORA-10636: ROW MOVEMENT is not enabled

Notice that the error number is different from the flashback example, but the message is pretty clear. We’ll alter the table and perform the shrink operation.

SQL> ALTER TABLE CITY_OFFICES ENABLE ROW MOVEMENT;

Table altered.

SQL> ALTER TABLE CITY_OFFICES SHRINK SPACE;

Table altered.

Now that the shrink has taken place, let’s examine the ROWIDs for the remaining two rows. Will the ROWIDs be the same or different?

SQL> SELECT ROWID, OFFICE_NUMBER FROM CITY_OFFICES;

ROWID              OFFICE_NUMBER

------------------ -------------

AAANSfAAEAAAEAkAAA             1

AAANSgAAEAAAEAsAAA             4

Interestingly enough, the ROWIDs for the two remaining rows are different from their original IDs before the delete statement.