Oracle資料庫管理

轉載：http://www.cnblogs.com/BeautyOfCode/archive/2010/09/21/1832205.html

一、資料庫管理員

概述：每個Oracle資料庫應該至少有一名資料庫管理員（dba），對於一個小的資料庫，一個dba就夠了，但是對於一個大的資料庫可能需要多個dba分別擔負不同的管理職責。

職責：

1、安裝和升級Oracle資料庫。

2、建庫、資料表空間、表、視圖、索引……

3、指定並實施備份與恢複計劃。

4、資料庫許可權管理，調優，故障排除。

5、對於進階資料庫管理員，要求能參與項目開發，會編寫Sql語句，預存程序，觸發器，規則，約束，包。

 

管理資料庫的使用者主要是sys和system。

這兩個使用者的主要區別是：

1、儲存資料的重要性不同

sys：所有Oracle的資料字典的基表和視圖都存放在sys使用者中，這些基表和視圖對於Oracle的運行時至關重要的，由資料庫自己維護，任何使用者都不能手動更改。sys使用者擁有dba，sysdba，sysoper角色或許可權。

system：用於存放次一級的內部資料，如Oracle的一些特性或工具的管理資訊。system使用者擁有dba，sysdba角色和系統許可權。

2、許可權不同

sys：sys使用者必須以as sysdba或assysoper形式登入，不能以normal方式登入資料庫。

system：如果正常登入，它其實就是一個普通的dba使用者，但是如果以as sysdba登入，其結果實際上它是作為sys使用者登入的，從登入資訊裡面我們可以看出。

 

sysdba和sysoper角色區別圖

系統角色	sysdba	sysoper
區別	startup（啟動資料庫）	startup
	shutdown（關閉資料庫）	shutdown
	alter database open/mount/backup	alter database open/mount/backup
	改變字元集	none
	create database（建立資料庫）	create database
	drop database（刪除資料庫）	drop databse
	create spfile	create spfile
	alter database recover（回複資料庫，可以是部分）	只能全部恢複，不能執行部分恢複
	alter database archivelog（歸檔日誌）	alter database archivelog
	restnicted session（會話限制許可權）	restnicted session
	可以讓使用者作為sys使用者串連	可以驚醒一些基本操作，但不能查看使用者資料
	登入之後使用者是sys	登入之後使用者是public

 

dba角色的許可權

dba使用者是指具有dba角色的資料庫使用者，特權使用者可以執行啟動執行個體，關閉執行個體等特殊操作，而dba使用者只有在啟動資料庫後才能執行各種管理工作。

 

管理初始化參數

概述：初始化參數用於設定執行個體或是資料庫的特徵。Oracle提供了200多個初始化參數，並且每個初始化參數都有預設值。

1、顯示初始化參數

show parameter命令

2、如何修改參數

如果你希望修改這些初始化的參數，可以到檔案X:\Oracle\admin\myora1\pfile\init.ora檔案中修改。

 

二、資料庫（表）的邏輯備份與恢複

概述：邏輯備份時指使用工具export將資料對象的結構和資料匯出到檔案的過程，邏輯恢複時指當資料庫物件被誤操作而損壞後使用工具import利用備份的檔案把資料對象匯入到資料庫的過程，物理備份即可在資料庫open的狀態下進行也可在關閉資料庫後進行，但邏輯備份和恢複只能在open的狀態下進行。

1、匯出

匯出具體分為：匯出表、匯出方案、匯出資料庫三種方式。

匯出使用exp命令來完成的，該命令常用的選項有：

userid：用於指定執行匯出操作的使用者名稱、口令、連接字串

tables：用於指定執行匯出操作的表。

owner：用於指定執行匯出操作的方案。

full=y：用於指定執行匯出操作的資料庫。

inctype：用於指定執行匯出操作的增量類型。

rows：用於指定執行匯出操作是否要匯出表中的資料。

file：用於指定匯出檔案名稱。

 

（1）匯出表

a、匯出自己的表

exp userid=scott/tiger@MyOra tables(emp,dept)file=d:\tabl.dmp

 

b、匯出其他方案的表

如果使用者要匯出其他方案的表，則需要dba的許可權或是exp_full_database的許可權，比如system就可以匯出scott的表。

