Archives

Oracle生产库报无法truncate某张表，但是可以正常select该表，错误代码：ORA-00604 error occurred at recursive SQL level stringORA-00942 table or view does not exist

登录至该库，检查表结构及表空间均正常，检查alert.log也没有异常，使用sql_trace找到问题。

模拟错误环境：

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Apr 19 08:53:56 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn custom/mondec1Connected.SQL> alter session set sql_trace=true;Session altered.

SQL> truncate table zf_test2;truncate table zf_test2               *ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-00942: table or view does not exist 

查看trc文件

[oracle@redhat udump]$ tail ora9irh_ora_17034.trcSTAT #8 id=2 cnt=0 pid=1 pos=1 obj=171 op='TABLE ACCESS CLUSTER SLOG$'STAT #8 id=3 cnt=1 pid=2 pos=1 obj=169 op='INDEX UNIQUE SCAN I_MLOG#'=====================PARSE ERROR #8:len=38 dep=1 uid=0 oct=7 lid=0 tim=1118562580893969 err=942delete from "CUSTOM"."MLOG$_ZF_TEST2"EXEC #1:c=15998,e=22698,p=0,cr=62,cu=8,mis=0,r=0,dep=0,og=4,tim=1118562580901281ERROR #1:err=604 tim=2871658588STAT #4 id=1 cnt=1 pid=0 pos=1 obj=0 op='FOR UPDATE'STAT #4 id=2 cnt=2 pid=1 pos=1 obj=170 op='TABLE ACCESS CLUSTER OBJ#(170)'STAT #4 id=3 cnt=2 pid=2 pos=1 obj=169 op='INDEX UNIQUE SCAN OBJ#(169)'

该表以前为高级复制的主表，后来取消高级复制后，出现异常。

SQL> select * from dba_objects a where a.object_name=upper('mlog$_zf_test2');no rows selected

SQL> select LOG_OWNER,MASTER,LOG_TABLE from dba_mview_logs;LOG_OWNER       MASTER          LOG_TABLE————— ————— ——————–CUSTOM          ZF_TEST2        MLOG$_ZF_TEST2 

问题就在这里，在取消高级复制时没有使用drop MATERIALIZED VIEW LOG  ON zf_test2;而简单的drop table mlog$_zf_test2，导致问题出现。

解决：

SQL> drop MATERIALIZED VIEW LOG  ON zf_test2;Materialized view log dropped.

SQL> truncate table zf_test2;Table truncated.

起因是这样的，昨天重建TEMP表空间的时候，由于偷懒，仅用以下语句CREATE TABLESPACE TEMPDATA datafile '/dev/vgqddata04/rlvqddata04_2000_01'size 2000M TEMPORARY;所有的参数均使用默认值，然后自己执行了几个小查询，没有出现问题就回家了。

今天上午，有应用程序在执行时报错，错误为： ORA-03232 unable to allocate an extent of 10 blocks from tablespace 8根据错误号查询Metalink，Doc ID:  Note:125271.1Error:  ORA-3232Text:   unable to allocate an extent of %s blocks from tablespace %s ---------------------------------------------------------------------------Cause:  An attempt was made to specify a HASH_MULTIBLOCK_IO_COUNT value that         is greater than the tablespace's NEXT value Action: Increase the value of NEXT for the tablespace using ALTER TABLESPACE         DEFAULT STORAGE or decrease the value of HASH_MULTIBLOCK_IO_COUNT.

This parameter determines how many sequential blocks a hash join reads and writes in one IO operation. The maximum value is operating system dependent.

It is always less than the maximum I/O size of the operating system expressed as Oracle blocks (MAX_IO_SIZE / DB_BLOCK_SIZE).

文档说明这个错误是由于哈希连接时顺序读取或写入的连续数据块大小大于相应表空间的next_extent值而引发的。SQL> select * from v$tablespace where TS#=8;

       TS# NAME---------- ------------------------------         8 TEMPDATA

这个表空间的情况：SQL>  select initial_extent,next_extent,extent_management from dba_tablespaces where tablespace_name='TEMPDATA';

