該当する結果がありません

一致する検索結果がありませんでした。

お探しのものを見つけるために、以下の項目を試してみてください。

  • キーワード検索のスペルを確認してください。
  • 入力したキーワードの同義語を使用してください。たとえば、「ソフトウェア」の代わりに「アプリケーション」を試してみてください。
  • 下記に示すよく使用される検索語句のいずれかを試してみてください。
  • 新しい検索を開始してください。
急上昇中の質問

しばちょう先生の試して納得!DBAへの道

みなさん、こんにちは。"しばちょう"こと柴田長(しばた つかさ)です。さて、第2回目の今回は「表と表領域の関係」について演習を実施してみましょう。

しばちょう先生による技術解説セミナー。

動画、資料を公開中です。

最新のセミナーをチェック

しばちょう先生の試して納得!DBAへの道  第2回 表と表領域の関係

みなさん、こんにちは。"しばちょう"こと柴田長(しばた つかさ)です。さて、第2回目の今回は「表と表領域の関係」について演習を実施してみましょう。データベースは空っぽだと何の役にも立ちませんが、何千、何億、何十億件という沢山のデータを保持することで、ビジネスに対する役割が大きくなっていきます。今回の演習も基本中の基本ではありますが、それらの貴重なデータ(レコード)を格納しておく箱が「表」ですから、DBAとしても絶対的に重要かつ必要なオペレーションになってきます。Oracle Database 11gの新機能によって、従来から少し動作が変化、進化している部分もあるので、是非チャレンジしてみてください。 では早速、演習をはじめましょう。前回も使用したOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。 以下に各演習の回答例と補足をまとめましたので、ご自身の回答と比較してください。 ■演習1.「TRY」という名前のユーザーを作成して下さい。パスワードは任意に設定して下さい。この段階では、ユーザーのデフォルト表領域や権限の設定は行わないでください。

  
sqlplus / as sysdba 
	SQL> create user TRY identified by TRY;
 
 

 はい、簡単ですね。簡単過ぎて申し訳ないので、ここでマニュアル「SQL言語リファレンス」をあまり使用したことが無い方の為に、少し読み方のコツをお伝えしておきます。

※クリックで拡大します

 例えば、マニュアル「SQL言語リファレンス」CREATE USER構文では、多くの分岐と単語が並んでいるように見えますが、今回の演習のSQLを作成する為には、上図のように赤い線に沿って読んで頂ければ良いですね。あとは、角が「尖っている」四角内の単語はそのまま、角が「丸まっている」四角の部分ではDBAが適切な文字列に変更すれば良いだけです。 ■演習2.ユーザー「TRY」に対し、データベース・インスタンスへ接続可能となる権限と表を作成可能な権限を与えてください。

  
sqlplus / as sysdba 
	SQL> grant CREATE SESSION to TRY;
	SQL> grant CREATE TABLE to TRY;
 
 

 こちらも大丈夫だと思います。create user文で作成した直後のユーザーでは、データベース・インスタンスへ接続できません。これは試して頂くとORA-01045エラーが発生します。「CREATE SESSION」システム権限を付与することで接続可能となります。もちろん、「grant CONNECT to TRY;」でも正解です。「CONNECT」は事前に定義されているロールですね。ロールは複数の権限をまとめているものと覚えてください。詳細はマニュアル「セキュリティ・ガイド」をご参照ください。ちなみに、ロールに含まれる権限は、バージョンによって変更されるので注意する必要があります。例えば、この「CONNECT」ロールに含まれる権限は、以前は合計9つ程度でしたが、11g Release 2では「CREATE SESSION」権限のみに変更されています。 また、回答例は、権限毎に2回のgrant文を実行していますが、「grant CREATE SESSION, CREATE TABLE to TRY;」のように1行で記述することも可能です。 ■演習3.TRYユーザーでデータベース・インスタンスへ接続し、「TBL1」という名前の表を作成して下さい。ただし、第一カラムは名前が「col1」でデータ型が「number」、第二カラムは名前が「col2」でデータ型が「varchar2(100)」の2つのカラムから構成される表とします。

  
