oracle基础+提高

1. windows环境下的oracle服务

1.1 OracleServiceSID

数据库服务，这个服务会自动地启动和停止数据库。如果安装了一个数据库，它的缺省启动类型为自动。服务进程为ORACLE.EXE，参数文件initSID.ora， ({Orcle_home} /database/iniSID.ora) （2）OracleHOME_NAMETNSListener

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

1.2 （3）OracleHOME_NAMEAgent

OEM代理服务，接收和响应来自OEM控制台的任务和事件请求，只有使用OEM管理数据库时才需要，它的缺省启动类型为自动。

Oralce数据库如果采用典型安装后，自动创建了一个叫做DBSNMP的用户，该用户负责运行Oracle系统的智能代理（Intelligent Agent），该用户的缺省密码也是“DBSNMP”。

1.3 （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。

1.4 （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。

2 创建数据库

1、 Dbca图形化界面建立数据库 （例如：test）

生成语句保存在“E:\\oracle\\product\\10.2.0\\admin\\test\\scripts”

批处理文件（test.bat）：

mkdir E:\\oracle\\product\\10.2.0\\admin\\test\\adump mkdir E:\\oracle\\product\\10.2.0\\admin\\test\\bdump mkdir E:\\oracle\\product\\10.2.0\\admin\\test\\cdump mkdir E:\\oracle\\product\\10.2.0\\admin\\test\\dpdump mkdir E:\\oracle\\product\\10.2.0\\admin\\test\\pfile mkdir E:\\oracle\\product\\10.2.0\\admin\\test\%udump

mkdir E:\\oracle\\product\\10.2.0\\db_1\\cfgtoollogs\\dbca\\test mkdir E:\\oracle\\product\\10.2.0\\db_1\\dbs

mkdir E:\\oracle\\product\\10.2.0\\flash_recovery_area mkdir E:\\oracle\\product\\10.2.0\\oradata\\test set ORACLE_SID=test

E:\\oracle\\product\\10.2.0\\db_1\\bin\\oradim.exe -new -sid TEST -startmode manual -spfile

E:\\oracle\\product\\10.2.0\\db_1\\bin\\oradim.exe -edit -sid TEST -startmode auto -srvcstart system

E:\\oracle\\product\\10.2.0\\db_1\\bin\\sqlplus

@E:\\oracle\\product\\10.2.0\\admin\\test\\scripts\\test.sql

2、 Dbca命令行建立数据库

/nolog

Dbca –help 查看命令帮助

3、 语句建立数据库

（1）建立实例

oradim –new –sid test -intpwd test

（2）创建pfile

拷贝一个模板后修改相应的值，db_name。。。 （3）建立数据库语句（createdatabase.sql）

set oracle_id=test set sqlplus /nolog conn / as sysdba

startup pfile=’d:\\’ nomount @ createdatabase.sql

（4）执行系统包

{Oracle_home}\\rdbms\\admin\\catalog.sql 创建数据字典表和视图 {Oracle_home}\\rdbms\\admin\\catproc.sql 安装oracle系统包

{Oracle_home}\\sqlplus\\admin\\pupbld.sql 安装product_user_profile表

3 数据库停顿、挂起

Alter system quiesce restricted --停顿 Alter system unquiesce; ---恢复

功能：非dba用户不能操作数据库任何操作

应用场景：增加表字段时，如果用户使用该表造成更新失败

Select a.sid,user,osuer,type,program from V$blocking_quiesce a,V$session b where a.sid=b.sid;

Alter system suspend ; --挂起 Alter system resume ; --挂起

数据文件和控制文件的I/0操作被终止。确保数据在没有i/o情况下备份。

4 em服务（DBSNMP 密码修改、启动异常、重新配置DBconsole）

1.Stop dbconsole

2.set new password for dbsnmp 3.test dbsnmp new password by connect 4.edit targets.xml Search for the line:

f XML files pending upload : 5 Size of XML files pending upload(MB) : 2.31 Available disk space on upload filesystem : 59.92% Agent is already started. Will restart the agent

This will stop the Oracle Enterprise Manager 10g Database Control process. Conti nue [y/n] :y

OracleDBConsoleora10g 服务正在停止............. OracleDBConsoleora10g 服务已成功停止。

C:\\Documents and Settings\\new>emctl start dbconsole

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved. http://MICROSOF-029:5501/em/console/aboutApplication

Starting Oracle Enterprise Manager 10g Database Control ...OracleDBConsoleora10g 服务正在启动 ..............

OracleDBConsoleora10g 服务已经启动成功。

4.7 附上EMCA的常用命令：

创建一个EM资料库 emca -repos create 重建一个EM资料库 emca -repos recreate 删除一个EM资料库 emca -repos drop

配置数据库的 Database Control emca -config dbcontrol db

删除数据库的 Database Control配置

emca -deconfig dbcontrol db

重新配置db control的端口，默认端口在1158 emca -reconfig ports

emca -reconfig ports -dbcontrol_http_port 1160 emca -reconfig ports -agent_port 3940

先设置ORACLE_SID环境变量后,启动EM console服务 emctl start dbconsole

