数据库-oracle-学习之路

年数据库学习之路-orac Ie-

1.数据库基础

2.数据库主键生成SelectFrom dual;

3.数据库空间查询一表空间在哪些空间下select df.tablespace_name〃表空间名〃，totalspace〃总空间M〃，freespace〃剩余空间round1-freespace/totalspace*100,2〃使用率%〃fromselect tablespace name,round sumbytes/1024/1024totalspacefrom dba_data_filesgroup by tab1espacename df,select tablespace^name,roundsumbytes/1024/1024freespacefrom dba_free_spacegroup bytablespacenamefswhere df.tab1espace_name=fs.tablespace_ncime;一查用户下所用空间SELECT owner,tablespace_name,ROUND SUMBYTES/1024/1024,2〃USEDMFROM dbasegmentsGROUPBY owner,tablespace nameORDER BY SUMBYTES DESC;一查用户下所有表所占空间select OWNER,t.segment_name,t.segment_type,sumt.bytes/1024/1024mmmfrom dba_segments twheret.owner=JIANGSUand t.segment_type=，TABLE，

16.sql中怎样计算某个字符在字符串中有多少个select lengthJoraclellg,oracle10g oracle9i|oracle8i，一lengthreplaceoraclellg,oracle10g oracle9i|oracle8i，,oracle,/lengthoracle，string_count from dual;

17.oracle学习物化视图物化视图，它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时、就可以防止进行这些耗时的操作，从而快速的得到结果物化视图有很多方面和索引很相似使用物化视图的目的是为了提高查询性能；物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新其中物化视图有三种聚集物化视图、包含连接物化视图、嵌套物化视图但三种物化视图的快速刷新的限制条件有很大区别，而其他方面则区别不大创立物化视图创立物化视图时可以指定多种选项，主要选项如下1＞创立方式Build MethodsBuild Immediate和Build Deferred两种Build Immediate是在创立物化视图的时候就生成数据，而Build Deferred则在创立时不生成数据，以后根据需要在生成数据默认为Build Immediate

2、查询重写Query RewriteEnable Query Rewrite和Disable QueryRewrite两种分别指出创立的物化视图是否支持查询重写查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则防止了聚集或连接操作，而直接从已经计算好的物化视图中读取数据默认为Disable QueryRewrite

3、刷新Refresh指当基表发生了DML操作后,物化视图何时采用哪种方式和基表进行同步刷新的模式有两种:On Demand和On CommitOnDemand指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MV1EW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新On Commit指物化视图在对基表的DML操作提交的同时进行刷新刷新的方法有四种Fast、Complete、Force和NeverFast刷新采用增量刷新，只刷新自上次刷新以后进行的修改Complete刷新对整个物化视图进行完全的刷新如果选择Force方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用Complete的方式Never指物化视图不进行任何刷新默认值是Force OnDemand在建立物化视图的时候可以指定Order by语句，使生o成的数据按照一定的顺序进行保存不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效

4、物化视图日志如果需要进行快速刷新，则需要建立物化视图日志物化视图日志根据不同物化视图的快速刷新的需要，可以建立为RowID或Primary Key类型的还可以选择是否包括Sequence、Including NewValues以及指定列的列表可以指明On PreBuildTable语句将物化视图建立在一个已经存在的表上这种情况下，物化视图和表必须同名当删除物化视图时、不会删除同名的表°这种物化视图的查询重写要求参数QueryRewriteintcgcrity必须设置为trusted或者stale tolcratcdo

5、物化视图可以进行分区而且基于分区的物化视图可以支持分区变化跟踪PCT具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进行快速刷新操作对于聚集物化视图，可以在Group by列表中使用Cube或RollUp,来建立不同等级的聚集物化视图例如1:create materialized view MV_DOP TESTrefresh force on demand as selectdsso.dop_id,soo.work centerno,soo.operation descriptionfrom SHOP ORDER OPERATIONSOO,DOP_SUPPLY_SHOPORDDSSO wheresoo.order_no=dsso.order_noand soo.release_no=dsso.release_no andsoo.sequence_no=dsso.sequence_no;其中创立与删除物化视图与其表或视图DDL一样DROPmaterialized view log onmaterialized view log name;创立物化视图时创立存储的日志空间（存储物化视图的对象的改变信息）----CREATE MATERIALIZEDVIEW LOGONDop_Supply_Shop_Ord_Tab一（基表名）tablespace ifsapp_DATA一日志保存在特定的表空间WITH ROWID;dropmaterialized view MV_materialized_view_name;其他

一、关于物化视图日志查询物化视图日志文件格式descmlog$」zwmvtest;创立物化视图时默认指定物化视图中存在主键，如果不指定，那么创立的物化视图日志文件的基表必须存在主键，否则会报错Demo:对一个表test创立日志create materializedview logon test;那么会报表LZWMVTEST不包含主键约束条件这种情况下，就必须指定日志文件结构比方create materializedview logon testwith rowid（具体的针对日志内容方面的在另外一个专题里说明，这里就简述到此）

二、关于生成数据和刷新1＞生成数据两大选项build immediatebuild deferredBuild immediate:在创立物化视图的同时根据主表生成数据Buliddefwred:在创立物化视图的同时，在物化视图内不生成数据，如果此时没有生成数据，以后可以采取EXEC DBMSJfVIEW.Refresh（MV_name,/）,注意必须使用全量刷新，默认是增量刷新，所以这里参数必须是C,因为之前都没有生成数据，所以必须全量2＞关于刷新2刷新方式complete fastforceComplete:完全刷新整个物化视图，相当于重新生成物化视图，此时即时增量刷新可用也全量刷新0Fast:当有数据更新时依照相应的规则对物化视图进行更新（此时必须创立物化视图日志（物化视图日志记录了数据更新的日志），关于日志的说明，参照“物化视图日志文件介绍〃）0Force:当增量刷新可用则增量刷新，当增量刷新不可用，则全量刷新（此项为默认选项）不过从实际情况出发，应该尽量不使用默认选项，可以考虑使用增量刷新，对大表特别有效，大表全量更新速度是非常慢的，特别是在存在索引的情况下（在创立物化视图语句中，可能某些限制查询的条件，导致了增量刷新无法使用，这个是需要注意的，具体是哪类语句导致fast刷新不可用，有待总结…・・》刷新时间on demandon commitstart with/nextOn demand:在需要刷新时进行刷新（人工判断）On commit:在基表上有提交操作时，进行更新Start with:指定首次刷新的时间（一般指定的是当前时间，不过也可以在创立物化视图时不生成数据，则可以考虑在指定的时间刷新，从而生成数据）Next:刷新的周期时间

三、基于主键的物化视图和ROWID的物化视图的说明创立物化视图日志时，指定了记录更新的原则即with后面的primary或者rowid或者object id等等，后面，默认是以primary key为记录更新，在物化视图内也是以此为更新的原则例如

1、如果日志内使用的是primarykey则在创立物化视图时指定rowid来更新，则会报ORA-12032:不能使用〃TEST〃上实体化视图日志中的rowid列

2、如过日志内使用的是rowid则在创立物化视图时指定primary或者默认指定,则会报ORA-23415:〃GIS〃.〃LZWMV〃的实体化视图日志不记录主键

四、关于物化视图存放的的表空间直接在创立物化视图时指定日志存放的表空间和物化视图的表空间例子create materializedviewMV_TEST tablespacetest-----------------------表空间名称

五、关于查询重写和更新在创立查询重写时，基表中必须有主键约束，视图里是无法创立主键的，不过其继承了基表的主键约束（关于视图的创立的一些技巧有待总结）下面给个例子Create materializedviewMVTESTRefresh fast前提是必须创立基表日志,可以忽略该项Enable queryrewrite前提是基表上必须存在主键约束AsSelect*from TEST;物化视图数据生成

六、关于创立物化视图的例子

