Oracleネタ帳 SQLについていろいろ

# SQLのあれこれ
ネタ帳的にリストアップしていきます。
後々整理して別記事に移動するかもしれません。

# SQL毎の一時表領域の使用量を確認する

``` sql
SELECT
    ss.sid
    ,ss.serial#
    ,ss.username
    ,tu.tablespace
    ,SUM(tu.blocks) * dts.block_size / 1024 / 1024 used_mb
    ,sq.sql_text
FROM
    v$tempseg_usage tu
    ,v$session ss
    ,dba_tablespaces dts
    ,v$sql sq
WHERE
    tu.session_addr = ss.saddr
AND ss.sql_id = sq.sql_id
AND tu.tablespace = dts.tablespace_name
GROUP BY
    ss.sid
    ,ss.serial#
    ,ss.username
    ,dts.block_size
    ,tu.tablespace
    ,sq.sql_text
ORDER BY
    ss.sid;
```

# インデックスや制約を確認する
インデックスを確認する。

``` sql
select index_name, table_name from user_indexes order by table_name, index_name;
```

制約を確認する。

``` sql
column constraint_name format a20
column table_name format a20
select constraint_name,constraint_type,table_name,status
from user_constraints
where table_name='&tabname';
```

# オブジェクトのステータスを確認
プロシージャやトリガーのステータスを確認する。

``` sql
set lin 500
set pages 9999
column OWNER format a15
column OBJECT_TYPE format a15
column OBJECT_NAME format a40
column LAST_DDL_TIME format a20
SELECT OWNER
      ,OBJECT_TYPE
      ,OBJECT_NAME
      ,STATUS
FROM   DBA_OBJECTS
WHERE  OWNER       = '&owner'
AND    OBJECT_TYPE = 'PROCEDURE', 'TRIGGER'
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME ASC;
```

# コントロールファイル一覧
コントロールファイルの一覧を表示する。

``` sql
set lines 120
set pages 100
col STATUS format a4
col NAME format a50
col IS_RECOVERY_DEST_FILE format a4
col BLOCK_SIZE format 999,999,990
col FILE_SIZE_BLKS format 999,999,990
select
  status,
  name,
  is_recovery_dest_file,
  block_size,
  file_size_blks
from
  v$controlfile
/
```

# スキーマと表領域の一覧を確認

``` sql
SELECT
    owner,
    tablespace_name,
    sum(bytes)/1024/1024
FROM
    dba_segments
GROUP BY
    owner,
    tablespace_name;
```

# スキーマの作成
GRANT文も必要かな...

``` sql
CREATE USER my_name 
   IDENTIFIED BY "my_password" 
   [DEFAULT TABLESPACE my_tablespace] 
   [TEMPORARY TABLESPACE my_temp_tablespace] 
```

# データベース・リンクの作成
データベース・リンクの作成

``` sql
CREATE DATABASE LINK <データベースリンク名>
CONNECT TO <接続先のユーザー名>
IDENTIFIED BY <接続先のパスワード>
USING '<データベースサーバーのIPアドレス/接続先のデータベースサービス名>';
```

## データベースリンクの確認

``` sql
SELECT * FROM ALL_DB_LINKS
```

## データベースリンクの削除

``` sql
DROP DATABASE LINK <name_of_db_link>
```

## データの抽出

``` sql
SELECT * FROM tabname@<name_of_db_link名>
```

# データベースの文字コード確認

``` sql
SELECT
   PARAMETER,
   VALUE
FROM
   NLS_DATABASE_PARAMETERS
WHERE
   PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
```

# テーブルのサイズを確認

``` sql
SELECT 
    a.table_name    --テーブル名称
  , a.num_rows      --テーブルのレコード数
  , b.MB            -- テーブルの利用容量
FROM user_tables a  -- 全てのテーブル
  , (SELECT 
         segment_name
       , sum(bytes)/1024/1024 MB
     FROM user_segments
     GROUP BY segment_name
    ) b             --テーブル名称＆容量の集合
WHERE a.table_name = b.segment_name
ORDER BY a.num_rows DESC, b.MB DESC;
```

