Oracle Tips

undoセグメント使用状況
redo生成サイズ確認
ランダム文字列、乱数の生成
ROWIDとは
フルパス指定のファイル名部分を切り出す
テーブルとカラムへのコメント追加
よく使う環境変数
SQL*Plusでの見た目調整
1. 列ヘッダー（見出し）を二段表示
SQL*Plusで１行が長くなった場合（ハイフン）で
アラートログファイルの所在を確認する
バッファキャッシュの内容を確認する
テーブルを異なる表領域へ移動
リソースの消費状況確認
各種ALTER TABLE
REDOログを調べる
テーブルを素早くバックアップ
アーカイブログ出力モードになっているか
アーカイブログ出力モードへの切り替え
パッチ適用の確認
データディクショナリに関するTips
1. データディクショナリ一覧を出力するSQL
DBリンク関するTips
マテビュ―に関するTips
テーブル情報に関するTips
1. PKを作成するSQL
2. ユニークインデックスを作成するSQL
3. カラムを追加するSQL
4. カラムを削除するSQL

undoセグメント使用状況

V$SESSIONとV$TRANSACTIONを結合し該当セッションにおける更新ブロックサイズを確認します。トランザクションが発生している間確認でき、終了とともに(commit/rollback)消えていきます。

select s.INST_ID,s.SID,s.SERIAL#,s.USERNAME,used_ublk,t.USED_UREC,t.STATUS,t.START_DATE

from gv$transaction t inner join gv$session s

on t.ADDR = s.TADDR

;

select s.INST_ID,s.SID,s.SERIAL#,s.USERNAME,used_ublk,t.USED_UREC,t.STATUS,t.START_DATE from gv$transaction t inner join gv$session s on t.ADDR = s.TADDR ;

select s.INST_ID,s.SID,s.SERIAL#,s.USERNAME,used_ublk,t.USED_UREC,t.STATUS,t.START_DATE
from gv$transaction t inner join gv$session s
on t.ADDR = s.TADDR
;

redo生成サイズ確認

v$sysstatの値を定期観測する事で、各種待機イベント発生状況が把握できる

統計情報の説明

set lin 100

COLUMN class_dec FORMAT A18

COLUMN name FORMAT A24

set lin 100 COLUMN class_dec FORMAT A18 COLUMN name FORMAT A24

set lin 100
COLUMN class_dec FORMAT A18
COLUMN name FORMAT A24

select s.STATISTIC# -- 統計番号

,decode(s.CLASS,1,'ユーザ',2,'REDO',4,'エンキュー',8,'キャッシュ',16,'OS',32,'RAC',64,'SQL',128,'デバッグ','???') class_dec

,s.NAME --統計名K

,s.VALUE -- 統計値

from v$sysstat s where name in (

'redo size' --生成されたREDOの合計バイト数

,'application wait time' --アプリケーションの待機クラスに属する待機の合計待機時間(100分の1秒単位)

)

;

-- select s.STATISTIC# -- 統計番号 ,decode(s.CLASS,1,'ユーザ',2,'REDO',4,'エンキュー',8,'キャッシュ',16,'OS',32,'RAC',64,'SQL',128,'デバッグ','???') class_dec ,s.NAME --統計名K ,s.VALUE -- 統計値 from v$sysstat s where name in ( 'redo size' --生成されたREDOの合計バイト数 ,'application wait time' --アプリケーションの待機クラスに属する待機の合計待機時間(100分の1秒単位) ) ;

-- 
select s.STATISTIC# -- 統計番号
,decode(s.CLASS,1,'ユーザ',2,'REDO',4,'エンキュー',8,'キャッシュ',16,'OS',32,'RAC',64,'SQL',128,'デバッグ','???') class_dec
,s.NAME  --統計名K
,s.VALUE  -- 統計値
from v$sysstat s where name in ( 
'redo size' --生成されたREDOの合計バイト数
,'application wait time' --アプリケーションの待機クラスに属する待機の合計待機時間(100分の1秒単位)
)
;

ランダム文字列、乱数の生成

ROWIDとは

長くなったので以下に切り出しました

フルパス指定のファイル名部分を切り出す

意外と活躍します

select REGEXP_SUBSTR('/usr/oracle/app/kore.txt','[^/]*[^/]$') from dual

;

select REGEXP_SUBSTR('/usr/oracle/app/kore.txt','[^/]*[^/]$') from dual ;

select REGEXP_SUBSTR('/usr/oracle/app/kore.txt','[^/]*[^/]$') from dual
;

SQL> select REGEXP_SUBSTR('/usr/oracle/app/kore.txt','[^/]*[^/]$') from dual
  2  ;

REGEXP_S
--------
kore.txt

SQL>

テーブルとカラムへのコメント追加

長くなったので以下に切り出しました

よく使う環境変数

長くなったので以下に切り出しました

SQL*Plusでの見た目調整

列ヘッダー（見出し）を二段表示