先设置ORACLE_SID环境变量后,停止EM console服务 emctl stop dbconsole

先设置ORACLE_SID环境变量后,查看EM console服务的状态 emctl status dbconsole

配置dbconsole的步骤 emca -repos create emca -config dbcontrol db emctl start dbconsole 重新配置dbconsole的步骤 emca -repos drop emca -repos create emca -config dbcontrol db emctl start dbconsole

4.8 Oracle DBSNMP进程

Oralce数据库如果采用典型安装后，自动创建了一个叫做DBSNMP的用户，该用户负责运行Oracle系统的智能代理（Intelligent Agent），该用户的缺省密码也是“DBSNMP”。

Oracle Enterprise Manager该资料档案库是一组表，是在设置 OMS 时创建的。OMS 使用

资料档案库作为其永久的后端存储。如果必要，可使用多个 OMS。多个 OMS 共享一个资料档案库并提供可靠性和容错功能 节点 第三层由受管节点组成，其中包含数据库和其它受管服务等目标。每个节点上都驻留着一个 Oracle 智能代理，它与 OMS 进行通信并执行控制台和客户端应用程序所发送的任务。 每个节点只需要一个智能代理。

智能代理是独立于数据库、控制台以及 Management Server 运行的。由于它独立于其它组件运行，智能代理可以执行如下任务：启动和关闭数据库、在系统的另一部分关闭时仍保持运行状态。智能代理的 ID 是 dbsnmp。

5 字符集

select * from sys.props$ where name='NLS_CHARACTERSET';

select userenv('language') from dual 服务器上

[******racle] /u01/app/oracle/product/10.2.0/network/admin> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 8 13:31:30 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning and Data Mining options

SQL> SELECT USERENV('LANGUAGE') \ Language

---------------------------------------------------- AMERICAN_AMERICA.UTF8 SQL> show parameter nls

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ nls_calendar string nls_comp string nls_currency string nls_date_format string nls_date_language string nls_dual_currency string nls_iso_currency string

nls_language string AMERICAN nls_length_semantics string BYTE nls_nchar_conv_excp string FALSE nls_numeric_characters string

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ nls_sort string

nls_territory string AMERICA nls_time_format string nls_time_tz_format string nls_timestamp_format string

nls_timestamp_tz_format string

windows客户端：

C:\\Documents and Settings\\Administrator>sqlplus system/manager@***** SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 10月 8 13:28:42 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production With the Partitioning and Data Mining options

SQL> SELECT USERENV('LANGUAGE') \ Language

-------------------------------------------------------------------------------- SIMPLIFIED CHINESE_CHINA.UTF8

服务器字符集select * from nls_database_parameters，其来源于props$，是表示数据库的字符集。

客户端字符集环境select * from nls_instance_parameters,其来源于v$parameter，

表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表

会话字符集环境 select * from nls_session_parameters，其来源于v$nls_parameters，表示会

话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。

客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符。如果多个设置存在的时候，alter session>环境变量>注册表>参数文件

字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。

客户端字符集由于客户端的操作系统不同分为 1.windows

察看注册表 9i

HKEY_LOCAL_MACHINE\\SOFTWARE\\ORACLE\\HOMExx\\NLS_LANG 10g

HKEY_LOCAL_MACHINE\\SOFTWARE\\ORACLE\\KEY_OraHomexx\\NLS_LANG

2.Unix/Linux

env|grep NLS_LANG

6 控制文件Controlfile

6.1 Controlfile的內容：

? 資料庫名字(由init.ora中的DB_NAME或Create database語句中所決定的) ? 資料庫辨識碼(Create database語句執行後所產生的)

? 資料庫的建立時間

? Datafile與Redo logfile的位置 ? 資料庫中所有的Tablespace名稱 ? Datafile的狀態為online或offline ? Log History(資料庫的redo log的歷史記錄) ? Archived log資訊(如果資料庫為Archivelog mode)

? 備份的資訊(如果使用RMAN來備份資料庫，RMAN將備份資料存在這裡) ? 目前的log sequence number ? 目前的Checkpint number

6.2 Controlfile的備份

既然Controlfile對Oracle Server而言，是相當重要的。那麼對Controlfile除了Multiplex外，也需要對它作備份。對Controlfile作備份也可以分成兩種方法： 1、alter database backup controlfile to 'c:/oracle/control.bak'; 將目前的controlfile備份成OS上的Binary file Alter database backup controlfile to trace

將目前的controlfile內容寫到user_dump_dest中的一個trace file中。不過在該目錄下的trace files太多了，我們需要知道那一個是我們要的，所以需要執行下列的指令，來找到所需的trace file。

select spid from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));

得知目前這session的process id為1840

alter database backup controlfile to trace;(备份创建控制文件的脚本)( {oracle_base}\\admin\\orcl\%udump)( 生成一个跟踪文件到init.ora中user_dump_dest所指的目录下)

alter database backup controlfile to ‘d:\\20080326.ctl’ reuse (存在重用)

7 Oracle服务启动基础

7.1 查看数据库启动状态

select status from v$instance; select open_mode from v$database;

1、 Oracle数据服务器包括：实例进程和数据库;实例进程包括：memory structure(sga)以

