Oracle FAQ 整理

关注

转载最新推荐文章于 2026-04-11 11:18:16 发布 832 阅读 AI 写同款· GEO 优化 ›

Harness运行时：企业级AI Coding的调度中枢与Skill契约工程 Harness 是企业级 AI 编程中承担调度中枢、协议桥接与技能生命周期管理的底层运行时框架，其核心价值在于将自然语言 spec 转化为可执行、可验证、可回滚的机器契约。它通过标准化 Skill 接口（如 SpecParserSkill、DeployGateSkill）实现跨模型、跨环境、跨操作系统的确定性执行，技术原理涵盖 AST 约束树解析、设备亲和性缓存控制、版本语义校验等关键机制。Harness 不替代开发者，而是将工程经验封装为原子化、可观测、可审计的 Skill 契约，广泛应用于混合部署（Wi 阅读详情

安装好Oracle数据库和客户端后,  服务器端用lsnrctl start启动监听程序, 在客户端用net config 配置，
连接数据库, 最后生成tnsnames.ora文件，格式如下(这是管理两个节点的配置).
#C:/oracle/ora92/NETWORK/ADMIN/tnsnames.ora
servicename =
(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.70)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = test)
   )
)

TEST_2 =
(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.71)(PORT = 1526))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = myora)
   )
)

可以用tnsping servicename来测试连接
然后sqlplus username/passwd@servicename 连接

常见问题：

<> ORA-12537: TNS: 连接已关闭

A:监听程序没有启动，运行lsnrctl start命令

<>ORA-12545: 因目标主机或对象不存在，连接失败

A:    检查tnsnames.ora文件配置，主机名和端口是否正确,  监听程序是否启动.

<>ORA-12560: TNS:protocol adapter error

A:   检查tnsnames.ora文件配置，主机名和端口是否正确,  监听程序是否启动. SID是否正确，
可以用tnsping 检测

<>ORA-03113 ：通信通道的文件结束
A:这个原因的问题很多，一般应检查网络状况，或者系统参数的配置

具体见： http://www.chinaunix.net/cgi-bin/bbs/topic.cgi?forum=8&topic=393&show=2340

<>  select 查询时,有2000条记录符合条件,如何先取出符合条件前1000条,然后再取出符合条件的后1000条?

A:
select * from table_name where rownum<=1000;
select * from table_name where rownum<=2000 minus select * from table_name where rownum<=1000;
select * from table_name where rownum<=3000 minus select * from table_name where rownum<=2000;

<>怎样能够查到数据库的名字?

A: select value from v$parameter where upper(name) like '%DB_NAME%'

<>怎样得到一个表的最后更新时间?

A: 1.  打开审计功能, 设置初始化文件: AUDIT_TRAIL = true
  2.  重新启动instance.
  3.  审计表：
AUDIT INSERT,SELECT,DELETE,UPDATE on TableName
by ACCESS WHENEVER SUCCESSFUL
  4.  得到详细信息：
      SELECT OBJ_NAME,ACTION_NAME ,to_char(timestamp,'dd/mm/yyyy , HH:MM:SS')
from sys.dba_audit_object.

<>察看表空间都有哪些表

A; select table_name  from dba_tables where tablespace_name='xxx';

<> 一个不常见的错误：
$ sqlplus
exec(): 0509-036 Cannot load program sqlplus because of the following errors:
      0509-130 Symbol resolution failed for sqlplus because:
      0509-136   Symbol pw_post (number 272) is not exported from
                 dependent module /unix.
      0509-136   Symbol pw_wait (number 273) is not exported from
                 dependent module /unix.
      0509-136   Symbol pw_config (number 274) is not exported from
                 dependent module /unix.
      0509-136   Symbol aix_ora_pw_version3_required (number 275) is not exported from
                 dependent module /unix.
      0509-192 Examine .loader section symbols with the
               'dump -Tv' command.

A:     重新 /etc/loadext -l /etc/pw-syscall (reload)
可能是 Oracle Kernel Extension for aix 在服务器重启动的时候没 load

