oracle教育訓練第四天

1.DML操作及名稱空間

模式與對象名稱空間的關系

模式(schema)是一種邏輯結構，它對應于使用者，每建一個使用者就有一套模式與之對應。

我們通常說對象的唯一辨別符是字首為模式名加上對象名稱，如scott.emp

同一模式下的對象是不可以重名的。比如在scott模式裡，表emp是唯一的，不能還有另外對象叫emp。但不同的模式下可以重名。

create table emp1 as select * from emp;

create view emp1 as select 1 as A from dual;

create unique index emp1 on emp1(empno);

alter table emp1 add constraint emp1 unique(empno);

名稱空間定義了一組對象類型，同一個名稱空間裡的不同對象不能同名，而不同的名稱空間中的不同對象可以共享相同的名稱。

表，視圖，序列，同義詞是不同類型的對象，但他們屬于同一名稱空間，是以在同一模式下也是不可以重名的。

索引，限制有自己的名稱空間，是以在同一模式下，可以由表A,索引A和限制A共存。

使用子查詢建立表

create table as subquery

表emp的索引不會被複制給表emp1

表emp中的限制隻有not null限制能夠帶到表emp1裡來

表emp中的default也不會被複制到emp1

改變表結構的DDL操作

drop和rename文法上要加column關鍵字。如drop列建議先使用set unused 然後drop unused column

當想要add一列，并限制該列為not null時，如果該表已經有資料了，則會報錯。

alter table emp1 add c number not null

alter table emp1 add  c number default 0 not null

alter table emp1 set unused column c;

select * from emp1

alter table emp1 drop unused column;

如果set unused 某列，該列上有索引，限制，并定義了視圖，引用過序列，結果如何？

索引和限制自動删除，序列無關，視圖保留定義。

create table test(id int ,name varchar(10));

create index idx_test_id on test(id);

alter table test add constraint unq_test_id unique(id);

create sequence seq_test start with 1 increment by 1;

create view vi_test as select id from test;

insert into test values(seq_test.nextval,'zzl');

commit;

select * from user_indexes where table_name ='TEST';

select * from user_constraints where table_name ='TEST';

select * from user_sequences ;

select * from user_views;

alter table test drop column id;

select * from vi_test

group by 産生統計報告

group by rollup

group by cube

grouping 

grouping_id

grouping set

select job,deptno,sal from emp1;

select job,deptno,sum(sal) from emp1 group by (job,deptno);

select job,deptno,sum(sal) from emp1 group by rollup(job,deptno);

select job,deptno,sum(sal) from emp1 group by cube(job,deptno);

grouping函數可以接受一列，傳回0或1，如果列為空，那麼grouping傳回1，如果列值非空，那麼傳回0，grouping隻能在使用rollup或cube的查詢中使用

select job,deptno,sum(sal),grouping(job),grouping(deptno), grouping_id(job,deptno) from emp group by cube(job,deptno);

select deptno,job,mgr, avg(sal) from emp

group by grouping sets((deptno,job),(job,mgr))

select deptno,job,null, avg(sal) from emp

group by deptno,job

union all

select null,job,mgr,avg(sal) from emp

group by job,mgr

oracle分層查詢

oracle是一個關系資料庫管理系統，它用表的形式組織資料，在某些表中的資料還呈現出樹形結構的聯系，

例如，emp表，empno和mgr兩列，通過這兩列反映出來的就是雇員之間上司和被上司的關系。他們之間的這種

關系就是一種樹結構。

周遊有兩個方向

top-down

down_top

select ...

connect by {prior 列名1=列名2|列名1 = prior 列名2}

[start with];

select level,empno,ename,mgr from emp

connect by prior empno = mgr

start with empno =7839 order by level;

connect by  empno = prior mgr

start with empno =7369 order by level;

select lpad(level,level*3,' '),empno,ename,mgr

from emp

start with ename ='KING' order by level

節點和分支的裁剪

在對樹結構進行查詢時，可以去掉表中的某些行，也可以剪掉樹中的一個分支，使用where子句來限定

樹形結構中的單個節點，以去掉樹中的單個節點，但他卻不影響後代節點（top_down）或前輩節點(down_top)

where ename <>'SCOTT'

and ename <> 'SCOTT'

2.oracle的事務和鎖

事務的ACID屬性

原子性(atomicity):事務是一個完整的操作，事務的各步操作是不可分的；要麼都執行，要麼都不執行。