及background process pmon(进程监控)、smon(系统监控system monitor，空间整理3秒)、dbwr(脏数据处理)、lgwr(将redo buffer文件写会log)，ckpt(checkpoint，同步数据，先写日志、脏数据)等进程;数据库包括：数据文件、控制文件，redo log 文件，三者是必须的，至于 参数文件、密码文件、归档文件是可选的，根据数据库的状态而定;

2、pga:包括user process 和server process，user process通过server process来与Oracle的实例进行进行通信。

3、sga=share pool+database buffer cache+redo log buffer + other structures(large pool,java pool可选) shared_pool=library cache(sql共享，缓冲sql的执行计划，采用最近最少使用算法) + data dictionary cache(row cache，数据字典); database buffer cache(db_cache_size)：Oracle以block size最为基本的数据读写单位 redo log buffer cache:

4、改变参数： alter system set shared_pool_size=64m; alter system set db_cache_size=64m; alter system set large_pool_size=64m; 5、os

验证用户： create user id create group (ora_dba，

ora_databasename_dba,ora_oper,ora_databasename_oper) add user id to group id edit sqlnet.ora (sqlnet.authentication_services=(nts))

6、忘记system/sys的密码：

删掉E:\\Oracle\\product\\10.2.0\\db_1\\database \\pwdtestdb.ora; 使用命令：orapwd file=d:\\pwdtestdb.ora password=admin1 entries=10; 将密码设置为admin1;

或则： sqlplus /@testdb as sysdba alter user sys identified by 新密码; alter user system identified by 新密码;

7、block(块) extend(盘区) segment(段) tablespace(表空间)

8、网络服务的配置信息在

D:\\Oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN\\tnsnames.ora文件中记录

Oracle数据服务器的启动命令

1、启动TNS监听 C:\\Documents and Settings\\Administrator>lsnrctl start

2、启动Oracle Services C:\\Documents and Settings\\Administrator>net start OracleServiceOrcl

3、以sysdba身份登录 SQL> connect system as sysdba

4、启动isqlplus C:\\Documents and Settings\\Administrator>isqlplusctl start

5、启动database control C:\\Documents and Settings\\Administrator>emctl start dbconsole 如果启动错误，很可能是ip地址进行了修改，可采取如下措施： set Oracle_hostname=主机名 emca -config dbcontrol db，进行相应的配置。

6、show all serveroutput OFF

7、set serveroutput on 启动及关闭模式 Oracle数据库的几种启动和关闭方式

Oracle数据服务器有以下几种启动方式：

1、startup nomount 非安装启动，这种方式启动下可执行：重建控制文件、重建数据库 读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件。

2、startup mount dbname 安装启动，这种方式启动下可执行： 数据库日志归档、数据库介质恢复、使数据文件联机或脱机， 重新定位数据文件、重做日志文件。执行“nomount”，然后打开控制文件，确认数据文件和联机日志文件的位置，但此时不对数据文件和日志文件进行校验检查。

3、startup open dbname 先执行“nomount”，然后执行“mount”，再打开包括Redo log文件在内的所有数据库文件， 这种方式下可访问数据库中的数据。

4、startup，等于以下三个命令 startup nomount alter database mount alter database open

5、startup restrict 约束方式启动这种方式能够启动数据库，但只允许具有一定特权的用户访问非特权用户访问时，会出现以下提示： ERROR： ORA-01035: Oracle 只允许具有 RESTRICTED SESSION 权限的用户使用

6、startup force 强制启动方式 当不能关闭数据库时，可以用startup force来完成数据库的关闭 先关闭数据库，再执行正常启动数据库命令

7、startup pfile=参数文件名 带初始化参数文件的启动方式 先读取参数文件，再按参数文件中的设置启动数据库 例：startup pfile=E:\\Oracle\\admin\\oradb\\pfile\\init.ora

USE_NL (glcc glf) USE_MERGE (gp gsb) */ b.application_id, b.set_of_books_id , b.personnel_id, p.vendor_id Personnel, p.segment1 PersonnelNumber, p.vendor_name Name FROM jl_br_journals j, jl_br_balances b,

gl_code_combinations glcc, fnd_flex_values_vl glf, gl_periods gp, gl_sets_of_books gsb, po_vendors p WHERE ...

指示优化器的方法与目标的 hints：

ALL_ROWS -- 基于代价的优化器，以吞吐量为目标 FIRST_ROWS(n) -- 基于代价的优化器，以响应时间为目标 CHOOSE -- 根据是否有统计信息，选择不同的优化器 RULE -- 使用基于规则的优化器 指示存储路径的 hints：

FULL /*+ FULL ( table ) */ 指定该表使用全表扫描

ROWID /*+ ROWID ( table ) */

指定对该表使用 rowid 存取方法，该提示用的较少 INDEX /*+ INDEX ( table [index]) */ 使用该表上指定的索引对表进行索引扫描

INDEX_FFS /*+ INDEX_FFS ( table [index]) */ 使用快速全表扫描

NO_INDEX /*+ NO_INDEX ( table [index]) */

不使用该表上指定的索引进行存取，仍然可以使用其它的索引进行索引扫描

