資料泵技術比原來導入/導出(imp,exp)技術快15-45倍。速度的提高源于使用了并行技術來讀寫導出轉儲檔案。此指令隻可用在服務端,用戶端無法使用。
1.打開SQL plus
首先需要輸入使用者名和密碼進行登入;
建立一個directory對象:create directory dpdata1 as 'd:\test\dump';
然後檢視目前執行個體下有哪些導出目錄可使用:select * from dba_directories;
最後登入一個管理者的賬戶來給目前的賬戶賦予讀寫的權限:grant read,write on directory dpdata1 to scott;
若想賦予全部的權限即管理者權限:grant dba to scott;
若重新建立一個新使用者:create user username identified by password;
給新使用者賦予登入的權限:grant connect,resource to username;
2.啟用和禁用主鍵等
Type Code | Type Description | Acts On Level |
C | Check on a table | Column |
O | Read Only on a view | Object |
P | Primary Key | Object |
R | Referential AKA Foreign Key | Column |
U | Unique Key | Column |
V | Check Option on a view | Object |
--删除所有主鍵限制 的Sql代碼
select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R'
--alter table EMP drop constraint EMP_PK ;
--禁用所有主鍵限制的Sql代碼
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'
--alter table EMP disable constraint EMP_PK ;
--啟用所有主鍵限制的Sql代碼
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'
--alter table EMP enable constraint EMP_PK ;
禁用掉主鍵等後會使導入導出的速度提高。
3.導出資料
按表導出(可單個或多個):
expdp scott/[email protected] directory=dpdata1 dumpfile=expdp.dmp tables=BONUS,emp,dept
結尾不要加分号;
按使用者導出:expdp scott/[email protected] directory=dpdata1 dumpfile=expdp.dmp schemas=scott
導出所有所有表:expdp scott/[email protected] directory=dpdata1 dumpfile=expdp.dmp full=y
4.導入資料
impdp yyb/[email protected] directory=dpdata1 dumpfile=expdp.dmp remap_schema=scott:yyb exclude=user(使用者已存在,最後不要加分号)
impdp yyb/[email protected] directory=dpdata1 dumpfile=expdp.dmp remap_schema=scott:yyb//使用者不存在;
從scott使用者導入到yyb使用者。
5.拓展:
查詢Oracle表是否有觸發器:
select * from user_triggers where table_owner = 'xxx' and table_name = upper('table_name');
導出資料
1)按使用者導
expdp scott/[email protected] schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;
2)并行程序parallel
expdp scott/[email protected] directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3
3)按表名導
expdp scott/[email protected] TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;
4)按查詢條件導
expdp scott/[email protected] directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';
5)按表空間導
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
6)導整個資料庫
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;
還原資料
1)導到指定使用者下
impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;
2)改變表的owner
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;
3)導入表空間
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;
4)導入資料庫
impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
5)追加資料
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION
6删除資料
truncate table test;
truncate與delete的異同:
TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系統和事務日志資源少。
DELETE 語句每次删除一行,并在事務日志中為所删除的每行記錄一項。TRUNCATE TABLE 通過釋放存儲表資料所用的資料頁來删除資料,并且隻在事務日志中記錄頁的釋放。
TRUNCATE TABLE 删除表中的所有行,但表結構及其列、限制、索引等保持不變。新行辨別所用的計數值重置為該列的種子。如果想保留辨別計數值,請改用 DELETE。