1、使用增量刷新的物化视图的写法创立物化视图日志，必须创立日志Create materializedviewlogonTEST——TEST为表名一一注TEST为表名或者视图名，关于视图上建立物化视图，见基于视图的物化视图一一创立物化视图语句Creatematerialized viewMV_TEST----------------------------------------------MVTEST为物化视图名Buildimmediate---------------创立时生成数据对应的是builddeferredRefresh fast--------------增量刷新On commit-----------在基表有更新时提交，这里该句对视图无效With rowid-----------这里创立基于rowid的物化视图，对应的是primary keyAsSelect*from TEST;-------------------生成物化视图数据语句

18.oracle学习之linux下启动停止效劳下的从以后其启动与关闭与以前版本有所不同linux oracle10g9i之后已经没有svrmgrl了，所有的管理工作都通过sqlplus来完成启动数据库步骤如下注$ORACLE_HOME为oracle的安装路径1,以oracle用户登录su oracle2,启动TNS监听器$ORACLE_HOME/bin/Isnrctl start3,用sqlplus启动数据库$ORACLE_HOME/bin/sqlplus/nologSQL connectsystem/change_on_instal1as sysdbaSQL startup出现如下显示，表示Oracle已经成功启动ORACLE instance started.Total SystemGlobal Area205520896bytesFixed Size778392bytesVariable Size74456936bytesDatabase Buffers130023424bytesRedo Buffers262144bytesDatabase mounted.Database opened.4,用sqlplus停止数据库$ORACLE_HOME/b in/sqlplus/nologSQL connectsystem/changeoninstal1as sysdbaSQL shutdown注shutdown可加关闭选项，从最温和到最粗暴的行为选项为shutdown、shutdown transactionalshutdown immediateshutdown abort命令解释如下shutdown:关闭，等待每个用户退出系统或被取消后退出关闭数据库shutdown transactional:事务性关闭，等待每个用户提交成回退当前的事务，然后oracle取消对话，在所有用户退出系统后执行关闭shutdown immediate:直接关闭，取消所有用户对话（促使回退），执行正常的关闭程序shutdownabort:终止关闭，关闭数据库时没有自动检查点或日志开关出现如下显示,表示oracle已经停止Database closed.Database dismounted.ORACLE instanceshut down.

19.oracle学习之rank函数select*From selectrankover partitionbyt.车辆品牌,t・车辆型号order byto_number数字desc,rownum rr,t.*from t_sj ttt wherett.rr=1说明t.车辆品牌，t.车辆型号唯一筛选说明to_number（数字）desc数值排序说明tt.rr=1取num第一个

20.oracle学习之JOB初始化相关参数job queue processesalter system set job_queue_processes=39scope二spfile;〃最大值不能超过1000;job_queue_interval=10//调度作业刷新频率秒为单位job_queue_process表示oracle能够并发的job的数量,可以通过语句show parameterjob_queue_process;来查看oracle中job queueprocess的值当job queueprocess值为0时表示全部停止oracle的job,可以通过语句ALTER SYSTEMSET job queueprocesses=10;来调整启动racle的job相关视图dba jobsalljobs userjobs dba_jobs_running包含正在运行job相关信息提交job语法:beginsys.dbms job.submitjob=:job,what=P_CLEAR_PACKBAL;,next_date=to_date，04-08-200805:44:09,,dd-mm-yyyy hh24:mi:ss,interval=sysdate+l/360,;commit;end;创立JOBvariable jobnonumber;begindbms job.submit:jobno,P CRED_PLAN;,SYSDATE,SYSDATE+1/2880,TRUE;commit;运行JOBSQL begindbms_job.run:jobl;end;删除JOBSQL begindbms job.remove:jobl;end;DBA_J0BS字段列类型描述JOB NUMBER任务的唯一标示号LOG_USER VARCHAR230提交任务的用户PRIVJSER VARCHAR230赋予任务权限的用户SCHEMA_USER VARCHAR230对任务作语法分析的用户模式LAST_DATE DATE最后一次成功运行任务的时间LAST_SEC VARCHAR28如HH24:MM:SS格式的last_date日期的小时，分钟和秒THIS_DATE DATE正在运行任务的开始时间，如果没有运行任务则为nullTHIS SEC VARCHAR28如HH24:MM:SS格式的this date日期的小时，分钟和秒NEXT_DATE DATE下一次定时运行任务的时间NEXT_SECVARCHAR28如HH24:MM:SS格式的next—date日期的小时，分钟和秒T0TAL_TIME NUMBER该任务运行所需要的总时间，单位为秒BROKEN VARCHAR21标志参数，Y标示任务中断，以后不会运行INTERVAL VARCHAR2200用于计算下一运行时间的表达式FAILURES NUMBER任务运行连续没有成功的次数WHAT VARCHAR22000执行任务的PL/SQL块CURRENT_SESSION_LABEL RAWMLSLABEL该任务的信任Oracle会话符CLEARANCEJH RAWMLSLABEL该任务可信任的Oracle最大间隙CLEARANCE_LO RAWMLSLABEL该任务可信任的Oracle最小间隙NLS-ENV VARCHAR22000任务运行的NLS会话设置MISC_ENV RAW32任务运行的其他一些会话参数描述INTERVAL参数值每天午夜12点TRUNC SYSDATE+1每天早上8点30分J TRUNC SYSDATE+1+8*60+30/24*60每星期二中午12点NEXT_DAYTRUNCSYSDATE TUESDAY+12/24，每个月第一天的午夜12点TRUNCLAST_DAYSYSDATE+1每个季度最后一天的晚上11点TRUNCADD_MONTHSSYSDATE+2/24,3,Q7/24每星期六和日早上6点10分TRUNC LEASTNEXTJAY SYSDATE,SATURDAY,NEXT_DAY SYSDATE,SUNDAY+6X60+10/24X601每分钟执行Interval=TRUNCsysdate,mi，+1/24*602:每天定时执行例如每天的凌晨1点执行Interval=TRUNCsysdate+1+1/243:每周定时执行例如每周一凌晨1点执行Interval=TRUNCnext daysysdate,星期一+l/244:每月定时执行例如每月1日凌晨1点执行Interval=TRUNCLAST_DAYSYSDATE+1+1/245:每季度定时执行例如每季度的第一天凌晨1点执行Interval=TRUNCADD_MONTHSSYSDATE,3,，Q*+1/246:每半年定时执行例如每年7月1日和1月1日凌晨1点Interval=ADD MONTHStruncsysdate,yyyy,6+1/247:每年定时执行例如每年1月1日凌晨1点执行Interval=ADD MONTHStruncsysdate,yyyy,12+1/

2421.oracle学习之路索引查吊查找表的所有索引包括索引名，类型，构成列查找表的主键包括名称，构成列select cu.*from user_cons_columns cu,user_constraints auwhere cu.constraint_name au.constraint_name andau.constraint_type=P排量生成创立脚本唯一主键selectalter table\\cu.table name||add constraint，cu.constraint_name||primary key||cu.column_name||from user_cons_columns cu,user_constraints auwhereand au.constraint type=P排量生成创立脚本所有selectcreate index||t.indexname||on||t.tablename||t.columnname||from user_ind_columns t,userindexes iwhereand排量生成创立脚本索弓I-去除主键select createindex||t.index_name||on||t.table_name t.column_name||;，from user_ind_columns t,user_indexes iwhereandandnot existsselect1from user_cons_columns cu,user_constraints auwhereand au.constraint_type=Pand uppert.index name=uppercu.constraint name

22.oracle学习之路oracle中导入dmp数据库文件imp username/password@SID file二XXX.dmp fromuser=XXX touser=XXX tables=XXX,XXX其中，fromuser若为多个表空间的话，使用将其括起来fromuser=a,b；touser参数仿fromuser参数；若只导入一局部表，使用tables参数，用括起要导入的表；如果想全部导入，不需要指定tables参数补充

1.要新建一个数据库；

