s Oracleメモ

Oracleメモ

０．更新履歴

2000.12.13　新規作成
2001.10.21　色々追加．
2001.10.22　インデックスの断片化分析方法の追加．

１．はじめに

　はたして，DBAといっていいのだろうか．
　忘れっぽいので，色々やったことをここに記述しておくだけである．

２．ロールバックセグメントの表示

SQL> select segment_name,tablespace_name from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
SYSTEM                         SYSTEM
R01                            RBS
R02                            RBS
R03                            RBS
R04                            RBS

SQL>

３．SQL*Plusの環境変数について

set pagesize 0	ヘッダーを表示しない
set feedback off	〜行選択されました等のMessageを表示しない
set linesize 100	出力するファイルのレコードサイズを指定すると良い

４．読取専用トランザクションの実行

SELECT TRANSACTION READ ONLY

５．フィールド定義を変更する

　tablenameテーブルのfieldフィールドの定義を，number(12)に変更する．

ALTER TABLE tablename MODIFY (field NUMBER(12))

６．セッション状況を表示する

SQL> select username,sid,serial#,status from v$session;

USERNAME                              SID    SERIAL# STATUS
------------------------------ ---------- ---------- --------
                                        1          1 ACTIVE
                                        2          1 ACTIVE
                                        3          1 ACTIVE
                                        4          1 ACTIVE
                                        5          1 ACTIVE
                                        6          1 ACTIVE
                                        7          1 ACTIVE
SCOTT                                   9      62124 INACTIVE
SCOTT                                  12      49632 INACTIVE
SCOTT                                  23      22113 INACTIVE
SCOTT                                  25      18112 INACTIVE

USERNAME                              SID    SERIAL# STATUS
------------------------------ ---------- ---------- --------
SCOTT                                  29      42437 INACTIVE
SCOTT                                  31       6000 INACTIVE
SCOTT                                  32      29182 INACTIVE
SCOTT                                  35      21999 INACTIVE
SCOTT                                  36      11228 INACTIVE
SCOTT                                  37      31826 INACTIVE
SCOTT                                  38      39692 INACTIVE
SCOTT                                  42      49760 INACTIVE
SCOTT                                  46      20954 INACTIVE
SCOTT                                  47      51806 INACTIVE
SCOTT                                  49      35017 INACTIVE

22 rows selected.

SQL>

７．セッションを切断する

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

８．ユーザが行った操作を追跡する

AUDIT用の表を作成する．
- CATAUDIT.SQL
初期化パラメータファイルのAUDIT_TRAILをDBにする．（DBの再起動）
AUDIOコマンドで監査を設定する．
NOAUDITコマンドで監査を停止する．
監査ログを解析する．

　この時，system表領域にログが貯まる．

９．WindowsのNTでインスタンスのエントリを削除する

oradim80 -delete -sid ORCL oradim80 -delete -srvc OracleStartORCL oradim80 -delete -srvc OracleServiceORCL

１０．データファイルの空状態・断片化数表示

     select tablespace_name,file_id,
              count(*)      "Pieces",
              max(blocks)  "Maximum",
              min(blocks)  "Minimum",
              avg(blocks)  "Average",
              sum(blocks) "Total"
        from dba_free_space
        group by tablespace_name,file_id
        order by tablespace_name,file_id;

１１．表領域へデータファイルの追加を行う

ALTER TABLESPACE 表領域 ADD DATAFILE 'データファイル名.dbf' SIZE 100m;

１２.データファイルの表示

SELECT * FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;

１３.テーブルスペース容量情報等の表示

１３．１．前準備

sysユーザでログインして以下のSQLを実行する．

create view V_FREESPACE as 
  select tablespace_name as
    tablespace,
    file_id as
     id,
     count(*) as
       pieces,
       sum(bytes) as
         byte
  from dba_free_space
    group by
      tablespace_name,
      file_id
    with read only;
grant select on v_freespace to public;

１３．２．テーブルスペース使用量表示

select f.tablespace "Tablespace",
round(sum(d.bytes)/1048576) "Total MB",
round((sum(d.bytes)-sum(f.byte))/1048576) "Used MB",
round(sum(f.byte)/1048576) "Free MB",
round((sum(d.bytes)-sum(f.byte))/sum(d.bytes)*100,1) "Used %"
from sys.v_freespace f, dba_data_files d
where f.id=d.file_id
group by f.tablespace
order by f.tablespace;