# プロファイルの確認

``` sql
col PROFILE format a15
col RESOURCE_NAME format a30
col RESOURCE_TYPE format a15
col LIMIT format a15
select
  profile,
  resource_name,
  resource_type,
  limit
from
  dba_profiles
order by
  resource_type, resource_name
/
```

# 現在実行中のSQLを表示

``` sql
select a.username,
       a.sid,
       a.serial#,
       a.machine,
       a.wait_time,
       b.sql_text
from v$session a,
     v$sqltext b
where   a.sql_address = b.address
  and   a.sql_hash_value = b.hash_value
  and   b.piece = 0
  and   a.username is not null
  and   a.status = 'ACTIVE'
  and   a.sid != sys_context('userenv', 'SID')
order by 
    a.username,
    a.sid,
    b.piece;
```

# 合計実行時間の長いSQLの確認
※elapsed_timeはマイクロ秒

``` sql
SET LINES 140
COL sql_text            FORM A140
COL buffer_per_run      FORM 999999999999
COL disk_per_run        FORM 999999999999
COL cpu_time            FORM 999999999999
COL elapsed_time        FORM 999999999999
SELECT * FROM
(SELECT
        sql_text,address,hash_value,parse_calls,executions,
        buffer_gets,disk_reads,
        buffer_gets/executions buffer_per_run,
        disk_reads/executions disk_per_run,cpu_time,
        elapsed_time
FROM v$sql
WHERE executions>0
ORDER BY elapsed_time desc)   /* ←この条件を変更する */
WHERE rownum <= 10;           /* ←表示件数はこの値を変更する */
```

# 削除してしまったテーブルを元に戻す

``` sql
FLASHBACK TABLE <tname> TO BEFORE DROP ;
```

# 接続しているサーバーのホスト名やIPアドレスなどを取得する(SYS_CONTEXT)

``` sql
SELECT SYS_CONTEXT('USERENV','SERVER_HOST') from dual;
```

|   パラメーター   |                                          説明                                              |
|:-----------------|:-------------------------------------------------------------------------------------------|
|CLIENT_INFO       |DBMS_APPLICATION_INFOパッケージを使用するアプリケーションが格納できるユーザーセッション情報 |
|CURRENT_SCHEMA    |カレント・スキーマで使用されているデフォルトのスキーマ名                                    |
|CURRENT_SCHEMAID  |現行のセッションで使用されているデフォルトのスキーマID                                      |
|DB_DOMAIN         |データベースのドメイン                                                                      |
|DB_NAME           |データベース名（DB_NAME初期化パラメーター）                                                 |
|DB_UNIQUE_NAME    |データベース名（DB_UNIQUE_NAME初期化パラメーター）                                          |
|ENTRYID           |現行セッションの監査エントリ番号                                                            |
|HOST              |接続中のクライアントのホスト・コンピューター名                                              |
|INSTANCE          |現行のインスタンスのインスタンス識別番号                                                    |
|INSTANCE_NAME     |インスタンス名                                                                              |
|IP_ADDRESS        |接続中のクライアントのマシンのIPアドレス                                                    |
|ISDBA             |DBA権限を保有している「true」、保有してない「false」                                        |
|LANGUAGE          |現在のセッションで使用している言語・地域                                                    |
|LANG              |文字列「LANGUAGE」の略称                                                                    |
|OS_USER           |クライアント・プロセスのＯＳ・ユーザー名                                                    |
|SERVER_HOST       |インスタンスを実行しているマシンのホスト名                                                  |
|SESSIONID         |セッションＩＤ                                                                              |
|SESSION_USER      |現行のユーザーが認証されているデータベース・ユーザー名                                      |
|SESSION_USERID    |現行のユーザーが認証されているデータベース・ユーザーID                                      |
|TERMINAL          |端末に対するオペレーティング・システム識別子                                                |

# データベースロールの確認

``` sql
SELECT DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE;
```

PRIMARY/PHYSICAL STANDBYなどが表示される。
V$DATABASEの詳細は[こちら](https://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_1097.htm)を参照。