スポンサーリンク
カテゴリー:"Oracle"

Oracleで全てのユーザーオブジェクトのソースを取得しよう

Oracle で全てのユーザーオブジェクトのソースを取得する必要があったので、ちょっと調べてみました。

まずは取得可能なオブジェクトの種類を下記の SQL で取得します。

SELECT OBJECT_TYPE FROM USER_OBJECTS GROUP BY OBJECT_TYPE

もうちょっとスマートな方法がある気がギュンギュンするのですが、とりあえずはこれで取得できます。

次に該当の種類のオブジェクトの一覧を、下記の SQL で取得します。

SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'オブジェクトの種類'

で、最後にオブジェクトのソースを下記の SQL で取得します。

SELECT LINE, TEXT FROM USER_SOURCE WHERE NAME = 'オブジェクト名' ORDER BY LINE

なお、LINE カラムの値が行番号に相当しますので、LINE で ORDER BY しておきます。

この一連の作業をプログラムにすると、

続きを読む »

Oracle 10g で 「ORA-01031」 エラーでオブジェクトがコンパイルできなかったので

久しぶりに Oracle のテスト用データベースを作ったら、一部のオブジェクトが

ORA-01031: 権限が不足しています。

というエラーを出してコンパイルできずにハマったので、覚え書きです。

ちなみにテストDBは新規にデータベースを作って、本番サーバーからエクスポートしたデータをインポートして作っています。

とりあえず詳しく調べていくと、どうやらコンパイルできないオブジェクトは他のユーザー(スキーマ)のデータを参照しているんですね。

だったら、他のユーザーのオブジェクトへの参照権限を付与すればOKのはず。と以下の SQL を実行したところ、うまくいきました。

まず、参照先のユーザー(USER1)でログインして、参照元のユーザー(USER2)に権限を付与します。ここではテーブル(TABLE1)への参照(SELECE)権限を付与しています。

> GRANT SELECT ON USER1.TABLE1 TO USER2 WITH GRANT OPTION;

これで USER2 でログインしなおして、エラーなっていたオブジェクトを再コンパイルすればOKです。

後から考えたら、インポートする時に権限をインポートしないようにしたのが原因と思われますが・・・、権限をインポートしたらしたで、いろいろあったりするからなぁ。難しいところです。

参照リンク
 ・ORA-01031 - オラクル・Oracle エラー FAQ
 ・ORA-01031: 権限が不足しています。 | 技術情報 | 株式会社コーソル
 ・ビュー(VIEW)に対するアクセス権限(GRANT)の付与の仕方についてアドバイスください - Database Expert - @IT
 ・GRANT Oracle® Database SQL言語リファレンス 11gリリース2(11.2)

Oracle 11g に 「ORA-28001」 エラーでログインできなくなったので

会社の Oracle 11g にいきなりログインできなくなったと思ったら、

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

ですってぇ!?

急いで調べてみたところ、Oracle 11g からはデフォルトでパスワードの有効期限が180日に設定されているそうなんです。知らんかった。

というわけで、パスワードの有効期限を無期限にする方法をメモメモです φ(..)。

まずはデータベースに sysdba 権限で接続し、以下のコマンドを入力します。

alter profile default limit password_life_time unlimited;
alter user [ユーザID] identified by [パスワード];
alter user [ユーザID] account unlock;

1行目では 「default」 という名前のプロファイルのパスワードの期限を無期限にします。特に設定していなければ、各ユーザーにはこのプロファイルが割り当てられているようです。

あとは2行目でユーザーのパスワードを再設定して、3行目でロックを解除します。

2行目のパスワードの再設定は必要ないような気がしましたが、現在パスワード自体に期限が設定されているので、ここで無期限のパスワードとして再度設定する必要があります。

3行目のロックの解除については、状況によっては必要ないかもしれません。実は 1行目>3行目>2行目 の順で実行したので (・・。)ゞ

ついでにユーザーに対してパスワードの期限を設定する方法(alter user で何とか出来ないか)がないか探してみたのですが、見つけられませんでした。残念。

参照リンク
 ・開発備忘録 ORA-28001:パスワードが期限切れです。
 ・Oracle11g パスワードの有効期限の変更方法 - エムイズム - つぶやきブログ

Oracle 10g R2 で複数スキーマのデータを一気にインポートしよう

Oracle 10g R2(10.2.0.1.0) で複数のスキーマ(ユーザー)のデータを一気にインポートいけれど、エクスポートファイルの全データをインポートしたいわけではない(full=y は使いたくない)場合の覚え書きです。

まず、一般的な imp コマンドの書き方としては、以下のような感じになります。