大家补充
此文章相关评论:
该文章有7个相关评论如下：(点这儿可以发表评论)
hzhrh         发表于： 2002/12/16 05:10pm
我觉得这样FAQ有当然好，但怎样找问题是根本，用Oerr Ora ErrNo，其中ErrNo是错误号。这样每个人都可以知道，问题的所在及知道解决的方法。

yikaikai         发表于： 2002/12/16 05:15pm
但那只是Unix下的啊， Windows下有吗？

freebob         发表于： 2002/12/16 05:40pm
可以把论坛里大家问的，已经解决的，有代表性的，作为本FAQ,以便后来人参考
也是好主意

chaoping         发表于： 2002/12/16 09:45pm
I think i write some useful articles about oracle dbms.
Maybe they are also frequently asked.
Hope it useful to you:)

I post some of them here:

SQLPLUS的AutoTrace是分析SQL的执行计划，执行效率的一个非常简单方便的工具，在绝大多数情况下，也是非常有用的工具。

这里，我们试图归纳一些常见的问题，并进行一定的分析。

1。如何设置和使用AUTOTRACE

SQL> connect / as sysdba
SQL> @?/rdbms/admin/utlxplan.sql

Table created.

SQL> create public synonym plan_table for plan_table;

Synonym created.

SQL> grant select,update,insert,delete on plan_table to public;

Grant succeeded.

SQL> @?/sqlplus/admin/plustrce.sql
SQL>grant plustrace to public.

2. 理解和使用AutoTrace
对于SQL 调整，使用Autotrace是最简单的方法了，我们只需要做：
SQL>SET AUTOTRACE ON
我们就可以看到我们SQL的执行计划，执行成本（PHYSICAL READ/CONSISTENT READ...)
加上SET Timing On或者Set Time On，我们可以得到很多我们需要的数据。

SQL> select nvl(title,' ') from punishinfo_cs where ci_id=45672 ;

NVL(TITLE,'')
--------------------------------------------------
阎王令

Elapsed: 00:00:00.00
SQL> set autotrace on
SQL> /

NVL(TITLE,'')
--------------------------------------------------
阎王令

Elapsed: 00:00:00.71

Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
  1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PUNISHINFO_CS' (Cost=2 C
         ard=1 Bytes=32)

  2 1 INDEX (UNIQUE SCAN) OF 'SYS_C001084' (UNIQUE) (Cost=1 Ca
         rd=1)

Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         3 consistent gets
         0 physical reads
         0 redo size
       376 bytes sent via SQL*Net to client
       425 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

3.关于使用Autotrace的一些常见疑问：

a.比如我上面的例子，我不用Autotrace，我的时间小于0.01S，但是用了Autotrace，我的执行时间变成了0.71S.
不注意的人往往会认为，或者没有测试不用Autotrace时候的情况，往往会忽视这个数字，认为时间就是0.71S.
实际上，这个0.7S，是花在Autotrace里面的时间。由于Autotrace需要记录你的SQL执行的成本，这个本身是往数据库里面读取和写入一定的数据的，需要一定的时间。当你的SQL执行时间足够短的时候，这个由于Autotrace带来的时间就变成非常可观的了。我们就需要通过不用 Autotrace的时间，和使用Autotrace的执行成本来结合比较。

我们通过结合Autotrace和Tkprof/SQLTRACE，很容易知道，AUtotrace就近作了什么：

