异构迁移常用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;