当前位置: 首页 > news >正文

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

http://www.mrgr.cn/news/74993.html

相关文章:

  • 设计模式(四)装饰器模式与命令模式
  • C# Winform--SerialPort串口通讯(ASCII码发送)
  • 笔记 | image may have poor performance,or fail,if run via emulation
  • SQL Server 查询设置 - LIKE/DISTINCT/HAVING/排序
  • 关于指针p有关的3个值
  • 基于Cocos Creator开发的打砖块游戏
  • 【Go 开发】pprof 排查问题流程:排查程序 CPU 占用高的问题
  • Android Mobile Network Settings | APN 菜单加载异常
  • 解密复杂系统:理论、模型与案例(3)
  • 计算机网络(7)
  • 山泽光纤HDMI线:铜线的隐藏力量
  • 《人类简史:从动物到上帝》读书笔记
  • Redhat7.9 安装 KingbaseES 金仓数据库 V9单机版(静默安装)
  • NFC批量写入网址、文本、应用app、蓝牙
  • 该如何升级Tableau server呢?
  • FastHTML快速入门: Cookies,Sessions,提示,认证和授权
  • 人机界面与人们常说的“触摸屏”有什么区别?这下终于清楚了
  • 谷歌浏览器扩展程序开发指南
  • Linux项目自动化构建工具—make与makefile
  • spring @Qualifier 注解解决依赖注入时类型相同但名称不同的 bean问题
  • window上 opencpn主要文件位置
  • JavaScript——DOM编程、JS的对象和JSON
  • VTK知识学习(8)-坐标系统
  • 18 为什么这些SQL语句逻辑相同,性能却差异巨大?
  • Spring Data Redis常见操作总结
  • Redis使用