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

これは、ちょっと前に咲いた早咲きの桜の写真です。家の近くの川べりに咲いた桜がきれいでした。

さて、年度が変わって子供たちも春休みの後半を迎え、もうすぐ新しい学年として出発します。中学生の息子が数学の問題集のわからないところについて、聞いてきたのですが、なかなかスパッと解き方がひらめかず、かっこよく教えてあげることができませんでした。ん～！　俺って結構年を取ってきたかな～。頭が固くなってきたみたい。とほほほ。。。

話しは変わりますが、dbSheetClientにまたまた新しい事例がアップされました。
全社、約８００名が利用する、念願の「 予算申請（損益予算・設備投資予算）システム 」をdbSheetClientで構築！予算責任者及び担当者、財務部の作業負担が大幅に削減！という、JR九州様の事例第2弾です。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice_sequel/jr_kyushu2.html

また、dbSheetClient(ディービーシート・クライアント)の導入メリットや導入の様子を、マンガを使って解りやすく解説したページができていますので、こちらも是非ご参照ください。
http://www.newcom07.jp/dbsheetclient/intromanga/index.html

＜本日の題材＞
デッドロックその２（SQL Server）

前回、SQL Serverでのデッドロックの現象について、実際に簡単なSQLで発生することを確認しましたが、実際にデッドロックが発生した場合には、その情報を調査して対応しなければなりません。今回は、デッドロック発生時の情報の取得のやり方について見てみたいと思います。

SQL Server 2012以降では、拡張イベントという機能でデッドロックの情報も確認することができます。（それ以前は、トレースフラグ：1204と1222を設定して確認していました）

SQL Server Management Studioのオブジェクトエクスプローラーの「管理」→「拡張イベント」→「セッション」→「system_health」の下に、「package0.event_file」というものがあり、それをクリックすると下記のような表示が出ます。

ここで、メニューの「拡張イベント」→「フィルター」から

フィールドに「name」、演算子に「=」、値に「xml_deadlock_report」を設定して「OK」を押下すると、拡張イベントがデッドロックのイベントに絞られて表示されます。

このイベントにカーソルを合わせて、下側のウィンドウの「デッドロック」のタブをクリックすると、下記のように図でデッドロックの情報が確認できます。

 図の×がついているプロセスの上にカーソルを置くと、デッドロックが発生したSQLが確認できます。

また、「詳細」タブのほうで、その行をダブルクリックすると、デッドロックのより詳細な情報を確認できます。

ここでもデッドロックになったSQLが確認できますが、<deadlock> タグの中の、<process-list>タグのほうに
waitresource="KEY: 46:72057594039762944 (a0c936a3c965)"
というものがあり、実際のどのデータを処理しようとしたときにデッドロックになったかがわかるかたちになっています。

実際のデータを確認するのは、上記のKEY情報を使って以下のように抽出します。

SELECT *
FROM tbl_B(NOLOCK)
where %%lockres%% = '(a0c936a3c965)';

上記から、tbl_B テーブルの、CD_B = 4 のデータの UPDATE のときにデッドロックが起きたことがわかります。

今日は以上まで

にほんブログ村

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

お世話になった知人の方が、仕事の都合もあって実家のある愛媛県のほうに引っ越しされるということで、瀬戸大橋を電車で移動されているときに撮られた写真を頂きました。まだ瀬戸大橋を渡ったことはないのですが、是非一度行ってみたいですね。
そういえば、瀬戸大橋を渡る瀬戸大橋線に乗っているときに、瀬戸の花嫁の曲が流れるみたいですね。「瀬戸は日暮れて～！」瀬戸に来たという実感がわくみたいです。

さて、dbSheetClientに新しい事例がアップされました。
成約引合管理、予算収集、ワークフロー、文書管理、業績見える化まで、様々な業務効率化システムを4年間で８０以上構築稼働させたナラサキ産業様の事例です。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice/narasakisangyo.html