exp userid=system/manager@MyOratables(scott.emp,scott.dept) file=d:\tabl.dmp

 

c、匯出表的結構

exp userid=scott/tiger@MyOra tables=(emp,dept)file=d:\tabl.dmp rows=n

 

d、使用直接匯出方式

exp userid=scott/tiger @MyOra talbes=(emp,dept)file=d:\tabl.dmp direct=y

這種方式比預設的常規方式速度要快，當資料量大時，可以考慮使用這樣的方法。這時需要資料庫的字元集要與用戶端字元集完全一致，否則會報錯。

 

（2）匯出方案

匯出方案是指使用export工具匯出一個方案或是多個方案中的所有對象（表、索引、約束）和資料，並存放到檔案中。

a、匯出自己的方案

exp userid=scott/tiger@MyOra owner=scott file=d:\scott.dmp

 

b、匯出其他方案

如果使用者要匯出其他方案，則需要dba的許可權或是exp_full_database的許可權，比如system就可以匯出任何的方案

exp userid=system/manager@MyOra owner=(system,scott)file=d:\owner.dmp

 

（3）匯出資料庫

匯出資料庫是指利用export匯出所有資料庫的對象及資料。要求該使用者具有dba角色或是exp_full_database許可權。

exp userid=system/manager@MyOra full=y inctype=completefile=d:\database.dmp

 

2、匯入

匯入就是使用import將檔案中的對象和資料匯入到資料庫中，但是匯入要使用的檔案必須是export所匯出的檔案，與匯出相似，匯入也分為匯入表，匯入方案，匯入資料庫三種方式。

imp常用的選項有

userid：用於指定執行匯入操作的使用者名稱，口令，連接字串。

tables：用於指定執行匯入操作的表。

formuser：用於指定源使用者。

touser：用於指定目標使用者。

file：用於指定匯入檔案名稱。

full=y：用於指定執行匯入整個檔案。

inctype：用於指定執行匯入操作的增量類型。

rows：指定是否要匯入表行（資料）。

ignore：如果表存在，則只匯入資料。

 

（1）匯入表

a、匯入自己的表

imp userid=scott/tiger@MyOra tables=(dept,emp)file=d:\tabl.dmp

 

b、匯入表到其他使用者

要求該使用者具有dba的角色，或是imp_full_database的許可權。

imp userid=system/tiger@MyOra tables=(dept,emp)file=d:\tabl.dmp

 

c、匯入表的結構

只匯入表的結構而不匯入資料。

imp userid=scott/tiger@MyOra tables=(dept,emp)file=d:\tabl.dmp rows=n

 

d、匯入資料

如果對象（比如表）已經存在可以只匯入表的資料。

impu userid=scott/tiger@MyOra tables=(dept,emp)file=d:\tabl.dmp ignore=y

 

（2）匯入方案

匯入方案是指使用import工具將檔案中的對象和資料匯入到一個或是多個方案中。如果要匯入其他方案，要求該使用者具有dba的角色，或是imp_full_database許可權。

a、匯入自己的方案

imp userid=scott/tiger file=d:\owner.dmp

 

b、匯入其他方案

要求該使用者具有dba的許可權

imp userid=system/manager file=d:\owner.dmpfromuser=system touser=scott

 

c、匯入資料庫

在預設情況下，當匯入資料庫時，會匯入所有對象結構和資料。

imp userid=system/manager full=y file=d:\database.dmp

 

三、資料字典和動態效能檢視

1、資料字典：資料字典是Oracle資料庫中最要的組成部分，它提供了資料庫的一些系統資訊，它是唯讀表和視圖的集合，資料字典的所有者為sys使用者。使用者只能在資料字典上執行查詢操作，而其維護和修改時由系統自動完成的。

資料字典的組成：

（1）資料字典基表。用來儲存資料庫的基本資料，普通使用者不能直接存取資料字典的基表。

（2）資料字典視圖。資料字典視圖時基於資料字典基表所建立的視圖，普通使用者可以通過查詢資料字典視圖取得系統資訊。資料字典主要包括user_xxx，all_xxx，dba_xxx三種類型。

動態效能檢視：動態效能檢視記載了常式啟動後的相關資訊。

A、user_tables

