oracle 创建新用户及表空间，导入备份数据

创建用户

创建用户文件路径，或选择已有的文件路径'D:\hls_oracle_dbf\jn_test'以及选择或创建导入备份数据的源路径'D:\sysload_file_dir'
plsql或命令行中执行如下脚本

--用户名：GW_CZ，密码：GW_CZ，备份数据来源的用户名：JN_PROD，用户便空间：JN_PROD
create tablespace GW_CZ logging datafile '/home/oracle/app/oracle/oradata/gwdev/gw_cz.dbf' size 1024m autoextend on next 64m maxsize UNLIMITED extent management local;

create user GW_CZ identified by GW_CZ default tablespace GW_CZ temporary tablespace TEMP profile DEFAULT;


grant execute on dbms_lock to GW_CZ;
grant execute on dbms_aq to GW_CZ;
grant execute on dbms_aqadm to GW_CZ;
grant connect,resource to GW_CZ;
grant create any table to GW_CZ;
grant unlimited tablespace to GW_CZ;
grant create public synonym to GW_CZ;
grant create synonym to GW_CZ;
grant execute on SYS.DBMS_AQ to GW_CZ;
grant execute on SYS.DBMS_AQADM to GW_CZ;
grant execute on SYS.DBMS_AQ_BQVIEW to GW_CZ;
grant execute on SYS.DBMS_LOCK to GW_CZ; 
grant connect to GW_CZ;
grant dba to GW_CZ;
grant alter session to GW_CZ;
grant create any context to GW_CZ;
grant create any table to GW_CZ;
grant create job to GW_CZ;
grant create procedure to GW_CZ;
grant create sequence to GW_CZ;
grant create table to GW_CZ;
grant create type to GW_CZ;
grant create user to GW_CZ;
grant create view to GW_CZ;
grant debug connect session to GW_CZ;
grant query rewrite to GW_CZ;
grant select any dictionary to GW_CZ;
grant dba to GW_CZ;

/*create or replace directory SYSLOAD_FILE_DIR
  as 'D:\sysload_file_dir'; */
grant read,write on directory DATA_PUMP_DIR to GW_CZ;

命令行窗口执行导入命令

impdp GW_CZ/GW_CZ  dumpfile=gw_prod_20200804.dmp directory=DATA_PUMP_DIR logfile=impdp.log remap_schema=GW_PROD:GW_CZ remap_tablespace=GW_PROD:GW_CZ transform=OID:N
#需加上transform=OID:N，否则汇报如下错误

删除用户及表空间

DROP USER XX CASCADE;
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

导出备份

//查看数据库实例
select * from v$instance;
//查看用户
select * from dba_users; 
//查看表空间
select * from v$tablespace; 
//查询用户SYSLOAD_FILE_DIR(命名不一定)
select * from dba_directories;
//查看表空间文件路径
select * from dba_data_files;

//SYSLOAD_FILE_DIR  定义用户时定义，gw_local_20200611.dmp导出文件名，自定义，gw_local_20200611.LOG导出日志名，自定义，窗口中执行
expdp username/pass directory=SYSLOAD_FILE_DIR  dumpfile=gw_local_20200611.dmp logfile=gw_local_20200611.LOG