2.若你的oracle安装在Unix/Linux上，直接在shell中使用imp；如果你的oracle安装在Windows上,随便在哪里开启一个CMD窗口就可以执行imp；

3.username/password指的是你的数据库的登录用户名和密码；

4.fromuser指对方数据库用户名，touser指你的数据库的用户名；“表空间〃中即可创立；

6.要导入所有的表最方便，不用写tables参数就成，不需要知道对方的表名下面介绍的是导入导出的实例数据的导入1将D:/daochu.dmp中的数据导入TEST数据库中imp aichannel/aichannel@HUST full=y file=file=d:/data/newsmgnt.dmp ignore=y上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入在后面加上ignore=y就可以了7将d:/daochu.dmp中的表tablel导入imp system/manager@TEST fi1e=d:/daochu.dmp tables=tablel根本上上面的导入导出够用了不少情况要先是将表彻底删除，然后导入注意操作者要有足够的权限，权限不够它会提示数据库时可以连上的可以用tnsping TEST来获得数据库TEST能否连上数据导出exp system/manager@TEST file=d:/daochu.dmp full=y2将数据库中system用户与sys用户的表导出exp system/manager@TEST file=d:/daochu.dmp owner=system,sys8将数据库中的表inner_notify、notify_staff_relat导出exp aichannel/aichannel@TESTDB2file=d:/data/newsmgnt.dmp tables=inner notify,notify staffrelat4将数据库中的表tablel中的字段filedl以〃00〃打头的数据导出exp system/manager@TEST fi1e=d:/daochu.dmp tables=tablelquery=/〃where filedllike00%/〃上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩也可以在上面命令后面加上compress二y来实现

23.oracle学习之路数据导入导出expDB/impDB一.创立逻辑目录，该命令不会在操作系统创立真正的目录.最好以system等管理员创立.create directory dpdata2asD:\oracle\backup2;二.查看管理理员目录同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错.select*from dba_directories;三.给scott用户赋予在指定目录的操作权限，最好以system等管理员赋予.grant read,write ondirectorydpdata2to aaa;expdp pjbj_chinalife/pjbj_chinalife@orcl dumpfile=pjbj_chinalife.dmp directory=dpdatal tabies=jy mdcode info,jy mdcomm part,jy mdfiginfo,jy mdfig pos,jy mdmodel,jy mdmodel config,jy_md_part_type,jy_md_version,jy_pt_dataimpdp BBB/BBB@orcl directory=dpdata2dumpfile=pjbj chinalife.dmp remapschema=pjbj_chinalife:bbb remaptablespace=users:JYDB1logfile=impdp

111217.log

24.oracle学习之扩展空间

25.oracle学习之磁盘读写

26.Oracle学习之物化视图简单介绍Create materializedview mate_view--视图名称Tablespace viewSpace-----------视图空间Builddeferred一延迟刷新Refresh force一如果可以快速刷新则快速刷新，否则全部刷新Ondemand一按照指定方式刷新Start withto date2012-3-2722:00:00,yyyy-mm-dd hh24:mi:ss―第一次刷新时间As select查询数据Next TRUNKsysdate+1+18/24--刷新时间间隔

1.创立方式build Methods包括BUILD IMMEDIATE和Build Deferred两种Build Immediate是在创立物化视图的时候就生成数据，而Build Deferred则在创立时不生成数据，以后根据需要生成数据默认为Build Immediate

2.查询重写QUERY Rewrite包括ENABLE QUERYREWRITE和Disable QueryRewrite两种分别指出创立的物化视图是否支持重写查询重写是指当对物化视图的基础表进行查询时，Oracle会自动判断是否通过查询物化视图来得到结果，如果可以，则防止了聚集或重新操作，而直接从已经计算好的物化视图中读取数据默认为Disable QeryRewrite

3.刷新REFRESH指当基础表发生了DML操作后，物化视图何时采用哪种方式和基础表进行同步刷新的模式有2种ON Demand和ONCommit oON Demand指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新ON Commit指出物化视图在对基础表的DML操作提交的同时进行刷新刷新的方法有四种FAST、COMPLETE FORCE和NEVEFast刷新采用增量刷新，只刷新自上次刷新以后的修改COMPLETE刷新对整个物化视图进行完全的刷新如果选择FORCE方式，则Oracle在刷新时回去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式NEVER指物化视图不进行任何刷新默认值是FORCE onDEMANDo

4.快速刷新限制REFRESH fast如果需要进行快速刷新，则需要建立物化视图日志物化视图日志根据不同物化视图的快速刷新的需要，可以建立在ROWID或PRIMARY KEY类型的还可以选择在包括SEQUENCE、INCLUDING NEWVALUES以及制定列的列表

5.基于物理表的物化视图可以指明ON PREBUILDTABLE语句将物化视图建立在一个已经存在的表上这种情况下，物化视图或表必须同名当删除物化视图时不会删除同名的表这种物化视图的查询重写要求参数QUERY_RENRITE」NTEGERITY必须设置为trusted或者stale_tolerated

6.REFRESH子句[refresh[fast|complete|force]][on demand|commit][start withdate][next date][with{primary key|rowed}]Refresh FAST增量刷新用物化视图日志参照上面所述来发送主表已经修改的数据行到物化视图中，如果指定Refresh Fast子句，那么应该对主表创立物化视图日志SQLcreale malerializedviewlogon emp;Materialized viewlog created.对于刷新重新生成整个视图，如果请求完全刷新，oracle会完成完全刷新即使增量刷新可用Refresh Complete完全刷新重新生成整个视图，如果请求完全刷新，Oracle会完成完全刷新即使增量刷新可用Refresh Forceorderby mmmdesc;

4.oracle之删除重复数据select a.rowid,a.*from表名a wherea.rowid!=select maxb.rowid from表名bwhere a.字段1二b.字段1anda.字段2二b.字段2一一删除delete from表名a wherea.rowid!=select maxb.rowid from表名bwhere a.字段1二b.字段1anda.字段2二b.字段

25.orac1e之查询数据第一条记录select*from tabrownum

26.oracle之存储过程/函数等书写规则

7.oracle之正贝!表达式函数regexp_like regexp_substr regexp_instr^regexp_replaceOracle使用正则表达式离不开这4个函数:1o regexplikeregexp_substr2o当指定Force子句，如果增量刷新可用Oracle将完成增量刷新，否则将完成完全刷新，如果不指定刷新方法Fast、Complete ForceForce选项默认选项Primary key和row idWithPrimary Key选项生成主键视图，也就是说物化视图是基于主表的主键，而不是Rowld对应于rowid子句PrimaryKey是默认选项，为了生成Prima可Key子句，应该在主表上定义主键，否则应该用基于Rowid的物化视图主键物化视图允许识别物化视图主表而不影响物化视图增量刷新的可用性Rowid的物化视图知识一个单一的主表，不能包括“Distinct、聚合函数、group by子查询、连接、SET操作〃刷新口寸间

7.Start with子句通知数据库完成从主表到本地表第一次复制的时间，应该及时估计下一次运行的时间点，NEXT子句说明了刷新的间隔时间物化视图的一般用法，oracle物化视图是一种特殊的物理表，“物化〃Materialized视图是相对普通视图而言的普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询这样对整体查询性能的提高,并没有实质上的好处

1、物化视图的类型ON DEMAND.ON COMMIT二者的区别在于刷新方法的不同，ON DEMAND顾名思义，仅在该物化视图“需要〃被刷新了，才进行刷新REFRESH,即更新物化视图，以保证和基表数据的一致性；而ON COMMIT是说，一旦基表有了COMMIT,即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致

