玩轉oracle學習第四天

玩轉oracle學習第四天

1.上節回顧
2.數據庫管理員
3.數據庫的邏輯備份與恢復
4.數據字典和動態性能視圖
5.管理表空間和數據文件

1.了解oracle管理員的基本職責
2.掌握備份和恢復數據庫/表的方法
3.理解表空間，數據字典，性能視圖數據庫管理員dba,對於一個小的數據庫，一個dba就夠了，但是對於一個大的數據庫可能需要多個dba分別擔負不同的管理職責，一個dba的主要工作：
職責：
（1）安裝和升級oracle數據庫
（2）建庫，表空間，表，視圖，索引
（3）制定並實施備份與恢復計劃
（4）數據庫權限管理，調優，故障排除
（5）對於高級dba，要求能參與項目開發，會編寫sql語句，存儲過程，觸發器，規則，約束，包

管理數據庫的用戶主要是sys和system，sys是董事長，system是總經理
主要區別如下：
（1）最重要的區別，存儲的數據的重要性不同sys:所有oracle的數據字典的基表和視圖都存放在sys用戶中，這些基表和視圖對於oracle的運行時至關重要的，由數據庫自己維護，任何用戶都不能手動更改，sys用戶擁有dba，sysdba，sysoper角色或權限，是oracle權限最高的用戶

system:用於存放次一級的內部結構，如oracle的一些特性或工具的
管理信息，system用戶擁有dba，sysdab角色或系統權限

（2）其次區別，權限的不同sys用戶必須以as sysdba（角色）或as sysoper（角色）形式登陸，不能以normal方式登陸數據庫

system如果正常登陸，它其實就是一個普通的dba用戶，但是如果以
as sysdba登陸，其結果實際上他是作為sys用戶登陸的，從
登陸信息裡面我們可以看出來

