ORACLE-的理论知识课件

ORACLE的理论知识余枫2001.121ORACLE的理论知识余枫1一、Oracle数据库体系结构1、物理结构

datafilesredologfilescontrolfilesparameterfile

数据文件日志文件控制文件参数文件

*.dbf相关数据字典dba_data_files*.log

相关数据字典v$logfilesInitoraid.ora*.ctl

相关数据字典v$controlfile2一、Oracle数据库体系结构1、物理结构*.dbf*.lo

本图为ORACLE数据库扩充前后在硬盘上存储结构的示意图：ORACLE数据库在物理上是存储于硬盘的各种文件。它是活动的，可扩充的，随着数据的添加和应用程序的增大而变化。3本图为ORACLE数据库扩充前后在硬盘上存储结查看当前数据库的物理文件SQL>columntablespace_nameformatA16;SQL>columnfile_nameformatA46;SQL>selectfile_name,round(bytes/(1024*1024),0) total_space,autoextensible, increment_by*4096/(1024*1024),maxbytes/(1024*1024) fromdba_data_files orderbytablespace_name;4查看当前数据库的物理文件SQL>columntables2、逻辑结构

ORACLE数据库在逻辑上是由许多表空间构成。

主要分为系统表空间和非系统表空间。系统表空间存数据字典,非系统表空间内存储着各项应用的数据、索引、程序等相关信息。52、逻辑结构

ORACLE数据库在逻辑上是由常见的表空间名称

表空间tablespacesystemrbsnosystemtoolsusersoem_repositorytempapplicationdataapplicationindex分开存放减少争用存放回滚段存放数据字典信息,不要放用户的数据用在排序和集合运算等6常见的表空间名称表空间tablespacesy查看当前数据库表空间的情况SQL>selecttablespace_name,max_extents,pct_increase,status fromdba_tablespaces;

创建新表空间的命令SQL>createtablespacetablespace_name datafile'/mountdirectory/tablespace_datafile_name.dbf' sizennnMdefaultstorage(initial128knext128k minextents1maxextentsunlimitedpctincrease1);7查看当前数据库表空间的情况SQL>selecttable查看当前数据库表空间使用情况SQL>select*from( selectsum(bytes)/(1024*1024)as"free_space(M)", tablespace_namefromdba_free_space groupbytablespace_name)orderby"free_space(M)";8查看当前数据库表空间使用情况SQL>select*fr

3、ORACLE的进程说明

数据库正常运行时可能出现的进程名#ps–aef|greporacle appdb是数据库sid/opt/oracle/product/8.1.6/bin/tnslsnrlistener–inherit

ora_pmon_appdb

ora_dbw0_appdb

ora_lgwr_appdb

ora_ckpt_appdb

ora_smon_appdb

ora_reco_appdb

ora_snp0_appdb

ora_s000_appdb

ora_d000_appdb9

3、ORACLE的进程说明

ORACLE的进程的详细说明tnslsnrlistener*

ORACLE网络监听进程,处理客户端的连接请求PMON 做程序的清洁工作，处理一些不正常退出的事件．

SMON 做系统的清洁工作，执行系统出错后自动恢复工作．

LCKN Oracle系统表级或行级加锁的进程.

RECO 恢复进程．

DBWRN写数据文件的进程

LGWRN写日志文件的进程

CKPT 检测点

ARCH 归档方式备份进程

SNAPN 管理快照复制等的进程

SNNN MTSServer进程

DNNN MTSDispatcher进程10ORACLE的进程的详细说明tnslsnrlistenerListener监听进程常用命令$listenerstatus

显示ORACLE网络监听进程的状态$listenerservices

显示当前详细的网络监听进程的服务处理状态$lsnrctlreload

重新启动$lsnrctlstop

关闭ORACLE网络监听进程$lsnrctlstart

启动ORACLE网络监听进程11Listener监听进程常用命令$listenerstat

4、ORACLE的内存结构(SGA)

