カテゴリー別アーカイブ: エラー、注意点

テーブルの項目数、レコード長の制限

ITコーディネータのシュウです。

IMG_0301

先日、会社の同僚の誘いで海釣りに行く機会がありました。写真は、そのときにボートから撮った写真です。小学生の息子も連れて行って来ました。ボートに乗る30分から1時間くらい前には酔い止めの薬を飲んで(妻から、忘れちゃダメと何度も言われたので、そこはしっかりとやって)、いよいよ乗船! 

最初のポイントでは、キスとかが釣れたらと思っていたところ、私も息子も同じく得体のしれない20cm超の魚をゲット! 釣れたのはたぶん「オキエソ」と言われるあまりかっこうのよくない魚。知人が「外道」が釣れたねと言っていたので、すごい名前の魚もいるものだと思いましたが、実は、狙っていた魚ではないとき、外道が釣れたと言うんですね!

次のポイントの岩場で、息子はカサゴをゲット。私も結構な引きがあったので、もう少しでカサゴが釣れそうだったけれど、リールを巻いているときに逃げられてしまい、とても残念。
息子は初めての海釣りの経験だったのですが、なかなか釣れない中、頑張っていました。その日は潮時としては「小潮」と言われるときで、魚のエサの捕食行動が鈍り、釣りにくい日だったようで、確かに全体的に釣れなかったですね。
それでも、いい経験ができてよかったです。家に帰ったら、釣った魚を妻に料理してもらって、その日はおいしい魚料理でした。

オコゼ  アイソメ

<本日の題材>
テーブルの項目数、レコード長の制限

仕事でEXCELを使って様々な業務を行う場合に、列数がものすごく多いシートを扱わざるを得ないケースが出てきますよね。そのようなEXCELのシートをそのまま使ってWebシステム化するためのツールとして、dbSheetClientというものがあることは、以前ご紹介したと思います。
http://www.newcom07.jp/dbsheetclient/dbsclan/index.html

そのソフトを使って開発する際に、EXCELの内容をDBと連結するための設計を行うのですが、たくさんの列数をそのままのかたちでデータベースに持たせたいと思う場合もときにあります。
そんなときに、1つのテーブルに持てる列数っていくつまでなんだろう? 1レコードに持てるレコード長に制限はあるのだろうか? などということを考える場合があるので、今回その内容を取り上げてみます。

SQL Serverの場合、調べてみると、通常のテーブルでは、列数は1024まで、行ごとの最大のバイト数は8060バイトとなっています。
https://msdn.microsoft.com/ja-jp/library/ms143432.aspx

それでは、実際に1024個の列を持つテーブルを作成してみます。

CREATE TABLE test_col_max(
    col_001key VARCHAR(10) NOT NULL
  , col_002 VARCHAR(10)
  , col_003 VARCHAR(10)
  , col_004 VARCHAR(10)
  , col_005 VARCHAR(10)
  , col_006 VARCHAR(10)
  , col_007 VARCHAR(10)
.....  途中省略
  , col_1020 VARCHAR(10)
  , col_1021 VARCHAR(10)
  , col_1022 VARCHAR(10)
  , col_1023 VARCHAR(10)
  , col_1024 VARCHAR(10)
  , CONSTRAINT PK_test_col_max PRIMARY KEY (col_001key)
) ;
列数1024

 テーブルは作成できました。
ただし、以下のような警告が表示されます。
「警告: 行の最大長が許容制限の 8060 バイトを超えています。一部の大きな値の組み合わせでは、挿入操作または更新操作が失敗します。」。これは、各列のタイプを VARCHAR(10)としたので、各列にMAXで値を詰め込んだ場合、10 * 1000 = 10000 バイトになってしまうので、このような警告が出るということです。

それでは、列数が 1025個ではテーブル作成がどうなるか試してみます。CREATE TABLE文で項目を1つ追加して、再度作成を実行してみると、下記のようなエラーが出て作成できません。
列数1025エラー

 エラーメッセージは、「CREATE TABLE が失敗しました。テーブル 'test_col_max' の列 'col_1025' が、列の最大数 1024 列を超えています。」。やはり、1024列までしか作成できないことが確認できました。(これは、一時テーブルでも同じ)