sys用戶----------->方案------------------>存放oracle數據庫數據字典的基表和動態視圖
說明：一個用戶對應一個方案，然後每個方案都有自己的基表和動態視圖
sys擁有以下角色：
dba sysdba sysoper
每個角色有自己的權限，然後就會有自己操作，每個角色可以有多個權限
system用戶---------------->方案-------------->次級數據
system擁有以下角色：
dba sysdba
conn sys/change_on_install;//次登陸方式是錯誤
conn sys/change_on_install as sysdba;//正常登陸
conn system/manager;//system用戶以一個普通用戶登陸
conn system/manager as sysdba;//作為sys用戶登陸
角色sysdba sysoper所擁有的權限是不同的
startup
shutdown
alter database open
創建數據庫 不能創建數據庫
dba權限的用戶：
dba用戶是擁有dba角色的權限的用戶
權限角色大小比較：
sysdba > sysoper > dba
conn system/manager as sysdba;
shutdown;//關閉數據庫
startup;//啟動數據庫
總結：
兩個重要的用戶，三個重要的用戶角色
管理初始化參數
show parameter;//顯示數據庫的管理參數
數據庫（表）的邏輯備份與恢復
邏輯備份使用expport命令將數據對象進行備份，導出到磁盤
導入和恢復：使用impport
物理備份即可在數據庫open狀態下，也可以在關閉下備份，
但是邏輯備份只能在數據庫open狀態下進行
導出：
導出具體分為：導出表，導出方案，導出數據庫三種方式
導出使用exp命令來完成的，該命令常用的選項有：
userid:用於指定執行導出 操作的用戶名，口令，連接字符串
tables:用於指定執行導出操作的表
owner:用於指定執行導出操作的方案
full=y：用於指定執行導出操作的數據庫
inctype:用於指定執行導出操作的增量類型
rows:用於指定執行導出操作是否要導出表中的數據
file:用於指定導出文件名
詳細介紹：
特別說明：
在導入和導出的時候，要到oracle的bin目錄下進行，否則不行的
導出表：
(1)導出自己的表
exp userid=scott/tiger@myoral tables=(tab1,tab2,...) file=D:\e1.dmp
conn scott/tiger;
exp userid=soctt/tiger@myoral table=(emp) file=D:\emp.dmp
注意：導出的是二進制文件，導出時文件後綴類型不影響的
exp userid=scott/tiger@myoral tbale=(emp,student) file=twotable.dmp
（2）導出其他方案的表
如果用戶要導出其他方案的表，則需要dba的權限或是
exp_full_database的權限，比如system就可以導出scott的表
exp userid=system/manager@myoral table=(scott.emp) file=d:\e2.dmp
exp userid=system/manager@myoral tables=(scott.emp) file=D:\e3.dmp
(3)導出表的結構
exp userid=scott/tigger@accp tables=(emp，dept) file=D:\e4.dmp rows=n
(4)使用直接導出方式:導出數據的速度比較快
exp userid=scott/tiger@accp tbales=(emp) file=D:\e5.dmp direct=y
導出方案：
導出方案是指使用export工具導出一個方案或是多個方案中
的所有對象（表，索引，約束..）和數據，並存放在文件中
(1)導出自己的方案
exp scott/tiger@myoracl owner=scott file=D:\scott.dmp
(2)導出其他方案
如果用戶要導出其他方案，則需要dba的權限或是
exp_full_database的權限，例如system用戶就可以導出任何方案
exp system/manager@myoracle owner=(system,scott) file=D:\system.dmp
導出數據庫
導出數據庫是指利用export導出所有數據庫中的對象及數據，
要求該用戶具有dba的權限或是exp_full_database權限
exp userid=system/manager@myoracle full=y inctype=complete file=x.dmp
導入：
導入表
(1)導入自己的表
imp userid=scott/tiger@myoracle tables=(emp) file=D:\xx.dmp
(2)導入表到其它用戶
要求該用戶具有dba的權限，或是imp_full_database角色
imp userid=system/manager@myoracle tables=(emp) file=D:\emp.dmp
（3）導入表的結構
只導入表的結構而不導入數據
imp userid=scott/tiger@myoral1 tables=(emp) file=...
（4）導入數據
如果對象（比如表）已經存在可以只導入表的數據
imp userid=scott/tiger@myoral1 tables=(emp) file=D:\xxx.dmp ignore=y
導入數據庫
在默認情況下，當導入數據庫時，會導入所有對象結構和數據，案例如下：
imp userid=system/manager full=y file=D:\xxx.dmp
scott用戶 ------------》 scott的方案--------》方案中有很多東西，稱為數據對象
表
視圖
system用戶-------------------->system方案----------》數據對象---------》磁盤
數據字典和動態性能視圖
數據字典是oracle數據庫中最重要的組成部分，它提供了數據庫的一些系統信息
動態性能視圖記載了例程啟動後的相關信息
數據字典是一個數據對象，它放在system方案中
包括數據基表和動態數據字典視圖
基表：存放靜態數據
動態視圖：存放動態的數據
查詢表：
user_table;
用於顯示當前用戶所擁有的所有表，他只返回當前用戶所對應方案的所有表
select table_name from user_tables;
all_tables:
用於顯示當前用戶可以訪問的所有表，它不僅會返回
當前用戶方案的所有表，還會返回當前用戶可以訪問的其它方案的表
比如：
select table_name from all_tables;
dba_tables:
它會顯示所有方案擁有的數據庫表，但是查詢這種數據庫字典視圖，要求
用戶必須是dba角色或是有select any table系統權限
例如：當前system用戶查詢數據字典視圖dba_table時，它會返回
system,sys,scott...方案所對應的數據庫表
用戶名，權限，角色
在建立用戶時，oracle會把用戶的信息存放到數據字典中，當給用戶授予權限或是角色時，
oracle會將權限和角色的信息存放在數據字典，
通過查詢dba_users可以顯示所有數據庫用戶的詳細信息；
通過查詢數據字典視圖dba_sys_privs,可以顯示用戶所具有的系統權限；
通過查詢數據字典視圖dba_tab_privs,可以顯示用戶具有的對象權限
通過查詢數據字典dba_col_privs,可以顯示用戶具有的列權限
通過查詢數據庫字典視圖dba_role_privs可以顯示用戶所具有的對象
desc dba_users;
select username,password from dba_users;
權限分為：系統權限和對象權限
例如：要查詢scott具有的角色，可查詢dba_role_privs:
desc dba_role_privs;
select * from dba_role_privs where grantee='scott';
角色 權限淺談：
角色：一個角色擁有很多權限，角色就是權限構成的，角色分配給一個用戶，這個用戶就擁有
了這個角色的所有權限，一個用戶可以擁有多個角色
//查詢oracle中所有的系統權限，一般是dba，大概130種
select * from system_privilege_map order by name;
//查詢oracle中所有的角色，一般是dba
select * from dba_roles;
//查詢oracle中所有對象權限，一般是dba，大概16種
select distinct privilege from dba_tab_privs;
//查詢數據庫的表空間
select tablespace_name from dba_tablespaces;
問題：
1.如何查詢一個角色所包含的權限？
1.1一個角色包含的系統權限？
select * from dba_sys_privs where grantee='DBA';
查看connect角色包含的系統權限
select * from dba_sys_privs where grantee='connect';
或者如下方法查看：
select * from role_sys_privs where role='connect';
1.2一個角色包含的對象權限？
select * from dba_tab_privs where grantee='connect';
2.oracle究竟有多少種角色？
select * from dba_roles; //以system用戶登陸，可查詢到oracle中預定義25種角色
3.如何知道一個某個用戶包含了哪些角色？
select * from dba_role_privs where grantee='scott';
4.顯示當前用戶可以訪問的所有數據字典視圖
select * from dict where comments like '%grant%';
5.顯示當前數據庫的全稱
select * from global_name;
*****同一個用戶可以登錄多個數據庫實例
其它說明
：：數據字典記錄有oracle數據庫的所有系統信息，通過查詢數據字典可以取得以下系統信息：
（1）對象定義情況
（2）對象占用空間大小
（3）列信息
（4）約束信息
。。。
但是因為這些個信息，可以通過PL/SQL developer工具查詢得到動態性能視圖：用於記錄當前例程的活動信息，當啟動oracle server時，系統會建立動態性能視圖，當停止oracle server時，系統會刪除動態性能視圖，oracle的所有動態性能視圖都是以v_$開始的，並且oracle為每個動態性能視圖都提供了相應的同義詞，並且其同義詞是以V$開始的，例如v_$datafile的同義詞為V$datafile;動態性能視圖的所有者為sys，一般情況下，由dba或是特權用戶來查詢動態性能視圖。因為這個在實際中較少，所以飛過管理表空間和數據文件-介紹
表空間：表空間是數據庫的邏輯組成部分，從物理上上講，數據庫數據
存放在數據文件中；從邏輯上講，數據庫則是存放在表空間中，
表空間由一個或是多個數據文件組成
表空間---》數據文件
類似
北京----->多塊土地
介紹：
oracle中邏輯結構包括表空間，段，區和塊。
說明一下數據庫是由表空間構成，而表空間又是由段構成，
而段又是由區構成，而區又是由oracle塊構成的這樣的一種
結構，可以提高數據庫的效率
邏輯圖如下：
表空間-------》段 -------------------》區--------------》塊
塊
。。。


