oracle-学习笔记-5

接 oracle-学习笔记-4

-------------------------------------

 

 

16.Oracle 小问题

Oracle Package有哪些作用呢？

简化应用设计、提高应用性能、实现信息隐藏、子程序重载。
 
ORACLE中的function   、package、package   bodies、procedure的有什么区别和相同的地方？

function有返回值，有参数；procedure无返回值，有参数；package、package   body是同时存在的，就像.h和.cpp文件，如果要外部调用的，就在package里声明一下，包内调用的，只要在body里写就行了。package可包括function,procedure

 
问题一：以前在sqlserver中，我直接写个存储过程就可以调用。但是在oracle中，我看好多资料上说要想调用存储过程必须把存储过程放进包中才能调用？是否是这样？ 
  
  不是，过程也可以单独写，单独调用 
  
  问题二：packages   与package   bodies有什么区别？ 
  
  packages中只有各个方法的定义，bodies中涉及具体的实现 
  
  问题三：我在存储过程就是procedures中写了个存储过程，再写个包名包含进去吗？还是可以直接在包中写包名：再把想写的存储过程直接写进包中就行啦呢？ 
  
  不用 
  
  问题四：写进包，要写进哪个？packages   ?还是package   bodies 
  
  这两个是一体的，必须同时存在 
 
package   body和package都需要手工去写。 
  需要先创建package(也就是包的定义)，再创建body。增加包中的过程或者修改包中过程的输入参数个数等也是要先改package再改body。

17.Oracle程序包 简单使用

程序包是对相关过程、函数、变量、游标和异常等对象的封装
程序包由规范和主体两部分组成
(1)    包规范 (package)：
包规范部分声明包内变量，常量，游标，子程序和异常错误处理等元素，这些元素为包的公有元素。语法如下：
CREATE [OR REPLACE]
PACKAGE
package_name IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];
       

(2)    包主体（package body）：

包主体是包规范部分的具体实现，它定义了包定义部分所有声明的游标和子程序等，在包主体中还可以声明包的私有元素。它的语法形式如下：
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
[Private item declarations]
[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];

创建包
例1（包含过程和函数）：
包规范：
create or replace package find_emp
as
procedure find_emp_proc(mes varchar2);
function find_emp_fun(mes varchar2)return varchar2;
end find_emp;
    

      包主体：

       
create or replace package body find_emp
as
procedure find_emp_proc(mes varchar2)
as
begin
dbms_output.put_line(mes);
end find_emp_proc;

function find_emp_fun(mes varchar2)return varchar2
as
begin
  return '你好:'||mes;
end find_emp_fun;
end find_emp;

      在SQL:>提示符下调用包中的过程和函数。
(1)    调用过程
        execute find_emp.find_emp_proc('fuxiange');
(2)    调用函数
   select find_emp.find_emp_fun('fuxiange') from dual;
 
 

17.查看当前用户下指定表大小

col segment_name format a10

select ts.* from
(select segment_name,sum(bytes)/1024/1024 as "SIZE(M)"
from user_extents group by segment_name) ts
where ts.segment_name = '&TABLE_NAME';

18. Oracle系统视图

---dba视图

　　select * from dba_data_files   --指定表空间的数据文件及所在的路径
　　select * from dba_free_space   --指定表空间的剩余空间
　　select * from dba_users        --找出当前数据库实例中的所有用户
　　select * from dba_segments     --找出当前数据库实例中的所有对象的物理信息，如：所占空间、pctincrease等
　　select * from dba_tab_columns  --指定所有表对应的列名
　　select * from dba_col_comments --指定所有列的注释信息
　　select * from dba_tablespaces  --列出所有的表空间及相关信息
　　select * from dba_tab_partitions --所有表分区的信息
　　select * from dba_ind_columns  --显示所有的被索引的列
　　select * from dba_indexes      --显示所有的索引信息
　　select * from dba_jobs        --显示所有的job信息
　　select * from dba_jobs_running --显示正在运行的job信息

---v$视图

　　select * from v$session        --显示当前所有的session信息
　　
    v$lock视图
　　反映内容：该视图展示当前保持的锁信息；
　　名称                                                类型                                      说明部分
　　-----------------------------------------  ----------------------------    ********************************
　　ADDR                                               RAW(4)                      //锁状态对象地址
　　KADDR                                              RAW(4)                      //锁地址
　　SID                                                NUMBER                      //保持锁的会话的会话标识符
　　TYPE                                               VARCHAR2(2)                 //锁类型 TM:dml排队TX:事务排队UL:用户提供
　　ID1                                                NUMBER                      //锁标示1
　　ID2                                                NUMBER                      //锁标示2
　　LMODE                                              NUMBER                      //会话保持的锁的模式 0，1，2，3，4，5，6
　　REQUEST                                            NUMBER                      //进程请求锁定时所处的模式
　　CTIME                                              NUMBER                      //当前的锁模式所消耗的时间
　　BLOCK                                              NUMBER                      //阻塞其他锁

　　0，1，2，3，4，5，6解释：0代表无，1代表空(null)，2代表行(ss)，3代表行(sx)，4共享S，5S/行X(SSX)，6独占；
　　通过这个视图你能初步了解锁的模式，从而判断锁会出现的地方；

