)oracle學習筆記

一、改善資料庫效能
1、        用explain plan 來統計檢查sql 語句的效能
具體操作：
產生plan_table表
通過運行 rdbms\admin\utlxplain.sql
explain plan set statement_id =’statement1’ into plan_table
for  select * from table_name ;

select statement_id, operation, options position from plan_table
註：position 是對開銷得描述
    operation 操作對象
        option
2、用set autotrace
set autotrace on
set autotrace off
或
set autot on
set autot off

3、        選擇最佳化sql 語句的方法
1）        基於代價的最佳化
兩個參數frist_rows 和 all_rows
用修改optimizer_mode 參數的方法：
optimizer_mode 參數在init.ora中。
optimizer_mode 參數 還可以設定為 choose .
用修改會話的方法：
                alter session set optimizer_goal = frist_rows
                alter session set optimizer_goal = all_rows
用修改提示的方法：
        select         --+frist_rows
*  from table_name where “條件”
                或
        select         /*+frist_rows*/
*  from table_name where “條件”
4、        以上是基於代價的最佳化，還有基於規則的最佳化已不常用
5、        使用索引改善效能
1) 索引的建立
create [unique] index index_name
on table table_name (欄位1，欄位2 )
註：   帶unique選項表示建立唯一索引       
注意： 索引有單列索引，重列索引；
主鍵、候選索引鍵不用建索引；
                外鍵最好鍵索引；
                索引最好建在大表和查詢頻率高的唯一值的列上；
                LONG型的列不能鍵索引；
                索引不宜建的太多。
1)        索引的刪除
drop index index_name ;
6、        編寫共用池中已有的sql語句
用select sql_text from V$sqlarea 可查看共用池中已有的sql語句

二、調整資料庫效能
1、        提供足夠的記憶體，減少I/O 操作。
設定 shared_pool_size 、db_block_size、db_block_buffer的大小。（在init.ora中）
2、        減少磁碟競爭
1）        將資料檔案和記錄檔放在不同磁碟上
如        create database db_name
        datafile  ‘c:\oracle\data\ db_name01.dbf ’
        logfile   ‘d:\ oracle\data\ log_name01.dbf’
        archivelog
2)        將索引和資料建在不同磁碟上
將索引資料表空間，資料資料表空間建在不同的磁碟上，分別基於這兩個資料表空間建表和索引。
                Create table        table_name
                (        NO                 number(5)                primary key,
                        name         varchar2（10）
)
tablespace        tablespace_in_c;

create  index  index_name
on table_name(NO)
tablespace   tablespace_in_d;
3、        調整復原段       
建復原段：
create public rollback segment rollback_name
                tablespace  RBS
                optimal  to 500k;
        註：復原段建好後和資料表空間一樣處於離線狀態，用修改資料表空間命令將其聯機
修改復原段：
        alter rollback segment rollback_name
                online| offline
                storage ……
                shrink to  …… ;
使用復原段：
        savepoint  savepoint_7 ;
        rollback  to savepoint_7;       
       
4、        調整網路多線程伺服器
1）        設定網路通訊協定的進程調度個數
網路通訊協定的進程調度個數參數
mts_dispatachers = ‘tcp,5’,’ipc,5’
         mts_max_dispatachers = 30
修改網路通訊協定的進程調度個數參數
         alter system mts_dispatchers  ‘tcp,8’, ‘ipc,8’ ,
2）        觀察V$dispatacher 資料字典
select  name, network, busy, idle from   V$dispatacher ;
3）        設定、修改伺服器處理序個數
mts_servers = 5
mts_max_servers = 20
                                alter system mts_servers  8 ;
4）        減少檢測點
log_checkpoint_interval = 1200
log_checkpoint_timeout = 0
5)        啟動檢測點監控進程
checkpoint_process         = true

5、增加日誌組檔案
1）        create database db_name
datafile ‘c:\oracle\data\db_name.dbf’
logfile group 1 ‘c:\oralce\log01.dbf’
logfile group 2 ‘d:\oralce\log01.dbf’
logfile group 3 ‘e:\oralce\log01.dbf’
archivelog ;
2)        alter database db_name
add logfile group 4 ‘f:/oracle/log4.daf’        ;
3)        alter database db_name
drop        logfile group 4 ;
三、加快資料收索速度
1、        建立索引
create [unique] index  index_name
                on table_name (欄位1、欄位2 )
                tablespace tablespace_name ;
