异构迁移常用SQL

1.筛选大表

1.1.查看业务用户下每个表的行数

  select owner, table_name, num_rowsfrom dba_tableswhere owner = 'SYS'and num_rows > 5000
order by 3 desc;

1.2.查看业务用户下每个表或者索引的占用空间

select owner, segment_name, sum(bytes) / 1024 / 1024 "占用空间（mb）"
from dba_segments
where owner in ('SYS')
and segment_type = 'TABLE'
group by owner, segment_name
order by 3 ;

  select owner, segment_name, sum(bytes) / 1024 / 1024 "占用空间（mb）"from dba_segmentswhere owner in ('SYS')and segment_type = 'INDEX'
group by owner, segment_name
order by 3 desc;

1.3.查看业务用户下每个表行数,表段和索引段占用空间之和

with t_tab as(select a.owner,a.table_name,a.num_rows,sum(b.bytes)/1024/1024 mbfrom dba_tables a,dba_segments bwhere a.table_name=b.segment_nameand a.owner=b.ownerand a.owner in ('SYS')and a.num_rows>1000group by a.owner,a.table_name,a.num_rows),t_ind as(select a.owner,a.table_name,a.index_name,sum(b.bytes)/1024/1024 mbfrom dba_indexes a,dba_segments bwhere a.index_name=b.segment_name and a.owner=b.ownergroup by a.owner,a.table_name,a.index_name)select t_tab.owner,t_tab.table_name ,t_tab.num_rows, t_tab.mb as table_size_mb, nvl(t_ind.mb,0) as index_size_mb,(t_tab.mb+nvl(t_ind.mb,0)) as total_size_mbfrom t_tab
left join t_indon t_tab.owner=t_ind.ownerand t_tab.table_name=t_ind.table_nameorder by 4 desc;

2.分区表

2.1.查看是一级分区还是二级分区表

SELECT distinct owner, table_name, partitioning_typeFROM dba_tab_partitionsWHERE owner = 'SYS';

3.大字段表

  SELECT t.owner AS 用户名,t.table_name AS 表名,t.num_rows AS 表行数,round((s.bytes / 1024 / 1024), 2) AS 表大小_MBFROM dba_segments sJOIN dba_lobs lON s.segment_name = l.segment_nameAND s.owner = l.ownerJOIN dba_tables tON l.table_name = t.table_nameAND l.owner = t.ownerJOIN dba_tab_columns cON c.table_name = t.table_nameAND c.owner = t.ownerWHERE t.owner NOT IN ('SYS', 'SYSTEM')AND t.tablespace_name NOT IN ('SYSAUX', 'SYSTEM')AND t.degree != 'DEFAULT'AND c.data_type IN ('CLOB', 'NCLOB', 'BLOB', 'LONG', 'LONG RAW', 'BFILE')
ORDER BY s.bytes DESC;

4.没有主键或者唯一建的表

4.1.Oracle查询SQL

   SELECT t.owner, t.table_nameFROM dba_tables t
LEFT JOIN dba_constraints cON t.owner = c.ownerAND t.table_name = c.table_nameAND c.constraint_type IN ('P', 'U')WHERE t.owner = 'SYSDBA'AND c.table_name IS NULLAND t.table_name IS NOT NULL;

     
4.2.DM查询SQL(指定模式)

    select t.namefrom syscons c
right join sysobjects ton (c.tableid = t.id and c.type$ = 'P')where t.subtype$='UTAB'and t.schid = sf_get_schema_id_by_name('SYSDBA')and c.id is null;

5.备份表或者带log日志表

with t_tab as(select a.owner,a.table_name,a.num_rows,sum(b.bytes)/1024/1024 mbfrom dba_tables a,dba_segments bwhere a.table_name=b.segment_nameand a.owner=b.ownerand a.owner in ('SYS')      and (a.table_name like '%BAK%' OR a.table_name LIKE '%bak%' OR a.table_name like '%LOG%' OR a.table_name LIKE '%log%')and a.num_rows>1000group by a.owner,a.table_name,a.num_rows),t_ind as(select a.owner,a.table_name,a.index_name,sum(b.bytes)/1024/1024 mbfrom dba_indexes a,dba_segments bwhere a.index_name=b.segment_name and a.owner=b.ownergroup by a.owner,a.table_name,a.index_name)select t_tab.owner,t_tab.table_name ,t_tab.num_rows, t_tab.mb as table_size_mb, nvl(t_ind.mb,0) as index_size_mb,(t_tab.mb+nvl(t_ind.mb,0)) as total_size_mbfrom t_tab
left join t_indon t_tab.owner=t_ind.ownerand t_tab.table_name=t_ind.table_nameorder by 4 desc;

6.临时表

SELECT owner, table_name, temporary_tablespace FROM dba_tablesWHERE temporary = 'Y';

7.物化视图日志表

SELECT owner, mview_name, log_tableFROM dba_mviewsWHERE owner = 'Your_User'AND log_owner = 'Your_User';

8.查看用户对象数据总数

8.1达梦指定模式查询

  select count(*), subtype$ from sysobjects where schid = sf_get_schema_id_by_name('PAPERLESS_CONFERENCE') 
group by subtype$;

8.2 通用查询

  select object_type, count(object_name) from dba_objects where owner = 'PAPERLESS' and object_type not like 'SCH' 
group by object_type;

9.查询归档量

  select to_char(completion_time, 'yyyy-mm-dd') as date1,count(0) as cnt,round(sum((blocks * block_size) / 1024 / 1024)) as mbfrom v$archived_log
group by to_char(completion_time, 'yyyy-mm-dd')
order by date1 desc;