では、1024列までの項目でテーブルの作成まではできたとしても、行サイズのトータルが8060バイトを超える場合はどうなるでしょうか? それも試してみます。

まずは、8060バイトを超えない場合として、EXCELで作成したCSVファイルを、1024個の列を持つ「test_col_max」テーブルに登録してみます。前々回、BCPコマンドを紹介しましたが、今回は、BULK INSERTコマンドで登録してみます。各列に「1」、「2」など、1文字のみを設定したCSVファイル(サンプルとして3行)を読込んでみます。

BULK INSERT dbo.test_col_max
FROM 'c:\WORK\blog\blog_1024.csv'
WITH
(
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
);

bulk_insert

登録できました。データを確認してみます。

SELECT * FROM dbo.test_col_max
 ORDER BY col_001key;

列数1024_確認

データが3行作成されているのが確認できます。
次に、各列に「1000000000」など10文字ずつの値が設定されたcsvファイルを読込んでみます。

先ほどと同様に、BULK INSERTを実行してみると、下記のようなエラーが発生してデータを取込めません。

行バイト数超過エラー

 これは、行の合計のレコード長が10 * 1024 = 10240 バイトとなり、8060バイトを超えてしまっているためです。

なお、テーブルの列数の制限は、1024ですが、SELECT文での列数の最大値は 4096ということですので、以前取り上げた縦横変換などで、縦のデータを横に項目を並べて抽出する場合など、SELECT文で多くの項目を抽出する場合、1024を超えて4096列まで行うことができるということです。

<ORACLEの場合>
ちなみに、ORACLEではどうかというと、1つのテーブルに持てる最大列数は、1000列です。
上記と同様に、1001列を持つテーブルを作成しようとすると、下記のようにエラーになります。

oracle_行するMAX

 「ORA-01792:表またはビューに指定できる最大列数は1000です。」というエラーが出て、テーブルを作成できません。

上記から1列減らして1000列を持つテーブルを作成しようとすると、下記のように無事作成することができました。

oracle_retu

ここで、SQL Serverでは行の合計サイズの制限のためエラーとなった、各項目が全て10バイトまで詰まったCSVファイルを取込んでみます。ORACLEのSQL*Loaderというツールで行ってみます。
制御ファイルは以下のような感じ:

oracle_sqlldr_ctl

(項目が多いので、上記の制御ファイルの画面コピーは、途中で項目が切れてます。)

それでは実行してみます。
sqlldr userid=blog control=blog.ctl

ora_sqlldr_exec

問題なく取り込めました。結果を確認してみます。
SELECT * FROM TEST_COL_MAX
ORDER BY COL_001KEY;

ora_col1000

1000個のすべての列に、10バイトずつ登録されたデータが確認できます。

ちなみに、ORACLEの場合は、行の合計レコード長が 8060バイトのような制限は特にはなく、例えば全ての項目がVARCHAR2型であれば、1項目で4000バイトまで持てるので、1000列 * 4000 バイト = 4000000 バイトまでは持てることになりますね。

※SQL Serverでも SQL Server 2005から、大きな値のデータ型とよばれる、varchar(max)、nvarchar(max)型というものが機能として増えています。これについては、別の機会に取り上げてみたいと思います。

 今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

エクスポート(Oracle11gR2)での注意点(0件のテーブル)

ITコーディネータのシュウです。

DSC_2897

ラグビーワールドカップで、日本がサモアを破り、2勝目をあげました。ほぼ完ぺきなかたちでサモアの攻撃を抑えて、前半は何と20-0、後半もサモアの攻撃を1トライに抑えての完勝といっていい内容でしたね。見ていてとても興奮しました。次のアメリカ戦も是非頑張ってほしいと思います。

さて、dbSheetClientには、EXCEL版だけでなく、Access版というものもありますが、この夏Access版の体験版がリリースされています。Access版については、「仙ちゃんのシステムフォロー記」という記事でも詳しく説明がありますので、是非興味のある方はそちらを読んでみてください。こちらもExcel版と同様、データベースにはOracle、SQL Serverなどを利用してシステムを構築できます。
http://www.newcom07.jp/dbsheetclient/access/forrow6.html

 <本日の題材>