SELECT /*+ FULL(e) */ employee_id, last_name FROM employees e

WHERE last_name LIKE :b1; 指示连接顺序的 hints:

ORDERED /*+ ORDERED */

按from 字句中表的顺序从左到右的连接 STAR /*+ STAR */ 指示优化器使用星型查询 指示连接类型的 hints：

USE_NL /*+ USE_NL ( table [,table, ...] ) */ 使用嵌套连接

USE_MERGE /*+ USE_MERGE ( table [,table, ...]) */ 使用排序- -合并连接

USE_HASH /*+ USE_HASH ( table [,table, ...]) */ 使用HASH 连接

? access path---访问路径(方法)

1. 全表扫描（Full Table Scans, FTS）

为实现全表扫描，Oracle 读取表中所有的行，并检查每一行是否满足语句的 WHERE 限制条件。Oracle 顺序地读取分配给表的每个数据块，直到读到表的最高水线处(high water mark, HWM，标识表的最后一个数据块)。一个多块读操作可以使一次I/O 能读取多块数据 块(db_block_multiblock_read_count 参数设定)，而不是只读取一个数据块，这极大的减少 了I/O 总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，

2. 通过 ROWID 的表存取（Table Access by ROWID 或 rowid lookup）

行的 ROWID 指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过 ROWID 来存取数据可以快速定位到目标数据上，是 Oracle 存取单行数据的最快方法。 为了通过 ROWID 存取表，Oracle 首先要获取被选择行的 ROWID，或者从语句的 WHERE 子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle 然后以得到 的ROWID 为依据定位每个被选择的行。 这种存取方法不会用到多块读操作，一次 I/O 只能读取一个数据块。我们会经常在执 行计划中看到该存取方法，如通过索引查询数据。

3. 索引扫描（Index Scan 或 index lookup）

我们先通过index 查找到数据对应的rowid 值(对于非唯一索引可能返回多个rowid 值)， 然后根据 rowid 直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找(index lookup)。一个 rowid 唯一的表示一行数据，该行对应的数据块是通过一次 i/o 得到的，在此 情况下该次 i/o 只会读取一个数据库块。

在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的 ROWID 值。索 引扫描可以由2 步组成：(1) 扫描索引得到对应的 rowid 值。 (2) 通过找到的 rowid 从表中 读出具体的数据。每步都是单独的一次 I/O，但是对于索引，由于经常使用，绝大多数都已 经 CACHE 到内存中，所以第 1 步的 I/O 经常是逻辑 I/O，即数据可以从内存中得到。但是 对于第2 步来说，如果表比较大，则其数据不可能全在内存中，所以其 I/O 很有可能是物理 I/O，这是一个机械操作，相对逻辑 I/O 来说，是极其费时间的。所以如果多大表进行索引 扫描，取出的数据如果大于总量的5% -- 10%，使用索引扫描会效率下降很多。

根据索引的类型与where 限制条件的不同，有4 种类型的索引扫描： 索引唯一扫描(index unique scan) 索引范围扫描(index range scan) 索引全扫描(index full scan) 索引快速扫描(index fast full scan)

JOIN

1. 排序 - - 合并连接(Sort Merge Join, SMJ)：

a) 对于非等值连接，这种连接方式的效率是比较高的。 b) 如果在关联的列上都有索引，效果更好。

c) 对于将2 个较大的 row source 做连接，该连接方法比 NL 连接要好一些。 d) 但是如果sort merge 返回的 row source 过大，则又会导致使用过多的 rowid 在表中查询数据时，数据库性能下降，因为过多的 I/O。

2. 嵌套循环(Nested Loops, NL)：

a) 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。 b) NESTED LOOPS 有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。 3. 哈希连接(Hash Join, HJ)：

a) 这种方法是在oracle7 后来引入的，使用了比较先进的连接理论，一般来说，其效率应该好于其它2 种连接，但是这种连接只能用在 CBO 优化器中，而且需要设置合适的 hash_area_size 参数，才能取得较好的性能。

b) 在2 个较大的 row source 之间连接时会取得相对较好的效率，在一个row source 较小时则能取得更好的效率。

c) 只能用于等值连接中

要使哈希连接有效，需要设置 HASH_JOIN_ENABLED=TRUE，缺省情况下该参 数为TRUE，另外，不要忘了还要设置hash_area_size 参数，以使哈希连接高效运 行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性 能比其他连接方式还要低。 4. 笛卡儿乘积(Cartesian Product)

当两个row source做连接，但是它们之间没有关联条件时，就会在两个row source中做笛卡儿乘积，这通常由编写代码疏漏造成(即程序员忘了写关联条件)。笛卡尔乘 积是一个表的每一行依次与另一个表中的所有行匹配。在特殊情况下我们可以使用笛卡儿乘积，如在星形连接中，除此之外，我们要尽量使用笛卡儿乘积，否则，自己想结果是什么吧！

SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) USE_NL (glcc glf) USE_MERGE (gp gsb) */ b.application_id, b.set_of_books_id , b.personnel_id, p.vendor_id Personnel,

p.segment1 PersonnelNumber, p.vendor_name Name FROM jl_br_journals j, jl_br_balances b,