INITIAL_EXTENT NEXT_EXTENT EXTENT_MANAGEMENT-------------- ----------- -----------------       4194304     4194304 DICTIONARY

这个表空间INITIAL_EXTENT和NEXT_EXTENT 大小均为40K，即默认值5*db_block_size的大小，采用字典管理方式。再看这个参数hash_multiblock_io_countSQL> show parameter hash_multiblock_io_count

NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------hash_multiblock_io_count             integer     0应用程序要求连续写入10*blocks的内容，即80K，而NEXT_EXTENT 为40K，引起错误。metalink给出的建议是：Option 1Set the initial and next extent size of the temporary tablespace equal to or greater than MAX_IO_SIZE.MAX_IO_SIZE is an operating system dependant parameter. Please refer to the operating system documentation for information about this parameter.增大表空间的initial 和next值，使之等于或大于操作系统的IO大小（MAX_IO_SIZE）。HP-UX11.0的MAX_IO_SIZE为512KOption 2Set HASH_MULTIBLOCK_IO_COUNT to a value different than 0. This however should notbe required as Oracle computes the value individually for every query.  If you let Oracle do the automatic computation, the value of the parameter appears as 0 in the V$PARAMETER dynamic performance view.设置HASH_MULTIBLOCK_IO_COUNT为非0的值。

考虑到数据读写效率，并参照之前建库的脚本，最后把initial 和next extent 的大小改为了4096K，问题解决。

测试中发现，该sql只需要将initial 和next extent 改为48K即可正常运行。

另外还有一个问题不能解释：当我把initial 和next extent 设为40K时SQL> select blocks,iniexts from sys.seg$ where file#=145;

    BLOCKS    INIEXTS---------- ----------        10          5执行sql时报错，发现是第10个block申请时。并可以理解INIEXTS=40K=5*db_block_size

但是，我将initial 和next extent 设为48K时SQL> select blocks,iniexts from sys.seg$ where file#=145;

    BLOCKS    INIEXTS---------- ----------      3650         10此时，INIEXTS为什么是10呢？

希望能找到答案.

Tom Kyte的One on one，还有Expert Oracle Database Architecture: 9i and 10g，里面都有这样一句，"data from more than one table may be stored on the same block"不理解，google上搜索了一下，查到ASKTOM有解释：Followup:  yes, it is a something that happens with clusters...

Didn't you see the "we have the same rowid" example?....Most of the EMP rows are on the same block as the DEPT rows. This example is somewhat contrived in that I woefully undersized the SIZE parameter on the cluster to make a point, but the approach suggested is correct for an initial load of a cluster. It will ensure that if for some of the cluster keys, you exceed the estimated SIZE, you will still end up with most of the data clustered on the same block. If you load a table at a time, you will not.This technique applies only to the initial load of a cluster—after that, you would use it as your transactions deem necessary. You will not adapt you application to work specifically with a cluster.Here is a bit of puzzle to amaze and astound your friends with. Many people mistakenly believe a rowid uniquely identifies a row in a database, and that given a rowid you can tell what table the row came from. In fact, you cannot. You can and will get duplicate rowids from a cluster. For example, after executing the preceding code you should findops$tkyte@ORA10GR1> select rowid from emp  2  intersect  3  select rowid from dept; ROWID------------------AAAOniAAJAAAAAKAAAAAAOniAAJAAAAAKAABAAAOniAAJAAAAALAAAAAAOniAAJAAAAAMAAAEvery rowid assigned to the rows in DEPT has been assigned to the rows in EMP as well. That is because it takes a table and row ID to uniquely identify a row. The rowid pseudo-column is unique only within a table.

......

明白了

看到RBO和CBO的文章总会说到几种访问方式，像全表扫描，rowid等等，看到一个英文的，拿来学习下

1、Full Table Scans

This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. During a full table scan, all blocks in the table that are under the high water mark are scanned. The high water mark indicates the amount of used space, or space that had been formatted to receive data. Each row is examined to determine whether it satisfies the statement's WHERE clause. When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once.

