创建用户

-- 创建数据库用户
create user ETL_TARGET identified by ETL_TARGET;
-- 赋予连接数据库，创建会话权限
GRANT CONNECT,CREATE SESSION,RESOURCE TO ETL_TARGET;
-- 赋予创建存储过程、方法、触发器、视图权限
GRANT CREATE ANY PROCEDURE,CREATE ANY SYNONYM,CREATE ANY TRIGGER,CREATE ANY VIEW TO ETL_TARGET;
-- 赋予导入导出dmp权限
-- GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE TO ETL_TARGET;
-- 赋予查看表空间信息权限
-- GRANT UNLIMITED TABLESPACE TO ETL_TARGET;
-- 赋予创建表信息权限
GRANT CREATE TABLE TO ETL_TARGET;
-- 赋予删除表信息权限
GRANT DROP ANY TABLE TO ETL_TARGET;
-- 赋予插入数据信息权限
GRANT INSERT ANY TABLE TO ETL_TARGET;
-- 赋予修改数据信息权限
GRANT UPDATE ANY TABLE TO ETL_TARGET;

删除用户

-- 删除数据库用户信息
REVOKE CONNECT, CREATE SESSION, RESOURCE FROM ETL_TARGET;
REVOKE CREATE ANY PROCEDURE, CREATE ANY SYNONYM, CREATE ANY TRIGGER, CREATE ANY VIEW FROM ETL_TARGET;
-- REVOKE EXP_FULL_DATABASE, IMP_FULL_DATABASE FROM ETL_TARGET;
-- REVOKE UNLIMITED TABLESPACE FROM ETL_TARGET;
REVOKE CREATE TABLE FROM ETL_TARGET;
REVOKE DROP ANY TABLE FROM ETL_TARGET;
REVOKE INSERT ANY TABLE FROM ETL_TARGET;
REVOKE UPDATE ANY TABLE FROM ETL_TARGET;
DROP USER ETL_TARGET CASCADE;

新版本命令

• 导出指定模式表结构与数据等

expdp cms/cms@//192.168.167.221:1521/orcl schemas=cms directory=DATA_PUMP_DIR dumpfile=your_dump.dmp logfile=your_log.log

• 导入指定模式表结构与数据等

impdp cms/cms@//localhost:1521/orcl schemas=cms directory=DATA_PUMP_DIR dumpfile=YOUR_DUMP.DMP

老版命令

• 导出指定表

exp cms/cms@//10.5.1.22/orcl file=C:\Users\15633\Desktop\数据迁移中间表\DDL\CMS-TO-HLJ.dmp tables=cif_base,grt_base

• 导入指定表

imp cms/cms@//localhost:1521/orcl file=C:\Users\15633\Desktop\数据迁移中间表\cms_mid_table.dmp full=y ignore=y