2、ON DEMAND物化视图物化视图的创立本身是很复杂和需要优化参数设置的，特别是针对大型生产数据库系统而言但Oracle允许以这种最简单的,类似于普通视图的方式来做，所以不可防止的会涉及到默认值问题也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的物化视图的特点1物化视图在某种意义上说就是一个物理表而且不仅仅是一个物理表，这通过其可以被user_tables查询出来，而得到佐证；2物化视图也是一种段segment,所以其有自己的物理存储属性；3物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证；仓ll立语句create materializedview mv_name asselect*from table_name默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMANDo物化视图的数据怎么随着基表而更新？Oracle提供了两种方式，手工刷新和自动刷新，默认为手工刷新也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性这是最根本的刷新方法了自动刷新，其实也就是Oracle会建立一个job,通过这个job来调用相同的存储过程或包，加以实现ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别，即前者不刷新手工或自动就不更新物化视图，而后者不刷新也会更新物化视图，一一只要基表发生了COMMIT创立定时刷新的物化视图create materializedview mvname refreshforce ondemand startwith sysdate next sysdate+1指定物化视图每天刷新一次上述创立的物化视图每天刷新，但是没有指定刷新时间，如果要指定刷新时间比方每天晚上1000定时刷新一次create materializedview mv_name refreshforce ondemand startwith sysdatenext todateconcatto charsysdate+1,dd-mm-yyyy，,’22:00:00,dd-mm-yyyy hh24:mi:ss

3、ON COMMIT物化视图ON COMMIT物化视图的创立，和上面创立ON DEMAND的物化视图区别不大因为ON DEMAND是默认的，所以ON COMMIT物化视图，需要再增加个参数即可需要注意的是，无法在定义时仅指定ON COMMIT,还得附带个参数才行创立ON COMMIT物化视图create materializedview mvname refreshforce oncommit asselect*fromtable name备注实际创立过程中，基表需要有主键约束，否则会报错0RA-

120144、物化视图的刷新刷新Refresh指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步刷新的模式有两种ON DEMAND和ON COMMIT如上所述刷新的方法有四种FAST、COMPLETE FORCE和NEVERFAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改COMPLETE刷新对整个物化视图进行完全的刷新如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式NEVER指物化视图不进行任何刷新对于已经创立好的物化视图，可以修改其刷新方式，比方把物化视图mv_name的刷新方式修改为每天晚上10点刷新一次alter materializedview mvname refreshforceondemand startwithsysdate nextto_dateconcatto_charsysdate+1,J dd-mm-yyyy,22:00:00dd-mm-yyyy hh24:mi:ss

5、物化视图具有表一样的特征，所以可以像对表一样，我们可以为它创立索引，创立方法和对表一样

6、物化视图的删除虽然物化视图是和表一起管理的，但是在经常使用的PLSQL工具中，并不能用删除表的方式来删除在表上右键选择drop，并不能删除物化视图，可以使用语句来实现drop materializedviewmvname

27.Oracle学习之TRUNC函数l.TRUNCfor datesTRUNC函数为指定元素而截去的日期值其具体的语法格式如下TRUNC date[,fmt]其中:date一个日期值fmt日期格式，该日期将由指定的元素格式所截去忽略它则由最近的日期截去下面是该函数的使用情况TRUNC TO_DATE24-Nov-199908:00pm,dd-mon-yyyy hh:mi am二24-Nov-199912:00:00amTRUNC T0_DATE24-Nov-199908:37pm,dd-mon-yyyy hh:mi am,hh=24-Nov-199908:00:00am，round date,J format未指定format时，如果日期中的时间在中午之前，则将日期中的时间截断为12A.M.午夜,一天的开始，否则进到第二天TRUNCdate」format未指定format时,将日期截为12A.M.,不考虑是否在中午之前的条件

2.TRUNC fornumberTRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的局部做相应舍入选择处理，而统统截去其具体的语法格式如下TRUNC number[,decimals]其中number待做截取处理的数值decimals指明需保存小数点后面的位数可选项，忽略它则截去所有的小数局部下面是该函数的使用情况TRUNC

89.985=89TRUNC

89.985,-1=80注意第二个参数可以为负数，表示为小数点左边指定位数后面的局部截去，即均以0记format为day时,只精确到天,而不管几年几月只要是符合的day就可以了，要想确定一年中的某月的某一天就要用truncdate,dd.通俗的说吧,format为年时,精确到----------年为月时.，精确年,月不管哪年，只要是相同的月和哪天年，月，日不管哪年的哪月，只关心是哪天到为日时，精确到实例:对数字，日期进行的SQL selecttrunc sysdatefrom dual;TRUNC SYSD07-1月-03SQL select truncsysdate,mm from dual;TRUNC SYSD01-1月-03SQL selecttrunc sysdate,J yyfrom dual;TRUNC SYSD01-1月-03SQL selecttrunc

234.5565from dual;TRUNC

234.5565234SQL select truncsysdate,fromdual;TRUNCSYSD05-1月-03select truncsysdate,dd fromdual;-2007-9-19selecttruncsysdate,yyyy fromdual;一一2007-1-1selecttruncsysdate,mm fromdual;-2007-9-1begindbms output.tochar sysdate,J yyyy-mm-dd hh24:mi:ss;put_line dbms_output.to_charsysdate+1/24/60/10,yyyy-mm-dd hh24:mi:ss;put_linedbms_output.to cliarsysdate+10/24*60*60,yyyy-mm-dd hh24:mi:ss;put_line dbms output.put lineto chartruncsysdate+10/24*60*60,yyyy-mm-dd hh24:mi:ss，;endbegindbms_output.put_line‘当前时间’；dbms output.put line‘yyyy—mm—dd hh24:mi:ss，;to charsysdate,dbms_output.put_line当前时间+1s;dbms_output.put_lineto_charsysdate+1/24/60/60,yyyy-mm-dd hh24:mi:ss，;dbmsoutput.put line当前时间+1s;dbms_output.put_lineto_charsysdate+5/24/60/60,yyyy-mm-dd hh24:mi:ss，;dbms_output.put_line当前时间+10s;dbms_output.put_lineto_charsysdate+10/24*60*60,yyyy-mm-dd hh24:mi:ss;dbms_output.put_line当前日’；dbms_output.put_line to_chartruncsysdate yyyy-mm-dd hh24:nii:ssdbms_output.put_line当前第2天1点;dbms_output.put_line to_chartruncsysdate+1+1/24yyyy-mm-dd hh24:mi:ssdbms output.put line当前第2天9点’；dbms_output.put_line tochartrunc sysdate+1+9/24,yyyy-mm-dd hh24:mi:ss;end;oracle日期常用函数SYSDATE.日期格式1SYSDATE2-O可得到目前系统的畤3ex.4select sysdatefromdual;5sysdate620-SEP-0711常用之日期格式1213日期格式言兑明1415YYYY/MM/DD~年/月/日16YYYY—年4位17YYY—年3位18YY—年2位19MM一月份20DD—日期21D—星期22-星期日=1星期一=2星期二=323—星期三二4星期四二5星期五二6星期六二72425DDD-----------年之第襄天26WW——年之第襄遇27W~一月之第襄遇28YYYY/MM/DD HH24:MI:SS—年/月/日畤24小畤制分:秒29YYYY/MM/DD HH:MI:SS~年/月/日畤非24小日寺制分:秒30J—Julian day,Be4712/01/01131RR/MM/DD—公元2000冏题32-00-49二下世幺己；50-99二本世幺己33ex.34select to_charsysdate,YYYY/MM/DD，FROM DUAL;-2007/09/2035select to_charsysdate,YYYY FROM DUAL;~200736select tocharsysdate,YYY FROM DUAL;--00737select to_charsysdate,YY，FROM DUAL;--0738select to_charsysdate,J MNfFROM DUAL;--0939select to_charsysdate,J DDJFROM DUAL;-2040select to_charsysdate,J DFROM DUAL;-541select tocharsysdate,J DDD，FROM DUAL;一26342select to_charsysdate,J WW*FROM DUAL;--3843select to_charsysdate,J WFROM DUAL;--344select to_charsysdate,J YYYY/MM/DD HH24:MI:SS，FROM DUAL;-2007/09/2015:24:1345select to_charsysdate,，YYYY/MM/DD HH:MI:SS，FROMDUAL;-2007/09/2003:25:2346select to_charsysdate,J J9FROMDUAL;-245436447select to_charsysdate,RR/MM/DD，FROMDUAL;—07/09/

