テーブルとインデックスのサイズ、件数を取得するスクリプト

USER_EXTENTS からテーブルとインデックスのサイズを集計するとともに、テーブルの場合は件数も取得して標準出力に出力します。

GetTableSize.ps1

[void][reflection.assembly]::LoadWithPartialName("Oracle.DataAccess")

$conStr = "Data Source=XE;User Id=hr;Password=hr"

$con = New-Object Oracle.DataAccess.Client.OracleConnection($conStr)
$con.Open()

$sqlStr="select segment_name,segment_type,sum(bytes)/1024/1024 as MB 
from user_extents 
group by segment_name,segment_type
order by segment_type,segment_name"

$cmd = New-Object Oracle.DataAccess.Client.OracleCommand($sqlStr, $con)
$reader = $cmd.ExecuteReader()

$outputDataFormat="  {0, -10} {1, -30} {2, 10} {3, 15}"
Write-Host ($outputDataFormat -F "TYPE", "OBJECT_NAME" ,"SIZE(MB)", "RECORD_COUNT")
while ( $reader.read() )
{
    if ($reader[1] -eq "TABLE"){
        $countSql=("select count(*) from {0}" -F $reader[0])
        $cmd = New-Object Oracle.DataAccess.Client.OracleCommand($countSql, $con)
        $countReader = $cmd.ExecuteReader()
        [void]$countReader.read()

        Write-Host ($outputDataFormat -F $reader[1] ,$reader[0] ,$reader[2], $countReader[0])
    }else{
        Write-Host ($outputDataFormat -F $reader[1] ,$reader[0] ,$reader[2], "")
    }
}
$con.Close()

以下のような結果が出力されます。

  TYPE       OBJECT_NAME                      SIZE(MB)    RECORD_COUNT
  INDEX      COUNTRY_C_ID_PK                    0.0625
  INDEX      DEPT_ID_PK                         0.0625
  INDEX      DEPT_LOCATION_IX                   0.0625
  INDEX      EMP_DEPARTMENT_IX                  0.0625
  INDEX      EMP_EMAIL_UK                       0.0625
  INDEX      EMP_EMP_ID_PK                      0.0625
  INDEX      EMP_JOB_IX                         0.0625
  INDEX      EMP_MANAGER_IX                     0.0625
  INDEX      EMP_NAME_IX                        0.0625
  INDEX      JHIST_DEPARTMENT_IX                0.0625
  INDEX      JHIST_EMPLOYEE_IX                  0.0625
  INDEX      JHIST_EMP_ID_ST_DATE_PK            0.0625
  INDEX      JHIST_JOB_IX                       0.0625
  INDEX      JOB_ID_PK                          0.0625
  INDEX      LOC_CITY_IX                        0.0625
  INDEX      LOC_COUNTRY_IX                     0.0625
  INDEX      LOC_ID_PK                          0.0625
  INDEX      LOC_STATE_PROVINCE_IX              0.0625
  INDEX      REG_ID_PK                          0.0625
  TABLE      DEPARTMENTS                        0.0625              27
  TABLE      EMPLOYEES                          0.0625             107
  TABLE      JOBS                               0.0625              19
  TABLE      JOB_HISTORY                        0.0625              10
  TABLE      LOCATIONS                          0.0625              23
  TABLE      REGIONS                            0.0625               4