　　2. v$PROCESS视图
　　放映内容：包含有关当前活动进程的信息；
　　SQL> desc v$process
　　名称                                               类型                                     说明部分
　　-----------------------------------------  ----------------------------      ********************************
　　ADDR                                               RAW(4)                       //进程对象状态地址
　　PID                                                NUMBER                       //oracle进程标识符，类似于序号
　　SPID                                               VARCHAR2(12)                 //操作系统进程标识符，可以用来和操作系统进程联系
　　USERNAME                                           VARCHAR2(15)                 //操作系统进程用户名
　　SERIAL#                                            NUMBER                       //进程序列号
　　TERMINAL                                           VARCHAR2(16)                 //操作系统终端标示符
　　PROGRAM                                            VARCHAR2(64)                 //进程中的程序
　　TRACEID                                            VARCHAR2(255)                //跟踪文件标识符
　　BACKGROUND                                         VARCHAR2(1)                  //1表示后台进程，null表示其它
　　LATCHWAIT                                          VARCHAR2(8)                  //进程正在等待锁的地址；如果该值为n/a，则锁地址为null
　　LATCHSPIN                                          VARCHAR2(8)                  //进程正在轮循的锁的地址，如果该职位n/a，则为地址nill
　　PGA_USED_MEM                                       NUMBER                       //当前正在使用的pga内存
　　PGA_ALLOC_MEM                                      NUMBER                       //当前已经分配的pga内存
　　PGA_FREEABLE_MEM                                   NUMBER                       //可以释放的已分配的pga内存
　　PGA_MAX_MEM                                        NUMBER                       //曾经需要的最大的最大pga内存

　　这个视图分很有用，可以和操做系统联系，这样用来诊断跟踪顶级系统资源使用很有益处。这里就可以有个案例，经常用到的，通过消耗资源做大的系统进程号的到执行的sql语句，需要结合v$session视图一起来完成；

　　3.v$session
　　反映内容：列出连接到实例的会话，这个视图有非常多的信息字段也比较多。
　　SQL> desc v$session
　　名称                                               类型
　　----------------------------------------- ------------
　　SADDR                                          RAW(4)                    session地址
　　SID                                            NUMBER                    session标识符
　　SERIAL#                                        NUMBER                    session会话序列号
　　AUDSID                                         NUMBER                    审计的会话id
　　PADDR                                          RAW(4)                    拥有该会话的oracle进程号和v$process联系
　　USER#                                          NUMBER                    oracle用户
　　USERNAME                                       VARCHAR2(30)              oracle用户名
　　COMMAND                                        NUMBER                    执行的命令
　　OWNERID                                        NUMBER                    会话的所有者
　　TADDR                                          VARCHAR2(8)               事务地址
　　LOCKWAIT                                       VARCHAR2(8)               锁等待地址，null if none
　　STATUS                                         VARCHAR2(8)               会话的状态：active,inactive,killed,cached,sniped
　　SERVER                                         VARCHAR2(9)               服务的类型，共享还是专用服务器
　　SCHEMA#                                        NUMBER                    用户标示模式
　　SCHEMANAME                                     VARCHAR2(30)              用户模式名称
　　OSUSER                                         VARCHAR2(30)              客户端操作系统名称
　　PROCESS                                        VARCHAR2(12)              操作系统客户端进程号
　　MACHINE                                        VARCHAR2(64)              操作系统机器名称
　　TERMINAL                                       VARCHAR2(16)              操作系统终端名
　　PROGRAM                                        VARCHAR2(64)              操作系统程序名
　　TYPE                                           VARCHAR2(10)              session种类
　　SQL_ADDRESS                                    RAW(4)                    当前回话识别目前执行的sql语句的表示地址
　　SQL_HASH_VALUE                                 NUMBER                    和sql_address一起唯一标示一条执行的sql语句
　　SQL_ID                                         VARCHAR2(13)              目前被执行的sql语句的标识符
　　SQL_CHILD_NUMBER                               NUMBER                    目前被执行的sql语句的子句数量
　　PREV_SQL_ADDR                                  RAW(4)                    与sql_hash_value一起标示上一条被执行的sql语句
　　PREV_HASH_VALUE                                NUMBER                    与PREV_SQL_ADDR 一起表示上一条被执行的sql语句
　　PREV_SQL_ID                                    VARCHAR2(13)              前一条被执行的sql语句
　　PREV_CHILD_NUMBER                              NUMBER                    上一条被执行的sql的子句数量
　　MODULE                                         VARCHAR2(48)              这个没太懂，oracle文档上面的解释比较清楚
　　MODULE_HASH                                    NUMBER                    这是针对上面一个字段的hash value
　　ACTION                                         VARCHAR2(32)              当前 正在执行被DBMS_APPLICATION_INFO.SET_ACTION procedure调用名称
　　ACTION_HASH                                    NUMBER                    针对上一字段name的hash value
　　CLIENT_INFO                                    VARCHAR2(64)              由APPLICATION_INFO.SET_ACTION procedure设定的过程名
　　FIXED_TABLE_SEQUENCE                           NUMBER                    oracle文档有着详细的解释session活动增长记录，最好再去看oracle文档
　　ROW_WAIT_OBJ#                                  NUMBER                    对象id，对象是table包含行源数据在OW_WAIT_ROW#中的id标识符
　　ROW_WAIT_FILE#                                 NUMBER                    标示数据文件，等待在OW_WAIT_ROW#中的row，这些行包含在这些数据文件中
　　ROW_WAIT_BLOCK#                                NUMBER                    表示数据块，这些数据块中行源等待在OW_WAIT_ROW#中
　　ROW_WAIT_ROW#                                  NUMBER                    目前被锁定的行
　　LOGON_TIME                                     DATE                      time of logon
　　LAST_CALL_ET                                   NUMBER      当前session为active时 记录的是session是session编程active状态来的运行时间，相反则为inactive的时间
　　PDML_ENABLED                                   VARCHAR2(3)               已经被PDML_STATUS字段所取代
　　FAILOVER_TYPE                                  VARCHAR2(13)

