2007年01月12日

2007年01月11日

—- 在国内外大中型数据库管理系统中,把ORACLE作为数据库管理平台的用户比
较多。ORACLE 不论是数据库管理能力还是安全性都是无可非议的,但是,它在汉
字信息的显示方面着实给中国用户带来不少麻烦,笔者多年从事ORACLE数据库管
理,经常收到周围用户和外地用户反映有关ORACLE数据库汉字显示问题的求援信,
主要现象是把汉字显示为不可识别的乱码,造成原来大量信息无法使用。本文将就
这一问题产生的原因和解决办法进行一些探讨,供存在这方面问题的用户朋友参
考。  
—- 1、原因分析  
  
—- 通过对用户反映情况的分析,发现字符集的设置不当是影响ORACLE数据库汉
字显示的关键问题。那么字符集是怎么一会事呢?字符集是ORACLE 为适应不同语
言文字显示而设定的。用于汉字显示的字符集主要有ZHS16CGB231280,
US7ASCII,WE8ISO8859P1等。字符集不仅需在服务器端存在,而且客户端也必须
有字符集注册。服务器端,字符集是在安装ORACLE时指定的,字符集登记信息存储
在ORACLE数据库字典的V$NLS_PARAMETERS表中;客户端,字符集分两种情况,一
种情况是sql*net 2.0以下版本,字符集是在windows的系统目录下的oracle.ini
文件中登记的;另一种情况是sql*net 2.0以上(即32位)版本,字符集是在  
windows的系统注册表中登记的。要在客户端正确显示ORACLE 数据库汉字信息,首
先必须使服务器端的字符集与客户端的字符集一致;其次是加载到ORACLE数据库的
数据字符集必须与服务器指定字符集一致。因此,把用户存在的问题归纳分类,产
生汉字显示异常的原因大致有以下几种:  
  
—- 1. 1服务器指定字符集与客户字符集不同,而与加载数据字符集一致。  
  
—- 这种情况是最常见的,只要把客户端的字符集设置正确即可,解决办法见
2.1。  
  
—- 1. 2服务器指定字符集与客户字符集相同,与加载数据字符集不一致。  
  
—- 这类问题一般发生在ORACLE版本升级或重新安装系统时选择了与原来服务器
端不同的字符集,而恢复加载的备份数据仍是按原字符集卸出的场合,以及加载从其
它使用不同字符集的ORACLE数据库卸出的数据的情况。这两种情况中,不管服务器
端和客户端字符集是否一致都无法显示汉字。解决办法见2.2。  
  
—- 1.3服务器指定字符集与客户字符集不同,与输入数据字符集不一致。  
  
—- 这种情况是在客户端与服务器端字符集不一致时,从客户端输入了汉字信
息。输入的这些信息即便是把客户端字符集更改正确,也无法显示汉字。解决办法
见2.3。  
  
—- 2.解决办法  
  
—- 下面将分别对上述三种情况给出解决办法。为了叙述方便,假设客户端使用
WINDOWS95/98环境,并已成功地配置了TCP/IP协议,安装了ORACLE的sql*net,
sql*pluse产品。  
  
—- 2.1 设置客户端字符集与服务器端字符集一致  
  
—- 假设当前服务器端使用US7ASCII字符集。  
  
—- (1)查看服务器端字符集  
  
—- 通过客户端或服务器端的sql*plus登录ORACLE的一个合法用户,执行下列
SQL语句:  
  
SQL > select * from V$NLS_PARAMETERS  
parameter value
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
…. ….
NLS_CHARACTERSET US7ASCII
NLS_SORT BINARY
NLS_NCHAR_CHARACTERSET US7ASCII
  
—- 从上述列表信息中可看出服务器端ORACLE数据库的字符集为’US7ASCII’。  
  
—- (2)按照服务器端字符集对客户端进行配置  
  
—- 配置方法有两种:  
  
安装ORACLE的客户端软件时指定  
—- 在安装ORACLE的客户端产品软件时,选择与ORACLE服务端一致的字符集(本
例为US7ASCII)即可。  
  
修改注册信息的方法  
—- 根据ORACLE 客户端所选sql*net 的版本分为下列两种情况:  
  
—- a. 客户端为 sql*net 2.0 以下版本  
  
—- 进入Windows的系统目录,编辑oracle.ini文件,用US7ASCII替换原字符
集,重新启动计算机,设置生效。  
  
—- b. 客户端为 sql*net 2.0 以上版本  
  
—- 在WIN98 下 运 行REGEDIT,第一步选HKEY_LOCAL_MACHINE,第二步选择
SOFTWARE, 第三步选择 ORACLE, 第四步选择 NLS_LANG, 键 入 与服 务 器  
端 相 同 的 字 符 集(本例为:AMERICAN_AMERICAN.US7ASCII)。  
  
—- 2.2 强制加载数据字符集与服务器端字符集一致  
  
—- 假设要加载数据从原ORACLE数据库卸出时的字符集为US7ASCII,当前ORACLE
服务器字符集为WE8ISO8859P1。  
  
—- 下面提供三种解决方法:  
  
—- (1) 服务器端重新安装ORACLE  
  
—- 在重新安装ORACLE 时选择与原卸出数据一致的字符集(本例为
US7ASCII)。  
  
—- 加载原卸出的数据。  
  
—- 这种情况仅仅使用于空库和具有同一种字符集的数据。  
  
—- (2)强行修改服务器端ORACLE当前字符集  
  
—- 在用imp命令加载数据前,先在客户端用sql*plus登录system DBA用户,执
行下列SQL语句进行当前ORACLE数据库字符集修改:  
  
SQL > create database character set US7ASCII
* create database character set US7ASCII
ERROR at line 1:
ORA-01031: insufficient privileges
  
—- 你会发现语句执行过程中,出现上述错误提示信息,此时不用理会,实际上
ORACLE数据库的字符集已被强行修改为US7ASCII,接着用imp命令装载数据。等数
据装载完成以后,shutdown 数据库,再startup 数据库,用合法用户登录ORACLE
数据库,在sql>命令提示符下,运行select * from V$NLS_PARAMETERS,可以看
到ORACLE数据库字符集已复原,这时再查看有汉字字符数据的表时,汉字已能被正
确显示。  
  
—- (3)利用数据格式转储,避开字符集限制  
  
—- 这种方法主要用于加载外来ORACLE数据库的不同字符集数据。其方法如下:  
  
—- 先将数据加载到具有相同字符集的服务器上,然后用转换工具卸出为
foxbase 格式或access格式数据库,再用转换工具转入到不同字符集的ORACLE数
据库中,这样就避免了ORACLE字符集的困扰。目前数据库格式转换的工具很多,象
power builder5.0以上版本提供的pipeline,Microsoft Access数据库提供的数
据导入/导出功能等。转换方法参见有关资料说明。.  
  
