我常用的Oracle知识点汇总

是时候该总结一下这2年的工作了, 这篇是我常用的Oracle知识点汇总, 很多都是从网上收集的.


---====================================
--sqlplus 的使用
---====================================
--当用sqlplus执行大量的insert语句是,如果sqlplus回写的话, 速度会很慢, 关闭回写,速度会快很多
set feed off
set echo off


--当用sqlplus的spool命令, 输出csv格式的报告, 需要配置sqlplus的设置项, 否则格式会很难看的.
set pages 0;
set head off;
set echo off;
set feedback off;
set term off;
set linesize 8;

--在linux上使用sqlplus, 不能像windows下通过上下键来调出前面的命令, 可以使用rlwrap给sqlplus装个套, 这样就支持了. 步骤如下,
首先安装rlwrap包：apt-get install rlwrap
然后在profile中增加一个alias
alias sqlplus2=’rlwrap sqlplus’


--以下是shell中如何使用sqlplus调用sp, 并获取sqlplus返回值的示例

${ORACLE_HOME}/bin/sqlplus -S $USER/$PWD@$ORA_SID<<!
whenever sqlerror exit sql.sqlcode rollback
exec  PKG1.SP1;
exit success commit
!

rc=$?
if [ "$rc" -eq "0" ]
then
    MESSAGE="PKG1.SP1 finished successfully"
else
    MESSAGE="PKG1.SP1 finished with errors, please check"
fi

---====================================
--程序影响性分析
---====================================
维护数据仓库, 经常会碰到前端业务系统变表结构, 或者干脆弃用了某些表, 这时候, 我们要分析数据仓库中是否有程序/视图用到该表.

/*
 检查有没有view用到某个表
*/
select * from dba_views dv
where 1=1
and (dv.owner, dv.view_name ) in
(
select dd.owner,dd.name from dba_dependencies dd
where  1=1
and dd.type='VIEW'
and dd.referenced_name='YOUR_TABLE_NAME'
and dd.referenced_type='TABLE'
)
;

--检查程序(如procedure/package, 但不包括view)使用到某个table的某个字段

/*
'PROCEDURE'
'FUNCTION',
'PACKAGE',
'PACKAGE BODY',
'TRIGGER',
'TYPE',
'TYPE BODY',
'UNDEFINED'
--但不包括view
*/

select *
  from dba_source ck_field
 where 1 = 1
 
   --check field name
   and upper(ck_field.text) like upper('%FIELD_NAME%')
     
   and (ck_field.owner, ck_field.name, ck_field.name, ck_field.type) in
       (select ck_table.owner, ck_table.name, ck_table.name, ck_table.type
          from dba_source ck_table
         where 1 = 1
  --check table name
           and  upper(ck_table.text) like upper('%TABLE_NAME%')
        )
;

 

---====================================
--oracle字符集
---====================================
http://blog.csdn.net/HPSG/archive/2009/02/18/3907418.aspx



---====================================
--oracle DB Link
---====================================
有两种方式可以创建dblink, 一种方式是通过预定义好的tns name指定remote server, 另一种是在dblink DDL语句中使用service name来指定remote server.
--通过预定好的tns name方式创建public dblink

create public database link LINK_SERVER_A
  connect to user_a1 identified by user_a1_PWD
  using 'Server_A_TNS';

--通过SERVICE_NAME方式创建public dblink 

create public database link LINK_SERVER_A
  connect to user_a1 identified by user_a1_PWD
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora9)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Server_A)
    )
  )';

 
关于Oracle DB Link的授权:
在Server B上创建了一个public型的DBLink后, Server B上所有的account都可以使用这个dblink访问 server A, 而且都是假借server A上user_a1账号来访问server A. 所以除非必要, 最好不要创建public dblink, 尽量创建private dblink.
 

---====================================
--oracle pl sql 编程
---==================================== 
Oracle cursor
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php

 
PL SQL没有break,continue, 该如何办?
--1. break可用exit代替。  
  loop  
      ...  
      exit   when   ...;  
      ...  
  end   loop; 
 
--2. continue和用自定义异常代替。  
  loop  
      begin  
          ...  
          raise   my_ex;  
          ...  
      exception  
          when   others   then   null;  
      end;  
  end   loop;  
 
 

 

---====================================
--监控oracle
---==================================== 
--访问Oracle enterprise manager, 可以获取到很多有价值的信息
https://oracle_server_ip:5500/em

--查看Oracle资源的锁定情况

SELECT  distinct  A.OWNER,        
    A.OBJECT_NAME,        
    B.SESSION_ID,        
    B.ORACLE_USERNAME,        
    B.OS_USER_NAME,        
    B.PROCESS,        
    B.LOCKED_MODE,          
    C.SID,        
    C.SERIAL#,        
    C.PROGRAM,
    C.SQL_ID,
       A.OBJECT_NAME,
    C.LOGON_TIME ,
       sysdate current_date                
           FROM    ALL_OBJECTS    A,        
                     V$LOCKED_OBJECT    B,        
                     SYS.GV_$SESSION    C    
         WHERE    (    A.OBJECT_ID    =    B.OBJECT_ID    )    
             AND    (B.PROCESS    =    C.PROCESS    )
            -- AND    A.OBJECT_NAME='YOUR_TABLE_OR_MV'
      order by a.object_name
      ;

 