　  这是oracle文档对以上这个字段的解释：
　　Indicates whether and to what extent transparent application failover
　　(TAF) is enabled for the session:
　　■ NONE - Failover is disabled for this session
　　■ SESSION - Client is able to fail over its session following a disconnect
　　■ SELECT - Client is able to fail over queries in progress as well
　　See Also:
　　■ Oracle Database Concepts for more information on TAF
　　■ Oracle Database Net Services Administrator's Guide for information on
　　configuring TAF
　　FAILOVER_METHOD                 VARCHAR2(10)
　　oracle文档的解释：
　　Indicates the transparent application failover method for the session:
　　■ NONE - Failover is disabled for this session
　　■ BASIC - Client itself reconnects following a disconnect
　　■ PRECONNECT - Backup instance can support all connections from
　　every instance for which it is backed up
　　FAILED_OVER                          VARCHAR2(3)         //判定是否session已经处于失败状态，yes或者no
　　RESOURCE_CONSUMER_GROUP                   VARCHAR2(32)          当前session用户的源数据组
　　PDML_STATUS                        VARCHAR2(8)
　　oracle的文档解释：
　　If ENABLED, the session is in a PARALLEL DML enabled mode. If
　　DISABLED, PARALLEL DML enabled mode is not supported for the
　　session. If FORCED, the session has been altered to force PARALLEL DML.
　　PDDL_STATUS                        VARCHAR2(8)
　　oracle文档解释：
　　If ENABLED, the session is in a PARALLEL DDL enabled mode. If
　　DISABLED, PARALLEL DDL enabled mode is not supported for the
　　session. If FORCED, the session has been altered to force PARALLEL DDL
　　PQ_STATUS                       VARCHAR2(8)
　　oracle文档解释：
　　If ENABLED, the session is in a PARALLEL QUERY enabled mode. If
　　DISABLED, PARALLEL QUERY enabled mode is not supported for the
　　session. If FORCED, the session has been altered to force PARALLEL
　　QUERY.
　　CURRENT_QUEUE_DURATION              NUMBER         if 1则session已经在队列中，if 0 则还未形成排队
　　CLIENT_IDENTIFIER                  VARCHAR2(64)       客户端session标识符
　　BLOCKING_SESSION_STATUS               VARCHAR2(11)
　　oracle文档资料注解：
　　Blocking session status:
　　■ VALID
　　■ NO HOLDER
　　■ GLOBAL
　　■ NOT IN WAIT
　　■ UNKNOWN
　　BLOCKING_INSTANCE                NUMBER          模块化的实例标识符
　　BLOCKING_SESSION                 NUMBER          模块化的session标识符
　　SEQ#                             NUMBER          不唯一的标示每个等待的序列号
　　EVENT#                           NUMBER          事件数量
　　EVENT                            VARCHAR2(64)    oracle的session正在等待的数据或者事件
　　P1TEXT                           VARCHAR2(64)    首个附加参数的描述
　　P1                               NUMBER          首个附加参数
　　P1RAW                            RAW(4)          首个附加参数和前一个区别我还不是很懂
　　P2TEXT                           VARCHAR2(64)    第二个附加参数的描述
　　P2                               NUMBER          第二个附加参数
　　P2RAW                            RAW(4)          第二个附加参数
　　P3TEXT                           VARCHAR2(64)    第三个附加参数的描述
　　P3                               NUMBER          第三个附加参数
　　P3RAW                            RAW(4)          第三个附加参数
　　WAIT_CLASS_ID                    NUMBER          标记等待事件种类
　　WAIT_CLASS#                      NUMBER          等待事件的种类
　　WAIT_CLASS                       VARCHAR2(64)    等待事件的名称
　　WAIT_TIME                        NUMBER          非0代表上一次session上次等待时间，0代表session当前正在等待
　　SECONDS_IN_WAIT                  NUMBER

oracle文档的资料：
　　If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the
　　current wait condition. If WAIT_TIME > 0, then SECONDS_IN_WAIT is the
　　seconds since the start of the last wait, and SECONDS_IN_WAIT - WAIT_
　　TIME / 100 is the active seconds since the last wait ended.
　STATE                VARCHAR2(19)
　　oracle资料文档：
　　Wait state:
　　■ 0 - WAITING (the session is currently waiting)
　　■ -2 - WAITED UNKNOWN TIME (duration of last wait is unknown)
　　■ -1 - WAITED SHORT TIME (last wait <1/100th of a second)
　　■ >0 - WAITED KNOWN TIME (WAIT_TIME = duration of last wait)
　　SERVICE_NAME                   VARCHAR2(64)        session的服务名称
　　SQL_TRACE                      VARCHAR2(8)         标示sql是否能被跟踪
　　SQL_TRACE_WAITS                VARCHAR2(5)         标记是否等待事件被跟踪
　　SQL_TRACE_BINDS                VARCHAR2(5)         标记是否绑定跟踪可用与否