COLUMN PARTITION_NAME HEADING 'PARTITION|NAME'

COLUMN PARTITION_NAME HEADING 'PARTITION|NAME'

SQL*Plusで１行が長くなった場合（ハイフン）で

SQL*Plusでは１行が長くなった場合、”-” (ハイフン）で次の行に続ける事ができます。以下例はプロシージャをEXECで実行していますが、パラメータが多く横長になりすぎるので、ハイフンで改行しています。

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI', -

tabname => 'TBL26', -

cascade => FALSE, -

method_opt => 'FOR COLUMNS AUTO_ID,COL5')

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI', - tabname => 'TBL26', - cascade => FALSE, - method_opt => 'FOR COLUMNS AUTO_ID,COL5')

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI', -
tabname => 'TBL26', -
cascade => FALSE, -
method_opt => 'FOR COLUMNS AUTO_ID,COL5')

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI', -
  2  tabname => 'TBL26', -
  3  cascade => FALSE, -
  4  method_opt => 'FOR COLUMNS AUTO_ID,COL5')

PL/SQLプロシージャが正常に完了しました。

SQL>

上記はexecを使ってPL/SQLステートメントを実行していましたが、以下のようにbegin/endで囲むブロック実行をするとハイフンを使う必要もなくなります。

begin

DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',

tabname => 'TBL26',

cascade => FALSE,

method_opt => 'FOR COLUMNS AUTO_ID,COL5');

end;

begin DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI', tabname => 'TBL26', cascade => FALSE, method_opt => 'FOR COLUMNS AUTO_ID,COL5'); end;

begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',
tabname => 'TBL26',
cascade => FALSE,
method_opt => 'FOR COLUMNS AUTO_ID,COL5');
end;

アラートログファイルの所在を確認する

当方12cなので以下にありました。初期化パラメータ（v$parameter）にあるかと探したのですがなぜかみつからず、、
$ORACLE_BASE/diag/rdbms/<SID>/<SID>/trace/alert_<SID>.log
に存在します。

バッファキャッシュの内容を確認する

テーブルを異なる表領域へ移動

alter table SOONI.EX01 move TABLESPACE USERS

;

alter table SOONI.EX01 move TABLESPACE USERS ;

alter table SOONI.EX01 move TABLESPACE USERS
;

リソースの消費状況確認

select resource_name,

current_utilization now,

max_utilization max,

to_number(initial_allocation) init

from v$resource_limit

where limit_value != ' UNLIMITED'

and resource_name not like '\_%' escape '\'

order by resource_name;

select resource_name, current_utilization now, max_utilization max, to_number(initial_allocation) init from v$resource_limit where limit_value != ' UNLIMITED' and resource_name not like '\_%' escape '\' order by resource_name;

select resource_name,
current_utilization now,
max_utilization max,
to_number(initial_allocation) init
from v$resource_limit
where limit_value != ' UNLIMITED'
and resource_name not like '\_%' escape '\'
order by resource_name;

各種ALTER TABLE

-- バッファキャッシュKEEPプールに読み込み

ALTER TABLE EX01 STORAGE(BUFFER_POOL KEEP)

;

--フルスキャンするとバッファキャッシュに保存される

ALTER TABLE EX01 cache

;

-- バッファキャッシュKEEPプールに読み込み ALTER TABLE EX01 STORAGE(BUFFER_POOL KEEP) ; --フルスキャンするとバッファキャッシュに保存される ALTER TABLE EX01 cache ;

-- バッファキャッシュKEEPプールに読み込み
ALTER TABLE EX01 STORAGE(BUFFER_POOL KEEP)
;

--フルスキャンするとバッファキャッシュに保存される
ALTER TABLE EX01 cache
;

REDOログを調べる

UNUSED – オンラインREDOログが、まだ書き込まれていない
CURRENT – 現行のREDOログ。これは、REDOログがアクティブであることを意味する
ACTIVE – ログはアクティブだが、現行のログではない。クラッシュ・リカバリで必要
INACTIVE – ログがインスタンス・リカバリに不要になったことを示す。
CLEARING_CURRENT – クローズされたスレッドからカレント・ログが消去されることを示す
CLEARING – ALTER DATABASE CLEAR LOGFILE文の後、空のログとして再作成されていることを示す。ログの消去の後、状態がUNUSEDに変更される。

select

group# -- ロググループ番号

, thread# -- ログスレッド番号

, sequence# -- ログ順序番号

, bytes/1024/1024 M_Byte -- ログブロックサイズ

, members

, status

from v$log order by 1,2

;

select group# -- ロググループ番号 , thread# -- ログスレッド番号 , sequence# -- ログ順序番号 , bytes/1024/1024 M_Byte -- ログブロックサイズ , members , status from v$log order by 1,2 ;

select
  group#     -- ロググループ番号
