工作日志备查：4.his_exp_imp逻辑导出导入

set oracle_sid=ora816
exp system/manager file =exp_full.dmp full=y direct =y log=exp_full_log.txt

1.查询出所以包含对象的用户， 用sys用户
select * from
(select distinct owner ,tablespace_name from dba_tables ) a
 where a.owner <>upper('sys') and a.owner<>upper('system');

2. 生成create tablespace 脚本，去除新库中已经存在的表空间，大小要修改的大些。

set pagesize 1000
set linesize 300
spool create_ts.sql
SELECT 'create tablespace '||upper(f.tablespace_name) ||' datafile   '|| '''D:\Oracle\oradata\test1\'
||upper(f.tablespace_name)||'.dbf '''||'  size  '||(d.Tot_grootte_Mb - f.total_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 ;
spool off

3.生成create user 脚本，在旧库ora816上执行

set pagesize 1000
set linesize 200
spool create_user.sql
select 'create user '||username||'  identified by values '''||password||''' default tablespace '||default_tablespace||';' from dba_users
where default_tablespace <>upper('system') ;
spool off

4.  全库导入
set oracle_sid=test1
imp system/manager file =exp_full.dmp full=y ignore=y log= imp_full_log.txt

5.比较新旧库文件大小
conn internal/oracle
set pagesize 200
col "表空间名" for a20
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 DESC;