ITPub博客

Oracle Database将普通表转换为分区表遇到的问题

原创 作者:尛样儿 时间:2015-07-27 14:08:50 0 删除 编辑
<br /> <span style="font-size:16px;font-family:'Courier New';"> &nbsp; &nbsp;这篇文章讨论一下在Oracle Database数据库中将普通表转换为分区表过程中遇到的问题,这里将普通表转换为分区表并没有使用在线重定义的方法,使用最传统的方法,将原始表重命名,创建新表,把原始表中的数据INSERT到新表中,完成分区表的转换。<br /> <br /> &nbsp; &nbsp;在线重定义表的内容可参考文章《使用DBMS_REDEFINITION包执行在线重定义表》:<a href="http://blog.itpub.net/23135684/viewspace-1765128/" target="_blank">http://blog.itpub.net/23135684/viewspace-1765128/<br /> <br /> </a>&nbsp; &nbsp; 使用在线重定义将普通表转换为分区表的例子可参考文章《使用DBMS_REDEFINITION在线重定义表普通表为分区表》:<a href="http://blog.itpub.net/23135684/viewspace-661756/" target="_blank">http://blog.itpub.net/23135684/viewspace-661756/</a><br /> <br /> 下面通过测试进行说明:</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><span style="font-size:16px;font-family:'Courier New';">C:\Users\LIUBINGLIN&gt;sqlplus / as sysdba</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL*Plus: Release 11.2.0.3.0 Production on 星期一 7月 27 12:42:54 2015</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">Copyright (c) 1982, 2011, Oracle. &nbsp;All rights reserved.</span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';">连接到:</span><br /> <span style="font-size:16px;font-family:'Courier New';">Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production</span><br /> <span style="font-size:16px;font-family:'Courier New';">With the Partitioning, OLAP, Data Mining and Real Application Testing options</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"></span><span style="font-size:16px;font-family:'Courier New';">SQL&gt; select * from v$version where rownum&lt;=2;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">BANNER</span><br /> <span style="font-size:16px;font-family:'Courier New';">--------------------------------------------------------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';">Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production</span><br /> <span style="font-size:16px;font-family:'Courier New';">PL/SQL Release 11.2.0.3.0 - Production</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; create user test1 identified by test1 default tablespace users temporary tablespace temp;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">用户已创建。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; create user test2 identified by test2 default tablespace users temporary tablespace temp;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">用户已创建。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; grant connect,resource to test1;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">授权成功。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; grant connect,resource to test2;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">授权成功。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; grant create view ,create trigger to test1;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">授权成功。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; grant create view ,create trigger to test2;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">授权成功。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; connect test1/test1</span><br /> <span style="font-size:16px;font-family:'Courier New';">已连接。</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; show user</span><br /> <span style="font-size:16px;font-family:'Courier New';">USER 为 "TEST1"</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; create table abc(id number,insdate date,name varchar2(20),age number); &nbsp; &nbsp; <span style="color:#E53333;">&lt;&lt;&lt;&lt; 创建一张测试表。</span></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">表已创建。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; alter table abc add constraint pk_id primary key(id); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span style="color:#E53333;">&lt;&lt;&lt;&lt; 为表指定一个主键,主键会对应一个唯一索引。</span></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">表已更改。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; create index ins_idx01 on abc(insdate); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span style="color:#E53333;">&lt;&lt;&lt;&lt; 创建一个普通索引。</span></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">索引已创建。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; create view v_abc as select * from abc; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span style="color:#E53333;">&lt;&lt;&lt;&lt; 创建一个和测试表相关的视图。</span></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">视图已创建。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; create table abc_mirror(id number,insdate date,name varchar2(20),age number); &nbsp; &nbsp;<span style="color:#E53333;">&lt;&lt;&lt;&lt; 创建一个测试表的触发器。</span></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">表已创建。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; create or replace trigger abc_trigger</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 2 &nbsp; &nbsp;after insert on test1.abc</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 3 &nbsp; &nbsp;for each row</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 4 &nbsp;begin</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 5 &nbsp; &nbsp;insert into test1.abc_mirror</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 6 &nbsp; &nbsp;values</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 7 &nbsp; &nbsp; &nbsp;(:new.id, :new.insdate, :new.name, :new.age);</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 8 &nbsp; &nbsp;commit;</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 9 &nbsp;end abc_trigger;</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp;10 &nbsp;/</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">触发器已创建</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select * from abc;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">未选定行</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; insert into abc values(1,sysdate,'a',12);</span><br /> <span style="font-size:16px;font-family:'Courier New';">insert into abc values(1,sysdate,'a',12)</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; *</span><br /> <span style="font-size:16px;font-family:'Courier New';">第 1 行出现错误:</span><br /> <span style="font-size:16px;font-family:'Courier New';">ORA-04092: COMMIT 不能在触发器中</span><br /> <span style="font-size:16px;font-family:'Courier New';">ORA-06512: 在 "TEST1.ABC_TRIGGER", line 5</span><br /> <span style="font-size:16px;font-family:'Courier New';">ORA-04088: 触发器 'TEST1.ABC_TRIGGER' 执行过程中出错</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">&lt;&lt;&lt;&lt;&nbsp;</span><span style="font-size:16px;font-family:'Courier New';color:#E53333;">从这个错误可以说明两点,触发器中不能包含COMMIT关键字,对测试表的事务和触发器的事务是同一个事务,结束源表的事务即结束了触发器中的事务。第二点是,如果触发器出现问题,那么对基础表的操作不能正常进行。</span><span style="font-size:16px;font-family:'Courier New';color:#E53333;"></span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;"></span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; create or replace trigger abc_trigger</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 2 &nbsp; &nbsp;after insert on abc</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 3 &nbsp; &nbsp;for each row</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 4 &nbsp;begin</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 5 &nbsp; &nbsp;insert into abc_mirror</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 6 &nbsp; &nbsp;values</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 7 &nbsp; &nbsp; &nbsp;(:new.id, :new.insdate, :new.name, :new.age);</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 8 &nbsp;end abc_trigger;</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 9 &nbsp;/</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">触发器已创建</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; insert into abc values(1,sysdate,'a',12);</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">已创建 1 行。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; commit; &nbsp; &nbsp;<span style="color:#E53333;">&lt;&lt;&lt;&lt; 触发器中的事务也结束。</span></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">&lt;&lt;&lt;&lt; 这点以前有过文章说明:《</span><span style="font-size:16px;font-family:'Courier New';color:#E53333;">存储过程、触发器与事务之间的关系</span><span style="font-size:16px;font-family:'Courier New';color:#E53333;">》:</span><a href="http://blog.itpub.net/23135684/viewspace-709920/" target="_blank"><span style="font-size:16px;font-family:'Courier New';color:#E53333;">http://blog.itpub.net/23135684/viewspace-709920/</span><span style="font-size:16px;font-family:'Courier New';color:#E53333;"></span></a><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;"></span><br /> <span style="font-size:16px;font-family:'Courier New';">提交完成。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select * from abc;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; ID INSDATE &nbsp; &nbsp; &nbsp; &nbsp;NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AGE</span><br /> <span style="font-size:16px;font-family:'Courier New';">---------- -------------- -------------------- ----------</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 27-7月 -15 &nbsp; &nbsp; a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select * from abc_mirror;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; ID INSDATE &nbsp; &nbsp; &nbsp; &nbsp;NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AGE</span><br /> <span style="font-size:16px;font-family:'Courier New';">---------- -------------- -------------------- ----------</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 27-7月 -15 &nbsp; &nbsp; a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select * from v_abc;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; ID INSDATE &nbsp; &nbsp; &nbsp; &nbsp;NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AGE</span><br /> <span style="font-size:16px;font-family:'Courier New';">---------- -------------- -------------------- ----------</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 27-7月 -15 &nbsp; &nbsp; a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; set linesize 200</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select index_name,index_type,table_name from user_indexes where table_name='ABC';&nbsp;</span><span style="white-space:normal;font-size:16px;font-family:'Courier New';">&nbsp;<span style="color:#E53333;">&lt;&lt;&lt;&lt; 测试表有2个相关的索引。</span></span><span style="font-size:16px;font-family:'Courier New';color:#E53333;"></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">INDEX_NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; INDEX_TYPE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TABLE_NAME</span><br /> <span style="font-size:16px;font-family:'Courier New';">------------------------------ --------------------------- ------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';">INS_IDX01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NORMAL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ABC</span><br /> <span style="font-size:16px;font-family:'Courier New';">PK_ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NORMAL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ABC</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select trigger_name,table_name from user_triggers where table_name='ABC'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span style="color:#E53333;">&lt;&lt;&lt;&lt; 测试表有1个相关的触发器。</span></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">TRIGGER_NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TABLE_NAME</span><br /> <span style="font-size:16px;font-family:'Courier New';">------------------------------ ------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';">ABC_TRIGGER &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ABC</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; grant select on abc to test2;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">授权成功。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; connect test2/test2</span><br /> <span style="font-size:16px;font-family:'Courier New';">已连接。</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; show user</span><br /> <span style="font-size:16px;font-family:'Courier New';">USER 为 "TEST2"</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; create view v_abc2 as select * from test1.abc;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">视图已创建。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; connect test1/test1</span><br /> <span style="font-size:16px;font-family:'Courier New';">已连接。</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; show user</span><br /> <span style="font-size:16px;font-family:'Courier New';">USER 为 "TEST1"</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; alter table test1.abc rename to test1.abcd; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span style="color:#E53333;">&lt;&lt;&lt;&lt; 对表进行重命名操作,to后面的表名不能加上用户的前缀。</span></span><br /> <span style="font-size:16px;font-family:'Courier New';">alter table test1.abc rename to test1.abcd</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;*</span><br /> <span style="font-size:16px;font-family:'Courier New';">第 1 行出现错误:</span><br /> <span style="font-size:16px;font-family:'Courier New';">ORA-14047: ALTER TABLE|INDEX RENAME 不能与其他分区组合</span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; alter table test1.abc rename to abcd;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">表已更改。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; set linesize 200</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><span style="font-size:16px;font-family:'Courier New';">SQL&gt; select index_name,index_type,table_name,status from user_indexes where table_name='ABCD'; &nbsp; &nbsp;&nbsp;</span><span style="white-space:normal;font-size:16px;font-family:'Courier New';">&nbsp;<span style="color:#E53333;">&lt;&lt;&lt;&lt; 对表进行了重命名,相关的索引会跟着发生变化,且状态为VALID。</span></span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';">INDEX_NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; INDEX_TYPE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TABLE_NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; STATUS</span><br /> <span style="font-size:16px;font-family:'Courier New';">------------------------------ --------------------------- ------------------------------ --------</span><br /> <span style="font-size:16px;font-family:'Courier New';">INS_IDX01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NORMAL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ABCD &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALID</span><br /> <span style="font-size:16px;font-family:'Courier New';">PK_ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NORMAL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ABCD &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALID</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select trigger_name,table_name,status from user_triggers where table_name='ABCD'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span style="color:#E53333;">&lt;&lt;&lt;&lt; 对表进行了重命名,相关的触发器会跟着发生变化,状态为ENABLED,下面的测试结果也证明状态是可用。这以为了在该触发器中的代码会将原来的ABC自动的修改为ABCD。</span></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">TRIGGER_NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TABLE_NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; STATUS</span><br /> <span style="font-size:16px;font-family:'Courier New';">------------------------------ ------------------------------ --------</span><br /> <span style="font-size:16px;font-family:'Courier New';">ABC_TRIGGER &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ABCD &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ENABLED</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select * from abcd;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; ID INSDATE &nbsp; &nbsp; &nbsp; &nbsp;NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AGE</span><br /> <span style="font-size:16px;font-family:'Courier New';">---------- -------------- -------------------- ----------</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 27-7月 -15 &nbsp; &nbsp; a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select * from abc_mirror;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; ID INSDATE &nbsp; &nbsp; &nbsp; &nbsp;NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AGE</span><br /> <span style="font-size:16px;font-family:'Courier New';">---------- -------------- -------------------- ----------</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 27-7月 -15 &nbsp; &nbsp; a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select * from v_abc; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span style="color:#E53333;">&lt;&lt;&lt;&lt; 虽然触发器会跟着变,但是视图不会跟着变。</span></span><br /> <span style="font-size:16px;font-family:'Courier New';">select * from v_abc</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; *</span><br /> <span style="font-size:16px;font-family:'Courier New';">第 1 行出现错误:</span><br /> <span style="font-size:16px;font-family:'Courier New';">ORA-04063: view "TEST1.V_ABC" 有错误</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; insert into abcd values(2,sysdate,'b',14);</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">已创建 1 行。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; commit;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">提交完成。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select * from abc_mirror;</span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; ID INSDATE &nbsp; &nbsp; &nbsp; &nbsp;NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AGE</span><br /> <span style="font-size:16px;font-family:'Courier New';">---------- -------------- -------------------- ----------</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 27-7月 -15 &nbsp; &nbsp; b &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;14</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 27-7月 -15 &nbsp; &nbsp; a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; connect test2/test2</span><br /> <span style="font-size:16px;font-family:'Courier New';">已连接。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select * from v_abc2; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span style="color:#E53333;">&lt;&lt;&lt;&lt; 其他用户,和测试表相关的视图也不会跟着变。</span></span><br /> <span style="font-size:16px;font-family:'Courier New';">select * from v_abc2</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; *</span><br /> <span style="font-size:16px;font-family:'Courier New';">第 1 行出现错误:</span><br /> <span style="font-size:16px;font-family:'Courier New';">ORA-04063: view "TEST2.V_ABC2" 有错误</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';">除了以上的内容外,我们还可能想知道哪些数据库对象关联了某张表,或者某个数据库对象引用了哪些表等等,这里讨论一下和此相关的3个数据字典:</span><br /> <strong><span style="font-size:16px;font-family:'Courier New';">&gt;</span></strong><strong><span style="font-size:16px;font-family:'Courier New';">public_dependency</span></strong><span style="font-size:16px;font-family:'Courier New';"> &nbsp;PUBLIC_DEPENDENCY lists dependencies to and from objects, by object number.</span><br /> <strong><span style="font-size:16px;font-family:'Courier New';"><br /> &gt;DBA_dependencies|ALL_dependencies|USER_dependencies</span></strong><span style="font-size:16px;font-family:'Courier New';"> &nbsp; DBA_DEPENDENCIES describes all dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links.</span><br /> <strong><span style="font-size:16px;font-family:'Courier New';"><br /> &gt;DBA_SOURCE|ALL_SOURCE|USER_SOURCE</span></strong><span style="font-size:16px;font-family:'Courier New';"> &nbsp; &nbsp; &nbsp; &nbsp; USER_SOURCE describes the text source of the stored objects owned by the current user.</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';">下面的SQL语句可以查询出哪些数据库对象关联了指定的表:</span><br /> <span style="font-size:16px;font-family:'Courier New';">Select owner, object_type, object_name, object_id, status</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; from sys.DBA_OBJECTS</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp;where object_id in</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp;(Select object_id</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from public_dependency</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; connect by prior object_id = referenced_object_id</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;start with referenced_object_id = (Select object_id</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from sys.DBA_OBJECTS</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where owner = 'TEST1' -- Owner</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and object_name = 'ABCD' -- table_name</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; /*and object_type = :type*/ -- TABLE / SYNONYM</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ))</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';">下面的SQL语句可以查询出某个数据库对象引用了哪些表:</span><br /> <span style="font-size:16px;font-family:'Courier New';">select de.referenced_name</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; from user_dependencies de</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp;where de.name = 'ABC_TRIGGER'</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;and de.referenced_type = 'TABLE';</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">下面的SQL语句可以查询出某个数据库对象引用了哪些序列:</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><span style="font-size:16px;font-family:'Courier New';">select de.referenced_name</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; from user_dependencies de</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp;where de.name = 'ABC_TRIGGER'</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;and de.referenced_type = 'SEQUENCE';</span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';">以上的测试结果在分区表转换的情况下会出现以下的问题:</span><br /> <span style="font-size:16px;font-family:'Courier New';">1).由于触发器会跟着表名的变化而自动的调整代码,这在新分区表插入数据之后需要重新修改编译相应的所有触发器,将其调整回原来的表名,这个可以通过USER_TRIGGER.TABLE_NAME字段来确定相关的触发器。</span><br /> <span style="font-size:16px;font-family:'Courier New';">2).由于视图、存储过程(没有USER_PROCESSES.TABLE_NAME数据字典)</span><span style="font-size:16px;font-family:'Courier New';">等不会发生</span><span style="font-size:16px;font-family:'Courier New';">变化,所以需要手动的验证相关的对象是否可用。</span><br /> <span style="font-size:16px;font-family:'Courier New';">3).为了不影响表相关对象的正常使用,在调整完成后应该执行utlrp.sql脚本,手动重新编译一下不正确的对象,确保表相关的对象都能够正常的使用,特别是触发器,触发器变得无效会使和源表相关的所有事务都无法执行。</span><br /> <span style="font-size:16px;font-family:'Courier New';">4).另外,新的分区表建议重新收集一下表的统计信息。</span><br /> <span style="font-size:16px;font-family:'Courier New';">5).在创建分区表的时候,除了注意表本身外,还需要注意创建表的索引,主外键,各种约束,授权等(这个可以通过PL/SQL工具在查看表的源码时很好的展现出来)。</span><br /> <span style="font-size:16px;font-family:'Courier New';">6).为了提高将旧表数据插入分区表的速度,建议先插入数据到分区表,最后来创建索引,主外键,各种约束和创建授权,</span><span style="font-size:16px;font-family:'Courier New';">在将旧表插入分区表时可以酌情考虑</span><span style="font-size:16px;font-family:'Courier New';">指定/*+APPEND PARALLEL(4) NOLOGGING</span><span style="font-size:16px;font-family:'Courier New';">*/的提示来提高数据的插入效率。</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';">--end--</span><span style="font-size:16px;font-family:'Courier New';"></span><br /> <br />
请登录后发表评论 登录
全部评论
Oracle数据库管理员,Oracle数据库系统构架员;2012年7月出版《构建最高可用Oracle数据库系统:Oracle 11gR2 RAC管理、维护与性能优化》一书;Oracle 10g OCM。

注册时间:2010-01-05

  • 博文量
    461
  • 访问量
    5178483