imp インポート権のあるユーザ名/パースワード file=エクスポートファイルのパス log=ログファイルのパス fromuser=エクスポート元のユーザー名 touser=インポート先のユーザー名 rows=y ignore=y

より実際的な例としては、

imp SYSTEM/PASSWD file=C:\Temp\exp.dat log=C:\Temp\exp.log fromuser=user1 touser=user2 rows=y ignore=y

となります。この場合、exp.dat 内の user1 スキーマのデータが user2 スキーマにインポートされます。

上記の例だと、fromuser,touser ともにスキーマ名を1つずつ指定しているわけですが、一度に複数のスキーマについてインポートしたい場合は、fromuser,touser をカンマ区切りで指定して以下のようにします。

imp SYSTEM/PASSWD file=C:\Temp\exp.dat log=C:\Temp\exp.log fromuser=user1,user3,user5 touser=user2,user4,user6 rows=y ignore=y

これで user1 のデータが user2 に、user3 のデータが user4 に、user5 のデータが user6 にインポートされます。

Oracle 10g R2 をインストールしたら、SYSTEMユーザーがロックされていたので

 久々に Oracle 10g Release 2 (10.2.0.1.0) のインストールしたんですが、インストール直後の状態で SYSTEM ユーザーがロックされてて焦りました。

 このバージョンの Oracle はこれまでも何度かインストールした経験があるのですが、 SYSTEM ユーザーってロックされてたかなぁ? 記憶が無い・・・orz

 とはいえ、SYSTEM ユーザーのロックを解除しないことには作業が進められないわけで、なんとかロックを解除した際の覚書です。

手順としては以下のとおり。

  1. ローカルで sqlplus を起動。
      >SQLPLUS /NOLOG
     
  2. sysdba でログイン
      >CONNECT / AS SYSDBA
     
  3. system ユーザーのロックを解除
      >ALTER USER SYSTEM ACCOUNT UNLOCK;
     
  4. sqlplus を終了

以上でリモートからも SYSTEM ユーザーで接続できるようになりました。

参照リンク
 ・ユーザのロック/アンロック by オラクル魂

Oracle 10g が「ORA-00257」エラーで止まったので

いろいろ忘れていて対処に手間取ってしまったので、自分用にメモメモ。

とりあえず詳しいエラー内容を確認すると、

ORA-00257: アーカイブ・エラーです。解除されるまで内部接続のみにしてください。

原因:
ARCHプロセスがREDOログをアーカイブしようとして、エラーを受け取りました。問題がすぐに解決されない場合、データベースはトランザクションの実行を停止します。アーカイブ先のデバイスで、REDOログ・ファイルを格納する領域が不足している可能性があります。

処置:
アーカイバ・トレース・ファイルを確認して、問題の詳細な説明を調べてください。また、初期化パラメータARCHIVE_LOG_DESTに指定されたデバイスが、アーカイブに対して適切に設定されていることを検証してください。

ORA-00000~ORA-00830 より引用

とのことだったので、とりあえず下記のSQLで現在のログモードを確認。

SQL> SELECT LOG_MODE FROM V$DATABASE ;

結果は ARCHIVELOG でしたので、とりあえずはアーカイブログモードで動いているようです。ちなみに上記SQLの結果は、ARCHIVELOG | MANUAL | NOARCHIVELOG のいずれかになります。

次に、フラッシュリカバリ領域の空き状況(使用率)を確認します。

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME           SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------- ----------- ---------- ----------------- ---------------
L:/oracle/log   4294967296 4119674880                 0              63

上記SQLの結果から、領域の最大サイズを示す SPACE_LIMIT と、使用済みの領域サイズを示す SPACE_USED を比べます。と、ここで空き容量がほとんど無くなっていることが判明・・・orz

ここからは RMAN で接続して作業です。ちなみに RMAN で接続するためのコマンドは以下の通り(いや、自分が忘れてたので)。また、USER/PASS の部分は、お使いの環境に合わせて置き換えてください。

rman target USER/PASS nocatalog

とりあえず、Oracle が認識しているファイルで、既に無くなっているファイルが無いか確認してみます。

RMAN> CROSSCHECK ARCHIVELOG ALL;

もしそんなファイルがあった場合は、上記コマンドの後で

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

としてやると、無くなっていたファイル分の領域が未使用分として認識されます。ここまでやって問題がなければ、古いアーカイブログを削除するか、フラッシュリカバリ領域を拡張するしかありません。

古いアーカイブログを削除する場合は、OSのコマンド(もしくは操作)で削除するのではなく、やっぱり RMAN から行います。そうしないと、Oracle がファイルが無くなったことを認識できないので、消えたファイルのサイズが空き容量として認識されません。

