Alter table table_name enable row movement, altertable_name

Alter table table_name enable row movement, altertable_name

Row movement is literally interpreted as moving rows. by default, a row in an oracle data block does not move in its lifecycle, that is, its rowid does not change. however, in some scenarios, we want the row rowid to change. In this case, we need to start the row movement feature of the table.

Use the following statement to enable the row movement feature:

 

Alter table table_name enable row movement;

 

 

Row movement must be enabled in three scenarios.

1: partitioned table

When we allow the partition key of the partition table to be updatable, if the partition key is updated, the currently modified data entries need to be moved to other partitions, then the rowid will change.

 

SQL> create table city_office (office_number number, city_id varchar2 (10 ))

2 partition by list (city_id)

3 (

4 partition p1 values ('1 '),

5 partition p2 values ('2 '),

6 partition p3 values ('3 '));

 

Table created.

 

SQL> insert into city_office values (1, '1 ');

 

1 row created.

 

SQL> insert into city_office values (2, '2 ');

 

1 row created.

 

SQL> insert into city_office values (3, '3 ');

 

1 row created.

 

SQL> insert into city_office values (4, '1 ');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from city_office partition (p1 );

 

OFFICE_NUMBER CITY_ID

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

1 1

4 1

 

SQL> select * from city_office partition (p2 );

 

OFFICE_NUMBER CITY_ID

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

2 2

 

SQL> update city_office set city_id = 2 where office_number = 4;

Update city_office set city_id = 2 where office_number = 4

*

ERROR at line 1:

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

 

 

SQL> alter table city_office enable row movement;

 

Table altered.

 

SQL> update city_office set city_id = 2 where office_number = 4;

 

1 row updated.

 

SQL>

SQL>

SQL> select * from city_office partition (p1 );

 

OFFICE_NUMBER CITY_ID

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

1 1

 

SQL> select * from city_office partition (p2 );

 

OFFICE_NUMBER CITY_ID

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

2 2

4 2

 

2: Flash back operation

During the flashback operation, we also need to enable the row movement feature of the table.

 

SQL> alter table city_office disable row movement;

 

Table altered.

 

SQL> select current_scn from v $ database;

 

CURRENT_SCN

-----------

1291848

 

SQL> select count (*) from city_office;

 

COUNT (*)

----------

4

 

SQL> delete from city_office where city_id = 1;

 

1 row deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> flashback table city_office to scn 1291848;

Flashback table city_office to scn 1291848

*

ERROR at line 1:

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

 

 

SQL> alter table city_office enable row movement;

 

Table altered.

 

SQL> flashback table city_office to scn 1291848;

 

Flashback complete.

 

SQL> select count (*) from city_office;

 

COUNT (*)

----------

4

 

Why does the flashback table change the rowid? The following experiment is used to study:

SQL> create table easy (id number );

 

Table created.

 

SQL> insert into easy values (1 );

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> alter table easy enable row movement;

 

Table altered.

 

SQL> select current_scn from v $ database;

 

CURRENT_SCN

-----------

1292223

 

SQL> update table easy set id = 2;

Update table easy set id = 2

*

ERROR at line 1:

ORA-00903: invalid table name

 

 

SQL> update easy set id = 2;

 

1 row updated.

 

SQL> commit;

 

Commit complete.

 

SQL> alter session set tracefile_identifier = 'ee ';

 

Session altered.

 

SQL> alter session set SQL _trace = true;

 

Session altered.

 

SQL> flashback table easy to scn 1292223;

 

Flashback complete.

 

SQL> select * from sys_temp_fbt;

 

SCHEMA OBJECT_NAME OBJECT # RID

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

EASY 76906 AAASxqAAGAAAAC0AAA D

EASY 76906 AAASxqAAGAAAAC0AAA I

 

By viewing the trace file, we can find that the oracle flashback table is implemented through a temporary table sys_temp_fbt. Some content in the trace file is as follows:

**************************************** ****************************************

 

SQL ID: fbk9spd3mwsfg Plan Hash: 3764894756

 

INSERT/* + APPEND */into SYS_TEMP_FBT SELECT/* + fbtscan full (S) PARALLEL (S,

DEFAULT) */: 1,: 2,: 3, rowid, SYS_FBT_INSDEL FROM "EASY". "EASY" as of SCN

 

**************************************** ****************************************

 

SQL ID: 7fh8jd0y92mv9 Plan Hash: 1584303325

 

DELETE/* + BYPASS_UJVC */FROM (SELECT/* + ORDERED USE_NL (S) PARALLEL (S,

DEFAULT) PARALLEL (T, DEFAULT) */S. rowid FROM SYS_TEMP_FBT T, "EASY". "EASY"

S

WHERE

T. rid = S. rowid and T. action = 'D' and T. object # =: 1) V

 

**************************************** ****************************************

 

SQL ID: gpj0cz9m8173z Plan Hash: 1430220360

 

INSERT/* + PARALLEL (S, DEFAULT) PARALLEL (T, DEFAULT) */INTO "EASY". "EASY"

SELECT/* + USE_NL (S) ordered parallel (S, DEFAULT) PARALLEL (T, DEFAULT )*/

S. * FROM SYS_TEMP_FBT T, "EASY". "EASY" as of SCN: 1 s where t. rid =

S. rowid and T. action = 'I' and T. object # =: 2

 

It can be seen that oracle deletes data through SYS_TEMP_FBT and then inserts data. Therefore, the row rowid may change,

 

 

 

 

.

 

 

Java code

1. SQL> ALTER TABLE CITY_OFFICES ENABLE ROW MOVEMENT;
2. Table altered.
3. SQL> FLASHBACK TABLE CITY_OFFICES
4. 2 to timestamp (effecimestamp-INTERVAL '05 'minute );
5. Flashback complete.
6. SQL> SELECT * FROM CITY_OFFICES;
7. OFFICE_NUMBER CITY_ID OFFICE_NAME
8. -------------------------------------------------------
9. 1 282 DENVER
10. 2 282 DENVER TECH CTR
11. 3 282 DENVER WEST
12. 4 283 BROOMFIELD

 

If the table has row movement disabled, why is it you can drop the table and flashback the table to before the drop without row movement being enabled?

 

Java code

1. SQL> ALTER TABLE CITY_OFFICES DISABLE ROW MOVEMENT;
2. Table altered.
3. SQL> DROP TABLE CITY_OFFICES;
4. Table dropped.
5. SQL> FLASHBACK TABLE CITY_OFFICES TO BEFORE DROP;
6. Flashback complete.

 

 

3: reclaim space

When the space is reduced, the row will also be moved, as shown below:

 

SQL> alter table easy disable row movement;

 

Table altered.

 

SQL> alter table easy shrink space;

Alter table easy shrink space

*

ERROR at line 1:

ORA-10636: row movement is not enabled

 

 

SQL> alter table easy enable row movement;

 

Table altered.

 

SQL> alter table easy shrink space;

 

Table altered.

 

Shrink spaceOperation (Without compact) Will cause all opened cursors to expire, so use it with caution.