sqlplus try/try
SQL*Plus: Release 11.2.0.2.0 Production on 水 12月 14 18:13:32 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
 
 

 いざ、新規ユーザー「TRY」で接続を試みようとした際、上記のようにORA-01017エラーが発生してしまった方はいらっしゃいませんか?そうなのです。これは、特にOracle 9i DatabaseやOracle Database 10gを良くオペレーションをされていた方が陥り易いミスになります。実は、Oracle Database 11gより導入された「Case Sensitive Passwords」機能がデフォルトで有効となっており、パスワードの大文字と小文字を区別するようになりました。 つまり、今回の演習の例の場合、「create user」文を実行してTRYユーザーを作成する際に指定したパスワードは、大文字の「TRY」だったので、小文字の「try」ではパスワードが一致しないことになります。もちろん、従来通りパスワードで大/小文字の区別を無効にする方法(初期化パラメータ「sec_case_sensitive_logon=FALSE」)はありますが、データベースのセキュリティ・レベルやデータベースのアップグレード(バージョンアップ)に伴うアプリケーション側への影響等を十分考慮して設定値を決定して頂きたいところです。 さて、演習に戻って、TRYユーザーで以下の通りの表を作成してみましょう。

  
sqlplus try/TRY  
	SQL> create table TBL1 (col1 number, col2 varchar2(100));
 
 

■演習4.どの表領域にTBL1表が作成されているかについて、TRYユーザーでデータ・ディクショナリ・ビュー「USER_TABLES」を確認して下さい。

  
SQL> select TABLE_NAME, TABLESPACE_NAME from USER_TABLES;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TBL1                           USERS
 
 

 データベース・インスタンスへ接続(ログオン)しているユーザー自身が所有する表の情報を参照する為のデータ・ディクショナリ・ビューが「USER_TABLES」です。この結果から、表「TBL1」は表領域「USERS」上に作成されたことが解ります。この理由については、次の演習で解説します。

■演習5.データ・ディクショナリ・ビュー「DBA_USERS」でTRYユーザーのデフォルト表領域を確認後、表領域「TBS_BIG」に変更して下さい。

  
SQL> connect / as sysdba
select USERNAME, DEFAULT_TABLESPACE from DBA_USERS where USERNAME = 'TRY';
USERNAME    DEFAULT_TABLESPACE
----------- -----------------------
TRY         USERS

alter user TRY default tablespace TBS_BIG;
select USERNAME, DEFAULT_TABLESPACE from DBA_USERS where USERNAME = 'TRY';
USERNAME    DEFAULT_TABLESPACE
----------- -----------------------
TRY         TBS_BIG
 
 

 ユーザー作成時にデフォルト表領域を指定しなかったことと、その際に自動的に設定される表領域が今回のデータベースにおいては「USERS」に設定されていたこと(第一回の記事にも登場したデータ・ディクショナリ・ビュー「DATABASE_PROPERTIES」で確認可能)により、TRYユーザーのデフォルト表領域が「USERS」に自動設定されたのです。これらの理由から、表「TBL1」は表領域「UESRS」上に作成されたことが理解できると思います。 ちなみに、各データ・ディクショナリ・ビューで用意されているカラム名を忘れてしまった場合は、「desc」コマンドを使用すると便利ですし、そもそも正確なビュー名を忘れてしまっても、「DI CTIONARYもしくは、DICT」や「DBA_VIEWS」でLike検索を使用すれば良いのですね。

  
SQL> desc USER_TABLES
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 .........

SQL> connect / as sysdba
SQL> select TABLE_NAME from DICTIONARY where TABLE_NAME like 'DATABASE%'
UNION select VIEW_NAME from DBA_VIEWS where VIEW_NAME like 'DATABASE%'; 
 TABLE_NAME
------------------------------
DATABASE_PROPERTIES
.........
 
 