もし、OSから直にアーカイブログファイルを削除してしまった場合は、上の 「CROSSCHECK ~」 と、「DELETE EXPIRED ~」 のコマンドで、Oracle に空き容量を再認識させてください。

というわけで、7日前より古いログを削除する場合のサンプルは以下の通り。

RMAN> DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7' ;

また、フラッシュリカバリ領域を拡張する場合は、SQLで

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4G SCOPE=BOTH ;

とすると、合計で 4G のサイズを割り当ててくれます。

今回は、最終的にフラッシュリカバリ領域を拡張する事で対応しました。それにしても、ドライブに空き容量があって本当によかった。

参照リンク
 ・ORA-00000~ORA-00830
 ・ORA-00257 - オラクル・Oracle エラー FAQ
 ・Oracle / フラッシュリカバリ領域-ARCHIVELOGモード | WEBレシピ
 ・フラッシュバックリカバリの基礎知識 (1/3) - @IT
 ・アーカイブログの削除 (OSTL)

OracleでLimit句を使ったかのようなSQLを実行しよう

 Oracle でも MySQL や PostgreSQL みたく、SQL で LIMIT句が使えないかな? とちょっと調べてみました。

 結論としては、Oracle では LIMIT句そのものは使えないのですが、ちょいとSQL文を工夫することで、同様の結果が得る事ができました。

というわけで、メモメモφ(..)。

EMPNOENAME
7369SMITH
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
7900JAMES
7902FORD
7934MILLER

 例えば、左のような EMP テーブルがあったとします。このデータから、EMPNO の大きい方から2行を取得したいわけですよ。

これが MySQL や PostgreSQL なら、

  select * from EMP order by EMPNO desc limit 2

で一発というシュチュエーションです。

 さて、じゃぁ Oracle ではどうやるんだ? て話しなんですが、ROWNUM というカラム(?)を使います。上記の SQL を Oracle 用に書き直すと、

となり、帰ってくる結果は

ROWNUMEMPNOENAME
17934MILLER
27902FORD

のようになります。

 ここで、元のテーブルに無い ROWNUM ってカラムは何なんだ? どこから出てきた? という疑問が湧きますが、「Oracle がクエリーの結果に自動で追加してくれる行番号」 みたいな理解でいいと思います。

 お次はもうちょっと難しく、EMPNO の大きい方から3行目と4行目を取得しましょう。SQL は以下のように変わります。

そして結果の方は、

ROWNUMRNEMPNOENAME
137900JAMES
247876ADAMS

となります。内側の select文で帰ってくる結果セットの ROWNUM に、別名を付けて情報を保持させているのがミソですね。

ちなみに、 MySQL や PostgreSQL だと、

  select * from EMP order by EMPNO desc limit 3,2

という SQL文になります。

 ただ Oracle はその仕様において、order by を使用していないクエリーの結果の行の並び順は保証していないので、厳密に並び順が重要となる場合は、

のようにしておいた方がいいのかも。

なお動作確認は、Wondows Server 2008 上の Oracle 10g(10.2.0.1)にて行いました。

参照リンク
 ・OracleではLimit句が使えない | yukotan hour
 ・Oracleでlimit offsetもどき - よねのはてな

Oracle 10g R2 が動いているサーバーのPC名を変更しよう

 Oracleが動いているサーバーのPC名を変更すると、Oracleが正しく起動しなくなってしまうので、その対処の覚書です。なお環境は、OS:Windows 2000 Server、Oracle 10g R2 (10.2.0.1) で行いました。

■Step.0

前準備としてデータベースの SID名 とリスナーのポート番号、 SYS,SYSMANユーザーのパスワードを準備します。ユーザーのパスワードが分からない場合は、下記のSQLで再設定しておいて下さい。

alter user <USER_NAME> IDENTIFIED BY <PASSWORD>

また、すでにPC名を変更済みの場合は、いったんPC名を元に戻してOSを再起動します。

■Step.1

上記の準備ができたら、コマンドプロンプトを起動して

emca -deconfig dbcontrol db -repos drop

と入力します。OracleのSIDやポート番号、SYS,SYSMANユーザーのパスワードを聞かれるので正しく入力します。入力した内容が表示され、

続行しますか。 [はい(Y)/いいえ(N)]:

と聞かれるので y と入力し、処理が終わるのを待ちます。最後から2行目に

Enterprise Managerの構成が正常に完了しました

と表示されていればOKです。

■Step.2

サーバーのPC名を変更して、OSを再起動します。

■Step.3

コマンドプロンプトを起動して、今度は

emca -config dbcontrol db -repos create

