SQL*Plus: Release 11.2.0.3.0 Production on 星期一 7月 27 12:42:54 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version where rownum<=2;
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production
SQL> create user test1 identified by test1 default tablespace users temporary tablespace temp;
用户已创建。
SQL> create user test2 identified by test2 default tablespace users temporary tablespace temp;
用户已创建。
SQL> grant connect,resource to test1;
授权成功。
SQL> grant connect,resource to test2;
授权成功。
SQL> grant create view ,create trigger to test1;
授权成功。
SQL> grant create view ,create trigger to test2;
授权成功。
SQL> connect test1/test1 已连接。 SQL> show user USER 为 "TEST1"
SQL> create or replace trigger abc_trigger 2 after insert on test1.abc 3 for each row 4 begin 5 insert into test1.abc_mirror 6 values 7 (:new.id, :new.insdate, :new.name, :new.age); 8 commit; 9 end abc_trigger; 10 /
触发器已创建
SQL> select * from abc;
未选定行
SQL> insert into abc values(1,sysdate,'a',12); insert into abc values(1,sysdate,'a',12) * 第 1 行出现错误: ORA-04092: COMMIT 不能在触发器中 ORA-06512: 在 "TEST1.ABC_TRIGGER", line 5 ORA-04088: 触发器 'TEST1.ABC_TRIGGER' 执行过程中出错
SQL> create or replace trigger abc_trigger 2 after insert on abc 3 for each row 4 begin 5 insert into abc_mirror 6 values 7 (:new.id, :new.insdate, :new.name, :new.age); 8 end abc_trigger; 9 /
SQL> connect test2/test2 已连接。 SQL> show user USER 为 "TEST2"
SQL> create view v_abc2 as select * from test1.abc;
视图已创建。
SQL> connect test1/test1 已连接。 SQL> show user USER 为 "TEST1" SQL> alter table test1.abc rename to test1.abcd; <<<< 对表进行重命名操作,to后面的表名不能加上用户的前缀。 alter table test1.abc rename to test1.abcd * 第 1 行出现错误: ORA-14047: ALTER TABLE|INDEX RENAME 不能与其他分区组合
SQL> alter table test1.abc rename to abcd;
表已更改。
SQL> set linesize 200
SQL> select index_name,index_type,table_name,status from user_indexes where table_name='ABCD'; <<<< 对表进行了重命名,相关的索引会跟着发生变化,且状态为VALID。
INDEX_NAME INDEX_TYPE TABLE_NAME STATUS ------------------------------ --------------------------- ------------------------------ -------- INS_IDX01 NORMAL ABCD VALID PK_ID NORMAL ABCD VALID
SQL> select trigger_name,table_name,status from user_triggers where table_name='ABCD'; <<<< 对表进行了重命名,相关的触发器会跟着发生变化,状态为ENABLED,下面的测试结果也证明状态是可用。这以为了在该触发器中的代码会将原来的ABC自动的修改为ABCD。
TRIGGER_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ -------- ABC_TRIGGER ABCD ENABLED
SQL> select * from abcd;
ID INSDATE NAME AGE ---------- -------------- -------------------- ---------- 1 27-7月 -15 a 12
SQL> select * from abc_mirror;
ID INSDATE NAME AGE ---------- -------------- -------------------- ---------- 1 27-7月 -15 a 12
SQL> select * from v_abc; <<<< 虽然触发器会跟着变,但是视图不会跟着变。 select * from v_abc * 第 1 行出现错误: ORA-04063: view "TEST1.V_ABC" 有错误
SQL> insert into abcd values(2,sysdate,'b',14);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from abc_mirror;
ID INSDATE NAME AGE ---------- -------------- -------------------- ---------- 2 27-7月 -15 b 14 1 27-7月 -15 a 12
SQL> connect test2/test2 已连接。
SQL> select * from v_abc2; <<<< 其他用户,和测试表相关的视图也不会跟着变。 select * from v_abc2 * 第 1 行出现错误: ORA-04063: view "TEST2.V_ABC2" 有错误
除了以上的内容外,我们还可能想知道哪些数据库对象关联了某张表,或者某个数据库对象引用了哪些表等等,这里讨论一下和此相关的3个数据字典: >public_dependency PUBLIC_DEPENDENCY lists dependencies to and from objects, by object number.
>DBA_dependencies|ALL_dependencies|USER_dependencies 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.
>DBA_SOURCE|ALL_SOURCE|USER_SOURCE USER_SOURCE describes the text source of the stored objects owned by the current user.
下面的SQL语句可以查询出哪些数据库对象关联了指定的表: Select owner, object_type, object_name, object_id, status from sys.DBA_OBJECTS where object_id in (Select object_id from public_dependency connect by prior object_id = referenced_object_id start with referenced_object_id = (Select object_id from sys.DBA_OBJECTS where owner = 'TEST1' -- Owner and object_name = 'ABCD' -- table_name /*and object_type = :type*/ -- TABLE / SYNONYM ))
下面的SQL语句可以查询出某个数据库对象引用了哪些表: select de.referenced_name from user_dependencies de where de.name = 'ABC_TRIGGER' and de.referenced_type = 'TABLE';
下面的SQL语句可以查询出某个数据库对象引用了哪些序列: select de.referenced_name from user_dependencies de where de.name = 'ABC_TRIGGER' and de.referenced_type = 'SEQUENCE';