—- 2.3匹配字符集替换汉字  
  
—- 对于1.3提到的情况,没有很好的办法,只能先把客户端与服务器端字符集匹  
配一致后,根据原输入汉字的特征码替换汉字字符部分。

2007年01月10日
       SQL*PLUS命令的使用大全
   
       Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中,可以运行sql*plus命令与sql*plus语句。
   我们通常所说的DML、DDL、DCL语句都是sql*plus语句,它们执行完后,都可以保存在一个被称为sql buffer的内存区域中,并且只能保存一条最近执行的sql语句,我们可以对保存在sql buffer中的sql 语句进行修改,然后再次执行,sql*plus一般都与数据库打交道。
   除了sql*plus语句,在sql*plus中执行的其它语句我们称之为sql*plus命令。它们执行完后,不保存在sql buffer的内存区域中,它们一般用来对输出的结果进行格式化显示,以便于制作报表。
   下面就介绍一下一些常用的sql*plus命令:
  
1. 执行一个SQL脚本文件
SQL>start file_name
SQL>@ file_name
我们可以将多条sql语句保存在一个文本文件中,这样当要执行这个文件中的所有的sql语句时,用上面的任一命令即可,这类似于dos中的批处理。

@与@@的区别是什么?
@等于start命令,用来运行一个sql脚本文件。
@命令调用当前目录下的,或指定全路径,或可以通过SQLPATH环境变量搜寻到的脚本文件。该命令使用是一般要指定要执行的文件的全路径,否则从缺省路径(可用SQLPATH变量指定)下读取指定的文件。
@@用在sql脚本文件中,用来说明用@@执行的sql脚本文件与@@所在的文件在同一目录下,而不用指定要执行sql脚本文件的全路径,也不是从SQLPATH环境变量指定的路径中寻找sql脚本文件,该命令一般用在脚本文件中。
如:在c:\temp目录下有文件start.sql和nest_start.sql,start.sql脚本文件的内容为:
@@nest_start.sql     - – 相当于@ c:\temp\nest_start.sql
则我们在sql*plus中,这样执行:
SQL> @ c:\temp\start.sql

2. 对当前的输入进行编辑
SQL>edit
  
3. 重新运行上一次运行的sql语句
SQL>/
  
4. 将显示的内容输出到指定文件
SQL> SPOOL file_name
   在屏幕上的所有内容都包含在该文件中,包括你输入的sql语句。
  
5. 关闭spool输出
SQL> SPOOL OFF
   只有关闭spool输出,才会在输出文件中看到输出的内容。
  
6.显示一个表的结构
SQL> desc table_name
  
7. COL命令:
主要格式化列的显示形式。
该命令有许多选项,具体如下:
COL[UMN] [{ column|expr} [ option ...]]
Option选项可以是如下的子句:
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE { expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
  
1). 改变缺省的列标题
COLUMN column_name HEADING column_heading
For example:
Sql>select * from dept;
     DEPTNO DNAME                        LOC
———- —————————- ———
         10 ACCOUNTING                   NEW YORK
sql>col  LOC heading location
sql>select * from dept;
    DEPTNO DNAME                        location
——— —————————- ———–
        10 ACCOUNTING                   NEW YORK
  
2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上:
Sql>select * from emp
Department  name           Salary
———- ———- ———-
         10 aaa                11         
SQL> COLUMN ENAME HEADING ’Employee|Name’
Sql>select * from emp
            Employee
Department  name           Salary
———- ———- ———-  
         10 aaa                11
note: the col heading turn into two lines from one line.
  
3). 改变列的显示长度:
FOR[MAT] format
Sql>select empno,ename,job from emp;
      EMPNO ENAME      JOB        
———- ———-     ———
       7369 SMITH      CLERK      
       7499 ALLEN      SALESMAN   
7521 WARD       SALESMAN   
Sql> col ename format a40
      EMPNO ENAME                                    JOB
———-   —————————————-         ———
       7369 SMITH                                    CLERK
       7499 ALLEN                                    SALESMAN
       7521 WARD                                    SALESMAN
  
4). 设置列标题的对齐方式
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
SQL> col ename justify center
SQL> /
      EMPNO           ENAME                   JOB
———-   —————————————-       ———
       7369 SMITH                                    CLERK
       7499 ALLEN                                    SALESMAN
7521 WARD                                     SALESMAN
对于NUMBER型的列,列标题缺省在右边,其它类型的列标题缺省在左边
  
5). 不让一个列显示在屏幕上
NOPRI[NT]|PRI[NT]
SQL> col job noprint
SQL> /
      EMPNO           ENAME
———-     —————————————-
       7369 SMITH
       7499 ALLEN
7521 WARD
  
6). 格式化NUMBER类型列的显示:
SQL> COLUMN SAL FORMAT $99,990
SQL> /
Employee
Department Name        Salary    Commission
———- ———- ——— ———-
30          ALLEN        $1,600    300
  
7). 显示列值时,如果列值为NULL值,用text值代替NULL值
COMM NUL[L] text
SQL>COL COMM NUL[L] text
  
8). 设置一个列的回绕方式
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
        COL1
——————–
HOW ARE YOU?
  
SQL>COL COL1 FORMAT A5
SQL>COL COL1 WRAPPED
COL1
—–
HOW A
RE YO
U?
  
SQL> COL COL1 WORD_WRAPPED
COL1
—–
HOW
ARE
YOU?
  
SQL> COL COL1 WORD_WRAPPED
COL1
—–
HOW A
  
9). 显示列的当前的显示属性值
SQL> COLUMN column_name
  
10). 将所有列的显示属性设为缺省值
SQL> CLEAR COLUMNS
  
8. 屏蔽掉一个列中显示的相同的值
BREAK ON break_column
SQL> BREAK ON DEPTNO
SQL> SELECT DEPTNO, ENAME, SAL
FROM EMP
  WHERE SAL < 2500
  ORDER BY DEPTNO;
DEPTNO      ENAME         SAL
———- ———– ———
10           CLARK        2450
MILLER      1300
20            SMITH       800
ADAMS       1100
  
9. 在上面屏蔽掉一个列中显示的相同的值的显示中,每当列值变化时在值变化之前插入n个空行。
BREAK ON break_column SKIP n
  
SQL> BREAK ON DEPTNO SKIP 1
SQL> /
DEPTNO ENAME SAL
———- ———– ———
10 CLARK 2450
MILLER 1300
  
20 SMITH 800
ADAMS 1100
  
10. 显示对BREAK的设置
SQL> BREAK
  
