Oracle関連備忘録（運用編）

## パスワードの有効期限を確認する
```sql
SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME';
```

## 表領域の情報を確認する
```sql
SELECT
  DF.TABLESPACE_NAME AS "表領域名",
  DF.FILE_NAME AS "DBFファイル",
  DF.STATUS AS "ファイルの状態",
  DF.ONLINE_STATUS AS "オンライン状態",
  DF.AUTOEXTENSIBLE AS "自動拡張設定",
  TO_CHAR((DF.BYTES / 1024 / 1024),'999999999') AS "ファイルサイズ(MB)",
  DS.FREE_BYTES AS "残り空きサイズ(MB)",
  ROUND((1-(DS.FREE_BYTES / TO_CHAR((DF.BYTES / 1024 / 1024),'999999999')))*100,1) AS "使用率(%)"
FROM SYS.DBA_DATA_FILES DF
LEFT OUTER JOIN
  (SELECT DS.FILE_ID, TO_CHAR((SUM(DS.BYTES) / 1024 / 1024),'999999999') AS FREE_BYTES 
   FROM DBA_FREE_SPACE DS GROUP BY DS.FILE_ID) DS
  ON DF.FILE_ID = DS.FILE_ID
ORDER BY DF.TABLESPACE_NAME, DF.FILE_ID
```

## 表領域(データファイル)のサイズを変更する
```sql
ALTER DATABASE DATAFILE '/DATA/XXXX.DBF' RESIZE 9999M;
```

## データファイルを追加する
```sql
ALTER TABLESPACE INDX ADD DATAFILE '/DATA/XXXX.DBF' SIZE 30G;
```

## データベースバッファキャッシュを確認する
```sql
select
  o.object_name,
  blsiz,
  count(*) blocks
from
  x$bh b,
  dba_objects o
where
  b.obj = o.data_object_id and b.ts# > 0
group by o.object_name, blsiz
order by blocks desc;
```

## 表領域の使用状況を確認する
```sql
select
  tablespace_name,
  to_char(nvl(total_bytes / 1024 / 1024,0),'999,999,999') as "size(MB)",
  to_char(nvl((total_bytes - free_total_bytes) / 1024 / 1024,0),'999,999,999') as "used(MB)",
  to_char(nvl(free_total_bytes/1024 / 1024,0),'999,999,999') as "free(MB)",
  round(nvl((total_bytes - free_total_bytes) / total_bytes * 100,100),2) as "rate(%)"
from
  ( select
  tablespace_name,
  sum(bytes) total_bytes
  from
  dba_data_files
  group by
  tablespace_name
  ),
  ( select
  tablespace_name free_tablespace_name,
  sum(bytes) free_total_bytes
  from
  dba_free_space
  group by tablespace_name
  )
where
  tablespace_name = free_tablespace_name(+);
```

## DBオブジェクトを確認する
例としてテーブルの場合
```sql
select object_name, created, timestamp from user_objects
where object_type='table'order by object_name;
```

## SQL実行履歴を確認する
```sql
select last_active_time,parsing_schema_name,sql_text from v$sqlarea
where parsing_schema_name <> 'SYS'
  and parsing_schema_name <> 'SYSMAN'
  and parsing_schema_name <> 'DBSNMP'
  and parsing_schema_name <> 'MDSYS'
  and parsing_schema_name <> 'EXFSYS'
order by last_active_time asc;
```

## 統計情報を更新する(スキーマ単位)
OPTIONSは"GATHER=全オブジェクトが対象"、"GATHER AUTO=Oracleが必要とするオブジェクトが対象"となる。他にもあるが良くつかうのはこの２つ。

```sql
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME => 'スキーマ名', OPTIONS => 'GATHER AUTO');
```

## セッション・プロセスを確認する
```sql
select * from v$resource_limit
where resource_name = 'processes' or resource_name = 'sessions';
```
```sql
select * from V$SESSION where username='XXXX' and machine='XXXX';
```

## コネクションを確認する
```sql
SELECT
     SID,USERNAME,STATUS,OSUSER,MACHINE,SQL_TEXT
 FROM
     V$SQLAREA,V$SESSION
 WHERE
     V$SQLAREA.ADDRESS(+)=V$SESSION.PREV_SQL_ADDR
 AND USERNAME='XXXX';
```

## dmpファイルをエクスポートする
```sql
expdp ユーザーID/パスワード@DB名 schemas=スキーマ名 directory=Oracleディレクトリ名 dumpfile=XXXX.dmp logfile=xpdp.log exclude=statistics reuse_dumpfiles=YES
```

## Oracleのバージョンを確認する
```sql
SELECT * FROM V$VERSION;
```

