Row Movement in Oracle(oracle中根據枚舉欄位建立分區)

One of the relatively newer features in Oracle concerns the moving of
rows. Why would a row move and who or what controls that movement?
Furthermore, by “move,” what exactly does move mean? Does a row move to
another table, or is row movement constrained to the row’s container
(i.e., a table)? An early use of row movement was highlighted in
Oracle8i, and row movement then, as well as now, applied to moving rows
in a partitioned table. With newer releases of the Oracle RDBMS, where
else does row movement come into play, and are there any gotcha’s with
respect to row movement operations?

This article looks at three common cases or situations where row movement needs to be enabled.

Partitioned Tables
As far back as Oracle 8.1.5 documentation, row movement applied to
updatable partition keys. A list partition where the partition key is a
specific value is a good example of this. 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?

Let’s create a quick partitioned table example and see how moving a row works.

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>
SQL> INSERT INTO CITY_OFFICES VALUES (1,'282','DENVER');

1 row created.

SQL> INSERT INTO CITY_OFFICES VALUES (2,'282','DENVER TECH CTR');

1 row created.

SQL> INSERT INTO CITY_OFFICES VALUES (3,'282','DENVER WEST');

1 row created.

SQL> INSERT INTO CITY_OFFICES VALUES (4,'282','BROOMFIELD');

1 row created.

SQL> COMMIT;

Commit complete.

All of the cities are located in the Denver area (city ID of 282 in
this example). Broomfield is further north of the Denver metro area and
has been slated to become part of the Boulder area office group (using
a city ID of 283). Let’s try and move it by updating the partition key
value for that office.

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

Why didn’t the update work? The error message doesn’t come right out
and say it, but the reason is that row movement is not enabled. How
would you know this right off the bat? The reason why you would know
this is due to the fact that row movement – by default – is not
enabled. A simple alter table statement remedies the update problem.

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> COMMIT;

Commit complete.

SQL> ALTER TABLE CITY_OFFICES DISABLE ROW MOVEMENT;

Table altered.

Why did I disable the row movement? Well, tables are partitioned for a
reason, and to help ensure data goes where it is supposed to, you
should disallow inadvertent updates or row movements. Obviously, if
your application requires those types of updates, this guideline
wouldn’t necessarily apply to you.

Flashback
Being able to flashback DML operations has greatly reduced the
frequency of how often you hear a DBA say, “Oops.” In the next example,
I’ll delete a row, commit the operation, and try to recover the deleted
record.

SQL> DELETE FROM CITY_OFFICES WHERE OFFICE_NUMBER = 1;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> FLASHBACK TABLE CITY_OFFICES
  2    TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '05' minute);
FLASHBACK TABLE CITY_OFFICES
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

The error message in this case is much clearer as to the nature of the
failed statement. I find it handy to keep the flashback syntax around
in an easily (and quickly) identifiable location, and that is chiefly
because I don’t use timestamps that much in my applications. Time is
still somewhat of the essence and the quicker you can recover the table
to a good state, the better.

SQL> ALTER TABLE CITY_OFFICES ENABLE ROW MOVEMENT;

Table altered.

SQL> FLASHBACK TABLE CITY_OFFICES
  2    TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '05' minute);

Flashback complete.

SQL> SELECT * FROM CITY_OFFICES;

OFFICE_NUMBER CITY_ID      OFFICE_NAME
------------- ------------ ------------------------------
            1 282          DENVER
            2 282          DENVER TECH CTR
            3 282          DENVER WEST
            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?

SQL> ALTER TABLE CITY_OFFICES DISABLE ROW MOVEMENT;

Table altered.

SQL> DROP TABLE CITY_OFFICES;

Table dropped.

SQL> FLASHBACK TABLE CITY_OFFICES TO BEFORE DROP;

Flashback complete.

Chapter 15 in the Database Concepts guide states the following:

For Flashback Table to succeed, the system must retain enough undo
information to satisfy the specified SCN or timestamp, and the
integrity constraints specified on the tables cannot be violated. Also,
row movement must be enabled.

The example above shows that “row movement must be enabled” is not entirely true.

Space Management
The third and final example of where row movement comes into play can
be found in shrink operations. If you think about it, shrinking a table
may entail moving data around within a table (handled internally by
Oracle), so the idea of a row moving around makes sense. First, let’s
get a record of the current ROWIDs for each office and then delete two
rows.

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. You may
have expected office_number 4’s ROWID to change, but not office_number
1’s, but it too changed location after the shrink.

The moral of the story here supports what the documentation says (in more than one place):

Before you use rowids in DML statements, they should be verified and
guaranteed not to change. The intended rows should be locked so they
cannot be deleted. Under some circumstances, requesting data with an
invalid rowid could cause a statement to fail.

Using ROWIDs to perform DML on records can be wicked fast, and is
virtually the fastest way to access a row. However, if there is any
chance that someone else’s operation (and even yours) can alter the
ROWIDs of a table, you can find yourself with lots of messy data.
Further, a shrink operation (without the COMPACT option) can invalidate
open cursors. That could spell trouble for an application.

In Closing
We looked at three major operations where row movement is required:
partition key value change, flashback and space management. Enabling
and disabling row movement is very simple to implement, and in most
cases, has no unwanted side effects. Space management operations using
the SHRINK option can have unintended consequences on other users and
operations, and as we all know, good deeds (cleaning up space) should
never go unpunished. Using row movement and understanding what takes
place makes this feature an invaluable asset in your administration
arsenal, and it is up to you to be careful so as not to be bitten by a
changed ROWID.