11. 删除6、7的设置
SQL> CLEAR BREAKS
  
12. Set 命令:
该命令包含许多子命令:
SET system_variable value
system_variable value 可以是如下的子句之一:
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {15|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT] {ON|OFF}
AUTORECOVERY [ON|OFF]
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
BLO[CKTERMINATOR] {.|c}
CMDS[EP] {;|c|ON|OFF}
COLSEP {_|text}
COM[PATIBILITY]{V7|V8|NATIVE}
CON[CAT] {.|c|ON|OFF}
COPYC[OMMIT] {0|n}
COPYTYPECHECK {ON|OFF}
DEF[INE] {&|c|ON|OFF}
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
ECHO {ON|OFF}
EDITF[ILE] file_name[.ext]
EMB[EDDED] {ON|OFF}
ESC[APE] {\|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF}
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
FLU[SH] {ON|OFF}
HEA[DING] {ON|OFF}
HEADS[EP] {||c|ON|OFF}
INSTANCE [instance_path|LOCAL]
LIN[ESIZE] {80|n}
LOBOF[FSET] {n|1}
LOGSOURCE [pathname]
LONG {80|n}
LONGC[HUNKSIZE] {80|n}
MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL
{ON|OFF}] [PRE[FORMAT] {ON|OFF}]
NEWP[AGE] {1|n|NONE}
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {10|n}
PAGES[IZE] {24|n}
PAU[SE] {ON|OFF|text}
RECSEP {WR[APPED]|EA[CH]|OFF}
RECSEPCHAR {_|c}
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_
WRAPPED]|TRU[NCATED]}]
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
SHOW[MODE] {ON|OFF}
SQLBL[ANKLINES] {ON|OFF}
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {ON|OFF}
SQLPRE[FIX] {#|c}
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|ON|OFF}
SUF[FIX] {SQL|text}
TAB {ON|OFF}
TERM[OUT] {ON|OFF}
TI[ME] {ON|OFF}
TIMI[NG] {ON|OFF}
TRIM[OUT] {ON|OFF}
TRIMS[POOL] {ON|OFF}
UND[ERLINE] {-|c|ON|OFF}
VER[IFY] {ON|OFF}
WRA[P] {ON|OFF}
  
1). 设置当前session是否对修改的数据进行自动提交
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
  
2).在用start命令执行一个sql脚本时,是否显示脚本中正在执行的SQL语句
SQL> SET ECHO {ON|OFF}
  
3).是否显示当前sql语句查询或修改的行数
SQL> SET FEED[BACK] {6|n|ON|OFF}
   默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ,则不管查询到多少行都返回。当为off 时,一律不显示查询的行数
  
4).是否显示列标题
SQL> SET HEA[DING] {ON|OFF}
当set heading off 时,在每页的上面不显示列标题,而是以空白行代替
  
5).设置一行可以容纳的字符数
SQL> SET LIN[ESIZE] {80|n}
   如果一行的输出内容大于设置的一行可容纳的字符数,则折行显示。
  
6).设置页与页之间的分隔
SQL> SET NEWP[AGE] {1|n|NONE}
当set newpage 0 时,会在每页的开头有一个小的黑方框。
当set newpage n 时,会在页和页之间隔着n个空行。
当set newpage none 时,会在页和页之间没有任何间隔。
  
7).显示时,用text值代替NULL值
SQL> SET NULL text
  
8).设置一页有多少行数
SQL> SET PAGES[IZE] {24|n}
如果设为0,则所有的输出内容为一页并且不显示列标题
  
9).是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。
SQL> SET SERVEROUT[PUT] {ON|OFF}  
在编写存储过程时,我们有时会用dbms_output.put_line将必要的信息输出,以便对存储过程进行调试,只有将serveroutput变量设为on后,信息才能显示在屏幕上。
  
10).当SQL语句的长度大于LINESIZE时,是否在显示时截取SQL语句。
SQL> SET WRA[P] {ON|OFF}
   当输出的行的长度大于设置的行的长度时(用set linesize n命令设置),当set wrap on时,输出行的多于的字符会另起一行显示,否则,会将输出行的多于字符切除,不予显示。
  
11).是否在屏幕上显示输出的内容,主要用与SPOOL结合使用。
SQL> SET TERM[OUT] {ON|OFF}
   在用spool命令将一个大表中的内容输出到一个文件中时,将内容输出在屏幕上会耗费大量的时间,设置set termspool off后,则输出的内容只会保存在输出文件中,不会显示在屏幕上,极大的提高了spool的速度。
  
12).将SPOOL输出中每行后面多余的空格去掉
SQL> SET TRIMS[OUT] {ON|OFF}  
   
13)显示每个sql语句花费的执行时间
set TIMING  {ON|OFF}

14). 遇到空行时不认为语句已经结束,从后续行接着读入。
SET SQLBLANKLINES ON
Sql*plus中, 不允许sql语句中间有空行, 这在从其它地方拷贝脚本到sql*plus中执行时很麻烦. 比如下面的脚本:
select deptno, empno, ename
from emp

where empno = ‘7788′;
如果拷贝到sql*plus中执行, 就会出现错误。这个命令可以解决该问题

15).设置DBMS_OUTPUT的输出
SET SERVEROUTPUT ON BUFFER 20000
用dbms_output.put_line(’strin_content’);可以在存储过程中输出信息,对存储过程进行调试
如果想让dbms_output.put_line(‘     abc’);的输出显示为:
SQL>     abc,而不是SQL>abc,则在SET SERVEROUTPUT ON后加format wrapped参数。

16). 输出的数据为html格式
set markup html
在8.1.7版本(也许是816? 不太确定)以后, sql*plus中有一个set markup html的命令, 可以将sql*plus的输出以html格式展现.
注意其中的spool on, 当在屏幕上输出的时候, 我们看不出与不加spool on有什么区别, 但是当我们使用spool filename 输出到文件的时候, 会看到spool文件中出现了等tag.

14.修改sql buffer中的当前行中,第一个出现的字符串
C[HANGE] /old_value/new_value
SQL> l
   1* select * from dept
SQL> c/dept/emp
   1* select * from emp
  
15.编辑sql buffer中的sql语句
EDI[T]
  
16.显示sql buffer中的sql语句,list n显示sql buffer中的第n行,并使第n行成为当前行
L[IST] [n]
  
17.在sql buffer的当前行下面加一行或多行
I[NPUT]
  
18.将指定的文本加到sql buffer的当前行后面
A[PPEND]
SQL> select deptno,
   2  dname
   3  from dept;
     DEPTNO DNAME
———- ————–
         10 ACCOUNTING
         20 RESEARCH
         30 SALES
         40 OPERATIONS
  
SQL> L 2
   2* dname