## Oracleのパラメータ
以下２つの方法で扱うことになる
・テキスト形式のpfile（初期化パラメータ・ファイル）
・バイナリ形式のSPFILE（サーバー・パラメータ・ファイル）

## Oracle接続方法
１．ローカル・ネーミング
　TNS(tnsnames.ora)に書かれた接続情報を用いて接続する方法
２．簡易接続ネーミング
　TNS(tnsnames.ora)を使わずに接続することが出来る方法
　例： sqlplus username/password@ホスト名:ポート番号/サービス名

## 重要ファイル① 制御ファイルについて
DBに必要不可欠で、少なくとも2ファイル以上存在している必要がある
デフォルトでは3ファイル作成される、中身はバイナリファイル（拡張子ctl）
複数ファイルのうち、1つが仮に壊れたとしても、正常な1つをコピーして復元すればよい

制御ファイルを確認するコマンドは以下。
```sql
show parameters control_files;
```

## 重要ファイル② データファイルについて
DBに必要不可欠
表領域と紐づいて存在する
中身はバイナリファイル（拡張子dbf）

## 重要ファイル➂ オンラインREDOログファイルについて
データの変更内容のコピーが保管される
v$logfileビューで確認可能

## Sql plusをもっと見やすくする
```sql
--1行の長さを設定する
set linesize 1000;
```

## ネットワークファイル
１．tnsnames.ora
　クライアント側に置かれるファイル
　ただし、サーバー側にもDB作成とともに作成される
　役割：接続情報を定義しておくもの
　重要なのは、listener.oraとの関連は全く無いということ
　(ADDRESS = ・・・の一文で指定しているPORTがリスナーのPORTであり、
　リスナーとDBがマッピングされるのは、この一文の指定でのみである　
　例：
　oradb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB01)
    )
  )
  
  「SID」と「SERVICE_NAME」は基本的には同じになっているはず。
  接続する際に、SERVICE_NAMEにDB01を指定しても、SIDにDB01を指定しても繋がる。

２．sqlnet.ora
　サーバー側に置かれるファイル
　役割：どんな名前解決ルールでアクセスを受け付けるか
　・SQLNET.AUTHENTICATION_SERVICES（認証方式）
　　・none	⇒認証方式を使用しない、ユーザーとパスワードを指定して認証する方法だけが使える
　　・all		⇒OS認証等、全ての認証方式を使用する
　　・nts	⇒WindowsOS認証方式を使用する
　　他もあるが使わないので割愛
　・NAMES.DIRECTORY_PATH（ネーミングメソッドの優先順）
　　・tnsnames	⇒クライアント上のtnsnames.oraファイル
　　・ldap		⇒LDAP
　　・ezconnectまたはhostname	⇒簡易接続
　　・nls		⇒外部ネーミングメソッド
　・NAMES.DEFAULT_DOMAIN（接続識別子にドメインが省略された場合のデフォルトドメイン）

３．listener.ora
　サーバー側に置かれるファイル
　役割：リスナーの設定ファイル
　以下のサイトが詳しい
　https://oracle-chokotto.com/ora_nwfile.html
　例：
　LISTENER =　　←ここはリスナー名である(何を指定してもよい)
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ホスト名)(PORT = 1521))
    )
  )
  上記があればクライアント用リスナーが定義されたことになり、通信できる
  リスナーの数だけ上記定義を作るイメージ
  
  例：以下は、ファイル内に１つだけ定義がある。そのままでも動くような。
  　　明示的に定義するなら、SID_DESCのブロックをDBごとに増やす
　SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\ora\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\ora\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
  )

## リスナーへの登録について
・静的な登録とは
　Oracleのlistener.oraファイルに定義を追加する方法。(ファイルを手作業で直接編集する、またはOracleが提供する各種GUIツール経由で編集する)

・動的な登録とは
　各DBごとのパラメータ(local_listner)に、リスナー名を設定する方法。(alter systemコマンドで設定する)

登録がされていないと、「ORA-12514」「ORA-12505」どちらかのエラーが発生し、DBに接続することが出来ない
リスナーのポートをデフォルトの1521で利用するとさまざまな設定を簡略化され、 バージョンによってはリスナーの設定作業自体が不要になる

## 用語
・正規化　データを複数の表に分けることで重複を避けること。
・DML　データ操作言語(SELECTとか)
・DDL　データ定義言語(CREATEとか)
・DCL　データ制御言語(COMMITとか)
・パーティショニング　１つの表にあるデータを、内部的に表を分割した状態で持つことで、効率的にデータを読める。いくつか種類がある。
　・レンジパーティション　期間
　・リストパーティション　項目
　・ハッシュパーティション　分散
・リダクション　データにマスクをかけることができる機能。
　
## その他：参考サイト
■Oracleの識別子周り
http://www.shift-the-oracle.com/config/oracle_sid-db_name-global_name.html