オラクる。

oracle専門ブログにしてみようかな~っと

2016年05月

SELECTにWITH句を利用することで一時表を作成することが出来ます。
さらに、その一時表を利用した新たなSELECTを発行することが出来ます。
join句を利用して同一の結果を返すことが出来ますが、見やすさ的にも、パフォーマンス的にもWITH句を利用した方が優れているようです。

select 1 one_col1, 10 one_col2 from dual

  ONE_COL1   ONE_COL2
---------- ----------
         1         10

例えば上記のSELECT文をWITH句で一時表としてまとめてみます。

WITH
    one AS (
        select 1 one_col1, 10 one_col2 from dual
    )
    select one_col1 + 1 from one;

ONE_COL1+1
----------
         2

一時表にoneという名前を付けて、次のSELECT文で利用しています。

WITH
    one AS (
        select 1 one_col1, 10 one_col2 from dual
    ),
    ones_next AS (
        select one_col1 + 1 from one
    )
    select * from ones_next;

ONE_COL1+1
----------
         2


また、一時表を利用して新たな一時表を作ることも可能です。
oneという一時表を利用して、ones_nextという一時表を作成しています。

select 1 from dual;


         1
----------
         1

また、今度は上記の一時表を作成します。

WITH
    ones_next (col) AS (
        select 1 from dual
    )
    select * from ones_next;

       COL
----------
         1

ones_nextという一時表を作成しています。
また、列名にcolという名前を付けています。

WITH
    ones_next (col) AS (
        select 1 from dual
        union all
        select 1 + col from ones_next where col < 5
    )
    select * from ones_next;

       COL
----------
         1
         2
         3
         4
         5


さらにones_nextという一時表の中で同じones_nextを利用する・・・という再帰的にWITH句を作成することも可能になります。
11gからの機能になります。
少し複雑になっていまいますが・・・

Oracleユーザーを作成してみます。

SQL> CREATE USER user2 IDENTIFIED BY password DEFAULT TABLESPACE TBS_BIG;

ユーザーが作成されました。

「user2」のデフォルト表領域はTBS_BIGです。

SQL> grant CREATE SESSION to user2;

権限付与が成功しました。

SQL> grant CREATE TABLE to user2;

権限付与が成功しました。


インスタンスの接続権限と表を作成する権限を付与しました。

[oracle@db01 ~]$ sqlplus user2/password

SQL*Plus: Release 11.2.0.3.0 Production on 木 5月 19 00:44:04 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> create table TBL5 (col1 number, col2 varchar2(100));

表が作成されました。

SQL> select TABLE_NAME, TABLESPACE_NAME from USER_TABLES;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TBL5                           TBS_BIG

早速、user2でログインし表を作成しました。
この時、デフォルト表領域のTBS_BIGに表が作成されました。

SQL> insert into TBL5 values(1, 'tadashi');
insert into TBL5 values(1, 'tadashi')
            *
行1でエラーが発生しました。:
ORA-01950: 表領域'TBS_BIG'に対する権限がありません


しかし、データをinsertすると権限が無いというエラーが出力されました。
これはユーザーにquota制限が割当てられていないからです。

SQL> alter user user2 quota 400M on TBS_BIG;

ユーザーが変更されました。

SQL> select USERNAME, TABLESPACE_NAME, MAX_BYTES/1024/1024 from DBA_TS_QUOTAS where USERNAME='USER2';

USERNAME                       TABLESPACE_NAME                MAX_BYTES/1024/1024
------------------------------ ------------------------------ -------------------
USER2                          TBS_BIG                                        400

user2にquotaの400MBを割当てました。

SQL> insert into TBL5 values(1, 'tadashi');

1行が作成されました。


これでデータの挿入が出来るようになりました。

ある表領域に作成した表と索引を別の表領域に移動する方法です。

SQL> create table TBL4 (col1 number, col2 varchar2(100));

表が作成されました。

SQL> create index TBL4_INDEX ON TBL4(col1);

索引が作成されました。

テスト用に1つの表と、その表の索引を1つ作ります。

SQL> select TABLE_NAME, TABLESPACE_NAME from USER_TABLES;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TBL4                           TBS_BIG