, thread#    -- ログスレッド番号
, sequence#  -- ログ順序番号 
, bytes/1024/1024 M_Byte     -- ログブロックサイズ
, members   
, status 
from v$log order by 1,2
;

テーブルを素早くバックアップ

ダイレクトパス（/*+ APPNED */）を記載すればさらに早くなるのでは、、と思いがちですがcreateしているので、無条件にダイレクト・パスモードが使われます。NOLOGGINGとすることで、作成するテーブルはREDOログを作成しなようになります。

create table CHECKSQL_BK nologging parallel as select * from CHECKSQL

;

create table CHECKSQL_BK nologging parallel as select * from CHECKSQL ;

create table CHECKSQL_BK nologging parallel as select * from CHECKSQL
;

本当にNOLOGGINGになっているのか確認する。

SQL> set sqlformat ansiconsole
SQL> select t.OWNER,t.TABLE_NAME,t.LOGGING from dba_tables t where t.TABLE_NAME = 'CHECKSQL';

      OWNER    TABLE_NAME    LOGGING
___________ _____________ __________
SCOTT      CHECKSQL      NO

SQL>

アーカイブログ出力モードになっているか

アーカイブログ出力モードへの切り替え

長くなったので以下に切り出しました

パッチ適用の確認

$ORACLE_HOME/OPatch/opatch lsinventory

$ORACLE_HOME/OPatch/opatch lsinventory

データディクショナリに関するTips

データディクショナリ一覧を出力するSQL

データディクショナリは数が多くとても覚えられませんが以下SQLで一覧を確認する事ができます。

SELECT * FROM DICTIONARY

;

SELECT * FROM DICTIONARY ;

SELECT * FROM DICTIONARY 
;

DBリンク関するTips

DBリンク一覧を確認するSQLです。

select t.OWNER,t.DB_LINK,t.USERNAME,t.HOST,t.CREATED,t.HIDDEN from DBA_DB_LINKS t

;

select t.OWNER,t.DB_LINK,t.USERNAME,t.HOST,t.CREATED,t.HIDDEN from DBA_DB_LINKS t ;

select t.OWNER,t.DB_LINK,t.USERNAME,t.HOST,t.CREATED,t.HIDDEN from DBA_DB_LINKS t
;

マテビュ―に関するTips

テーブル情報に関するTips

各テーブルのPK一覧出力SQL

select

c.OWNER

,c.constraint_name

,c.table_name

,e.position

,e.column_name

,c.CONSTRAINT_TYPE

,c.INDEX_NAME

from

dba_constraints c,

dba_CONS_COLUMNS e

where 1=1

and e.OWNER = 'SCOTT'

and e.TABLE_NAME in ('EMP','ADDRESS')

and c.OWNER = e.OWNER

and c.constraint_name = e.constraint_name

and c.constraint_type = 'P'

order by c.OWNER,c.TABLE_NAME,c.constraint_name,e.column_name,e.position

;

select c.OWNER ,c.constraint_name ,c.table_name ,e.position ,e.column_name ,c.CONSTRAINT_TYPE ,c.INDEX_NAME from dba_constraints c, dba_CONS_COLUMNS e where 1=1 and e.OWNER = 'SCOTT' and e.TABLE_NAME in ('EMP','ADDRESS') and c.OWNER = e.OWNER and c.constraint_name = e.constraint_name and c.constraint_type = 'P' order by c.OWNER,c.TABLE_NAME,c.constraint_name,e.column_name,e.position ;

select
 c.OWNER
,c.constraint_name
,c.table_name
,e.position
,e.column_name
,c.CONSTRAINT_TYPE
,c.INDEX_NAME
 from
dba_constraints c,
dba_CONS_COLUMNS e
where 1=1
and e.OWNER = 'SCOTT'
and e.TABLE_NAME in ('EMP','ADDRESS')
and c.OWNER = e.OWNER
and c.constraint_name = e.constraint_name
and c.constraint_type = 'P'
order by c.OWNER,c.TABLE_NAME,c.constraint_name,e.column_name,e.position
;

PKを作成するSQL

alter table ex1 add constraint ex1_pk primary key (lno)

USING INDEX TABLESPACE TBS01

;

alter table ex1 add constraint ex1_pk primary key (lno) USING INDEX TABLESPACE TBS01 ;

alter table ex1 add constraint ex1_pk primary key (lno)
USING INDEX TABLESPACE TBS01
;

ユニークインデックスを作成するSQL

create unique index uni_idx_ex1 on ex1(c2)

;

create unique index uni_idx_ex1 on ex1(c2) ;

create unique index uni_idx_ex1 on ex1(c2)
;

カラムを追加するSQL

alter table ex1 add (c3 number)

;

alter table ex1 add (c3 number) ;

alter table ex1 add (c3 number)
;

カラムを削除するSQL

制約も含めて削除したい時は　cascade constraints　を最後に追加します。

alter table ex1 drop (c3)

;

alter table ex1 drop (c3) ;

alter table ex1 drop (c3)
;