2、        若表經常被刪除、修改 應刪除並重建索引
3、        人工強制索引
select  *  /*+ index (欄位1) */  from table_name  
4、        建資料簇
create cluster cluster_name (id interger )
                                size 50
                                hash is id
                                hashkey 500 ;
create table table_name
( 定義子段 …… ) cluster cluster_name （）
5、        平行處理
平行處理機制適用於多機處理機制，它能使用分類、串連、表查詢、建立索引等操作平行處理，這對於大型表，尤其是多個大型表的處理十分有效。
Parallel_max_servers = 50
Parallel_min_servers = 5
Parallel_server_idle_time = 10 (單位是分鐘)
並行度= Parallel_default_size
                 = 表中的行數/ Parallel_default_scansize
最大並行度 = Parallel_default_max_scan = 50           

建立表時加上parallel 選項：
                create table table_name
                (        欄位1，
                        ……
)
parallel  15

                        select --+parallel ( table_name, 50)
from  table_name ;
                        select --+noparallel ( table_name)
from  table_name ;
                對索引平行處理：
                alter table table_name
                        enable primary key
                        using index parallel
                alter table table_name
                        enable primary key
                        using index noparallel

四、防止存取違規
1、        加鎖
lock table  table_name | view_name
                        in lock_type  mode  [ nowait ]
其中lock_type 包括 row share , row exclusive ,share updata , share, exclusive, share row exclusive .

[2002-3-12]
在資料庫中對oracle資料庫物件的常用操作
一、對象：        表、視圖、快照、索引、簇、序列、同義字、資料庫鏈、預存程序、函數、觸發器和包、聚集。

1、        table :
建表：
create table table_name         (
                field_name_1  type( length ) [not null] ,
                ……
                unique ( )
        primary key ( )
                foreign key( )  references  other_table.field on delete/updata cascade
……
) tablespace  tablespace_name;

create table  table_name as
         select * from other_table

查詢
select field1, field2,…… from table_name
where          條件
group by  field
having    條件
orader by  field  asc| desc        

刪除：
drop table table_name ;
修改：
alter table table_name
                add (field_1  type  [not null ],
                        ……)
alter table table_name
                modify (field_1  type  [not null ],
…… )
        insert into table_name (field1,field2,field3 )
values(val1,val2,val3);

insert into table_name select * from table2

update table_name field set field = values where ……

alter table table_name  add  constraints  pk_name  primary key (“field_name”) ;

alter table table_name  drop constraints  pk_name;

alter table child_table_name add constraints fk_name foreign key ( “child_field_name.field ”) references parent_table_name ( parent_table_name ) on delete cascade ;

alter table child_talbe_name drop constrints fk_name cascade ;

2、        視圖
建視圖
create view view_name as
                        select * from table_name  
                刪除視圖
                drop view view_name
3、        快照
建快照
create snapshot snapshot_name
build immediate
using index tablespace ‘tablespace_name’ storage (initial 12k next 12k pctincrease 10)
refresh [complete/force/fast] with [rowed/primary key]
using local rollback segment “rbs0”
on [commit/demand]  for update
as select * from table_name

4、        索引
create index index_name on table table_name(field1,field2) ;
                drop  index index_name ;
5、        簇
create cluster cluster_name (val type )
size  100
storage (  )
[index][hashkeys 200] ;

                create table tab_1 (
field1  type
……)
cluster cluster_name ;

6、        序列
create sequence sequence_name
increment by 1
start with 1
maxvalue 1024
minvalue 1
nocycle/cycle
cache 22
noorder/order
7、        同義字
create synonym synonym_name for view/table ;
create synonym synonym_name for view/table@
8、        資料連線
Create database link link_name connect to user_name identified by password using ‘nstude’                       
9、        預存程序  
create[or replace] procedure procdure_name
( <參數1> in/out <type> ,
……       
)
is | as
內部變數1        type;
內部變數2        type;
begin
……
end

運行：        execute procdure_name
查看參數                print  參數1

