//删除表空间 DROP TABLESPACE erpdev INCLUDING CONTENTS AND DATAFILES; //删除用户 drop user username cascade;
1、新建表空间
CREATE TABLESPACE erpdev DATAFILE 'D:\app\orcl12c\oradata\erpdev\erpdev.dbf' SIZE 120M AUTOEXTEND ON NEXT 10M LOGGING DEFAULT NOCOMPRESS ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; drop tablespace ERPdev;
2、新建临时表空间
create temporary tablespace erpdev_temp tempfile 'D:\app\orcl12c\oradata\erpdev\erptemp.dbf' size 40M autoextend on next 5M ;
3、新建用户并指定默认表空间
create user erpdev identified by erpdev default tablespace erpdev;
4、授权
grant resource , connect , dba,AUDIT_ADMIN, AUDIT_VIEWER,SELECT_CATALOG_ROLE,EXECUTE_CATALOG_ROLE,DELETE_CATALOG_ROLE,CAPTURE_ADMIN,EXP_FULL_DATABASE,IMP_FULL_DATABASE,CDB_DBA,PDB_DBA,LOGSTDBY_ADMINISTRATOR,DBFS_ROLE,GSMUSER_ROLE,AQ_ADMINISTRATOR_ROLE,AQ_USER_ROLE,DATAPUMP_EXP_FULL_DATABASE to erpdev;
5、新建逻辑dump存储目录(先在系统文件目录'D:\app下,新建dump文件夹)
create directory dir_expdp as 'D:\app\orcl12c\oradata\dir_expdp';
6、给新用户(erp)授读写新建目录的权限
grant read,write on directory dir_expdp to erpdev;