数据库-oracle-学习之路.docx

《数据库-oracle-学习之路.docx》由会员分享，可在线阅读，更多相关《数据库-oracle-学习之路.docx（39页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、年数据库-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) totalspac

2、efrom 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_segme

3、ntsGROUP 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;4. oracle 之删除重复数据selec

4、t 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 sqlplus /nologSQL*Plus: Release 9.2.0

5、.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 Buf

6、fers 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;

7、3)启动监听器oraclesuse92: lsnrctl start4)停止监听器oraclesuse92: lsnrctl stop5)查看监听器状态oraclesuse92: lsnrctl status或lsnrctl命令进入监听程序后再用status10. oracle 之备份工作背景：Oracle 10g 服务器，Oracle 10g 客户端，windowsXP 操作平台要求：Oracle 数据库服务器对数据库 ytcn 每天自动备份一次。解决方案：利用任务计划、批处理文件和 Oracle 的 exp 导出功能，根据日期自动生成 Oracle 备份文件。详细步骤： 1） 创建批处理文

8、件 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，密码 yt

9、cn，要在目录 e:/bak/ytcn/oracle/ytcn 下生成形如 ytcn20090711.dmp 和 ytcn20090711.log 的备份和日志文件，全表导出。另外：%date%的值在不同的系统、语言版本下可能是不一样的，控制面板里面区域选项的设定也会改变%date%的值。请先在命令行中测试 echo %date% 的返回值。%date:4,10% 是返回日期函数，后的第一个参数是要截取的起始位置（从0开始），第二个参数是要截取的长度，如没有则是截取到最后，参数可酌情修改。 如需要准确的时间做为文件名，请用%time%函数，参数同上。 2） 添加一个任务计划 ytcn开始 所有

10、程序 附件 系统工具 任务计划 添加任务计划 下一步 在 浏览 中查找刚刚写好的 ytcn.bat 文件 任务名输入ytcn,执行这个任务选择每天,下一步 起始时间下午12:00,起始日期2009-7-11,下一步 输入用户名及密码,用户名要求是管理员权限用户名,下一步 完成点击完成之后，会在任务计划栏目下新增一个名为ytcn的任务计划，表明已经配置完毕。备注：有时点击完成 之后,系统警告已创建新任务，但可能不能运行，因为无法设置账户信息。指定的错误是：Ox80041315：任务计划程序服务没有运行这是因为电脑的任务计划程序服务没有启动起来。开始 所有程序 管理工具 服务，找到Task Sch

11、eduler服务，发现启动类型为已禁用,右键单击更改为自动，并把它启动起来，然后重新添加一次任务计划 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

12、.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

13、 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 Cach

14、e 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的大小通过i

15、nit.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 / 1024 / 1025 FROM V$sgastat WHERE pool = java pool;

16、调整重做有关的性能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 re

17、tries.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”的值要 生成数据两大选项：build immediate build deferredBuild immediate:在创建物化视图的同时根据主表生成数据Bulid deferred

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

19、可用则增量刷新，当增量刷新不可用，则全量刷新（此项为默认选项）不过从实际情况出发，应该尽量不使用默认选项，可以考虑使用增量刷新，对大表特别有效，大表全量更新速度是非常慢的，特别是在存在索引的情况下（在创建物化视图语句中，可能某些限制查询的条件，导致了增量刷新无法使用，这个是需要注意的，具体是哪类语句导致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、如过日志内使用的是rowid 则在创建物化视图时指定primary或者默认指定

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

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

23、刷新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/