Oracle學習筆記—(三)

Oracle學習筆記---(三)

三

一，鎖

為了防止使用者在同一時間並發地訪問和修改資源，ORACLE使用不同類型的鎖控制對資料的並發訪問，以防止使用者之間出現破壞性的互動操作
，oracle
為處理事務自動鎖定資源。

  鎖在SQL語句開始它們與資料的相互作用時獲得，並在事務的整個過程中有效

  oracle9i使用兩種鎖模式：
  .獨佔模式（排他）：不允許其他任何並發會話以任何方式共用鎖定定的資源，修改資料時需要這種鎖。
  .共用模式：允許對同一塊資料的並發讀訪問。在更改資料時，上升為獨佔模式

一)行級鎖
      insert    update   delete   隱式加行鎖(排他)
      select ...
for  update  顯示加行鎖(共用)

      select ...for update
用於顯示鎖定將要更新的資料行，防止其他使用者在更新之前操作此行
                如：select * from emp where
deptno=30  for  update
                    update   emp set ename='Joke'
where  empno=7499;
  

            在鎖釋放之前，其他使用者不可以對鎖定的資料行進行(修改，刪除)操作，查詢可以
  
      假如有其他使用者要鎖定同一資源：可以使用wait
子句對鎖的等待時間控制
        如：  在另一使用者中：select * from emp where
deptno=30  for  update  wait  2  (等待2秒 )
      如2秒鐘還未釋放資源，系統將會給出提示資訊

  
  
二)表級鎖

    共用模式（in share mode)
    共用更新模式（in share update
mode)
    獨佔鎖定模式

    鎖定表的通用文法：

    lock  table  表名  in  <share  or  share  update  or  exclusive  mode>;

    1) 共用模式
       不允許其他使用者插入，更新和刪除行,多個使用者可以同時在同一表上設定共用鎖定，這樣設定鎖的多個使用者都只能執行查詢  

    
           lock  table emp  in  share  mode;

    2）共用更新模式（in share update mode)
       允許多個使用者同時鎖定表的不同行,
允許其他使用者進行DML(insert update delete select)操作 , 除了鎖定的行            

      如：
lock  table emp  in  share update  mode;
                      select
*  from  emp  where deptno=30  for update        //鎖定的行
        其他使用者不能delete
,update 部門30的僱員資訊
          
      其他使用者可以查看鎖定的行:   select
*  from  emp  where deptno=30  

    3）獨佔鎖定模式(限制性強)
       不允許其他使用者插入，更新和刪除行,
允許查看資料，但只有一個使用者可以在表中放置獨佔鎖定
        
           lock  
table  emp  in  exclusive mode;
    
三) 死結

    如：USERA: lock table scott.emp  in  share  mode;    
        USERB:
lock table scott.emp  in   share  mode ;
      
      
USERA:  update  scott.emp set ename='Smith'  where empno=7369;
      
USERB:  update  scott.emp set job='CLERK' where   empno=7521;
      
發生死結

二，表分區

    分區的類型：
     1)，定界分割: 分區基於某一特定列或一組列的值的範圍
     2)，散列分區：資料基於hash
函數進行分區
     3)，複合分區：首先基於定界分割，然後使用HASH函數進一步劃分為子分區
     4),
列表分區：通過在每個分區的描述中指定分區鍵的離散值列表,
                  
允許按自然方式對無序和不相關的資料集進行分組和組織

  一)定界分割
  
  1)  建立分區表（一列）

       1,建立分區表(一列分區鍵empno)
         create table emp1(
        
empno  number(4),
         ename  varchar2(10),
         job  
varchar2(9),
         mgr   number(4),
         hiredate date,
        
sal  number(7,2),
         comm  number(7,2),
        
deptno  number(2))
         partition  by  range(empno)
        
(partition  p1   values  less than  (7566),
          partition  p2  
values  less than  (7900),
          partition  p3   values  less
than  (9999))
      
      2,插入資料
         insert into emp1 select *
from scott.emp;

      3,顯示分區資料
        select * from  emp1 partition(p1);
--分區鍵empno,小於7566的empno插入到p1分區
        select * from  emp1
partition(p2);
        select * from  emp1 partition(p3);

      4,資料字典
        col table_name for a15;
        col partition_name
for a15;
        select table_name,partition_name from
user_tab_partitions;

   2）建立分區表（兩列）
      1, 建立分區表（兩列deptno,empno）
         create table
emp2(
         empno  number(4),
         ename  varchar2(10),
        
job   varchar2(9),
         mgr   number(4),
         hiredate
date,
         sal  number(7,2),
         comm  number(7,2),
        
deptno  number(2))
            
