1、用户数据清理

登录用户，并执行以下SQL语句，将查询的结果中的DROP语句放到Command窗口批量执行清理动作。

SELECT object_name,

object_type,

CASE

WHEN object_type = 'TABLE' THEN

'drop TABLE ' || object_name || ' CASCADE CONSTRAINTS;'

WHEN object_type = 'SEQUENCE' THEN

'drop SEQUENCE ' || object_name || ';'

WHEN object_type = 'FUNCTION' THEN

'drop FUNCTION ' || object_name || ';'

WHEN object_type = 'PACKAGE' THEN

'drop PACKAGE ' || object_name || ';'

WHEN object_type = 'PROCEDURE' THEN

'drop PROCEDURE ' || object_name || ';'

WHEN object_type = 'VIEW' THEN

'drop VIEW ' || object_name || ';'

WHEN object_type = 'TYPE' THEN

'drop TYPE ' || object_name || ';'

WHEN object_type = 'SYNONYM' THEN

'drop SYNONYM ' || object_name || ';'

WHEN object_type = 'TRIGGER' THEN

'drop TRIGGER ' || object_name || ';'

END CASE

FROM user_objects;

执行完以上清理步骤之后，执行以下SQL，释放回收站

PURGE RECYCLEBIN;

查询以下SQL语句，如果结果不为空，重复执行以上两个步骤，直到结果为0。结果集为空表示清理完毕。

select object_name,OBJECT_TYPE,COUNT(*) from user_objects group by object_name,OBJECT_TYPE;

2、数据库的导出

$exp CHAINWORK/CHAINWORK@orcl147 file='F:/data/CHAINWORK.dmp' log='F:/data/CHAINWORK_out.log' owner='CHAINWORK'

3、数据库的导入

$imp CHAINWORK/CHAINWORK@orcl9 file='F:/data/CHAINWORK.dmp' log='F:/data/CHAINWORK_in.log' full=y ignore=y

ITPub博客

用户清理及用户数据迁移