oracle 常用命令【创建表空间用户及删除】

/*第1步：创建临时表空间  */

create temporary tablespace ZJY_TEMP

tempfile '/usr/u01/app/oradata/orcl/ZJY_TEMP.dbf'

size 500m

autoextend on

next 500m maxsize 20480m

extent management local;

/*第2步：创建数据表空间  */

create tablespace ZJY

logging

datafile '/usr/u01/app/oradata/orcl/ZJY.dbf'

size 500m

autoextend on

next 500m maxsize 20480m

extent management local;

/*第3步：创建用户并指定表空间  */

create user ZJY identified by ZJY2018 default tablespace ZJY  temporary tablespace ZJY_TEMP;

/*第4步：给用户授予权限  */

GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,

   DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,

      CONNECT,RESOURCE,CREATE SESSION  TO ZJY;

imp ZJY/ZJY2018@orcl  full=y  file=ZJY.dmp ignore=y

exp ZJY/ZJY2018@orcl owner=ZJY file=whdx_zjk.dmp

 删除用户及数据

drop user ZJY cascade;

# 删除表空间

DROP TABLESPACE ZJY INCLUDING CONTENTS AND DATAFILES;

# 删除表空间

DROP TABLESPACE ZJY_TEMP INCLUDING CONTENTS AND DATAFILES;