Help us understand the problem. What is going on with this article?

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 クライアント・プロセスのOS・ユーザー名
SERVER_HOST インスタンスを実行しているマシンのホスト名
SESSIONID セッションID
SESSION_USER 現行のユーザーが認証されているデータベース・ユーザー名
SESSION_USERID 現行のユーザーが認証されているデータベース・ユーザーID
TERMINAL 端末に対するオペレーティング・システム識別子

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

SELECT DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE;

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

mako24
主にインフラエンジニア。 Oracleを中心にデータベースのお守りもします。 サーバサイドのWebアプリケーションエンジニアでもあった。 最近はご無沙汰してます。 フットボールと動物を愛でるおじさん。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away