Oracleネタ帳 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;

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

インデックスを確認する。

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

制約を確認する。

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';

オブジェクトのステータスを確認

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

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;

コントロールファイル一覧

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

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
/

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

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

スキーマの作成

GRANT文も必要かな...

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

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

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

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

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

SELECT * FROM ALL_DB_LINKS

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

DROP DATABASE LINK <name_of_db_link>

データの抽出

SELECT * FROM tabname@<name_of_db_link名>

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

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

テーブルのサイズを確認

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;

プロファイルの確認

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を表示

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はマイクロ秒

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;           /* ←表示件数はこの値を変更する */

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

FLASHBACK TABLE <tname> TO BEFORE DROP ;

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

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	端末に対するオペレーティング・システム識別子

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

SELECT DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE;

PRIMARY/PHYSICAL STANDBYなどが表示される。
V$DATABASEの詳細はこちらを参照。