一緻性（consistency）：一個查詢的結構必須與資料庫在查詢開始的狀态一緻（讀不等待寫，寫不等待讀）

隔離性（isolation）:對于其他會話來說，未完成的（也就是未送出的）事務必須不可見

持久性（durability）:事務一旦送出完成後，資料庫就不可以丢失這個事務的結果，資料庫通過日志能夠保持事務的持久性。

事務的開始和結束

事務采用隐形的方式，起始于session的第一條dml語句

事務結束于：

commit或rollback

ddl語句被執行（送出）

dcl語句被執行（送出）

使用者退出sqlplus（正常退出是送出，非正常退出是復原）

機器故障或系統崩潰(復原)

shutdown immediate(復原)

oracle的事務儲存點功能

savepoint 指令允許在事務進行中設定一個标記（儲存點），這個标記可以控制rollback的效果，

即在一個事務中復原掉最近的部分dml語句，保留下儲存點之前的dml語句，并使事務本身繼續執行，

也就是說復原到儲存點這個動作并不使事務結束。

insert into test values(1,'a'); 

savepoint A; 

insert into test values(2,'b');

savepoint B; 

insert into test values(3,'c');

savepoint C; 

rollback to A; 

commit; 

scn的概念

scn：system change number

它是一個不斷增長的整數，相當于oracle内部的一個時鐘，隻要資料庫一有變更，這個scn就會+1

oracle通過scn記錄資料庫裡事務的一緻性。scn涉及了執行個體恢複和媒體恢複的核心概念，它幾乎無處不在，

控制檔案，資料檔案，日志檔案都有scn。包括block上也有scn。

select current_scn from v$database;

select dbms_flashback.get_system_change_number from dual;

共享鎖與排他鎖的基本原理

排他鎖，排斥其他排他鎖和共享鎖

共享鎖，排斥其他排他鎖，但不排斥其他共享鎖

oracle資料庫鎖分類

dml鎖（data locks 資料鎖）用于保護資料的完整性

ddl鎖（dictionary locks 字典鎖）用于保護資料對象的結構 如表，索引等的結構定義

内部鎖和闩（internal locks and latches）保護資料庫的内部結構

當一個使用者對某表做dml操作時，也會加ddl鎖，這樣在事務未結束前，可防止另一使用者對該表做ddl操作。初始化參數ddl_lock_timeout可以

設定了ddl鎖的等待時間。時間過後如果事務仍未結束，則顯示資源正忙。

當一個使用者對某表做DDL操作時，也會加DML鎖，這樣可以防止另一個使用者對該表做DML操作

oracleDML鎖，包括tm和tx兩種

tm是面向對象的鎖，它表示鎖定了系統中的一個對象，在鎖定期間不允許其他人對這個對象做ddl操作。

tx是面向事務的鎖，他表示發起了一個事務，是否有事務産生，這是根據是否使用undo段作為評斷标準的。

五種tm表鎖的含義

row share 行共享(RS)允許其他使用者同時更新其他行，允許其他使用者同時加共享鎖，不允許有獨占（排他性質）的鎖

row exclusive 行排他（RX） 允許其他使用者同時更新其他行，隻允許其他使用者同時加行共享鎖或者行排他鎖

share 共享（S） 不允許其他使用者同時更新任何行，隻允許其他使用者同時加共享鎖或者行共享鎖

share row exclusive(srx) 共享行排他，不允許其他使用者同時更新其他行，隻允許其他使用者同時加行共享鎖

exclusive(X)排他，其他使用者禁止更新任何行，禁止其他使用者同時加任何排他鎖

sql語句                                         加鎖模式    許可其他使用者的加鎖模式

selec * from emp1                                  無       RS,RX,S,SRX,X

insert,update,delete                               RX       RS,RX

select * from emp1 for update                      RX       RS,RX

lock table emp1 in row share mode                  RS       RS,RX,S,SRX

lock table emp1 in row exclusive mode              RX       RS,RX

lock table emp1 in share mode                      S        RS，S

lock table emp1 in share row exclusive mode        SRX      RS

lock table emp1 in exclusive mode                  X        無

加鎖模式

自動加鎖

做DML操作時，如insert,update,delete 以及select ... for update 由oracle自動完成加鎖

scott

select * from emp1 for update

sys

update scott.emp1 set ename ='SCOTT' where empno =7788

select * from emp1 where deptno =10 for update

select * from emp1 for update nowait

select * from scott.emp1 for update wait 5