gl_code_combinations glcc, fnd_flex_values_vl glf, gl_periods gp, gl_sets_of_books gsb, po_vendors p WHERE ...

指示优化器的方法与目标的 hints：

ALL_ROWS -- 基于代价的优化器，以吞吐量为目标 FIRST_ROWS(n) -- 基于代价的优化器，以响应时间为目标 CHOOSE -- 根据是否有统计信息，选择不同的优化器 RULE -- 使用基于规则的优化器 Create a unique index, then alter the index to be reverse key

SQL> CREATE UNIQUE INDEX i2_t1 ON t1(c2); SQL> ALTER INDEX i2_t1 REBUILD REVERSE;

30.3 语句优化

1. 不要认为将 optimizer_mode 参数设为 rule，就认为所有的语句都使用基于规则的优化器 不管optimizer_mode 参数如何设置，只要满足下面3 个条件，就一定使用 CBO。 1) 如果使用 Index Only Tables(IOTs), 自动使用CBO.

2) Oracle 7.3 以后，如果表上的Paralle degree option 设为>1，则自动使用CBO, 而不管是否用 rule hints.

3) 除rlue 以外的任何hints 都将导致自动使用CBO 来执行语句

总结一下，一个语句在运行时到底使用何种优化器可以从下面的表格中识别出来，从上 到下看你的语句到底是否满足description 列中描述的条件：

Description 对象是否被分析 优化器的类型 ~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~ Non-RBO Object (Eg:IOT) n/a #1 Parallelism > 1 n/a #1 RULE hint n/a RULE ALL_ROWS hint n/a ALL_ROWS FIRST_ROWS hint n/a FIRST_ROWS *Other Hint n/a #1

8、startup EXCLUSIVE

有三种启动方式：

1、shutdown normal 正常方式关闭数据库。

2、shutdown immediate 立即方式关闭数据库。 在SVRMGRL中执行shutdown immediate，数据库并不立即关闭，而是在Oracle执行某些清除工作后才关闭(终止会话、释放会话资源)，当使用shutdown不能关闭数据库时，shutdown immediate可以完成数据库关闭的操作。

3、shutdown abort 直接关闭数据库，正在访问数据库的会话会被突然终止， 如果数据库中有大量操作正在执行，这时执行shutdown abort后，重新启动数据

2、 库需要很长时间。

8 oradim相关用法，（windows系统oracle服务设置）

8.1 创建例程：

-NEW -SID sid | -SRVC 服务 [-INTPWD 口令] [-MAXUSERS 数量] [-STARTMODE a|m] [-PFILE 文件] [-TIMEOUT 秒]

编辑例程：

-EDIT -SID sid [-NEWSID sid] [-INTPWD 口令] [-STARTMODE auto|manual] [-PFILE 文件名] [-SHUTMODE a|i|n] [-SHUTTYPE srvc|inst|srvc,inst] [-TIMEOUT 秒]

删除例程：

-DELETE -SID sid | -SRVC 服务名称

启动服务和例程：

-STARTUP -SID sid [-USRPWD 口令] [-STARTTYPE srvc|inst|srvc,inst] [-PFILE 文件名]

关闭服务和例程：

-SHUTDOWN -SID sid [-USRPWD 口令] [-SHUTTYPE srvc|inst|srvc,inst] [-SHUTMODE a|i|n]

查询帮助： -? | -h | -help

相关参数说明:

-SID sid 指定要启动的实例名称 -SRVC 服务 指定要启动的服务名称

-USRPWD 口令 指定内部用户的口令，如果作为NT管理员登录，不用此参数 -PFILE 文件名 为实例指明初始化参数文件，如果参数文件在Oracle的默认位置，则不需要此命令

-STARTTYPE srvc|inst|srvc,inst 表示要启动什幺srvc—只启动服务inst—启动实例，服务必须已经启动并开始工作了srvc，inst—服务和实例都启动 -SHUTTYPE srvc|inst|srvc,inst 表示要终止什幺，参数选项同上

-STARTMODE a|m 表示启动实例所使用的模式a—auto方式（自动）m—manual方式（手动）

-SHUTMODE a|i|n 表示终止实例所使用的关闭模式a—abort方式i—immediate方式n—normal方式

例子：

1.在服务里生成一个新的实例管理服务，启动方式为手工：oradim -NEW -SID test -STARTMODE manual -PFILE \oradim -NEW -SRVC OracleServicetest -STARTMODE manual -PFILE \注：有效的服务名为“OracleService” 后跟“SID” -SID test 与 -SRVC OracleServicetest 等价

8.2 2.删除此实例或服务：oradim -DELETE -SID test

oradim -DELETE -SRVC OracleServicetest

3.编辑此实例，启动方式改为手动：oradim -EDIT -SID test -STARTMODE manual

4.编辑此实例，启动方式改为自动：oradim -EDIT -SID test -STARTMODE auto

5. 启动test：oradim -STARTUP -SID test

oradim -STARTUP -SID test -STARTTYPE srvc,inst

6.只启动test服务：oradim -STARTUP -SID test -STARTTYPE srvc