　　4.v$SQL
　　反映内容：包括查询游标等级的详细信息，可以用来找到负责解析游标的会话或者人；同样也有着繁多的信息字段，我下面只拿出一些常用的关键的列：
　　SQL> desc v$sql;
　　名称                                                类型
　　-----------------------------------------  ------------
　　SQL_TEXT                                     VARCHAR2(1000）     sql语句的前1千个字符
　　SQL_FULLTEXT                                 CLOB                sql语句的全部分，作为一个clob字段
　　SQL_ID                                       VARCHAR2(13)        sql与在liberary cache中的parent cursor的标识符
　　SHARABLE_MEM                                 NUMBER              被子cursor所使用的共享内存的和bytes
　　PERSISTENT_MEM                               NUMBER              整个生命周期child cursor所使用的固定内存的大小bytes
　　USERS_OPENING                                NUMBER
　　FETCHES                                      NUMBER              这条sql语句返回的数据行数量
　　EXECUTIONS                                   NUMBER              library cache中这条sql语句被执行的次数
　　USERS_EXECUTING                              NUMBER              执行这条sql语句的用户的数量
　　LOADS                                        NUMBER              sql或者object被装载load或者被reloaded次数
　　FIRST_LOAD_TIME                              VARCHAR2(38)        parent cursor创建的时间
　　INVALIDATIONS                                NUMBER              this child cursor 曾经无效的数量
　　PARSE_CALLS                                  NUMBER              做语法分析调用child cursor的次数
　　DISK_READS                                   NUMBER              直接做磁盘读取的次数
　　DIRECT_WRITES                                NUMBER              直接做磁盘写的次数
　　BUFFER_GETS                                  NUMBER              逻辑读次数
　　APPLICATION_WAIT_TIME                        NUMBER              应用等待时间 单位微秒
　　CLUSTER_WAIT_TIME                            NUMBER              集群等待时间 单位微妙
　　USER_IO_WAIT_TIME                            NUMBER              用户由于I/O造成等待时间
　　PLSQL_EXEC_TIME                              NUMBER              plsql程序执行时间 单位微秒
　　ROWS_PROCESSED                               NUMBER              通过语法分析的sql返回的数据总行数
　　OPTIMIZER_MODE                               VARCHAR2(10)        优化器选择方式
　　OPTIMIZER_COST                               NUMBER              采用上面指定优化器所要花费的代价
　　HASH_VALUE                                   NUMBER              Hash value of the parent statement in the library cache
　　SERVICE                                      VARCHAR2(64)        服务名，能够知道是oracle用户进程做得还是oracle后台进程
　　CPU_TIME                                     NUMBER              CPU time (in microseconds) used by this cursor for parsing, executing,and fetching
　　ELAPSED_TIME                                 NUMBER              用在解析，分析，取回返回数据所使用的总的时间
　　REMOTE                                       VARCHAR2(1)         是否是远程调用
　　LAST_LOAD_TIME                               VARCHAR2(38)        上次加载时间
　　CHILD_LATCH                                  NUMBER              受保护的子闩锁数量
　　LAST_ACTIVE_TIME                             DATE                上次活动时间
　　BIND_DATA                                    RAW(2000)           绑定数据

　　这个视图，你能很容易的得到造成过多的解析、物理读、物理写、逻辑读等待sql语句；对于查找低效率sql语句很方便；

　　5、v$event_name
　　反映内容：所有等待事件以及相关参数(p1-p3的定义)，没有全部写出来，只写了最重要的字段
　　SQL> desc v$event_name
　　名称                                                               类型
　　-----------------------------------------------------   --------------------
　　EVENT#                                                         NUMBER             该事件的引用编号
　　EVENT_ID                                                       NUMBER             该事件的标识符
　　NAME                                                           VARCHAR2(64)       oracle针对次事件的名称
　　PARAMETER1                                                     VARCHAR2(64)       P1信息的描述
　　PARAMETER2                                                     VARCHAR2(64)       P2信息的描述
　　PARAMETER3                                                     VARCHAR2(64)       P3信息的描述

　　6、V$session_event
　　反映的内容：最近的所有等待事件的统计信息
　　SQL> desc v$session_event
　　名称                           类型
　　-----------------------      --------
　　SID                              NUMBER            标识符
　　EVENT                            VARCHAR2(64)      该事件的名称
　　TOTAL_WAITS                      NUMBER            该会话总的等待次数
　　TOTAL_TIMEOUTS                   NUMBER            该会话在等待事件期间遇到的超时次数
　　TIME_WAITED                      NUMBER            该会话等待该事件所消耗的总时间单位0.01秒
　　AVERAGE_WAIT                     NUMBER            该会话等待该事件所消耗平均等待时间0.01秒
　　MAX_WAIT                         NUMBER            进程必须等待该事件的最大时间总值单位0.01秒
　　EVENT_ID                         NUMBER            等待事件的唯一标识符对应V$event_name表

　　通过这个动态性能视图，你应该很容易了解到最近常常发生的等待事件的大体情况；

　　7、v$session_wait
　　反映的内容：提供了当前会话的当前等待事件的详细信息
　　SQL> desc v$session_wait
　　名称                    是否为空? 类型
　　----------------------- -------- ----------------
　　SID                              NUMBER               唯一表示符
　　SEQ#                             NUMBER               等待次序的计数器，进程每开始一次新的等待就就增加1
　　EVENT                            VARCHAR2(64)         Resource or event for which the session is waiting
　　P1TEXT                           VARCHAR2(64)         等待事件P1参数名称
　　P1                               NUMBER               p1的值
　　P1RAW                            RAW(4)               p1参数2进制值
　　P2TEXT                           VARCHAR2(64)         等待事件P2参数名称
　　P2                               NUMBER               p2的值
　　P2RAW                            RAW(4)               p2参数2进制值
　　P3TEXT                           VARCHAR2(64)         等待事件P3参数名称
　　P3                               NUMBER               p3的值
　　P3RAW                            RAW(4)               p3参数2进制值
　　WAIT_TIME                        NUMBER               上一次等待持续的时间单位0.01秒
　　SECONDS_IN_WAIT                  NUMBER               等待时间单位秒
　　STATE                            VARCHAR2(19)         指出进程是已经完成了等待还是还在等待

　　比如：
　　SQL> select event name,p1text p1name,p1 p1value from v$session_wait;
　　NAME                           P1NAME               P1VALUE
　　------------------------------ --------------- ------------
　　jobq slave wait                                           0
　　SQL*Net message from client    driver id         1111838976
　　Streams AQ: qmn slave idle wai                            0
　　t
　　Streams AQ: qmn coordinator id                            0
　　le wait
　　Streams AQ: waiting for time m                            0
　　anagement or cleanup tasks
　　rdbms ipc message              timeout                  100
　　rdbms ipc message              timeout                  300
　　rdbms ipc message              timeout                  500

　　很容易看到当前等待事件的情况；

　　8、v$system_event
　　反映的内容：列出自从实例启动以来的等待事件的统计信息
　　SQL> desc v$system_event
　　名称                       类型
　　----------------------- -------------------
　　EVENT                            VARCHAR2(64)   等待事件的名称
　　TOTAL_WAITS                      NUMBER         次等待事件的总的等待次数
　　TOTAL_TIMEOUTS                   NUMBER         总的超时等待事件的次数
　　TIME_WAITED                      NUMBER         等待事件的总的等待时间
　　AVERAGE_WAIT                     NUMBER         平均等待时间单位0.01s
　　EVENT_ID                         NUMBER         等待事件的唯一标示符 和v$event_name中的对应

　　9、dba_tables
　反映内容：记录数据库表所有信息，这里我也只是列出用于性能测试多的字段
　　SQL> desc dba_tables;
　　名称                        类型
　　-----------------------  -------------
　　OWNER                   NOT NULL VARCHAR2(30)    所有者
　　TABLE_NAME              NOT NULL VARCHAR2(30)    表名
　　TABLESPACE_NAME                  VARCHAR2(30)    表所在的表空间名
　　CLUSTER_NAME                     VARCHAR2(30)    所在集群的名称
　　PCT_FREE                         NUMBER          数据块允许空闲的最小百分比
　　PCT_USED                         NUMBER          数据块允许使用的最大百分比
　　MAX_TRANS                        NUMBER          最大事务数
　　FREELISTS                        NUMBER          被分配给段的空闲进程数量
　　LOGGING                          VARCHAR2(3)     是否记录日志生成重做日志记录
　　NUM_ROWS                         NUMBER          总共有多少行数据记录
　　BLOCKS                           NUMBER          此表使用的块数
　　EMPTY_BLOCKS                     NUMBER          表中从来不没有被使用的空块
　　AVG_SPACE                        NUMBER          表中平均可用空闲空间
　　AVG_SPACE_FREELIST_BLOC          NUMBER          Average freespace of all blocks on a freelist
　　NUM_FREELIST_BLOCKS              NUMBER          空闲列表的块数
　　CACHE                            VARCHAR2(5)     检查表是否被cache到buffer中
　　TABLE_LOCK                       VARCHAR2(8)     表名表是正在否被锁定
　　SAMPLE_SIZE                      NUMBER          表被分析的比例或者数量
　　LAST_ANALYZED                    DATE            表上一次被分析的时间点
　　NESTED                           VARCHAR2(3)     表是否嵌套
　　BUFFER_POOL                      VARCHAR2(7)     buffer_pool中被用于表块的default、keep、recycle
　　MONITORING                       VARCHAR2(3)     表名表是否正被监控

这个视图非常的有用，一般可以用来作为健康检查，检查数据库表的分析情况等，对于oracle9以后的版本都推荐使用基于成本的优化器cbo，基于规则的优化器逐渐被放弃了，所以分析的了解很重要：
　　如下：
　　TABLE_NAME                     TABLESPACE_NAME                  PCT_FREE   PCT_USED   NUM_ROWS BUFFER_ SAMPLE_SIZE LAST_ANALYZED
　　------------------------------ ------------------------------ ---------- ---------- ---------- ------- ----------- --------------
　　LT_LHT_COUNT                   LHT_BBS_SPACE                          10                     8 DEFAULT           8 24-6月 -09
　　LT_LHT_CYXX                    LHT_BBS_SPACE                          10                    26 DEFAULT          26 24-6月 -09
　　LT_LHT_FLBK                    LHT_BBS_SPACE                          10                     7 DEFAULT           7 24-6月 -09
　　LT_LHT_SORT                    LHT_BBS_SPACE                          10                     3 DEFAULT           3 24-6月 -09
　　LT_LHT_STYLE                   LHT_BBS_SPACE                          10                    15 DEFAULT          15 24-6月 -09
　　LT_LHT_XTYH                    LHT_BBS_SPACE                          10                     6 DEFAULT           6 24-6月 -09
　　LT_LHT_ZCYH                    LHT_BBS_SPACE                          10                    36 DEFAULT          36 24-6月 -09
　　LT_LHT_FT                      LHT_BBS_SPACE                          10                    24 DEFAULT          24 24-6月 -09
　　LT_LHT_HT                      LHT_BBS_SPACE                          10                    21 DEFAULT          21 24-6月 -09
　　TEST                           LHT_BBS_SPACE                          10                     0 DEFAULT           0 29-6月 -09
　　TEST_LOG                       LHT_BBS_SPACE                          10                     6 DEFAULT           6 29-6月 -09

　　可以清晰的了解到裱褙分析的情况，如果LAST_ANALYZED没有值或者时间很早了，那就必须重新的分析这张表得到更为准确统计信 息，SAMPLE_SIZE代表分析的采样值，如果不合理也可以在分析的时候作调整，这些检查有利于却确定基于成本的优化器能够按照最优化的路经化最小的 成本来完成操作和响应。
　既然对于dba_tables有这样应用，那么dba_indexes也就有同样的使用方式了，索引和表达大同小异就不再说了。

　　9、V$SGA_TARGET_ADVICE
　　采用动态sga内存管理，但是你希望能知道如何设置这个最大大小才合适呢，那可以采用这个视图；
　　SQL> desc V$SGA_TARGET_ADVICE
　　名称                         类型
　　----------------------- ------------------------
  　SGA_SIZE                         NUMBER            sga大小
　　SGA_SIZE_FACTOR                  NUMBER            此表中的sga_size和当前的parameter中的sga大小的比值
　　ESTD_DB_TIME                     NUMBER
　　ESTD_DB_TIME_FACTOR              NUMBER
　　ESTD_PHYSICAL_READS              NUMBER            估计的物理读的次数

　　比如：
　　SQL> select sga_size,sga_size_factor,estd_db_time,estd_db_time_factor,estd_physical_reads from V$SGA_TARGET_ADVICE;
　　SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
　　---------- --------------- ------------ ------------------- -------------------
　　276               1          154                   1               10828
　　138              .5          191              1.2403               12666
　　207             .75          154                   1               10828
　　552               2          154                   1               10828
　　414             1.5          154                   1               10828
　　483            1.75          154                   1               10828
　　345            1.25          154                   1               10828
　　已选择7行。
　　可以发现当sga设置为207M或者更大的时候就没有性能上的提升了，所以最大也就设置为207左右是比较合适的。

　　10、 V$pga_TARGET_ADVICE
　　结合9的sga设置pga的pga_target_max设置同样可以采用这样的方式；
　　SQL> desc V$pga_TARGET_ADVICE
　　名称                                                       类型
　　-----------------------------------------          --------------------------
　　PGA_TARGET_FOR_ESTIMATE                            NUMBER                      pga设置大小bytes
　　PGA_TARGET_FACTOR                                  NUMBER                      与当前parameter中设置的值的比例
　　ADVICE_STATUS                                      VARCHAR2(3)                 表名advice是否课可采用on/off，取决于STATISTICS_LEVEL
　　BYTES_PROCESSED                                    NUMBER                      被所有的进程所占用的资源bytes
　　ESTD_EXTRA_BYTES_RW                                NUMBER                      被估计的用于读和写的资源占用
　　ESTD_PGA_CACHE_HIT_PERCENTAGE                      NUMBER                      估计的命中率，当PGA_TARGET_FOR_ESTIMATE等于实际设置的pgaparameter时
　　ESTD_OVERALLOC_COUNT                               NUMBER                      这里的值如果为零表示pga设置足够大，非零说明pga的设置不是足够大的

　　QL> select * from  V$pga_TARGET_ADVICE;
　　GA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUT
　　---------------------- ----------------- --- --------------- ------------------- ----------------------------- ------------------
　　11927552              .125 ON        202396672             6168576                            974
　　23855104               .25 ON        202396672                   0                           1000
　　47710208                .5 ON        202396672                   0                           100
　
　Oracle维护常用SQL语句（查询系统表和视图）
　　提要：
　　1、查看表空间的名称及大小
　　2、查看表空间物理文件的名称及大小
　　3、查看回滚段名称及大小
　　4、查看控制文件
　　5、查看日志文件
　　6、查看表空间的使用情况
　　7、查看数据库库对象
　　8、查看数据库的版本
　　9、查看数据库的创建日期和归档方式
　　10、捕捉运行很久的SQL
　　11。查看数据表的参数信息
　　12.查看还没提交的事务
　　13。查找object为哪些进程所用
　　14。回滚段查看
　　15。耗资源的进程（top session）
　　16。查看锁（lock）情况
　　17。查看等待（wait）情况
　　18。查看sga情况
　　19。查看catched object
　　20。查看V$SQLAREA
　　21。查看object分类数量
　　22。按用户查看object种类
　　23。有关connection的相关信息
　　1）查看有哪些用户连接
　　2）根据v.sid查看对应连接的资源占用等情况
　　3）根据sid查看对应连接正在运行的sql
　　24．查询表空间使用情况
　　25.查询表空间的碎片程度
　　26.查询正在运行的数据库实例

　　1、查看表空间的名称及大小
　　select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
　　from dba_tablespaces t, dba_data_files d
　　where t.tablespace_name = d.tablespace_name
　　group by t.tablespace_name;
　　
    2、查看表空间物理文件的名称及大小
　　select tablespace_name, file_id, file_name,
　　round(bytes/(1024*1024),0) total_space
　　from dba_data_files
　　order by tablespace_name;
　　
    3、查看回滚段名称及大小
　　select segment_name, tablespace_name, r.status,
　　(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
　　max_extents, v.curext CurExtent
　　From dba_rollback_segs r, v$rollstat v
　　Where r.segment_id = v.usn(+)
　　order by segment_name ;
　　
    4、查看控制文件
　　select name from v$controlfile;
　　
    5、查看日志文件
　　select member from v$logfile;
　　
    6、查看表空间的使用情况
　　select sum(bytes)/(1024*1024) as free_space,tablespace_name
　　from dba_free_space
　　group by tablespace_name;
　　SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
　　(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
　　FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
　　WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
　　
    7、查看数据库库对象
　　select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
　　
    8、查看数据库的版本
　　Select version FROM Product_component_version
　　Where SUBSTR(PRODUCT,1,6)='Oracle';
　　
    9、查看数据库的创建日期和归档方式
　　Select Created, Log_Mode, Log_Mode From V$Database;
　　
    10、捕捉运行很久的SQL
　　column username format a12
　　column opname format a16
　　column progress format a8
　　select username,sid,opname,
　　round(sofar*100 / totalwork,0) || '%' as progress,
　　time_remaining,sql_text
　　from v$session_longops , v$sql
　　where time_remaining <> 0
　　and sql_address = address
　　and sql_hash_value = hash_value
　　/
　　
    11。查看数据表的参数信息
　　SELECT partition_name, high_value, high_value_length, tablespace_name,
　　pct_free, pct_used, ini_trans, max_trans, initial_extent,
　　next_extent, min_extent, max_extent, pct_increase, FREELISTS,
　　freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,
　　empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,
　　last_analyzed
　　FROM dba_tab_partitions
　　--WHERE table_name = :tname AND table_owner = :towner
　　ORDER BY partition_position
　　
    12.查看还没提交的事务
　　select * from v$locked_object;
　　select * from v$transaction;
   
    13。查找object为哪些进程所用
　　select
　　p.spid,
　　s.sid,
　　s.serial# serial_num,
　　s.username user_name,
　　a.type object_type,
　　s.osuser os_user_name,
　　a.owner,
　　a.object object_name,
　　decode(sign(48 - command),
　　1,
　　to_char(command), 'Action Code #' || to_char(command) ) action,
　　p.program oracle_process,
　　s.terminal terminal,
　　s.program program,
　　s.status session_status
　　from v$session s, v$access a, v$process p
　　where s.paddr = p.addr and
　　s.type = 'USER' and
　　a.sid = s.sid and
　　a.object='SUBSCRIBER_ATTR'
　　order by s.username, s.osuser

　　14。回滚段查看
　　select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents
　　Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,
　　v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,
　　sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,
　　v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and
　　v$rollstat.usn (+) = v$rollname.usn order by rownum

　　15。耗资源的进程（top session）
　　select s.schemaname schema_name, decode(sign(48 - command), 1,
　　to_char(command), 'Action Code #' || to_char(command) ) action, status
　　session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,
　　nvl(s.username, '[Oracle process]') user_name, s.terminal terminal,
　　s.program program, st.value criteria_value from v$sesstat st, v$session s , v$process p
　　where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL'
　　or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc

　　16。查看锁（lock）情况

　　select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,
　　decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',
　　'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,
　　o.object_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3,
　　'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)
　　lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2
　　from sys.dba_objects o, ( select s.osuser, s.username, l.type,
　　l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,
　　v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner
　　<> 'SYS' order by o.owner, o.object_name

　　17。查看等待（wait）情况
　　SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value
　　FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets',
　　'consistent gets') group by v$waitstat.class, v$waitstat.count

　　18。查看sga情况
　　SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC

　　19。查看catched object
　　SELECT owner, name, db_link, namespace,
　　type, sharable_mem, loads, executions,
　　locks, pins, kept FROM v$db_object_cache

　　20。查看V$SQLAREA
　　SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,
　　VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS,
　　USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,
　　BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA

　　21。查看object分类数量

　　select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 ,
　　'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity from
　　sys.obj$ o where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' , 3
　　, 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union select
　　'COLUMN' , count(*) from sys.col$ union select 'DB LINK' , count(*) from

　　22。按用户查看object种类
　　select u.name schema, sum(decode(o.type#, 1, 1, NULL)) indexes,
　　sum(decode(o.type#, 2, 1, NULL)) tables, sum(decode(o.type#, 3, 1, NULL))
　　clusters, sum(decode(o.type#, 4, 1, NULL)) views, sum(decode(o.type#, 5, 1,
　　NULL)) synonyms, sum(decode(o.type#, 6, 1, NULL)) sequences,
　　sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))
　　others from sys.obj$ o, sys.user$ u where o.type# >= 1 and u.user# =
　　o.owner# and u.name <> 'PUBLIC' group by u.name order by
　　sys.link$ union select 'CONSTRAINT' , count(*) from sys.con$

　　23。有关connection的相关信息

　　1）查看有哪些用户连接

　　select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command),
　　'Action Code #' || to_char(command) ) action, p.program oracle_process,
　　status session_status, s.terminal terminal, s.program program,
　　s.username user_name, s.fixed_table_sequence activity_meter, '' query,
　　0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num
　　from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'
　　order by s.username, s.osuser

　　2）根据v.sid查看对应连接的资源占用等情况
　　select n.name,
　　v.value,
　　n.class,
　　n.statistic#
　　from v$statname n,
　　v$sesstat v
　　where v.sid = 71 and
　　v.statistic# = n.statistic#
　　order by n.class, n.statistic#

　　3）根据sid查看对应连接正在运行的sql
　　select /*+ PUSH_SUBQ */
　　command_type,
　　sql_text,
　　sharable_mem,
　　persistent_mem,
　　runtime_mem,
　  sorts,
　　version_count,
　　loaded_versions,
　　open_versions,
　　users_opening,
　　executions,
　　users_executing,
　　loads,
　　first_load_time,
　　invalidations,
　　parse_calls,
　　disk_reads,
　　buffer_gets,
　　rows_processed,
　　sysdate start_time,
　　sysdate finish_time,
　　'>' || address sql_address,
　　'N' status
　　from v$sqlarea
　　where address = (select sql_address from v$session where sid = 71)

　　24．查询表空间使用情况
　　select a.tablespace_name "表空间名称",
　　100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",
　　round(a.bytes_alloc/1024/1024,2) "容量(M)",
　　round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)",
　　round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",
　　Largest "最大扩展段(M)",
　　to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"
　　from (select f.tablespace_name,
　　sum(f.bytes) bytes_alloc,
　　sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
　　from dba_data_files f
　　group by tablespace_name) a,
　　(select f.tablespace_name,
　　sum(f.bytes) bytes_free
　　from dba_free_space f
　　group by tablespace_name) b,
　　(select round(max(ff.length)*16/1024,2) Largest,
　　ts.name tablespace_name
　　from sys.fet$ ff, sys.file$ tf,sys.ts$ ts
　　where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#
　　group by ts.name, tf.blocks) c
　　where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name

　　25. 查询表空间的碎片程度
　　select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
　　having count(tablespace_name)>10;
　　alter tablespace name coalesce;
　　alter table name deallocate unused;
　　create or replace view ts_blocks_v as
　　select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
　　union all
　　select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
　　select * from ts_blocks_v;
　　select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
　　group by tablespace_name;

　　26。查询有哪些数据库实例在运行
 　select inst_name from v$active_instances;

评论

10 楼 dplead 2011-03-17  

楼主，我跟你学

9 楼 mamacmm 2011-01-05  

lixin_2002 写道

楼主看的什么书学习的oracle？

偶是跟着视频学的，呵呵。

8 楼 778856 2011-01-05  

介绍Oracle的好书还是很多的

不过我是笨都没买过 都是去图书馆借的

7 楼 lixin_2002 2011-01-04  

楼主看的什么书学习的oracle？

6 楼 xiaomogui 2011-01-03  

sql语句 排版 看着不舒服！

5 楼 mamacmm 2010-12-29  

qq240996777 写道

请把前面的链接贴出来！

我已经写在博客里了，既然看不到，那就改到论坛吧。

4 楼 qq240996777 2010-12-29  

请把前面的链接贴出来！

3 楼 cartonwang 2010-12-28  

怎么把最后的修改update 这段sql写出存储过程啊。。。
指定product产品的价格上下限修改。。。

2 楼 cartonwang 2010-12-28  

--修改操作：根据产品的经销价，得到产品定义价格中的最高和最低价
update sc_tt_price p set (p.max_price, p.min_price) =
  (select pi.sale_price*(ptd.max_make_up_rate/ptd.min_make_up_rate), ptd.min_make_up_rate
  from sc_tt_price p, sc_tm_product_info pi, sc_tt_price_template pt, sc_tt_price_tmp_dtl ptd
  where 1=1
  and p.sc_tm_product_info_id = pi.sc_tm_product_info_id
  and p.sc_tt_price_template_id = pt.sc_tt_price_template_id
  and pt.sc_tt_price_template_id = ptd.sc_tt_price_template_id
  and pi.sc_tt_bak3 = ptd.dimesion_2         --中类
  and pi.grade = ptd.product_level           --档次
  and pi.bland = '1')                        --品牌

1 楼 cartonwang 2010-12-28  

Oracle怎么写存储过程。。。
不知道怎么动手。。

--得到品牌，中类，档次，最高上限，最低下限，建议最高上限，最低下限 价格
select pi.sc_tm_product_info_id, pi.sale_price, pi.bland "品牌", ptd.sc_tt_price_tmp_dtl_id, ptd.max_make_up_rate, ptd.min_make_up_rate, pi.sale_price*(ptd.max_make_up_rate/ptd.min_make_up_rate)
  from sc_tt_price p, sc_tm_product_info pi, sc_tt_price_template pt, sc_tt_price_tmp_dtl ptd
  where 1=1
  and p.sc_tm_product_info_id = pi.sc_tm_product_info_id
  and p.sc_tt_price_template_id = pt.sc_tt_price_template_id
  and pt.sc_tt_price_template_id = ptd.sc_tt_price_template_id
  and pi.sc_tt_bak3 = ptd.dimesion_2         --中类
  and pi.grade = ptd.product_level           --档次
  and pi.bland = '1'                         --品牌