数据库-oracle-学习之路 组卷用的

1、附录资料：不需要的可以自行删除年数据库-oracle-学习之路数据库基础数据库主键生成Select Substr(To_char(dbms_random.value), 2, 22) | getUUID.NextvalFrom dual ;数据库空间查询-表空间在哪些空间下select df.tablespace_name 表空间名,totalspace 总空间M,freespace 剩余空间M,round(1-freespace/totalspace)*100,2) 使用率%from(select tablespace_name,round(sum(bytes)/1024/1024) tot

2、alspacefrom 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

3、_segmentsGROUP BY owner, tablespace_nameORDER BY SUM (BYTES) DESC;-查用户下所有表所占空间select OWNER, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mmmfrom dba_segments twhere t.owner = JIANGSUand t.segment_type=TABLEgroup by OWNER, t.segment_name, t.segment_typeorder by mmm desc;oracle 之删除重复数据se

4、lect 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 )oracle 之查询数据第一条记录select * from tab rownum sqlplus /nologSQL*Plus: Release .0 Pr

5、oduction 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 25

6、165824 bytesRedo Buffers 679936 bytesDatabase mounted.2)关闭数据库： HYPERLINK mailto:oraclesuse92 oraclesuse92: sqlplus /nologSQL*Plus: Release .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.SQ

7、L shutdwon abort;3)启动监听器 HYPERLINK mailto:oraclesuse92 oraclesuse92: lsnrctl start4)停止监听器 HYPERLINK mailto:oraclesuse92 oraclesuse92: lsnrctl stop5)查看监听器状态 HYPERLINK mailto:oraclesuse92 oraclesuse92: lsnrctl status或lsnrctl命令进入监听程序后再用statusoracle 之备份工作背景：Oracle 10g 服务器，Oracle 10g 客户端，windowsXP 操作平台要求

8、：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/ytc

9、n%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% 是返回日期函数，后的

10、第一个参数是要截取的起始位置（从0开始），第二个参数是要截取的长度，如没有则是截取到最后，参数可酌情修改。 如需要准确的时间做为文件名，请用%time%函数，参数同上。 2） 添加一个任务计划 ytcn开始 所有程序 附件 系统工具 任务计划 添加任务计划 下一步 在 浏览 中查找刚刚写好的 ytcn.bat 文件 任务名输入ytcn,执行这个任务选择每天,下一步 起始时间下午12:00,起始日期2009-7-11,下一步 输入用户名及密码,用户名要求是管理员权限用户名,下一步 完成点击完成之后，会在任务计划栏目下新增一个名为ytcn的任务计划，表明已经配置完毕。备注：有时点击完成 之后,系统

11、警告已创建新任务，但可能不能运行，因为无法设置账户信息。指定的错误是：Ox80041315：任务计划程序服务没有运行这是因为电脑的任务计划程序服务没有启动起来。开始 所有程序 管理工具 服务，找到Task Scheduler服务，发现启动类型为已禁用,右键单击更改为自动，并把它启动起来，然后重新添加一次任务计划 ytcn 就可以了。oracle之命中率查询Buffer cache由数据块组成。1. Buffer cache的工作原理LRU列表：MRU 。 LRU.（全表扫描FTS放在LRU端。）缓冲区块的状态：Free、Pinned、Clean、Dirty.Dirty List或Write L

12、ist（写列表）。数据库写进程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 reads And = physical reads d

13、irect and = physical reads direct (lob) And = 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

14、 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 POOLL

15、arge Pool用于共享服务器、RMAN、并行查询、DBWR的从属进程。Large Pool的大小通过init.ora参数Large_pool_size设置。默认为8M.从V$sgastat中监视free memory的值：SQLSELECT 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

16、/ 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的速度超过LGW

17、R存盘的速度，就会因等待而降低性能。监视Redo Log Buffer的重试率（1%）。Select retries.value / entries.value Redo Log Buffer Retry Ratio From V$sysstat retries,V$sysstat entries Where = redo buffer allocation retries And = redo entries“Redo Log Buffer Retry Ratio”的值要 生成数据两大选项：build immediate build deferredBuild immediate:在创建物化