SQL> a ,loc
   2* dname,loc
SQL> L
   1  select deptno,
   2  dname,loc
   3* from dept
SQL> /
  
     DEPTNO DNAME          LOC
———- ————– ————-
         10 ACCOUNTING     NEW YORK
         20 RESEARCH       DALLAS
         30 SALES          CHICAGO
         40 OPERATIONS     BOSTON
  
19.将sql buffer中的sql语句保存到一个文件中
SAVE file_name
  
20.将一个文件中的sql语句导入到sql buffer中
GET file_name
  
21.再次执行刚才已经执行的sql语句
RUN
or
/
  
22.执行一个存储过程
EXECUTE procedure_name
  
23.在sql*plus中连接到指定的数据库
CONNECT user_name/passwd@db_alias
  
24.设置每个报表的顶部标题
TTITLE
  
25.设置每个报表的尾部标题
BTITLE
  
26.写一个注释
REMARK [text]
  
27.将指定的信息或一个空行输出到屏幕上
PROMPT [text]
  
28.将执行的过程暂停,等待用户响应后继续执行
PAUSE [text]
  
Sql>PAUSE Adjust paper and press RETURN to continue.
  
29.将一个数据库中的一些数据拷贝到另外一个数据库(如将一个表的数据拷贝到另一个数据库)
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query
  
sql>COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST  
create emp_temp
USING SELECT * FROM EMP
  
30.不退出sql*plus,在sql*plus中执行一个操作系统命令:
HOST
  
Sql> host hostname
该命令在windows下可能被支持。
  
31.在sql*plus中,切换到操作系统命令提示符下,运行操作系统命令后,可以再次切换回sql*plus:
!
  
sql>!
$hostname
$exit
sql>
  
该命令在windows下不被支持。
  
32.显示sql*plus命令的帮助
HELP
如何安装帮助文件:
Sql>@ ?\sqlplus\admin\help\hlpbld.sql ?\sqlplus\admin\help\helpus.sql
Sql>help index
  
33.显示sql*plus系统变量的值或sql*plus环境变量的值
Syntax
SHO[W] option
where option represents one of the following terms or clauses:
system_variable
ALL
BTI[TLE]
ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|
TRIGGER|VIEW|TYPE|TYPE BODY} [schema.]name]
LNO
PARAMETERS [parameter_name]
PNO
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
SQLCODE
TTI[TLE]
USER
  
1) . 显示当前环境变量的值:
Show all
  
2) . 显示当前在创建函数、存储过程、触发器、包等对象的错误信息
Show error
当创建一个函数、存储过程等出错时,变可以用该命令查看在那个地方出错及相应的出错信息,进行修改后再次进行编译。
  
3) . 显示初始化参数的值:
show PARAMETERS [parameter_name]
  
4) . 显示数据库的版本:
show REL[EASE]
  
5) . 显示SGA的大小
show SGA
  
6). 显示当前的用户名
show user

34.查询一个用户下的对象
SQL>select * from tab;
SQL>select * from user_objects;

35.查询一个用户下的所有的表
SQL>select * from user_tables;

36.查询一个用户下的所有的索引
SQL>select * from user_indexes;

37. 定义一个用户变量
方法有两个:
a. define
b. COL[UMN] [{column|expr} NEW_V[ALUE] variable [NOPRI[NT]|PRI[NT]]
                            OLD_V[ALUE] variable  [NOPRI[NT]|PRI[NT]]

下面对每种方式给予解释:
a. Syntax
DEF[INE] [variable]|[variable = text]
定义一个用户变量并且可以分配给它一个CHAR值。

assign the value MANAGER to the variable POS, type:
SQL> DEFINE POS = MANAGER

assign the CHAR value 20 to the variable DEPTNO, type:
SQL> DEFINE DEPTNO = 20

list the definition of DEPTNO, enter
SQL> DEFINE DEPTNO
        ―――――――――――――――
DEFINE DEPTNO = ”20” (CHAR)

定义了用户变量POS后,就可以在sql*plus中用&POS或&&POS来引用该变量的值,sql*plus不会再提示你给变量输入值。

b. COL[UMN] [{column|expr} NEW_V[ALUE] variable [NOPRI[NT]|PRI[NT]]
NEW_V[ALUE] variable
指定一个变量容纳查询出的列值。
例:column col_name new_value var_name noprint
   select col_name from table_name where ……..
将下面查询出的col_name列的值赋给var_name变量.

一个综合的例子:
得到一个列值的两次查询之差(此例为10秒之内共提交了多少事务):
column redo_writes new_value commit_count

select sum(stat.value) redo_writes
from v$sesstat stat, v$statname sn
where stat.statistic# = sn.statistic#
and sn.name = ‘user commits’;

– 等待一会儿(此处为10秒);
execute dbms_lock.sleep(10);

set veri off
select sum(stat.value) – &commit_count commits_added
from v$sesstat stat, v$statname sn
where stat.statistic# = sn.statistic#
and sn.name = ‘user commits’;

38. 定义一个绑定变量
VAR[IABLE] [variable [NUMBER|CHAR|CHAR (n)|NCHAR|NCHAR (n) |VARCHAR2 (n)|NVARCHAR2 (n)|CLOB|NCLOB|REFCURSOR]]
定义一个绑定变量,该变量可以在pl/sql中引用。
可以用print命令显示该绑定变量的信息。
如:
column inst_num  heading "Inst Num"  new_value inst_num  format 99999;
column inst_name heading "Instance"  new_value inst_name format a12;
column db_name   heading "DB Name"   new_value db_name   format a12;
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c;

prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~

select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,
       v$instance i;

variable dbid       number;
variable inst_num   number;
begin
  :dbid      :=  &dbid;
  :inst_num  :=  &inst_num;
end;
/
说明:
在sql*plus中,该绑定变量可以作为一个存储过程的参数,也可以在匿名PL/SQL块中直接引用。为了显示用VARIABLE命令创建的绑定变量的值,可以用print命令

注意:
绑定变量不同于变量:
1.        定义方法不同
2.        引用方法不同
绑定变量::variable_name
        变量:&variable_name or &&variable_name
3.在sql*plus中,可以定义同名的绑定变量与用户变量,但是引用的方法不同。

39. &与&&的区别
&用来创建一个临时变量,每当遇到这个临时变量时,都会提示你输入一个值。
&&用来创建一个持久变量,就像用用define命令或带new_vlaue字句的column命令创建的持久变量一样。当用&&命令引用这个变量时,不会每次遇到该变量就提示用户键入值,而只是在第一次遇到时提示一次。

如,将下面三行语句存为一个脚本文件,运行该脚本文件,会提示三次,让输入deptnoval的值:
select count(*) from emp where deptno = &deptnoval;
select count(*) from emp where deptno = &deptnoval;
select count(*) from emp where deptno = &deptnoval;

将下面三行语句存为一个脚本文件,运行该脚本文件,则只会提示一次,让输入deptnoval的值:
select count(*) from emp where deptno = &&deptnoval;
select count(*) from emp where deptno = &&deptnoval;
select count(*) from emp where deptno = &&deptnoval;

40.在输入sql语句的过程中临时先运行一个sql*plus命令(摘自www.itpub.com)
#
有没有过这样的经历? 在sql*plus中敲了很长的命令后, 突然发现想不起某个列的名字了, 如果取消当前的命令,待查询后再重敲, 那太痛苦了. 当然你可以另开一个sql*plus窗口进行查询, 但这里提供的方法更简单.

比如说, 你想查工资大于4000的员工的信息, 输入了下面的语句:

SQL> select deptno, empno, ename
2 from emp
3 where
这时, 你发现你想不起来工资的列名是什么了.

这种情况下, 只要在下一行以#开头, 就可以执行一条sql*plus命令, 执行完后, 刚才的语句可以继续输入

SQL>> select deptno, empno, ename
2 from emp
3 where
6 #desc emp
Name Null? Type
—————————————– ——– ————–
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

6 sal > 4000;

DEPTNO EMPNO ENAME
———- ———- ———-
10 7839 KING

41. SQLPlus中的快速复制和粘贴技巧(摘自www.cnoug.org)
1) 鼠标移至想要复制内容的开始
2) 用右手食指按下鼠标左键
3) 向想要复制内容的另一角拖动鼠标,与Word中选取内容的方法一样
4) 内容选取完毕后(所选内容全部反显),鼠标左键按住不动,用右手中指按鼠标右键
5) 这时,所选内容会自动复制到SQL*Plus环境的最后一行