select nvl(title,' ')  from   punishinfo_cs where ci_id=45672    call     count       cpu    elapsed       disk      query    current        rows  ------- ------  -------- ---------- ---------- ---------- ----------  ----------  Parse        1      0.00       0.01          0          0          0           0  Execute      1      0.00       0.00          0          0          0           0  Fetch        2      0.00       0.00          0          3          0           1  ------- ------  -------- ---------- ---------- ---------- ----------  ----------  total        4      0.00       0.01          0          3          0           1    DELETE FROM PLAN_TABLE  WHERE   STATEMENT_ID=:1    EXPLAIN PLAN SET STATEMENT_ID='PLUS185025' FOR  select nvl(title,' ') from    punishinfo_cs where ci_id=45672    insert into plan_table (statement_id, timestamp, operation, options,    object_node, object_owner, object_name, object_instance, object_type,    search_columns, id, parent_id, position, other,optimizer, cost, cardinality,     bytes, other_tag, partition_start, partition_stop, partition_id,    distribution )  values  (:1,SYSDATE,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,    :20,:21,:22)    SELECT ID ID_PLUS_EXP,PARENT_ID PARENT_ID_PLUS_EXP,LPAD(' ',2*(LEVEL-1))    ||OPERATION||DECODE(OTHER_TAG,NULL,'','*')||DECODE(OPTIONS,NULL,'','    ('||OPTIONS||')')||DECODE(OBJECT_NAME,NULL,'',' OF '''||OBJECT_NAME||'''')    ||DECODE(OBJECT_TYPE,NULL,'',' ('||OBJECT_TYPE||')')||DECODE(ID,0,    DECODE(OPTIMIZER,NULL,'',' Optimizer='||OPTIMIZER))||DECODE(COST,NULL,'','    (Cost='||COST||DECODE(CARDINALITY,NULL,'',' Card='||CARDINALITY)    ||DECODE(BYTES,NULL,'',' Bytes='||BYTES)||')') PLAN_PLUS_EXP,OBJECT_NODE    OBJECT_NODE_PLUS_EXP  FROM   PLAN_TABLE START WITH ID=0 AND STATEMENT_ID=:1 CONNECT BY PRIOR ID=PARENT_ID    AND STATEMENT_ID=:1 ORDER BY ID,POSITION    SELECT ID ID_PLUS_EXP,OTHER_TAG OTHER_TAG_PLUS_EXP,OTHER OTHER_PLUS_EXP  FROM   PLAN_TABLE WHERE STATEMENT_ID=:1 AND OTHER_TAG IS NOT NULL ORDER BY ID

我们看到，由于我们的Autotrace，简简单单的一句话，实际上oracle 做了那么多的事情。

3.关于使用Autotrace的一些常见疑问(2)

B。什么叫做Recursive Call？
为什么Recursive Call那么多？
首先我们要明白，什么是Recursive Call，为什么需要Recursive Call：
下面是我摘自Oracle 9.2的Document的一段话（oracle 9.2 performance and tuning reference)

Understanding Recursive Calls
Sometimes, in order to execute a SQL statement issued by a user, Oracle must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, then Oracle makes recursive calls to allocate the space dynamically. Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk.

If recursive calls occur while the SQL trace facility is enabled, then TKPROF produces statistics for the recursive SQL statements and marks them clearly as recursive SQL statements in the output file. You can suppress the listing of Oracle internal recursive calls (for example, space management) in the output file by setting the SYS command-line parameter to NO. The statistics for a recursive SQL statement are included in the listing for that statement, not in the listing for the SQL statement that caused the recursive call. So, when you are calculating the total resources required to process a SQL statement, consider the statistics for that statement as well as those for recursive calls caused by that statement.

有时候我们会看到一些看上去很奇怪的问题，比如，有时候，我们的Autotrace，会显示，Select语句也会有Redo 产生。
这里可能有两种情况：
1。Delayed Block Cleanout：
比如下面这个例子：

SQL>DELETE FROM T WHERE ROWNUM<100;
SQL>DELETE FROM T WHERE ROWNUM<100;
SQL>DELETE FROM T WHERE ROWNUM<100;
SQL>DELETE FROM T WHERE ROWNUM<100;
COMMIT;

SQL> select count(*) from t;

COUNT(*)
----------
    25606

Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE
  1 0 SORT (AGGREGATE)
  2 1 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
         0 recursive calls
        12 db block gets
       326 consistent gets
         0 physical reads
       360 redo size
       369 bytes sent via SQL*Net to client
       426 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

第二种情况是：
用户用来排序的临时表空间不是真正的临时表空间：
例子：

SQL> L
1* ALTER USER TEST TEMPORARY TABLESPACE SYSTEM
SQL> /

User altered.

SQL> SELECT * FROM T ORDER BY 1,2,3,4,5,6;

25606 rows selected.

Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE
  1 0 SORT (ORDER BY)
  2 1 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
      1382 recursive calls
       286 db block gets
       740 consistent gets
       809 physical reads
     28264 redo size
    1239304 bytes sent via SQL*Net to client
    189903 bytes received via SQL*Net from client
      1709 SQL*Net roundtrips to/from client
         0 sorts (memory)
         1 sorts (disk)
     25606 rows processed

我们把用户的临时表空间重新改成Locally MANAGED TEMPORARY TABLESPACE:

SQL> ALTER USER TEST TEMPORARY TABLESPACE TEMP;

User altered.

SQL> CONN TEST/TEST
Connected.
SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT * FROM T ORDER BY 1,2,3,4,5,6;

25606 rows selected.

Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE
  1 0 SORT (ORDER BY)
  2 1 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
         0 recursive calls
        53 db block gets
       320 consistent gets
       808 physical reads
         0 redo size
   1239304 bytes sent via SQL*Net to client
    189903 bytes received via SQL*Net from client
      1709 SQL*Net roundtrips to/from client
         0 sorts (memory)
         1 sorts (disk)
     25606 rows processed

还有一个挺难理解的现象：

SQL> conn internal
Connected.
SQL> set autotrace traceonly;
SQL> select * from test.t ORDER BY 1,2,3,4,5,6;

25606 rows selected.

Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE
  1 0 SORT (ORDER BY)
  2 1 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         0 consistent gets
         0 physical reads
         0 redo size
         0 bytes sent via SQL*Net to client
         0 bytes received via SQL*Net from client
         0 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
     25606 rows processed

同样的语句，没有任何Trace的值。
这里，是因为我用Internal用户的连接，或者说，SYSDBA权限的连接。
用这个用户连接，没有Trace的结果的。

这一点，特别感谢Oldwain老哥，我当时也是想了很久也没有想出来。

chaoping         发表于： 2002/12/16 09:46pm
If you have further questions on this topic, please post your question to my mailbox or :
http://www.cnoug.org/bin/ut/topic_show.cgi?id=229&h=1&bpg=1&age=0

chaoping         发表于： 2002/12/16 09:47pm
This topic is about Oracle process memory utilization in linux:

前面brain 讲述了如何查看Solaris下面的Oracle的连接所使用的内存，我们来看看Linux下，这个问题如何分析。

Solaris用来查看内存占用的命令主要是pmap , 位于
$ which pmap
/usr/proc/bin/pmap

我们通过Pmap可以看到每个进程的heap area, stack area, shared memory, etc.

Linux平台没有Pmap命令，但是Linux提供了强大的Proc文件系统。

我们通过Proc文件系统，可以看到很多的秘密。

--------------------------------------------------------------------------------
为了一个共同的梦
China Oracle User Group
chao_ping

版主

发帖: 301
于2002-12-03 13:52
--------------------------------------------------------------------------------
每对应一个进程，在Linux Proc文件系统下面，都会有一个新的目录生成，目录名字就是相应的进程号。

我们检查当前状态下这个进程的资源占用：

[oracle@ish3 8229]$ cat status
Name: oracle
State: S (sleeping)
Pid: 8229
PPid: 8228
TracerPid: 0
Uid: 504 504 504 504
Gid: 505 505 505 505
FDSize: 1024
Groups: 505
VmSize: 821544 kB
VmLck: 0 kB
VmRSS: 40224 kB
VmData: 428 kB
VmStk: 52 kB
VmExe: 22752 kB
VmLib: 5024 kB
SigPnd: 0000000000000000
SigBlk: 0000000080000000
SigIgn: 0000000006005203
SigCgt: 0000000389c02cfc
CapInh: 0000000000000000
CapPrm: 0000000000000000
CapEff: 0000000000000000
SQL> @myspid
SPID
---------
8229

SQL> host ps -aux|grep 8229
oracle 8229 0.8 2.4 831612 51456 ? S 11:23 0:16 oraclecatalog (DE
oracle 8712 0.0 0.0 1628 616 pts/2 S 11:55 0:00 grep 8229

SQL> @whoisit
Enter value for spid: 8229
old 3: select addr from v$process where spid in(&spid))
new 3: select addr from v$process where spid in(8229))

      SID SERIAL# USERNAME OSUSER MACHINE PROGRAM PROCESS TO_CHAR(LOGON_TIME,
---------- ---------- ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- -------------------
       36 21957 USER oracle ish3 sqlplus@ish3 (TNS V1-V3) 8228 2002/12/03 11:23:17

SQL> @sesstat
Enter value for sid: 36
old 3: where a.sid=&sid
new 3: where a.sid=36

    VALUE NAME
---------- --------------------------------------------------
    48136 session uga memory
    80192 session uga memory max
   170224 session pga memory
   170224 session pga memory max
       34 sorts (memory)


chao_ping 编辑于 2002-12-03 13:54

--------------------------------------------------------------------------------
为了一个共同的梦
China Oracle User Group
chao_ping

版主

发帖: 301
于2002-12-03 13:58
--------------------------------------------------------------------------------
我现在做一个小测试，
我修改我的sort_area_size,到10M（当前默认是64k). 然后我做一个大的排序。(test.sql: select * from some_big_table order by 1,2,3,4,5,6).

SQL>
1* alter session set sort_area_size=10000000
SQL> /

Session altered.

Elapsed: 00:00:00.06
SQL> @test.sql
－－这里做测试，用完所有的sort_area_size.
SQL> set term on
SQL> @sesstat
Enter value for sid: 36
old 3: where a.sid=&sid
new 3: where a.sid=36

    VALUE NAME
---------- --------------------------------------------------
    54828 session uga memory
   121176 session uga memory max
10496600 session pga memory
10496600 session pga memory max
       43 sorts (memory)
我们看到，PGA的占用马上就达到了10M。
我们再检查相应的/proc文件系统下面的oracle进程的状态：

[oracle@ish3 8229]$ cat status
Name: oracle
State: D (disk sleep)
Pid: 8229
PPid: 8228
TracerPid: 0
Uid: 504 504 504 504
Gid: 505 505 505 505
FDSize: 1024
Groups: 505
VmSize: 831612 kB
VmLck: 0 kB
VmRSS: 51236 kB
VmData: 10496 kB
VmStk: 52 kB
VmExe: 22752 kB
VmLib: 5024 kB
SigPnd: 0000000000000000
SigBlk: 0000000080000000
SigIgn: 0000000006005203
SigCgt: 0000000389c02cfc
CapInh: 0000000000000000
CapPrm: 0000000000000000
CapEff: 0000000000000000
我们看到，从v$sesstat和/proc，这个进程的使用资源状态都明显变化。

chao_ping 编辑于 2002-12-03 13:59

--------------------------------------------------------------------------------
为了一个共同的梦
China Oracle User Group
chao_ping

版主

发帖: 301
于2002-12-03 14:02
--------------------------------------------------------------------------------
其实，Solaris和Linux本身自带的ps命令或者Top之类，对于这些使用共享内存的进程，无法准确看到这个进程使用了多少资源。

因为这些进程也可以读写共享内存，ps把共享内存（对于Oracle，就是SGA）也包含在里面了。
这样的话，就无法准确看到某个进程占用了多少内存。

我们使用proc文件系统，或者Pmap来准确的看究竟系统使用了多少资源。

对于别的Unix，由于没有环境，无法准确用实例来说明，欢迎大家各抒己见，解决这个问题。

收录于