select username,default_tablespace from dba_users;

select tablespace_name from dba_temp_files;

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",

F.TOTAL_BYTES "空闲空间(M)",

F.MAX_BYTES "最大块(M)"

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT DD.TABLESPACE_NAME,

 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 1;

select * from dba_data_files;

创建表空间

CREATE TABLESPACE TS_USERNAME_TJ_DATA datafile '+DATA' autoextend on next 100M maxsize 30G;

创建临时表空间

CREATE TEMPORARY TABLESPACE TS_USERNAME_TEMP TEMPFILE '+DATA' SIZE 30G  AUTOEXTEND ON  NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

drop TABLESPACE TS_USERNAME_TEMP including contents and datafiles;

CREATE USER MOE_WAREHOUSE  identified by password  DEFAULT TABLESPACE TS_USERNAME_TJ_DATA temporary tablespace TS_USERNAME_TEMP ;

为用户赋予权限

grant unlimited tablespace to UserName;

grant create tablespace to UserName;

grant alter tablespace to UserName;

grant drop tablespace to UserName;

grant manage tablespace to UserName;

grant create view to UserName;

grant create session to UserName;

grant select on V_$session to UserName;

grant select on V_$sesstat to UserName;

grant select on V_$statname to UserName;

grant all privileges to UserName；

grant dba to UserName;

drop user USERNAME cascade;

根据2.1和2.2完成新系统操作，数据泵数据迁移，只需要创建好表空间即可。数据泵数据备份是按用户模式进行的。

创建数据

create directory UserName_dir as '/home/oracle';

grant read,write on directory UserName_dir to UserName;

expdp \' / as sysdba \' parfile=exp_UserName.par

vim exp_UserName.par

dumpfile=exp_UserName_20201126.dmp

logfile=exp_UserName_20201126.log

directory=DBBAK

SCHEMAS=UserName

impdp \' / as sysdba \' parfile=imp_moewarehouse.par

imp_UserName.par

dumpfile=exp_UserName_20201126.dmp

logfile=exp_UserName_20201126.log

directory=DBBAK

FULL=y

shutdown immediate

startup

alter user UserName identified by UserName2018$;