8 Replies Latest reply: Feb 17, 2013 6:06 PM by user9351922 RSS

    ORA-1654 を防ぐには?

    user9351922
      Windows Server 2003 R2 で Oracle9i R2を運用している時にORA-1654が発生しました。

      発生時のDBの使用状況
         表領域情報: 使用している領域 サイズ:32,768M 増分:1,000M 最大:Unlimited 使用済:32,767M 状態:ONLINE
                   TEMP:8,388M
      UNDO:5,114M
         サーバーHDD:空き容量300G以上
         で発生しました。
         その後、再度同じ処理を実行したところORA-1654は発生せずに終了しました。

      こちらの 他の質問で
      -- 引用開始 --
      自動拡張の最大サイズに達しているケースが
      まず考えられますね。もう一つはデータファイル自体が最大サイズ
      (DB_BLOCK_SIZE×4M)に達している場合でしょうか?
      http://otn.oracle.co.jp/forum/message.jspa?messageID=2014707
      -- 引用終了 --
      とあるのですが表領域の最大をUnLimitedにしていたら自動で最大サイズは設定した増分ずつ
      増えてORA-1654が発生することは無いと思うのですが間違っているのでしょうか?
      アドバイス等ご教示お願いします。

      -- アラートログファイルより抜粋 --
      Sat Feb 16 04:36:42 2013
      ORA-1654: unable to extend index IPS_USER.SN1F_MKEY by 128 in tablespace      IPS_USER

      不足情報等ございましたらご指摘のほうも頂ければ幸いですので宜しくお願いします。
        • 1. Re: ORA-1654 を防ぐには?
          asahide
          IPS_USER表領域に属するデータファイルの情報を提示できますでしょうか。
          具体的には以下の出力を
          タグで表示して頂ければと。
          
          [code]
          SQL> SELECT * FROM DBA_DATA_FILES where tablespace_name='IPS_USER';
          [/code]
          
          # linesizeやpagesizeは適切に設定をお願いします
          
          
          ※※以下追記
          
          
          使用している領域 サイズ:32,768M 増分:1,000M 最大:Unlimited 使用済:32,767M 状態:ONLINE
          すみません、9iと言う事は、SMALLFILEですね。 SMALLFILEの最大サイズは32GBになります(8KBブロックの場合)。 データファイルを該当の表領域に追加すれば解消すると思います。 こちらのマニュアルの「4.データベースの制限事項」の「物理データベースの制限」にそれっぽい事が書いてあります。 <<http://otndnld.oracle.co.jp/document/oracle9i/920/generic/server/J06256-02.pdf>> Edited by: asahideO on 2013/02/16 11:37                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
          • 2. Re: ORA-1654 を防ぐには?
            user9351922
            asahideO さん返信ありがとうございます。
            SQL> SELECT * FROM DBA_DATA_FILES where tablespace_name='IPS_USER';
            の結果ですが

            {D:\ORACLE\ORADATA\ORCL\IPS_USER     11     IPS_USER     34359721984     4194302     AVAILABLE     11     YES     34359721984     4194302     128000     34359607296     4194288}

            でよかったでしょうか?
            linesizeやpagesizeは適切に設定をお願いします
            ですが適切な設定の仕方が分かっていないものでデフォルトのままDBを運用しています。
            出来ればこの機会に設定の仕方をご教示頂ければ幸いです。
            SMALLFILEの最大サイズは32GBになります(8KBブロックの場合)。
            ということなのですがSQLの結果でも分かるとおりIPS_USERの表領域は32GBを超えていますが
            SQLの結果は論理値で物理値とは違うのでしょうか?

            現状のままDBを運用しないほうがいいという認識で間違っていないでしょうか?
            質問ばかりになって申し訳ないですが宜しくお願いします。
            • 3. Re: ORA-1654 を防ぐには?
              asahide
              34359721984
              34359721984 B なので 32GBとなります。

              で、USERBYTESが「 *34359607296* 」なのでほぼMAXですね。。
              現状のままDBを運用しないほうがいいという認識で間違っていないでしょうか?
              もうMAXなので、データファイルを追加しないと運用を続けられないと思います。
              もしくは、データを削除するとか。。
              #不明な事があれば別途スレッドたてて頂ければ

              codeタグ
              こちらのスレッドを参照下さい。
              <<OTN掲示板の簡易マニュアル(タグの使い方とか)
              linesizeやpagesize
              SQLPLUSで出力するときに見易くしてほしい、という意味でした。
              set pagesize 3000 linesize 500 のような感じで。。
              • 4. Re: ORA-1654 を防ぐには?
                user9351922
                asahideO さん詳しい説明ありがとうございます。

                さしあたってデータの削除をしたいと思います。

                データファイルの追加については何をどうしたらよいか
                全くわからないのでデータファイルを追加する時にまた
                質問させて頂きますのでご教示のほどお願いします。
                • 5. Re: ORA-1654 を防ぐには?
                  asahide
                  データの削除は、TRUNCATEやDROP等でHWMを下げる方法で実施してくださいね。
                  DELETEだと領域が開放されないので、一部のデータを削除したい場合にはMOVEとかExp/Impとかが必要になってきます。。。
                  • 6. Re: ORA-1654 を防ぐには?
                    user9351922
                    注意点のアドバイスありがとうございます。

                    一部のデータを消そうとしていたので非常に助かりました。

                    ありがとうございました。
                    • 7. Re: ORA-1654 を防ぐには?
                      asahide
                      一応どのようなデータが存在しているのかは以下で確認できると思います。
                      select segment_name, SUM(bytes)/1024/1024 MB from dba_segments where tablespace_name='IPS_USER' group by segment_name order by 2 desc;
                      データファイルの追加はすぐにできますが、 DBA_TABLESPACES とか提示すれば大体コマンド出てくると思います。
                      • 8. Re: ORA-1654 を防ぐには?
                        user9351922
                        詳細なSQLのご教示ありがとうございます。