管理数据库性能——修正无效的对象

管理数据库性能——修正无效的对象！

首先熟悉两张系统表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/