2028.Oracle学习之JOB定时任务事实上，对于以上需求，我们可以利用数据库本身拥有的功能Job Queue（任务队列管理器）去实现任务队列管理器允许用户提前调度和安排某一任务，使其能在指定的时间点或时间段内自动执行一次或屡次，由于任务在数据库中被执行，所以执行效率很高任务队列管理器允许我们定制任务的执行时间，并提供了灵活的处理方式，还可以通过配置，安排任务在系统用户访问量少的时段内执行，极大地提高了工作效率例如，对于数据库日常的备份、更新、删除和复制等耗时长、重复性强的工作，我们就可以利用任务队列管理器去自动执行以减少工作量目前，拥有此项功能的数据库有许多，最有代表性的是SQL Server

7.

0、Oracle

8.

0.5等但是，要让任务队列管理器工作，还需要我们加以配置才能实现SQL Server的功能配置是在一个图形化界面GUI中实现的，非常简单利用OEM客户端管理工具，Oracle的配置也可以在一个图形界面中完成然而大多数的用户更习惯于命令行的方式去操纵数据库本文介绍如何通过命令行实现这种配置

二、实现步骤

1.确保Oracle的工作模式允许启动任务队列管理器Oracle定时执行“Job Queue〃的后台程序是SNP进程，而要启动SNP进程，首先要确保整个系统的模式是可以启动SNP进程的，这需要以DBA的身份去执行如下命令svrmgrl alter system enablerestricted session;或sql〉alter systemdisenable restricted session;利用如上命令更改系统的会话方式为disenable restricted,为SNP的启动创造条件

2.确保Oracle的系统已经配置了任务队列管理器的启动参数SNP的启动参数位于Oracle的初始化文件中，该文件放在$ORACLE_HOME/dbs路径下，如果Oracle的SID是myora8的话，则初始化文件就是initmyora

8.ora,在文件中对SNP启动参数的描述局部如下jobqueueprocess=n job_queue_interval=N第一行定义SNP进程的启动个数为n系统缺省值为3正常定义范围为036,根据任务的多少，可以配置〜不同的数值第二行定义系统每隔N秒唤醒该进程一次系统缺省值为60秒，正常范围为13600秒事实上，该进程〜执行完当前任务后，就进入睡眠状态，睡眠一段时间后，由系统的总控负责将其唤醒如果该文件中没有上面两行，请按照如上配置添加配置完成后，需要重新启动数据库，使其生效注意如果任务要求执行的间隔很短的话，N的配置也要相应地小一点

3.将任务参加到数据库的任务队列中调用Oracle的dbms.job包中的存储过程，将任务参加到任务队列中dbms_job.submit jobout binaryinteger,what inarchar2,date,next_date invarchar2,interval inbooleanno parsein其中是此任务在任务队列中的编号;•job输出变量,•what执行的任务的名称及其输入参数；•next_date任务执行的时间；•interval任务执行的时间间隔下面详细讨论一下dbms job.submit中的参数interval严格地讲，interval是指上一次执行结束到下一次开始执行的时间间隔，当interval设置为null时，该job执行结束后，就被从队列中删除假设我们需要该job周期性地执行，则要用sysdate+m表示任务重复运行的时间间隔取决于interval参数中设置的日期表达式下面就来详细谈谈该如何设置interval参数才能准确满足我们的任务需求一般来讲，对于一个任务的定时执行，有三种定时要求在一个特定的时间间隔后，重复运行该任务在特定的日期和时间运行任务任务成功完成后，下一次执行应该在一个特定的时间间隔之后第一种调度任务需求的日期算法比较简单，即SYSDATE+n,这里n是一个以天为单位的时间间隔表1给出了一些这种时间间隔设置的例子表1一些简单的interval参数设置例子描述参数值Interval每天运行一次SYSDATE+r每小时运行一次SYSDATE+1/24每10分钟运行一次SYSDATE+10/60*24每30秒运行一次SYSDATE+30/60*24*60每隔一星期运行一次，SYSDATE+7，不再运行该任务并删除它NULL表1所示的任务间隔表达式不能保证任务的下一次运行时间在一个特定的日期或者时间，仅仅能够指定一个任务两次运行之间的时间间隔例如，如果一个任务第一次运行是在凌晨12点，interval指定为SYSDATE+1,则该任务将被方案在第二天的凌晨12点执行但是，如果某用户在下午4点手工DBMS_JOB.RUN执行了该任务，那么该任务将被重新定时到第二天的下午4点还有一个可能的原因是如果数据库关闭或者说任务队列非常的忙以至于任务不能在方案的那个时间点准时执行在这种情况下，任务将试图尽快运行，也就是说只要数据库一翻开或者是任务队列不忙就开始执行，但是这时，运行时间已经从原来的提交时间漂移到了后来真正的运行时间这种下一次运行时间的不断“漂移〃是采用简单时间间隔表达式的典型特征第二种调度任务需求相对于第一种就需要更复杂的时间间隔interval表达式，表7是一些要求在特定的时间运行任务的interval设置例子表

2.定时到特定日期或时间的任务例子描述参数值INTERVAL每天午夜12点J TRUNCSYSDATE+1每天早上8点30分J TRUNCSYSDATE+1+8*60+30/24*60每星期二中午12点J NEXT_DAYTRUNCSYSDATE,TUESDAY+12/24J TRUNCLAST_DAYSYSDATE+1每个月第一天的午夜12点TRUNCADDJ10NTHSSYSDATE+2/24,3,QT/24每个季度最后一天的晚上11点每星期六和日早上6TRUNCLEASTNEXT_DAYSYSDATE,SATURDAY,NEXT_DAYSYSDATE,点10分“SUNDAY+6X60+10/24X60第三种调度任务需求无论通过怎样设置interval日期表达式也不能满足要求这时因为一个任务的下一次运行时间在任务开始时才计算，而在此时是不知道任务在何时结束的遇到这种情况怎么办呢？当然方法肯定是有的，我们可以通过为任务队列写过程的方法来实现这里我只是简单介绍以下，可以在前一个任务队列执行的过程中，取得任务完成的系统时间，然后加上指定的时间间隔，拿这个时间来控制下一个要执行的任务这里有一个前提条件，就是目前运行的任务本身必须要严格遵守自己的时间方案将任务参加到任务队列之前，要确定执行任务的数据库用户，若用户是scott,则需要确保该用户拥有执行包dbms_job的权限；若没有，需要以DBA的身份将权利授予scott用户svrmgrl grantexecute ondbms jobto scott;

