1 表空间查询
1.1 检查用户与表空间对应情况
select username,default_tablespace from dba_users;
1.2 查看临时表空间
select tablespace_name from dba_temp_files;
1.3 查看表空间大小
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;
2 创建及删除表空间
2.1 查看数据文件路径
select * from dba_data_files;
2.2 创建表空间及设置初始化空间
创建表空间 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;
2.3 删除表空间
drop TABLESPACE TS_USERNAME_TEMP including contents and datafiles;
2.4 创建用户,关联表空间
CREATE USER MOE_WAREHOUSE identified by password DEFAULT TABLESPACE TS_USERNAME_TJ_DATA temporary tablespace TS_USERNAME_TEMP ;
3 用户授权
为用户赋予权限 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;
4 删除用户及清理数据
drop user USERNAME cascade;
5 数据迁移
5.1 注意事项
根据2.1和2.2完成新系统操作,数据泵数据迁移,只需要创建好表空间即可。数据泵数据备份是按用户模式进行的。 创建数据
5.2 创建数据备份目录
create directory UserName_dir as '/home/oracle'; grant read,write on directory UserName_dir to UserName;
5.3 数据导出
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
5.4 数据导入
impdp \' / as sysdba \' parfile=imp_moewarehouse.par imp_UserName.par dumpfile=exp_UserName_20201126.dmp logfile=exp_UserName_20201126.log directory=DBBAK FULL=y
6 数据库重启
shutdown immediate startup
7 修改密码
alter user UserName identified by UserName2018$;
温馨提示:本文由自己记录,仅作参考,出了问题概不负责。。。。。