select * from scott.emp1 for update skip locked

對整個表for udpate是不鎖insert語句的

死鎖和解鎖

plsql

update emp1 set ename ='aaa' where empno =7788;

update emp1 set ename = 'bbb' where empno = 7369;

sqlplus

select sess.sid, 

    sess.serial#, 

    lo.oracle_username, 

    lo.os_user_name, 

    ao.object_name, 

    lo.locked_mode 

    from v$locked_object lo, 

    dba_objects ao, 

    v$session sess 

where ao.object_id = lo.object_id and lo.session_id = sess.sid; 

alter system kill session 'sid,serial#'

3.使用者通路控制

建立和管理資料庫使用者

檢視資料庫使用者

select * from dba_users;

模式 schema

show user

使用者預設表空間

select * from v$tablespace

create user zzl identified by bsoft;

預設表空間

select * from database_properties

alter  database default tablespace tablespace_name

空間配額

配額 (quota)是表空間中為使用者的對象使用的空間量

alter user zzl quota 10M on users;

alter user zzl quota

alter user zzl quota 0 on users;

管理概要檔案(profile)

作用是對使用者通路資料庫做一些限制

概要檔案（profile）具有兩個功能，一個是實施密碼限制，另一個是限制會話可以占用的資源

始終要實施密碼控制，而對于資源限制，則隻有執行個體參數resoure_limit為true時才會實施，預設是false

系統自動使用概要檔案，有一個預設的default profile 限制很松，作用很小

可以使用create profile為使用者建立它自己的概要檔案，沒有說明的參數就從default profile的目前版本中提取

create profile zzl limit failed_login_attempts 2;

alter user zzl1 profile zzl;

zzl1用錯誤密碼嘗試登陸3次

drop profile zzl cascade

alter user zzl1 account unlock;

權限的引入

系統安全：使用者名和密碼，配置設定給使用者的磁盤空間及使用者的系統操作，如profile等

資料庫安全：對資料庫對象的通路及操作

使用者具備系統權限才能夠通路資料庫

具備對象權限才能通路資料庫中的對象

系統權限通常由dba授予

典型dba權限

create user

drop user

backup any table

select any table

create any table

典型使用者需要的系統權限

create session

create table

create sequence

create view

create procedure

對象權限

對象權限有8種：alter,delete,execute,index,insert,references,select ,udpate

權限的授權

授予系統權限文法

grant sys_privs,[role] to user|role|public [with admin option]

授予對象權限文法

grant object_privs on object to user|role|public [with grant option]

系統權限和對象權限文法格式不同，不能混合使用

grant create table ,select on emp1 to zzl1;

建立和删除角色

create role myrole

drop role myrole

角色的引入：

系統權限太複雜，将系統權限打包成角色，oracle建議通過角色授權權限，目的就是為了簡化使用者通路管理

權限傳遞

系統權限級聯

create user zzl1 identified by bsoft;

create user zzl2 identified by bsoft;

grant create session to zzl1 with admin option;

--zzl1

grant create session to zzl2;

嘗試用zzl2登入

revoke create session from zzl1;

嘗試用zzl1和zzl2登入

對象權限級聯

grant select on emp1 to zzl1 with grant option;

grant select on emp1 to zzl2;

select * from scott.emp1;

--zzl2

--scott

revoke select on emp1 from zzl1;

與權限有關的資料字典

select * from session_privs;//使用者目前會話擁有的系統權限

select * from user_role_privs;//使用者被授予的角色

select * from role_sys_privs;//使用者目前擁有的角色的系統權限

select * from user_sys_privs;//直接授予使用者的系統權限

select * from user_tab_privs;//授予使用者的對象權限

select * from role_tab_privs;//授予角色的表的權限

4.邏輯導入導出

傳統的導入導出 imp/exp

導出程式exp将資料庫中的對象定義和資料備份到一個作業系統二進制檔案中

導入程式imp讀取二進制導出檔案并将對象和資料載入資料庫中

傳統的導入導出是基于用戶端的

exp/imp特點

1.可以按時間儲存表結構和資料

2.允許導出指定的表，并重新導入到新的資料庫中

3.可以把資料庫遷移到另一台異構伺服器上

4.在兩個不同版本的oracle資料庫之間

5.在聯機狀态下進行備份和恢複

6.可以重新組織表的存儲結構，減少連結及磁盤碎片

使用以下三種方法調用導出和導入實用程式

1.互動提示符：以互動的方式提示使用者逐個輸入參數的值