2、Rowid Scans

The rowid of a row specifies the datafile and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row, because the exact location of the row in the database is specified.

3、Index Scans

The optimizer's decision to use full table scans is influenced by the percentage of blocks accessed, not rows. This is called the index clustering factor.

Although the clustering factor is a property of the index, the clustering factor actually relates to the spread of similar indexed column values within data blocks in the table. A lower clustering factor indicates that the individual rows are concentrated within fewer blocks in the table. Conversely, a high clustering factor indicates that the individual rows are scattered more randomly across blocks in the table. Therefore, a high clustering factor means that it costs more to use a range scan to fetch rows by rowid, because more blocks in the table need to be visited to return the data.

（1） Index Unique Scans

This scan returns, at most, a single rowid. Oracle performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.

When the Optimizer Uses Index Unique Scans This access path is used when all columns of a unique (B-tree) index or an index created as a result of a primary key constraint are specified with equality conditions.

（2）Index Range Scans

An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.

When the Optimizer Uses Index Range Scans The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as the following:■ col1 = :b1■ col1 < :b1■ col1 > :b1■ AND combination of the preceding conditions for leading columns in the index■ col1 like 'ASD%' wild-card searches should not be in a leading position

otherwise the condition col1 like '%ASD' does not result in a range scan.Range scans can use unique or non-unique indexes. Range scans avoid sorting when index columns constitute the ORDER BY/GROUP BY clause.

Index Range Scans Descending

An index range scan descending is identical to an index range scan, except that the data is returned in descending order. Indexes, by default, are stored in ascending order. Usually, this scan is used when ordering data in a descending order to return the most recent data first, or when seeking a value less than a specified value.

INDEX_DESC(table_alias index_name)

（3）Index Skip Scans

Index skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.

Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.

The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

（4）Index Full Scans

A full scan is available if a predicate references one of the columns in the index. The predicate does not need to be an index driver. A full scan is also available when there is no predicate, if both the following conditions are met:■ All of the columns in the table referenced in the query are included in the index.■ At least one of the index columns is not null.A full scan can be used to eliminate a sort operation, because the data is ordered by the index key. It reads the blocks singly.

（5）Index Fast Full Index Scans

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads,unlike a full index scan, and can be parallelized.

You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.

A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

（6）Index Joins

An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation.

INDEX_JOIN

（7）Bitmap Indexes

A bitmap join uses a bitmap for key values and a mapping function that converts each bit position to a rowid. Bitmaps can efficiently merge indexes that correspond to several conditions in a WHERE clause, using Boolean operations to resolve AND and OR conditions.

4、Cluster Access

A cluster scan is used to retrieve, from a table stored in an indexed cluster, all rows that have the same cluster key value. In an indexed cluster, all rows with the same cluster key value are stored in the same data block. To perform a cluster scan, Oracle first obtains the rowid of one of the selected rows by scanning the cluster index. Oracle then locates the rows based on this rowid.

5、Hash Access

A hash scan is used to locate rows in a hash cluster, based on a hash value. In a hash cluster, all rows with the same hash value are stored in the same data block. To perform a hash scan, Oracle first obtains the hash value by applying a hash function to a cluster key value specified by the statement. Oracle then scans the data blocks containing rows with that hash value.

6、Sample Table Scans

A sample table scan retrieves a random sample of data from a simple table or a complex SELECT statement, such as a statement involving joins and views. This access path is used when a statement's FROM clause includes the SAMPLE clause or the SAMPLE BLOCK clause. To perform a sample table scan when sampling by rows with the SAMPLE clause, Oracle reads a specified percentage of rows in the table. To perform a sample table scan when sampling by blocks with the SAMPLE BLOCK clause,Oracle reads a specified percentage of table blocks.

用orapwd修改了数据库的密码文件，然后用DP备份数据库归档日志时报错，错误提示“无法连接目标数据库”，仔细再看有错误代码ORA-01031:insufficcient privileges怎么会突然没有权限了呢，再仔细看SQL> select * from  V$PWFILE_USERS;USERNAME                       SYSDBA SYSOPER------------------------------ ------ ------- 