■演習6.TRYユーザーでTBL1表と同じ定義の表「TBL2」を作成し、それらの表が格納されている表領域を確認して下さい。

  
sqlplus /nolog
SQL> connect TRY/TRY
SQL> create table TBL2 (col1 number, col2 varchar2(100));
SQL> select TABLE_NAME, TABLESPACE_NAME from USER_TABLES;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TBL2                           TBS_BIG
TBL1                           USERS
 
 

 これまでの演習の繰り返しになりますので、特に難しいポイントは無いかと思います。 ここでおまけとして、同じ定義の表を複製する際に使用できる2つのSQLをお伝えしておきます。 1つ目はご存知の方も多いかと思いますが、「CREATE TABLE AS SELECT」文になります。今回の演習であれば、具体的には次のような構文になります。

  
SQL> create table TBL2 as select * from TBL1 where 1=2;
 
 

 ちなみに、「*」ではなくいくつかの列名を明記することで、それらの列を抜粋した表も作成可能ですし、WHERE句の書き方次第で、新規表へのコピーする行を選択することも可能です。「where 1=2」は、1行もコピーしたくない場合のテクニックです。ただし、新規表へコピーされない制約が存在したりする点に注意してください。 次に、2つ目の表の複製方法ですが、「DBMS_METADATA」パッケージの「GET_DDL」プロシージャになります。既存の作成済みの表や索引からそれらを再作成する為のDDL文を出力してくれるので、手元にDDL文が保存されていない場合等は非常に役立ちますので、覚えておいて損はありません。皆さんの業務用PCに次のサンプルをコピーして保存しておくと良いでしょう。

  
SQL> 
set echo off
set feedback off
set heading off
set trimspool on
set timing off
set time off
set pages 0
set long 2000000000
set linesize 50000

spool スプール・ファイル名

-- Storage句なしのDDLを取得する場合に実行
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

-- テーブルDDL取得例
select DBMS_METADATA.GET_DDL('TABLE','TBL1,'TRY')||'/' from dual;

-- 索引DDL取得例
select DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,'TRY')||'/' from user_indexes where table_name='TBL1;

spool off
 
 

■演習7.TBL1表をTBS_BIG表領域へ移動して下さい。

  
SQL> alter table TBL1 move tablespace TBS_BIG;

SQL> select TABLE_NAME, TABLESPACE_NAME from USER_TABLES;

TABLE_NAME   TABLESPACE_NAME
------------ ------------------------------
TBL1         TBS_BIG
TBL2         TBS_BIG
 
 

 はい、この演習問題は簡単ですね。今回の演習ではこのSQLのみで正解ですが、索引が作成されている表の表領域を移動した場合は、その索引が使用不可能な状態になってしまうので索引の再構成(Rebuild)が別途必要となります。 データベースでは内部処理で全レコードを識別する必要がある為、全てのレコードにはROWIDが割り当てられています。このROWIDは、そのレコードが格納されているデータ・ファイル番号やブロックアドレスから生成されている為、「表領域を移動する」=「データ・ファイル番号が変化する」=「ROWIDが変化する」ということになります。さらに、索引はこのROWIDを使用して構成されている為、表領域を移動すると索引が使用不可能な状態になるのです。よって、「MOVE」句を含むDDL文を実行する場合は最新の注意を払ってください。 今回の演習を最後まで実施した後に試して頂きたいのですが、上記で説明させて頂いた動作を次のようにSQL文で確認することができます。表領域を移動する前後で、ROWIDが変化していることを確認できます。

  
sqlplus / as sysdba
SQL> alter user TRY quota 10m on USERS;

SQL> connect TRY/TRY
SQL> select TABLE_NAME, TABLESPACE_NAME from USER_TABLES where TABLE_NAME ='TBL1';

TABLE_NAME   TABLESPACE_NAME
------------ ------------------------------
TBL1         TBS_BIG

SQL> select ROWID, COL1, COL2 from TBL1;

ROWID                    COL1 COL2
------------------ ---------- ------------------------
AAAEwFAAAAAAACDAAA          1 shibacho

SQL> alter table TBL1 move tablespace USERS;
SQL> select TABLE_NAME, TABLESPACE_NAME from USER_TABLES where TABLE_NAME ='TBL1';

TABLE_NAME   TABLESPACE_NAME
------------ ------------------------------
TBL1         USERS

