テーブルスペースの利用状況

テーブルスペースの総容量と使用率を確認するためのクエリです。

SELECT ts.tablespace_name,
       ROUND(SUM(df.bytes) / 1024 / 1024 / 1024, 2) AS total_size_gb,
       ROUND(SUM(df.bytes) / 1024 / 1024 / 1024 - SUM(fs.bytes) / 1024 / 1024 / 1024, 2) AS used_size_gb,
       ROUND(SUM(fs.bytes) / 1024 / 1024 / 1024, 2) AS free_size_gb,
       ROUND(SUM(fs.bytes) / SUM(df.bytes) * 100, 2) || '%' AS free_rate
FROM dba_data_files df
LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name
GROUP BY ts.tablespace_name;

ユーザーとテーブルスペースの関連

ユーザーが使用するデフォルトのテーブルスペースを確認します。

SELECT username, default_tablespace
FROM dba_users
ORDER BY username;

データファイルのパス確認

特定のテーブルスペースに属するデータファイルのパスを取得します。

SELECT file_name, tablespace_name
FROM dba_data_files
WHERE tablespace_name = 'USERS'
AND file_name LIKE '%2023%';

データベース全体のサイズ

データベースの総容量をGB単位で表示します。

SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) || ' GB' AS total_size
FROM dba_segments;

特定ユーザーの使用容量

指定されたユーザーが使用しているデータの総量を表示します。

SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) || ' GB' AS user_size
FROM dba_segments
WHERE owner = 'ANALYTICS';

テーブルのサイズ確認

特定のテーブルの使用容量を表示します。

SELECT segment_name,
       ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb,
       tablespace_name
FROM dba_segments
WHERE owner = 'REPORTING'
AND segment_name = 'SALES_DATA';

実行中のセッションとSQL

アクティブなセッションとその実行中のSQLを確認します。

SELECT s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       s.status,
       s.logon_time,
       ROUND((SYSDATE - s.logon_time) * 24 * 60, 2) AS login_duration_minutes,
       s.sql_id,
       q.sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
AND s.wait_class != 'Idle'
ORDER BY s.logon_time DESC;

ブロッキングセッションの確認

ブロッキング関係を階層的に表示します。

SELECT LPAD(' ', 5 * (LEVEL - 1)) || s.username AS user_name,
       LPAD(' ', 5 * (LEVEL - 1)) || s.sid AS session_id,
       s.serial#,
       s.sql_id,
       s.event,
       s.seconds_in_wait
FROM v$session s
WHERE s.blocking_session IS NOT NULL
OR s.sid IN (SELECT DISTINCT blocking_session FROM v$session)
START WITH s.blocking_session IS NULL
CONNECT BY PRIOR s.sid = s.blocking_session;

パフォーマンス分析

データバッファキャッシュのヒット率を計算します。

SELECT 1 - ((physical.value - direct.value) / logical.value) AS hit_ratio
FROM v$sysstat physical,
     v$sysstat direct,
     v$sysstat logical
WHERE physical.name = 'physical reads'
  AND direct.name = 'physical reads direct'
  AND logical.name = 'session logical reads';

最近実行されたSQLの確認

最近実行されたSQLを確認します。

SELECT sql_text,
       last_load_time
FROM v$sql
WHERE last_load_time IS NOT NULL
ORDER BY last_load_time DESC
FETCH FIRST 10 ROWS ONLY;

テーブルのDDL変更日時

テーブルの最後のDDL操作日時を確認します。

SELECT owner,
       object_name,
       object_type,
       TO_CHAR(last_ddl_time, 'YYYY-MM-DD HH24:MI:SS') AS last_ddl_time
FROM dba_objects
WHERE owner = 'HR'
  AND object_type = 'TABLE'
  AND last_ddl_time > SYSDATE - 30;

セッションのIPアドレス確認

セッションのIPアドレスを確認します。

SELECT s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       s.client_info AS ip_address
FROM v$session s
WHERE s.username IS NOT NULL
ORDER BY s.username, s.machine;