partition  by  range(deptno,empno)
            (partition  om1   values  less
than  (20,7566), --分區邊界的左部分優先,當empno<7566時,deptno=20的記錄也將被插入
            
partition  om2   values  less than  (30,7900),
             partition  om3  
values  less than  (maxvalue,maxvalue))

      2, 插入資料
        insert into emp2  select * from emp;
      3,
顯示分區資料
         select * from  emp2 partition(om1);
         select *
from  emp2 partition(om2);
         select * from  emp2
partition(om3);

   3) 按日期類型分區
        create table
emp3(
        empno  number(4),
        ename  varchar2(10),
        job  
varchar2(9),
        mgr   number(4),
        hiredate
date,
        sal  number(7,2),
        comm  number(7,2),
        deptno  number(2))
        partition  by  range(hiredate)
        (partition  om1  
values  less than  (to_date('1980-12-31','yyyy-mm-dd')),
        
partition  om2   values  less
than  (to_date('1981-12-31','yyyy-mm-dd')),
         partition  om3  
values  less than  (to_date('1982-12-31','yyyy-mm-dd')))  

      
    二)散列分區  
       使用hash 函數將資料劃分到分區中
        create table
dept1(deptno  number(2),
                    dname  
varchar2(14),
                    loc  varchar2(13))
                    partition  by  hash(deptno)
                    (partition
p1,partition p2);
        insert into  dept1  select * from
scott.dept;

    三）複合分區
        create table salgrade1(grade number,losal number,hisal
number)
        partition by range(grade)
        subpartition  by
hash(losal,hisal)
              (
              partition p1 values  less
than (3)
                 (subpartition  sp1,subpartition
sp2),
              partition p2  values  less than (6)
                
(subpartition  sp3,subpartition sp4)
             )

        insert into  salgrade1  select * from  
scott.salgrade;

            select * from salgrade1
partition(p1);
            select * from salgrade1
subpartition(sp1);
            select * from salgrade1
subpartition(sp2);

   四)列表分區

     create table  emp4
    
(empno  number,
      ename  varchar2(10),
      deptno  number)
      partition  by  list(deptno)
      (
        partition  p1  values(10,20),
        partition  p2  values(30)
      )

     insert into emp4 select empno,ename,deptno from scott.emp;
    
select * from emp4 partition(p1);

三，維護分區
    
      --表emp1在其使用者所在的資料表空間中,建立使用者時指定，沒指定預設為系統資料表空間
        
1)在system使用者中授權：
          grant create tablespace to scott;
        
2)登入到scott使用者
          connect scott/tiger;    --假設在伺服器端，用戶端要加連接字串

         3)建立資料表空間test,該資料表空間包含一個資料檔案,大小是5M
          create tablespace  test
datafile        
                       'd:\ora1.dbf'  size 5m;

     1,移動分區
        
        alter table  emp1
                    
move  partition p1  tablespace  test;--將分區移動到資料表空間test
     2,添加分區
        
alter table emp3
                     add   partition om4  values less
than(to_date('1984-12-31','yyyy-mm-dd'));
              
    
3,刪除分區
         alter table emp3
                     drop  partition
om4;

     4,結合分區--用於散列分區
        
使用散列方法分區的表中，可以將某個分區的內容分發到由hash函數確定的一個或多個分區中，然後清除選定的分區
          alter  table
dept1  coalesce  partition;

     5，截斷分區
           刪除分區的資料（不能復原）
         alter table  emp3  
truncate  partition  om3;
         delete from emp3
partition(om1)  //可以復原

     6,
拆分分區
                原來的分區p2不存在了
                --p21將包括:7566到7700的記錄
                --p22將包括:7701到7900的記錄
              alter
table  emp1  split  partition  p2
at(7700)  
              into(partition  p21,partition  p22);

    
7,合并分區
        合并定界分割表中的相鄰兩個分區的內容，產生新的分區p2，原來的分區p21,p22不存在了
        alter
table emp1  merge partitions  p21,p22  into  partition
p2;                                            
          
    
8,交換表分區
       非分區表的資料和資料分割資料表的某個分區資料進行交換
       要求：非分區表的結構要和資料分割資料表相同
      
如：
       1，建立分區表
            create table  
Pdept(deptno  number(2),
                    dname  
varchar2(14),
                    loc  varchar2(13))
                    
partition  by  range(deptno)
                     (partition p1  values less
than (20)  tablespace test,    --指定分區所在的資料表空間
                      partition
p2  values less than (maxvalue)  tablespace test);
          
      
2,插入（分區表）資料
            insert into  Pdept  select * from dept;

       3,建立非分區表
            create table Sdept as select * from  dept
where 1=2;      

       4,插入（非分區表）資料
            insert into  Sdept
values(60,'MyDept','MyLoc');

       5,交換分區
           alter table Pdept exchange partition  p2
with  table  Sdept;