用於顯示目前使用者所擁有的所有表，它只返回使用者所對應方案的所有表。

sql>select table_name from user_tables;

 

B、all_tables

用於顯示目前使用者可以訪問到的所有表，它不僅會返回目前使用者方案的所有表，還會返回目前使用者可以訪問到的其它方案的表。

sql>select table_name from all_tables;

 

C、dba_tables

它會顯示所有方案擁有的資料庫表，但是查詢這種資料庫字典視圖，要求使用者必須是dba角色或是有select any table系統許可權。

例如：當用System 使用者查詢資料字典視圖dba_tables時，返回system,sys,scott……發方案所對應的資料庫表。

 

2、使用者名稱，許可權，角色

在建立使用者時，Oracle會把使用者的資訊存放到資料字典中，當給使用者授予許可權或是角色時，Oracle會將許可權和角色的資訊存放到資料字典。

通過查詢dba_users可以顯示所有資料庫使用者的詳細資料。

通過查詢dba_sys_privs可以顯示使用者所具有的系統許可權。

通過查詢dba_tab_privs可以顯示使用者所具有的對象許可權。

通過查詢dba_col_privs可以顯示使用者具有的列許可權。

通過查詢dba_role_privs可以顯示使用者所具有的角色。

 

（1）查詢Oracle中所有的系統許可權，一般是dba

sql>select * from system_privilege_map order by name;

（2）查詢Oracle中所有的角色，一般是dba

sql>select * from dba_roles;

（3）查詢Oracle中所有對象許可權，一般是dba

sql>select distinct privilege from dba_tab_privs;

（4）查詢資料庫的資料表空間

sql>select tablespace_name from dba_tablespaces;

（5）查詢一個角色，包括哪些許可權

a、一個角色包含的系統許可權

方法一：sql>select * from dba_sys_privs where grantee='CONNECT';

方法二：sql>select * from role_sys_privs where role='CONNECT';

b、一個角色包含的對象許可權

sql>select * from dba_tab_privs wheregrantee='CONNECT';

（6）如何查詢某個使用者，具有什麼樣的角色

sql>select * from dba_role_privs where grantee='SCOTT'

（7）顯示目前使用者可以訪問的所有資料字典視圖

sql>select * from dict where comments like '%grant%';

（8）顯示當前資料庫的全稱

sql>select * from global_name;

 

3、動態效能檢視

動態效能檢視用於記錄當前常式的活動資訊，當啟動Oracle Server時，系統會建立動態效能檢視；當停止Oracle Server時，系統會刪除動態效能檢視。Oracle的所有動態效能檢視都是以v_$開始的，並且Oracle為每個動態效能檢視都提供了相應的同義字，並且其同義字是以V$開始的，例如v_$datafile的同義字為v$datafile；動態效能檢視的所有者為sys，一般情況下，由dba或是特權使用者來查詢動態效能檢視。

 

四、管理資料表空間和資料檔案

資料表空間是資料庫的邏輯組成部分，從物理上講，資料庫資料存放在資料檔案中；從邏輯上講，資料庫則是存放在資料表空間中，資料表空間有一個或多個資料檔案組成。

1、資料庫的邏輯結構

Oracle中邏輯結構包括資料表空間、段、區和塊。資料庫由資料表空間構成，而資料表空間又是由段構成，而段又是由區構成，而區又是由Oracle塊構成的這樣的一種結構，可以提供資料庫的效率。

 

2、資料表空間

資料表空間用於從邏輯上組織資料庫的資料，資料庫邏輯上是由一個或是多個資料表空間組成的。通過資料表空間可以達到以下作用。

（1）控制資料庫佔用的磁碟空間

（2）DBA可以將不同資料類型部署到不同的位置，這樣有利於提高I/O效能，同時利於備份和恢複等管理操作。

A、建立資料表空間

建立資料表空間是使用create tablespace命令完成的，需要注意是，一般情況下，建立資料表空間是特權使用者或是DBA來執行的，如果用其他使用者來建立資料表空間，則使用者必須要具有createtablespace的系統許可權。

 

B、建立資料資料表空間

在建立資料庫後，為便於管理表，最好建立自己的資料表空間。

sql>create space sp001 datafile='d:\sp001.dbf' size=20muniform size 128k;