7.只启动test实例：oradim -STARTUP -SID test -STARTTYPE inst

8. 关闭test：oradim -SHUTDOWN -SID test

oradim -SHUTDOWN -SID test -SHUTTYPE srvc,inst

常见应用：Oracle重建服务

在Windows系统中有些时候，Oracle的数据库会无法自动启动成功，但是手动启动却是可以的，在这种情况下我们可以尝试重新创建Oracle服务来看看问题是否能够解决。

假设我们的Oracle实例名称是test，使用的参数文件是c:\\oracle\\product\\10.2.0\\db_1\\database\\inittest.ora

那么我们执行下面的命令：

oradim -EDIT -SID test -PFILE c:\\oracle\\product\\10.2.0\\db_1\\database\\inittest.ora -STARTMODE auto -SRVCSTART system

然后重新启动服务，看看Oracle数据库是否可以自动启动。

9 SQL*Loader

1、连入SQL*Plus

以system/manager用户登录， SQL> conn system/manager

创建表结构 SQL> create table test ( id number,--序号 usernamevarchar2(10), --用户名 passwordvarchar2(10), --密码 sj varchar2(20) --建立日期 );

6、创建SQL*Loader输入数据所需要的文件，均保存到C:\\，用记事本编辑： 控制文件：input.ctl，内容如下： load data --1、控制文件标识

infile 'test.txt' --2、要输入的数据文件名为test.txt append into table test--3、向表test中追加记录

fields terminated by X'09'--4、字段终止于X'09'，是一个制表符（TAB） (id,username,password,sj) -----定义列对应顺序 a、insert，为缺省方式，在数据装载开始时要求表为空 b、append，在表中追加新记录

c、replace，删除旧记录，替换成新装载的记录 d、truncate，同上

7、在DOS窗口下使用SQL*Loader命令实现数据的输入 C:\\>sqlldr userid=system/manager control=input.ctl 默认日志文件名为：input.log 默认坏记录文件为：input.bad

10 ORACLE_BASE、ORACLE_HOME有什么区别

ORACLE_BASE下是admin和product

ORACLE_HOME下则是ORACLE的命令、连接库、安装助手、listener等等一系列的东东。 这只是ORACLE自己的定义习惯。ORACLE_HOME比ORACLE_BASE目录要更深一些。也就是说：ORACLE_HOME＝$ORACLE_BASE/product/version

ORACLE_BASE是oracle的根目录，ORACLE_HOME是oracle产品的目录。

简单说，你如果装了2个版本的oracle，那么ORACLE_BASE可以是一个，但ORACLE_HOME是2个

取所有的数据块直到HWM为止，所以全表扫描的性能不会因为delete而提高，但是经过truncate操作后速度会很快。

4、truncate不触发任何delete触发器。

5、不能赋给某个用户truncate其它用户表的权限。如果需要trucate其它用户表的权限必须对该用户赋DROP ANY TABLE权限。

6、当表被truncate后，这个表和索引所占用的空间会恢复到初始大小，而delete操作不会减少表或索引所占用的空间。

7、不能truncate一个带有外键的表，如果要删除首先要取消外键，然后再删除。

13 Redo、undo

在这里会介绍UNDO，REDO是如何产生的，对TRANSACTIONS的影响，以及他们之间如何协同工作的。

13.1

什么是REDO

? ? ? ?

REDO记录transaction logs，分为online和archived。以恢复为目的。

比如，机器停电，那么在重起之后需要online redo logs去恢复系统到失败点。 比如，磁盘坏了，需要用archived redo logs和online redo logs区恢复数据。 比如，truncate一个表或其他的操作，想恢复到之前的状态，同样也需要。

13.2

什么是UNDO

REDO是为了重新实现你的操作，而UNDO相反，是为了撤销你做的操作，比如你得一个TRANSACTION执行失败了或你自己后悔了，则需要用ROLLBACK命令回退到操作之前。回滚是在逻辑层面实现而不是物理层面，因为在一个多用户系统中，数据结构，blocks等都在时时变化，比如我们INSERT一个数据，表的空间不够，扩展了一个新的EXTENT，我们的数据保存在这新的EXTENT里，其它用户随后也在这EXTENT里插入了数据，而此时我想ROLLBACK，那么显然物理上讲这EXTENT撤销是不可能的，因为这么做会影响其他用户的操作。所以，ROLLBACK是逻辑上回滚，比如对INSERT来说，那么ROLLBACK就是DELETE了。

1.

2. 3.

COMMIT

以前，常想当然地认为，一个大的TRANSACTION（比如大批量地INSERT数据）的COMMIT会花费时间比短的TRANSACTION长。而事实上是没有什么区别的，因为ORACLE在COMMIT之前已经把该写的东西写到DISK中了，我们COMMIT只是

4.

1，产生一个SCN给我们TRANSACTION，SCN简单理解就是给TRANSACTION

排队，以便恢复和保持一致性。

5.

2，REDO写REDO到DISK中（LGWR，这就是log file sync），记录SCN在ONLINE

REDO LOG，当这一步发生时，我们可以说事实上已经提交了，这个TRANSACTION已经结束（在V$TRANSACTION里消失了）