１３．３．ＤＢファイル情報表示

select d.tablespace_name "Tablespace",
round(sum(d.bytes)/1048576) "Alloc MB",
sum(f.pieces) "Pieces",
d.file_name "File Name"
from dba_data_files d, sys.v_freespace f
where d.file_id=f.id
group by d.tablespace_name,d.file_name
order by d.tablespace_name,d.file_name;

１４．インデックス関連

インデクス名検索

SELECT INDEX_NAME FROM USER_INDEXES
                       WHERE TABLE_NAME='table_name';

インデックスカラム表示

SELECT * FROM USER_IND_COLUMNS
                       WHERE INDEX_NAME='index_name';

プライマリーインデックス削除

ALTER TABLE table_name DROP CONSTRAINT index_name;

インデックス削除

DROP INDEX index_name;

１４．１．インデックスの断片化の解消

　B-Treeのインデックスは，使いつづけると断片化をおこす．　よって，断片化が発生しているかを調べ，必要であればインデックススの再作成を行う．

インデックスを分析する．

SQL>analyze index インデックス名 validate structure;

アナライズの結果を表示する．

SQL>select
2> lf_rows,
3> lf_rows_len,
4> del_lf_rows,
5> del_lf_rows_len
6> from index_status
7> where name = 'インデックス名';<CR>

LF_ROWS　LF_ROWS_LEN　DEL_ROWS　DEL_LF_ROWS_LEN
-------- ----------- --------- ----------------
    2589       92340        97             3432

現在のインデックスが2589行分ある．
その物理消費量が92340バイトとなる．
削除された行に対する空のインデックスが97個．
その空が3432バイトの領域を使っている．
97/2580で3.7%のが無駄に使われている．
この値が10％を越えたら，インデックスの再作成を行う．

１５．データファイルの移動

表領域をオフラインにする．

ALTER TABLESPACE 表領域名 OFFLINE;

OSコマンド等で，ファイルを移動する.
データファイル名を変更する.

ALTER TABLESPACE 表領域名
RENAME DATAFILE
 '移動する前のパスとファイル名.DBF'
TO
 '移動した後のパスとファイル名.DBF';

表領域をオンラインにする．

ALTER TABLESPACE 表領域名 ONLINE;

１６．テーブルのサイズを調べる

以下のこういうテーブルの場合．

SVRMGR> desc emp;
Column Name                    Null?    Type
------------------------------ -------- ----
EMPNO                          NOT NULL NUMBER(4)
ENAME                                   VARCHAR2(10)
JOB                                     VARCHAR2(9)
MGR                                     NUMBER(4)
HIREDATE                                DATE
SAL                                     NUMBER(7,2)
COMM                                    NUMBER(7,2)
DEPTNO                                  NUMBER(2)
SVRMGR> select

以下の様にフィールドを指定する．

SVRMGR> select
     2> avg(nvl(vsize(
     3> empno
     4> ),1)) +
     5> avg(nvl(vsize(
     6> ename
     7> ),1)) +
     8> avg(nvl(vsize(
     9> job
    10> ),1)) +
    11> avg(nvl(vsize(
    12> mgr
    13> ),1)) +
    14> avg(nvl(vsize(
    15> hiredate
    16> ),1)) +
    17> avg(nvl(vsize(
    18> sal
    19> ),1)) +
    20> avg(nvl(vsize(
    21> comm
    22> ),1)) +
    23> avg(nvl(vsize(
    24> deptno
    25> ),1))
    26> from emp;
AVG(NVL(VS
----------
30.2857143
1 row selected.
SVRMGR>

1レコード平均30Byteとわかる．

１７．ソートの分析

SVRMGR> SELECT NAME,VALUE FROM v$sysstat WHERE NAME LIKE '%sort%';

NAME                                              VALUE
------------------------------------------------- ----------
sorts (memory)                                          6457
sorts (disk)                                               0
sorts (rows)                                          843823
3 行選択されました。
SVRMGR>

　ディスクが使われていないため,ソートはメモリ上で行われている．