4.将要执行的任务写成存储过程或其他的数据库可执行的pl/sql程序段例如，我们已经建立了一个存储过程，其名称为my_job,在sql/plus中以scott用户身份登录，执行如下命令sql variablen number;sql begindbms_job.submit:n my_job;,sysdate,sysdate+1/360;commit;end;系统提示执行成功Sql print:n;系统打印此任务的编号，例如结果为300如上，我们创立了一个每隔4分钟执行一次的任务号为300的任务可以通过Oracle提供的数据字典user_jobs观察该任务的执行情况sql selectjob,next_date,next_sec,failures,broken fromuser_jobs;执行结果如下job next_datenext_sec failuresbroken3002000/10/1011:45:150N这表示任务号为300的任务，下一次将在2000/10/1011:45:15执行，此任务的执行失败记录为0次注意当执行job出现错误时，Oracle将其记录在日志里，失败次数每次自动加1当执行失败次数到达16时,Oracle就将该job标志为broken此后，Oracle不再继续执行它,直到用户调用过程dbms_job.broken,重新设0置为not broken,或强制调用dbms_job.run来重新执行它除了以上我们讨论的submit存储过程之外，Oracle还提供了其他许多存储过程来操作任务例如dbms_job.change、dbms_job.what dbms_job.interval可以用来修改提交的任务要想删除该任务，只需运行dbms_job.removen即可，其中n为任务号对于DBA来说，数据库Job再熟悉不过了，因为经常要数据库定时的自动执行一些脚本，或做数据库备份，或做数据的提炼，或做数据库的性能优化，包括重建索引等等的工作但是，Oracle定时器Job时间的处理上，千变万化，今天我把比较常用写法汇总如下在总结之前，先把Job的参数一一说明一下job参数是由Submit过程返回的binary_ineger这个值用来唯一标识一个工作；what参数是将被执行的PL/SQL代码块；next_date参数指识何时将运行这个工作写Job的时候可以不指定该值；interval参数何时这个工作将被重执行其中Interval这个值是决定Job何时，被重新执行的关键例如有存储过程p_dosomethings,需要被不同的时间间隔执行1declarejobno number;3begin4dbmsjob.submit5jobno,6p_dosomething;,一what7to_date20090101020000,yyyy-mm-dd hh24:mi:ss,—next_date,可以不填8Interval时间字符串‘--interval,关键设置9commit;10end;

1、每分钟执行Interval=TRUNCsysdate,mi+1/24*

602、每天定时执行例如每天的凌晨2点执行Interval=TRUNCsysdate+1+2/

243、每周定时执行例如每周一凌晨2点执行Interval=TRUNC next_day sysdate,星期一+2/

244、每月定时执行例如每月1日凌晨2点执行Interval=〉TRUNCLAST_DAYSYSDATE+1+2/

245、每季度定时执行例如每季度的第一天凌晨2点执行Interval=TRUNC ADD_MONTIIS SYSDATE,3,+2/

246、每半年定时执行例如每年7月1日和1月1日凌晨2点Interval=ADD MONTHStruncsysdate,yyyy,6+2/

247、每年定时执行例如每年1月1日凌晨2点执行Interval=ADD_MONTHStruncsysdate,yyyy，,6+2/

2429.Oracle学习之字符集转换于时先将windows下字符集转换成AL32UTF8SQL selectuserenvlanguage fromdual;USERENV LANGUAGE以下是修改正程SQL startupmountORACLE例程已经启动Total SystemGlobal Area535662592bytesFixed Size1334380bytesVariable Size176161684bytesDatabase Buffers352321536bytesRedo Buffers5844992bytes数据库装载完毕SQL alter system enablerestrictedsession;系统已更改SQL alter system setjob_queue_processes=0;系统已更改SQL alter systemsetaq tmprocesses、；系统已更改SQL alterdatabase open;数据库已更改SQL ALTERDATABASE character set INTERNAL_USE AL32UTF8;数据库已更改SQLshutdownimmediate数据库已经关闭3o regexp_instr4o regexpreplace看函数名称大概就能猜到有什么用了REGEXP LIKEregexp_like只能用于条件表达式，和like类似，但是使用的正则表达式进行匹配，语法很简单:match_parame u18c haX^^patteREGEXP SUBSTRregexp_substr函数，和substr类似，用于拾取合符正则表达式描述的字符子串，语法如下:t REGEXPINSTR ha^y^patte rn^regexp_instr函数，和instr类似，用于标定符合正则表达式的字符子串的开始位置，语法如下:regexpjnstr__________return_option/oocurrenoey^---------------2mate h_paramete rj-^—REGEXP R印LACEposition》rrenoepositiorQ~regexp,epgce「研JO^repgs-Strinregexp_replace函数，和replace类似，用于替换符合正则表达式的字符串，语法如下:match_parame已经卸载数据库ORACLE例程已经关闭SQL startupORACLE例程已经启动一查看字符集SQL selectuserenvlanguage fromdual;USERENVC LANGUAGE修改完之后，10g以上采用csscan工具验证字符集先安装DOS下csscan system/1Character SetScanner v

2.2:Release

11.

1.

0.

6.0-Production on星期三12月2114:12:502011Copyright c1982,2007,Oracle.All rightsreserved.Connected to:Oracle Database山Enterprise EditionRelease

11.

1.

0.

6.0-ProductionWith thePartitioning,OLAP,Data Miningand RealApplication Testingoptions lFulldatabase,2User,3Table,4Column:11Current database charactersetis AL32UTF

8.Enter newdatabasecharacterset name:AL32UTF8Enter arrayfetch buffersize:1024000Enter numberof scanprocesses toutilized..32:1是后验证成功！

30.Oracle学习之调整表字段长度select Jalter table\\t.TABLE_NAME||，modify，||t.COLUMN_NAME||，||t.DATATYPE||||t.DATA_LENGTH*2||from alltabcolumns twheret.OWNER=PJBJ_JY-用户名称and t.DATATYPE=VARCHAR2or t.DATATYPE=CHAR，

31.Oracle学习之ASM数据库的启动关闭开srvctl startinstance-d cxlpdb-i cxlpdb2关srvctl stopdatabase-d cxlpdbsrvctlstatus database-d orcl查看

32.Oracle学习之空间调整select Jalter table|owner||||table_name|move tablespacePJBJ;from dba_tables whereowner inPJBJJY,PJBJ_TEST，,，PJBJ_BD,PJBJ_AQQX，;select Jalter table||owner||.||table name|move tablespaceZCCX;from dbatables whereowner in，RCBX_ZC，;SELECT ALTERindex||index_name||REBUILD tablespaceIDX;FROM USERJNDEXESWHERE TABLE_OWNER inPJBJ_JY,PJBJ_BD,RCBX_ZC，PJBJ_AQQX，;

33.Oracle学习之双层循环存储过程create orreplace procedureproc testiswid varchar2500;cursor vCurisselect t.id fromaq_zz t;beginopen vCur;loopfetch vCurinto wid;if vCur%notfound thenexit;end if;for vclin selectid asseq fromaq_yhxxb loopinsertinto AAal,a2,a3values vvid,vcl.seq,T;end loop;end loop;close vCur;end proc_test;

34.Oracle学习之instr函数INSTR CORPORATE FLOOR，,J0R，,3,2中,源字符串为‘CORPORATEFLOOR,目标字符串为‘OR,起始位置为3,取第2个匹配项的位置查询某字符在字段中的位置

35.Oracle学习之锁表Oracle的锁表与解锁SELECT/*+rule*/s.username,decode i.type,‘TM,TABLE LOCK，,‘TX,ROW LOCK,NULL LOCK_LEVEL,o.owner,o.object_name,o.object_type,FROM v$session s,v$lock1,dba objects oAND

1.idl=o.object_id+AND s.username isNOT Null--kill session语句alter systemkill session50,492’;一以下几个为相关表SELECT*FROM v$lock;SELECT*FROM v$sqlarea;SELECT*FROM v$session;SELECT*FROM v$process;SELECT*FROM v$locked_object;SELECT*FROM all_objects;SELECT*FROM v$session_wait;SELECT

1.session id sid,s.serial#,

1.locked mode,

1.orac1e username,FROM v$locked_object1,all_objectso,v$session sORDERBY sid,s.serial#;--

2.查出锁定表的session的sid,serial#,os_user_name,machine name,terminal和执行的语句一比上面那段多出sql_text和actionSELECT Lsession_idsid,s.serial#,

1.locked_mode,

1.oracle_username,s.user#,FROM v$sqlarea a,v$session s,v$locked_object1ORDERBY sid,s.serial#;一一

3.查出锁定表的sid,serial#,os username,machine name,terminal,锁的type,mode SELECT s.sid,s.serial#,s.username,s.schemaname,s.osuser,s.process,s.machine,FROM v$session s,v$lock1AND s.username ISNOT NULLORDERBYsid;这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁杀锁命令alter systemkill sessionsid,serial#SELECT/*+rule*/s.username,decode i.type,‘TM,TABLE LOCK,‘TX,ROW LOCK,NULL LOCK_LEVEL,o.owner,o.object_name,o.object_type,FROM v$session s,v$lock1,dba objectsoAND