說明：執行完上述命令後，會建立名稱為spoo1的資料表空間，並為該資料表空間建立名稱為sp001.dbf的資料檔案，區的大小為128K。

 

C、使用資料資料表空間

sql>create table MyDeparts(deptno numeber(4),dnamevarchar2(14),loc varchar2(13)) tabalespace sp001;

 

D、改變資料表空間的狀態

當建立資料表空間時，資料表空間處於聯機的（online）狀態，此時該資料表空間是可以訪問的，並且該資料表空間是可以讀寫的，即可以查詢該資料表空間的資料，而且還可以在資料表空間執行各種語句。但是在進行系統維護或是資料維護時，可能需要改變資料表空間的狀態。一般情況下有特權使用者或是DBA來操作。

（1）使資料表空間離線

sql>alter tablespace sp001 offline;

 

（2）事資料表空間聯機

sql>alter tablespace sp001 online;

 

（3）唯讀資料表空間

當建立資料表空間時，資料表空間可以讀寫，如果不希望在資料表空間上執行Update，Delete，Insert操作，那麼可以將資料表空間修改為唯讀

sql>alter tablespace sp001 read only;

 

（4）使資料表空間可讀寫

sql>alter tablespace sp001 read write;

 

部分案例:

1、知道資料表空間名，顯示該資料表空間包括的所有表

sql>select * from all_tables wheretablespace_name='EMP';

 

2、知道表名，查看該表屬於哪個資料表空間

sql>select tablespace_name,table_name from user_tableswhere table_name='EMP';

 

E、刪除資料表空間

一般情況下，由特權使用者或是DBA來操作，如果是其他使用者操作，那麼要求使用者具有drop tablespace系統許可權。

sql>drop tablespace sp001 including contents anddatafiles;

說明：including contents表示刪除資料表空間時，刪除該空間的所有資料庫物件，而datafiles表示將資料庫檔案也刪除。

 

F、擴充資料表空間

資料表空間是由資料檔案組成的，資料表空間的大小實際上就是資料檔案相加後的大小，那麼我們可以想象，假定表employee存放到data01資料表空間上，初始大小就是2M，當資料滿2M空間後，如果在向employee表插入資料，這樣就會顯示控制項不足的錯誤。

擴充資料表空間，為其增加更多的儲存空間，有三種方法。

（1）增加資料檔案

sql>alter tablespace sp001 add datafile 'd:\sp002.dbf'size 20m;

 

（2）增加資料檔案的大小

sql>alter tablespace sp001 'd:\sp001.dbf' resize 40m;

 

（3）設定檔案的自動成長

sql>alter tablespace sp001 'd:\sp002.dbf' autoextend onnext 10m maxsize 500m;

 

G、移動資料檔案

有時，如果你的資料檔案所在的磁碟損壞時，該資料檔案將不能再使用，為了能夠重新使用，需要將這些檔案的副本移動到其他磁碟，然後恢複。

下面以移動資料檔案sp001.dbf為例來說明

（1）確定資料檔案所在的資料表空間

sql>select tablespace_name from dba_data_files wherefile_name='d:\sp001.dbf';

（2）使資料表空間離線

確保資料檔案的一致性，將資料表空間轉變為offline的狀態

sql>alter tablespace sp001 offline;

（3）使用命令移動資料檔案到指定的目標位置

sql>host move 'd:\sp001.dbf ' 'c:\sp001.dbf';

（4）執行alter tablespace命令

在物理上移動了資料後，還必須執行alter tablespace命令對資料庫檔案進行邏輯修改

sql>alter tablespace sp001 rename datafile'd:\sp001.dbf' to 'c:\sp001.dbf';

（5）使得資料表空間聯機

在移動了資料檔案後，為了使使用者可以訪問該資料表空間，必須將其轉變為online狀態

sql>alter tablespace sp001 online;

 

H、顯示資料表空間資訊

查詢資料字典視圖dba_tablespaces，顯示資料表空間的資訊

sql>select tablespace_name from dba_tablespaces;

 

I、使用資料表空間所包含的資料檔案

查詢資料字典視圖dba_data_files，可以顯示資料表空間所包含的資料檔案

sql>select file_nam,bytes,from dba_data_files wheretabalespace_name='SP001';