區
。。。

段
。。。
oracle數據庫之所以有如此結構，目的是為了更好的管理數據庫，一個數據庫有多少
表空間是沒有任何限制的，
表空間用於從邏輯上組織數據庫的數據，數據庫邏輯上是由
一個或是多個表空間組成的，通過表空間可以達到以下作用：
（1）控制數據庫占用的磁盤空間
（2）dba可以將不同數據類型部署到不同的位置，這樣有利於
提高i/o性能，同時有利於備份和恢復等管理操作
有經驗的數據庫管理員一般是把表，視圖，觸發器放在不同
的表空間中，
建立表空間：
建立表空間是使用create tablespace命令完成的，需要注意的是：
一般情況下，建立表空間是特權用戶或是dba來執行的，如果用其它用戶來創建表空間，
則用戶必須要具有create tablespace的系統權限
建立數據表空間：
必須以dba權限才可以的，及sys用戶或是system用戶才可以的
在建立數據庫後，為便於管理表，最好建立自己的表空間
create tablespace data01 datafile 'D:\test\date01.dbf' size 20m uniform size 128k;
以上語句建立了一個表空間，表空間名為data01，對應的數據庫文件為data01.dbf，這個文件的的大小為20M（即段大小），最大只能為500m，區的大小按128K為單位
創建數據庫表指定表空間：
create table mypart(deptno number(4),dname varchar2(14),loc varchar2(13)) tablespace sp001;
管理表空間和數據文件
改變表空間的狀態
當建立表空間時，表空間處於聯機狀態（online），此時
該表空間是可以訪問的，並且該表空間是可以讀寫的，即可以查詢該表空間的數據，而且還可以在表空間執行各種語句，但是在進行
系統維護或是數據維護時，可能需要改變表空間的狀態，一般情況下，
由特權用戶或是dba來操作
（1）使表空間脫機
alter tablespace 表空間名 offline；
（2）使表空間聯機
alter tablespace 表空間名 online；
（3）只讀表空間
當建立表空間時，表空間可以讀寫，如果不希望在該表空間上執行
update，delete，insert操作，那麼可以將表空間修改為只讀
alter tablespace query_data read only;
例如：
alter tablespace sp001 read only;
表空間為只讀時候，就不能再往表裡插入數據
去掉表空間的可讀屬性
alter tablespace sp001 read write;
1）知道表空間名，顯示該表空間包括的所有表
select * from all_tables where tablespace_name='表空間名';
2）知道表名，查看該表屬於哪個表空間
select tablespace_name,table_name from user_tables where table_name='emp';
改變表空間狀態：
刪除表空間：
一般情況下，由特權用戶或是dba來操作，如果是其它用戶操作，
那麼要求用戶具有drop tablespace系統權限
例如：
drop tablespace '表空間' including contents and datafiles;
說明：including contents表示刪除表空間時，刪除該表空間
的所有數據庫對象，而datafiles表示將數據庫文件也刪除
擴展表空間：
表空間是由數據文件組成的，表空間的大小實際就是數據文件相加後的大小，那麼我們可以想象，假定表emp
存放到data01表空間上，初始大小就是2m，當數據滿2m空間後，如果
再向employee表插入數據，這樣就會顯示空間不足的錯誤
案例說明
1.建立一個表空間 sp001
2.當插入數據過多，表空間不足時，該怎麼辦
解決方法：
擴展表空間
（1）增加數據文件
alter tablespace sp01 add datafile 'D:\test\sp01.dbf' size 20m;
（2）增加數據文件的大小
alter tablespace 表空間名 ‘D:\test\sp01.dbf’ resize 20m;
這裡需要注意的是數據文件的大小不要超過500m
（3）設置數據文件自動增長
alter tablespace 表空間名 'D:\test\sp01.dbf' autoextend on next 10m maxsize 500m;
移動數據文件（遷移表空間）：
有時，如果你的數據文件所在的磁盤損壞時，該數據文件將不能再使用，為了能夠
重新使用，則需要將這些文件的副本移動到其它的磁盤，然後恢復：
下面以移動數據文件sp001.dbf為例來說明：
1）確定數據文件所在的表空間
select tablespace_name from dba_data_files where file_name='D:\test\sp01.dbf';
2）使表空間脫機,讓其不能使用
確保數據文件的一致性，將表空間轉變為offline的狀態
alter tablespace sp01 offline;
3）使用命令移動數據文件到指定的目標位置
host move D:\testsp01.dbf c:\test\sp01.dbf
4）移動數據文件
執行alter tablespace 命令在物理上移動數據後，還必須執行alter tablespace 命令對數據庫文件進行
邏輯修改：
alter tablespace sp01 rename datafile 'D:\sp001.dbf' to 'C:\sp001.dbf';
5)使表空間聯機
在移動了數據文件後，為了使用戶可以訪問該表空間，必須
將其轉變為online狀態
alter tablespace data01 online;
以上簡稱五部曲
顯示表空間的信息
查詢數據字典視圖dba_tablespaces,顯示表空間的信息：
select tablespace_nae from dba_tablespaces;
顯示表空間所包含的數據文件
查詢數據字典視圖dba_data_files,可顯示表空間所包含的
數據文件，如下：
select file_name,bytes from dba_data_files where tablespace_name='表空間名';
表空間小結：
1）了解表空間和數據文件的作用
2）掌握常用表空間，undo表空間和臨時表空間的建立方法
3）了解表空間的各個狀態
（online，offline，read，write，read only）的作用，以及
如何改變表空間的狀態的方法
4）了解移動數據文件的原因，及使用alter tablespace
和alter datatable命令移動數據文件的方法
其它表空間
除了最常用的數據表空間外，還有其它類型表空間
（1）索引表空間
（2）undo表空間
（3）臨時表空間
（4）非標准塊的表空間
將索引單建一個表空間，會提高數據的訪問效率
初始事務數：
最大事務數：