1.idl=o.object_id+AND s.username isNOT NULL如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待以下的语句可以查询到谁锁了表，而谁在等待以上查询结果是一个树状结构，如果有子节点，则表示有等待发生如果想知道锁用了哪个回滚段，还可以关联到V$rollname,其中xidusn就是回滚段的USN coluser_name formatalO colownerformat alOcolobject_name formatalO colobject_type formatalOSELECT/*+rule*/lpad，,decode

1.xidusn,0,3,

011.oracle_username User_name,o.owner,o.object_name,o.object_type,s.sid,s.serial#FROM v$locked object1,dba objectso,v$session sORDERBY o.object_id,xidusn DESC

36.Oracle学习之进程处理oracle杀死死锁进程.先查看哪些表被锁住了from v$locked_object a,dba_objects bwhere b.object_id=a.object_id;OWNER OBJECT_NAMESESSION_ID LOCKEDJIODEWSSBSBDA_PSHPFTDT223WSSB_RTREPOS WB_RT_SERVICE_QUEUE_TAB242WSSB_RTREPOS WB_RT_NOTIFY_QUEUE_TAB292WSSB RTREPOSWB RTNOTIFY QUEUE,TAB392SBDA_PSDBDTWSSB47WSSB RTREPOSWB_RT_AUDIT_DETAIL473select b.username,b.sid,b.serial#,logon_time fromv$locked_object a,v$session bwherea.session_id=b.sid orderby b.logon_time;USERNAME SIDSERIAL#LOGON_TIMEWSSB_RTACCESS3911782006-5-221WSSB_RTACCESS2954972006-5-221杀会话alter systemkill session sid,serial#;altersystemkill session29,5497’;如果有ora-00031错误，则在后面加immediate;altersystemkill session29,5497immediate;查V$DB-OBJECT CACHE视图SELECT*FROM V$DB_OBJECT_CACHE WHEREOWNER=过程的所属用户AND CLOCKS!=0;

2.查是哪一个SID,通过SID可知道是哪个SESSION.查丫$ACCESS视图SELECT*FROM V$ACCESS WHEREOWNER=过程的所属用户AND NAME=刚刚查到的过程名；

3.查出SID和SERIAL#查V$SESSION视图SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERESID=刚刚查到的SID，查V$PROCESS视图SELECT SPIDFROM V$PROCESS WHEREADDR=刚刚查到的PADDR;

4.杀进程

1.先杀ORACLE进程ALTER SYSTEM KILL SESSION查出的SID,查出的SERIAL#;2,再杀操作系统进程KILL-9刚刚查出的SPID或ORAKILL刚刚查出的SID刚刚查出的SPIDoracle的死锁查询数据库死锁from v$locked objecttl,v$session t2,v$sqltext t3where tl.session_id=t

2.sidorder byt

2.logon_time;查询出来的结果就是有死锁的session了，下面就是杀掉拿到上面查询出来的SID和SERIAL#,填入到下面的语句中altersystemkill sessionsid,serial#;一般情况可以解决数据库存在的死锁了，或通过session id查到对应的操作系统进程，在Unix中杀掉操作系统的进程SELECT a.username,c.spid ASos_process_id,c.pid ASoracle_process_id FROMv$session a,v$p rocesscWHERE c.addr=a.paddr anda.sid=anda.serial#=;然后采用kill unix或orakill windows在unix中ps-ef|grep os_process_idkill-9os_process_idps-ef|grep os_process_id经常在oracle的使用过程中碰到这个问题，所以也总结了一点解决方法1查找死锁的进程sqlplus〃/as sysdba”sys/change_on_instal1SELECTs.username,

1.OBJECT_ID,

1.SESSION_ID,s.SERIAL#,

1.ORACLE_USERNAME,

1.OS_USER_NAME,

1.PROCESSFROM V$LOCKED_OBJECT1,V$SESSION SWHERE

1.SESSION_ID=S.SID;2kill掉这个死锁的进程altersystemkill sessionsid,serial#;其中sid=

1.session_id3如果还不能解决，select pro.spid from v$session ses,v$process prowhere ses.sid=XX andses.paddr=pro.addr其中sid用死锁的sid替换exitps-ef|grep spid其中spid是这个进程的进程号，kill掉这个Oracle进程经过以下处理，对于释放效劳器资源,效果非常明显一查询死锁的进程select ALTERSYSTEMKILLSESSION，＞，||b.sid||\||b.serial#||fromv$access a,v$session band b.program likedis%group byb.sid,b.serial#一查询SID对应的操作系统进程select spid,osuser,s.program fromv$sessions,v$process pwherein54,82,500,195,303,492,496一查看操作系统进程ps-ef|more一从操作系统杀掉会话不能杀的进程kill-9操作系统进程号

37.Oracle学习之这里解析一下几个参数的含义:source_char,输入的字符串，可以是列名或者字符串常量、变量lo2o pattern,正则表达式match_parameter,匹酉己选项3o取值范围i大小写不敏感；c大小写敏感；n点号.不匹配换行符号；m多行模式；x扩展模式，忽略正则表达式中的空白字符position,标识从第几个字符开始正则表达式匹配4ooccurrence,标识第几个匹配组5oreplace_string,替换的字符串

608.oracle之decode函数decode函数=java中的trueA:B decode（参数，1,2,3）参数=1时结果为2否则结果为

39.oracle之linux下启动脚本1）启动数据库oracle@suse92:〜〉sqlplus/nologSQL*Plus:Release

9.

2.

0.

4.0-Production onFri Jan2002:29:372006Copyright（c）1982,2002,Oracle Corporation.All rightsreserved.SQL connect/as sysdbaConnectedto anidle instance.SQL startupORACLEinstancestarted.Total SystemGlobal Area135352820bytesFixed Size455156bytesVariable Size109051904bytesDatabase Buffers25165824bytesRedo Buffers679936bytes Databasemounted.2）关闭数据库oracle@suse92:~〉sqlplus/nologSQL*Plus:Release

9.

2.

0.

4.0-Production onFri Jan2002:29:372006Copyright（c）1982,2002,Oracle Corporation.All rightsreserved.SQL connect/as sysdbaConnectedto anidle instance.SQL shutdwonabort;3）启动监听器oracle@suse92:~Isnrctl start4）停止监听器oracle@suse92:~〉Isnrctl stop5）查看监听器状态oracle@suse92:~Isnrctl status或Isnrctl命令进入监听程序后再用status

10.oracle之备份工作背景Oracle10g效劳器，Oracle10g客户端，windowsXP操作平台要求Oracle数据库效劳器对数据库ytcn每天自动备份一次解决方案利用任务方案、批处理文件和Oracle的exp导出功能，根据日期自动生成Oracle备份文件详细步骤ytcn.bat中详细内容如下:@echo offecho正在备份艮通网Oracle数据库，请稍等file-e:/bak/ytcn/oracle/ytcn/ytcn%date:^0,4%%date:^5,2%%date:^8,2%.exp userid二as sysdbadmplog=e:/bak/ytcn/oracle/ytcn/ytcnHodate:^0,4%%date:~5,2%%date:^8,2%.log fu1l=y echo任务完成！其中红色局部是根据需要进行变动的地方，例如作者的工程名“银通网〃，数据库ytcn用户名ytcn,密码ytcn,要在目录:/bak/ytcn/oracle/ytcn/z下生成形如〃ytcn

