数据库-oracle-学习之路.docx
年数据库-oracle-学习之路1. 数据库基础2. 数据库主键生成Select Substr(To_char(dbms_random.value), 2, 22) | getUUID.NextvalFrom dual ;3. 数据库空间查询-表空间在哪些空间下select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M",round(1-freespace/totalspace)*100,2) "使用率%"from(select tablespace_name,round(sum(bytes)/1024/1024) totalspacefrom dba_data_filesgroup by tablespace_name) df,(select tablespace_name,round(sum(bytes)/1024/1024) freespacefrom dba_free_spacegroup by tablespace_name) fswhere df.tablespace_name=fs.tablespace_name;-查用户下所用空间SELECT owner, tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024, 2) "USED(M)" FROM dba_segmentsGROUP BY owner, tablespace_nameORDER BY SUM (BYTES) DESC;-查用户下所有表所占空间select OWNER, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mmm from dba_segments t where t.owner = 'JIANGSU' and t.segment_type='TABLE' group by OWNER, t.segment_name, t.segment_type order by mmm desc;4. oracle 之删除重复数据select a.rowid,a.* from 表名 a where a.rowid != (select max(b.rowid) from 表名 b where a.字段1 = b.字段1 and a.字段2 = b.字段2 )-删除delete from 表名 a where a.rowid != (select max(b.rowid) from 表名 b where a.字段1 = b.字段1 and a.字段2 = b.字段2 )5. oracle 之查询数据第一条记录select * from tab rownum<26. oracle 之存储过程/函数等书写规则7. oracle 之正则表达式函数：regexp_like、regexp_substr、regexp_instr、regexp_replace（Oracle使用正则表达式离不开这4个函数：1。regexp_like2。regexp_substr3。regexp_instr4。regexp_replace看函数名称大概就能猜到有什么用了。regexp_like 只能用于条件表达式，和 like 类似，但是使用的正则表达式进行匹配，语法很简单：regexp_substr 函数，和 substr 类似，用于拾取合符正则表达式描述的字符子串，语法如下：regexp_instr 函数，和 instr 类似，用于标定符合正则表达式的字符子串的开始位置，语法如下：regexp_replace 函数，和 replace 类似，用于替换符合正则表达式的字符串，语法如下：这里解析一下几个参数的含义：（1。source_char，输入的字符串，可以是列名或者字符串常量、变量。（2。pattern，正则表达式。3。match_parameter，匹配选项。取值范围： i：大小写不敏感； c：大小写敏感；n：点号 . 不匹配换行符号；m：多行模式；x：扩展模式，忽略正则表达式中的空白字符。4。position，标识从第几个字符开始正则表达式匹配。5。occurrence，标识第几个匹配组。6。replace_string，替换的字符串。8. oracle 之decode 函数decode函数=java中的 true?A:B decode(参数，1，2，3) 参数=1时 结果为2否则结果为39. oracle 之linux下启动脚本1)启动数据库oraclesuse92:> sqlplus /nologSQL*Plus: Release 9.2.0.4.0 Production on Fri Jan 20 02:29:37 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect /as sysdbaConnected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 135352820 bytesFixed Size 455156 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 679936 bytesDatabase mounted.2)关闭数据库：oraclesuse92:> sqlplus /nologSQL*Plus: Release 9.2.0.4.0 Production on Fri Jan 20 02:29:37 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect /as sysdbaConnected to an idle instance.SQL> shutdwon abort;3)启动监听器oraclesuse92:> lsnrctl start4)停止监听器oraclesuse92:> lsnrctl stop5)查看监听器状态oraclesuse92:> lsnrctl status或lsnrctl命令进入监听程序后再用status10. oracle 之备份工作背景：Oracle 10g 服务器，Oracle 10g 客户端，windowsXP 操作平台要求：Oracle 数据库服务器对数据库 ytcn 每天自动备份一次。解决方案：利用任务计划、批处理文件和 Oracle 的 exp 导出功能，根据日期自动生成 Oracle 备份文件。详细步骤： 1） 创建批处理文件 ytcn.batytcn.bat中详细内容如下：echo offecho 正在备份银通网 Oracle 数据库，请稍等.exp userid='ytcn/ytcnytcn as sysdba' file=e:/bak/ytcn/oracle/ytcn/ytcn%date:0,4%date:5,2%date:8,2%.dmp log=e:/bak/ytcn/oracle/ytcn/ytcn%date:0,4%date:5,2%date:8,2%.log full=yecho 任务完成！其中红色部分是根据需要进行变动的地方，例如作者的项目名“银通网”，数据库 ytcn 用户名 ytcn，密码 ytcn，要在目录 "e:/bak/ytcn/oracle/ytcn" 下生成形如 "ytcn20090711.dmp" 和 "ytcn20090711.log" 的备份和日志文件，全表导出。另外：%date%的值在不同的系统、语言版本下可能是不一样的，控制面板里面区域选项的设定也会改变%date%的值。请先在命令行中测试 echo %date% 的返回值。%date:4,10% 是返回日期函数，后的第一个参数是要截取的起始位置（从0开始），第二个参数是要截取的长度，如没有则是截取到最后，参数可酌情修改。 如需要准确的时间做为文件名，请用%time%函数，参数同上。 2） 添加一个任务计划 ytcn开始 > 所有程序 > 附件 > 系统工具 > 任务计划 > 添加任务计划 > 下一步 > 在 浏览 中查找刚刚写好的 ytcn.bat 文件 > 任务名输入ytcn,执行这个任务选择每天,下一步 > 起始时间下午12:00,起始日期2009-7-11,下一步 > 输入用户名及密码,用户名要求是管理员权限用户名,下一步 > 完成点击"完成"之后，会在任务计划栏目下新增一个名为"ytcn"的任务计划，表明已经配置完毕。备注：有时点击"完成" 之后,系统警告"已创建新任务，但可能不能运行，因为无法设置账户信息。指定的错误是：Ox80041315：任务计划程序服务没有运行"这是因为电脑的任务计划程序服务没有启动起来。开始 > 所有程序 > 管理工具 > 服务，找到"Task Scheduler"服务，发现启动类型为"已禁用",右键单击更改为"自动"，并把它启动起来，然后重新添加一次任务计划 ytcn 就可以了。11. oracle之命中率查询Buffer cache由数据块组成。1. Buffer cache的工作原理LRU列表：MRU 。 LRU.（全表扫描FTS放在LRU端。）缓冲区块的状态：Free、Pinned、Clean、Dirty.Dirty List或Write List（写列表）。数据库写进程DBW0将缓冲区高速缓存中的数据写到数据文件中。2. 测量Buffer cache的性能测量Buffer cache的命中率：SQL> select 1 - (physical.value - direct.value - lobs.value) / logical.value) "Buffer Cache Hit Ratio" from V$SYSSTAT physical, V$SYSSTAT direct, V$SYSSTAT lobs, V$SYSSTAT logical where physical.name = 'physical reads' And direct.name = 'physical reads direct' and lobs.name = 'physical reads direct (lob)' And logical.name = 'session logical reads'“Buffer Cache Hit Ratio”的值要 > 90%.使用STATSPACK来监视Buffer cache.使用REPORT.TXT来监视Buffer cache.非命中率指标：Free Buffer Inspected、Free Buffer Waits、Buffer Busy Waits.（V$sysstat）使用Performance Manager（数据库例程）来监视Buffer Cache.3. 提高缓冲区高速缓存性能的方法加大Buffer Cache的大小：init.ora参数DB_CACHE_SIZE（动态参数）。使用Buffer Cache Advisory功能决定Buffer Cache的大小：首先将init.ora参数DB_CACHE_ADVICE设成ON，然后查询V$DB_CACHE_ADVICE.使用多个缓冲区池：Keep Pool： DB_KEEP_CACHE_SIZERecycle Pool：DB_RECYCLE_CACHE_SIZEDefault Pool： DB_CACHE_SIZE在内存中缓存表： 表的CACHE选项，对优化小表的全表扫描。正确创建索引。4. 调整Large Pool和JAVA POOLLarge Pool用于共享服务器、RMAN、并行查询、DBWR的从属进程。Large Pool的大小通过init.ora参数Large_pool_size设置。默认为8M.从V$sgastat中监视free memory的值：SQL>SELECT name，bytes FROM V$sgastat WHERE pool = large pool；JAVA_POOL池的默认大小为32M.对于大型Java应用程序，JAVA_POOL池的大小应大于50M.init.ora参数java_pool_size从V$sgastat中监视free memory的值。SQL> SELECT name, bytes / 1024 / 1025 FROM V$sgastat WHERE pool = 'java pool' 调整重做有关的性能Oracle重做有关的组件包括：Redo Log Buffer、Online Redo Log、LGWR、Archive Log、Checkpoint、Arch0.1. 监视Redo Log Buffer的性能Redo Log Buffer不采用LRU（Least Recently Used）算法管理。当下列事件发生时，Redo Log Buffer的内容存盘：Commit时、每3秒、空间使用1/3、达到1M、检查点。如果写入Redo Log Buffer的速度超过LGWR存盘的速度，就会因等待而降低性能。监视Redo Log Buffer的重试率（<1%）。Select retries.value / entries.value "Redo Log Buffer Retry Ratio" From V$sysstat retries,V$sysstat entries Where retries.name = 'redo buffer allocation retries' And entries.name = 'redo entries'“Redo Log Buffer Retry Ratio”的值要 < 1%.Select name,value from V$sysstat where name= 'redo log space requests'如果该值大，需要增加Redo Log Buffer.2. 提高Redo Log Buffer的性能增加Redo Log Buffer的大小：init.ora参数log_buffer.减小重做日志的生成量。（如果设置表的NOLOGGING属性，下列操作不记录在Online Redo Log中：用SQL* Loader的直接路径加载。NOLOGGING属性还可用于下列SQL语句：CREATE TABLE AS SELECT、CREATE INDEX、ALTER INDEX REBUILD、CREATE TABELSPACE）。3. 调整检查点进程的性能测量检查点进程的性能：没有完成的检查点进程的次数。select * from V$system_event；两个事件：checkpoint completed、log file switch（checkpoint incomplete）。Select * from V$sysstat.background checkpoints started和background checkpoints completed.使用Alert日志来记录检查点进程：init.ora参数log_checkpoint_to_alert.使用Performance Manager来测量检查点进程的性能：I/O中的平均灰数据队列长度（如果为0，说明检查点太频繁）。建议调整online redo log的大小，使检查点进程每20-30分钟执行一次。4. 调整联机重做日志文件使用V$system_event来监视联机重做日志文件的性能：log file parallel write、log file switch completed.调整联机重做日志文件的方法：与数据文件、控制文件、归档日志文件分开，放在原始设备上。5. 调整归档性能检查归档进程的性能：通过V$system_event中的log file switch（archiving needed）事件。检查每个归档进程的状态：V$archive_processes.创建多个归档进程：init.ora参数LOG_ARCHIVE_MAX_PROCESSES（默认为212. oracle 之数据库空间限额revoke unlimited tablespace from ccicjy; alter user ccicjy quota 0 on JINGYOUTBS; alter user ccicjy quota unlimited on JYSPACE;空间转换13. oracle 之 查询表空间文件所在路径select * from dba_data_files;14. oracle 之 物化视图学习一、环境数据库1： 数据库：oralce ORACLE10.2.0.1.0 RAC 基表用户：WEB_USER 查询用户：MVLOG_USER MVLOG_USER权限如下： grant comment any table to MVLOG_USER; grant create any table to MVLOG_USER; grant create materialized view to MVLOG_USER; 数据库2 数据库：oralce ORACLE10.2.0.1.0 RAC 物化视图用户：QUERY_USER 网络环境：10M光纤 目的：将数据库1中WEB_USER用户下的表，同步到数据库2的QUERY_USER用户下，为确保数据库1的WEB_USER中数据安全性，通过MVLOG_USER创建物化视图。 二、建立物化视图日志（数据库1 WEB_USER）CREATE MATERIALIZED VIEW LOG ON WEB_USER.ZFZZB TABLESPACE TS_MVLOG; -将对表及表日志查询权限给mvlog_user用户。 grant select on WEB_USER.ZFZZB to mvlog_user; grant select on WEB_USER.mlog$_ZFZZB to mvlog_user;三、创建物化视图（数据库2 QUERY_USER） 1、创建DBlink CREATE public DATABASE LINK TJSB_LINK CONNECT TO mvlog_user IDENTIFIED BY mvlog_user USING 'TJDB' 2、创建表 create table ZFZZB as select * from WEB_USER.ZFZZBTJSB_LINK where 1=2; 3、建立物化视图 CREATE MATERIALIZED VIEW ZFZZB on prebuilt table REFRESH FORCE WITH primary key ON DEMAND AS ( SELECT AAB001, AAC001, AAC002, AAC003, AAE002, AKC087, AKC087S, CAB004, CKC202, CKC205, CKC435, CKC438, CKC442, ZZ from WEB_USER.ZFZZBTJSB_LINK ); 4、全量刷新 exec dbms_mview.refresh('ZFZZB','Complete'); 6、创建主键及索引 alter table ZFZZB add constraint PK_ZFZZB primary key (AAC001,CKC442) using index tablespace TJSB_TS_INDEX; 7、增量刷新 exec dbms_mview.refresh('ZFZZB');15. oracle 学习之位置查询select (length(t.a2) - length(replace(t.a2,'_','')/length('_') from zwj_1 t 16. sql中怎样计算某个字符在字符串中有多少个select (length('oracle11g, oracle10g oracle9i | oracle8i') - length(replace('oracle11g, oracle10g oracle9i | oracle8i', 'oracle', '')/length('oracle') string_count from dual; 17. oracle 学习物化视图物化视图，它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，从而快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL 语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。 其中物化视图有三种：聚集物化视图、包含连接物化视图、嵌套物化视图。但三种物化视图的快速刷新的限制条件有很大区别，而其他方面则区别不大。 创建物化视图创建物化视图时可以指定多种选项，主要选项如下： 1、创建方式（Build Methods）：Build Immediate 和Build Deferred两种。Build Immediate 是在创建物化视图的时候就生成数据，而Build Deferred则在创建时不生成数据，以后根据需要在生成数据。默认为Build Immediate 。 2、查询重写（Query Rewrite）：Enable Query Rewrite和Disable Query Rewrite两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为Disable Query Rewrite。 3、刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：On Demand和On Commit。On Demand指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新。On Commit指物化视图在对基表的DML操作提交的同时进行刷新。刷新的方法有四种：Fast 、Complete 、Force和Never。Fast 刷新采用增量刷新，只刷新自上次刷新以后进行的修改。Complete 刷新对整个物化视图进行完全的刷新。如果选择Force方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用Complete 的方式。Never指物化视图不进行任何刷新。默认值是Force On Demand。 在建立物化视图的时候可以指定Order by 语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。 4、物化视图日志：如果需要进行快速刷新，则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为RowID或Primary Key类型的。还可以选择是否包括Sequence、Including New Values以及指定列的列表。 可以指明On PreBuild Table语句将物化视图建立在一个已经存在的表上。这种情况下，物化视图和表必须同名。当删除物化视图时，不会删除同名的表。这种物化视图的查询重写要求参数Query_Rewrite_integerity必须设置为 trusted或者stale_tolerated。 5、物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪（PCT）。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进行快速刷新操作。对于聚集物化视图，可以在Group by 列表中使用Cube或RollUp，来建立不同等级的聚集物化视图。 示例1: create materialized view MV_ DOP_TEST refresh force on demand as select dsso.dop_id, soo.work_center_no, soo.operation_description from SHOP_ORDER_OPERATION SOO, DOP_SUPPLY_SHOP_ORD DSSO where soo.order_no=dsso.order_no and soo.release_no=dsso.release_no and soo.sequence_no=dsso.sequence_no; -其中创建与删除物化视图与其表或视图DDL一样: DROP materialized view log on materialized_view_log_name ; 创建物化视图时创建存储的日志空间（存储物化视图的对象的改变信息） CREATE MATERIALIZED VIEW LOG ON Dop_Supply_Shop_Ord_Tab -(基表名) tablespace ifsapp_DATA -日志保存在特定的表空间 WITH ROWID ; drop materialized view MV_materialized_view_name ; 示例2: create materialized view MV_DOP_TEST TABLESPACE ifsapp_DATA -保存表空间 BUILD DEFERRED -延迟刷新不立即刷新,此建立初始视图一般没数据 refresh force -如果可以快速刷新则进行快速刷新，否则完全刷新 with rowid -根据rowid刷新（默认是主键） On demand -按照指定方式刷新 start with to_date('2008-12-11 13:20:51','YYYY-MM-DD HH24:MI:SS') next sysdate+1/48 as select dsso.dop_id, soo.work_center_no, soo.operation_description from SHOP_ORDER_OPERATION SOO, DOP_SUPPLY_SHOP_ORD DSSO where soo.order_no=dsso.order_no and soo.release_no=dsso.release_no and soo.sequence_no=dsso.sequence_no其他一、 关于物化视图日志：查询物化视图日志文件格式：desc mlog$_lzwmvtest;创建物化视图时默认指定物化视图中存在主键，如果不指定，那么创建的物化视图日志文件的基表必须存在主键，否则会报错Demo:对一个表test创建日志：create materialized view log on test;那么会报：表'LZWMVTEST'不包含主键约束条件这种情况下，就必须指定日志文件结构比如：create materialized view log on test with rowid（具体的针对日志内容方面的在另外一个专题里说明，这里就简述到此）二、 关于生成数据和刷新：1>生成数据两大选项：build immediate build deferredBuild immediate:在创建物化视图的同时根据主表生成数据Bulid deferred:在创建物化视图的同时，在物化视图内不生成数据，如果此时没有生成数据，以后可以采取：EXEC DBMS_MVIEW.Refresh(MV_name,C)，注意必须使用全量刷新，默认是增量刷新，所以这里参数必须是C，因为之前都没有生成数据，所以必须全量。2>关于刷新² 刷新方式：complete fast forceComplete :完全刷新整个物化视图，相当于重新生成物化视图，此时即时增量刷新可用也全量刷新Ø Fast:当有数据更新时依照相应的规则对物化视图进行更新（此时必须创建物化视图日志（物化视图日志记录了数据更新的日志），关于日志的说明，参照“物化视图日志文件介绍”）Ø Force:当增量刷新可用则增量刷新，当增量刷新不可用，则全量刷新（此项为默认选项）不过从实际情况出发，应该尽量不使用默认选项，可以考虑使用增量刷新，对大表特别有效，大表全量更新速度是非常慢的，特别是在存在索引的情况下（在创建物化视图语句中，可能某些限制查询的条件，导致了增量刷新无法使用，这个是需要注意的，具体是哪类语句导致fast刷新不可用，有待总结.）² 刷新时间：on demand on commit start with/ nextOn demand:在需要刷新时进行刷新（人工判断）On commit:在基表上有提交操作时，进行更新Start with:指定首次刷新的时间（一般指定的是当前时间，不过也可以在创建物化视图时不生成数据，则可以考虑在指定的时间刷新，从而生成数据）Next:刷新的周期时间三、 基于主键的物化视图和ROWID的物化视图的说明创建物化视图日志时，指定了记录更新的原则即with 后面的primary 或者rowid 或者object id等等，后面，默认是以primary key为记录更新，在物化视图内也是以此为更新的原则。例如：1、如果日志内使用的是primary key 则在创建物化视图时指定rowid来更新，则会报ORA-12032: 不能使用 "TEST" 上实体化视图日志中的 rowid 列2、如过日志内使用的是rowid 则在创建物化视图时指定primary或者默认指定，则会报ORA-23415: "GIS"."LZWMV" 的实体化视图日志不记录主键四、 关于物化视图存放的的表空间直接在创建物化视图时指定日志存放的表空间和物化视图的表空间。例子：create materialized view MV_TEST tablespace test -表空间名称五、 关于查询重写和更新在创建查询重写时，基表中必须有主键约束，视图里是无法创建主键的，不过其继承了基表的主键约束。（关于视图的创建的一些技巧有待总结）下面给个例子Create materialized view MV_TESTRefresh fast -前提是必须创建基表日志,可以忽略该项Enable query rewrite -前提是基表上必须存在主键约束AsSelect * from TEST; -物化视图数据生成六、 关于创建物化视图的例子：1、 使用增量刷新的物化视图的写法创建物化视图日志，必须创建日志Create materialized view log on TEST -TEST为表名-注：(TEST为表名或者视图名，关于视图上建立物化视图，见基于视图的物化视图-创建物化视图语句：Create materialized view MV_TEST -MVTEST为物化视图名Build immediate -创建时生成数据对应的是build deferredRefresh fast -增量刷新On commit -在基表有更新时提交，这里该句对视图无效With rowid-这里创建基于rowid的物化视图，对应的是 primary keyAsSelect * from TEST;-生成物化视图数据语句18. oracle学习之linux下启动停止服务 linux下的oracle从10g以后其启动与关闭与以前版本有所不同9i 之后已经没有 svrmgrl 了，所有的管理工作都通过 sqlplus 来完成启动数据库步骤如下:注:$ORACLE_HOME为oracle的安装路径1,以oracle用户登录su oracle2,启动TNS监听器$ORACLE_HOME/bin/ lsnrctl start3,用sqlplus启动数据库$ORACLE_HOME/bin/
