oracle笔记(个人日常应用记录)

1 查看最占资源的进程执行的查看最占资源的进程执行的 sql bin bash ps e o pcpu o pid o user o args grep ora sort k 1 tail 5r spid ps e o pcpu o pid o user o args grep ora sort k 1 tail 5r awk print 2 for i in spid do sqlplus S nolog sqlplus nolog SQL conn as sysdba SQL D oracle product 10 2 0 db 2 RDBMS ADMIN dbmslm sql SQL D oracle product 10 2 0 db 2 RDBMS ADMIN dbmslmd sql SQL D oracle product 10 2 0 db 2 RDBMS ADMIN dbmslms sql SQL show parameter utl 2 创建数据字典 SQL alter system set utl file dir d oracle logmnr scope both SQL EXECUTE dbms logmnr d build dictionary ora d oracle logmnr 3 添加日志文件 SQL EXECUTE dbms logmnr add logfile LogFileName D 1 15969 dbf Options dbms log mnr new SQL EXECUTE dbms logmnr add logfile LogFileName D 1 15969 dbf Options dbms log mnr addfile 或 SQL begin sys dbms logmnr add logfile LogFileName D 1 15969 dbf options dbms logmnr addfile end 4 使用字典分析日志文件 SQL execute dbms logmnr start logmnr dictfilename d oracle logmnr dictionary ora 5 查询结果 SQL select scn sql redo from v logmnr contents 6 退出 logmnr SQL execute dbms logmnr end logmnr PS 这里最重要的是第 5 步 如果结果集很大的话建议使用 PL SQL 等工具进行操作 这样便 于后期修改 相对于 SQLPLUS 的格式化输出命令来说使用 PL SQL DEVELOPER 确实能方 便很多 10 Oracle10g undo 表空间不释放问题表空间不释放问题 Oracle10g 的自动 undo retention 调整特性 Oracle10g 中引入了一个新的自动调整 undo retention 的特性 本意是为了尽量避免 ora 01555 错误 但是自动的东西 有时候会不可避免的聪明过头 这个特性容易导致 undo 表空间过度使用无法回收 在 Oracle10gR2 中只要使用了自动 undo 表空间管理 不管设置 undo retention 为多少 自动 undo retention 特性都会启用 这时 MMON 进程每隔 30 秒会根据 maxquerylen 计算出 一个 tuned undo retention 然后将系统的 undo retention 设置为该值 如果 undo tablespace 的 datafile 是不能自动扩展的话 可能触发 bug 5387030 tuned undo retenttion 会变得非常大 导致 undo 表空间长时间无法回收空间 通过以下查询可以查看 tuned undo retention 的值 select tuned undoretention maxquerylen maxqueryid from v undostat 解决方案有以下三种 1 10 2 0 2 10 2 0 3 有相应的 patch 这个 bug 在 10 2 0 4 中已经修复 建议找时间 停机打 patch 2 设置隐含参数 smu debug mode 33554432 将 tuned undoretention 取值算法修 正为 max maxquerylen secs 300 undo retention 不建议使用 3 设置隐含参数 undo autotune false 关闭自动 undo retention 调整特性 不建议 使用 参考 Note 461480 1 FAQ Automatic Undo Management AUM System Managed Undo SMU Note 240746 1 10g NEW FEATURE on AUTOMATIC UNDO RETENTION Bug 5387030 Automatic tuning of undo retention causes unusual extra space allocation 11 查看表空间使用情况查看表空间使用情况 查询一般表空间 SELECT a tablespace name 表空间名 total 表空间大小 free 表空间剩余大小 total free 表空间使用大小 ROUND total free total 4 100 使用率 FROM SELECT tablespace name SUM bytes free FROM DBA FREE SPACE GROUP BY tablespace name a SELECT tablespace name SUM bytes total FROM DBA DATA FILES GROUP BY tablespace name b WHERE a tablespace name b tablespace name and a tablespace name 查询的表空间名称 查询临时表空间 SELECT a tablespace name 表空间名 total 表空间大小 total used 表空间剩余大小 used 表空间使用大小 ROUND used total 4 100 使用率 FROM SELECT tablespace name SUM bytes used used FROM v temp space header GROUP BY tablespace name a SELECT tablespace name SUM bytes total FROM dba temp files GROUP BY tablespace name b WHERE a tablespace name b tablespace name and a tablespace name 查询的临时表空间名称 12 表空间改名表空间改名 alter tablespace NIMDBS rename to IRMS 13 修改默认和临时表空间修改默认和临时表空间 ALTER USER username DEFAULT TABLESPACE OtherUserTableSpaceName ALTER USER username TEMPORARY TABLESPACE OtherTempTableSpaceName 14 增加和删除权限增加和删除权限 增加权限 GRANT Connect TO username GRANT Resource TO username GRANT Unlimited Tablespace TO username 删除权限 REVOKE Unlimited Tablespace FROM username REVOKE Connect FROM username REVOKE Resource FROM username 增加对象权限 GRANT INSERT DELETE UPDATE SELECT ALTER INDEX REFERENCES ON TableName TO username 删除对象权限 REVOKE INSERT DELETE UPDATE SELECT ALTER INDEX REFERENCES ON TableName FROM username 对象权限详细 TABLE INSERT DELETE UPDATE SELECT ALTER INDEX REFERENCES VIEW INSERT DELETE UPDATE SELECT PACKAGES EXECUTE SYNONYMS INSERT DELETE UPDATE SELECT ALTER INDEX REFERENCES SEQUENCES ALTER SELECT TYPE EXECUTE FUNCTION EXECUTE PROCEDURES EXECUTE QUEUES ENQUEUE DEQUEUE SNAPSHOTS SELECT 15 查看用户权限查看用户权限 1 查看所有用户 select from dba users select from all users select from user users 2 查看用户或角色系统权限 直接赋值给用户或角色的系统权限 select from dba sys privs select from user sys privs 3 查看角色 只能查看登陆用户拥有的角色 所包含的权限 select from role sys privs 4 查看用户对象权限 select from dba tab privs select from all tab privs select from user tab privs 5 查看所有角色 select from dba roles 6 查看用户或角色所拥有的角色 select from dba role privs select from user role privs 7 查看哪些用户有 sysdba 或 sysoper 系统权限 查询时需要相应权限 select from V PWFILE USERS 16 创建创建 job ORACLE JOB 间隔时间参考 关键字 oracle job 间隔时间 trunc 假设你的存储过程名为 PROC RAIN JM 再写一个存储过程名为 PROC JOB RAIN JM 内容是 Java 代码 1 Create Or Replace Procedure PROC JOB RAIN JM 2 Is 3 li jobno Number 4 5 Begin 6 DBMS JOB SUBMIT li jobno PROC RAIN JM SYSDATE TRUNC SYSDATE 1 7 End 提交 job 语法 Java 代码 1 begin 2 sys dbms job submit job job 3 what P CLEAR PACKBAL 4 next date to date 04 08 2008 05 44 09 dd mm yyyy hh24 mi ss 5 interval sysdate 1 360 mit 7 end 创建 JOB Java 代码 1 variable jobno number 2 begin 3 dbms job submit jobno P CRED PLAN SYSDATE SYSDATE 1 2880 TRUE mit 运行 JOB Java 代码 1 begin 2 dbms job run job1 3 end 删除 JOB Java 代码 1 begin 2 dbms job remove job1 3 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 日期的小时 分钟和秒 THIS DATE DATE 正在运行任务的开始时间 如果没有运行任务则为 null THIS 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 任务运行连续没有成功的次数 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 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 6 60 10 24 60 1 每分钟执行 Interval TRUNC sysdate mi 1 24 60 2 每天定时执行 例如 每天的凌晨 1 点执行 Interval TRUNC sysdate 1 1 24 3 每周定时执行 例如 每周一凌晨 1 点执行 Interval TRUNC next day sysdate 星期一 1 24 4 每月定时执行 例如 每月 1 日凌晨 1 点执行 Interval TRUNC LAST DAY SYSDATE 1 1 24 5 每季度定时执行 例如每季度的第一天凌晨 1 点执行 Interval TRUNC ADD MONTHS SYSDATE 3 Q 1 24 6 每半年定时执行 例如 每年 7 月 1 日和 1 月 1 日凌晨 1 点 Interval ADD MONTHS trunc sysdate yyyy 6 1 24 7 每年定时执行 例如 每年 1 月 1 日凌晨 1 点执行 Interval ADD MONTHS trunc sysdate yyyy 12 1 24 TRUNC 函数分两种函数分两种 trunc 实际上是 truncate 函数 字面意思是截断 截尾 1 TRUNC for dates TRUNC 函数为指定元素而截去的日期值 截尾后返回最近日期 其具体的语法格式如下 TRUNC date fmt date 一个日期值 fmt 日期格式 该日期将由指定的元素格式所截去 忽略它则由最近的日期截去 下面是该函数的使用情况 select trunc sysdate mm from dual 2009 1 1 2 TRUNC for number TRUNC 函数的功能是将数字进行截断 对所截取的结果并不四舍五入 其具体的语法格式如下 TRUNC number decimals number 待做截取处理的数值 decimals 指明需保留小数点后面的位数 可选项 忽略它则截去所有的小数部分 下面是该函数的使用情况 TRUNC 89 985 2 89 98 TRUNC 89 985 89 TRUNC 89 985 1 80 注意 第二个参数可以为负数 表示为小数点左边指定位数后面的部分截去 即均以 0 记 17 查看表的碎片查看表的碎片 ANALYZE TABLE objects COMPUTE STATISTICS ELECT blocks empty blocks num rows FROM user tables WHERE table name OBJECTS BLOCKS 列代表该表中曾经使用过得数据库块的数目 即水线 EMPTY BLOCKS 代表分配给该表 但是在水线以上的数据库块 即从来没有使用 的数据块 18 oracle 10g 中开启中开启 flashback 功能 包括开启归档功功能 包括开启归档功 能 能 10g 中开启 flashback 功能需要在归档模式下才行 看来我还是一只游啊游的小 虾米 要成长为一只大虾米还有好多要学啊 设置过程如下 1 开启归档功能 使数据库处于归档模式下 先关闭数据库 shutdown immediate 启动到 mount 状态 startup mount 开启归档功能 alter database archivelog 打开数据库 alter database open 2 开启闪回 flashback 功能 归档下才可开启数据库闪回 查看 flashback 是否开启 select name flashback on from v database 查看闪回恢复区及大小是否设置 show parameter db recovery 先设置闪回恢复区的大小 alter system set db recovery file dest size 2G 再设置闪回恢复区路径 alter system set db recovery file dest E oracle product 10 2 0 db recovery file dest 设置数据库回退的时间 默认 1440 分钟为一天时间 alter system set db flashback retention target 1440 启动到 mount 状态 startup mount 开启 flashback 功能 alter database flashback on 打开数据库 alter database open 19 关于关于 v sqlarea v sql v sqltext 通过看这个表的结构 可以了解共享池中 SQL 是如何运行的 这样可以了解不少关键的信息 可以了解 SQL 全文 执行使用的内存 消耗的 CPU 被装载的次数等等比较有意义的信息 通过这个视图 可以了解最近被执行最多次数的 SQL 语句 被最多用户执行的 SQL 语句 当然共享 池要足够大 设计的语句也应该尽量考虑到共享 例如一个很简单的就是多考虑变量绑定 研究 这个对于了解性能还是有一定作用的 SQL TEXTVARCHAR2 1000 SQL 千一千个字符内容 SQL FULLTEXTCLOB SQL 全文 SQL IDVARCHAR2 13 SQL identifier of the parent cursor in the library cache SHARABLE MEMNUMBER 使用了多少共享内存 包含子游标所使用的 PERSISTENT MEMNUMBER 打开的游标其生命周期所消耗的固定内存 包含 子有效的固定内存 RUNTIME MEMNUMBER 游标运行时候所需要的内容 包含子游标的 SORTSNUMBER 所有的排序测试 包含子游标的 VERSION COUNTNUMBERNumber of child cursors that are present in the cache under this parent LOADED VERSIONSNUMBERNumber of child cursors that are present in the cache and have their context heap KGL heap 6 loaded OPEN VERSIONSNUMBER The number of child cursors that are currently open under this current parent USERS OPENINGNUMBER 共有多少用户打开它 包含子游标 FETCHES NUMBERNumberNumberof fetches associated with the SQL statement EXECUTIONSNUMBER 总共执行次数 包含子游标 PX SERVERS EXECUTIONS NUMBERTotal number of executions performed by Parallel eXecution Servers The value is 0 when the statement has never been executed in parallel END OF FETCH COUNTNUMBER 完整执行的次数 通常小于总共执行次数 Number of times this cursor was fully executed since the cursor was brought into the library cache The value of this statistic is not incremented when the cursor is partially executed either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re executed By definition the value of the END OF FETCH COUNT column should be less or equal to the value of the EXECUTIONS column USERS EXECUTINGNUMBER 共多少个用户执行 表示包含子游标 下同 LOADSNUMBER 对象被装载或者重载的次数 FIRST LOAD TIMEVARCHAR2 19 第一次装载的时间 INVALIDATIONSNUMBERTotal number of invalidations over all the child cursors PARSE CALLSNUMBERSum of all parse calls to all the child cursors under this parent 统共分析的次数 DISK READSNUMBERSum of the number of disk reads over all child cursors DIRECT WRITESNUMBERSum of the number of direct writes over all child cursors BUFFER GETSNUMBERSum of buffer gets over all child cursors APPLICATION WAIT TIME NUMBERApplication wait time in microseconds CONCURRENCY WAIT TIME NUMBERConcurrency wait time in microseconds CLUSTER WAIT TIMENUMBERCluster wait time in microseconds USER IO WAIT TIMENUMBERUser I O Wait Time in microseconds PLSQL EXEC TIMENUMBERPL SQL execution time in microseconds JAVA EXEC TIMENUMBERJava execution time in microseconds ROWS PROCESSEDNUMBER 处理了多少条记录 COMMAND TYPENUMBEROracle command type definition OPTIMIZER MODEVARCHAR2 10 Mode under which the SQL statement was executed OPTIMIZER COSTNUMBERCost of this query given by the optimizer OPTIMIZER ENVRAW 703 Optimizer environment OPTIMIZER ENV HASH VALUE NUMBERHash value for the optimizer environmen PARSING USER IDNUMBERUser ID of the user that has parsed the very first cursor under this parent PARSING SCHEMA ID NUMBERSchema ID that was used to parse this child cursor PARSING SCHEMA NAME VARCHAR2 30 Schema name that was used to parse this child cursor KEPT VERSIONSNUMBERNumber of child cursors that have been marked to be kept using the DBMS SHARED POOL package ADDRESSRAW 4 8 Address of the handle to the parent for this cursor HASH VALUENUMBERHash value of the parent statement in the library cache OLD HASH VALUENUMBEROld SQL hash value PLAN HASH VALUENUMBERNumerical representation of the SQL plan for this cursor Comparing one PLAN HASH VALUE to another easily identifies whether or not two plans are the same rather than comparing the two plans line by line MODULEVARCHAR2 64 Contains the name of the module that was executing at the time that the SQL statement was first parsed as set by calling DBMS APPLICATION INFO SET MODULE MODULE HASHNUMBERHash value of the module that is named in the MODULE column ACTIONVARCHAR2 64 Contains the name of the action that was executing at the time that the SQL statement was first parsed as set by calling DBMS APPLICATION INFO SET ACTION ACTION HASHNUMBERHash value of the action that is named in the ACTION column SERIALIZABLE ABORTS NUMBERNumber of times the transaction fails to serialize producing ORA 08177 errors totalled over all the child cursors OUTLINE CATEGORYVARCHAR2 64 If an outline was applied during construction of the cursor then this column displays the category of that outline Otherwise the column is left blank CPU TIMENUMBER 分析 执行和提取所消耗的时间 单位为微秒 CPU time in microseconds used by this cursor for parsing executing and fetching ELAPSED TIMENUMBER 客观上分析 执行和提取所消耗的时间 单位为微 秒 这个时间通常比 CPU TIME 长 因为可能还包含其 它时间 OUTLINE SIDVARCHAR2 40 Outline session identifier LAST ACTIVE CHILD ADDRESS RAW 4 Address identifier of the child cursor that was the last to be active in the group that is the child cursor on behalf of which statistics in V V SQL were updated REMOTEVARCHAR2 1 Indicates whether the cursor is remote mapped Y or not N OBJECT STATUSVARCHAR2 19 Status of the cursor VALID Valid authorized without errors VALID AUTH ERROR Valid authorized with authorization errors VALID COMPILE ERROR Valid authorized with compilation errors VALID UNAUTH Valid unauthorized INVALID UNAUTH Invalid unauthorized INVALID Invalid unauthorized but keep the timestamp LITERAL HASH VALUE NUMBERHash value of the literals which are replaced with system generated bind variables and are to be matched when CURSOR SHARING is used This i not the hash value for the SQL statement If CURSOR SHARING is no used then the value is 0 LAST LOAD TIMEDATE 执行计划最后被此装载的时间 Time at which the query plan heap 6 was loaded into the library cache IS OBSOLETEVARCHAR2 1 Indicates whether the cursor has become obsolete Y or not N happen if the number of child cursors is too large CHILD LATCHNUMBER Child latch number that is protecting the cursor SQL PROFILEVARCHAR2 64 SQL profile PROGRAM IDPROGRAM IDNUMBERProgram identifier PROGRAM LINE PROGRAM LINE NUMBERProgram line number EXACT MATCHING SIGNATURe NUMBERThe signature used when the CURSOR SHARING parameter is set to EXACT FORCE MATCHING SIGNATURE NUMBERThe signature used when the CURSOR SHARING parameter is set to FORCE LAST ACTIVE TIMEDATE 最后换一次查询计划被激活的时间 Time at which the query plan was last active BIND DATARAW 2000 Bind data 其中 PROGRAM ID PROGRAM LINE 可以用于定位是那个程序 通常指过程 函数或者是其它包裹 等 可以用于跟踪 SELECT T USER NAME e SQL FULLTEXT E PROGRAM ID E PROGRAM LINE X OBJECT NAME FROM V V OPEN CURSOR T V V SESSION D v v sqlareasqlarea e JOIN ALL OBJECTS X ON X OBJECT ID E PROGRAM ID WHERE T SID D SID and t SQL ID e SQL ID AND T USER NAME IN DM WH IPD MK 关于共同点 1 都存储了 sql 内容 2 记录的都是位于内存中的 sql 内容 3 因为是内存 所以都不保