＜本日の題材＞
デッドロック（SQL Server）

なかなか普段はそんなにはお目にかからないデッドロックの現象ですが、複数のクライアントが同時に同じテーブルにアクセスする環境では、アプリの作り方によっては簡単に起きてしまうこともあります。
一方のタスクがロックを試みているリソースに対して他方のタスクがロックを獲得し、これが相互に行われるとデッドロックが発生するというのが一般的な現象になると思います。本日は、複数のテーブルに対して更新する処理を、処理の順番が逆になるかたちで2つのプロセスが行うかたちでのデッドロック現象を確認してみたいと思います。ちなみに、同じテーブルの異なる行に対する処理でも同様に発生します。

 例）
2つのテーブル（tbl_A、tbl_B）があり、片方の処理は、トランザクション処理の中で、tbl_Aのレコード（CD_A=4）を更新した後に、tbl_Bのレコード（CD_B=4）を更新しますが、同時に、もう一つの処理が tbl_Bのレコード（CD_B=4）を更新した後に、tbl_A（CD_A=4）の値を更新します。
なお、タイミングが重なるように、処理の途中で以前ブログでも取り上げましたスリープ処理（WAITFOR DELAY）を入れてみます。

処理A）：
BEGIN TRANSACTION
 UPDATE dbo.tbl_A SET
   amount = 2100
  WHERE CD_A = 4;
 
 WAITFOR DELAY '00:00:10'
 
 UPDATE dbo.tbl_B SET
   amount = 3000
  WHERE CD_B = 4;
COMMIT TRANSACTION

処理B）：
BEGIN TRANSACTION
 UPDATE dbo.tbl_B SET
   amount = 1200
  WHERE CD_B = 4;

 WAITFOR DELAY '00:00:3'

 UPDATE dbo.tbl_A SET
   amount = 1500
  WHERE CD_A = 4;
COMMIT TRANSACTION

処理Aを実行した直後に、処理Bを実行します。すると、下記のようなかたちで処理が進み、デッドロックが発生します。

実際に、SQL Serverで実行してみます。
SQL Server Maangement Studioの1つのウィンドウで処理Aを実行し、すぐに別のウィンドウで処理Bを実行します。

処理A

処理B

 すると、処理Aでは、上記の結果のように、下記のようなデッドロックのエラーが表示されます。

「メッセージ 1205、レベル 13、状態 51、行 9
トランザクション (プロセス ID 52) が、ロック個のリソースで他のプロセスとデッドロックして、このトランザクションがそのデッドロックの対象となりました。トランザクションを再実行してください。」

それでは、次に、デッドロックが発生したときの対応として、エラーコード：1205を取得したら処理をリトライするという仕組みを、BEGIN CATCH～END CATCHの中に組み込んでみます。

処理A)
RETRY:
BEGIN TRANSACTION
BEGIN TRY
 UPDATE dbo.tbl_A SET
   amount = 2100
  WHERE CD_A = 4;
 WAITFOR DELAY '00:00:10'
 UPDATE dbo.tbl_B SET
   amount = 3000
  WHERE CD_B = 4;
 COMMIT TRANSACTION
END TRY

BEGIN CATCH
 PRINT 'ロールバックします'
 ROLLBACK TRANSACTION
 IF ERROR_NUMBER() = 1205 -- デッドロックエラーコード
 BEGIN
   WAITFOR DELAY '00:00:05'
   GOTO RETRY
 END
END CATCH

処理B)
RETRY:
BEGIN TRANSACTION
BEGIN TRY
 UPDATE dbo.tbl_B SET
   amount = 1200
  WHERE CD_B = 4;
 WAITFOR DELAY '00:00:3'
 UPDATE dbo.tbl_A SET
   amount = 1500
  WHERE CD_A = 4;
 COMMIT TRANSACTION
END TRY