2007年01月09日

以前跟贴说了一下ORACLE用计划任务自动备份的方法,想不到不少朋友来信索取,现将方法公布,有不足之处请各位批评指正:
在计划任务中加一批处理文件,内容如下:
set rq=%date:~-10%    &&将日期格式设为YYYY-MM-DD格式 ,并将当前日期放到变量RQ中
exp user/password@sid file=d:\BAK%rq%.dmp full=yes log=d:\lg%rq%.log  &&每次备份的文件名为D:\BAK2004-04-01.DMP 日志文件名为D:\LG2004-04-01.LOG
你必须保证你的磁盘有足够的空间来存放每次的备份文件!!

SID – 数据库标识
    HOME_NAME – Oracle Home名称,如OraHome92、OraHome81
(1)OracleServiceSID                       
数据库服务,这个服务会自动地启动和停止数据库。如果安装了一个数据库,它的缺省启动类型为自动。服务进程为ORACLE.EXE,参数文件initSID.ora,日志文件SIDALRT.log,控制台SVRMGRL.EXE、SQLPLUS.EXE。

(2)OracleHOME_NAMETNSListener
监听器服务,服务只有在数据库需要远程访问时才需要(无论是通过另外一台主机还是在本地通过 SQL*Net 网络协议都属于远程访问),不用这个服务就可以访问本地数据库,它的缺省启动类型为自动。服务进程为TNSLSNR.EXE,参数文件Listener.ora,日志文件listener.log,控制台LSNRCTL.EXE,默认端口1521、1526。

(3)OracleHOME_NAMEAgent
OEM代理服务,接收和响应来自OEM控制台的任务和事件请求,只有使用OEM管理数据库时才需要,它的缺省启动类型为自动。服务进程为DBSNMP.EXE,参数文件snmp_rw.ora,日志文件nmi.log,控制台LSNRCTL.EXE,默认端口1748。

(4)OracleHOME_NAMEClientCache       
名字缓存服务,服务缓存用于连接远程数据库的Oracle Names 数据。它的缺省启动类型是手动。然而,除非有一台Oracle Names 服务器,否则没有必要运行这个服务。服务进程为ONRSD.EXE,参数文件NAMES.ORA,日志文件ONRSD.LOG,控制台NAMESCTL.EXE。

(5)OracleHOME_NAMECMAdmin                       
连接管理服务,是构建Connection Manager服务器所用,只有服务器作为Connection Manager才需要,它的缺省启动类型是手动。服务进程为CMADMIN.EXE,参数文件CMAN.ORA,日志文件CMADM_PID.TRC,控制台CMCTL.EXE,默认端口1830。

(6)OracleHOME_NAMECMan                       
连接网关服务,是构建Connection Manager服务器所用,只有服务器作为Connection Manager才需要,它的缺省启动类型是手动。服务进程为CMGW.EXE,参数文件CMAN.ORA,日志文件CMAN_PID.TRC,控制台CMCTL.EXE,默认端口1630。

(7)OracleHOME_NAMEDataGatherer
性能包数据采集服务,除非使用Oracle Capacity Planner 和 Oracle Performance Manager,否则不需要启动,它的缺省启动类型是手动。服务进程为VPPDC.EXE,日志文件alert_dg.log,控制台vppcntl.exe。

(8)OracleHOME_NAMEHTTPServer
Oracle提供的WEB服务器,一般情况下我们只用它来访问Oracle Apache 目录下的Web 页面,比如说JSP 或者modplsql 页面。除非你使用它作为你的HTTP服务,否则不需要启动(若启动它会接管IIS的服务),它的缺省启动类型是手动。服务进程为APACHE.EXE,参数文件httpd.conf,默认端口80。

(9)OracleHOME_NAMEPagingServer
通过一个使用调制解调器的数字传呼机或者电子邮件发出警告(没试过),它的缺省启动类型是手动。服务进程PAGNTSRV.EXE,日志文件paging.log。

(10)OracleHOME_NAMENames
Oracle Names服务,只有服务器作为Names Server才需要,它的缺省启动类型是手动。服务进程NAMES.EXE,参数文件NAMES.ORA,日志文件NAMES.LOG,控制台NAMESCTL.EXE,默认端口1575。

(11)OracleSNMPPeerMasterAgent
SNMP服务代理,用于支持SNMP的网管软件对服务器的管理,除非你使用网管工具监控数据库的情况,否则不需要启动,它的缺省启动类型是手动。服务进程为AGNTSVC.EXE,参数文件MASTER.CFG,默认端口161。

(12)OracleSNMPPeerEncapsulater
SNMP协议封装服务,用于SNMP协议转换,除非你使用一个不兼容的SNMP代理服务,否则不需要启动,它的缺省启动类型是手动。服务进程为ENCSVC.EXE,参数文件ENCAPS.CFG,默认端口1161。