没有一个用户拥有SYSDBA权限，然后试图给用户授权：grant sysdba to user;ORA-01999 password file mode has changed from 'EXCLUSIVE' to 'SHARED'

再看remote_login_passwordfile参数SQL> show param remote_login_passwordfileNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------remote_login_passwordfile            string      EXCLUSIVE好象也很正常，怎么会不是EXCLUSIVE模式呢？

当密码文件被创建时，它的缺省状态为SHARED。当启动数据库实例时，Oracle系统从初始化参数文件中读取REMOTE_LOGIN_PASSWORDFILE参数的设置。

到此，原因全部明了：我修改了数据库密码文件，造成remote_login_passwordfile重置成SHARED，并且所有sysdba用户全部被revoke。而备份归档日志时，连接target数据库需要sysdba权限，权限不足，导致无法连接，归档失败。

解决方法就是ORA-1999的解决方法：Action: Shut down your instance and start up later when the password file changes are completed for the other instance.

问题解决了，以后不能乱改文件了

临下班时，随意执行一句sql，检查一下表里的数据，突然报错，大致是"No Shared Memory"，然后急忙telnet到主机，发现oracle已经Crash了，SGA也全部释放出来了。检查alert.logErrors in file /oracle/app/oracle/admin/custom/bdump/lgwr_6215_custom.trc:ORA-00313: open failed for members of log group 3 of thread 1ORA-00312: online log 3 thread 1: '/dev/vgqddata01/rredo_3'ORA-27086: skgfglk: unable to lock file - already in useHP-UX Error: 46: No locks availableAdditional information: 8Tue May 16 17:05:33 2006LGWR: terminating instance due to error 313Instance terminated by LGWR, pid = 6215

 还有trc文件*** 2006-05-16 17:05:33.133*** SESSION ID:(3.1) 2006-05-16 17:05:33.111ORA-00313: open failed for members of log group 3 of thread 1ORA-00312: online log 3 thread 1: '/dev/vgqddata01/rredo_3'ORA-27086: skgfglk: unable to lock file - already in useHP-UX Error: 46: No locks availableAdditional information: 8error 313 detected in background processORA-00313: open failed for members of log group 3 of thread 1ORA-00312: online log 3 thread 1: '/dev/vgqddata01/rredo_3'ORA-27086: skgfglk: unable to lock file - already in useHP-UX Error: 46: No locks availableAdditional information: 8

 试图重起数据库，失败ALTER DATABASE OPENTue May 16 17:08:55 2006Errors in file /oracle/app/oracle/admin/custom/bdump/dbw0_8764_custom.trc:ORA-01157: cannot identify/lock data file 157 - see DBWR trace fileORA-01110: data file 157: '/dev/vgqddata04/rlvqddata04_2000_13'ORA-27086: skgfglk: unable to lock file - already in useHP-UX Error: 46: No locks available

从表象上看，应该是主机对设备文件操作时，无法为设备文件分配新的lock，怀疑和nflocks参数有关。先未改参数，重起主机后，起数据库，正常。将nflocks参数改为1632后重起机器及数据库，正常，但是发现参数并未修改成功。

继续实验一下，后面再补。

2006.5.17 update 

 Moving the New Kernel into Place

对内核配置做了修改后，选择Process New Kernel，SAM将对所做的修改生成新的内核。新建的内核有3个组件：1.vmunix_test 静态可执行内核。2.dlkm.vmunix_test 经编译的新内核相关的DLKM模块。3.system.SAM 列出结合到新内核中的静态驱动程序，子系统、参数的一份文本文件。SAM在/stand/build目录中生成新的内核，然而，下一次重新引导时，系统将从/stand/vmunix目录中引导内核。因此，必须将新内核与相关文件移动到/stand，然后重新引导，使改变生效。需要保留一份当前系统的内核，防止新内核不可引导。选择Move Kernel into Palce and Shutdown/Reboot System Now后，SAM将立即对新内核进行备份，并将内核移动到指定位置。若需要稍后再移动并重新引导，执行以下命令：#cd /stand#cp /stand/system /stand/system.prev#cp /stand/build/system.SAM /stand/system#kmupdate /stand/build/vmunix_test#shutdown -ry 0使用kmupdate而不是cp或mv替代当前的vmunix文件或dlkm目录