2.指令行參數：在指令行指定程式的參數和參數值

3.參數檔案：允許使用者将運作參數和參數值存儲在參數檔案中，以便重複使用參數。

導出導入資料庫對象的四種模式

1.資料庫模式：導出和導入整個資料庫中的所有對象

2.表空間模式：導出和導入一個或多個指定的表空間中的所有對象

3.使用者模式：導出和導入一個使用者模式中的所有對象

4.表模式：導出和導入一個或多個指定的表或表分區

導入導出表

exp scott/bsoft file =D:\scott.dmp tables=(emp1,dept1)

drop table emp1 purge;

drop table dept1 purge;

imp scott/bsoft file = D:\scott.dmp

exp sys/bsoft  file = D:\scott.dmp tables =(scott.emp1,scott.dept1)

imp  'sys/bsoft as sysdba' file = D:\scott.dmp fromuser = scott

導入導出使用者

exp scott/bsoft file =D:\scott.dmp owner=scott

drop user scott cascade

grant connect,resource to scott identified by bsoft

imp scott/bsoft file =D:\scott.dmp

或者

imp  'sys/bsoft as sysdba' file = D:\scott.dmp fromuser = scott touser=scott

imp  'sys/bsoft as sysdba' file = D:\scott.dmp fromuser = scott touser=zzl

導入導出表空間

create tablespace test  datafile 'E:\database\test.dbf' size 20 M  

create table test (id int)  TABLESPACE test

insert into test

select 1 from dual;

commit;  

--驗證表空間是否可傳輸

 exec dbms_tts.transport_set_check('test', true, true);

 SELECT * FROM TRANSPORT_SET_VIOLATIONS;

alter tablespace test   read only

exp '/ as sysdba' tablespaces = test transport_tablespace = y file =e:\database\test.dmp

imp userid ='/ as sysdba' tablespaces=test file =e:\database\test.dmp transport_tablespace = y  datafiles =e:\database\test.dbf

alter tablespace test read write

可傳輸表空間需要滿足幾個前提條件

1.原庫和目标庫字元集要一緻，若不一緻可以通過轉換環境變量NLS_LANG解決

2.字元序有大端和小端之分

select * from v$transportable_platform,如果不一緻可以使用rman轉換

3.compatible 10.0.0 或更高

4.遷移的表空間要自包含（目前表空間中的對象不依賴于該表空間之外的對象）

test表空間中有表T1，T1上的索引T1_indx在users表空間上，索引依賴于表

test是自包含的，導出丢棄T1_indx.

users不是自包含的，無法導出

檢查表空間是否自包含

execute ddms_tts.transport_set_check('USERS');

select * from transport_set_violations;

導出整個資料庫的對象

exp 'sys/bsoft as sysdba' file = D:\scott.dmp full=y

資料泵

資料泵優點

1.改進性能（較傳統的exp/imp速度提高1到2個數量級）

2.重新開機作業能力

3.并行執行能力

4.關聯運作作業能力

5.估算空間需求能力

6.操作網絡方式

資料泵組成

核心部分程式包：DBMS_DATAPUMP

提供中繼資料的程式包:DBMS_MATADATA

指令行客戶機：expdp，impdp

資料泵檔案

轉儲檔案：此檔案包含對象資料

日志檔案：記錄操作資訊和結果

SQL檔案：将導入作業中的DDL語句寫入sqlfile指定的參數檔案中

資料泵的目錄及檔案位置

以sys或system完成資料泵的導入導出時，可以使用預設的目錄DATA_PUMP_DIR

select * from dba_directories

若果設定了環境變量ORACLE_BASE,則預設目錄位置是

$oracle_base/admin/database_name/dpdump

否則是

$oracle_home/admin/database_name/dpdump

資料泵的兩種操作方式

使用expdp和impdp

create directory dir_dp as 'E:\database'; 

Grant read,write on directory dir_dp to scott;

expdp scott/bsoft directory = dir_dp  dumpfile = scott.dmp tables=(emp,dept)

impdp scott/bsoft directory = dir_dp  dumpfile = scott.dmp 

導出資料但不導出結構

expdp scott/bsoft directory = dir_dp  dumpfile = scott.dmp tables=(emp,dept) content = data_only reuse_dumpfiles=y

導出scott使用者

expdp scott/bsoft directory = dir_dp  dumpfile = scott.dmp schemas=scott

//與exp的差別owner換成了schemas

使用oem操作資料泵