6. 7. 8. 9.

3，SESSION所拥有的LOCK（V$LOCK）被释放。

4，Block Cleanout（这个问题是产生ORA-01555: snapshot too old的根本原因）

ROLLBACK

ROLLBACK和COMMIT正好相反，ROLLBACK的时间和TRANSACTION的大小有直接关系。因为ROLLBACK必须物理上恢复数据。COMMIT之所以快，是因为ORACLE在COMMIT之前已经作了很多工作（产生UNDO，修改BLOCK，REDO，LATCH分配），ROLLBACK慢也是基于相同的原因。

10. ROLLBACK会

11. 1，恢复数据，DELETE的就重新INSERT，INSERT的就重新DELETE，UPDATE的

就再UPDATE。

12. 2，RELEASE LOCK 13.

14. ROLLBACK要比COMMIT消耗更多资源，因为ORACLE认为你一旦做数据更新，

那么就意味着你要COMMIT（其他数据库不全是这种设计理念，比如DB2），所以在你更新数据的时候就做了大量的工作，这也可以理解为什么不建议用TABLE来做TEMPORARY TABLE。（TEMP TABLE消耗的REDO比固定表在INSERT时要少很多 ，UPDATE时差不多是1/2，但是DELETE却相差无几）

15.

REDO

LGWR管理REDO，并且是TRANSACTION的结束标志。

16. 产生REDO越多，你的系统越慢，不但影响你自己的SESSION，还影响其他SESSION，

17. 首先要知道怎么监控REDO，当然，SET AUTOTRACE ON可以，不过只能监控DML

语句，而像PROCEDURE则无法监视。那么我们就需要观察字典了，V$MYSTAT, V$STATNAME，前面有两个脚本，mystat，mystat2

14 Create database、instance、lisener 15 Oracle日志查看 17 FlashRecovery

Alter system Flashback=off; Alter system flashback=on;

18 BACKUP&RECOVER 19 Datagurd 20 Rman 21 STATSPACT 22 TUNING 23 merge into 24 正则表达式(over)

25 将oracle设为归档模式archivelog

手工在主服务器创建归档目录 $cd $ORACLE_BASE

#$ORACLE_BASE 所指向的目录是/opt/oracle/，可参看ORACLE用户环境变量的具体设置 $mkdir -p oradata/orcl/archive

# orcl的目录名称根据数据库的SID号进行指定，其实这个目录可以任意指定

$mkdir -p oradata/orcl/standbyarchive #如果主服务器有可能作为备用服务器，则建立该目录

[oracle@testorcl]$ sqlplus / as sysdba;

Nomount 下不可修改archivelog SQL> startup mount;

SQL> alter database archivelog;

alter database archivelog alter database archivelog manual alter database noarchivelog

SQL> archive log start; SQL> archive log list; SQL> alter database open;

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/oracle/oradata/orcl/archive'; # /oracle/oradata/orcl/archive必须和前面mkdir -p oradata/szdb/archive操作建立的路径一模一样

SQL> alter system set log_archive_format='%t_%s.dbf' scope=spfile; SQL> alter system set log_archive_start=true scope=spfile; 重新启动数据库，使修改结果生效 SQL> shutdown immediate; 察看归档模式 SQL> startup SQL> archive log list;

Database log mode Archive Mode Automatic archival Enabled

Archive destination/oracle/oradata/orcl/archive Oldest online log sequence 565 Next log sequence to archive 567 Current log sequence 567

以上alter system set操作会在数据库的spfile@sid.ora和init@sid.ora文件中新增以下参数 *.log_archive_dest_1='LOCATION=/oracle/oradata/orcl/archive' *.log_archive_format='%t_%s.dbf' *.log_archive_start=TRUE

26.14 ARCHIVE_LAG_TARGET

参数可以设置一个时间，通过时间限制，指定数据库强制进行Log Switch，进行归档。

这个参数的缺省值是0，即为不启用该参数。该参数合理的取值范围在60 ~ 7200之间。 通常大于7200和小于1800不被推荐，低于30分钟的切换时间可能导致性能问题。

select name,COMPLETION_TIME from v$archived_log where name is not null

查询日志切换情况。

27 诊断磁盘SQL性能

可以结合v$session_longops 动态性能视图来进行诊断，该视图中记录了长时间(运行时间超过6 秒的)运行的事物，可能很多是全表扫描操作。

27.1 增大logfile

重做日志(redo log)是由LGWR来进行控制的。Oracle对于DML操作数据的时候，并不会马上去修改数据块，而是产出redo记录，并记录到联机的redo log里，当redo Log用满，或者启动全局checkpoint，也或者alter system switch logfile; 这时都会切换redo log group,(如果redo log状态为current，是当前redo log,如果是inactive就是已经有DBWN写入数据块，Inactive还没有写入)redo log进行切换时，有一定的消耗，或者有时由于redo log过小而造成一定的错误，这时，我们都需要将redo log调整变大。 我们可以采取以下步骤来达到目的 1. 检查logfile组状态

SQL> select a.status, b.member, a.THREAD#, a.GROUP# from v$log a, v$logfile b where a.GROUP#=b.GROUP#;