エクスポート(Oracle11gR2)での注意点(0件のテーブル)

最近、Oracle11gR2にて、テスト環境の作成のため、スキーマごとエクスポート(expコマンド)して、テスト用ユーザにインポート(impコマンド)したときに、一部のテーブルが作成されず、それを使用したプロシージャがコンパイルされないということを経験しました。
よくよく調べてみると、インポート(impコマンド)が失敗しているという訳ではなく、エクスポート(expコマンド)の際に、全てのテーブルがエクスポートされていなかったのです。どういうテーブルがエクスポートされなかったかというと、データ件数が0件のテーブルです。
何故こういうことが起きるのか、原因を調べていくと、Oracle11gR2のデータベース・ユーティリティについてのドキュメントに、以下のような記載がありました。

オリジナルのエクスポート
・Oracle Database 11gリリース2(11.2)では、デフォルトで、DEFERRED_SEGMENT_CREATIONパラメータがTRUEに設定されます。つまり、作成した表は、データの最初の行が表に挿入されるまでセグメントがありません。オリジナルのエクスポートではセグメントのない表が無視されます。したがって新しい表を作成しても、エクスポートの前にデータを挿入しないと、それらの表はエクスポートされません。(データ・ポンプ・エクスポートではこの制限がありません。これはセグメントのない表が無視されないためです。)
http://docs.oracle.com/cd/E16338_01/server.112/b56303/whatsnew.htm

 つまり、作成した表に1行でも行を登録しないとセグメントが作成されず、そのためもともとのexportコマンドでは無視されてしまうということです。
昔から利用していた exportコマンドではなく、データ・ポンプ・エクスポートと言われる expdpコマンドでのエクスポートでは、こういうことは起きないということなので、こういうケースの対応としては、以下のどれかになると思われます。

・expdpコマンドでのエクスポート、及びimpdpコマンドでのデータ・ポンプ・インポートを行う。
・初期化パラメータ「DEFERRED_SEGMENT_CREATION」パラメータを「FALSE」に変更して、exportコマンドを実行する。
・件数が0件というテーブルをなくしてから、エクスポートを行う。

しかし、3番目は現実的には難しいと思われるので、1番目か2番目の対応となりますよね。

実際に試してみます。

例)
SCOTTユーザのスキーマに、以前ブログの自律型トランザクションの題目でも紹介したエラーのログをためるための「ERROR_LOG」テーブルを作成し、それを利用するプロシージャ「RECORD_ERROR」を作成した直後に、通常のexportを実施します。

このとき、scottのスキーマにあるテーブルには何があるか確認します。
SELECT TABLE_NAME FROM USER_TABLES;

scott_table

ここで、scottユーザのスキーマごとexportを実行します。
exp scott/tiger file=scott.dmp  STATISTICS=none

exsport_scott

(※STAISTICS=none は、「EXP-00091: 不審な統計をエクスポートしています。」のエラーが出ないようにするためのもので、統計情報をエクスポートしない設定に今回はしています)

エクスポートされているテーブルに、先ほど作成したデータが0件の「ERROR_LOG」テーブルがないことがわかります。こうなると、このダンプファイルをインポートしても「ERROR_LOG」テーブルは存在しないので、それを使っているプロシージャはエラーになってしまいます。

次に、expdpコマンドによるデータ・ポンプ・エクスポートを実行してみます。

まず、前段階として、ディレクトリの作成とread/write権限の付与を行います。

sqlplus / as sysdba
create or replace directory DP_DIR as ‘\work\dpdir’;
grant read, write on directory DP_DIR to SCOTT;

directory

その後、expdpコマンドで、SCOTTユーザのスキーマ全体をバックアップします。

expdp scott/tiger directory=DP_DIR dumpfile=scott.dmp

expdp_scott

先ほどのexportではエクスポートされなかった0行の「ERROR_LOG」テーブルが、今回はエクスポートされているのが確認できます。

このエクスポートファイルを、scott2 というスキーマに丸ごとインポートします。