18、视图的同时根据主表生成数据Bulid deferred:在创建物化视图的同时，在物化视图内不生成数据，如果此时没有生成数据，以后可以采取：EXEC DBMS_MVIEW.Refresh(MV_name,C)，注意必须使用全量刷新，默认是增量刷新，所以这里参数必须是C，因为之前都没有生成数据，所以必须全量。2关于刷新 刷新方式：complete fast forceComplete :完全刷新整个物化视图，相当于重新生成物化视图，此时即时增量刷新可用也全量刷新 Fast:当有数据更新时依照相应的规则对物化视图进行更新（此时必须创建物化视图日志（物化视图日志记录了数据更新的日志），关于日志的说明，

19、参照“物化视图日志文件介绍”） Force:当增量刷新可用则增量刷新，当增量刷新不可用，则全量刷新（此项为默认选项）不过从实际情况出发，应该尽量不使用默认选项，可以考虑使用增量刷新，对大表特别有效，大表全量更新速度是非常慢的，特别是在存在索引的情况下（在创建物化视图语句中，可能某些限制查询的条件，导致了增量刷新无法使用，这个是需要注意的，具体是哪类语句导致fast刷新不可用，有待总结.） 刷新时间：on demand on commit start with/ nextOn demand:在需要刷新时进行刷新（人工判断）On commit:在基表上有提交操作时，进行更新Start with:指

20、定首次刷新的时间（一般指定的是当前时间，不过也可以在创建物化视图时不生成数据，则可以考虑在指定的时间刷新，从而生成数据）Next:刷新的周期时间三、 基于主键的物化视图和ROWID的物化视图的说明创建物化视图日志时，指定了记录更新的原则即with 后面的primary 或者rowid 或者object id等等，后面，默认是以primary key为记录更新，在物化视图内也是以此为更新的原则。例如：1、如果日志内使用的是primary key 则在创建物化视图时指定rowid来更新，则会报ORA-12032: 不能使用 TEST 上实体化视图日志中的 rowid 列2、如过日志内使用的是row

21、id 则在创建物化视图时指定primary或者默认指定，则会报ORA-23415: GIS.LZWMV 的实体化视图日志不记录主键四、 关于物化视图存放的的表空间直接在创建物化视图时指定日志存放的表空间和物化视图的表空间。例子：create materialized view MV_TEST tablespace test -表空间名称五、 关于查询重写和更新在创建查询重写时，基表中必须有主键约束，视图里是无法创建主键的，不过其继承了基表的主键约束。（关于视图的创建的一些技巧有待总结）下面给个例子Create materialized view MV_TESTRefresh fast -前提是