数据库启动时内存分配的情况ORACLE8.1.X版本SGA=((db_block_buffers*blocksize)+(shared_pool_size+large_pool_size+ java_pool_size+log_buffers)+1MB理论上SGA可占OS系统物理内存的1/2——1/3，我们可以根据需求调整,

我推荐SGA=0.4*(OSRAM)shared_pooldatabase_buffer_cacheredo_logbuffer12

4、ORACLE的内存结构(SG

4、Instance和Transaction是什么Instance=SGA+backgroundprocess

实例=内存分配+一组后台进程启动Transaction交易事务(一组修改动作的集合)

Eg:

1、insertDDL(数据定义语句)

delete例如：create,alter,drop,conmit等

commit 每两个DDL语句间是一个transaction

2、updateDML(数据控制语句)

rollback例如：Insert,Delete,Update13

4、Instance和Trans

5、ORACLE的整体体系结构14

5、ORACLE的整体体系结构1

6、分析一个SQL语句是怎样在Orcle内部工作的A、用户发出SQL请求，打开游标B、对SQL语句进行语法分析，找到执行计划，

数据字典等信息存入SGA中共享池内C、从数据文件中把相关数据块读入数据缓冲区D、做相应操作，若做修改，先加上行级锁，经确认后

把改过前后记录内容存入重做日志缓冲区内E、返回结果给用户，关闭游标备注：SQL语句大小写敏感的，同样的一个语句，若大小写不同，ORACLE需分析执行两次，每句后必以“；”结束

15

6、分析一个SQL语句是怎样在O二、启动和关闭数据库

1、命令$svrmgrl 唤醒SVRMGRL数据库管理

SVRMGRL>connectinternal; 以系统管理员身份登录。

SVRMGRL>startup

启动instance

连上数据库

打开数据库$svrmgrl

SVRMGRL>connectinternal;

SVRMGRL>shutdown

关闭数据库时有几个参数

shutdownimmediate;回退所有Transaction,关闭DB

altersystemswitchlogfile; 做日志文件切换,

把内存中修改过的数据块存进物理文件

shutdownabort; 强行关闭DB16二、启动和关闭数据库

1、命令$svrmgrl 唤

2、启动和关闭Oracle数据库过程图

shutdownopen开机关机读控制文件未连上数据库仅启动instancesnomountmount连上数据库，但未打开读参数文件17

2、启动和关闭Oracle数据库三、管理数据库的用户

1、查看当前数据库的用户信息

SQL>selectusername,default_tablespace,temporary_tablespacefromdba_users;

查看在线用户信息SQL>selectcount(*)"currentusernumber",username"currentusername"

fromv$sessiongroupbyusername;

用户查看自己的缺省表空间

SQL>selectusername,default_tablespacefromuser_users;

18三、管理数据库的用户

1、查看当前数据库的用户信息

SQ2、创建新用户

SQL>createuserusernameidentifiedbypassword

defaulttablespacetablespace_name

temporarytablespacetemp

quotaunlimitedontablespace_namequota0konsystem

[quota0konother_tablespace_name……];给用户赋权限SQL>grantconnect,resourcetousername;查看当前用户的权限角色SQL>select*fromuser_role_privs;查看当前用户的系统权限和表级权限SQL>select*fromuser_sys_privs;

SQL>select*fromuser_tab_privs;192、创建新用户

SQL>createuseruser3、常用的角色及其权限

CONNECT 8 privs 连上Oracle,做最基本操作RESOURCE 8privs 具有程序开发最的权限DBA 114privs 数据库管理员所有权限EXP_FULL_DATABASE 5privs 数据库整个备份输出的权限IMP_FULL_DATABASE64privs 数据库整个备份输入的权限

查看角色明细的系统权限SQL>select*fromrole_sys_privs;203、常用的角色及其权限

CONNECT 4、改变老用户

可以改变老用户的密码,缺省表空间,临时表空间,空间限额.SQL>alteruserusernameidentifiedbypassword

defaulttablespacetablespace_name

temporarytablespacetemp

quotaunlimitedontablespace_namequota0konsystem

[quota0konother_tablespace_name……];撤销用户的角色或权限SQL>revokerole_nameorpriv_namefromusername;注意事项撤消用户的角色dba时,同时撤消了用户unlimitedtablespace

的系统权限,切记要再次赋予resource角色给此用户SQL>grantresourcetousername;214、改变老用户

可以改变老用户的密码,缺省表空5、删除老用户

如果用户下没有任何数据对象SQL>dropuserusername;如果用户下有数据对象SQL>dropuserusernamecascade;注意事项

如果用户下有含clob,blob字段的表,应该先删除这些表后,

才能用cascade选项完全删除.225、删除老用户

如果用户下没有任何数据对象22四、数据库的存储结构

1、Oracle数据存储单位

A、Block数据块：2,4,8,16K最小的I-O单位

伴随Database产生而产生，不可变B、Extent一组连续的数据块：是用户所能分配存储的最小单位C、Segment段：有共同结构的一个或几个区域(Extent)

数据对象以段的形式存在(Table,Index,等)D、Tablespace表空间：一组物理数据的逻辑组合，(象逻辑的数据仓库)E、File文件：属于某个表空间的物理文件F、Database数据库：一组表空间所构成的逻辑的可共享的数据。23四、数据库的存储结构

1、Oracle数据存储单位

A、

2、数据块(block)的结构24

2、数据块(block)的结

3、数据块的存储参数 Table的默认值

Pctfree 10 Pctfree,Pctused是互相消涨的,其和不能超过100

Pctused 40

Inittrans 1 在单一块中最初活动的交易事务数

Maxtrans255 在单一块中最大交易事务数常见的几种分配方案大量Update操作 Pcrfree20 Pctused40大量Insert,Select,少量Update Pctfree5 Pctused60体积大的表,Select较多 Pctfree5 Pctused9025

3、数据块的存储参数

4、表空间、表、索引、分区、快照、快照日志的存储参数

默认值 最小值

Initial 5个数据块 2个数据块

Next 5个数据块 1个数据块

Minextents 1 1

Maxextents 根据数据块大小而定 1

Pctincrease 50 0推荐使用的参数规则,使用大小一致的,增长率较低存储分配 initial=next

表空间的 pctincrease1

其它数据对象 pctincrease026

4、表空间、表、索引、分区、快照

5、表空间的存储参数常用的表空间存储参数

INITIAL 128K

NEXT 128K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 1存放超过128M数据对象的表空间存储参数

INITIAL 4M

NEXT 4M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 127

5、表空间的存储参数常用的表空间

6、表和索引的存储参数(一)常用的表和索引存储参数

INITIAL 128K

NEXT 128K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0超过128M表和索引存储参数

INITIAL 4M

NEXT 4M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 028

6、表和索引的存储参数(一)常

6、表和索引的存储参数(二)小于128K很小的表和索引存储参数

INITIAL 128K

NEXT 128K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0如果硬盘资源有限,小于128K的表和索引数量很多,INITIAL和NEXT可以设为两个数据块,假如数据块大小为4KINITIAL 8K

NEXT 8K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 029

6、表和索引的存储参数(二)小

7、segment中可能有十种不同类型的存储对象(1)table 表

(2)view 视图(实为一个select语句)

(3)index 索引

(4)cluster 簇

(5)sequence 序列

(6)synonme 定义另一个数据库对象同义词

(7)snapshot 快照

(8)storedprocedure(function) 存储过程(函数)

(9)package 程序包

(10)dbtrigger 数据触发器30

7、segment中可能有十种ORACLE的理论知识余枫2001.1231ORACLE的理论知识余枫1一、Oracle数据库体系结构1、物理结构

datafilesredologfilescontrolfilesparameterfile

数据文件日志文件控制文件参数文件

*.dbf相关数据字典dba_data_files*.log

相关数据字典v$logfilesInitoraid.ora*.ctl

相关数据字典v$controlfile32一、Oracle数据库体系结构1、物理结构*.dbf*.lo

本图为ORACLE数据库扩充前后在硬盘上存储结构的示意图：ORACLE数据库在物理上是存储于硬盘的各种文件。它是活动的，可扩充的，随着数据的添加和应用程序的增大而变化。33本图为ORACLE数据库扩充前后在硬盘上存储结查看当前数据库的物理文件SQL>columntablespace_nameformatA16;SQL>columnfile_nameformatA46;SQL>selectfile_name,round(bytes/(1024*1024),0) total_space,autoextensible, increment_by*4096/(1024*1024),maxbytes/(1024*1024) fromdba_data_files orderbytablespace_name;34查看当前数据库的物理文件SQL>columntables2、逻辑结构

ORACLE数据库在逻辑上是由许多表空间构成。

主要分为系统表空间和非系统表空间。系统表空间存数据字典,非系统表空间内存储着各项应用的数据、索引、程序等相关信息。352、逻辑结构

ORACLE数据库在逻辑上是由常见的表空间名称

表空间tablespacesystemrbsnosystemtoolsusersoem_repositorytempapplicationdataapplicationindex分开存放减少争用存放回滚段存放数据字典信息,不要放用户的数据用在排序和集合运算等36常见的表空间名称表空间tablespacesy查看当前数据库表空间的情况SQL>selecttablespace_name,max_extents,pct_increase,status fromdba_tablespaces;

创建新表空间的命令SQL>createtablespacetablespace_name datafile'/mountdirectory/tablespace_datafile_name.dbf' sizennnMdefaultstorage(initial128knext128k minextents1maxextentsunlimitedpctincrease1);37查看当前数据库表空间的情况SQL>selecttable查看当前数据库表空间使用情况SQL>select*from( selectsum(bytes)/(1024*1024)as"free_space(M)", tablespace_namefromdba_free_space groupbytablespace_name)orderby"free_space(M)";38查看当前数据库表空间使用情况SQL>select*fr

3、ORACLE的进程说明

数据库正常运行时可能出现的进程名#ps–aef|greporacle appdb是数据库sid/opt/oracle/product/8.1.6/bin/tnslsnrlistener–inherit

ora_pmon_appdb

ora_dbw0_appdb

ora_lgwr_appdb

ora_ckpt_appdb

ora_smon_appdb

ora_reco_appdb

ora_snp0_appdb

ora_s000_appdb

ora_d000_appdb39

3、ORACLE的进程说明

ORACLE的进程的详细说明tnslsnrlistener*

ORACLE网络监听进程,处理客户端的连接请求PMON 做程序的清洁工作，处理一些不正常退出的事件．

SMON 做系统的清洁工作，执行系统出错后自动恢复工作．

LCKN Oracle系统表级或行级加锁的进程.

RECO 恢复进程．

DBWRN写数据文件的进程

LGWRN写日志文件的进程

CKPT 检测点

ARCH 归档方式备份进程

SNAPN 管理快照复制等的进程

SNNN MTSServer进程

DNNN MTSDispatcher进程40ORACLE的进程的详细说明tnslsnrlistenerListener监听进程常用命令$listenerstatus

显示ORACLE网络监听进程的状态$listenerservices

显示当前详细的网络监听进程的服务处理状态$lsnrctlreload

重新启动$lsnrctlstop

关闭ORACLE网络监听进程$lsnrctlstart

启动ORACLE网络监听进程41Listener监听进程常用命令$listenerstat

4、ORACLE的内存结构(SGA)

数据库启动时内存分配的情况ORACLE8.1.X版本SGA=((db_block_buffers*blocksize)+(shared_pool_size+large_pool_size+ java_pool_size+log_buffers)+1MB理论上SGA可占OS系统物理内存的1/2——1/3，我们可以根据需求调整,

我推荐SGA=0.4*(OSRAM)shared_pooldatabase_buffer_cacheredo_logbuffer42

4、ORACLE的内存结构(SG

4、Instance和Transaction是什么Instance=SGA+backgroundprocess

实例=内存分配+一组后台进程启动Transaction交易事务(一组修改动作的集合)

Eg:

1、insertDDL(数据定义语句)

delete例如：create,alter,drop,conmit等

commit 每两个DDL语句间是一个transaction

2、updateDML(数据控制语句)

rollback例如：Insert,Delete,Update43

4、Instance和Trans

5、ORACLE的整体体系结构44

5、ORACLE的整体体系结构1

6、分析一个SQL语句是怎样在Orcle内部工作的A、用户发出SQL请求，打开游标B、对SQL语句进行语法分析，找到执行计划，

数据字典等信息存入SGA中共享池内C、从数据文件中把相关数据块读入数据缓冲区D、做相应操作，若做修改，先加上行级锁，经确认后

把改过前后记录内容存入重做日志缓冲区内E、返回结果给用户，关闭游标备注：SQL语句大小写敏感的，同样的一个语句，若大小写不同，ORACLE需分析执行两次，每句后必以“；”结束

45

6、分析一个SQL语句是怎样在O二、启动和关闭数据库

1、命令$svrmgrl 唤醒SVRMGRL数据库管理

SVRMGRL>connectinternal; 以系统管理员身份登录。

SVRMGRL>startup

启动instance

连上数据库

打开数据库$svrmgrl

SVRMGRL>connectinternal;

SVRMGRL>shutdown

关闭数据库时有几个参数

shutdownimmediate;回退所有Transaction,关闭DB

altersystemswitchlogfile; 做日志文件切换,

把内存中修改过的数据块存进物理文件

shutdownabort; 强行关闭DB46二、启动和关闭数据库

1、命令$svrmgrl 唤

2、启动和关闭Oracle数据库过程图

shutdownopen开机关机读控制文件未连上数据库仅启动instancesnomountmount连上数据库，但未打开读参数文件47

2、启动和关闭Oracle数据库三、管理数据库的用户

1、查看当前数据库的用户信息

SQL>selectusername,default_tablespace,temporary_tablespacefromdba_users;

查看在线用户信息SQL>selectcount(*)"currentusernumber",username"currentusername"

fromv$sessiongroupbyusername;

用户查看自己的缺省表空间

SQL>selectusername,default_tablespacefromuser_users;

48三、管理数据库的用户

1、查看当前数据库的用户信息

SQ2、创建新用户

SQL>createuserusernameidentifiedbypassword

defaulttablespacetablespace_name

temporarytablespacetemp

quotaunlimitedontablespace_namequota0konsystem

[quota0konother_tablespace_name……];给用户赋权限SQL>grantconnect,resourcetousername;查看当前用户的权限角色SQL>select*fromuser_role_privs;查看当前用户的系统权限和表级权限SQL>select*fromuser_sys_privs;

SQL>select*fromuser_tab_privs;492、创建新用户

SQL>createuseruser3、常用的角色及其权限

CONNECT 8 privs 连上Oracle,做最基本操作RESOURCE 8privs 具有程序开发最的权限DBA 114privs 数据库管理员所有权限EXP_FULL_DATABASE 5privs 数据库整个备份输出的权限IMP_FULL_DATABASE64privs 数据库整个备份输入的权限

查看角色明细的系统权限SQL>select*fromrole_sys_privs;503、常用的角色及其权限

CONNECT 4、改变老用户

可以改变老用户的密码,缺省表空间,临时表空间,空间限额.SQL>alteruserusernameidentifiedbypassword

defaulttablespacetablespace_name

temporarytablespacetemp

quotaunlimitedontablespace_namequota0konsystem

[quota0konother_tablespace_name……];撤销用户的角色或权限SQL>revokerole_nameorpriv_namefromusername;注意事项撤消用户的角色dba时,同时撤消了用户unlimitedtablespace

的系统权限,切记要再次赋予resource角色给此用户SQL>grantresourcetousername;514、改变老用户

可以改变老用户的密码,缺省表空5、删除老用户

如果用户下没有任何数据对象SQL>dropuserusername;如果用户下有数据对象SQL>dropuserusernamecascade;注意事项

如果用户下有含clob,blob字段的表,应该先删除这些表后,

才能用cascade选项完全删除.525、删除老用户

如果用户下没有任何数据对象22四、数据库的存储结构

1、Oracle数据存储单位

A、Block数据块：2,4,8,16K最小的I-O单位

伴随Database产生而产生，不可变B、Extent一组连续的数据块：是用户所能分配存储的最小单位C、Segment段：有共同结构的一个或几个区域(Extent)

数据对象以段的形式存在(Table,Index,等)D、Tablespace表空间：一组物理数据的逻辑组合，(象逻辑的数据仓库)E、File文件：属于某个表空间的物理文件F、Database数据库：一组表空间所构成的逻辑的可共享的数据。53四、数据库的存储结构

1、Oracle数据存储单位

A、

2、数据块(block)的结构54

2、数据块(block)的结

3、数据块的存储参数 Table的默认值

Pctfree 10 Pctfree,Pctused是互相消涨的,其和不能超过100

Pctused 40

Inittrans 1 在单一块中最初活动的交易事务数

Maxtrans255 在单一块中最大交易事务数常见的几种分配方案大量Update操作 Pcrfree20 Pctused40大量Insert,Select,少量Update Pctfree5 Pctused60体积大的表,Select较多 Pct