impdp dbadmin/パスワード directory=DP_DIR dumpfile=scott.dmp REMAP_SCHEMA=scott:scott2

impdp_scott

※以前のimpコマンドでは、FROMUSER=scott  TOUSER=scott2 となっていたところが、impdpコマンドでは、REMAP_SCHEMA=scott:scott2 となります。

上記によってインポートされたscott2ユーザのほうでは、「ERROR_LOG」テーブルもインポートされ、それを使ったプロシージャもVALID(使用可能)な状態となっています。

さて、もう一つの初期化パラメータ「DEFERRED_SEGMENT_CREATION」パラメータを「FALSE」に変更して、exportコマンドを実行するほうも試してみます。

sysdbaでログインし、初期化パラメータ「DEFERRED_SEGMENT_CREATION」を確認した後、FALSEに変更します。

sqlplus / as sysdba
SHOW PARAMETER DEFERRED_SEGMENT_CREATION
ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;

alter_system_def

この状態で、再度、exportコマンドを実行してみます。

exsport_scott_after

しかし、今回も「ERROR_LOG」テーブルはエクスポートされませんでした。

ここで、初期化パラメータを変更した後に、データが0件の「ERROR_LOG」テーブルを再作成する必要があるようです。

一旦DROP TABLEを行った後に、再度 CREATE TABLEを行います。(プロシージャも再度コンパイルします)
その後、もう一度exportを実行してみます。

 exsport_scott_after2

 今度は、「ERROR_LOG」テーブルがエクスポートされているのが確認できました。少し手間な部分もありますが。。。

今日は以上まで

 

にほんブログ村 IT技術ブログへ
にほんブログ村

ORA-28001:パスワードが期限切れです

ITコーディネータのシュウです。

IMG_0655_2

ときおり散歩する公園で、かもがたくさん群れをなして歩いているのを見ました。珍しいと思って急いで撮ってみた写真です。こんなにたくさん歩いているとはびっくりですね。何かあったのかな~!

<本日の題材>
ORA-28001:パスワードが期限切れです。

今回は、昨日までOracleを使って問題なかったのに、ログインしようとしたら急に「ORA-28001:パスワードが期限切れです。」というエラーが出てログインできなくなるという事態に遭遇したことがあったので、それについて情報としてアップします。

これは、今までOracle10gを使っていたが、Oracle11gにバージョンアップした場合などによく起きることがあります。理由としては、Oracle10gまではユーザのパスワードの有効期限はデフォルトで無期限であったものが、Oracle11gからはデフォルトで180日に設定されているため、180日たったときに起きてしまうというものです。

さすがにOracle11gがリリースされてかなりの期間が立ちますので、既に知っておられる方が多いかとは思いますが、意外とこのエラーに出くわした方が多いのではないでしょうか。

セキュリティ上はたしかにパスワードを定期的に変更したほうがよいのでしょうが、開発時やテストなどでは無期限にしてアクセスしたい場合も多いと思います。

Oracle11gの場合、「プロファイル」という、ユーザーに対するシステムリソースやパスワードの制限の設定をまとめたものをユーザに割り当てることで、一括してパスワードの制御などを行うことができるようになっており、パスワードの有効期間もそこで設定されます。
ですので、デフォルトのプロファイルが設定されているユーザであれば、このときの対処法の例としては、

1)デフォルトのプロファイルのパスワード有効期限を無期限にする
2)エラーでログインできないユーザのパスワードを再設定する
3)ロックされているアカウントのロックを解除する

というかたちで対応はできることになります。

例として、SQL Plusで行う場合を以下に記載します。
DOSプロンプトからSQL Plusを起動。

sqlplus sys/[パスワード]@接続文字列 as sysdba

・デフォルトのプロファイルのパスワード有効期限を無期限にします。

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

alter_profile

・エラーでログインできないユーザのパスワードを再設定します。

ALTER USER ユーザ名 IDENTIFIED BY 新パスワード;

alter_user_pass

 ・ロックされているアカウントのロックを解除します。

alter_user_unlock

 以上で、再度 scott でログインできるようになります。

scott_login

今日は以上まで

 

にほんブログ村 IT技術ブログへ
にほんブログ村