一、創建表空間create tablespace atp logging datafile 'D:\oracle\oradata\orcl\atp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; ...
一、創建表空間
create tablespace atp
logging
datafile 'D:\oracle\oradata\orcl\atp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
二、創建用戶
-- Create the user
create user ATP
default tablespace ATP
temporary tablespace TEMP
profile DEFAULT
identified by atp;
-- Grant/Revoke role privileges
grant connect to ATP;
grant dba to ATP;
grant resource to ATP;
-- Grant/Revoke system privileges
grant create any view to ATP;
grant select any sequence to ATP;
grant select any table to ATP;
grant unlimited tablespace to ATP;
三、導出
1、創建邏輯目錄，該命令不會在操作系統創建真正的目錄，最好以system等管理員創建。
create directory expdp_dir as 'D:\home\Oracle\dump';
2、給scott用戶賦予在指定目錄的操作許可權，最好以system等管理員賦予。
grant read,write on directory expdp_dir to scott;
3、在windows中創建目錄“D:\home\Oracle\dump”
4、導出命令
1)按用戶導出：expdp system/1@orcl directory=expdp_dir dumpfile=0526NETOBDC.dmp logfile=0526NETOBDC.log schemas=netobdc
2)按表名導出：expdp system/1@orcl directory=expdp_dir dumpfile=0526NETOBDC.dmp logfile=0526NETOBDC.log TABLES=emp,dept
3)導出整個資料庫：expdp system/1@orcl directory=expdp_dir dumpfile=0526NETOBDC.dmp logfile=0526NETOBDC.log FULL=y
四、導入
1、創建邏輯目錄，該命令不會在操作系統創建真正的目錄，最好以system等管理員創建。
create directory impdp_dir as 'D:\home\Oracle\impdp_dir';
2、給scott用戶賦予在指定目錄的操作許可權，最好以system等管理員賦予。
grant read,write on directory impdp_dir to scott;
3、在windows中創建目錄“D:\home\Oracle\impdp_dir”然後將導出的dmp包拷貝到此目錄下
4、導入命令
1)導入到指定用戶下：impdp bdc_atp/bdc_atp@orcl directory=impdp_dir dumpfile=0526ATP.dmp logfile=0526ATP.log schemas=bdc_atp
2)導入到不同的用戶下：impdp bdc_atp/bdc_atp@orcl directory=impdp_dir dumpfile=0526ATP.dmp logfile=0526ATP.log remap_schema=atp:bdc_atp
3)導入整個資料庫：impdp bdc_atp/bdc_atp@orcl directory=impdp_dir dumpfile=0526ATP.dmp logfile=0526ATP.log FULL=y