10、        分區
create table table_name(
field1  type ,
field2  type                )
partition by range (field1)
        ( partition part1 values less than (value )
                tablespace        tableapace_name1,         
         partition part1 values less than (value )
                tablespace        tableapace_name2,         
         partition part1 values less than (value )
                tablespace        tableapace_name3
)         

11、        函數
create [or replace] function function_name
( <參數1> in/out <type> ,
……       
)
return  type
is | as
內部變數1        type;
內部變數2        type;
begin
……
end

execute        value := function_name(參數1) ；

12、        觸發器

13、        包

二、資料庫安全
1、        角色
create role  role_name not identified
create role  role_name identified by password
create role  role_name identified externally
create role  role_name identified globally
grant 許可權        to role_name ;

2、        使用者
建立使用者：
create user user_name
profile “default”
identified by “password”
default tablespace        tablespace_name
temporary tablespace  temp_tablespace_name
account unlock
修改使用者：
alter user user_name        

三、資料存放區
1、        資料表空間
create tablespace tablespace_name
logging datafile  'D:\ORACLE\ORADATA\ORASTUDY\TEST.ora' size 5M
default storage  (initial 10k next 10k pctincrease 20)

alter tablespace  tablespace_name default storage (initial 40k)
alter database rename file ‘path\filename.ora’to ‘path1\filename1.ora’
alter database datafile ‘path\filename.ora’ resize 7m
alter database tablespace_name minimum extent 25k
alter database  tablespace_name online
alter database  tablespace_name  offline [normal/temporary/immediate]
註：normal/temporary/immediate 三個參數的選擇參考  p295

2、        復原段
create rollback segment rollback_name tablespace “RBS”
storage ( INITIAL 10K NEXT 10K OPTIMAL 13K MINEXTENTS 5 MAXEXTENTS 120)
alter rollback  segment rollback_name online
alter rollback  segment rollback_name offline

注 資料表空間建好後是離線的，需要將其改為線上。

3、        歸檔重做日誌組
增加日誌組
alter database add logfile group n （’path\logn.ora’）size 1024k
修改日誌組
alter database rename file （’path\logn.ora’）
        to  （’other_path\ other_logn.ora’）
增加日誌群組成員
alter database add logfile member ‘path\name_file.ora’to group n

四、備份與回複
        exp       
        imp
（一）冷備份：
1、        關閉資料庫
2、        建立備份路徑
3、        copy 資料檔案、控制檔案、系統初始設定檔案。
4、        開啟資料庫。
（二）熱備份：
1、        轉換到歸檔模式下 。
connect  internal
archive log start
查看歸檔模式的狀態：archive log list
        2、置為備份狀態。
Alter tablespace        temp        begin        backup
3、copy        資料檔案
        $        copy  path1\name1                        path2\name2 ；
5、        結束備份
alter tablespace temp                end         backup
6、        設定系統檢測點
alter system        checkpoint
        6、備份控制檔案
                alter database backup controlfile  to  path\name
        7、關閉archive log
                archive log stop

（三）資料回複
                用        V$log_history         可查看記錄檔資訊:
                RECID                  
                STAMP                  
                THREAD#                    線程號
                SEQUENCE#        序號      
                FIRST_CHANGE#    第一次變化號      
                FIRST_TIME       第一次變化時間      
                NEXT_CHANGE#     最後一次變化時間      
        1、不完全回複
        1)基於時間點的不完成回複
                connect internal
                startup mount  
                recover database        until time ‘02/19/99 12:43:55’;
                alter database open
        2)基於變化號碼的不完成回複
                connect internal
                startup mount       
                recover        database until change  12345 ;
                alter database open
        3)基於停止的恢複
                connect internal
                startup mount
                recover        database  ;
(當提示：<ret>= suggest | filename | auto | cancel )作出相應的選擇
                alter database open ;

2、        完全恢複
connect internal
startup mount
recover database        
alter database open
3、        僅對錶空間回複
recover tablespace tablespace_name
alter database open
4、        聯機恢複
當資料庫工作在archive模式下，當system資料表空間沒有損壞，方可進行聯機恢複。
Alter        tablespace tablespace_name offline ;
Recover tablespace tablespace_name ;
Alter tablespace tablespace_name online ;
5、        復原段恢複
create rollback segment rollback_name
tablespace  tablespace_name ;

savepoint savepoint_8

rollback to savepoint_8