记录日常工作关于系统运维,虚拟化云计算,数据库,网络安全等各方面问题。

ORACLE数据库如何查看分区表的相关信息

如下语句,根据Toad所用SQL,其中可以根据自己的情况修改表名与表所属用户。


Select
  TABLE_NAME, PARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, PARTITION_POSITION,
  TABLESPACE_NAME, LOGGING, nvl(INI_TRANS, -1) ini_trans, nvl(MAX_TRANS, -1) max_trans,
  nvl(INITIAL_EXTENT, -1) initial_extent, nvl(NEXT_EXTENT, -1) next_extent,
  nvl(MIN_EXTENT, -1) MIN_EXTENT, nvl(MAX_EXTENT, -1) MAX_EXTENT, nvl(PCT_INCREASE, -1) PCT_INCREASE, nvl(PCT_FREE, -1) PCT_FREE,
  nvl(PCT_USED, -1) pct_used, nvl(FREELISTS, -1) freelists, nvl(FREELIST_GROUPS, -1) freelist_groups, buffer_pool,
  LAST_ANALYZED, nvl(NUM_ROWS, -1) NUM_ROWS, nvl(BLOCKS, -1) BLOCKS, nvl(EMPTY_BLOCKS, -1) EMPTY_BLOCKS, nvl(AVG_SPACE, -1) AVG_SPACE
  ,SUBPARTITION_COUNT
  ,COMPRESSION
  ,COMPRESS_FOR
  ,FLASH_CACHE, CELL_FLASH_CACHE
FROM SYS.DBA_TAB_PARTITIONS
WHERE TABLE_OWNER = 'NEC'
and TABLE_NAME = 'TH_REPORT'
ORDER BY TABLE_NAME, PARTITION_POSITION;


图片.png


Select PARTITION_NAME,
  HIGH_VALUE, HIGH_VALUE_LENGTH, TABLESPACE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, LAST_ANALYZED, AVG_SPACE
  ,SUBPARTITION_COUNT
  ,COMPRESSION
FROM sys.DBA_TAB_PARTITIONS
WHERE table_name= 'TH_REPORT'
AND TABLE_OWNER= 'NEC'
ORDER BY PARTITION_POSITION

图片.png



转载请标明出处【Oracle查看分区表相关信息与数据】。

《www.92cto.com》 虚拟化云计算,系统运维,安全技术服务.

网站已经关闭评论