SQL> select ROWID, COL1, COL2 from TBL1;

ROWID                    COL1 COL2
------------------ ---------- ------------------------
AAAEwGAAEAAAACDAAA          1 shibacho
 
 

■演習8.TBL1表に、1件INSERTして下さい。ORAエラーが出力された場合には、マニュアル「Oracle Databaseエラー・メッセージ」で原因について確認して下さい。

  
SQL> insert into TBL1 values(1, 'shibacho');

行1でエラーが発生しました。:
ORA-01950: 表領域'TBS_BIG'に対する権限がありません
 
 

 上記のようなORA-01950エラーが発生したかと思います。マニュアル「Oracle Databaseエラー・メッセージ」で確認すると次のような記述となっています。

  
ORA-01950: 表領域'string'に対する権限がありません
原因: ユーザーには、指定された表領域でエクステントを割り当てる権限がありません。
処置: ユーザーに適切なシステム権限を付与するか、表領域における領域リソースを付与してください。
 
 

 対処方法としては、TRYユーザーに対して「適切なシステム権限を付与」もしくは「TBS_BIG表領域における領域リソースの付与」となりそうです。今回の演習では後者の方法を解説します。 マニュアルでは「領域リソースを付与」と記述されていますが、正確には「Quota(領域使用の制限)を設定」するという作業になります。実は、一般ユーザーは各表領域を好き勝手に使用することは許されていないので、どこまで使用して良いのかをデータベース管理者が許可する必要があります。この具体的な設定方法については、次の演習で解説します。 ここで悩んで頂きたい点は、「CREATE TABLEは成功していた点」と「初めてINSERTしたら失敗した点」の2点です。この動作は、Oracle 9i DatabaseやOracle Database 10gをご利用されているデータベース管理者の方にとっては非常に疑問に感じる部分かと思います。実は、Oracle Database 11gからの新機能が影響しているのです。これについても以降の演習で解説します。

■演習9.TRYユーザーがTBS_BIG表領域を400MB使用できるようにQuota設定を行い、再度、TBL1表に1件INSERTして下さい。

  
sqlplus / as sysdba
SQL> alter user TRY quota 400M on TBS_BIG;

SQL> connect TRY/TRY
SQL> insert into TBL1 values (1, 'shibacho');
1行が作成されました。
SQL> commit;
コミットが完了しました。
 
 

 まず、SYSユーザーでTRYユーザーのQuota設定を変更します。このQuota設定の詳細については、「セキュリティ・ガイド」に記載があります。この作業により、TRYユーザーは、TBS_BIG表領域上に400MBまでのデータを格納することが許可されたので、再度、TBL1表にINSERTを行ったところ、問題なく成功することが確認できたかと思います。ちなみに、ユーザーにおける表領域のQuota設定はデータ・ディクショナリ・ビュー「DBA_TS_QUOTAS」で確認することが可能です。

  
SQL> connect / as sysdba

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

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

■演習10.データ・ディクショナリ・ビュー「USER_TABLES」で、2つの表のセグメントが作成済みか否かを確認して下さい。

  
sqlplus TRY/TRY
SQL> select TABLE_NAME, SEGMENT_CREATED from USER_TABLES;

TABLE_NAME                     SEGMENT_CREATED
------------------------------ ----------------
TBL1                           YES
TBL2                           NO
 
 

 データ・ディクショナリ・ビュー「USER_TABLES」には「SEGMENT_CREATED」列が存在し、表にセグメントが作成されたか否かを確認することが可能です。 上記の実行結果からは、INSERTを行った表「TBL1」にはセグメントが作成済みで、「CREATE TABLE」後に未だ1件もINSERTしていない表「TBL2」にはセグメントが作成されていないことが確認できます。これが、Oracle Database 11g Release 2の新機能「セグメント作成の遅延」が有効になっている動作となります。 この機能のメリットとしては大きく2点あり、「インストール時に数百、数千の表が作成されるが、一度もデータがINSERTされない表が大部分を占めるようなアプリケーションにおいて、ディスク領域が大幅に削減されること」とや「セグメントを作る時間を短縮できるので、そのようなアプリケーションのインストール時間が短縮されること」になります。この機能は初期化パラメータ「DEFERRED_SEGMENT_CREATION」で制御することが可能であり、デフォルトでは「TRUE」で有効化されている状態になっています。さらに、「CREATE TABLE」文の構文が拡張されており、この初期化パラメータの設定を上書きすることが可能になっていますので、ご興味のある方は勉強してみてください。 ちなみに、データ・ディクショナリ・ビューの各カラムがどのような情報を示しているのかについては、データ・ディクショナリ・ビュー「DICT_COLUMNS」で私は良く確認していますので、参考まで掲載しておきます。

  