22、必须创建基表日志,可以忽略该项Enable query rewrite -前提是基表上必须存在主键约束AsSelect * from TEST; -物化视图数据生成六、 关于创建物化视图的例子：1、 使用增量刷新的物化视图的写法创建物化视图日志，必须创建日志Create materialized view log on TEST -TEST为表名-注：(TEST为表名或者视图名，关于视图上建立物化视图，见基于视图的物化视图-创建物化视图语句：Create materialized view MV_TEST -MVTEST为物化视图名Build immediate -创建时生成数据对应的是bui

23、ld deferredRefresh fast -增量刷新On commit -在基表有更新时提交，这里该句对视图无效With rowid-这里创建基于rowid的物化视图，对应的是 primary keyAsSelect * from TEST;-生成物化视图数据语句oracle学习之linux下启动停止服务 linux下的oracle从10g以后其启动与关闭与以前版本有所不同9i 之后已经没有 svrmgrl 了，所有的管理工作都通过 sqlplus 来完成启动数据库步骤如下:注:$ORACLE_HOME为oracle的安装路径1,以oracle用户登录su oracle2,启动TNS监听

24、器$ORACLE_HOME/bin/ lsnrctl start3,用sqlplus启动数据库$ORACLE_HOME/bin/sqlplus/nologSQL connect system/change_on_install as sysdbaSQL startup出现如下显示，表示Oracle已经成功启动ORACLE instance started.Total System Global Area 205520896 bytesFixed Size 778392 bytesVariable Size 74456936 bytesDatabase Buffers 130023424 byt

25、esRedo Buffers 262144 bytesDatabase mounted.Database opened.4,用sqlplus停止数据库$ORACLE_HOME/bin/sqlplus/nologSQL connect system/change_on_install as sysdbaSQL shutdown注：shutdown可加关闭选项,从最温和到最粗暴的行为选项为(shutdown、shutdown transactional、shutdown immediate、shutdown abort)命令解释如下shutdown:关闭，等待每个用户退出系统戓被取消后退出关闭数据

26、库。shutdown transactional:事务性关闭，等待每个用户提交戓回退当前的事务，然后oracle取消对话，在所有用户退出系统后执行关闭。shutdown immediate:直接关闭，取消所有用户对话（促使回退），执行正常的关闭程序。shutdown abort:终止关闭，关闭数据库时没有自动检查点戓日志开关。出现如下显示,表示oracle已经停止Database closed.Database dismounted.ORACLE instance shut down.oracle学习之rank函数 select * From (select rank() over(parti

27、tion by t.车辆品牌, t.车辆型号 order by to_number(数字) desc, rownum) rr, t.* from t_sj t) tt where tt.rr = 1说明：t.车辆品牌, t.车辆型号 唯一筛选说明：to_number(数字) desc 数值排序说明：tt.rr = 1 取num 第一个oracle学习之JOB初始化相关参数job_queue_processesalter system set job_queue_processes=39 scope=spfile;/最大值不能超过1000 ;job_queue_interval = 10 /调度

28、作业刷新频率秒为单位job_queue_process 表示 HYPERLINK javascript:; t _self oracle能够并发的job的数量，可以通过语句show parameter job_queue_process;来查看oracle中job_queue_process的值。当job_queue_process值为0时表示全部停止oracle的job，可以通过语句ALTER SYSTEM SET job_queue_processes = 10;来调整启动oracle的job。相关视图：dba_jobsall_jobsuser_jobsdba_jobs_running 包

29、含正在运行job相关信息提交job语法：beginsys.dbms_job.submit(job = :job, what = P_CLEAR_PACKBAL;, next_date = to_date(04-08-2008 05:44:09, dd-mm-yyyy hh24:mi:ss), interval = sysdate+ 1/360);commit;end;/创建JOBvariable jobno number;begindbms_job.submit(:jobno, P_CRED_PLAN;,SYSDATE,SYSDATE+1/2880,TRUE);commit;运行JOBSQL

30、begin dbms_job.run(:job1); end; /删除JOBSQL begin dbms_job.remove(:job1); end; /DBA_JOBS字段（列） 类型 描述JOB NUMBER 任务的唯一标示号LOG_USER VARCHAR2(30) 提交任务的用户PRIV_USER VARCHAR2(30) 赋予任务权限的用户SCHEMA_USER VARCHAR2(30) 对任务作语法分析的用户模式LAST_DATE DATE 最后一次成功运行任务的时间LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小时，分钟和秒THI

31、S_DATE DATE 正在运行任务的开始时间，如果没有运行任务则为nullTHIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小时，分钟和秒NEXT_DATE DATE 下一次定时运行任务的时间NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小时，分钟和秒TOTAL_TIME NUMBER 该任务运行所需要的总时间，单位为秒BROKEN VARCHAR2(1) 标志参数，Y标示任务中断，以后不会运行INTERVAL VARCHAR2(200) 用于计算下一运行时间的表达式FAILURES NUMBER 任

32、务运行连续没有成功的次数WHAT VARCHAR2(2000) 执行任务的PL/SQL块CURRENT_SESSION_LABEL RAW MLSLABEL 该任务的信任Oracle会话符CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置MISC_ENV RAW(32) 任务运行的其他一些会话参数描述 INTERVAL参数值每天午夜12点 TRUNC(SYSDATE + 1)每天早上8点30分 TRUNC

33、(SYSDATE + 1) + （8*60+30）/(24*60)每星期二中午12点 NEXT_DAY(TRUNC(SYSDATE ), TUESDAY ) + 12/24每个月第一天的午夜12点 TRUNC(LAST_DAY(SYSDATE ) + 1)每个季度最后一天的晚上11点 TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), Q ) -1/24每星期六和日早上6点10分 TRUNC(LEAST(NEXT_DAY(SYSDATE, SATURDAY), NEXT_DAY(SYSDATE, SUNDAY) + （660+10）/（2460）1:每分钟执行Inter

34、val = TRUNC(sysdate,mi) + 1/ (24*60)2:每天定时执行例如：每天的凌晨1点执行Interval = TRUNC(sysdate) + 1 +1/ (24)3:每周定时执行例如：每周一凌晨1点执行Interval = TRUNC(next_day(sysdate,星期一)+1/244:每月定时执行例如：每月1日凌晨1点执行Interval =TRUNC(LAST_DAY(SYSDATE)+1+1/245:每季度定时执行例如每季度的第一天凌晨1点执行Interval = TRUNC(ADD_MONTHS(SYSDATE,3),Q) + 1/246:每半年定时执行例

35、如：每年7月1日和1月1日凌晨1点Interval = ADD_MONTHS(trunc(sysdate,yyyy),6)+1/247:每年定时执行例如：每年1月1日凌晨1点执行Interval =ADD_MONTHS(trunc(sysdate,yyyy),12)+1/24oracle学习之路索引查询查找表的所有索引（包括索引名，类型，构成列）：select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_n

36、ame查找表的主键（包括名称，构成列）：select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = P排量生成创建脚本（唯一主键）：select alter table | cu.table_name | add constraint | cu.constraint_name | primary key ( | cu.column_name | ); from user_cons_columns

37、cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = P 排量生成创建脚本（所有）select create index | t.index_name | on | t.table_name | ( | t.column_name | ); from user_ind_columns t, user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name排量生成创建

38、脚本（索引-去除主键）select create index | t.index_name | on | t.table_name | ( | t.column_name | ); from user_ind_columns t, user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and not exists (select 1 from user_cons_columns cu, user_constraints au where cu.constraint_name = au.c

39、onstraint_name and au.constraint_type = P and upper(t.index_name) = upper(cu.constraint_name)oracle 学习之路oracle中导入dmp数据库文件 imp username/passwordSID file=XXX.dmp fromuser=XXX touser=XXX tables=(XXX,XXX)其中，fromuser若为多个表空间的话，使用()将其括起来：fromuser=(a,b)；touser参数仿fromuser参数；若只导入一部分表，使用tables参数，用()括起要导入的表；如果想

40、全部导入，不需要指定tables参数补充：1.要新建一个数据库；2.若你的oracle安装在Unix/Linux上，直接在shell中使用imp；如果你的oracle安装在Windows上，随便在哪里开启一个CMD窗口就可以执行imp；3.username/password指的是你的数据库的登录用户名和密码；4.fromuser指对方数据库用户名，touser指你的数据库的用户名；5.使用oracle的管理端在“表空间”中即可创建；6.要导入所有的表最方便，不用写tables参数就成，不需要知道对方的表名。下面介绍的是导入导出的实例。数据的导入1 将D:/daochu.dmp 中的数据导入 T

41、EST数据库中。imp system/managerTEST file=d:/daochu.dmpimp aichannel/aichannelHUST full=y file=file= d:/data/newsmgnt.dmp ignore=y上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。在后面加上 ignore=y 就可以了。2 将d:/daochu.dmp中的表table1 导入imp system/managerTEST file=d:/daochu.dmp tables=(table1)基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入。注意

42、：操作者要有足够的权限，权限不够它会提示。数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。数据导出：1 将数据库TEST完全导出,用户名system 密码manager 导出到D:/daochu.dmp中exp system/managerTEST file=d:/daochu.dmp full=y2 将数据库中system用户与sys用户的表导出exp system/managerTEST file=d:/daochu.dmp owner=(system,sys)3 将数据库中的表inner_notify、notify_staff_relat导出exp ai

43、channel/aichannelTESTDB2 file= d:/data/newsmgnt.dmp tables=(inner_notify,notify_staff_relat) 4 将数据库中的表table1中的字段filed1以00打头的数据导出exp system/managerTEST file=d:/daochu.dmp tables=(table1) query=/ where filed1 like 00%/上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。也可以在上面命令后面 加上 compress=y 来实现。oracle 学习之路数据导入导出ex

44、pDB/impDB一.创建逻辑目录,该命令不会在操作系统创建真正的目录.最好以system等管理员创建.createdirectorydpdata2asD:oraclebackup2;二.查看管理理员目录(同时查看操作系统是否存在,因为Oracle并不关心该目录是否存在,如果不存在,则出错.)select*fromdba_directories;三.给scott用户赋予在指定目录的操作权限,最好以system等管理员赋予.grantread,writeondirectorydpdata2toaaa;impdpaaa/aaaorcldirectory=dpdata2dumpfile=dpdata

45、1.dmpremap_schema=pjbj_chinalife:aaaremap_tablespace=users:userslogfile=impdp_111217.logexpdppjbj_chinalife/pjbj_chinalifeorcldumpfile=pjbj_chinalife.dmpdirectory=dpdata1tables=(jy_md_code_info,jy_md_comm_part,jy_md_figinfo,jy_md_fig_pos,jy_md_model,jy_md_model_config,jy_md_part_type,jy_md_version,j

46、y_pt_data)impdpBBB/BBBorcldirectory=dpdata2dumpfile=pjbj_chinalife.dmpremap_schema=pjbj_chinalife:bbbremap_tablespace=users:JYDB1logfile=impdp_111217.log oracle学习之扩展空间oracle学习之磁盘读写Oracle 学习之物化视图简单介绍Create materialized view mate_view -视图名称Tablespace viewSpace -视图空间Builddeferred -延迟刷新Refresh force -如果

47、可以快速刷新则快速刷新，否则全部刷新Ondemand -按照指定方式刷新Start with to_date（2012-3-27 22:00:00,yyyy-mm-dd hh24:mi:ss） -第一次刷新时间Next TRUNK(sysdate+1)+18/24 -刷新时间间隔As select 查询数据创建方式（build Methods）包括BUILD IMMEDIATE和Build Deferred 两种。Build Immediate 是在创建物化视图的时候就生成数据，而Build Deferred 则在创建时不生成数据，以后根据需要生成数据。默认为Build Immediate。查

48、询重写（QUERY Rewrite）包括ENABLE QUERY REWRITE和Disable Query Rewrite 两种。分别指出创建的物化视图是否支持重写。查询重写是指当对物化视图的基础表进行查询时，Oracle会自动判断是否通过查询物化视图来得到结果，如果可以，则避免了聚集或重新操作，而直接从已经计算好的物化视图中读取数据。默认为Disable Qery Rewrite。刷新（REFRESH）指当基础表发生了DML 操作后，物化视图何时采用哪种方式和基础表进行同步。刷新的模式有2种：ON Demand 和ON Commit 。ON Demand 指物化视图在用户需要的时候进行刷新

49、，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新。ON Commit 指出物化视图在对基础表的DML操作提交的同时进行刷新。刷新的方法有四种：FAST、COMPLETE、FORCE和NEVE。Fast刷新采用增量刷新，只刷新自上次刷新以后的修改。COMPLETE 刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时回去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。默认值是FORCE on DEMAND。快速刷新限制（REFRESH fast）如

50、果需要进行快速刷新，则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要，可以建立在ROWID或PRIMARY KEY类型的。还可以选择在包括SEQUENCE、INCLUDING NEW VALUES 以及制定列的列表。基于物理表的物化视图可以指明ON PREBUILD TABLE 语句将物化视图建立在一个已经存在的表上。这种情况下，物化视图或表必须同名。当删除物化视图时，不会删除同名的表。这种物化视图的查询重写要求参数QUERY_REWRITE_INTEGERITY必须设置为trusted 或者stale_tolerated、REFRESH 子句refreshfast|com

51、plete|forceon demand|commitstart with datenext datewith primary key|rowedRefresh FAST增量刷新用物化视图日志（参照上面所述）来发送主表已经修改的数据行到物化视图中，如果指定Refresh Fast子句，那么应该对主表创建物化视图日志。SQLcreate materialized view log on emp ;Materialized view log created.对于刷新重新生成整个视图，如果请求完全刷新，oracle会完成完全刷新即使增量刷新可用。Refresh Complete 完全刷新重新生成整个

52、视图，如果请求完全刷新，Oracle会完成完全刷新即使增量刷新可用。Refresh Force当指定Force子句，如果增量刷新可用Oracle将完成增量刷新，否则将完成完全刷新，如果不指定刷新方法（Fast、Complete、Force）。Force选项默认选项。Primary key 和rowidWith Primary Key 选项生成主键视图，也就是说物化视图是基于主表的主键，而不是RowId（对应于rowid子句）。Primary Key 是默认选项，为了生成Primary Key 子句，应该在主表上定义主键，否则应该用基于Rowid的物化视图。主键物化视图允许识别物化视图主表而不影

53、响物化视图增量刷新的可用性。Rowid的物化视图知识一个单一的主表，不能包括“Distinct、聚合函数、group by、子查询、连接、SET操作”刷新时间Start with 子句通知数据库完成从主表到本地表第一次复制的时间，应该及时估计下一次运行的时间点，NEXT子句说明了刷新的间隔时间。另外：快速刷新的各类操作：/post/468/14245oracle物化视图的一般用法，物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，

54、并没有实质上的好处。 1、物化视图的类型：ON DEMAND、ON COMMIT 二者的区别在于刷新方法的不同，ON DEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性；而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。 2、ON DEMAND物化视图 物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产 HYPERLINK /database/ t _blank 数据库系统而言。但 HYPERLINK /database/Oracle/ t

55、_blank Oracle允许以这种最简单的，类似于普通视图的方式来做，所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。 物化视图的特点： (1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到佐证； (2) 物化视图也是一种段(segment)，所以其有自己的物理存储属性； (3) 物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证； 创建语句：create materialized view mv_name as selec

56、t * from table_name 默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。 物化视图的数据怎么随着基表而更新？Oracle提供了两种方式，手工刷新和自动刷新，默认为手工刷新。也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包，加以实现。 ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别，即前者不刷新(手工或自动)就不更新物化视图，而后者不刷新也会更新物化视图

57、，只要基表发生了COMMIT。 创建定时刷新的物化视图：create materialized view mv_name refresh force on demand start with sysdate next sysdate+1 （指定物化视图每天刷新一次） 上述创建的物化视图每天刷新，但是没有指定刷新时间，如果要指定刷新时间（比如每天晚上10：00定时刷新一次）：create materialized view mv_name refresh force on demand start with sysdate next to_date( concat( to_char( sysda

58、te+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 materialized view mv_name refresh force on commit as select * from table_name 备注：实际创建过程中，

59、基表需要有主键约束，否则会报错（ORA-12014） 4、物化视图的刷新 刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。（如上所述） 刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。 对于已经创

60、建好的物化视图，可以修改其刷新方式，比如把物化视图mv_name的刷新方式修改为每天晚上10点刷新一次：alter materialized view mv_name refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,dd-mm-yyyy), 22:00:00),dd-mm-yyyy hh24:mi:ss) 5、物化视图具有表一样的特征，所以可以像对表一样，我们可以为它创建索引，创建方法和对表一样。 6、物化视图的删除： 虽然物化视图是和表一起管理的，但是在经常使用的PLSQL工具中