oracle错误及解决方式集(转)[@more@]1)启动hp-unix下oracle时出现如下所示:
Oracle Server Manager Release 3.1.7.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved
Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.2.0 - 64bit Production
SVRMGR> Password:
Password:
ORA-01031: insufficient privileges
SVRMGR> ORA-01031: insufficient privileges
SVRMGR>
Server Manager complete.
Database "ora" warm started.
#
显示权限不够;用root去启动oracle当然是不行的;
2)col 用法
col子命令中有两个参数:truncated 和 wrapped,col定义的显示行的宽度,truncated就是如果行超过显示宽度就截断,wrapped就是换行!
/*=========================================================================*/
/* 建立同义词并使用 */
/*=========================================================================*/
我们先看如下的一系列执行:
SQL> create or replace view v_bmw_pay_online_new as
2 select *
3 from taobao.bmw_pay_online_new@lnk_db215;
SQL> create or replace procedure sp_v_test is
2 v_id number;
3 begin
4 select id into v_id from v_bmw_pay_online_new where id=1;
5 end;
6 /
Procedure created
SQL> create or replace synonym s_bmw_pay_online_new
2 for taobao.bmw_pay_online_new@lnk_db215;
Synonym created
SQL> create or replace procedure sp_s_test is
2 v_id number;
3 begin
4 select id into v_id from s_bmw_pay_online_new where id=1;
5 end;
6 /
Warning: Procedure created with compilation errors
SQL> show error
Errors for PROCEDURE TAOBAO.SP_S_TEST:
LINE/COL ERROR
-------- ------------------------------------------------------------------------------------
4/29 PL/SQL: ORA-00600: 内部错误代码,参数: [17069], [0x57E77854], [], [], [], [], [], []
4/4 PL/SQL: SQL Statement ignored
可以看到,在同样一个远程对象上面,我可以通过创建视图,然后在该视图上创建存储过程,是没有任何问题的,
但是如果我对该远程操作做一个同义词,再在同义词上创建存储过程,则报出了Ora-00600。跟踪也无果,
看产生的日志文件也不能看出来什么,上metalink,搜索"ora-00600 17069",发现查出现的东西一大堆,
大致是library cache错误,但是到底怎么会产生这个错误呢,online联系上一个oracle在线技术支持,聊了一会儿,
问题是解决了,但是他就是不承认是bug,呵呵。
以下是聊天的总结:
ORA-00600 [17069] reorted on compiling a procedure.
Invalid lock in library cache.
Unable to pin the object and hence the Error.
"Failed to pin a library cache object after 50 attempts"
Clearing the shared memory will help to get rid of inconsistant information
in memory which is causing the error.
The inconsistency was suspected to be in the remote site. But flushing the
shared pool in remote location didnt help.Tried recreation of the procedure
after dropping and recreating the synonym in the local database. But the
same failed.Flushed the shared pool in local database abd successfully
created the procedure.
alter system flush share_pool;
/*=========================================================================*/
/* 建立同义词并使用 */
/*=========================================================================*/
create synonym synonym_name for table_name@db_link;
select * from table_name;
/*=========================================================================*/
/* 建立DB-LINK连接并使用 */
/*=========================================================================*/
create public database link db_link
connect to user identified by pwd
using 'connect string';
select * from dual@db_link;
/*=========================================================================*/
/* 插入时间串 */
/*=========================================================================*/
insert into test(IMPORT_DATE) values
(to_date('2002-10-20 15:30:00','yyyy-mm-dd hh24:mi:ss'));
insert into test(testtime) values(sysdate);
取得时候可以to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
/*=========================================================================*/
/* 更改表时记录其变化的TRIGGER */
/*=========================================================================*/
CREATE OR REPLACE TRIGGER T_APBT_CONTRACT_ALL_AIUDR
after insert or update or delete on APBT_CONTRACT_ALL
for each row
/*无论一条语句改变了多少条记录,ORACLE对于每条记录触发一次触发器*/
/*before和after的区别:事件发生前还是事件发生后*/
begin
if inserting then
insert into A_APBT_CONTRACT_ALL(ID,CONTRACT_SERIAL_NUM,flag)
values (Seq_APBT_ARMS.NEXTVAL,:new.CONTRACT_SERIAL_NUM,1);/* :new*/
elsif updating then
insert into A_APBT_CONTRACT_ALL(ID,CONTRACT_SERIAL_NUM,flag)
values (Seq_APBT_ARMS.NEXTVAL,:old.CONTRACT_SERIAL_NUM,2);
/*此句存在问题*/
elsif deleting then
insert into A_APBT_CONTRACT_ALL(ID,CONTRACT_SERIAL_NUM,flag)
values (Seq_APBT_ARMS.NEXTVAL,:old.CONTRACT_SERIAL_NUM,3);
end if;
end;
/
/*=========================================================================*/
/* 你能够创建被如下语句所触发: */
/*=========================================================================*/
DML语句( DELETE,INSERT,UPDATE)
DDL语句( CREATE,ALTER,DROP)
数据库操作( SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)
/*=========================================================================*/
/* 建立sequence序列: */
/*=========================================================================*/
DROP SEQUENCE ARMS.SEQ_APBT_ARMS;
CREATE SEQUENCE ARMS.SEQ_APBT_ARMS
START WITH 30
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
Oracle Server Manager Release 3.1.7.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved
Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.2.0 - 64bit Production
SVRMGR> Password:
Password:
ORA-01031: insufficient privileges
SVRMGR> ORA-01031: insufficient privileges
SVRMGR>
Server Manager complete.
Database "ora" warm started.
#
显示权限不够;用root去启动oracle当然是不行的;
2)col 用法
col子命令中有两个参数:truncated 和 wrapped,col定义的显示行的宽度,truncated就是如果行超过显示宽度就截断,wrapped就是换行!
/*=========================================================================*/
/* 建立同义词并使用 */
/*=========================================================================*/
我们先看如下的一系列执行:
SQL> create or replace view v_bmw_pay_online_new as
2 select *
3 from taobao.bmw_pay_online_new@lnk_db215;
SQL> create or replace procedure sp_v_test is
2 v_id number;
3 begin
4 select id into v_id from v_bmw_pay_online_new where id=1;
5 end;
6 /
Procedure created
SQL> create or replace synonym s_bmw_pay_online_new
2 for taobao.bmw_pay_online_new@lnk_db215;
Synonym created
SQL> create or replace procedure sp_s_test is
2 v_id number;
3 begin
4 select id into v_id from s_bmw_pay_online_new where id=1;
5 end;
6 /
Warning: Procedure created with compilation errors
SQL> show error
Errors for PROCEDURE TAOBAO.SP_S_TEST:
LINE/COL ERROR
-------- ------------------------------------------------------------------------------------
4/29 PL/SQL: ORA-00600: 内部错误代码,参数: [17069], [0x57E77854], [], [], [], [], [], []
4/4 PL/SQL: SQL Statement ignored
可以看到,在同样一个远程对象上面,我可以通过创建视图,然后在该视图上创建存储过程,是没有任何问题的,
但是如果我对该远程操作做一个同义词,再在同义词上创建存储过程,则报出了Ora-00600。跟踪也无果,
看产生的日志文件也不能看出来什么,上metalink,搜索"ora-00600 17069",发现查出现的东西一大堆,
大致是library cache错误,但是到底怎么会产生这个错误呢,online联系上一个oracle在线技术支持,聊了一会儿,
问题是解决了,但是他就是不承认是bug,呵呵。
以下是聊天的总结:
ORA-00600 [17069] reorted on compiling a procedure.
Invalid lock in library cache.
Unable to pin the object and hence the Error.
"Failed to pin a library cache object after 50 attempts"
Clearing the shared memory will help to get rid of inconsistant information
in memory which is causing the error.
The inconsistency was suspected to be in the remote site. But flushing the
shared pool in remote location didnt help.Tried recreation of the procedure
after dropping and recreating the synonym in the local database. But the
same failed.Flushed the shared pool in local database abd successfully
created the procedure.
alter system flush share_pool;
/*=========================================================================*/
/* 建立同义词并使用 */
/*=========================================================================*/
create synonym synonym_name for table_name@db_link;
select * from table_name;
/*=========================================================================*/
/* 建立DB-LINK连接并使用 */
/*=========================================================================*/
create public database link db_link
connect to user identified by pwd
using 'connect string';
select * from dual@db_link;
/*=========================================================================*/
/* 插入时间串 */
/*=========================================================================*/
insert into test(IMPORT_DATE) values
(to_date('2002-10-20 15:30:00','yyyy-mm-dd hh24:mi:ss'));
insert into test(testtime) values(sysdate);
取得时候可以to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
/*=========================================================================*/
/* 更改表时记录其变化的TRIGGER */
/*=========================================================================*/
CREATE OR REPLACE TRIGGER T_APBT_CONTRACT_ALL_AIUDR
after insert or update or delete on APBT_CONTRACT_ALL
for each row
/*无论一条语句改变了多少条记录,ORACLE对于每条记录触发一次触发器*/
/*before和after的区别:事件发生前还是事件发生后*/
begin
if inserting then
insert into A_APBT_CONTRACT_ALL(ID,CONTRACT_SERIAL_NUM,flag)
values (Seq_APBT_ARMS.NEXTVAL,:new.CONTRACT_SERIAL_NUM,1);/* :new*/
elsif updating then
insert into A_APBT_CONTRACT_ALL(ID,CONTRACT_SERIAL_NUM,flag)
values (Seq_APBT_ARMS.NEXTVAL,:old.CONTRACT_SERIAL_NUM,2);
/*此句存在问题*/
elsif deleting then
insert into A_APBT_CONTRACT_ALL(ID,CONTRACT_SERIAL_NUM,flag)
values (Seq_APBT_ARMS.NEXTVAL,:old.CONTRACT_SERIAL_NUM,3);
end if;
end;
/
/*=========================================================================*/
/* 你能够创建被如下语句所触发: */
/*=========================================================================*/
DML语句( DELETE,INSERT,UPDATE)
DDL语句( CREATE,ALTER,DROP)
数据库操作( SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)
/*=========================================================================*/
/* 建立sequence序列: */
/*=========================================================================*/
DROP SEQUENCE ARMS.SEQ_APBT_ARMS;
CREATE SEQUENCE ARMS.SEQ_APBT_ARMS
START WITH 30
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10617542/viewspace-961531/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10617542/viewspace-961531/