BEGIN CATCH
 PRINT 'ロールバックします'
 ROLLBACK TRANSACTION
 IF ERROR_NUMBER() = 1205 -- デッドロックエラーコード
 BEGIN
   WAITFOR DELAY '00:00:05'
   GOTO RETRY
 END
END CATCH

 SQL Serverで実際に実行してみます。

処理A）

 処理B）

処理Aのほうが、一旦はデッドロックのエラーが発生しましたが、5秒後に再度リトライを実施して、最終的には更新が終了している様子が確認できます。

処理後の、tbl_A、tbl_B のデータを確認してみると

tbl_Aは

 tbl_Bは

 上記から、最後は処理Aの更新処理がされていることが確認できました。

さて、実際には、デッドロックは上記のようなケースだけではなく、ロックのエスカレーションによるブロッキングが原因で発生する場合などもあります。これについては、また機会があるときに取りあげてみたいと思います。

今日は以上まで

にほんブログ村

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

新年あけましておめでとうございます。
ごあいさつがとても遅くなってしまいました。本年もよろしくお願いいたします。

昨年2016年は、IOTとともにAI（人工知能）がとてもクローズアップされるようになってきた1年ではなかったかと思います。Googleの「AlphaGo」は囲碁で韓国のトッププロ棋士を破り、話題になりましたね。囲碁は打ち手のパターンが10の360乗通り、あるいは400乗通り存在するといわれており、AIがプロ棋士に匹敵する強さになるにはあと10年かかるといわれていたのを、10年前倒しで達成したということです。また、IBMのワトソンは患者の正確な白血病の病名を10分で見抜き、割り出した病名に対する適切な治療によって患者の命を救ったと報道され、AIが医療の世界で大きく貢献する事例となりました。また、自動運転、ディープラーニングなど、様々な技術へのチャレンジ、大きな投資がなされています。
そして、20年後には現在日常で行われている仕事のほどんどをロボットが行う時代が来ると断言する有名なCEOもいれば、科学者や有識者の間では、2045年には人工知能が人間の知能を超えるという予測「シンギュラリティ」についての議論もあり、人工知能は人類史上最悪の脅威と主張する英ノーベル賞授賞物理学者・ホーキング博士のような学者もいます。
いよいよ、過去SFでしか想像できなかったような世界が具体的にやってくると予感させる時代になってきたと感じますが、この科学を正しく使いこなせるかという点が、昔から言われてきたことではありますが、今後の人類にとってのさらに重要な課題、焦点になってくるのではないでしょうか？
将来は、仕事や技術関連は、ロボットが受け持ち、人は、心情、文化、芸術などで楽しむ世界になるかも知れませんね！

＜本日の題材＞
Oracle12cでのユーザー作成でエラー「ORA-65096」

Oracle12cが国内で提供開始されたのは、2013年7月、既に3年半近くが経過しました。クラウドの『c』を製品名に掲げ、コンテナ、プラガブル、マルチテナントなど、これまでのOracle Databaseにはなかった多くのコンセプトを引っさげ、実に500を超える新機能が実装された12cですが、なかなか開発で12cに触れる機会が少なく、残念に思っていたところ、少し触れる機会がありましたので、今回はそれについて取り上げてみたいと思います。

まず、最初に行うことの一つがユーザの作成だと思いますが、ここで、11gまでと同じようにユーザを作成しようとすると、「ORA-65096: 共通ユーザーまたはロール名が無効です」のエラーが発生します。

まず、SQL*Plusを立ち上げ、sysユーザでログインします。

sqlplus / as sysdba

このあと、「BLOG_TEST」というユーザを作成してみます。

create user blog_test
identified by passwd
default tablespace users
temporary tablespace temp
quota unlimited on users;

すると、上記のように、「ORA-65096: 共通ユーザーまたはロール名が無効です」のエラーが発生します。
11gまでしか知らないユーザでは、ここでまずびっくりしてしまいますが、このエラーに対応するためには、Oracle12c で大幅に変更された、マルチテナント・アーキテクチャという新しいアーキテクチャの仕組みを理解する必要があります。