SQL> 
col TABLE_NAME for a12
col COLUMN_NAME for a25
col COMMENTS for a80
set linesize 120 pagesize 500
select * from DICT_COLUMNS where TABLE_NAME='USER_TABLES';

TABLE_NAME   COLUMN_NAME               COMMENTS
------------ ------------------------- ---------------------------------------------------
USER_TABLES  TABLE_NAME                Name of the table
USER_TABLES  TABLESPACE_NAME           Name of the tablespace containing the table
.........
USER_TABLES  SEGMENT_CREATED           Whether the table segment is created or not
USER_TABLES  RESULT_CACHE              The result cache mode annotation for the table
 
 

■演習11.データ・ディクショナリ・ビュー「USER_SEGMENTS」で2つの表のセグメントが存在するか否かを確認して下さい。

  
sqlplus TRY/TRY
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENTS, BLOCKS from USER_SEGMENTS;

SEGMENT_NAME     SEGMENT_TYPE       TABLESPACE_NAME                   EXTENTS     BLOCKS
---------------- ------------------ ------------------------------ ---------- ----------
TBL1             TABLE              TBS_BIG                                 1          8
 
 

 はい、この最後の演習はおまけ的なものです。データ・ディクショナリ・ビュー「USER_SEGMENTS」では、そのユーザーが保持するセグメントを確認することが可能です。よって、一つ前の演習の結果と同じように、表「TBL1」に属するセグメントだけが存在している状況となっています。 前回よりも演習問題が少し多かったため解説も長くなってしまいましたが、以上で、基本的なオペレーションである、表と表領域の関係や新機能について試せたかと思います。最後に出てきた「セグメント作成の遅延」機能に触れることで、表構成の定義と、実際にデータが格納されるセグメントが異なることが新鮮であったかと思います。やはり自分で手を動かした方が、理解が深まりますし何かしらの気付きがあるので興味も広がりますよね。 次回は、「データ領域管理の理解~SQLチューニングにも挑戦~」についてになります。是非ご覧下さい。お疲れさまでした。

img_sibacho.gif

■しばちょう先生より

データベース・スペシャリストとして、 Oracle GRID Center の設立当初からOracleの持つ最新技術をパートナー各社と共同で検証し、これまでにリアルなパフォーマンスに裏付けられた数多くのWhite Paperを執筆してきました。現在は大規模案件の現場を訪問し、お客様のシステムに最適なソリューション・デザインの提案やパフォーマンス・トラブルの問題解決に従事しております。

これらの提案やトラブル解決を行う上で痛感していることは、SIer時代の開発現場や Oracle GRID Center での実機検証の経験が確実に生かされているということです。経験しているからこそ、マニュアル棒読みの機能紹介では留まらず、瞬時にその機能の適用シナリオも含めて自信を持って自分の言葉(お客様に合わせた言葉)でお客様に提案できますし、早期にトラブル原因の当たりを付けたり解決のアイディアを閃いたりすることが可能になっていると思っています。

今回の連載は、正に体験して頂くことが主軸となります。単純な機能紹介ではなく手を動かして理解を深めて頂けるような連載にしていきたいと考えております。内容としては私が新人をDBAに育てる際に使用する課題をカスタマイズしたものであり、レベルとしては初級~中級を想定しております。これからDBAを目指される方、実機での作業から数年間離れられている方等々、多くの方にご活用頂ければ幸いです。