kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

MySQLのSHOW構文をORACLE(SQL*Plus)でも実行したい

MySQL ORACLE SE

JPOUG Advent Calendar 2015の3日目のエントリーです。
昨日はShinnosuke Akitaさんの「Oracle DatabaseのASMディスクグループの空き容量を調べる」でした。

普段ORACLEとMySQLを使用してるとよくSQL*PlusでもSHOW構文を使ってしまって、こうなっちゃうのです。

SQL>show create table test_table
SP2-0158: 不明なSHOWオプションです。"create"
SP2-0158: 不明なSHOWオプションです。"table"
SP2-0158: 不明なSHOWオプションです。"test_table"

なのでSQL*PlusでもSHOW構文っぽいのを作成してます。ただスクリプト実行してるだけですが。

インストール・設定方法

ここから取得 github.com

oracle$git clone https://github.com/kenken0807/oracle-showtools.git

oracle$ vim .bash_profile
export SQLPATH=~/oracle-showtools

それから対象のユーザにログインしてから実行します。

oracle$sqlplus orauser/orauser@orcl

show_tables

  • テーブル一覧の取得
SQL> @show_tables;

TABLE_NAME
------------------------------
AAA
MASTER_TABLE
TEST_TABLE
USERS

show_tables_like

  • テーブル一覧からlike句で取得
SQL> @show_tables_like '%TEST%';

TABLE_NAME
------------------------------
TEST_TABLE

show_table_status

  • テーブル情報確認 
SQL> @show_table_status TEST_TABLE

TABLE_NAME  STATUS       NUM_ROWS     BLOCKS         MB  OBJECT_ID LAST_DDL_TIME       CREATE_DATE          LAST_ANALYZED
----------- ---------- ---------- ---------- ---------- ---------- ------------------- -------------------- -------------------
TEST_TABLE  VALID          100000        622          5     196813 2015/11/13 16:38:30 2015-11-13:16:38:14  2015/11/13 16:57:53


TABLE_NAME  COLUMN_NAME    SEQ_ID DATA_TYPE        LENGTH PK NULL  NUM_NULLS NUM_DISTINCT HISTOGRAM        LAST_ANALYZED
----------- -------------- ------ ------------ ---------- -- ---- ---------- ------------ ---------------- -------------------
TEST_TABLE  ID                  1 NUMBER               22 P  N             0       100000 HEIGHT BALANCED  2015/11/13 16:57:53
TEST_TABLE  NAME                2 VARCHAR2             50    Y             0        98992 HEIGHT BALANCED  2015/11/13 16:57:53
TEST_TABLE  STATUS              3 NUMBER               22    Y             0            5 FREQUENCY        2015/11/13 16:57:53
TEST_TABLE  CREATE_DATE         4 DATE                  7    Y             0       100000 HEIGHT BALANCED  2015/11/13 16:57:53

show_table_hist

SQL> @show_table_hist TEST_TABLE STATUS

TABLE_NAME   COLUMN_NAME  COLUMN_VALUE ENDPOINT_NUMBER
------------ ------------ ------------ ---------------
TEST_TABLE   STATUS                  0            1111
TEST_TABLE   STATUS                  1            2281
TEST_TABLE   STATUS                  2            3385
TEST_TABLE   STATUS                  3            4491
TEST_TABLE   STATUS                  4            5649

show_index_from

  • 指定したテーブルのインデックス一覧取得
SQL> @show_index_from TEST_TABLE

INDEX_NAME                     IND_COLUMNS
------------------------------ --------------
TEST_TABLE_IDX1                CREATE_DATE
TEST_TABLE_IDX2                STATUS,NAME
TEST_TABLE_IDX3                SYS_NC00005$,NAME
TEST_TABLE_PK                  ID

show_index_status

  • 指定したインデックス情報取得
  • ファンクションインデックスがある場合はここで確認できる
SQL> @show_index_status TEST_TABLE_IDX3

INDEX_NAME      INDEX_TYPE              NUM_ROWS UNIQUENESS CLUSTERING_FACTOR  MB  OBJECT_ID LAST_DDL_TIME       CREATE_DATE          LAST_ANALYZED
--------------- --------------------- ---------- ---------- ----------------- --- ---------- ------------------- -------------------- -------------------
TEST_TABLE_IDX3 FUNCTION-BASED NORMAL     100000 NONUNIQUE              20836   4     197020 2015/11/20 16:54:21 2015-11-20:16:54:21  2015/11/20 16:54:21


COLUMN_NAME   COLUMN_POSITION COLUMN_LENGTH CARDINALITY NULL  NUM_NULLS FUNCTION_IDX    
------------- --------------- ------------- ----------- ---- ---------- ----------------
SYS_NC00005$                1            22                             MOD("STATUS",3)
NAME                        2            50       98992 Y             0

show_create

  • インデックスやテーブルのcreate文を確認

    テーブル

SQL> @show_create table TEST_TABLE

SHOW_CREATE
--------------------------------------------------------------------------------

  CREATE TABLE "ORAUSER"."TEST_TABLE"
   (    "ID" NUMBER(*,0),
        "NAME" VARCHAR2(50),
        "STATUS" NUMBER(*,0),
        "CREATE_DATE" DATE,
         CONSTRAINT "TEST_TABLE_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

インデックス

SQL> @show_create index TEST_TABLE_IDX2

SHOW_CREATE
--------------------------------------------------------------------------------------

  CREATE INDEX "ORAUSER"."TEST_TABLE_IDX2" ON "ORAUSER"."TEST_TABLE" ("STATUS", "NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

show_help

  • 構文忘れた場合
SQL> @show_help

COMMAND                   OPT                                           DESCRIBE                                                 
------------------------- --------------------------------------------- ---------------------------------------------------------
@show_tables              none                                          List the tables in the schema
@show_tables_like         [table name] or wildcard %                    List the tables in the schema by the LIKE clause
@show_create              [index | table] [index name | table name]     Shows the CREATE TABLE or INDEX statement
@show_index_from          [table name]                                  List index columns of the table
@show_index_status        [index name]                                  Shows index information
@show_table_status        [table name]                                  Shows table information
@show_table_hist          [table name] [column name]                    Shows histogram information that the column has
@show_insert_table_cols   [table name]                                  Shows all columns in a comma-separated that the table has

クエリのチューニングするときに
SQL*PLUSの場合はインデックス確認するときとかいちいちSQL文記述しなくちゃいけないので面倒ですよね。
そういうときにこれでさくっと調べることができます。

明日はdiscus_hamburgさんですー！