Oracle 检查 DATE 列 RANGE 分区表已有分区的最大日期时间

特立独行 2021-09-15 09:17:34
SQL oracle 分区 range


Oracle 检查 DATE 列 RANGE 分区已有分区的最大日期时间

-- 方法1:SYS.TABPART$ 计算时间日期
WITH THIS_PART AS
(SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME
FROM DBA_TAB_PARTITIONS
WHERE (TABLE_OWNER, TABLE_NAME, PARTITION_POSITION) IN
(SELECT B.TABLE_OWNER,
B.TABLE_NAME,
MAX(B.PARTITION_POSITION) - 1 POSITION
FROM DBA_TAB_PARTITIONS B
JOIN DBA_USERS DU
ON B.TABLE_OWNER = DU.USERNAME
AND DU.ACCOUNT_STATUS = 'OPEN'
WHERE B.TABLE_OWNER NOT IN
('SYS', 'SYSTEM', 'DBSNMP', 'MGMT_VIEW', 'SYSMAN', 'SH')
GROUP BY B.TABLE_OWNER, B.TABLE_NAME)),
ALL_PART AS
(SELECT U.NAME USERNAME,
O.NAME TABLENAME,
O.SUBNAME PARTNAME,
TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 3, 2), 'XX') - 100 Y1,
TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 5, 2), 'XX') - 100 Y2,
TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 7, 2), 'XX') M,
TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 9, 2), 'XX') D,
TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 11, 2), 'XX') - 1 HH,
TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 13, 2), 'XX') - 1 MI,
TO_NUMBER(SUBSTR(RAWTOHEX(CAST(BHIBOUNDVAL AS RAW(8))), 15, 2), 'XX') - 1 SS
FROM SYS.TABPART$ TP, SYS.OBJ$ O, SYS.USER$ U
WHERE TP.OBJ# = O.OBJ#
AND O.OWNER# = U.USER#
AND U.NAME NOT IN
('SYS', 'SYSTEM', 'DBSNMP', 'MGMT_VIEW', 'SYSMAN', 'SH')),
TMP AS
(SELECT T2.USERNAME,
T2.TABLENAME,
T2.PARTNAME,
TO_DATE(TRIM(TO_CHAR(T2.Y1 * 100 + T2.Y2, '9999') || '-' ||
TO_CHAR(T2.M, 'FM09') || '-' || TO_CHAR(T2.D, 'FM09') || ' ' ||
TO_CHAR(T2.HH, 'FM09') || ':' ||
TO_CHAR(T2.MI, 'FM09') || ':' ||
TO_CHAR(T2.SS, 'FM09')),
'YYYY-MM-DD HH24:MI:SS') MAX_RANGE
FROM THIS_PART T1
JOIN ALL_PART T2
ON T1.TABLE_OWNER = T2.USERNAME
AND T1.TABLE_NAME = T2.TABLENAME
AND T1.PARTITION_NAME = T2.PARTNAME)
SELECT * FROM TMP WHERE MAX_RANGE <= SYSDATE + 365 ORDER BY USERNAME, TABLENAME;
-- 方法2:取值long字段转换后截取字符串,依赖日期写全了 如2021-09-16 00:00:00,或2021/09/16 00:00:00
WITH XML AS
(SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM DBA_TAB_PARTITIONS WHERE (TABLE_OWNER, TABLE_NAME, PARTITION_POSITION) IN (SELECT B.TABLE_OWNER, B.TABLE_NAME, MAX(B.PARTITION_POSITION) - 1 POSITION FROM DBA_TAB_PARTITIONS B WHERE TABLE_OWNER NOT IN (''SYS'', ''SYSTEM'', ''DBSNMP'', ''MGMT_VIEW'', ''SYSMAN'',''SH'') AND TABLE_OWNER IN (SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS=''OPEN'') GROUP BY B.TABLE_OWNER, B.TABLE_NAME) ORDER BY 1,2') AS X
FROM DUAL),
MAXPART AS
(SELECT EXTRACTVALUE(RWS.OBJECT_VALUE, '/ROW/TABLE_OWNER') TABLE_OWNER,
EXTRACTVALUE(RWS.OBJECT_VALUE, '/ROW/TABLE_NAME') TABLE_NAME,
EXTRACTVALUE(RWS.OBJECT_VALUE, '/ROW/PARTITION_NAME') PARTITION,
EXTRACTVALUE(RWS.OBJECT_VALUE, '/ROW/HIGH_VALUE') HIGH_TEXT
FROM XML X, TABLE(XMLSEQUENCE(EXTRACT(X.X, '/ROWSET/ROW'))) RWS
ORDER BY EXTRACTVALUE(RWS.OBJECT_VALUE, '/ROW/TABLE_NAME')),
DT AS
(SELECT TABLE_OWNER,
TABLE_NAME,
PARTITION,
TO_DATE(SUBSTR(M.HIGH_TEXT, INSTR(M.HIGH_TEXT, '20', -3), 19), 'YYYY-MM-DD HH24:MI:SS') RANGE_DT
FROM MAXPART M)
SELECT * FROM DT WHERE DT.RANGE_DT <= SYSDATE + 365;
版权声明
本文为[特立独行]所创,转载请带上原文链接,感谢
https://segmentfault.com/a/1190000040685714

  1. 快速从 Windows 切换到 Linux 环境
  2. 五分钟向MySql数据库插入一千万条数据
  3. Java日期时间API系列42-----一种高效的中文日期格式化和解析方法
  4. 用Java实现红黑树
  5. 使用Redis Stream来做消息队列和在Asp.Net Core中的实现
  6. 海量列式非关系数据库HBase 架构,shell与API
  7. Architecture, Shell et API de base de données non relationnelle à grande échelle
  8. Mise en œuvre de l'arbre Rouge et noir en Java
  9. Java Date Time API Series 42 - - a efficient Chinese Date Format and Analysis Method
  10. 5 minutes pour insérer 10 millions de données dans la base de données MySQL
  11. Passage rapide de Windows à l'environnement Linux
  12. Notes on Java backend development of PostgreSQL (I)
  13. 海量列式非關系數據庫HBase 架構,shell與API
  14. Byte Jump the latest open source, the most Classic hashtap Graph details,
  15. L'interview Java de Byte Hopping Society, l'analyse super populaire de l'utilisation et du code source de countdownlatch,
  16. "Anti Mafia storm" Wang Zhifei's love history is really wonderful: he divorced Zhang Xinyi and married a 14-year-old wife
  17. In spring in the jade mansion, Jia Fengyuan was not moved by his brother's death. Why was su Yingxue changed? The reason is realistic
  18. Adam Oracle Oracle fully constructs Adam token incentive for ecological development
  19. 实战SpringCloud通用请求字段拦截处理,超过500人面试阿里,
  20. 宅家36天咸鱼翻身入职腾讯,Zookeeper一致性级别分析,
  21. The first starcoin & move hacksong source code analysis - P (a)
  22. Zhaijia 36 days Salt Fish turn into Tencent, Zookeeper Consistency level analysis,
  23. Traitement de l'interception des champs de demande communs de Spring Cloud, plus de 500 personnes interviewent Ali,
  24. About JavaScript modules
  25. Object oriented programming (2)
  26. Java日期时间API系列42-----一种高效的中文日期格式化和解析方法
  27. Java日期時間API系列42-----一種高效的中文日期格式化和解析方法
  28. 宅家36天鹹魚翻身入職騰訊,Zookeeper一致性級別分析,
  29. Java Date Time API Series 42 - - a efficient Chinese Date Format and Analysis Method
  30. 已成功拿下字节、腾讯、脉脉offer,7年老Java一次操蛋的面试经历,
  31. 小米Java社招面试,每次面试必问的二叉树的设计与编码,
  32. 小米Java校招面试,阿里、百度、美团、携程、蚂蚁面经分享,
  33. 小米Java校招面試,阿裏、百度、美團、攜程、螞蟻面經分享,
  34. Xiaomi Java School Recruitment interview, Ali, baidu, meituan, ctrip, ant Facebook Sharing,
  35. La conception et le codage de l'arbre binaire requis pour chaque entrevue d'embauche de la société Java millet;
  36. A remporté avec succès Byte, Tencent, Pulse offer, 7 ans Java une expérience d'entrevue de baise,
  37. 干货来袭,Java岗面试12家大厂成功跳槽,
  38. 常用Java框架面试题目,现在做Java开发有前途吗?
  39. 常用Java框架面試題目,現在做Java開發有前途嗎?
  40. Les questions d'entrevue couramment utilisées pour le cadre Java sont - elles prometteuses pour le développement Java?
  41. L'arrivée de marchandises sèches, l'entretien d'emploi Java 12 grandes usines ont réussi à changer d'emploi,
  42. Multiple postures for handling container time in k8s environment
  43. Echarts remove left Gap, Blank
  44. Hotspot Weekly | zoom $100 million, docker fees, $38 billion Data bricks
  45. JsonMappingException: No serializer found for class org.apache.ibatis.executor.loader.javassist.JavassistProxyFactory...
  46. Java. Security. Securerandom source code analysis Java. Security. EGD = file: / dev /. / urandom
  47. When using IntelliJ idea, jump directly and quickly from the mapper interface to mapper.xml
  48. When idea writes SQL in mybatis XML, the solution to the problems of table name, field and red reporting
  49. Spring cloud integrates Nacos
  50. 应届毕业生Java笔试题目,2021大厂Java社招最全面试题,
  51. Liver explosion! Take you to understand Hadoop serialization
  52. linux系列之:告诉他,他根本不懂kill
  53. java版gRPC实战之三:服务端流
  54. RabbitMQ核心知识总结!
  55. linux系列之:告诉他,他根本不懂kill
  56. java版gRPC实战之三:服务端流
  57. RabbitMQ核心知识总结!
  58. 10天拿到字节跳动Java岗位offer,学习Java开发的步骤
  59. 10天拿到字节跳动Java岗位offer,Java知识点思维导图
  60. Résumé des connaissances de base de rabbitmq!