T_S_TAB_PARTITION

本文详细介绍了如何创建和管理数据库表的分区,并配置相应的表空间,包括分区的类型、数量、日期、状态和存储参数等关键信息。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

-- Create table
create table LBI_SYS_VDF.T_S_TAB_PARTITION
(
  OWNER                VARCHAR2(250),
  TABLE_NAME           VARCHAR2(250),
  PARTITION_NAME       VARCHAR2(250),
  TABLESPACE_NAME      VARCHAR2(250),
  TABLESPACE_NAME_ODD  VARCHAR2(250),
  TABLESPACE_NAME_EVEN VARCHAR2(250),
  COMPRESSION          VARCHAR2(1),
  PARTITION_COUNT      NUMBER(38),
  PARTITION_DATE       DATE,
  PROC_FLAG_ID         VARCHAR2(1),
  DEAL_CYCLE           VARCHAR2(8),
  PARTITION_TYPE_ID    VARCHAR2(1),
  TABLESPACE_TYPE_ID   VARCHAR2(1),
  LAST_DAY_TYPE_ID     VARCHAR2(1),
  DATE_TYPE_ID         VARCHAR2(1),
  MAX_DELETE_DATE      VARCHAR2(14)
)
tablespace TBS_LBI_SYS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
WITH seg_data AS ( /* 预分类处理段数据 */ SELECT s.owner, s.segment_name, s.partition_name, s.bytes, CASE WHEN s.segment_type IN ('TABLE', 'TABLE PARTITION') THEN 'TABLE' WHEN s.segment_type IN ('INDEX', 'INDEX PARTITION') THEN 'INDEX' WHEN s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION', 'LOBINDEX') THEN 'LOB' ELSE 'OTHER' END AS category FROM dba_segments s ) SELECT t.owner, t.table_name, ROUND(SUM(CASE WHEN sd.category = 'TABLE' THEN sd.bytes ELSE 0 END) / 1073741824, 2) AS table_size_gb, ROUND(SUM(CASE WHEN sd.category = 'INDEX' THEN sd.bytes ELSE 0 END) / 1073741824, 2) AS index_size_gb, ROUND(SUM(CASE WHEN sd.category = 'LOB' THEN sd.bytes ELSE 0 END) / 1073741824, 2) AS lob_size_gb, ROUND(SUM(sd.bytes) / 1073741824, 2) AS total_size_gb FROM dba_tables t /* 精确连接逻辑 */ LEFT JOIN ( /* 普通表段 */ SELECT owner, segment_name AS table_name, bytes, category FROM seg_data WHERE category = 'TABLE' UNION ALL SELECT p.table_owner AS owner, p.table_name, s.bytes, s.category FROM seg_data s JOIN dba_tab_partitions p ON s.partition_name = p.partition_name AND s.owner = p.table_owner WHERE s.category = 'TABLE' UNION ALL SELECT i.owner, i.table_name, s.bytes, s.category FROM seg_data s JOIN dba_indexes i ON s.segment_name = i.index_name AND s.owner = i.owner WHERE s.category = 'INDEX' UNION ALL SELECT l.owner, l.table_name, s.bytes, s.category FROM seg_data s JOIN dba_lobs l ON (s.segment_name = l.segment_name OR s.segment_name = l.index_name) AND s.owner = l.owner WHERE s.category = 'LOB' ) sd ON t.owner = sd.owner AND t.table_name = sd.table_name GROUP BY t.owner, t.table_name ORDER BY total_size_gb DESC; 能很快的出来结果 ,但数据都是0 ,请优化
08-01
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值