SQL> select INDEX_NAME, TABLESPACE_NAME from USER_INDEXES;

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TBL4_INDEX                     TBS_BIG


ビューより、作成した表と索引はTBS_BIGという表領域に作成されていることがわかります。

SQL> insert into TBL4 values(1,'tadashi');

1行が作成されました。

SQL> insert into TBL4 values(2,'aiai');

1行が作成されました。

SQL> insert into TBL4 values(3,'ddd');

1行が作成されました。


作成した表にデータを挿入します。

SQL> select ROWID, COL1, COL2 from TBL4;

ROWID                    COL1 COL2
------------------ ---------- --------------------
AAATJXAAAAAAACHAAA          1 tadashi
AAATJXAAAAAAACHAAB          2 aiai
AAATJXAAAAAAACHAAC          3 ddd


作成した各行のROWIDを確認しています。
このROWIDはデータファイルのデータ番号を元にして作られ、索引はそのROWIDを元にデータを参照しています。

SQL> alter table TBL4 move tablespace USERS;

表が変更されました。

SQL> select TABLE_NAME, TABLESPACE_NAME from USER_TABLES;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TBL4                           USERS

表が属する表領域を移動しました。

SQL> select INDEX_NAME, TABLESPACE_NAME from USER_INDEXES;

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TBL4_INDEX                     TBS_BIG

表に対して作成した索引は移動されていません。

SQL> select ROWID, COL1, COL2 from TBL4;

ROWID                    COL1 COL2
------------------ ---------- --------------------
AAATJZAAEAAAAILAAA          1 tadashi
AAATJZAAEAAAAILAAB          2 aiai
AAATJZAAEAAAAILAAC          3 ddd


再度、ROWIDを確認すると先程と変わっていることがわかります。
表領域が変わることで、データファイルも変わってしまったからです。
また索引も参照するべきROWIDが無くなってしまったので、有効では無くなってしまいました。

SQL> ALTER INDEX TBL4_INDEX REBUILD TABLESPACE users;

索引が変更されました。

SQL> select INDEX_NAME, TABLESPACE_NAME from USER_INDEXES;

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TBL4_INDEX                     USERS


索引の属する表領域を変更しています。
変更すると同時に索引の再構築も行ないました。
これで索引は正しい表のROWIDを参照するようになりました。

Oracleの表領域にはスモールファイルとビッグファイルの2種類があります。

SQL> create tablespace TBS_SMALL datafile '/data/tbs_small01.dbf' size 100M,
  2                                       '/data/tbs_small02.dbf' size 100M,
  3                                       '/data/tbs_small03.dbf' size 100M;


表領域が作成されました。

例えばスモールファイル表領域の場合、複数のファイルをデータファイルとして表領域を作成します。

SQL> select tablespace_name, sum(bytes)/1024/1024 "size(MB)" from DBA_DATA_FILES
  2         where tablespace_name = 'TBS_SMALL'
  3         group by tablespace_name;

TABLESPACE_NAME                  size(MB)
------------------------------ ----------
TBS_SMALL                             300


DBA_DATA_FILESビューで表領域のサイズを確認しています。
100MB×3ファイルで300MBです。

alter tablespace TBS_SMALL add datafile '/data/tbs_small04.dbf' size 100M;

SQL> select tablespace_name, sum(bytes)/1024/1024 "size(MB)" from DBA_DATA_FILES
       where tablespace_name = 'TBS_SMALL'
  3         group by tablespace_name;

TABLESPACE_NAME                  size(MB)
------------------------------ ----------
TBS_SMALL                             400

alter database datafile '/data/tbs_small01.dbf' resize 125M;
alter database datafile '/data/tbs_small02.dbf' resize 125M;
alter database datafile '/data/tbs_small03.dbf' resize 125M;
alter database datafile '/data/tbs_small04.dbf' resize 125M;

TABLESPACE_NAME                  size(MB)
------------------------------ ----------
TBS_SMALL                             500


また、データファイルを追加したり、データファイルのサイズを拡張することで、表領域を拡張することが出来ます。

SQL> create bigfile tablespace TBS_BIG datafile '/data/tbs_big01.dbf' size 100M,
  2                                             '/data/tbs_big02.dbf' size 100M,
  3                                             '/data/tbs_big03.dbf' size 100M;
