Oracle建立表空間、使用者、分配許可權語句

Starlight發表於2016-08-24
Oracle

以系統管理員登入，命令：sqlplus / as sysdba #首先需要 su - oracle 進入到oracle使用者下

一、分為四步

/第1步：建立臨時表空間 /

create temporary tablespace User_Temp tempfile `/u01/app/oracle/oradata/orcl/user_temp.dbf` size 200m autoextend on next 100m maxsize 20480m extent management local;

注:此步建立的是臨時表空間，可以多個資料公用一個臨時表空間，注意建立的大小即可，名稱隨意。

/第2步：建立資料表空間 /

create tablespace User_Data logging datafile `/u01/app/oracle/oradata/orcl/user_data.dbf`  size 200m autoextend on next 100m maxsize 20480m extent management local;

注:此步注意資料表空間的名稱最好與匯出的備份檔案所用的表空間名稱一致，不一致容易報錯！
用UltraEdit開啟dmp檔案切換16進位制編輯，搜尋tablespace可以檢視備份檔案的表空間名稱。

/第3步：建立使用者並指定表空間/

create user User_Name identified by "Passwd" default tablespace user_data temporary tablespace user_temp;

注:使用者名稱最好與匯出的備份檔案的資料庫名保持一致！

/第4步：給使用者授予許可權 /

grant connect,resource,dba to User_Name;

二、其他：

/修改使用者密碼 /

alter user [username] identified by [password];

/刪除使用者 /

drop user User_Name cascade;

/刪除表空間 /

DROP TABLESPACE 表空間名 INCLUDING CONTENTS AND DATAFILES;

/清空某張表/

TRUNCATE TABLE tableName

**tableName是要清空表的表名**

/清空當前使用者下所有的表和資料的方法 /

使用場景：覆蓋一個已存在的庫，並且裡邊有資料，這時直接匯入備份檔案會報錯，只能清空裡邊原有表和資料之後再匯入；當然你也可以新建一個其他庫來匯入。

$ su - oracle  #切換到Oracle使用者
$ sqlplus / as sysdba  #登入資料庫控制檯
SQL>select `Drop table `||table_name||`;` from all_tables where owner=`User_Name`;
注：`User_Name`英文一定要大寫；將會輸出一批刪除表的sql語句，這些SQL語句執行一下就可以了（需要有drop table的許可權）

建議使用sqldeveloper圖形化客戶端連線Oracle資料庫執行刪除語句。

三、資料庫匯入、匯出

兩種方式：exp/imp方式、資料庫泵expdp/impdp方式，推薦用資料泵的方式，支援大資料量。

1、資料泵expdp/impdp

1）前期準備，dumpdir目錄建立

以Linux系統為例
$ mkdir /home/oracle/dumpdir
#執行該命令之前，要首先在硬碟上建立`/home/oracle/dumpdir`資料庫匯入、匯出所對應的資料夾
$ su - oracle  #切換到Oracle使用者
$ sqlplus / as sysdba  #登入資料庫控制檯
SQL>create directory dumpdir as `/home/oracle/dumpdir`;  #建立資料泵匯入、匯出目錄
SQL>select * from dba_directories;  #檢視一下是否已存在該目錄
-------------------------------------------------------------------------------
SYS                            DUMPDIR
/home/oracle/dumpdir

SQL>grant read,write on directory dumpdir to dbname;
#授權給操作使用者這個dump目錄的許可權【dbname是一個已經存在的資料庫使用者】
SQl>exit

2）expdp資料泵匯出

expdp User_Name/Password@orcl directory=dumpdir dumpfile=User_Name.bak.2016.1.1.dmp logfile=User_Name.bak.2016.1.1.log

注：幾個要點，使用者名稱密碼不用說，orcl是你配置的例項名；dumpdir是個變數，就是上邊那步建立的目錄，可以多個，名稱隨意；
備份完檔案會存放在建立的/home/oracle/dumpdir目錄下。

3）impdp資料泵匯入

impdp User_Name/Password@orcl directory=dumpdir dumpfile=User_Name.bak.2016.1.1.dmp remap_schema=SOURCE_USER_Name:DEST_USER_Name

注：前幾個引數跟匯出時一樣，匯入需要把備份檔案放到dumpdir目錄下；多了一個remap_schema，
是填寫備份檔案的資料庫使用者名稱`SOURCE`和本機建立的資料庫使用者名稱`DEST`，這個在本機建立的使用者名稱不一樣的情況下需要用到。
如果一樣寫成一樣的就行了。

2）exp/imp方式

exp命令互動匯出

$ su - oracle
$ exp  #Windows下cmd也是輸入這個命令執行，下同。
1.使用者名稱:User_Name 回車
2.口令：輸入此使用者名稱的口令，回車；
3.輸入陣列提取緩衝區大小：直接回車，選擇預設的4096；
4.匯出檔案: EXPDAT.DMP > 填寫匯出生成的備份檔案完整路徑、檔名，注意相應路徑要已存在，且要有足夠空間，
如：C:ackupUser_Name_2016.dmp ,回車；
5.選擇匯出方式：直接回車，選擇預設的按使用者方式匯出；
6.匯出許可權：直接回車，選擇預設的yes;
7.匯出表資料：直接回車，選擇預設的yes; 
8.匯出許可權：直接回車，選擇預設的yes; 
9.壓縮區：直接回車，選擇預設的yes; 
10.要匯出的使用者：輸入剛開始的使用者名稱確認，注：一定要輸入，不然沒有資料匯出！
11.再次出現“要匯出的使用者”：此時直接回車即可；
12.匯出完成，其中提示：匯出成功終止, 但出現警告；這種輸入是正常的，說明匯出操作成功完成；

imp匯入

imp User_Name/Password@127.0.0.1:/orcl fromuser=User_Name touser=User_Name_New file=C:ackupUser_Name_2016.dmp commit=y ignore=y

相關文章