Oracleのメモ

抽出

#!/bin/bash

# 引数の初期化
start_date=""
end_date=""

# 引数の解析
while getopts ":--:start-time:--:end-time:" opt; do
  case $opt in
    -)
      case $OPTARG in
        start-time)
          start_date="${!OPTIND}"; OPTIND=$(( $OPTIND + 1 ))
          ;;
        end-time)
          end_date="${!OPTIND}"; OPTIND=$(( $OPTIND + 1 ))
          ;;
      esac
      ;;
    \?)
      echo "Invalid option: -$OPTARG" >&2
      exit 1
      ;;
  esac
done

# 引数が適切でない場合はエラーメッセージを表示してスクリプトを終了
if [[ -z "$start_date" || -z "$end_date" ]]; then
    echo "Usage: $0 --start-time 'YYYY-MM-DD HH:MM:SS' --end-time 'YYYY-MM-DD HH:MM:SS'"
    exit 1
fi

# SQLクエリの実行と結果の保存
sqlplus -s username/password@database <<EOF > query_result.txt
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SET PAGESIZE 0
SELECT 
    TO_CHAR(TRUNC(CREATE_DATE, 'HH'), 'YYYY-MM-DD HH24:MI:SS') AS create_hour,
    sender, 
    COUNT(*)
FROM 
    amimsadm01.t_forward_mail
GROUP BY 
    TRUNC(CREATE_DATE, 'HH'),
    sender
ORDER BY 
    TRUNC(CREATE_DATE, 'HH'),
    sender;
EXIT;
EOF

# メールアドレスリストをループ
while read -r email; do
    current_date=$start_date
    while [[ "$current_date" < "$end_date" ]]; do
        # 時間帯とメールアドレスがSQLクエリの結果に存在するかチェック
        if grep -q "$current_date,$email," query_result.txt; then
            grep "$current_date,$email," query_result.txt
        else
            echo "$current_date,$email,0"
        fi
        # 次の時間帯へ
        current_date=$(date -d "$current_date + 1 hour" +"%Y-%m-%d %H:%M:%S")
    done
done < mail.list

UNDO

合計

SELECT d.tablespace_name,
       d.total_mb,
       u.used_mb,
       (u.used_mb / d.total_mb) * 100 AS used_percent
FROM   (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb 
        FROM dba_data_files 
        WHERE tablespace_name = '[UNDO表領域の名前]' 
        GROUP BY tablespace_name) d,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS used_mb 
        FROM dba_segments 
        WHERE tablespace_name = '[UNDO表領域の名前]' 
        GROUP BY tablespace_name) u
WHERE  d.tablespace_name = u.tablespace_name;

もう少し細かく

col undo_tablespace_size_mib format 9999D99
col total_mib format 9999D99
col used_percent format 9999D99
col active_mib format 9999D99
col unexpired_mib format 9999D99

SELECT
  d.total_mb AS undo_tablespace_size_mib,
  NVL(a.active_mib, 0) + NVL(u.unexpired_mib, 0) AS total_mib,
  (NVL(a.active_mib, 0) + NVL(u.unexpired_mib, 0)) / d.total_mb * 100 AS used_percent,
  NVL(a.active_mib, 0) AS active_mib,
  NVL(u.unexpired_mib, 0) AS unexpired_mib
FROM
  (SELECT SUM(bytes) / (1024 * 1024) AS total_mb FROM dba_data_files WHERE tablespace_name = 'UNDOTBLSP01') d,
  (SELECT SUM(bytes) / (1024 * 1024) AS active_mib FROM dba_undo_extents WHERE status = 'ACTIVE') a,
  (SELECT SUM(bytes) / (1024 * 1024) AS unexpired_mib FROM dba_undo_extents WHERE status = 'UNEXPIRED') u;

PLSQLでupdate

DECLARE
    CURSOR rec_cursor IS SELECT ROWID FROM 表.テーブル;
    l_rowid ROWID;
    l_count NUMBER := 0;
BEGIN
    OPEN rec_cursor;

    LOOP
        FETCH rec_cursor INTO l_rowid;
        EXIT WHEN rec_cursor%NOTFOUND;

        UPDATE 表.テーブル SET RECIPIENT = '[e-mail]' WHERE ROWID = l_rowid;

        l_count := l_count + 1;

        IF l_count >= 500 THEN
            COMMIT;
            l_count := 0;
        ELSE
            -- 1秒間の遅延を追加
            DBMS_LOCK.SLEEP(1);
        END IF;
    END LOOP;

    COMMIT;
    CLOSE rec_cursor;
END;

export

expdp username/password DIRECTORY=directory_name DUMPFILE=dumpfile_name.dmp TABLES=AMIMSADM01.T_FORWARD_MAIL,AMIMSADM01.T_MAIL_STATUS,AMIMSADM01.T_POSTFIX_Q LOGFILE=export.log

表領域確認

SELECT df.tablespace_name "Tablespace",
       totalusedspace "Used MB",
       (df.totalspace - tu.totalusedspace) "Free MB",
       df.totalspace "Total MB",
       ROUND((totalusedspace / df.totalspace) * 100, 2) "Percent Used"
  FROM (SELECT tablespace_name,
               ROUND(SUM(bytes) / 1048576) TotalSpace
          FROM dba_data_files
         GROUP BY tablespace_name) df,
       (SELECT tablespace_name,
               ROUND(SUM(bytes) / 1048576) TotalUsedSpace
          FROM dba_segments
         GROUP BY tablespace_name) tu
 WHERE df.tablespace_name = tu.tablespace_name
   AND df.tablespace_name = 'USERTBLSP01';