--上面的语句确定了session的sid和serial#, 如要kill session, 可用如下语句
alter system kill session 'sid,serial#' 
--比如 alter system kill session '11,314'

 
关于oracle lock的各种mode, 可以看下面博客 
  http://www.cnitblog.com/stomic/archive/2011/05/26/74079.html


--通过V$SQLAREA查找有问题的查询
--V$SQLAREA视图记录着sql语句的磁盘读数据, 所以可以通过它来识别哪些SQL需要优化。

select b.username username,
       a.disk_reads reads,
       a.executions exec,
       a.disk_reads / decode(a.executions, 0, 1, a.executions) rds_exec_ratio,
       a.command_type,
       a.sql_text Statement
  from v$sqlarea a, dba_users b
 where 1=1
   and a.parsing_user_id = b.user_id
   and a.disk_reads > 100000 --you can change this threshold value based on your situation
 order by a.disk_reads desc;
 

 
 
---====================================
--oracle的资源管理
---==================================== 

--查询TABLE或MV的占用空间

SELECT *
  FROM (Select Segment_Name, Sum(bytes) / 1024 / 1024 SIZE_M
          From dba_extents de
         where 1 = 1
           and de.segment_type = 'TABLE'
           --AND DE.tablespace_name='YOUR_TABLE_SPACE'
           --AND DE.owner = 'YOUR_ACCOUNT'
         Group By Segment_Name)
 order by SIZE_M desc

 


---oracle表空间的事情状况要经常查看，一般空闲比例过低的时候就应该考虑增大表看空间了。查看方法如下SQL:

--方法1(速度很快)

select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
order by dbf.tablespace_name
;

--或者, 方法2(速度很快)

SELECT a.tablespace_name,
       SUM(a.bytes) / 1024 / 1024 "ALLOCATED/MB",
       SUM(DECODE(a.autoextensible, 'YES', a.maxbytes, a.bytes)) / 1024 / 1024 "MAXSPACE/MB",
       SUM(b.free) / 1024 / 1024 "FREE/MB",
       SUM(b.free +
           DECODE(a.autoextensible, 'YES', (a.maxbytes - a.bytes), 0)) / 1024 / 1024 "UNUSED/MB",
       SUM(b.free +
           DECODE(a.autoextensible, 'YES', (a.maxbytes - a.bytes), 0)) /
       SUM(DECODE(a.autoextensible, 'YES', a.maxbytes, a.bytes)) * 100 "UNUSED%"
  FROM (SELECT file_id, tablespace_name, autoextensible, maxbytes, bytes
          FROM dba_data_files) a,
       (SELECT file_id, tablespace_name, SUM(bytes) free
          FROM dba_free_space
         GROUP BY file_id, tablespace_name) b
 WHERE a.file_id = b.file_id
 GROUP BY a.tablespace_name;

 
--查看temp tablespace.
 

select db.name,
       s.tablespace_name,
       s.user_bytes/1024/1024  total_size_M,
       s.t_used_blocks,
       s.total_blocks,      
       round(100 - (s.t_used_blocks / s.total_blocks) * 100, 3) idle_ratio,
       sysdate
  from (select d.tablespace_name tablespace_name, sum(d.user_bytes ) user_bytes ,
               nvl(sum(used_blocks), 0) t_used_blocks,
               sum(blocks) total_blocks
          from v$sort_segment v, dba_temp_files d
         where d.tablespace_name = v.tablespace_name(+)
         group by d.tablespace_name) s,
       v$database db;

      
 

--当发现有的表空间不够的错误时，处理如下：
--1：找出该表空间对应的数据文件及路径
select * from dba_data_files t
where t.tablespace_name = 'ARD'

--2：增大数据文件
alter database datafile '全路径的数据文件名称' resize ***M

--3：增加数据文件
alter tablespace 表空间名称
add datafile '全路径的数据文件名称' ***M
--注解：表空间尽量让free百分比保持在10%以上，如果低于10%就增加datafile或者resize datafile，一般当个数据文件不要超过2G

 
---====================================
--查看oracle服务器的一些基本信息
---====================================
--版本信息：
select * from v$version;

--查看数据库信息
select name, created, log_mode from v$database;

--检查数据库中已安装的产品项, 即option, 比如olap option, rac option等
--查询V$OPTION视图，可以获取您已安装的Oracle产品项。V$VERSION视图给出已安装的基本产品项的版本。
select * from v$option;

更多V$ 视图查询, 见下面博客
http://www.cnblogs.com/jasoname/archive/2011/03/15/1985309.html


 
---====================================
--执行计划和分析统计
---====================================
查看执行计划的方法
1. 首先将执行计划写到系统表中,
  Explain plan for select * from view_a;
  并commit这个命令
2. 显示执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());



--分析统计
  analyze table  compute statistics 等同
 于 analyze table compute statistics for table for all indexes for all columns;
 比 analyze table compute statistics for table for all indexes for all indexed columns 分析了更多的信息 
 对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。
 更多信息见博客 http://bianxq.javaeye.com/blog/464679