(13)OracleHOME_NAMEManagementServer
OEM管理服务,使用OEM时需要,它的缺省启动类型是手动。服务进程为OMSNTSVR.EXE,日志文件oms.nohup。

关于这个饰品的用处和强处可以另开帖子讨论,这里只说如何更快的入手。

一、首先着个饰品是ZG的疯狂之缘四大隐藏BOSS出的
 四个BOSS掉落的装备虽然一般,但是收集完整BOSS分别掉落的格里雷克之血、雷纳塔基之牙、乌苏雷之鬃和哈扎拉尔的梦境之丝,可以和相应职业的打孔的巫毒人偶组合成强大的职业饰品 。

[格里雷克之血]
[雷纳塔基之牙]
[乌苏雷之鬃]
[梦境之丝]

[打孔的巫毒人偶](猎人可以自己单刷出来)

以上护符全部入手最久只需要2个月。

二、只有炼金师才能在疯狂之缘(去老虎的半路小鬼的地方)学会如何制作召唤BOSS的材料。

要1极效魔精、6强力魔精、1个英雄之血和1个黑莲花才能做出3个召唤BOSS的古拉巴什魔精
[古拉巴什疯狂魔精]

古拉巴什疯狂魔精可以交易,所以可以买到。

三、鉴于现在G团盛行,基本上没有人去杀隐藏BOSS,如果要以最短的时间入手,就必须自己开G团,自己做团长,自己出材料

自己出材料的好处是不用和别人竟拍。

从老一开始自己开团,先组自己技术装备好的朋友或者工会里的,然后再其他。
开团必须注意要有强势的态度,晚上7:30或者下午1点开组,只组4紫以上的玩家,职业配置3战士 3/4牧师 3/4法师 2/3骑士%撒满 1/2猎人 2/3盗贼 1小德 1术士
组团的时候如果战士强,就喊;有强力战士来其他职业(其他类同), 反正就是能骗就骗把强力的人骗进来,而且组的时候要求贴装备,我们的目标是隐藏BOSS,不用理会有没有人买装备的。
组好后杀掉一个BOSS大家都有进度之后,宣布有材料可以召唤隐藏BOSS,不杀不分G(好黑 -_-!)

有时候会有只杀金度,哈卡的团(其他祭司被杀掉那种)
一般能杀金度的这种团实力都很强,加进去之后问杀不杀隐藏BOSS,强势要求先杀隐藏BOSS。
然后走水路去疯狂之缘,能节省不少时间。

四、关于打法:

1、里雷克,钢铁之血掉 [格里雷克之血]

ZG巡逻的狂暴者变大版本。
肉搏型BOSS,没有一点魔法技能。
BOSS会随机缠绕一个人,可以被QS,牧师解掉。
BOSS每30秒会天神下凡一次,变的无敌会秒人。BOSS在变身状态下跑的很慢,可以用风筝战术。
猎人再组个骑士可以据说可以单刷他,就是猎人单刷鳄鱼、老虎的方法,缠绕了骑士解掉。但是怎么绕过小鬼去放召唤材料是个难题。

2、哈扎拉尔,握叩鬧item]梦境之丝[/item]

法系BOSS。
难点是
此BOSS令所有人昏睡数秒。
然后还会召唤小怪,每次召唤3个,每个小怪500血,只会近战攻击,攻击大概打皮甲每下3500-4000。
如果被睡了,小怪出来很容易秒人。所以最好准备多点厄运的小树苗,工程的假人,仆人玲铛,一些战斗宠物== 然后睡的时候 叫FS准备冰箱,骑士无敌,一被睡就马上解掉,然后秒掉小怪。

3、雷纳塔基,千刃之王掉[雷纳塔基之牙]

盗贼型BOSS,技能说的通俗一点,就是会消失、伏击、凿击。可同时凿击多个目标。还有一个技能叫千刃,是放飞刀远程攻击的,伤害不大,800-1000,没什么威胁。
唯一有威胁的技能就是消失+伏击,会秒布衣。BOSS的消失无解,什么标记、精灵火、DOT都无法阻止BOSS消失。唯一办法是看准BOSS消失时面朝的方向,那个方向上的人自己小心了。应对BOSS凿击MT之后打其他人的办法很简单,搞双MT,一个MT吃凿晕了以后2MT上。

4、雷巫乌苏雷掉[乌苏雷之鬃]

这个BOSS的技能主要就是闪电,一直放小范围的群体闪电。一下打大概800-1000血。
注意散开站位,猎人开自然抗,如果想保险,发自然抗药给其他人。

五、关于BOSS的转换日期

其实只要看墙上的石板就知道是哪个BOSS了,看到XXX就在疯狂之缘徘徊的字样 就会召唤出那BOSS出来。

如果想有所准备请看下面的表

2006/11/20 ~ 2006/12/3 乌苏雷
2006/12/4 ~ 2006/12/17 格里雷克
2006/12/18 ~ 2006/12/31 哈扎拉尔
2007/1/1 ~ 2007/1/14 雷纳塔基
2007/1/15 ~ 2007/1/28 乌苏雷
2007/1/29 ~ 2007/2/11 格里雷克
2007/2/12 ~ 2007/2/25 哈扎拉尔
2007/2/26 ~ 2007/3/11 雷纳塔基
2007/3/12 ~ 2007/3/25 乌苏雷
2007/3/26 ~ 2007/4/8 格里雷克
2007/4/9 ~ 2007/4/22 哈扎拉尔
2007/4/23 ~ 2007/5/6 雷纳塔基
2007/5/7 ~ 2007/5/20 乌苏雷
2007/5/21 ~ 2007/6/3 格里雷克
2007/6/4 ~ 2007/6/17 哈扎拉尔
2007/6/18 ~ 2007/7/1 雷纳塔基
2007/7/2 ~ 2007/7/15 乌苏雷
2007/7/16 ~ 2007/7/29 格里雷克
2007/7/30 ~ 2007/8/12 哈扎拉尔
2007/8/13 ~ 2007/8/26 雷纳塔基
2007/8/27 ~ 2007/9/9 乌苏雷
2007/9/10 ~ 2007/9/23 格里雷克
2007/9/24 ~ 2007/10/7 哈扎拉尔
2007/10/8 ~ 2007/10/21 雷纳塔基
2007/10/22 ~ 2007/11/4 乌苏雷
2007/11/5 ~ 2007/11/18 格里雷克
2007/11/19 ~ 2007/12/2 哈扎拉尔
2007/12/3 ~ 2007/12/16 雷纳塔基
2007/12/17 ~ 2007/12/30 乌苏雷
2007/12/31 ~ 2008/1/13 格里雷克
2008/1/14 ~ 2008/1/27 哈扎拉尔
2008/1/28 ~ 2008/2/10 雷纳塔基
2008/2/11 ~ 2008/2/24 乌苏雷
2008/2/25 ~ 2008/3/9 格里雷克
2008/3/10 ~ 2008/3/23 哈扎拉尔
2008/3/24 ~ 2008/4/6 雷纳塔基
2008/4/7 ~ 2008/4/20 乌苏雷
2008/4/21 ~ 2008/5/4 格里雷克
2008/5/5 ~ 2008/5/18 哈扎拉尔
2008/5/19 ~ 2008/6/1 雷纳塔基
2008/6/2 ~ 2008/6/15 乌苏雷
2008/6/16 ~ 2008/6/29 格里雷克
2008/6/30 ~ 2008/7/13 哈扎拉尔
2008/7/14 ~ 2008/7/27 雷纳塔基
2008/7/28 ~ 2008/8/10 乌苏雷
2008/8/11 ~ 2008/8/24 格里雷克
2008/8/25 ~ 2008/9/7 哈扎拉尔
2008/9/8 ~ 2008/9/21 雷纳塔基
2008/9/22 ~ 2008/10/5 乌苏雷
2008/10/6 ~ 2008/10/19 格里雷克
2008/10/20 ~ 2008/11/2 哈扎拉尔
2008/11/3 ~ 2008/11/16 雷纳塔基
2008/11/17 ~ 2008/11/30 乌苏雷