20090711.dmp〃和〃ytcn2009071L log”的备份和日志文件，全表导出另外％date%的值在不同的系统、语言版本下可能是不一样的，控制面板里面区域选项的设定也会改变%date%的值请先在命令行中测试echo%date%的返回值%date X10%是返回日期函数，〜后的第一个参数是要截取的起始位置（从0开始），第二个参数是要截取的长度，如没有则是截取到最后，参数可酌情修改如需要准确的时间做为文件名，请用％time%函数，参数同上2）添加一个任务方案ytcn开始〉所有程序附件系统工具任务方案添加任务方案〉下一步在浏览中查找刚刚写好的ytcn.bat文件任务名输入ytcn,执行这个任务选择每天，下一步》起始时间下午12:00,起始日期2009-7-11,下一步输入用户名及密码，用户名要求是管理员权限用户名，下一步完成点击〃完成〃之后，会在任务方案栏目下新增一个名为〃ytcn〃的任务方案，说明已经配置完毕备注有时点击〃完成〃之后，系统警告〃已创立新任务，但可能不能运行，因为无法设置账户信,目指定的错误是0x80041315任务方案程序效劳没有运行这是因为电脑的任务方案程序效劳没有启动起来开始所有程序管理工具效劳，找到〃Task Schedule/效劳，发现启动类型为〃已禁用〃，右键单击更改为〃自动〃，并把它启动起来，然后重新添加一次任务方案ytcn就可以了

11.oracle之命中率查询Buffer cache由数据块组成

2.测量Buffer cache的性能测量Buffer cache的命中率SQL select1一physical,value-direct,value-lobs,value/logical,value“Buffer CacheHit Ratio”V$SYSSTAT direct,lobs,logicalV$SYSSTAT二physical reads，V$SYSSTAT physical reads direct，where physical,nameAnd direct,name=from V$SYSSTAT physical,and lobs,name=physicalreadsdirect lobAndlogical,name=session logicalreads，;uBuffer CacheHit Ratio”的值要90%.使用STATSPACK来监视Buffer cache.使用REPORT.TXT来监视Buffer cache.非命中率指标Free BufferInspected、Free BufferWaits BufferBusy Waits.V$sysstat使用Performance Manager数据库例程来监视Buffer Cache.

3.提高缓冲区高速缓存性能的方法加大Buffer Cache的大小init.ora参数DB_CACHE_SIZE动态参数使用Buffer CacheAdvisory功能决定Buffer Cache的大小:首先将init.ora参数DB_CACI1E_ADVICE设成ON,然后查询V$DB_CACIIE_ADVICE.使用多个缓冲区池Keep PoolDB_KEEP_CACHE_SIZERecycle PoolDB_RECYCLE_CACHE_SIZEDefault PoolDB_CACHE_SIZE在内存中缓存表表的CACHE选项，对优化小表的全表扫描正确创立索引

4.调整Large Pool和JAVA POOLLarge Pool用于共享效劳器、RMAN、并行查询、DBWR的附属进程LargePool的大小通过init.ora参数Large_pool_size设置默认为8M.从V$sgastat中监视free memory的值SQLSELECT name,bytes FROMV$sgastat WHEREpool二large pool；JAVA_POOL池的默认大小为32M.对于大型Java应用程序，JAVA_POOL池的大小应大于50M.从V$sgastat中监视free memory的值SQL SELECTname,bytes/1024/1025FROMV$sgastat WHEREpool=java pool;调整重做有关的性能Oracle重做有关的组件包括Redo Log Buffer^Online Redo Log LGWR、Archive LogCheckpoints ArchO.

5.监视Redo LogBuffer的性能Redo LogBuffer不采用LRU（Least RecentlyUsed）算法管理当以下事件发生时，Redo LogBuffer的内容存盘Commit时、每3秒、空间使用1/

3、到达1M、检查点如果写入Redo LogBuffer的速度超过LGWR存盘的速度，就会因等待而降低性能监视Redo LogBuffer的重试率（1%）Select retries,value/entries,value Redo LogBuffer Retry Ratio”From V$sysstat retries,V$sysstat entriesWhereretries,name=redo bufferallocation retriesAndentries.name=*redo entries“Redo LogBufferRetryRatio的值要1%.Select name,value from V$sysstat wherename二）redo logspace requests，如果该值大，需要增加Redo LogBuffer.

6.提高RedoLogBuffer的性能增加RedoLogBuffer的大小init.ora参数log_buffer.减小重做日志的生成量（如果设置表的N0L0GGING属性，以下操作不记录在Online RedoLog中用SQL*Loader的直接路径加载N0L0GGING属性还可用于以下SQL语句CREATE TABLEAS SELECT、CREATE INDEX、ALTER INDEXREBUILD、CREATE TABELSPACE）

7.调整检查点进程的性能测量检查点进程的性能没有完成的检查点进程的次数select*from V$system_event；两个事件checkpoint completed、log fileswitch（checkpoint incomplete）Select*fromV$sysstat.background checkpointsstarted和background checkpointscompleted.使用Alert日志来记录检查点进程init.ora参数log_checkpoint_to_alert.使用Performance Manager来测量检查点进程的性能I/O中的平均灰数据队列长度（如果为0,说明检查点太频繁）建议调整nline redolog的大小，使检查点进程每20-30分钟执行一次

8.调整联机重做日志文件使用V$system_event来监视联机重做日志文件的性能log fileparallel writelog fileswitch completed.调整联机重做日志文件的方法与数据文件、控制文件、归档日志文件分开，放在原始设备上

9.调整归档性能检查归档进程的性能通过V$system_event中的log fileswitch（archiving needed）事件检查每个归档进程的状态V$archive_processes.创立多个归档进程init.ora参数LOG_ARCHIVE_MAX_PROCESSES（默认为

212.oracle之数据库空间限额revoke unlimitedtablespace fromccicjy;alter userccicjy quota0on JINGYOUTBS;alter userccicjy quotaunlimited onJYSPACE;空间转换

13.oracle之查询表空间文件所在路径select*from dba_data_files;

14.oracle之物化视图学习

一、环境数据库1:数据库oralce0RACLE

10.

2.

0.

1.0RAC基表用户WEB USER查询用户MVLOG_USERMVLOG_USER权限如下grant commentany tableto MVLOGUSER;grant createany tableto MVLOGUSER;grant creatematerializedviewto MVLOGUSER;数据库2数据库oralce ORACLE

10.

2.

0.

1.0RAC物化视图用户QUERY_USER网络环境10M光纤目的将数据库1中WEB_USER用户下的表，同步到数据库2的QUERY_USER用户下，为确保数据库1的WEB_USER中数据平安性，通过MVLOGJJSER创立物化视图

二、建立物化视图日志数据库1WEB.USERCREATE MATERIALIZEDVIEW LOGTABLESPACETSJIVLOG;一将对表及表日志查询权限给mvlog_user用户grant selecton WEBUSER.ZFZZB tomvlog user;grant selecton WEBUSER.mlog$ZFZZB tomvlog user;

三、创立物化视图数据库2QUERY_USER

1、创立DBlinkCREATE publicDATABASE LINKTJSB LINKCONNECT TOmvlog userIDENTIFIED BYmvlog userUSING TJDB;

2、创立表create tableZFZZB asselect*from WEBUSER.ZFZZB@TJSB_LINK where1=2;

3、建立物化视图CREATE MATERIALIZEDVIEW ZFZZBon prebuilttableREFRESH FORCEWITH primarykeyON DEMANDASSELECT AAB001,AAC001,AAC002,AAC003,AAE002,AKC087,AKC087S,CAB004,CKC202,CKC205,CKC435,CKC438,CKC442,ZZfrom WEB_USER.ZFZZB@TJSB_LINK

4、全量刷新exec dbmsmview.refresh ZFZZB,Complete;

6、创立主键及索引altertableZFZZBadd constraintPKZFZZB primarykey AAC001,CKC442using indextablespaceTJSB_TS_INDEX;

7、增量刷新exec dbmsmview.refresh JZFZZB，;

15.oracle学习之位置查询select lengtht.a2-lengthreplacet・a2,/lengthfrom zwj1t。