当前位置：首页 > article >正文

一次oracle迁移11g到19c后用到的对象数量对比脚本

article 2024/10/3 6:20:01

一次oracle迁移11g到19c后用到的对象数量对比脚本，按用户习惯就好。

oracle@jyc:/home/oracle/stat>ll
合計 584
-rw-r--r-- 1 oracle oinstall 82709 9月 27 09:20 11g-79-01.txt
-rw-r--r-- 1 oracle oinstall 332 9月 27 09:17 11g-79-02.txt
-rw-r--r-- 1 oracle oinstall 161788 9月 27 09:17 11g-79-03.txt
-rw-r--r-- 1 oracle oinstall 721 9月 27 09:07 o1.bak
-rw-r--r-- 1 oracle oinstall 25878 9月 27 09:20 o1.log
-rwxr-xr-x 1 oracle oinstall 335 9月 27 09:07 o1.sh
-rw-r--r-- 1 oracle oinstall 809 9月 27 09:18 o1.sql
-rw-r--r-- 1 oracle oinstall 82659 9月 27 09:20 o1.txt
-rw-r--r-- 1 oracle oinstall 492 9月 27 09:17 o2.log
-rwxr-xr-x 1 oracle oinstall 134 9月 27 09:07 o2.sh
-rw-r--r-- 1 oracle oinstall 655 9月 27 09:15 o2.sql
-rw-r--r-- 1 oracle oinstall 29932 9月 27 09:17 o3.log
-rwxr-xr-x 1 oracle oinstall 317 9月 27 09:07 o3.sh
-rw-r--r-- 1 oracle oinstall 1570 9月 27 12:01 o3.sql
-rw-r--r-- 1 oracle oinstall 161756 9月 27 09:17 o3.txt
oracle@jyc:/home/oracle/stat>more o1.sh
#!/bin/bash
sqlplus system/oracle <<EOF
spool /home/oracle/stat/o1.txt;
set timing on
@/home/oracle/stat/o1.sql;
spool off;
exit;
EOF
echo "OWNER TABLESPACE_NAME TABLE_NAME ROW_COUNT TABLE_SIZE(M)" | cat - /home/oracle/stat/o1.txt |grep -v "SQL>" > /home/oracle/stat/o11.txt
oracle@jyc:/home/oracle/stat>more o1.sql
set pagesize 0
set line 180
--set head on
col OWNER for a10
col TABLESPACE_NAME for a32
col table_name for a40
col ROW_COUNT for 9999999999999999
col TABLE_SIZE(M) for a30
SELECT T.OWNER,
T.TABLESPACE_NAME,
T.table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from ' || T.OWNER ||'.' || T.TABLE_NAME)),
'/ROWSET/ROW/C')) AS row_count,
(select trim(to_char(round(sum(bytes) / (1024 * 1024), 4),
'999990.9999')) || 'M' as M
from dba_segments
where segment_name = upper(t.TABLE_NAME)) as "TABLE_SIZE(M)"
FROM dba_tables T
WHERE T.OWNER IN ('JYC1','JYC2')
ORDER BY T.OWNER, T.TABLESPACE_NAME, T.TABLE_NAME;
oracle@jyc:/home/oracle/stat>more o2.sh
#!/bin/bash
sqlplus system/oracle <<EOF
spool /home/oracle/stat/o2.txt;
set timing on
@/home/oracle/stat/o2.sql;
spool off;
exit;
EOF
oracle@jyc:/home/oracle/stat>more o2.sql
set head on
SELECT
(SELECT COUNT(*) FROM dba_tables where owner in ('JYC1','JYC2')) AS table_count,
(SELECT COUNT(*) FROM dba_views where owner in ('JYC1','JYC2')) AS view_count,
(SELECT COUNT(*) FROM dba_procedures where owner in ('JYC1','JYC2')) AS procedure_count,
(SELECT COUNT(*) FROM dba_triggers where owner in ('JYC1','JYC2')) AS trigger_count
FROM
dual;
oracle@jyc:/home/oracle/stat>more o3.sh
#!/bin/bash
sqlplus system/oracle <<EOF
spool /home/oracle/stat/o3.txt;
set timing on
@/home/oracle/stat/o3.sql;
spool off;
exit;
EOF
echo "用户对象类型对象名称对象表空间" | cat - /home/oracle/stat/o3.txt |grep -v "SQL>" > /home/oracle/stat/o31.txt
oracle@jyc:/home/oracle/stat>more o3.sql
set head on
set pagesize 0
set line 180
col 用户 for a10
col 对象类型 for a20
col 对象名称 for a40
col 对象表空间 for a32
SELECT ss.用户, ss.对象类型, ss.对象名称, ss.对象表空间
FROM (SELECT 'table' as 对象类型,
t.OWNER as 用户,
t.TABLESPACE_NAME as 对象表空间,
t.TABLE_NAME as 对象名称
FROM dba_tables t where t.owner in ('JYC1','JYC2')
union all
SELECT 'view' as 对象类型,
v.OWNER as 用户,
'' as 对象表空间,
v.VIEW_NAME as 对象名称
FROM dba_views v where v.owner in ('JYC1','JYC2')
union all
SELECT 'procedure' as 对象类型,
p.owner as 用户,
'' as 对象表空间,
DECODE(NVL(p.PROCEDURE_NAME,null),null,p.OBJECT_NAME,p.OBJECT_NAME||'.'||p.PROCEDURE_NAME) as 对象名称
FROM dba_procedures p where p.owner in ('JYC1','JYC2')
union all
SELECT 'trigger' as 对象类型,
tt.owner as 用户， '' as 对象表空间,
tt.trigger_name as 对象名称
FROM dba_triggers tt where tt.owner in ('JYC1','JYC2')) ss
order by ss.用户, ss.对象类型, ss.对象名称;

执行查询脚本
nohup ./o1.sh > o1.log &
查看结果
more o1.txt