=================================


杀完国王后,先和3号卫兵对话,在开箱子。

正常刷一回,然后组个人小退,结果传到副本门口,副本已重只,再叫那个人组你,在副本里杀个小动物(蛇蟑螂之类),出副本,再进,小怪基本不打你,直接跑到国王那,国王不在,下线,国王刷新,直接杀!!!!!!!!

使用DECODE函数来减少处理时间
  
  
  
  使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
  
  
  
  例如:
  
   SELECT COUNT(*),SUM(SAL)
  
   FROM EMP
  
   WHERE DEPT_NO = 0020
  
   AND ENAME LIKE ‘SMITH%’;
  
  
  
   SELECT COUNT(*),SUM(SAL)
  
   FROM EMP
  
   WHERE DEPT_NO = 0030
  
   AND ENAME LIKE ‘SMITH%’;
  
  
  
  你可以用DECODE函数高效地得到相同结果
  
  
  
  SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
  
   COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
  
   SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
  
   SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
  
  FROM EMP WHERE ENAME LIKE ‘SMITH%’;
  
  
  
  类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.

减少对表的查询
  
  在含有子查询的SQL语句中,要特别注意减少对表的查询.
  
  
  
  例如:
  
   低效
  
   SELECT TAB_NAME
  
   FROM TABLES
  
   WHERE TAB_NAME = ( SELECT TAB_NAME
  
   FROM TAB_COLUMNS
  
   WHERE VERSION = 604)
  
   AND DB_VER= ( SELECT DB_VER
  
   FROM TAB_COLUMNS
  
   WHERE VERSION = 604)
  
  
  
   高效
  
   SELECT TAB_NAME
  
   FROM TABLES
  
   WHERE (TAB_NAME,DB_VER)
  
   = ( SELECT TAB_NAME,DB_VER)
  
   FROM TAB_COLUMNS
  
   WHERE VERSION = 604)

Update 多个Column 例子:
  
   低效:
  
   UPDATE EMP
  
   SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
  
   SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
  
   WHERE EMP_DEPT = 0020;
  
  
  
   高效:
  
   UPDATE EMP
  
   SET (EMP_CAT, SAL_RANGE)
  
   = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
  
   FROM EMP_CATEGORIES)
  
   WHERE EMP_DEPT = 0020;
  
  
  
  
  
  16. 通过内部函数提高SQL效率.
  
  
  
   SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
  
   FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H
  
   WHERE H.EMPNO = E.EMPNO
  
  AND H.HIST_TYPE = T.HIST_TYPE
  
  GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
  
  
  
  通过调用下面的函数可以提高效率.
  
  FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2
  
  AS
  
   TDESC VARCHAR2(30);
  
   CURSOR C1 IS
  
   SELECT TYPE_DESC
  
   FROM HISTORY_TYPE
  
   WHERE HIST_TYPE = TYP;
  
  BEGIN
  
   OPEN C1;
  
   FETCH C1 INTO TDESC;
  
   CLOSE C1;
  
   RETURN (NVL(TDESC,’?’));
  
  END;
  
  
  
  FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2
  
  AS
  
   ENAME VARCHAR2(30);
  
   CURSOR C1 IS
  
   SELECT ENAME
  
   FROM EMP
  
   WHERE EMPNO=EMP;
  
  BEGIN
  
   OPEN C1;
  
   FETCH C1 INTO ENAME;
  
   CLOSE C1;
  
   RETURN (NVL(ENAME,’?’));
  
  END;
  
  
  
  SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),
  
  H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
  
  FROM EMP_HISTORY H
  
  GROUP BY H.EMPNO , H.HIST_TYPE;
  
  
  
  (译者按: 经常在论坛中看到如 ’能不能用一个SQL写出….’ 的贴子, 殊不知复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的)

使用表的别名(Alias)
  
  当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
  
  
  
  (译者注: Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)
  
  
  
  18. 用EXISTS替代IN
  
  在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.
  
  
  
  低效:
  
  SELECT *
  
  FROM EMP (基础表)
  
  WHERE EMPNO > 0
  
  AND DEPTNO IN (SELECT DEPTNO
  
  FROM DEPT
  
  WHERE LOC = ‘MELB’)
  
  
  
  高效:
  
  SELECT *
  
  FROM EMP (基础表)
  
  WHERE EMPNO > 0
  
  AND EXISTS (SELECT ‘X’
  
  FROM DEPT
  
  WHERE DEPT.DEPTNO = EMP.DEPTNO
  
  AND LOC = ‘MELB’)
  
  
  
  
  
   (译者按: 相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下一节中将指出)
  
  
  
  
  
  19. 用NOT EXISTS替代NOT IN
  
  在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
  
  
  
  例如:
  
  SELECT …
  
  FROM EMP
  
  WHERE DEPT_NO NOT IN (SELECT DEPT_NO
  
   FROM DEPT
  
   WHERE DEPT_CAT=’A’);
  
  
  
  为了提高效率.改写为:
  
  
  
  (方法一: 高效)
  
  SELECT ….
  
  FROM EMP A,DEPT B
  
  WHERE A.DEPT_NO = B.DEPT(+)
  
  AND B.DEPT_NO IS NULL
  
  AND B.DEPT_CAT(+) = ‘A’
  
  
  
  
  
  (方法二: 最高效)
  
  SELECT ….
  
  FROM EMP E
  
  WHERE NOT EXISTS (SELECT ‘X’
  
   FROM DEPT D
  
   WHERE D.DEPT_NO = E.DEPT_NO
  
   AND DEPT_CAT = ‘A’);

