查询数据库主要参数列表sql

最新推荐文章于 2024-08-03 03:58:27 发布

原创最新推荐文章于 2024-08-03 03:58:27 发布 · 617 阅读

0 ·

CC 4.0 BY-SA版权

ORA-DailySQL 专栏收录该内容

16 篇文章

订阅专栏

本文提供了查询Oracle数据库中数据文件自动扩展参数、段扩展参数及实例参数的SQL脚本。通过这些脚本可以详细了解数据库文件如何自动扩展、段如何分配空间以及当前实例的具体配置。

------**************Datafile Disk Allocation Parameter************
SELECT *
  FROM (SELECT T.FILE_NAME,
               T.AUTOEXTENSIBLE,
               T.INCREMENT_BY *
               (SELECT TT.VALUE
                  FROM V$PARAMETER TT
                 WHERE LOWER(TT.NAME) = 'db_block_size') / 1024 / 1024 || 'M' AS NEXT_AUTOEXTEND_DELTA
          FROM DBA_DATA_FILES T
        UNION ALL
        SELECT '**************Datafile Disk Allocation Parameter Above************',
               '',
               ''
          FROM DUAL)
 ORDER BY 1;

------**************Extent Allocation Parameter************
SELECT *
  FROM (SELECT T.TABLESPACE_NAME,
               T.BLOCK_SIZE,
               NVL(T.INITIAL_EXTENT, 0) / 1024 || 'K' AS INITIAL_EXTENT,
               NVL(T.NEXT_EXTENT, 0) / 1024 || 'K' AS NEXT_EXTENT,
               T.PCT_INCREASE,
               T.LOGGING,
               T.EXTENT_MANAGEMENT,
               T.ALLOCATION_TYPE,
               T.SEGMENT_SPACE_MANAGEMENT
          FROM DBA_TABLESPACES T
        UNION ALL
        SELECT '**************Extent Allocation Parameter Above************',
               NULL,
               NULL,
               NULL,
               NULL,
               NULL,
               NULL,
               NULL,
               NULL
          FROM DUAL)
 ORDER BY 1;

------**************Instance Parameter************
SELECT *
  FROM (SELECT T.NAME, T.VALUE
          FROM V$PARAMETER T
         WHERE T.VALUE IS NOT NULL
           AND T.VALUE NOT LIKE '%/%'
           AND T.VALUE NOT LIKE '%:%'
        UNION ALL
        SELECT '**************Instance Parameter Above************', NULL
          FROM DUAL)
 ORDER BY 1;