SQL> alter system switch logfile;

一直hang在那里，看log什么纪录也没有。傻了。想了好久，然后查看V$log:SQL> select group#,archived,status from v$log;    GROUP# ARC STATUS---------- --- ----------------         1 NO  ACTIVE         2 NO  CURRENT         3 NO  INACTIVE

SQL> alter system archive log all;System altered.

唉，解决，现在反应越来越慢了

按照昨天讨论的方法，我写了一个简单的存储过程模拟申请IP的过程，并请朝阳看看，有没有什么问题。

我的大概步骤是这样的：

create table test_ip_table(id number(9) primary key, ip_addr varchar2(20), used varchar2(3));create table test_ip_table_log(seq number, id  number, sleep_time number, apply varchar2(6), now_used number, release number); create sequence SEQ_test_ip_tableminvalue 1maxvalue 999999999999999start with 1increment by 1cache 10;

insert into test_ip_tableselect rownum,'192.168.0.'||rownum,'N'from dba_tables awhere rownum<=5;commit;

用于申请IP的过程:

create or replace procedure proc_test_ip_table(num in number) is  n_seq number(9);  n_id  number(9);  v_state varchar2(3);  v_state2 varchar2(3);  v_result varchar2(6);  n_sleep number(9,3);     --  v_cnt  number;  n_release_id number;  n_rn number;  --  v_msg varchar2(2000); begin    --PART 1  申请IP  begin    select SEQ_test_ip_table.Nextval into n_seq from dual;     select mod(n_seq,num) into n_id from dual;    select used into v_state from test_ip_table where id = n_id;        if(v_state='N')    then      --随机休眠一段时间，模拟未及时提交      select trunc(dbms_random.value(0,20),2) into n_sleep from dual;      dbms_lock.sleep(n_sleep);            --置已用标志      --再次判断改IP是否被人使用      select used into v_state2 from test_ip_table where id = n_id;      if v_state='N'      then         update test_ip_table set used='Y' where id=n_id;        v_result:='SUCC';--成功申请        commit;      else          v_result:='FAIL';--未成功提交      end if;    else      v_result:='CANNOT';--IP不可用    end if;            select count(*) into v_cnt from test_ip_table where used='Y';        insert into test_ip_table_log(seq,id,sleep_time,apply,now_used)    values(n_seq,n_id,n_sleep,v_result,v_cnt);    commit;        --PART 2 随机释放一个目前在用的IP    if (sign(dbms_random.value(-1,1))>=0)    then      select count(*) into v_cnt from test_ip_table where used='Y';      if v_cnt>0 then        select trunc(dbms_random.value(1,v_cnt)) into n_rn from dual;                if n_rn >0        then          select id into n_release_id          from (select rownum rn,id from test_ip_table where used='Y')          where rn=n_rn;          end if;                update test_ip_table set used='N' where id=n_release_id;        update test_ip_table_log        set release=n_release_id        where seq=n_seq;      end if;      commit;          end if;    exception when others then       rollback;    v_msg:=sqlerrm;     end;  end proc_test_ip_table;

然后测试时，开10个session同时执行proc_test_ip_table(5);分别若干次。

纪录的log如下：SQL> select * from test_ip_table_log;       SEQ         ID SLEEP_TIME APPLY    NOW_USED    RELEASE---------- ---------- ---------- ------ ---------- ----------       491          1       0.95 SUCC            1        496          1            CANNOT          1          1       494          4       2.54 SUCC            1        497          2      16.16 SUCC            2        499          4            CANNOT          2        493          3      20.96 SUCC            3          3       502          2            CANNOT          2        498          3      18.41 SUCC            3        504          4            CANNOT          3  

原来考虑可能会出现的v_result:='FAIL';–未成功提交 并没有出现

