ITPub博客

ora-014380, "value larger than specified precision allows for this column"这种报错很常见，大部时侯我们知道自己用的是什么语句，操作的是哪张表，但是哪个value引起的问题就很难判断了。[@more@]产生ora-01438的原因也不少，比如表上的trigger的级联DML操作引起的，bug的原因，但最主要的原因还是插入的列值过来引起的。按习惯上讲，我们可以做一个1438的errorstack的trace,不过一般这个trace很大且用处也不大，因为只能从中看到哪个表、哪个列、哪条语句引起的问题，但是不清楚哪个值引起的问题。如下以一个测试来粗略说

问题提出 by empo007关于约束ENABLE NOVALIDATE的一个疑问 create table test (id varchar2(12), name varchar2(30) ,constraint pk_test primary kalter table test disable constraint pk_test 然后向表中插入两条完全一样的数据并commit 我的印象是ENABLE NOVALIDATE并不检查已经存在的数SQL> alter table test enable novalidate constraalter table test enable novalidate constraint p* ERROR 位于第 1 行: ORA-02437: 无法验证 (DBO.PK_TEST) - 违反主键[@more@]我的测试及解决方法这个问题实质上是由primary key所依赖的唯一键

Details of RMAN backups are stored in the database control files and optionally a Recovery Catalog. If both these are gone, RMAN cannot restore the database. In such a situation one must extract a control file (or other files) from the backup pieces written out when the last backup was taken. Let's look at an example。本文引自：www.orafaq.com[@more@]Let's take a backup (partial in our case for ilustrative purposes):$ rman target / nocatalogRecovery Manager: Release 10.1.0.2.0 - 64bit ProductionC

RMAN-00571: ======================================RMAN-00569: ===== ERROR MESSAGE STACK FOLLOWS ======RMAN-00571: ======================================RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/19/2006 08:31:40ORA-19597: file /oradata/anny/system01.dbf blocksize 8192 does not match set blocksize of 16384[@more@]问题描述:steven_hua提问：有错误,各位来诊治, 请教了!启动 backup 于 19-4月 -06分配的通道: ORA_DISK_1通道 ORA_DISK_1: sid=142 devtype=DISK通道 ORA_DISK_1: 正在启动 fu

在SQL*Plus中，你可以通过设置autotrace选项来在执行SQL命令的同时，自动的获得语句的执行计划和附加的统计信息。AUTOTRACE是一个很出色的Oracle SQL语句的诊断工具，与Explain plan不同的是这条SQL是实际执行了的，同时AUTOTRACE使用起来也极为方便。[@more@]一、启用Autotrace功能。任何以SQL*PLUS连接的session都可以用Autotrace,不过还是要做一些设置的，否则可能报错。1、报错示例：SQL :> set autotrace on;SP2-0613: Unable to verify PLAN_TABLE format or existenceSP2-0611: Error enabling EXPLAIN reportSP2-0618: Cannot

ORA-19206 EXP-00056 While Taking Export Metalink:206313.1[@more@]Oracle Server Version 9.2.0.1Export Version 9.2.0.1Symptom:---------During export you receive the following errors:EXP-00056: ORACLE error 19206 encounteredORA-19206: Invalid value for query or REF CURSOR parameterORA-06512: at "SYS.DBMS_XMLGEN", line 83ORA-06512: at "SYS.DBMS_METADATA", line 345ORA-06512: at "SYS.DBMS_METADATA", line 410ORA-06512: at "SYS.DBMS_METADATA", line 449ORA-06512: a

在我们的日常的维护中，有时我们会调整一张表的表结构或是修改某一个底层一些的procedure等对象，而对其有依赖性的一些对象就会更得无效。事实上我们常需要看一下有哪些Object对于要修改的这个Object有依赖性以确实影响的范围，从而确定修改的时间。[@more@]一、以HR用户下的Table A进行测试，建一Procedure参照它。SQL> select * from tab where tname='A';TNAME TABTYPE CLUSTERID------------------------------ ------- ----------A TABLECREATE PROCEDURE "HR"."TEST" asv1 number;beginselect count(*) into

Oracle SYS_CONTEXT Function[@more@]Actions As SYSNote: USERENV is an Oracle provided namespace that describes the current session.UsageSELECT sys_context('', '', );FROM dual;SELECT sys_context('USERENV', 'OS_USER') FROM dual;Attribute Return ValueACTIONIdentifies the position in the module (application name) and is set through the DBMS_APPLICATION_INFO package or OCI.AUDITED_CURSORIDReturns the cursor ID of the SQL that triggered the audit. This parameter is not valid in a fine-grained auditing

如果一张表有N多的Extents，当它被Drop时相关的用户进程就可能消耗大量的Cpu，从而影响了正常的应用，这是一个不可避免的事实。其实我们还是可以采取一些措施以对此进行改善的。[@more@]一、流程简介1、先不直接使用Drop table命令，取而带之用带有REUSE STORAGE子句的Tuncate table命令。因为没有Extents要被回收，Trunate table命令执行很快，只是段的highwater mark简单的被调整到段头的block上。2、使用带有KEEP子句的alter table命令逐渐的回收表的Extents，以减轻对Cpu的压力。二、以一个例子来说明：假定有一个表名字为BIGTAB,系统

操作系统是AIX5L ,oslevel是5300-03 ，现在我们想安装oracle 10.1.0.2,安装过程中，系统提示：checking operating system versions :must be 5200 failed,然后就终止了安装。分析原因，给出处理方法。[@more@]版本兼容性方面的一个bug,这样解决：1.编辑Disk1/Install/oraparam.inioraparam.ini 这个文件，修改[Certified Versions]AIX=5100, 5200为[Certified Versions]AIX=5100, 5200, 5300或者2. 使用如下命令启动runInstaller,跳过版本检查:./runInstaller -ignoreSysPrereqs

经常有人会需要Oracle数据库随着操作系统一起启动和关闭，而修改/etc/oratab,设置了oracle SID行启动为Y时实际上是不好用的。本文以一个例子来示范如何去做。[@more@]如下给的是一个example，注意部分内容可能要改，比如oracle的用户名。1、在/sbin/init.d/目录下建立一个文件，并命名为oracle，并授予它555权限，更改它owner为bin:bin。#!/bin/sh## /etc/rc.d/init.d/oracle# 解说∶启动及停止 Oracle 数据库和监听程序# 检查如何执行脚本文件case "$1" instart)echo -n "Starting Oracle Databases: "echo "--

BBED是一个Oracle的内部工具,全名Block Browser/Editor，用于在数据库是Open的时侯浏览/编辑磁盘上的数据结构。在修补Oracle Block的corrupution特别是Block逻辑结构corrupution方面很有用处。为不因翻译影响效果，原引Metalink的原文。[@more@]BBED (Block Browser/Editor)----------------------------1. Getting access to BBED:BBED is shipped with Oracle8 releases, and with some Oracle7.3 releases.BBED is a SUPPORT ONLY tool and should NOT be discussed with customers.On UNIX the tool needs to be built using a comman

常用的Oracle x$ Tables，本文章源自：http://www.adp-gmbh.ch[@more@]x$bhInformation on buffer headers. Contains a record (the buffer header) for each block in the buffer cache. This select statement lists how many blocks are Available, Free and Being Used. select count(*), State from ( select decode (state, 0, 'Free', 1, decode (lrba_seq, 0, 'Available', 'Being Used'), 3, 'Being Used', state) State from x$bh ) group by st

Undo Tablespace Corruption出现问题是一个较常见的问题，硬件的问题、机器的突然断电、人为的误操做及Oracle的Bug都可能引起回滚段段头及段本身的Corruption。[@more@]Platform:Oracle9.2.0.5+Aix5LDuring a long import process, i had added a datafile to my undo tablespace (UNDOTBS1). UNDO_MANAGEMENT initialization parameter was AUTO. Now when the import was going on, I tried dropping the datafile i had just added to the undo tablespace. The drop statement hung so i killed the session. Next i also killed my i

Transaction Table[@more@]==> Transaction Table==> state 0 = IDLE1 = Collecting 2 = Prepared 3 = Committed4 = Forced Abort 5 = Forced Commit 6 = Forced Mixed7 = try again later9 = No TX (Committed) 10= Active local TX==> cflags 1 = TX has started storing collecting information2 = TX has forced the collecting information4 = Prepared TX needs distributed recovery10= Rollback failed on this TX - mark SMON for recover20= TX has rolled back its updates==> wrap# is incremented TX slot reuse

我的一个应用系统库因为online换ibm shark硬件出现了ORA-00600 [kcbgcur_9]，此文章供大家出现同样问题的一个参考。[@more@]1、报错平台操作系统Aix 4.3.3数据库版本Oracle 9.2.0.3其它没有归档方式的备份2、问题描述1IBM的阵列出现了硬件故障，在更换过程中由于执行的是online操作，且数据库是Open的。在更换硬件完毕后，发现数据库Select操作没有问题，部分表空间如：TS_FACT_GSM，DML操作报Ora-00600的错。2检查Alert日志，发现N多的ORA-00600: internal error code, arguments: [kcbgcur_9]。3应用无法正常运行。3、我的诊断流程(1) 检

RMAN> show all;RMAN-00571: ======================================RMAN-00569: ===== ERROR MESSAGE STACK FOLLOWS ======RMAN-00571: ======================================RMAN-03002: failure of show command at 03/24/2006 14:27:06RMAN-03014: implicit resync of recovery catalog failedRMAN-03009: failure of full resync command on default channel at 03/24/2006 14:27:06ORA-19550: cannot use backup/restore functions while using dispatcher 什么原因呢？[@more@]报1950指的是target database，原因是因为rman的

简单说明了一下ora-04201这个报错的处理方法。[@more@]法一、1、建立相关数据字典$ORACLE_HOME/rdbms/admin/catblock.sql 2、查询dba_blockers及dba_waiters找到阻塞或wait进程。select * from dba_blockersselect * from dba_waiters3、杀掉相关的session法二、1、直接用如下语句查找锁定object的sessionSELECT /*+ ordered */W1.SID WAITING_SESSION,H1.SID HOLDING_SESSION,W.KGLLKTYPE LOCK_OR_PIN,W.KGLLKHDL ADDRESS,DECODE(H.KGLLKMOD,0,'None',1,'Null',2,'Share',3,'Exclusive','Unknown') MODE_HELD,DECODE(W.KGLLKREQ,0,'None'

给出非归档模式下当前联机日志文件坏的一处理方法。[@more@]问题描述：求救 系统突破崩溃，oracle9起不来，非归档模式 Microsoft Windows 2000 [Version 5.00.2195](C) 版权所有 1985-2000 Microsoft Corp.C:Documents and SettingsAdministrator>sqlplus /nologSQL*Plus: Release 9.0.1.0.1 - Production on Thu Feb 9 17:16:46 2006(c) Copyright 2001 Oracle Corporation. All rights reserved.SQL> conn / as sysdbaConnected.SQL> shutdownORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL&

实际过程中有时我们会遇到这样的问题，当你用startup试图启动数据库时会遇到ORA-01102的报错，如何处理呢？[@more@]一、提出问题实际过程中有时我们会遇到这样的问题，当你用startup试图启动数据库时会遇到ORA-01102的报错。我们可以在Unix下切换到Oracle的用户，执行一下oerr ora 1102便会看到有关1102的简短的描述，如下：rp2$[/home/ora2]oerr ora 110201102, 00000, "cannot mount database in EXCLUSIVE mode"// *Cause: Some other instance has the database mounted exclusive or shared.// *Action: Shutdown other in