oracle 10g笔记.doc

ORACLE10g笔记目录第一部分：数据库基础31.1ORACLE10g安装31.1.1下载oracle的注意事项31.1.2 oracle10g安装（操作系统为linux5.4版本） oracle10g数据库安装的配置.1检查环境.2安装的前期设置.3数据库中出现的错误与解决方式。81.2ORACLE管理91.2.1客户端方式下字符管理工具sqlplus91.2.2WEB方式下连接ORACLE数据库91.2.3oracle客户端安装【window操作系统下】101.2.4Oracle常见字符集101.2.5Oracle语义排序111.2.6Toad（Tools of Oracle Application Developer）121.2.7Oracle 10g 补丁与升级121.2.8Oracle 10g 卸载121.3SQL/PLSQL基础131.3.1Sqlplus 内部命令131.3.2Sqlplus安装查看历史命令161.3.3SQL（Structured Query Language）数据定义数据操作数据查询完整性约束约束的类型281.3.4PL/SQL（Procedual Language/SQL）311.3.4 .1identifer（标识符）：3Literal (直接量)3Delimiter (分隔符)3Datatypes3程序的例外处理6动态SQL71第二部分：Oracle体系结构732.1Oracle内存结构732.1.1实例与数据库732.1.2 SGA(System Global Area)7 Pre_page_sga7 Lock_sga7 Sga_max_size7Sga_target(10g新参)782.1.3 Data_buffer_cache8 Default pool8 Keep pool8 Recycle pool9 db_nk_cache_size922.1.4 Shared_pool9 Library cache9 Data Dictionary1012.1.5 Large_pool1062.1.6 Java_pool1062.1.7 Streams_buffer1072.1.8 Redo_log_buffer1072.1.9 oracle进程（难点）10 DBWR（Database writer,数据库写入进程）10 LGWR（Log writer，日志写入进程）1 CKPT（check Point 检查点进程）1 ARCH（Archiver，归档进程）1 SMON(System Monitor,系统监控进程)1 PMON(Process Monitor,进程监控进程)1 RECO(Recovery,恢复进程)1 LCKn(Lock ,锁进程)1 Dnnn(Dispatcher,调度进程)10服务器进程server process11 用户进程 User process1202.1.10PGA（ Program Global Area程序全局区）1212.1.11UGA（User Global Area 用户全局区）1232.1.12启动和关闭数据库实例1 数据库启动1 数据库关闭12 操作系统自动启停数据库1282.2Oracle物理结构1312.2.1初参文件13 Pfile 与spfile13查询初始化参数设置13修改初始化参数设置1342.2.2控制文件13控制文件的概述13查询数据库控制文件13修改数据库控制文件1382.2.3数据文件14数据文件的概述14查看数据库数据文件14修改数据库数据文件1442.2.4重做日志文件15重做日志文件概述15查看重做日志文件15修改重做日志文件1522.2.5归档日志文件15启用/禁用归档模式15配置归档文件位置160配置归档文件1632.2.6口令文件16 本地连接16网络连接1732.2.7警告文件和跟踪文件17警告文件17跟踪文件1792.2.8数据库文件与SCN（system change number）17系统当前SCN179第三部分：Oracle系统管理180第一部分：数据库基础1.1 ORACLE10g安装1.1.1下载oracle的注意事项下载oracle的注意事项：硬件，操作系统，CPU（如果内存在4G以上，那么硬件和操作系统都必须是支持64位的）1.1.2 oracle10g安装（操作系统为linux5.4版本） oracle10g数据库安装的配置.1检查环境A # cat /proc/meminfo | grep Total (检查内存和swap空间该点若不符合可以忽略不计，不是必备条件。mem922M，swap1024M)B # df k (检查tmp的大小，存放oracle安装的临时文件。 /tmp400M)C 磁盘空间：2.8G（安装目录）+ 1.5G（源目录）D # uname a(检查kernel版本。Kernel：AS5.4 vmlinuz-2.6.18-164.EL5)E oracle安装软件版本的需求 # uname a(kernel 2.6.9以上) # rpm q glibc(glibcC语言编译器库文件 5.27以上) # rpm q gcc(gcc 3.2以上) # rpm q make (make 编译命令 3.79以上) # rpm q binutils(binutils 二进制编译库文件 2.14以上) # rpm q libaio(libaio 0.3以上) # rpm q openmotif(openmotif 2.2.2-16以上) # rpm q setarch(setarch 1.3-1以上)F 配置IP与主机名对应关系（） # vim /etc/redhat-release(修改红帽的版本5为4。因为oracle只支持在redhat4中安装) # hostname(查看和修改主机名 此处是在内存中查) # vim /etc/sysconfig/network(从配置文件中查看和修改主机名若内存中与配置文件中不一致，须改成一致) # ifconfig eth0 (查看和修改IP此处是在内存中查) # vim /etc/syscofig/network-scripts/ifcfg-eth0(从配置文件中查看和修改IP若内存中与配置文件中不一致，须改成一致) # vim /etc/hosts (修改主机名和IP地址的对应关系若此处的对应关系不正确，会导致开机时启动sendmail服务须很长时间)G 确认selinux及防护墙是否禁用（不禁用会导致oracle客户端连接不上） # vim /etc/selinux/config SELINUX=disabled # chkconfig iptables off # shutdown r nowH 解决安装过程及isqlplus字体乱码问题 # mkdir -p /usr/share/fonts/zh_CN/TrueType/ # cd /usr/share/fonts/zh_CN/TrueType/ # cp ././chinese/TrueType/uming.ttf ./zysong.ttfI 配置linux内核参数 # sysctl -a | grep shm (查看kernel.shmall =2097152；kernel.shmax =536870912；kernel.shmmni =4096) # sysctl a | grep sem(查看 kernel.sem = 250 32000 100 128) # sysctl a | grep file-max(查看 fs.file-max =65536) # sysctl a | grep ip_local_port_range (查看 net。ipv4. ip_local_port_range =1024 65000) # sysctl a | grep mem(查看 net.core.rmem_default = 262144；net.core.wmem_default = 262144；net.core.rmem_max = 262144；net.core.wmem_max = 262144；)（若上述输出值低于指定值，则需要编辑 /etc/sysctl/conf ，加入上述参数即值后，在执行 sysctl p 生效）【附：UNIX系统常用参数shmmaxOS范围内每个共享内存段的最大尺寸，单位为Byte，默认为32M 。查看# ipcs ls# sysctl a | grep shmmax# cat /proc/sys/kernel/shmmax修改# vim /etc/sysctl.conf kernel.shmax =536870912# sysctl p 或 # sysctl w kernel.shmax =536870912 本次有效shmsize同 shmmax，单位Kbytes，AS 5 没有该参数。shmminOS范围内每个共享内存段的最小值，默认值为1 Byte 。查看# ipcs ls# sysctl a | grep shmmin# cat /proc/sys/kernel/shmminshmmniOS范围内每个共享内存段的最大数目，默认为4096。查看# ipcs ls# sysctl a | grep shmmni# cat /proc/sys/kernel/shmmni修改# vim /etc/sysctl.conf kernel.shmmni =4096# sysctl p 或 # sysctl w kernel.shmmni =4096 本次有效shmallOS范围内每个共享内存段的最大页面数量，单位为4 K，默认为2097152 。查看# ipcs ls# sysctl a | grep shmall# cat /proc/sys/kernel/shmall修改# vim /etc/sysctl.conf kernel.shall=2097152# sysctl p 或 # sysctl w kernel.shall =2097152 本次有效参数关系SHMMAX=SHMSIZE*1024；SHMALL*PAGESIZE=OS共享内存最大值，单位Byte。SHMMNI*SHMMAX=OS共享内存最大值，单位Byte。SHMSEG*SHMMAX=一个进程可以可用的共享内存最大值，单位Byte。设置规则SHMALL*PAGESIZE= SHMSEG*SHMMAXSHMALL*PAGESIZE= SHMMNI*SHMMAX】【附：信号量参数(Semaphore)查看# ipcs ls# sysctl a | grep sem# cat /proc/sys/kernel/shmmax 或 cat /proc/sys/kernel/shmsemmni：（Semaphore max number of identifer）OS范围内设置信号量集（set）的最大数目，默认128。Semmsl：（Semaphore max number in limit）一个信号量集（set）中Semaphore的最大个数，默认250。Semmns：（Semaphore max number of system）整个系统中（不是一个信号量集中）Semaphore的最大个数，默认是32000。Semopm：控制每个semop系统调用（用于完成多个信号量操作，属于底层操作）可以执行的信号操作的数量，默认为32，建议为100。】.2安装的前期设置A 创建oracle组与组用户# groupadd oinstall# groupadd dba# useradd m g oinstall G dba oracle# id oracle# passwd oracleB 创建oracle 文件系统或目录 # mkdir p /u01/app/oracle # chown Roracle:oinstall /u01 # chmod R 775 /u01 C 设置shell设置（oracle建议对每个linux账户可以使用的进程数和打开的文件数设置限制）# vim /etc/security/limits.conforacle softnproc2047oracle hardnproc16384oracle softnofile1024oracle hardnofile65536# vim /etc/pam.d/loginSession required/lib/security/pam_limits.soD 设置Oracle用户的环境变量（完成后重登录生效）# vim /home/oracle/.bash_profileumask 022 【创建的目录夹或文件的默认权限】PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/binLD_LIBRARY_PATH=/usr/lib:/usr/X11R6/libORACLE_BASE=/u01/app/oracleORACLE_HOME=$ ORACLE_BASE/produce/10.2.0/db_1 【系统安装目录】ORACLE_SID=ora10 【数据库SID】 LD_LIBRARY_PATH=$ORACLE_HOME/jdk/jre/lib/i386:$ORACLE_HOME/jdk/jre/lib/i386/server: $ORACLE_HOME/rdbms/lib: $ORACLE_HOME/lib:$ LD_LIBRARY_PATHPATH= $ORACLE_HOME/bin:$PATH 【oracle的命令路径】NLS_LANG=American_America.ZHS16GBK 【ZHS16GBK是oracle的字符集】export PATH LD_LIBRARY_PATHexport ORACLE_BASE ORACLE_HOME ORACLE _SID NLS_LANGE 安装包解压# mkdir /oradisk# cp oracle10g01_.0_database_linux32.zip /oradisk# cd /oradisk# unzip oracle10g01_.0_database_linux32.zip # chown Roracle:oinstall /oradisk# chmod R 775 /oradiskF 注销后以oracle用户登录开始安装# cd /oradisk/database 【database是解压安装包生成的子目录】# ./runInstaller【注意不能以su oracle的方式登录系统进行安装，因为这种方式不能使所有的环境变量都导入】G 安装过程中的注意事项1 全局数据库名一定要与/home/oracle/.bash_profile文件中的ORACLE_SID一致 2 数据库口令一定要以字母开头。3 /u01/app/oracle/product/10.2.0/db_1/dbs/sqfileora10.ora【服务器参数文件，规定了oracle启动的参数】4 在文件/etc/oratab中一行要注意：“ora10: /u01/app/oracle/product/10.2.0/db_1/:Y ”【ora10是数据库名字，/u01/app/oracle/product/10.2.0/db_1/是数据库路径，Y决定了数据库用脚本的方式启动，N则是数据库不以脚本的方式启动】H数据库完成后# vim /etc/redhat-release【恢复才做系统版本文件】.3数据库中出现的错误与解决方式。A 错误描述一：“cant connect to X11 window server using 0:0 as the value of the DISPLAY variable”。原因： 使用su oracle的方式登录系统进行安装，因为这种方式不能使所有的环境变量都导入。解决方法是：注销后以oracle用户登录系统安装数据库。B错误描述二：安装完成后，不能启动数据库。显示错误为“could open panameter file /u01/app/oracle/product/10.2.0/db_1/dbs/sqfileora10.ora”。原因：全局数据库名一定要与/home/oracle/.bash_profile文件中的ORACLE_SID不一致解决方法是：使用dbca命令，重新见一个数据库，将数据库名与/home/oracle/.bash_profile文件中的ORACLE_SID一致。C错误描述三：安装过程，出现错误为“Unable to directory /product/10.2.0/db_1. you may not hava the proper permission to create the directory , or there is no space left in the volume .Unable to create the Oracle Home you have speciried”。原因：/home/oracle/.bash_profile文件中ORACLE_HOME的路径写错了。D没有改变信号量，在安装是会出现警告1.2 ORACLE管理1.2.1 客户端方式下字符管理工具sqlplus# lsnrctllsnrctl start【启动服务器端监听】lsnrctl status 【查看服务器端监听】lsnrctl stop 【关闭服务器端监听】1.2.2 WEB方式下连接ORACLE数据库A字符管理工具# isqlplusctl start 【启动数据库】34:5560/isqlplus/ 【客户端访问】【在此方式下不可以关闭/重启数据库】B 字符管理工具 （以dba 的身份登录数据库）# cd $ORACLE_HOME/oc4j/j2ee/isplplus/application-deployments/isqlplus# $ORACLE_HOME/jdk/bin/javaDperties=$ORACLE_HOME/oc4j/j2ee/home/config/ps jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar user “iSQL*Plus DBA/admin” passwd welcome shellJAZH： adduser “iSQL*Plus DBA/admin” xr xr123456 【创建用户及定义密码】JAZH： listusers “iSQL*Plus DBA/admin” 【列出用户】JAZH： grantorle webDba “iSQL*Plus DBA/admin” xr 【赋予dba权限】JAZH： setpasswd “iSQL*Plus DBA/admin” xr xr123456 redhat 【修改密码】JAZH： exit34:5560/isqlplus/dbaC 图形管理工具# emtcl start dbconsole 【启动服务器端】34:1158/em/【客户端访问】1.2.3 oracle客户端安装【window操作系统下】在安装过程下要注意使用Oracle Net Configuration Assistant工具对连接Oracle服务器端进行配置，此配置方法叫 “命名方法配置”。该工具是对4个方面进行配置：数据库名，协议，服务器端IP地址，端口号。【注意：数据库名要与ORACLE_SID一致】 测试连接通不过是，我们可以通过如下方法进行检测：A 命名方法配置须配置的4个参数是否配置正确B ping 服务器端ip C 服务器端监听是否开启D 防火强是否关闭，selinux是否禁用。1.2.4 Oracle常见字符集Oracle常见字符集：US7ASCII：单字节7位表达字符，美国使用。WE8ISO8859P1：单字节8位表达字符，西欧使用。AF16UTF16：固定双字节16位表达字符，符合UTF16标准，东欧及亚洲使用。AL32UTF8：变长1-3字节表达字符，符合UTF8标准，亚洲地区使用。ZHS16GBK：变长1-3字节表达字符，中国大陆使用。SQL select * from nls_database_parameters where parameter in (NLS_CHARACTERSET,NLS_NCHAR_CHARACTERSET);【查看服务器数据库的字符集和国家字符集】NLS_CHARACTERSET：数据库字符集，char，varchar2，clob（保存大数据），long类型数据根据数据库字符集以单字节编码方式进行编译，默认ZHS16GBK，若考虑到未来需要兼容非简体中文字符，可靠虑UTF-8。因为ZHS16GBK为变长字符集，不能处理定长多字符集编码（如nchar数据类型）的处理需求，故引入国家字符集以增强oracle的字符处理能力。NLS_NCHAR_CHARACTERSET：国家字符集，nchar，varchar2，clob类型数据根据国家字符集以定长多字节编码方式进行编码，是oracle的附加补充字符集，用来增强oracle对定长多字节编码的处理，国家字符集只能在unicode编码中的UTF-8和AF16UTF16（默认）中选择。问题描述：在实际使用情况中，在查看数据时，可能会出现乱码的问题。原因是：（如图示）AS5SQL Plus(服务器端)数据库SQL Plus(客户端)windowsUTF-8ZHK16GBKZHK16GBKWE8ISO9660GBK 解决方法是：将客户端的SQLPlus的字符集改成ZHK16GBK就可以解决乱码问题，而服务器端的字符集可以改成gb2312也可以不改，因为实际情况中一般不会在服务器端插入和查看数据。在linux操作系统中# vim /etc/sysconfig/i18n【修改字符集】# locale【查看Linux服务器OS字符集】# set | grep NLS_LANG (NLS_LANG格式：language_territory.charset)【查看Linux服务器客户端程序字符集】Hkey_local_machinesoftwareoracleKey_OraClient10g_home1nls_lang (在regedit中改) 【查win client 客户端程序字符集】C： chcp 【差win OS 字符集】为避免乱码的产生，应考虑服务器OS，服务器客户端程序，数据库，client客户端程序，clientOS各字符集一致或兼容。在装好数据库后，就要更改好字符集，插入数据后，在更改字符集要慎重。更改的字符集要与之前的字符集一定要兼容，不然之前的数据将会被损坏。在导入数据时，要先确定数据的字符集是想对应的，否则数据将会受损，而且不能恢复。建议：在导入数据和更改字符集时，一定要先将数据进行备份。1.2.5 Oracle语义排序SQL select * from nls_database_parameters where parameters=NLS_SORT; 查当前DB语义排序设置，默认为binary，即将待排序数据先转为字符编码，在将编码转为二进制值，在对二进制值进行排序。SQL select value from v$nls_valid_values where parameters=SORT order by value;查DB所支持的语义排序设置，中文支持schinese_pinyin_M拼音，schinese_radical_m偏旁部首，schinese_stroke_m笔画数排序。select * from test; 【默认按插入顺序（rownum）输出】select * from test order by name; 【默认排序按二进制排序输出】select ascii (金) from dual;【查“金”的ascii值】alter session nls_solt=schinese_pinyin_m; 【修改为按拼音排序输出】alter session nls_solt=schinese_radical_m; 【修改为按偏旁部首排序输出】alter session nls_solt=schinese_stroke_m;【修改为按笔画数排序输出】alter session nls_solt=binary_ci;【修改语义排序为二进制排序且忽略大小写】select * from test where name=A;【默认匹配数据时区分大小写】alter session nls_conp=anci;nls_conp 默认值是binary，表示匹配时需将要匹配值都转为二进制值后在匹配，nls_conp设为anci后表示匹配是按照nls_sort所设置的语义排序方式，而nls_solt语义排序又是binary_ci即忽略大小写。1.2.6 Toad（Tools of Oracle Application Developer）Toad for oracle xpert Edition 10.5.1 ,是quest 公司开发的一个oracle辅助软件，有更好的用户界面，支持自动生成很多脚本。常用与开发和管理oracle，Toad支持oracle、DB2、SQL SERVER、MYSQL等多种主流数据库。须先安装oracle客户端，在安装Toad软件。1.2.7 Oracle 10g 补丁与升级在大任何补丁前，先对数据库进行备份1.2.8 Oracle 10g 卸载# rm R /u01/app/oracle/*# rm R /tmp/*# /etc/oratab# shutdown r now 1.3 SQL/PLSQL基础1.3.1 Sqlplus 内部命令SQL select * from scott.emp 2 where sal 2000 3* order by ename;【* 表示当前行】SQL l【列出上一条命令】SQL l 2 【列出上一条命令的第2行（并将其改变成当前行）】SQL l 1 2 【列出上一条命令的第1到第2行】SQL i order by sal 【在当前行后增加一行】SQL del 4 【删除指定行】SQL c/by sal/by abc 【修改当前行中指定字符串】SQL c/by abc/ 【删除当前行中指定字符串】SQL a by sal 【在当前行尾添加指定字符串（a后空两格）】（第一个空格表示命令与字符之间的空格符，第二个空格表示sql语句关键字之间的空格）SQL / 【重新执行上一条SQL语句】这些命令都只能对上一条sql语句进行操作注意：用“sqlplus中如何修改上一条语句中的/字符”的方法是：方式一：select replace (2011/03/17,/,-) from dual;方式二：sqlplus中的替换命令中可以用任意字符（除标点符号外）代替“/”如：c */*-*SQL select 2008/01 from dual;2008/0-2008/01SQL c / *SP2-0023: 未找到字符串SQL c / *1* select 2008 *01 from dualSQL /2008*01-2008 *01SQLSQLselect 2008/08 from dual;2008/08-2008/08已用时间:00: 00: 00.00SQLc/11* select 2008108 from dualSQLl1* select 2008108 from dualSQL/2008108-2008108已用时间:00: 00: 00.00SQLSQL sava aaa； 【将当前命令存盘为aaa.sql】SQL get aaa； 【读入aaa.sql文件】SQL aaa【不读入而直接调用并执行aaa.sql】SQL spool bbb； 【将一下的所有内容全部保存到文件bbb.lst中直至遇到spool off 】 （注意 spool 不可以往文件中追加文字）SQL select * from scott.emp;SQL set term off; 【内容只输出到文件而不输出到屏幕以提高速度】（这种方式只对aaa这种调用文件的形式才有作用）SQL spool;【查看spool变量现在的状态】SQL spoll off; 【关闭spool输出】（在哪个目录下进入的sqlplus则将文件保存在那个目录下）SQL desc scott.emp; 【查看表结构】SQL show user； 【查看当前用户名】SQL clear scr； 【清屏】SQL host vim aaa.sql 【不退出SQLPlus执行操作系统命令】SQL ! vim aaa.sql 【不退出SQLPlus执行操作系统命令】SQL connect scott/tiger; 【不退出SQLPlus切换用户】SQL select * from scott.emp;SQL set pause on; 【设置分屏显示结果】SQL set pagesize 30; 【设置分屏显示结果的行数】SQL select * from scott.emp;SQL set linesize 2000; 【设置一行显示的宽度】（set 命令对所有的SQL语句都有效）SQL create table I （I number）；SQL insert into I values （123456789000）；SQL select * from I；SQL set numformat 999999999999999999； 【设置未定列宽数字输出的宽度】SQL select * from I；SQL set numformat “”; 【恢复原有设置】SQL set linesize 300;SQL select * from scott.emp;SQL column ename format a15 heading 员工姓名 【设置列标题和输出宽度】（column可以缩写成 col ；format 可以缩写成 for；a15 改字符的宽度 ）SQL col job for a20 heading 工作职位 justify center 【设置标题居中】SQL col mgr heading 上级领导 just right null 无【设置空值替换】SQL col sal for $9999.99【设置数字输出格式】SQL select * from scott.emp;SQL col ename 【查询指定列设置】SQL col 【查询所有列设置】SQL show all 【查询所有参数设置】（所做的设置对所有表都生效）SQL create table I （mgr char（8）；SQL insert into I values （）； 【往表中插入空值】若插入空值的语句这样写：Insert into I values （“ ”）；会出现“ORA-01741: illegal zero-length identifier ”错误。SQL select * from I；SQL col mgr clear; 【清楚指定列设置】SQL select * from I；SQL clear columns 【清楚所有列设置】SQL select * from scott.emp;SQL select department_id,last_name,salary from hr.employees order by department_id;SQL set pagesize 150 feedback off 【feedback off清楚表外的细枝末节】SQL select department_id,last_name,salary from hr.employees order by department_id;SQL ttitle left “ * Departmental Salary Bill *” skip 2 【skip 2标题后空1行】SQL select department_id,last_name,salary from hr.employees order by department_id;SQL column salary format $9999.99 heading salarySQL select department_id,last_name,salary from hr.employees order by department_id;SQL column last_name heading EmployeeSQL select department_id,last_name,salary from hr.employees order by department_id;SQL compute sum label subtotal of salary on department_id 【按部门对工资进行小计】SQL break on department_id skip 1 【空1行】SQL select department_id,last_name,salary from hr.employees order by department_id;SQL compute sum label total of salary on reportSQL break on department_id skip 1 on report skip 1SQL select department_id,last_name,salary from hr.employees order by department_id;1.3.2 Sqlplus安装查看历史命令# cp ora004_rlwrap-0.33.tar.gz /root/# cd /root# tar xzvf ora004_rlwrap-0.33.tar.gz# cd rlwrap-0.33# ./configure# make# make install# su oracle# alias sqlplus=rlwrap sqlplus# sqlplus / as sysdbaSQL show sgaSQ