管理数据库性能——修正无效的对象!
首先熟悉两张系统表dba_dependencies,user_objects;
SQL> desc dba_dependencies;
名称 是否为空? 类型
----------------------------------------- -------- -------------------------
OWNER NOT NULL VARCHAR2(30)
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(17)
REFERENCED_OWNER VARCHAR2(30)
REFERENCED_NAME VARCHAR2(64)
REFERENCED_TYPE VARCHAR2(17)
REFERENCED_LINK_NAME VARCHAR2(128)
DEPENDENCY_TYPE VARCHAR2(4)
SQL> desc user_objects;
Name Type Nullable Default Comments
-------------- ------------- -------- ------- ----------------------------------------------------------------------------
OBJECT_NAME VARCHAR2(128) Y Name of the object
SUBOBJECT_NAME VARCHAR2(30) Y Name of the sub-object (for example, partititon)
OBJECT_ID NUMBER Y Object number of the object
DATA_OBJECT_ID NUMBER Y Object number of the segment which contains the object
OBJECT_TYPE VARCHAR2(19) Y Type of the object
CREATED DATE Y Timestamp for the creation of the object
LAST_DDL_TIME DATE Y Timestamp for the last DDL change (including GRANT and REVOKE) to the object
TIMESTAMP VARCHAR2(19) Y Timestamp for the specification of the object
STATUS VARCHAR2(7) Y Status of the object
TEMPORARY VARCHAR2(1) Y Can the current session only see data that it place in this object itself?
GENERATED VARCHAR2(1) Y Was the name of this object system generated?
SECONDARY VARCHAR2(1) Y Is this a secondary object created as part of icreate for domain indexes?
Select REFERENCED_OWNER,REFERENCED_NAME ,REFERENCED_TYPE
From dba_dependencies
Where name=’EP_ROADNUM_DESC’;
SQL> Select REFERENCED_OWNER,REFERENCED_NAME ,REFERENCED_TYPE
2 From dba_dependencies
3 Where name='EP_ROADNUM_DESC';
REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE
------------------------------ -------------------------------------------- -----------------
修正无效的参数
创建表
create table peng(n1 number,d1 date);
Table created
insert into peng values(1,sysdate);
1 row inserted
创建视图
create or replace view v1 as select d1 from peng;
View created
创建存储
create or replace procedure p1 as
cnt number;
begin
select count(*) into cnt from peng;
end;
/
Procedure created
查看状态
select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
V1 VIEW VALID
P1 PROCEDURE VALID
OBJECT_NAME OBJECT_TYPE STATUS
PENG TABLE VALID
做drop操作
alter table peng drop column d1 ;
Table altered
再查看状态
select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
V1 VIEW INVALID
P1 PROCEDURE INVALID
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
PENG TABLE VALID
此时状态为INVALID
重新编译
alter procedure p1 compile;
Procedure altered
alter view v1 compile;
Warning: View altered with compilation errors
诊断问题:
select referenced_name, referenced_owner,referenced_type from user_dependencies
where name='V1' ;
REFERENCED_NAME REFERENCED_OWNER REFERENCED_TYPE
---------------------------------------------------------------- ------------------------------ -----------------
PENG PENG TABLE
D1 PENG NON-EXISTENT
D1 PUBLIC NON-EXISTENT
通过检索DBA_DEPENDENCIES视图所基于的代码来查明导致问题的原因。
select text from user_views
where view_name= 'V1' ;
TEXT
--------------------------------------------------------------------------------
select d1 from peng
可以看出select d1 from peng出错了,引用了无效的列;
重新编译,增加列;
alter table peng add (d1 date);
Table altered
alter view v1 compile;
View altered
alter procedure p1 compile;
Procedure altered
查询对象状态,确认编译有效;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
TESTTAB TABLE VALID
V1 VIEW VALID
P1 PROCEDURE INVALID
DI_IDX INDEX VALID
N1_IDX INDEX VALID
T_LOG TABLE VALID
LOG_XWJID_PK INDEX VALID
SYS_LOB0000051446C00002$$ LOB VALID
T_CSYS TABLE VALID
CSYS_XH_PK INDEX VALID
T_XWJPZ TABLE VALID
XWJPZ_XH_PK INDEX VALID
EP_ALARMTYPE_DESC TABLE VALID
EPALARMTYPEDESC_ALARMTYPE_PK INDEX VALID
EP_ROADNUM_DESC TABLE VALID
EPROADNUMDESC_ROADNUM_PK INDEX VALID
EP_PASSVEHICLE TABLE VALID
SYS_LOB0000051458C00018$$ LOB VALID
SYS_LOB0000051458C00017$$ LOB VALID
SYS_LOB0000051458C00016$$ LOB VALID
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
EP_PASSVEHICLE_PK_PTFNRN INDEX VALID
EP_PECCANCY TABLE VALID
SYS_LOB0000051466C00018$$ LOB VALID
SYS_LOB0000051466C00017$$ LOB VALID
SYS_LOB0000051466C00016$$ LOB VALID
EP_PECCANCY_PK_PTFNRN INDEX VALID
EP_HUNTVEHICLE TABLE VALID
V_WFDD_TONGJI VIEW VALID
V_WFLX_TONGJI VIEW VALID
PENG TABLE VALID
30 rows selected
SQL> alter procedure p1 compile;
Procedure altered
SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
V1 VIEW VALID
P1 PROCEDURE VALID
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
PENG TABLE VALID
无效对象修改完成!!!
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22934571/viewspace-1032617/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22934571/viewspace-1032617/