“五扩”已经提出很久了，其实上上周就说了，一直拖到现在，终于在昨夜实施，到目前为止，后续工作仍在进行中。

昨晚7时许，俺们部门不大的办公室里黑压压的站满了大大小小的领导和干活的人，他们来自公司的各个部门以及厂商的人。主要有以下几类：

1. 完全不干活的，这包括省公司支援中心的和市公司的老总。
2. 自己不干，但下面人干活的，包括计算机中心、动力等部门的领导，还有应用集成商的几个领导。
3. 自己虽然也是个领导，但需要亲自动手的，包括俺们的主管、其他部门的主管，另外还有应用集成商的几个项目经理。
4. 干活的，我们、其他部门的跟割接有关的岗位，还有应用集成商的干活的，还有厂商的工程师。
5. 无关人员，拍照片的。

随着装模做样的宣布开始，“五扩”拉开序幕。其实，这几天一直都在准备，主要是对DataBase的备份，全备和逻辑备份的计划都很合理。谁料想，前两天备份一直都相当完美的带库突然error，两个机械手同时备的时候，到65G左右出以下错：

[Major] From: BMA@njqdnew "qdnew_drive2"  Time: 2006-7-21 23:17:31[90:51]      /dev/rmt/4mn    Cannot write to device ([5] I/O error)

伤，真伤！考虑到今天晚上全备的重要性，决定换一个driver备份，结果到80G时就hang住不动了，仍然失败。这个时候，终于有人想到俺们一直用的都是这个旧的带库终于能有充足的理由换新的了...

经过检查，前几天的全备数据在磁带上应该仍然有效，以防万一，把逻辑备份未考虑的表全部exp。

凌晨1时许，机器陆续下电，进行搬迁，搬迁机房的原因是目前的楼层承重到了，而有有2台新机器以及一个新阵列，工程要求对现有机房进行分拆。

拆机器、阵列的一系列过程略去（反正不是俺动手），到位后就是加电测试，然后改地址，再然后几台机器扩容，包括Superdome加cell板和俺的两台机器加CPU和Memory，这时，俺基本睡着了...

起来以后，俺的机器还没有出现什么问题，VG激活以后起库，基本正常。不过，听说Superdome的cell板在其他机器上好的，插到某台机器上就有问题。俺的机器，起了服务，恢复job，库目前正常。

一个遗留问题，带库报修，把归档模式改成了NoArchivelog，下周再处理。

END 

看了朝阳写的索引扫描还是全表扫描（Index Scan Or Full Table Scan）？以后，请教了朝阳几个问题。

最后，讨论的焦点集中在了Logical data block reads 上，Concepts上关于逻辑读是这样定义的，Logical data block reads include data block reads from both memory and disk.我的理解是，其中的“data block reads from memory”是指的一般意义的从buffer中获得数据的情况，而from disk则表示类似dml这类操作去访问数据块，dml 操作数据最终也是在访问buffer的过程。那么，当一个buffer为空的情况下，读取数据的hit ratio应该为0，公式是

hit ratio = 1 - [physical reads/(block gets + consistent gets)]

那么，0 = 1 - [1/(0+1)]，即产生一个物理读和一个逻辑读。

当然，也有特殊情况，请教了Fenng才明白，物理读仍然是可以获取数据的

Direct reads are performed for parallel scans, for reads from temporary tablespaces, and for LOB access. Blocks are read directly into private buffers in the PGA, rather than into the database buffer cache in the SGA. There are no cache hits, because blocks are not searched for in the cache before being read.

主要表现在 临时表 和 LOB 的情况下，有直接读的情况产生。

另外，今天看朝阳的文章还明白了一个关于Index Scan的理解

在根据索引读取数据的时候，与是否数据在buffer里面并无关系，关键是看索引的键值的顺序和数据存放的物理顺序（在buffer中和磁盘上顺序是一样的）。首先索引会根据条件将符合条件的rowid全部收集起来，然后是根据键值的顺序来get，正是因为这个顺序可能导致读取多个块。

OS:HP-UX 11.11

