我常用的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.rzrgm.cn/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

posted @ 2011-06-11 13:07 harrychinese 閱讀(894) 評論(0) 收藏舉報

刷新頁面返回頂部

harrychinese

我常用的Oracle知識點匯總

公告