- 浏览: 85778 次
- 性别:
- 来自: 北京
最新评论
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.
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.
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.
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.
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.
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?
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.
Even in this small table, we should be able to reclaim some space, so let’s try shrinking the table.
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.
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?
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.
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.
发表评论
-
dbms_output can not put the zero
2011-08-25 09:29 754DECLARE V_INTA NUMBER ... -
what is the difference between object_id and data_object_id?
2011-08-24 09:17 956The object_id is the primary k ... -
oracle EXECUTE IMMEDIATE ora-00911
2011-08-14 10:15 1515I get an error when I try to ex ... -
Will the valid status of index impact dml operation?
2011-08-05 10:34 847DROP TABLE tab01; SELECT * FRO ... -
where can i find the job number of those jobs defined in dba_scheduler_jobs?
2011-08-01 10:41 851Question: Hello, could anybody ... -
Listener HPUX Error: 242: No route to host
2011-05-17 14:55 982现象: 引用LSNRCTL> status Conne ... -
一进程阻塞问题解决
2011-05-12 16:38 4122同事反映,删除一条数据总是没有反应,请求协助解决. 问题非常 ... -
open database with ORA-00704 and ORA-39700
2011-05-06 16:13 29201,Error 1)alter.log Fri May ... -
oracle text index create and use
2011-05-06 13:41 1922一、Install Text Index 1,The ste ... -
offline datafile and offline tablespace
2011-05-04 11:43 25321)offline datafile OFFLINE Spe ... -
oracle three type of block size
2011-04-28 17:35 772Tools: 引用[oracle@node oracle]$ ... -
bbed一(安装)
2011-04-26 14:54 1510bbed ----------------------- bl ... -
Strategies for RAC inter-instance parallelized queries
2011-04-25 14:14 1138I recently had to sit down and ... -
Enable Row Movement in Partitioning and Overhead
2011-04-24 14:03 1561Question 1: Hi, I am partitio ... -
ORA-14402 updating partition key column
2011-04-23 19:48 6375做DBA几年来,经常遇到项目到了维护期总是修改表的结构,原因很 ... -
ORACLE DSI 介绍
2011-04-19 18:33 869DSI是Data Server Internals的缩写,是O ... -
Oracle / Buffer cache
2011-04-19 17:18 791引用8.7 Tuning the Operating Syst ...
相关推荐
ROW MOVEMENT特性最初是在8i时引入的,其目的是提高分区表的灵活性,这一特性默认是关闭,只要使用一下3个功能才需要打开...
介绍oracle数据库row_number用法,通俗易懂
Oracle row_number()over start with...connect by prior start with...connect by prior
介绍了 row_number() over(order by column asc) 函数和 row_number() over(partition by column1 order by column2 asc) 的使用实例和方法
oracle分析函数row_number() over()使用,很好的ORACLE分析函数
2 Oracle开发专题之:分析函数 Rank Dense rank row number 3 Oracle开发专题之:分析函数3 Top Bottom N First Last NTile 4 Oracle开发专题之:窗口函数 5 Oracle开发专题之:报表函数 6 Oracle开发专题之:...
## oracle恢复误删除的表数据 ** 查询某个时间节点的表数据 ```java select * from 表名 as of timestamp to_timestamp('2020-04-27 17:25:00','yyyy-mm-dd hh24:mi:ss') ``` 开启行移动功能 ```java alter table ...
Oracle如何精确计算row的大小 38 PL/SQL编程 39 数据库的分组问题 41 oracle知识 42 数据库的导入导出 42 Oracle查询中rownum与Order by查询 45 oracle9i小结 46 Oracle 数据库的聚簇技术 61 数据库、服务名、实例 ...
等待事件row cache lock,latch row cache objects处理过程
oracle9i优化器
oracle报错是常遇到的问题,本文搜集了几乎全部报错提示内容
BLOG_【故障处理】Oracle_lhr_队列等待之TX - row lock contentionBLOG_【故障处理】Oracle_lhr_队列等待之TX - row lock contentionBLOG_【故障处理】Oracle_lhr_队列等待之TX - row lock contention
在实际的工作中我们经常会碰到一些Oracle数据库性能较低的问题,当然,引起 Oracle数据库性能较低的原因是多方面的,我们能够通过一些正确的设计和诊断来尽量的避免一些Oracle数据库性能不好,Row Migration (行迁移...
oracle 分区技术-大批量数据操作 大数据对象 (表, 索引)被分成小物理段 ...(row movement enabled) 分区可以存储在不同的表空间 分区可以有不同的物理存储参数 分区支持IOT表,对象表,LOB字段,varrays等
Five in a Row program
在Oracle数据库中,DML锁主要包括TM锁和TX锁,其中TM锁称为表级锁(用来保证表的结构不被用户修改),TX锁称为事务锁或行级锁。当Oracle执行DML语句时,系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后,系统...
Oracle 数据类型及存储方式 概述 通过实例,全面而深入的分析oralce的基本数据类型及它们的存储方式。以ORACLE 10G为基础,介绍oralce 10g引入的新的数据类型。让你对oracle数据类型有一个全新的认识。揭示一些不...
Oracle恢复流程图 Oracle恢复流程图 Oracle恢复流程图 Oracle恢复流程图 Oracle恢复流程图