《数据库oracle学习之路.docx》由会员分享，可在线阅读，更多相关《数据库oracle学习之路.docx（39页珍藏版）》请在课桌文档上搜索。
1、数据库oracle学习之路1 .数据库基础2 .数据库主键生成SelectSubstr(To_char(dbms_random.value),2,22)IIgetUUID.NextvalFromdual;3 .数据库空间查询表空间在什么空间下selectdf.tablespace_name表空间名，totalspace总空间M,freespace”剩余空间M,zround(l-freespacetotalspace)*100,2)from(selecttablespace-name,rond(sum(bytes)10241024)totalspacefromdba_data_filesgrou
2、pbytablespace_name)df,(selecttablespace-name,rond(sum(bytes)10241024)freespacefromdba_free_spacegroupbytablespace_name)fswheredf.tablespace_name=fs.tablespace_name;一查用户下所用空间SELECTowner,tablespace,namezROUND(SUM(BYTES)/1024/1024,2)USED(M)FROMdba_segmentsGROUPBYowner,tablespace_nameORDERBYSUM(BYTES)DE
3、SC;-查用户下所有表所占空间selectOWNER,t.segment_name,t.segment-typ/sum(t.bytes/1024/1024)mmmfromdba_segmentstwheret.owner=JIANGSU,andt.segment-type=TABLE,groupbyOWNER,t.segment_name,t.segment_typeorderbymmmdesc;4. oracle之删除重复数据selecta.rowid,a.*from表名awherea.rowid!=(selectmax(b.rowid)from表名bwherea.字段1=b.字段1anda
4、.字段2=b.字段2)一删除deletefrom表名awherea.rowid!=(selectmax(b.rowid)from表名bwherea.字段1=b.字段1anda.字段2=b.字段2)5. oracle之查询数据第一条记录select*fromtabrownum.regexp_substr函数，与substr类似，用于拾取合符正则表达式描述的字符子串，语法如下：REGEXPSUBSTRIJroecrregexpjnstr函数，与instr类似，用于标定符合正则表达式的字符子串的开始位置，语法如下：T REGEXPNSTR f)our8/仙*patterDregexp_replace
5、函数，与replace类似，用于替换符合正则表达式的字符串，语法如下:REGEXPREPIACEPattemAegexp-replace FZrGTXepb8.String)2rsqlplsZnologSQL*Plus:Release9.2.0.4.0-ProductiononFriJan2002:29:372006Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.SQLconnect/assysdbaConnectedtoanidleinstance.SQLstartupORACLEinstancestarted.TotalS
6、ystemGlobalArea135352820bytesFixedSize455156bytesVariableSize109051904bytesDatabaseBuffers25165824bytesRedoBuffers679936bytesDatabasemounted.2)关闭数据库：OraeIeSLlSe92sqlplusnologSQL*Plus:Release9.2.0.4.0-ProductiononFriJan2002:29:372006Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.SQLconnect
7、/assysdbaConnectedtoanidleinstance.SQLShutdwonabort;3）启动监听器OraCIeSUSe92:Isnrctlstart4）停止监听器OraCIeSlJSe92Isnrctlstop5）查看监听器状态OraCIeSUSe92Isnrctlstatus或者ISnretl命令进入监听程序后再用status10. oracle之备份工作背景：OraCleIOg服务器，OraCIeIOg客户端，WindowsXP操作平台要求：Oracle数据库服务器对数据库ytcn每天自动备份一次。解决方案：利用任务计划、批处理文件与Oracle的exp导出功能，根据日
8、期自动生成Oracle备份文件。全面步骤：1）创建批处理文件ytcn.batytcn.bat中全面内容如下：echooffecho正在备份Oracle数据库，清稍等expuserid=cnytcn11ytcnassysdbafile=mi|HHdate:0,4%date:5,2%date:8,2%.dmpecho任务完成！其中红色部分是根据需要进行变动的地方，比如作者的项目名“银通网”，数据库ytcn用户名ytcn,密码ytcn,要在目录,ebakytcnoracleytcn,下生成形如ytcn200907ILdmp与ytcn20090711.log-的备份与日志文件，全表导出。另外：%dat
9、e%的值在不一致的系统、语言版本下可能是不一样的，操纵面板里面区域选项的设定也会改变date%的值。请先在命令行中测试echo%date%的返回值。%date-4,10%是返回日期函数，后的第一个参数是要截取的起始位置（从0开始），第二个参数是要截取的长度，如没有则是截取到最后，参数可酌情修改。如需要准确的时间做为文件名，请用time%函数，参数同上。2）添加一个任务计划ytcn开始所有程序附件系统工具任务计划添加任务计划下一步在浏览中查找刚刚写好的ytcn.bat文件任务名输入ytcn,执行这个任务选择每天,下一步起始时间下午12:00,起始日期2009711,下一步输入用户名及密码,用户名
10、要求是管理员权限用户名,下一步完成点击“完成”之后，会在任务计划栏目下新增一个名为“ytcn”的任务计划，说明已经配置完毕。备注：有的时候点击“完成”之后,系统警告指定的错误是：bx8004l:这是由于电脑的任务计划程序服务没有启动起来。开始所有程序管理工具服务，找到“TaSkSChedUIer”服务，发现启动类型为“已禁用”,右键单击更换为“自动”，并把它启动起来，然后重新添加一次任务计划ytcn就能够了。11. oracle之命中率查询Buffercache由数据块构成。1 .Buffercache的工作原理LRU列表：MRULRU,（全表扫描FTS放在LRU端。）缓冲区块的状态：Free
11、.Pinned、Clean、Dirty.DirtyLiSt或者WriteList（写列表）。数据库写进程DBWO将缓冲区高速缓存中的数据写到数据文件中。2 .测量Buffercache的性能测量Buffercache的命中率：SQLselect1-(physical.value-direct.value-lobs.value)/logical.value)BufferCacheHitRatio*physical, direct, lobs, logicalfromV$SYSSTATV$SYSSTATV$SYSSTATV$SYSSTATwherephysical.name=,physicalre
12、ads,Anddirect.name=physicalreadsdirect,andlobs.name=physicalreadsdirect(Iob)Andlogical.name=,sessionlogicalreads,;BufferCacheHitRatio的值要90%.使用STATSPACK来监视Buffercache.使用REPORT.TXT来监视Buffercache.非命中率指标：FreeBufferInspectedFreeBufferWaits、BufferBusyWaits.(V$sysstat)使用PerformanceManager(数据库例程)来监视BufferCa
13、che.3 .提高缓冲区高速缓存性能的方法加大BUfferCaChe的大小：init.ora参数DB_CACHE_SIZE(动态参数)。使用BufferCacheAdvisory功能决定BufferCache的大小：首先将initora参数DB_CACHE_ADVICE设成ON,然后查询V$DB_CACHE_ADVICE.使用多个缓冲区池：KeepPool：DB_KEEP_CACHE_SIZERecyclePLDB_RECYCLE_CACHE_SIZEDefaultPool：DBCACHESIZE在内存中缓存表：表的CACHE选项，对优化小表的全表扫描。正确创建索引。4调整LargePool与
14、JAVAPOOL1.argePool用于共享服务器、RMAN、并行查询、DBWR的从属进程。1.argePl的大小通过init.ora参数Large_pool_size设置。默认为8M.从V$SgaStat中监视freememory的值：SQLSELECTname,bytesFROMV$sgastatWHEREpool=largePooI；JAVA.POOL池的默认大小为32M.关于大型Java应用程序，JAVA_POOL池的大小应大于50M.init.ora参数java_pool_size从VJsgastat中监视freememory的值。SQLSELECTname,bytes/1024/1
15、025FRoMU$SgaStatWHEREpool=,javapool;调整重做有关的性能Oracle重做有关的组件包含：RedoLogBufferOnlineRedoLogLGWRArchiveLogCheckpointsArchO.4 .监视RedoLogBuffer的性能RedoLogBuffer不使用LRU（LeastRecentlyUsed）算法管理。当下列事件发生时，RedoLogBuffer的内容存盘：Commit时、每3秒、空间使用1/3、达到1M、检查点。假如写入RedoLogBuffer的速度超过LGWR存盘的速度，就会因等待而降低性能。监视RedoLogBuffer的重试
16、率（V1%）。Selectretries.value/entries.valueRedoLogBufferRetryRatioFromVSsysstatretries,VSsysstatentriesWhereretries.name=,redobufferallocationretries,Andentries.name=,redoentriesuRedoLogBufferRetryRati的值要connectsystem/change_on_instal1assysdbaSQLstartup出现如下显示，表示OraCle已经成功启动ORACLEinstancestarted.TotalSy
17、stemGlobalArea205520896bytesFixed SizeVariable SizeDatabase BuffersRedo BuffersDatabase mounted.Database opened.4,用sqlplus停止数据库778392 bytes74456936 bytes130023424 bytes262144 bytes$ORACLE_HOME/bin/sqlplusnologSQLconnectsystem/change_on_instal1assysdbaSQLshutdown注：shutdown可加关闭选项，从最温与到最粗暴的行为选项为(ShUtdO
18、Wn、shutdowntransactionalshutdownimmediateshutdownabort)命令解释如下ShUtdOWn:关闭，等待每个用户退出系统或被取消后退出关闭数据库。ShUtdoWntranSaetiona1:事务性关闭，等待每个用户提交或回退当前的事务，然后OraCle取消对话，在所有用户退出系统后执行关闭。shutdownimmediate:直接关闭，取消所有用户对话(促使回退)，执行正常的关闭程序。shutdownabort:终止关闭，关闭数据库时没有自动检查点或日志开关。出现如下显示,表示oracle已经停止Databaseclosed.Databasedis
19、mounted.ORACLEinstanceshutdown.18. oracle学习之rank函数select*From(selectrankOover(partitionbyt.车辆品牌，t.车辆型号orderbyto_number(数字)desc,rownum)rr,t.*fromt_sjt)ttwherett.rr=1说明：t.车辆品牌，t.车辆型号唯一筛选说明：to_numbcr(数字)desc数值排序说明：tt.rr=1取num第一个19. OraCIe学习之JOB初始化有关参数job_queue_processesaltersystemsetjob_queue_processes
20、=39SCoPe=SPfiIeJ/最大值不能超过IOoo;job_queueJnterval=10调度作业刷新频率秒为单位job_queue_process表东OraCle能够并发的iob的数量,能够通过语句showparameterjob_queue_process;来查看OraCle中job_queue_PrOCeSS的值。当job_queue_ProCeSS值为0时表示全部停止Oraele的job.能够通过语句ALTERSYSTEMSETjob_queue_processes=10;来调整启动oracle的job.有关视图：dbajobsallJobsuserjobsdbajobs_ru
21、nning包含正在运行job有关信息提交job语法:beginsys.dbmsjob.submit(job=:job,what=P_CLEAFLPACKBAL;,next_date=to_date(04-08-200805:44:09,dd-mm-yyyyhh24:mi:ss),interval=sysdate+1360);commit;end;创建JOBvariablejobnonumber;begindbmsjob.submit(:jobno,P-CRED-PLANj,SYSDATE,SYSDATE+128801,TRE);commit;运行Je)BSQLbegindbmsjob.run(:job1);end;删