Oracle11gまでは、１つのインスタンスには1つのデータベースしかありませんでしたが、12cからは１つのインスタンスには1つのコンテナ・データベース(CDB)と、その下にプラガブル・データベース(PDB)と呼ばれる子DB（複数作成可能）が存在するかたちになります。
このマルチテナントのメリット、魅力については、既にいろいろな資料がWEB上などで出ていますが、例えば、OTNの「Oracle Database 12c　マルチテナントの魅力」というHPでは大きく5つの内容が上がっています。

１．サーバー最適化の向上
２．多数のデータベースを一元管理
３．多数のデータベースをまとめてバックアップ
４．データベースの迅速なプロビジョニング
５．データベースの迅速な移動
詳細はそちらのHPのほうを確認してみてください。

さて、ユーザー作成のエラーの対応の仕方に戻ります。11gまでと同様にOracleに接続した場合は、コンテナ・データベース(CDB)に接続されるのですが、CDBではユーザスキーマ(ローカルユーザー)を作成することができません。上記のような ORA-65096 のエラーになります。ローカルユーザーは、実際にデータを管理するプラガブル・データベース（PDB）に接続して作成する必要があります。

※ローカル・ユーザーとは、特定のPDBのみに存在するユーザーのことで、共通ユーザーとは、各コンテナ（ルートと各PDB）に同名のユーザーが存在する場合のことで、ユーザー名に接頭辞(C##)が必要になります。

それでは、現在接続しているデータベースを確認します。
SQL*Plus上で、show con_name を実行します。

上記のように、「CDB$ROOT」と表示されたら、コンテナ・データベース(CDB)に接続していることがわかります。プラガブル・データベース(PDB)に接続し直すために、まず、PDBの名前と現在のOPEN_MODEを確認します。

select name, open_mode from v$pdbs;

今回接続したいPDBは「PDBORCL」で、OPEN_MODEは「READ WRITE」となっていて、起動できていることが確認されましたので、PDBORCLに接続し直します。

alter session set container = pdborcl;

再度、現在接続しているデータベースを確認します。

プラガブル・データベースのほうに接続していることが確認できました。
もしも、PDBのOPEN_MODEが「MOUNTED」の状態であれば、まだPDBが起動していない状態のため、以下のようにしてPDBを起動する必要があります。
alter pluggable database pdborcl open;

また、停止する場合は、以下のようにします。
alter pluggable database pdborcl close immediate;

それでは、PDBに接続した状態でユーザーを作成してみます。

create user blog_test
identified by passwd
default tablespace users
temporary tablespace temp
quota unlimited on users;

次に、connect, resource のロールを付与します。

grant connect, resource to blog_test;

それでは、作成したユーザ「blog_test」でログインしてみます。

sqlplus blog_test/passwd@pdborcl

作成したユーザーでログインできました。
※ただし、PDBには、たとえサーバー内からの接続であっても、上記のように @ネット・サービス名 をつけて、必ずリスナー経由でログインする必要があるようです。

今日は以上まで

にほんブログ村

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

先日、会社の同僚の誘いで海釣りに行く機会がありました。写真は、そのときにボートから撮った写真です。小学生の息子も連れて行って来ました。ボートに乗る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)
) ;

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

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

 エラーメッセージは、「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'
);

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

SELECT * FROM dbo.test_col_max
 ORDER BY col_001key;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 今日は以上まで

にほんブログ村

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

ラグビーワールドカップで、日本がサモアを破り、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ユーザのスキーマごとexportを実行します。
exp scott/tiger file=scott.dmp  STATISTICS=none

（※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;

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

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

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

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

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

※以前の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;

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

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

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

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

 

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

今日は以上まで

 

にほんブログ村

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

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

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

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

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

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

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

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

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

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

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

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

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

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

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

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

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

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

今日は以上まで

 

にほんブログ村