【分区表】分区表最大分区字段信息收集
2023-02-06 16:01 明朝散发 阅读(53) 评论(0) 编辑 收藏 举报- 分区表分区最大分区字段sql生成
SELECT
'select nvl(to_char(max(' || column_name || ')),''0'') from ' || owner || '.' || name || ' union all'
FROM
DBA_PART_KEY_COLUMNS
WHERE
name IN ( SELECT DISTINCT table_name FROM dba_tab_partitions WHERE tablespace_name NOT IN ( 'SYSAUX', 'SYSTEM' ) )
ORDER BY
name;
- 分区表时间上限查询
WITH pt1 AS (
SELECT
table_owner,
table_name,
--partition_name,
decode(
instr( sys.long_2_varchar ( t2.table_owner, t2.table_name, t2.partition_name ), 'TIMESTAMP' ),
1,
substr( sys.long_2_varchar ( t2.table_owner, t2.table_name, t2.partition_name ), 12, 11 ),
0,
decode(
instr( sys.long_2_varchar ( t2.table_owner, t2.table_name, t2.partition_name ), 'TO_DATE' ),
1,
substr( sys.long_2_varchar ( t2.table_owner, t2.table_name, t2.partition_name ), 11, 11 ),
0,
sys.long_2_varchar ( t2.table_owner, t2.table_name, t2.partition_name )
)
) ptime
FROM
all_tab_partitions t2
WHERE
TABLE_OWNER NOT IN ( 'SYS', 'SYSTEM' )
AND table_name IN ( SELECT table_name FROM dba_part_tables WHERE OWNER NOT IN ( 'SYS', 'SYSTEM' ) AND partitioning_type = 'RANGE' )
) SELECT
pt1.table_owner,
pt1.table_name,
max( pt1.ptime ) max_partition_time
FROM
pt1
GROUP BY
pt1.table_owner,
pt1.table_name
ORDER BY
max_partition_time;
***long_2_varchar
CREATE
OR REPLACE FUNCTION sys.long_2_varchar ( p_table_owner IN all_tab_partitions.table_owner % TYPE, p_table_name IN all_tab_partitions.table_name % TYPE, p_partition_name IN all_tab_partitions.partition_name % TYPE )
return VARCHAR2 AS l_high_value LONG;
BEGIN
SELECT
high_value INTO l_high_value
FROM
all_tab_partitions
WHERE
table_owner = p_table_owner
AND table_name = p_table_name
AND partition_name = p_partition_name;
return substr( l_high_value, 1, 4000 );
END;
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 使用C#创建一个MCP客户端
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· ollama系列1:轻松3步本地部署deepseek,普通电脑可用
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· 按钮权限的设计及实现