STATUS MEMBER THREAD# GROUP#

—————- ————————————————————- ———- ———-

CURRENT /opt/oracle/oradata/ocrl/redo01.log 1 1 INACTIVE /opt/oracle/oradata/ocrl/redo02.log 1 2 INACTIVE /opt/oracle/oradata/ocrl/redo03.log 1 3

2. 将inactive的redo.log删除，inactive的redo.log已经写入数据块。

SQL> alter database drop logfile ‘/opt/oracle/oradata/ocrl/redo03.log’; 或者直接用 SQL> alter database drop logfile group 3;

3. 添加上面被drop掉的redo log group

SQL> alter database add logfile group 3 (’/opt/oracle/oradata/ocrl/redo03.log’) size 120M reuse;

4. 循环2,3把所有的inactive的redo log文件都resize掉

5. 对于current和active的redo log文件，需要先把redo log做到inactive状态，在前言部分就已经提到了，current表示是当前正在记录的redo log文件，对待这样的我们需要 SQL>alter system switch logfile; 切换日志文件，不过由于redo log的重做日志不会马上触发DBWR写数据库，所以这个redo log的状态由current变成active状态，我们看查看 SQL> select a.status, a.THREAD#, a.GROUP# from v$log a; STATUS THREAD# GROUP# —————- ———- ———- CURRENT 1 1 INACTIVE 1 2 UNUSED 1 3

SQL>alter system switch logfile;

SQL> select a.status, a.THREAD#, a.GROUP# from v$log a;

STATUS THREAD# GROUP# —————- ———- ———- ACTIVE 1 1 CURRENT 1 2 UNUSED 1 3

（注意 group 3是我新加入的redo log group,还没有被使用，状态是UNUSED）

5. 对于active状态的，表示这里的脏数据还没有写入写入数据库，手工加个全局检查点，督促CKPT马上唤醒DBWR写入脏数据 SQL>alter system checkpoint; 查看v$log

STATUS THREAD# GROUP# —————- ———- ———- INACTIVE 1 1 CURRENT 1 2 UNUSED 1 3

在使用2，3步把group 1也重建。

6. 依照4,5把所有active的redo log做掉

到此，所有的redo log都已经全面改变大小，任务完成。

这里要注意两点

1. 单纯加redo log group单个文件的大小没有作用，同一个group里，文件大小都是一致的。

2. 如果是归档模式的话，确保已经自动归档，如果手动归档的话，需要在alter system switch logfile锁死的时候，用alter system log current 来手动归档。或者通过alter system archive log start打开自动归档。否则的话，当redo log group切换完整个groups的时候，会一直等待状态(******).

增加Controlfile

查看控制文件：select name from v$controlfile;

增加控制文件：

alter system set control_files='E:\\oracle\\product\\10.2.0\\oradata\\orcl\\control01.ctl', 'E:\\oracle\\product\\10.2.0\\oradata\\orcl\\control02.ctl','

E:\\oracle\\product\\10.2.0\\oradata\\orcl\\control03.ctl' scope=spfile

28 V$VIEWS

These views are owned by SYS. The V$ views are actually public synonyms for corresponding SYS.V_$ views.

To list all the V$ views on your system:

select name from V$FIXED_TABLE where name like 'V$%';

29 spfile/pfile

create spfile from pfile; create

spfile

=’$oracle_home/database/spfileorcl.ora’

from

pfile=’d:\\dd.ora’; 用

startup

命令启动数据库，oracle

搜索

spfile

的顺序为：

spfile$SID.ora,spfile.ora,init$SID.ora; pfile的文件也是存储初始参数的文件 两者各区别：

spfile是server端维护的，pfile是客户端维护的 pfile

文件会和

spfile

文件在通一个目录下生成：init$SID.ora

（WINDOWS:$ORACLE_HOME/DATABASE

UNIX:$ORACLE_HOME/DBS）

30

INDEX

30.1 对于 CBO 优化器

在 ORACLE 文档上说：对于 RBO 来说，以from 子句中从右到左的顺序选择驱动表，即最右边的表为第一个驱动表，即使在RBO 中，也是有一套规则来 决定使用哪种连接类型和哪个表作为驱动表，在选择时肯定会考虑当前索引的情况，还可能会考虑where中的限制条件，但是肯定是与where 中限制条件的位置无关。

30.2 对于 CBO 优化器：

CBO 根据统计信息选择驱动表，假如没有统计信息，则在from 子句中从左到右的顺序选择驱动表。这与 RBO 选择的顺序正好相反。

Hints

hints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计 划。我们可以用hints来实现： 1) 使用的优化器的类型

2) 基于代价的优化器的优化目标，是all_rows还是first_rows。 3) 表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。 4) 表之间的连接类型 5) 表之间的连接顺序 6) 语句的并行程度

除了”RULE”提示外，一旦使用的别的提示，语句就会自动的改为使用CBO优化器，此 时如果你的数据字典中没有统计数据，就会使用缺省的统计数据。所以建议大家如果使用 CBO或HINTS提示，则最好对表和索引进行定期的分析。

SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b)

本文来源：https://www.bwwdw.com/article/fedd.html