create bigfile tablespace TBS_BIG datafile '/data/tbs_big01.dbf' size 100M,
*
行1でエラーが発生しました。:
ORA-32774: BIGFILE表領域TBS_BIGに複数のファイルが指定されています


一方、ビッグファイル表領域ではデータファイルを複数指定することが出来ません。
その代わり、32TBというスモールファイル領域より大きなサイズの表領域を作成することが出来ます。

SQL> create bigfile tablespace TBS_BIG datafile '/data/tbs_big.dbf' size 300M;

表領域が作成されました。


300MBの表領域を作成しています。

SQL> alter tablespace TBS_BIG resize 500M;

表領域が変更されました。

サイズ変更もalter tablespaceで行なうことが出来ます。

前回までで表領域を断片化させたので、今回は断片化の解消方法を確認してみます。

SQL> alter tablespace BIG_TBS resize 350m ;
alter tablespace BIG_TBS resize 350m
*
行1でエラーが発生しました。:
ORA-03297:
ファイルには、リクエストしたRESIZE値を超える使用中のデータが含まれています。


例えば、ディスクに空き領域が無い状態で200MBのサイズの領域が必要になったとします。
その場合、既存の表領域を550MB→350MBに減らします。
しかし、領域が断片化しているため縮小できません。

set linesize 150 pages 5000
col SEGMENT_NAME for a24
select distinct T1.TABLESPACE_NAME, T1.FILE_ID,
       T1.SEGMENT_TYPE, T1.OWNER||'.'||T1.SEGMENT_NAME "SEGMENT_NAME", T1.SEGMENT_TYPE
  from DBA_EXTENTS T1
 where ((T1.BLOCK_ID + 1)
        * (select BLOCK_SIZE
             from DBA_TABLESPACES T2
            where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (&SIZE * 1024 * 1024)
   and TABLESPACE_NAME = 'BIG_TBS'
 order by 1, 2;

sizeに値を入力してください: 350
旧   7:             where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (&SIZE * 1024 * 1024)
新   7:             where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (350 * 1024 * 1024)

TABLESPACE_NAME                   FILE_ID SEGMENT_TYPE       SEGMENT_NAME             SEGMENT_TYPE
------------------------------ ---------- ------------------ ------------------------ ------------------
BIG_TBS                                 7 INDEX              TEST_USER1.PK_TAB1_COL1  INDEX
BIG_TBS                                 7 INDEX              TEST_USER1.PK_TAB3_COL1  INDEX
BIG_TBS                                 7 INDEX              TEST_USER1.PK_TAB4_COL1  INDEX
BIG_TBS                                 7 TABLE              TEST_USER1.TAB4          TABLE


350MB以降に存在しているセグメントを確認します。
主キー索引とテーブルが存在しているようです。
これを350MBより前に移動することで550MB→350MBの縮小ができるようになります。

alter table TAB4 move ;
alter index PK_TAB4_COL1 rebuild online ;
alter index PK_TAB1_COL1 rebuild online ;
alter index PK_TAB3_COL1 rebuild online ;

表はalter table ~ move、索引はalter index ~ rebuildで領域が最適化されます。

set linesize 150 pages 5000
col SEGMENT_NAME for a24
select distinct T1.TABLESPACE_NAME, T1.FILE_ID,
       T1.SEGMENT_TYPE, T1.OWNER||'.'||T1.SEGMENT_NAME "SEGMENT_NAME", T1.SEGMENT_TYPE
  from DBA_EXTENTS T1
 where ((T1.BLOCK_ID + 1)
        * (select BLOCK_SIZE
             from DBA_TABLESPACES T2
            where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (&SIZE * 1024 * 1024)
   and TABLESPACE_NAME = 'TBS17_FOR_TAB'
 order by 1, 2;

sizeに値を入力してください: 350
旧   7:             where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (&SIZE * 1024 * 1024)
新   7:             where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (350 * 1024 * 1024)

レコードが選択されませんでした。

実行後、再度確認すると350MB以降にセグメントが無くなりました。

SQL> alter tablespace BIG_TBS resize 350m ;

表領域が変更されました。


これで断片化が解消し、550MB→350MBの縮小が出来るようになりました。

このページのトップヘ