と入力します。今度は、OracleのSIDやポート番号、SYS,SYSMANユーザーのパスワードに加えて、DBSMNPユーザーのパスワードを聞かれるので適切なパスワードを入力します。

実はこの段階では “DBSMNP” というユーザーはOracle内に存在していないため、ここで入力したパスワードでユーザーが作成されます。

続いて聞かれる 「通知用の電子メール・アドレス (オプション):」 と「 通知用の送信メール(SMTP)サーバー (オプション):」 については、必要なければ何も入力せずに[Enter]でOKです。

すべての入力が完了すると、入力した内容が表示され

続行しますか。 [はい(Y)/いいえ(N)]:

と聞かれるので y と入力し、処理が終わるのを待ちます。最後から2行目に

Enterprise Managerの構成が正常に完了しました

と表示されていればOKです。OSを再起動して、リモートクライアントから Oracle に接続できれば、作業完了です。

参考リンク
 ・Oracle10gサーバのホスト名とIPを変更する方法 - 亀吉日記 - Yahoo!ブログ
 ・emcaが動かない。 今日もなんだかいい天気♪/ウェブリブログ

追記:
 つか、Oracle 10g R2 って、IPアドレスのみの変更なら、特にOracle側をいじる必要はなくなったんでしょうか?
 うちの環境だとIPアドレスを変更しても普通に接続できるんですけど・・・。

Oracle 10g のリスナーのポートを変更しよう

 Oracleのリスナーのポートを、デフォルトの 1521 から変更した時の覚書です。環境は、OS:Windows 2003 Server、Oracle 10g R2 (10.2.0.1) で行いました。

 まずリスナーのポート番号を、「Net Configuration Assistant」を使って変更します。今回は 1526番ポートを使うことにします。

 「Net Configuration Assistant」での設定変更そのものは簡単にできるんですが、これだけではローカルから sqlplus で接続きても、リモートからでは接続できなくなってしまします。

 ここでサーバーの tnsnames.ora をテキストエディタで開いて、自分自身に接続するための記述(接続識別子は何か?)を確認します。例えばこんなのですね。

<接続識別子> =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <HOST_NAME>)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <SID>)
    )
  )

次に SYSTEMユーザーで Oracle に接続して、以下のコマンドを実行します。

ALTER SYSTEM SET LOCAL_LISTENER = '<接続識別子>' scope=both;

これで、リモートから接続できれば完了です。

参考リンク
 ・Oracle Databaseのポート番号の管理

Oracle 10g で勝手に作られる BIN$~ ってオブジェクトを何とかしたい

 先日いきなり「Oracle 10g を使えるようにしろ」と言われました。あの~、僕、まともに触ったことがあるのは 9i までなんですけど・・・。と言ったところで、許してもらえそうになかったので、テストサーバーを立ててほげほげしてたんです。

 ところが知らない間に「BIN$」で始まる名前のオブジェクトがたっくさん出来てるじゃないですか。

 
  なんじゃこりゃ~!?
 

 というわけでちょっと調べてみたんですが、10g から Drop したオブジェクトはリサイクルビン(Recycle Bin:再利用する瓶。Windowsのゴミ箱みたいなもの?)に入るようになって、即座には消えて無くならなくなったんだそうです。

 このリサイクルビンに入ってるオブジェクトが BIN$~ という変な名前のオブジェクトとして見えてたんですね。だから放っておいても害は無いんですが、ディスク領域は確保されたままですから、理由が無ければ消してしまいたいところです。まぁ、一番の理由は「目障りだから」だったりするんですけど(苦笑)。

 ではリサイクルビンを空にする(領域を解放する)方法ですが、一番簡単なのは下記のSQLを実行する事のようです。

SQL> purge user_recyclebin;

これで現在ログインしているユーザーのリサイクルビンの中身を空にすることができます。あぁ~、スッキリした。

参照リンク
 ・オラクル リサイクルビンメモ(Hishidama's Oracle Recycle Bin Memo)
 ・ORA-38301:リサイクルビンのオブジェクトにDDL/DMLを実行できません。日記と雑記と覚え書き/ウェブリブログ
 ・Oracle10g フラッシュバック アゲインの巻 その7|おら! オラ! Oracle|技術情報|株式会社インサイトテクノロジー

スポンサーリンク

プロフィール


  • 書いてる人:夢界 陸

    名古屋在住のおっさん。
    プログラミングやガジェットの話など、 日々の興味を徒然と綴っています。



    Twitterやってます @mukairiku



    運営サイト
    www.mukairiku.net

ブログ内検索

Licenses

  • Creative Commons License

OTHER

  • このブログのはてなブックマーク数

Blog powered by TypePad

スポンサーリンク