oracle创建表空间datafile,oracle创建表空间

--gjjmx12

--删除表空间--/mnt/pg/u01/app/oracle/data/orcl/gjjmx12

alter database datafile '/home/oracle/DB/oradata/ORCL01/wasys3_hnszdwzx.dbf' offline drop ;

DROP TABLESPACE wasys3_hnszdwzx INCLUDING CONTENTS AND DATAFILES;

--创建表空间

--select * from v$datafile

create tablespace WADAGX

datafile '/db/oracle/oradata/orcl/WADAGX' size 3500m

autoextend on

next 100M

extent management local

segment space management auto;

drop user wasys3_hnszdwzx cascade;

--创建用户(GJJMX12)

--创建用户并指定表空间

create user hafown identified by hafmis

default tablespace wasys3_jscs

temporary tablespace TEMP;

--给用户授予权限(用sys用户sysdba模式)

grant select any dictionary,select any table,unlimited tablespace,update any table,connect,

resource,dba,exp_full_database,imp_full_database,alter any index,insert any table,

alter any procedure,alter any table,delete any table,drop any sequence,

debug any procedure, debug connect session to hafown;

--imp wasys3_btzx/btgjj2012@ORCL01 full=y file='/opt/backup/bt202-6-7.dump' log='/opt/backup/bt202-6-7.log'

--exp wasys3_hnszdwzx/111111@orcl direct=n file='bt-12-16-null.dump' log='bt-12-16-null.log'