用表连接替换EXISTS
  
  
  
   通常来说 , 采用表连接的方式比EXISTS更有效率
  
   SELECT ENAME
  
   FROM EMP E
  
   WHERE EXISTS (SELECT ‘X’
  
   FROM DEPT
  
   WHERE DEPT_NO = E.DEPT_NO
  
   AND DEPT_CAT = ‘A’);
  用EXISTS替换DISTINCT
  
  当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换
  
  
  
  例如:
  
  低效:
  
   SELECT DISTINCT DEPT_NO,DEPT_NAME
  
   FROM DEPT D,EMP E
  
   WHERE D.DEPT_NO = E.DEPT_NO
  
  高效:
  
   SELECT DEPT_NO,DEPT_NAME
  
   FROM DEPT D
  
   WHERE EXISTS ( SELECT ‘X’
  
   FROM EMP E
  
   WHERE E.DEPT_NO = D.DEPT_NO);
  
  
  
   EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.
  
  
  
  22. 识别’低效执行’的SQL语句
  
  
  
  用下列SQL工具找出低效SQL:
  
  
  
  SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
  
   ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
  
   ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
  
   SQL_TEXT
  
  FROM V$SQLAREA
  
  WHERE EXECUTIONS>0
  
  AND BUFFER_GETS > 0
  
  AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
  
  ORDER BY 4 DESC;
  
  
  
  (译者按: 虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法)
  
  
  
  23. 使用TKPROF 工具来查询SQL性能状态
  
  
  
  SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中. 这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你的系统.
  
  
  
  设置SQL TRACE在会话级别: 有效
  
  
  
   ALTER SESSION SET SQL_TRACE TRUE
  
  
  
  设置SQL TRACE 在整个数据库有效仿, 你必须将SQL_TRACE参数在init.ora中设为TRUE, USER_DUMP_DEST参数说明了生成跟踪文件的目录
  
  
  
  (译者按: 这一节中,作者并没有提到TKPROF的用法, 对SQL TRACE的用法也不够准确, 设置SQL TRACE首先要在init.ora中设定TIMED_STATISTICS, 这样才能得到那些重要的时间状态. 生成的trace文件是不可读的,所以要用TKPROF工具对其进行转换,TKPROF有许多执行参数. 大家可以参考ORACLE手册来了解具体的配置. )

  
  
   (更高效)
  
   SELECT ENAME
  
   FROM DEPT D,EMP E
  
   WHERE E.DEPT_NO = D.DEPT_NO
  
   AND DEPT_CAT = ‘A’ ;
  
  
  
  (在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTED LOOP)

ORACLE采用自下而上的WHERE语句,表之间的连接应该写在其他WHERE条件之前,能过滤掉大多数据的条件因该写在WHERE条件之后。

  例如:
  
  
  
  (低效,执行时间156.3秒)
  
  SELECT …
  
  FROM EMP E
  
  WHERE SAL > 50000
  
  AND JOB = ‘MANAGER’
  
  AND 25 < (SELECT COUNT(*) FROM EMP
  
   WHERE MGR=E.EMPNO);
  
  
  
  (高效,执行时间10.6秒)
  
  SELECT …
  
  FROM EMP E
  
  WHERE 25 < (SELECT COUNT(*) FROM EMP
  
   WHERE MGR=E.EMPNO)
  
  AND SAL > 50000
  
  AND JOB = ‘MANAGER’;
  6. SELECT子句中避免使用 ‘ * ‘
  
  当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.
  
  
  
  
  
  7. 减少访问数据库的次数
  
  当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.
  
  
  
  例如,
  
   以下有三种方法可以检索出雇员号等于0342或0291的职员.
  
  
  
  方法1 (最低效)
  
   SELECT EMP_NAME , SALARY , GRADE
  
   FROM EMP
  
   WHERE EMP_NO = 342;
  
  
  
   SELECT EMP_NAME , SALARY , GRADE
  
   FROM EMP
  
   WHERE EMP_NO = 291;
  
  
  
  方法2 (次低效)
  
  
  
   DECLARE
  
   CURSOR C1 (E_NO NUMBER) IS
  
   SELECT EMP_NAME,SALARY,GRADE
  
   FROM EMP
  
   WHERE EMP_NO = E_NO;
  
   BEGIN
  
   OPEN C1(342);
  
   FETCH C1 INTO …,..,.. ;
  
   …..
  
   OPEN C1(291);
  
   FETCH C1 INTO …,..,.. ;
  
   CLOSE C1;
  
   END;
  
  
  
  
  
  
  
  
  
  
  
  方法3 (高效)
  
  
  
   SELECT A.EMP_NAME , A.SALARY , A.GRADE,
  
   B.EMP_NAME , B.SALARY , B.GRADE
  
   FROM EMP A,EMP B
  
   WHERE A.EMP_NO = 342
  
   AND B.EMP_NO = 291;
  
  
  
  
  
  注意:
  
   在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200

ORACLE是从右到左解析FROM子句后面的表名,所以写在FROM最后的表是基础表,将被最先处理。

在FROM子句后面包含多个表的时候,必须选择纪录条数最少的表作为基础表,当ORACLE处理多个表的时候,

会利用排序和合并的方式连接他们。首先,扫描第一个表(FROM子句最后的表)并对记录进行排序,然后扫描第

二个表,然后将第二个表中检索出来的记录与第一个表合适的纪录进行合并。

例如:
  
   表 TAB1 16,384 条记录
  
   表 TAB2 1 条记录

选择TAB2作为基础表 (最好的方法)
  
   select count(*) from tab1,tab2 执行时间0.96秒
  
  
  
   选择TAB2作为基础表 (不佳的方法)
  
   select count(*) from tab2,tab1 执行时间26.09秒

如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
  
  
  
  例如:
  
  
  
   EMP表描述了LOCATION表和CATEGORY表的交集.
  
  
  
   SELECT *
  
  FROM LOCATION L ,
  
   CATEGORY C,
  
   EMP E
  
  WHERE E.EMP_NO BETWEEN 1000 AND 2000
  
  AND E.CAT_NO = C.CAT_NO
  
  AND E.LOCN = L.LOCN
  
  
  
  将比下列SQL更有效率
  
  
  
  SELECT *
  
  FROM EMP E ,
  
  LOCATION L ,
  
   CATEGORY C
  
  WHERE E.CAT_NO = C.CAT_NO
  
  AND E.LOCN = L.LOCN
  
  AND E.EMP_NO BETWEEN 1000 AND 2000