Oracle:8.1.7.4 compatible 8.1.7

一个建库早期遗留下的数据字典管理表空间，由于碎片已经明显影响该表空间的效率，已经将绝大多数有用数据转移至另一个本地管理表空间中，剩余一些不常用表及其他对象，等待系统略闲时处理。

昨天，一同事建一个大表的索引，表大约6000万数据，40G，随手就将index建到这个表空间上，当建到临时文件写到13G大小时，报错退出，此时，噩梦开始。SMON进程开始连接随片，这个空间上碎片大约40万，可怜的smon运行了3个小时后，仍然没有起色，主机CPU全部占用，进程异常缓慢。

熬到下班，重起主机，shutdown immediate不成功，arort后，再次immediate仍然不成功，手工起alter tablespace ... coalesce，smon进程没有了，碎片减少缓慢，主机仍然高load。

今天早上，还有24万碎片，load逐渐下降，应用基本正常，将可能用到的对象全部挪走，过两天处理这个表空间。

目前dba_free_space仍然基本不能访问，记得修改fet$的文章，风险操作，不敢擅自处理。

请教，正确的处理步骤应当怎样？我有没有错误的处理？因为该主机主要为内部使用，未造成大的影响。

下午不知道对一个表做了一个什么操作，在做关联查询的时候就开始报错“ORA-08103: object no longer exists”，同时伴有ORA-600的错。

SQL> select count(*) from zf_dura_day_detail_tmp2;

  COUNT(*)
----------
   2014639

SQL> desc zf_dura_day_detail_tmp2
Name            Type       Nullable Default Comments
--------------- ---------- -------- ------- --------
STAT_CYCLE_ID   NUMBER     Y                        
SERV_ID         NUMBER(10) Y                        
LS_DURATION_MIN NUMBER     Y                        
TS_DURATION_MIN NUMBER     Y                        

SQL> analyze table ZF_DURA_DAY_DETAIL_TMP2 validate structure cascade;

analyze table ZF_DURA_DAY_DETAIL_TMP2 validate structure cascade

ORA-08103: object no longer exists

开始怀疑是不是有坏块，于是想找出坏块位置。
declare
  cursor c_cur is  
         SELECT ROWID ROW_ID, a.* from zf_dura_day_detail_tmp2 a;
   begin
    for rec in c_cur loop
      insert into zf_rowid_test(row_id,stat_cycle_id,serv_id,ls_duration_min,ts_duration_min)
      values(rec.ROW_ID,rec.stat_cycle_id,rec.serv_id,rec.ls_duration_min,rec.ts_duration_min);
      commit;
    end loop; 
   end; 

/

SQL> select count(*) from zf_rowid_test;

  COUNT(*)
----------
   2014639

结果一切正常，然后再运行分析，整个世界清静了

SQL> analyze table ZF_DURA_DAY_DETAIL_TMP2 validate structure cascade;

Table analyzed

单位的机器一直装的都是英文版的XP，然后加上中文语言包，以前都用的挺好。最近的一次重装後，我发现，PLSQL Developer复制中文就是乱码了，这个对于我来说是狂郁闷的一件事情，无数的统计出来都是中文结果，只要暂时用变通的方法：要么就存成csv格式，要么就Copy to Excel。

今天终于忍受不了了，Google了一下，找到了解决方法。

原来这个地方除了Chinese(PRC)还有一个EN(USA)，问题就处在这个EN上，其实都是输入英文的，只要把这个删除就解决了。以前重装完机器都是看这个不爽就删掉了，这次不晓得怎么会忘记这回事，害的我郁闷了不少天。

C:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Aug 27 12:39:49 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn custom/XXXXX@custom_old
ERROR:
ORA-12154: TNS:could not resolve service name

CUSTOM_OLD =
  (DESCRIPTION =……
  )

   YC_QD =
  (DESCRIPTION =……
  )

cannot insert null to sys.partobj$.DEFTINIEXTS

SQL> select name,created from v$database;

NAME      CREATED
--------- -----------
CUSTOM    2003-10-16