Oracle

Oracle8i, Oracle10gを使っているので、よく使うコマンドや、うまく動かなかったときのメモ。

• Oracle関連書籍はこちら

conn / as sysdba で接続できない | Oracle

オペレーティング・システム認証である conn / as sysdba で接続できない。なので、シェルでのdbstart、dbshutスクリプトコマンドも使えないので不便。

製品：　Oracle Server - Enterprise Edition 

製品バージョン：　10.2.0.3.0 

プラットフォーム：　Sun Solaris x86-64bit 

 

 

■connect / as sysdba できない症状を確認する

conn / as sysdbaできない。つまりオペレーティング・システム認証ができない状態であることを確認する。 

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges 

dbshutスクリプトコマンドの中のconn / as sysdbaが効かないので、dbshutしても元気にインスタンスが立ち上がったままという現象がおきている。

 

■調査する項目

・OSユーザのidコマンドを確認する。

・$ORACLE_HOME/rdbms/lib/config.s を確認する。

・$ORACLE_HOME/rdbms/lib/config.c を確認する。

・OSユーザーが、Oracleインストール時に指定したOSDBAグループに属しているかを確認する。

・/etc/hostsについてドメイン指定されているかを確認する。フルドメインの記述の追加で回避できる可能性がある。

 

 

■調査する

OSユーザーのidコマンドを実行する。 

$ id
uid=75016(orauser2) gid=876(hrdba) 

ウチの会社では、oracle, oinstall といった一般的な命名はしていません。 

 

config.sを確認する。（一部抜粋）

.LV13:	.string "dba"
.LV12:	.string "dba"

 

以上の結果から、OSDBAである"dba"に所属していないことが分かる。

 

■解決する

OSDBAである"dba"にユーザを所属させる。

# usermod -G hrdba,dba orauser2 

 

■conn / as sysdbaで接続できるかを確認する

OSユーザーで、ログアウト、ログインし"dba"を有効にする。

SQL*Plusで確認する。

SQL> conn / as sysdba
Connected. 

 無事、接続できることを確認した。

 

■dbstart, dbshutできるかを確認する

OSユーザーで、インスタンスの起動と停止ができるかを確認する。

$ dbstart
$ dbshut 

無事、起動と終了ができることを確認した。

 

 

JDBC接続できない原因

なんで、Oracle8iへJDBC接続できないんだろ...と悩んでいたが、原因発覚！なんとSIDの名前を間違えていたのだ。tnsnames.oraのSERVICE_NAMEと違うじゃないかー！！

■ソースはこれ

Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@192.168.0.5:1521:ORA8";
String user = "scott";
String password = "tiger";
Connection conn = DriverManager.getConnection(url, user, password);

■エラーはこれ

java.sql.SQLException: I/O例外です。: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=135286784)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
...続くけど省略... 

 

■SQL*PlusでSIDを確認

SQL> set termout off
SQL> column sid0 new_value sid1
SQL> select rtrim(instance, chr(0)) sid0 from v$thread;

SID0
----------------
ORCL

■ついでにシェル上でも確認

$ echo $ORACLE_SID
ORCL

■でもtnsnames.oraはというと

ORA8.xxx.com =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.5) (PORT = 1521))
    )
    (CONNECT_DATA =
      (SEVICE_NAME = ORA8)
    )
)

というように「ORA8」なので、ORA8を指定すると思っていたのだ。

■ということで

最初のソースのORA8をORCLに変更すれば、無事接続！
これを発見するのに導いてくれたY君に感謝！

 

ORA-01034とORA-27101エラーでSQLPlusでログインできない

 Oracle XEを使い始めてしばらくするとログインできなくなっていた。

 JDBC経由だとなぜか接続できるが、SQLPlusだとORA-01034とORA-27101エラーが発生してログインできない。

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

 

ORA-01034エラーの原因は、環境設定が変更されたときに発生するとのこと。

そういえば、NTPで日付を設定し直したので、それが原因と考えられる。

 

で、Oracle XEを再起動したところ、状況は変わらなかった。

# /etc/init.d/oracle-xe stop
# /etc/init.d/oracle-xe start 

 

次に環境変数をチェックした。

$ env | grep ORACLE_SID
ORACLE_SID=orcl

 

このマシンは、以前SIDがorclで起動していたが、途中でSIDをXEに変更した。よって環境変数が古いマシンの設定を引き継いでいたので、.bash_profileからOracle環境変数の設定を削除した。

 

Oracle XEの環境変数は、/etc/profile.d/oracle_env.sh で設定されている。 

 

シェルをログインし直すと、ちゃんとSQLPlusでログインできるようになった。 

 

参考URL：

http://www.oracle.co.jp/2shin/ora66/18.html

http://biz.rivus.jp/technote507058.html

http://q.hatena.ne.jp/1121132650

 

Oracle XEのインストール

 

MiracleLinux v3 にOracle XE（Oracle Database 10g Express Edition）をインストールしてみた。Oracle EXではなく XEなんだな。

RPMでインストールするだけなので、簡単だった。

 

■ocssd.binを止めておく

以前にOracle10.1.0 をインストールしようとしたせいか、ps aux | grep oracle を実行すると、ocssd と ocssd.bin が動いていることが分かったので停止する。

# /etc/init.d/init.cssd stop
# /etc/init.d/oracleasm stop
# /etc/init.d/oracleasm disable

次回、サーバ起動時にocssd.binが起動しないように/etc/inittabの一行をコメントアウトしておく。下の#は、コメントアウトのシャープ。

# h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null 

 ocssd.binは、ASM (Automatic Storage Management) の機能を使うためのプロセスだそう。Oracleから起動されるのではなく、OS自体が起動させてコントロールするとのこと。

ASMは使わないので、停止する事にした。

詳細情報はこちら。 

http://forums.oracle.com/forums/thread.jspa?threadID=240943

 

find /etc/ -name "*.cssd" で検索して、rcスクリプトで起動されるか確認する。で、起動しないようにしておく。

# cd /etc/rc2.d
# mv S96init.cssd _S96init.cssd
# cd /etc/rc3.d
# mv S96init.cssd _S96init.cssd
# cd /etc/rc5.d
# mv S96init.cssd _S96init.cssd

マシンを再起動すれば、このメッセージは出なくなる。

INIT: Id "h1" respawning too fast: disabled for 5 minutes 

 

 

■Oracle-XEをダウンロードする

http://www.oracle.com/technology/products/database/xe/index.html

 

■Oracle-XEをインストールする

スワップが1GB以上必要とのこと。足りないので/tmp/swapを作って追加した。

# rpm -ivh oracle-xe-univ-10.2.0.1-1.0.i386.rpm

 データベース管理者「SYSTEM」のパスワードの入力が求められる。

 

■Oracle-XEの起動と停止

起動

# /etc/init.d/oracle-xe start

停止

# /etc/init.d/oracle-xe stop

 

■Oracle-XEへログインする

$ sqlplus /nolog
SQL> conn system

 SELECT * FROM TAB; でずらっとテーブルが表示されれば一安心。

 

■Oracle-XEのSIDを確認

SIDやORACLEのバージョン等、基本情報を確認をしておく。ORACLE_SIDはXE。バージョンは、10.2.0.1.0。

SQL> SELECT instance_name, status, version, edition FROM v$instance;




 SQL> SELECT * FROM v$version;

 

 使っているキャラクタセットなどの確認。

 SQL> SELECT * FROM v$nls_parameters;

 

データの保存場所と容量の確認。

SQL> SELECT file_name, bytes FROM dba_data_files;
SQL> SELECT file_name, bytes FROM dba_temp_files;

 

 

■ユーザとデータベースを作成する

 あとは、ユーザを作る。そして、そのユーザのデータベースを作る。

そのユーザでログインして、テーブルを作る。って流れ。 

まずは、表領域の確認をしておく。

SQL> SELECT file_name, tablespace_name FROM dba_data_files;
SQL> SELECT file_name, tablespace_name FROM dba_temp_files;

ユーザ表領域：USERS

一時表領域：TEMP

現状の登録ユーザを確認しておく。

SQL> SELECT username, account_status FROM dba_users; 

 登録されていないユーザ名で新規作成する。

CREATE USER suzuki     
  IDENTIFIED BY xxxxxx
 DEFAULT TABLESPACE users
 TEMPORARY TABLESPACE temp
 QUOTA unlimited ON users; 

 権限を付与しておく。

GRANT connect, resource TO suzuki; 

 

■Webページでの管理「HTML DB」を試す

 いやー、ハマりました。MiracleLinuxの8080ポートは開いているのにSafariから接続できない。（netstat -an | grep 8080）

手元のMacからLinuxへの接続確認。アプリケーション　＞　ユーティリティ　＞　ネットワークユーティリティ。これが便利。Portscanでポートスキャンしてみると8080が開いていません。

そう。ローカルからの接続からのみ許可していました。

ということで、MiracleLinux上での作業。

# xdm

Xが起動したら、ログインしてFirefoxで接続。

http://localhost:8080/apex/

管理　＞　HTTPアクセスの管理

「ローカル・サーバーおよびリモート・クライアントから使用可能」を許可してあげれば、MacのSafariからも接続OKに！　ふぅ。

あ、xdmを終了するには、ログアウトして、Control + R で。

 

Oracle XEのインストール後の初期状態

Oracle XEの初期状態をメモしておく。

SQLPLUSは、SYSTEMでログインしておく。

 

■初期の状態

ディレクトリの確認

$ echo $ORACLE_HOME
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server 

 

メモリ使用量の確認

SQL> show sga

Total System Global Area  281018368 bytes
Fixed Size                  1258440 bytes
Variable Size              96472120 bytes
Database Buffers          180355072 bytes
Redo Buffers                2932736 bytes

 

キャラクタセットの確認

SQL> select parameter, value
  2  from nls_database_parameters
  3  where parameter like '%HARACTERSET%';

PARAMETER
----------
VALUE
----------------------
NLS_CHARACTERSET
AL32UTF8

NLS_NCHAR_CHARACTERSET
AL16UTF16 

 

ユーザの確認

SQL> select username, created from all_users;

USERNAME                       CREATED
------------------------------ --------
FLOWS_020100                   06-01-31
FLOWS_FILES                    06-01-31
HR                             06-01-31
MDSYS                          06-01-31
ANONYMOUS                      06-01-31
XDB                            06-01-31
CTXSYS                         06-01-31
DBSNMP                         06-01-31
TSMSYS                         06-01-31
DIP                            06-01-31

USERNAME                       CREATED
------------------------------ --------
OUTLN                          06-01-31
SYSTEM                         06-01-31
SYS                            06-01-31

13行が選択されました。 

 

■データ容量の確認

データの保存場所を調べる。

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/usr/lib/oracle/xe/oradata/XE/users.dbf
/usr/lib/oracle/xe/oradata/XE/sysaux.dbf
/usr/lib/oracle/xe/oradata/XE/undo.dbf
/usr/lib/oracle/xe/oradata/XE/system.dbf

 

データ容量の確認。 

 $  ls -lh /usr/lib/oracle/xe/oradata/XE/
合計 1.1G
-rw-r-----  1 oracle dba 6.8M  1月 10 07:07 control.dbf
-rw-r-----  1 oracle dba 471M  1月 10 07:00 sysaux.dbf
-rw-r-----  1 oracle dba 341M  1月 10 07:00 system.dbf
-rw-r-----  1 oracle dba  21M  1月  9 22:00 temp.dbf
-rw-r-----  1 oracle dba 176M  1月 10 07:05 undo.dbf
-rw-r-----  1 oracle dba 101M  1月 10 06:46 users.dbf

 

 ■表領域の確認

SQL> SELECT tablespace_name, status, contents FROM dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDO                           ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT

 

■「ユーザの作成」と「権限の付与」

SQL> CREATE USER suzuki
  2  IDENTIFIED BY xxxx
  3  DEFAULT TABLESPACE users
  4  TEMPORARY TABLESPACE temp
  5  QUOTA UNLIMITED ON users;

 

SQL> GRANT CONNECT TO suzuki;
SQL> GRANT CREATE ANY TABLE TO suzuki;
SQL> GRANT CREATE ANY INDEX TO suzuki;
SQL> GRANT CREATE ANY SYNONYM TO suzuki;
SQL> GRANT CREATE ANY VIEW TO suzuki;

 

 

 

Oracle 別表（別テーブル）から値をコピーして一括更新する

別のテーブルからフィールドの値をコピーしてUPDATEしたいときがある。

郵便番号の一覧表から、都道府県をコピーする。などというときに使う。

UPDATE時に、SELECT結果を用いるというやり方で実施する。

 

■別表からコピーして行を更新する

 自分の住所録の住所欄に郵便番号リストの住所をコピーする。（全ての行を一括で更新）

 SQL> UPDATE my_addr
  2  SET jusho = (SELECT jusho
  3               FROM zip_list
  4               WHERE zip_no = my_addr.zip_no) ;

 

一部の行だけ変更したいとき。（WHEREで指定する）

 SQL> UPDATE my_addr
  2  SET jusho = (SELECT jusho
  3               FROM zip_list
  4               WHERE zip_no = my_addr.zip_no)
  5  WHERE jusho IS NULL ;

 

複数の列（カラム）を変更したいとき。

 SQL> UPDATE my_addr
  2  SET ( jusho, tel ) = (SELECT jusho, tel
  3               FROM zip_list
  4               WHERE zip_no = my_addr.zip_no) ;

 

確認してOKだったら commit; を忘れずに実行する。

 

 

■販売店テーブルの一部の店名が新しくなった場合

吸収合併等で社名が変わるのは、よくある話し。

わざと正規化していないテーブルだと、販売時の店名を残しておくことができることがメリット。でも「今の会社名に変更したいよね」なんてこともある。 

古い販売店テーブル

ID	販売店名
1	よろず屋
2	三河屋
3	イズミヤ

 

新しい販売店テーブル

ID	販売店名	変更対象
1	スーパーよろずや	はい
2	三河屋	いいえ
3	Too	はい

 

販売記録テーブル

ID	販売した商品	販売先ID	販売先の名前
1001	みかん	1	よろずや
1002	みかん	2	三河屋
1003	みかん	3	イズミヤ
1004	みかん	28	玉川屋
1005	みかん	29	伊勢屋

期待する、更新後の販売記録テーブル

ID	販売した商品	販売先ID	販売先の名前
1001	みかん	1	スーパーよろずや
1002	みかん	2	三河屋
1003	みかん	3	Too
1004	みかん	28	玉川屋
1005	みかん	29	伊勢屋

 

一括更新する方法、その１。

SQL> UPDATE 販売記録テーブル
  2  SET ( 販売先の名前 )
  3   =  (
  4         SELECT 販売店名
  5         FROM 販売店テーブル
  6         WHERE id = 販売記録テーブル.id
  7      ) ;

実行すると、販売記録テーブルの全件が更新される。

 

一括更新する方法、その２。

SQL> UPDATE 販売記録テーブル
  2  SET ( 販売先の名前 )
  3   =  (
  4         SELECT 販売店名
  5         FROM 販売店テーブル
  6         WHERE id = 販売記録テーブル.id
  7      )
  8  WHERE 販売先ID
  9  IN  ( SELECT id FROM 販売店テーブル WHERE 変更対象 like 'はい' ) ;

変更対象を 'はい' に絞ることで、販売記録テーブルの2件だけが更新される。 

 

一括更新する方法、その３。

SQL> UPDATE 販売記録テーブル
  2  SET ( 販売先の名前, 販売先の住所, 販売先の電話番号 )
  3   =  (
  4         SELECT 販売店名, 住所, 電話番号
  5         FROM 販売店テーブル
  6         WHERE id = 販売記録テーブル.id
  7      ) ;

複数の列（カラム）も、合わせて一括更新したいとき。

 

 

Oracle 重複行を検索して削除する

重複行を削除したいケースがたまにある。

• 取り込んだマスターデータに重複があった
• インポートを2回行って、二重登録してしまった

サンプル：　住所録テーブル

ID　　　名前　　　電話　　　　　　　住所

1　　　阿部　　　03-1234-1234　　東京

2　　　井上　　　03-5678-5678　　東京

3　　　阿部　　　03-1234-1234

4　　　井上　　　03-5678-5678 

 

今回は　名前、電話　が重複している3行目と4行めを削除することが目的。

ID番号を用いていない場合は、Oracleが内部で使用している行番号ROWIDを利用する。 

 

■重複行を検索して、何行くらいあるかを調べる

数千行のデータの中から、何行重複しているかを調べる。

SQL> SELECT 名前, 電話, count(*)
  2  FROM 住所録
  3  GROUP BY 名前, 電話
  4  HAVING count(*) > 1 ; 

名前、電話　が重複している行がリストアップされる。

（住所　は無視するというケース） 

 

重複が1行や2行であれば手動で削除し、数百行に及ぶ場合はSQL文で削除を実行するという目安になる。

 

 

■残したいデータを検索する

重複していない行、つまり残しておきたい行を検索する。

SQL> SELECT MIN( id )
  2  FROM 住所録
  3  GROUP BY 名前, 電話 ; 

名前、電話　が重複せず1件だけ存在しているときは、その行のIDがリストアップされる。

名前、電話　が重複して3件存在している場合は、一番小さいID番号がリストアップされる。

よって、残しておきたい行の、ID番号がリストアップされる。

 

idが存在しないときは、ROWIDを使う。

SQL> SELECT MIN( rowid )
  2  FROM 住所録
  3  GROUP BY 名前, 電話 ; 

 

 

■残したいデータ以外を削除する

上記で検索した、「残したいデータ」以外を削除する。

SQL> DELETE FROM 住所録
  2  WHERE id NOT IN
  3  (
  4     SELECT MIN( id )
  5     FROM 住所録
  6     GROUP BY 名前, 電話
  7  ) ; 

 これで　名前、電話　が重複したデータを削除できる。

 

idが存在しないときは、ROWIDを使う。

SQL> DELETE FROM 住所録
  2  WHERE rowid NOT IN
  3  (
  4     SELECT MIN( rowid )
  5     FROM 住所録
  6     GROUP BY 名前, 電話
  7  ) ; 

 

 

■重複行を削除する別の方法

名前、電話　が重複しているデータを検索して表示する。

SQL> SELECT rowid, 名前, 電話 FROM 住所録 A
  2  WHERE A.rowid > (
  3     SELECT MIN( B.rowid )
  4     FROM 住所録 B
  5     WHERE B.名前 = A.名前
  6     AND   B.住所 = A.住所
  7  ) ; 

 2行目の A.rowid > は、

A.rowid > B.rowid という真偽を判定している。

 

名前、電話　が重複せず1件だけ存在しているときは、A.rowid > B.rowid　は成り立たないのでリストアップされない。

名前、電話　が重複して3件存在している場合は、A.rowid > B.rowid　を処理し一番小さいROWID以外の2件がリストアップされる。

 

確認したら、削除する。

SQL> DELETE FROM 住所録 A
  2  WHERE A.rowid > (
  3     SELECT MIN( B.rowid )
  4     FROM 住所録 B
  5     WHERE B.名前 = A.名前
  6     AND   B.住所 = A.住所
  7  ) ; 

 

 

Oracle8iからOracle10gへの引っ越し

Oracle8iからOracle10g XEへの引っ越しメモ。

 

■データのエクスポートの前準備

キャラクタセットを確認しておく。EUCでエクスポートされることが分かる。

$ echo $NLS_LANG
Japanese_Japan.JA16EUC

 

キャラクタセットをSJISでエクスポートしたいときはNLS_LANGを指定しておく。

$ export NLS_LANG=Japanese_Japan.JA16SJIS

  

 

■データをエクスポートする 

全テーブルをエクスポートする場合。

$ exp ユーザ名/パスワード file=oracle8.dmp log=exp.log

 

一つのテーブルEMPをエクスポートする場合。

$ exp ユーザ名/パスワード tables=EMP file=ora.dmp log=ora.log compress=y

 

詳細は、expのヘルプを確認する。

$ exp help=y

 

NLS_LANGに合わせて、ターミナルの文字セットエンコーディングを合わせておくと文字化けせずに出力メッセージが読める。

 

■データのインポート

 書き込まれる文字コードを確認しておく。

$ echo $NLS_LANG
JAPANESE_JAPAN.AL32UTF8 

 

テーブルをインポートする。

$ imp userid=ユーザ名 ignore=y file=oracle8.dmp fromuser=旧ユーザ名 touser=ユーザ名

ユーザ名：　10gで使っているユーザ名

旧ユーザ名：　8iでエクスポートしたユーザ名

8iのときのユーザ名と10gのユーザ名をきちんと指定しないとインポートできないので注意。

 

インポート時のキャラクタセットについてのメッセージ。 

 エクスポート・ファイルはEXPORT:V08.01.05によって従来型パス経由で作成されました
AL32UTF8キャラクタ・セットおよびAL16UTF16 NCHARキャラクタ・セットでインポートを
実行します   

 

きちんと、EUCからUTF8へ変換されてインポートされたか、sqlplusで確認しておく。

 

OracleからDr. Sumにデータを取り込む際にエラー発生

OracleからDr. Sumにデータを取り込む際にエラーが発生した。

 

EMP       line(341)にて、フィールド位置が不正とのエラーが発生しています。

"line(341), position(49/48)"

 

■行を指定して情報を取得する

ROWNUMを利用して先頭からn行目を取得すればよい。 しかし残念ながらn行目のみを取得しようとして、「以上」や「BETWEEN」を使いたいが、ROWNUMは「以下」でしか指定できない。

「以上」や「BETWEEN」を使って検索すると、エラーを起こさず、検索結果が0件になるので注意。

 

ということで、「以下」を利用して、ROWNUMとID番号の列を取得する。エラーが起きている341行目を指定する。

select rownum, id from emp where rownum <= 341;

たら～っと長い行が表示されるが、この際気にしない。

 

341行目の ID番号を取得して、IDが543と判明した。

ID番号543のレコードの入力日、名前等を取得する。

select rownum, id, last_name, imput_date 
from emp
where a_id=543;

 

施設名：　丸嘉受付日：　2001/06/06で、48番目と49番目のフィールドは、

48　address1　「住所フィールド1」　中身は空っぽ。

49　address2　「住所フィールド2」　中身は空っぽ。

 

特におかしいデータは、見当たらなかったが、とりあえず未入力の社員番号だけ入れて保存してみた。

Dr. Sumでエラーが再び発生するかどうか、今は結果待ち。

 

 

OracleからテキストTAB、CSVデータでエクスポートする-1

Oracleからテキストタブ形式やCSV形式でデータをエクスポートして書き出す。

 

■Oracleからテキストタブファイルとしてエクスポートする

テープル：　emp

書き出す列：　id, last_name, first_name, address

書き出す行：　全レコード 

書き出し先のファイル名：　ファイル名.txt 

 

 

■テキストタブ（TSV）でエクスポートするSQL文をテキストで保存する

emp_export_tab.sql

set echo off
set heading off
set termout off
set pause off
set pagesize 0
set linesize 1000
set trimspool on
set feedback off
spool ファイル名.txt

SELECT
id || '    ' ||
last_name || '    ' ||
first_name || '    ' ||
address
FROM emp;

spool off
; 

上記の  '　'　は、「シングルクォート」「タブ」「シングルクォート」として入力している。

（例） ||の役割は「接続すること」

last_name || first_name　：　北野武

last_name || 'ビート' || first_name　：　北野ビート武

last_name || 'タブ' || first_name　：　北野    武 

 

（注意）

SELECTからFROM emp;までは、余分な改行を入れないこと。

 

■SQL*Plusでログインして、実行する。 

SQL> @emp_export_tab.sql 

出力されたファイルを確認する。 

SQL> ! 
$ ls
emp_export_tab.sql        ファイル名.txt

作業は、UNIX上のサーバで行う。SQL*Plusでログインするときの作業ディレクトリに、「ファイル名.txt」として出力されていることを確認する。

 

■OracleからCSVファイルとしてエクスポートするSQLをテキストで保存する

emp_export_csv.sql

set echo off
set heading off
set termout off
set pause off
set pagesize 0
set linesize 1000
set trimspool on
set feedback off
spool ファイル名.txt 

SELECT
'"' || id || '","' ||
last_name || '","' ||
first_name || '","' ||
address || '"'
FROM emp ;
spool off
;

（例） ||の役割は「接続すること」

last_name || first_name　：　北野武

'あの人は' last_name || 'さん'　：　あの人は北野さん

'ダブルクォート' last_name || 'ダブルクォートとカンマ'　：　"北野",

 

（注意）

SELECTからFROM emp;までは、余分な改行を入れないこと。

 

 

■SQL*Plusでログインして、実行する。 

SQL> @emp_export_csv.sql 

出力されたファイルを確認する。 

SQL> ! 
$ ls
emp_export_csv.sql        ファイル名.txt

作業は、UNIX上のサーバで行う。SQL*Plusでログインするときの作業ディレクトリに、「ファイル名.txt」として出力されていることを確認する。

 

 

OracleからテキストTAB、CSVデータでエクスポートする-2

TSV、CSVで、エクスポートするためのSQL文を簡単に作る方法。

3列くらいの書き出しなら手書きが早い。

しかし、50列もあるような場合は、以下の方法でSQL文を組み立てる。

 

■最終的に作りたいSQLを確認する

 emp_export_tab.sql

set echo off
set heading off
set termout off
set pause off
set pagesize 0
set linesize 1000
set trimspool on
set feedback off
spool ファイル名.txt

SELECT
id || '    ' ||
last_name || '    ' ||
first_name || '    ' ||
address
FROM emp;

spool off
;  

このようにid, last_name, first_name, addressくらいだと手入力が早いけど、何十個も列（フィールド）があったら大変。

 

■テーブルの列名を取得する

書き出すテーブル名が EMP の場合、

SQL> SELECT column_name
  2  FROM all_tab_columns
  3  WHERE UPPER(TABLE_NAME) = 'EMP' ;

 'emp'ではなく、'EMP'と入力すること。

 

 

■テキストエディタで加工する

ID
LAST_NAME
FIRST_NAME
ADDRESS

 このように出力された列名をコピーペーストでテキストエディタへ持っていく。

 

置換えコマンド等で、一括変換する。

viの場合、先頭（1）から最終行（$）までを対象に、

各業のお尻（$）を || 'タブ' || に置換える方法は以下のコマンドで実行。

: 1,$ s/$/ || ' ' ||/g 

 

すると、この通り。 

ID || '     ' ||
LAST_NAME || '     ' ||
FIRST_NAME || '     ' ||
ADDRESS  || '     ' ||

 

 

 ■さらに整形して完成

頭とお尻に情報を加えて完成。最後のフィールドのADDRESSには「TAB」が必要ないので消すのを忘れずに。

set echo off
set heading off
set termout off
set pause off
set pagesize 0
set linesize 1000
set trimspool on
set feedback off
spool ファイル名.txt

SELECT
ID || '     ' ||
LAST_NAME || '     ' ||
FIRST_NAME || '     ' ||
ADDRESS
FROM emp;

spool off
;   

Oracleで小文字のテーブル名の扱い

ある業者さんが、テーブル名、フィールド名を小文字で作ったため、SQL*Plusを使うときの注意をメモした。
テーブル名をactual_userとする。

■セレクト文の書き方

> SELECT "email" FROM "actual_user"

■SQL*Loader
idは、「順序」から発行する。
login_nameは、C00173547のようにCを頭に8桁の数字。
regist_timeは、時間付きの日付。
source_otherは、全件、CLを入力。
input_dateは、今日の日付。
とする。

LOAD DATA
中略
(
"id"    "?"actual_user_id_seq?".NEXTVAL"   ,
"login_name"    EXPRESSION "'C' || LPAD(to_char(test2_seq.NEXTVAL), 8, '0')"  ,
"email"   ,
"regist_time"   "to_date(:?"regist_time?", 'yyyy/mm/dd HH24:MI:SS')"  ,
"source_other"  CONSTANT "CL"  ,
"input_date"  SYSDATE
)


■データのエクスポート

$ exp scott/tiger tables='?"actual_user?"'

■改行コードを置換する
生データを用意したとき、改行コードをLFにするのを忘れて、CRLFにしていたため、CRがデータとしてインポートされてしまったので、一括で削除する。

> UPDATE "actual_user"
> SET "email" = REPLACE( "email", chr(13), '' )
> WHERE "email" like '%' || chr(13) ;

Oracleで表のコピーを作成する

副問い合わせを使用して、表のコピーを作成する。と同時に行の挿入を行う。つまり、テーブルをコピーすることになる。

 

■別の表の行を使用して表を作成する

 SQL> CREATE TABLE employee2  
2  AS   
3  SELECT * FROM employee ;

 

■別の表の行を使用して表を作成する

列を選択して表をつくる。

 SQL> CREATE TABLE employee2  
2  AS   
3    SELECT emp_id, emp_name 
4    FROM employee;
5    WHERE dept_id = 10 ; 

 

Oracleで連番

連続した番号を自動的に生成して主キーに使用する。

＜社員番号を自動生成＞
EMPテーブルに社員番号EMP_IDカラムがあるとする。

■順序の作成

EMP_ID_SEQという名前の順序（シーケンス）を作る。

CREATE SEQUENCE emp_id_seq
  START WITH 1
  INCREMENT BY 1
  NOCACHE ;

NOCHACHEを指定しないと20個の値がキャッシュされる。

■順序の確認 (user_sequences)

SELECT sequence_name, last_number
FROM user_sequences ;

last_numberは、次にメモリー上に取得される順序番号を表示する。

■順序（シーケンス）を使って社員番号にする

INSERT INTO emp (emp_id, ename)
VALUES  (emp_id_seq.NEXTVAL , '田中') ;

■単純に次の番号を見たいとき

SELECT emp_id_seq.NEXTVAL 
FROM dual ;

カウントアップされて、次にふられる番号が１大きくなるので注意。
ま、シリアル番号なんてものは重複しなければよい、というのが目的だから気にしなくていいんだけど。シリアル番号に意味を持たせてはいけないのだ。

■順序を作り直したいとき

動作をテストさせてOKとなって、再度1からスタートさせたいとき。
データをインポートする前に、10000からスタートさせたいときなど。

実は、「別の番号」からスタートさせるには、順序を削除して再作成する必要があるのだ。
ということで、

DROP SEQUENCE emp_id_seq;

再作成、

CREATE SEQUENCE emp_id_seq
  START WITH 10000
  INCREMENT BY 1
  NOCACHE ;


 

OracleのSQL*Plus便利もの

ちょっと便利なTips。SQLPlusは、奥が深いのであった。

 

■表示する列の長さを変更

> COLUMN [列名] FORMAT [A10]

■CSVファイル出力

set echo off
set heading off
set termout off
set pause off
set pagesize 0
set linesize 500
set feedback off
spool ＜パス／ファイル名＞
＜ここにSELECT文を記述。または、@hoge.sqlなどで実行する＞
spool off

■作業中に一時的にシェルを使う

SQL> !

!でシェルへ移動する。戻るときは、シェル上でexitする。

■前回実行したSQLを再実行する

SQL> /

■前回実行したSQLをファイルに保存する

SQL> LIST　（直前に実行したSQLのバッファを確認する）
SQL> SAVE emp.sql
SQL> SAVE emp.sql replace　（現在のバッファで置き換えるとき）
SQL> SAVE emp.sql　append　（現在のバッファを追加したいとき）
SQL> !ls　（!のあとにlsコマンドで保存されたかを確認する）

■保存したSQLを修正する

SQL> !vi emp.sql

■保存したSQLを実行する

SQL> @emp.sql
SQL> @tanaka/sql/emp.sql　（ディレクトリを指定するとき）

■ターミナルの画面サイズを大きくしたとき

SQL> SET lines 120　（横を120文字にセット）
SQL> SET pages 40　（高さを60文字にセット）
SQL> SET pause on　（1ページ毎に止めるとき）

■起動時にSQL文やSETコマンドを自動実行させる

シェルで、

$ORACLE_HOME/sqlplus/admin/glogin.sql

を変更する。変更前にバックアップしておく。

OracleのSQLコマンド

よく使うOracle8iのコマンドをメモ。Oracle10iでもほぼ同じ。

• Oracle SQL*Plus編
• Oracle 管理編
• Oracle SQL編
• Oracle エラー編

 

 

 

 

---




【SQL*Plus編】

---

 

■シェルからSQL/PLUSを使う


$ sqlplus /nolog

 

■ログインする

いくつかの方法がある。 

conn sys as sysdba
conn / as sysdba
conn sys/change_on_install
conn system/manager
conn scott/tiger
conn scott

データベース管理者ユーザー

• SYSユーザー：管理に重要なデータ・ディクショナリを所有している。
• SYSTEMユーザー：管理情報が含まれる表やビューを所有している。
セキュリティの理由からSYSユーザーとSYSTEMユーザーの初期パスワードは、早めに変更した方がよい。（passwordとコマンドを入力するだけで簡単に変更できる）

 

 

---




【Oracle管理編】

---

データベースを作成したら、表領域を確認して、ユーザーの作成を行う。

管理の作業は、管理者でログインして行う。

$ sqlplus /nolog
SQL> conn system/manager 　（Oracle 8i）
SQL> conn sys as sysdba　（Oracle 10g）

 

■表領域の確認

SQL> SELECT tablespace_name, status, contents FROM dba_tablespaces;

 

■表領域とデータ・ファイル情報の取得


SQL> SELECT file_name, tablespace_name FROM dba_data_files;

 

■ユーザーのデフォルト表領域


SQL> SELECT username, default_tablespace FROM dba_users;

 

■ユーザーの一覧

 SQL> SELECT username, default_tablespace FROM dba_users;

 

■ユーザー作成


CREATE USER suzuki     
  IDENTIFIED BY xxxxxx
 DEFAULT TABLESPACE users
 TEMPORARY TABLESPACE temp
 QUOTA unlimited ON users;

 

QUOTAを必ず付ける。QUOTA 15m ON usersなど。
後から変更もできる。（Oracle 8i）

ALTER USER suzuki QUOTA unlimited on temp;
ALTER USER suzuki QUOTA unlimited on rbs;

 

■ユーザー作成後に権限の付与（ロールを与える。ざっくりと指定。）


GRANT connect, resource TO suzuki;

 これを付与しないと、せっかくユーザを作ってもログインさえできない。

 

■ユーザー作成後に権限の付与（システム権限を与える。細かく指定。）

アプリケーション開発者向けの場合、

SQL> GRANT CREATE SESSION, CREATE TABLE, 
  2  CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE
  3  TO suzuki;

 

■権限の確認

 自分が持っている権限を確認できる。

SQL> SELECT * FROM session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE ANY TABLE
CREATE CLUSTER
CREATE ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
CREATE VIEW
CREATE ANY VIEW
CREATE SEQUENCE
CREATE DATABASE LINK

 

 

■ユーザーの削除


DROP USER suzuki;
 ユーザーの所有するスキーマ・オブジェクトも一緒に削除
 DROP USER suzuki CASCADE;

 注意：接続しているユーザーがいたらダメ。

 

■誰が何のプロファイルを使用しているか


SELECT username, profile
 FROM dba_users;

 

■新規にテーブルを作成する

CREATE TABLE emp
 (id      number,
 last_name  varchar2(100));

 

■既存のテーブルから新しいテーブルを作成する

部署の番号が20のものを拾って、新しいemp表を作る。

CREATE TABLE new_emp AS
SELECT * FROM emp
WHERE deptno = 20;

 

■テーブルを一覧する


SELECT * FROM tab;

よく使う。手クセになるコマンドのひとつ。

まず、これで手持ちのテーブルを一覧して、次に列の一覧でフィールド名を確認する。 

 

■列の一覧


DESC emp;

これも手クセになるはず。 

 

■列を追加する

ALTER TABLE emp
 ADD (first_name varchar2(10));

列の一覧で確認し、足りないフィールドがあったらこれで足していく。 

 

■列を削除する

ALTER TABLE emp
 DROP (first_name);

 

■列を変更する

ALTER TABLE emp
 MODIFY (first_name varchar2(30));

 

■一意キーを作成する

ALTER TABLE emp
 ADD CONSTRAINT id_pk PRIMARY KEY(id);

 

■順序（主キー値）の作成 

CREATE SEQUENCE emp_id_seq
 START WITH 1
 INCREMENT BY 1
 NOCACHE
 NOCYCLE;

 

■順序（主キー値）の確認


SELECT sequence_name, last_number
 FROM user_sequences;

 

 

---




【Oracle SQL編】

---




 ■日付で検索する

SELECT id, name FROM emp
WHERE birthday = TO_DATE( '1980/09/28' , 'YYYY/MM/DD' ) ; 




SELECT id, name FROM emp
WHERE TO_CHAR( birthday, 'YYYYMMDD' ) = '1980/09/28' ;

 

■期間で検索する

SELECT id, name FROM emp
WHERE birthday >= TO_DATE( '1980/09/28' , 'YYYY/MM/DD' ) 
AND birthday < TO_DATE( '1981/09/28' , 'YYYY/MM/DD' ) ;

 

 

■インサート文

INSERT INTO emp
(id, name)
VALUES
( 3, '高橋' ); 

 順序を使うとき、

INSERT INTO emp
(id, name)
VALUES
( emp_seq.nextval, '高橋' ); 

 

■データの修正

日付を一括変更。ついでにメモ欄を空にする。

UPDATE emp
SET 
modify_date = TO_DATE('2004/12/16', 'YYYY/MM/DD') , 
memo = NULL ; 

 

■データの修正

'Sales' を 'SALE' に変更。 

SQL> UPDATE emp
  2  SET 
  3  dept = 'SALE'
  4  WHERE dept = 'Sales'; 

 

■文字列を数値に変換

'00123'という文字列は、数字の123に変換したい。

SQL> UPDATE emp
  2  SET
  3  num_no = to_number( str_no );

 

■数値を文字列に変換 

数字の123を文字列の'123'に変換したい。

SQL> UPDATE emp
  2  SET
  3  str_no = to_char( num_no );

 

■行を削除する

 SQL> DELETE FROM emp
  2  WHERE id = 300;

 

 

 

 

---




【Oracle エラー編】

---




 ■インサート文でエラー

　　ORA-01552: SYSTEM表領域でない表領域:
 　　USERSにシステム・ロールバック・セグメントは使用できません。

原因として、ロールバックセグメントがオフラインになっている可能性がある。

SELECT segment_name, status
 FROM dba_rollback_segs;
  SEGMENT_NAME                   STATUS
 ------------------------------ ----------------
 SYSTEM                         ONLINE
 R01                            OFFLINE
 R02                            OFFLINE
 R03                            OFFLINE
 R04                            OFFLINE

STATUSがOFFLINEになっていたので、
/u01/app/oracle/product/8.1.5/dbs/initXXX.oraを編集。
# rollback_segments = (r01, r02, r03, r04)のコメントを外して有効にする。
そして、dbshutでデータベース停止、dbstartでデータベース開始する。

 

Oracleのバックアップスクリプト

いろいろなやり方があると思うが...とりあえずメモしておく。

 （未完成記事ですよ）

■backup_ORCL.sql

sqlを記述したファイルをviで作っておく。

set echo on
set feedback on
alter tablespace SYSAUX begin backup;
host cp -f "/u02/oradata/ORCL/sysaux01.dbf" /u02/orabackup/ORCL/hot_backup
host compress -f /u02/orabackup/ORCL/hot_backup/sysaux01.dbf"
alter tablespace SYSAUX end backup;
alter tablespace SYSTEM begin backup;
host cp -f "/u02/oradata/ORCL/systemORCL01.dbf" /u02/orabackup/ORCL/hot_backup
host compress -f /u02/orabackup/ORCL/hot_backup/systemORCL01.dbf"
alter tablespace SYSTEM end backup;
alter tablespace TOOLS begin backup;
host cp -f "/u02/oradata/ORCL/toolsORCL01.dbf" /u02/orabackup/ORCL/hot_backup
host compress -f /u02/orabackup/ORCL/hot_backup/toolsORCL01.dbf"
alter tablespace TOOLS end backup;
alter tablespace UNDOTBS begin backup;
host cp -f "/u02/oradata/ORCL/undoORCL01.dbf" /u02/orabackup/ORCL/hot_backup
host compress -f /u02/orabackup/ORCL/hot_backup/undoORCL01.dbf"
alter tablespace UNDOTBS end backup;
alter tablespace USERS begin backup;
host cp -f "/u02/oradata/ORCL/usersORCL01.dbf" /u02/orabackup/ORCL/hot_backup
host compress -f /u02/orabackup/ORCL/hot_backup/usersORCL01.dbf"
host cp -f "/u02/oradata/ORCL/usersORCL02.dbf" /u02/orabackup/ORCL/hot_backup
host compress -f /u02/orabackup/ORCL/hot_backup/usersORCL02.dbf"
host cp -f "/u02/oradata/ORCL/usersORCL03.dbf" /u02/orabackup/ORCL/hot_backup
host compress -f /u02/orabackup/ORCL/hot_backup/usersORCL03.dbf"
host cp -f "/u02/oradata/ORCL/usersORCL04.dbf" /u02/orabackup/ORCL/hot_backup
host compress -f /u02/orabackup/ORCL/hot_backup/usersORCL04.dbf"
alter tablespace USERS end backup;
alter database backup controlfile to '/u02/orabackup/ORCL/hot_backup/controlfile';
alter system archive log all;
alter system switch logfile;
exit 

 

 

 

Oracleの実行結果をPHPで表示する

 

WebページでSQLの結果を表示したいときがある。

 

 ■SELECT結果を表示する

EMPテーブルから、LAST_NAME列を取り出して一覧にする。

['LAST_NAME'] か ['last_name'] か、大文字・小文字をきちんと指定する。

select.php

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<meta http-equiv="content-type" content="text/html;charset=euc-jp">
<!--京-->
<title>SELECT文の出力</title>
</head>

<body>

<h3>テスト</h3>

<?php
        $con = oci_connect( 'ユーザ名', 'パスワード', 'ORCL' );
        $sql = 'SELECT last_name FROM emp';
        $state = oci_parse( $con, $sql );
        oci_execute( $state );
        while ( $rec = oci_fetch_array( $state ) ) {
                print $rec['LAST_NAME'] . "<br>\n" ;
        }
        oci_free_statement( $state );
        oci_close( $con );
?>

</body>
</html>

 

 

 

 ■OCI_RESULTを使用して、SELECT結果を表示する

EMPテーブルから、LAST_NAME列を取り出して一覧にする。

'LAST_NAME' か 'last_name' か、大文字・小文字をきちんと指定する。

select.php

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<meta http-equiv="content-type" content="text/html;charset=euc-jp">
<!--京-->
<title>SELECT文の出力</title>
</head>

<body>

<h3>テスト</h3>

<pre>
<?php
        $con = oci_connect( 'ユーザ名', 'パスワード', 'ORCL' );
        $sql = 'SELECT last_name FROM emp';
        $state = oci_parse( $con, $sql );
        oci_execute( $state );
        while ( oci_fetch( $state ) ) {
                echo oci_result( $state, 'LAST_NAME' ), "\n";
        }
        oci_free_statement( $state );
        oci_close( $con );
?>
</pre>

</body>
</html>

 

 

Oracleを削除、アンインストールする

Solaris 10 にインストールしたOracle10gを再インストールするために削除することにした。

 

1. データベースを削除する。

2. Oracleソフトウェアを削除する。

3. 使わないディレクリやファイルを削除する。

 

■データベースを削除する

X Windowが使える環境で、dbcaを起動する。

日本語で表示したいときは、LANGを指定しておく。 

$ LANG=ja
$ export ja
$ $ORACLE_HOME/bin/dbca

 

■Oracleソフトウェアを削除する

X Windowが使える環境で、Oracleインストーラを起動する。パッチのインストーラでもOKだった。

$ cd /tmp
$ /インストーラへのパス/runInstaller

 

■使わないディレクトリやファイルを削除する

次回のOracleインストールに備えて、いらないファイルを削除しておく。

$ORACLE_HOME/*を削除、またはリネームする。

$ cd /u01/app/オーナー名/
$ rm -rf *

 

/u01/oradata/SID名/* を削除する。

$ cd /u01/oradata/
$ rm -rf SID名
$ cd /u02/oradata/
$ rm -rf SID名 

 

コマンドを削除する。

# rm /usr/local/bin/dbhome
# rm /usr/local/bin/oraenv
# rm /usr/local/bin/coraenv 

 

/var/opt/oracleを削除、またはリネームする。

# cd /var/opt/
# rm -rf oracle 

 

.profile内のOracle関連の記述を削除する。必要に応じてリネームしてバックアップしておく。

$ cd
$ vi .profile

 

.profileの変更を有効にする。

$ source ./.profile 

 

/etc/init.d/dbora等の起動・終了に必要なファイルがを削除してする。

 

以上で、Oracleの削除は完了。

 

 

spfileを変更したら接続できなくなった

オペレーティング認証、conn / as sysdbaができない状態だったので、Oracleデータベース管理の教科書を参考に初期化パラメータを変更したら、connで接続できないし、startupもできない状態になってしまった。

元に戻す方法をメモしておいた。 

 

■初期化パラメータを変更した

初期化パラメータファイルのREMOTE_LOGIN_PASSWORDFILEパラメータを確認した。

SQL> show parameter remote_login_passwordfile

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile	     string	 EXCLUSIVE 

 

VALUEをEXCLUSIVEからNONEに変更してみた。

SQL> alter system set remote_login_passwordfile=none scope=spfile;

EXCLUSIVE：　GRANT文を使用して、SYS以外のユーザーをパスワードファイルに追加できる。

SHARED：　複数のインスタンスがアクセスできるパスワードファイルを使用できる。SYSユーザだけパスワードファイルに追加できる。

NONE：　パスワード・ファイルを無視する。特権ユーザはOSによって認証される必要がある。

 

 

そして、再起動するために一度、シャットダウンした。

SQL> shutdown immediate

 

■そしたら、ひどいことになった

SQL*Plusを一度終了し、再度接続してみたところ、エラーが発生した。

SQL> conn sys as sysdba
Enter password: 
Connected to an idle instance.

SQL> startup
ORA-01031: insufficient privileges 

 

 

■元に戻す

Oracleのサポセンに電話して、元に戻す方法を教えてもらった。

spfileの運用から、pfileでの運用に切り換える。

 

ます、spfileを作る。SQL*Plusで作業する。

SQL> conn sys as sysdba

SQL> create pfile from spfile;

File created.

 

spfileを捨てる。spfile + SID名 + .ora というファイル名をリネームすればよい。

$ cd $ORACLE_HOME/dbs
$ mv spfileorcl.ora spfileorcl.ora.OLD 

 

 

initorcl.oraを書き換える。init + SID名 + .ora というファイルを編集して、今回変更した初期化パラメータを元に戻す。

変更前
*.remote_login_passwordfile='NONE' 

変更後
*.remote_login_passwordfile='EXCLUSIVE' 

  

startupもできるようになった。めでたし。 

SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size		    2466304 bytes
Variable Size		  395861504 bytes
Database Buffers	 1207959552 bytes
Redo Buffers		    4325376 bytes
Database mounted.
Database opened. 

 

■pfile運用からspfile運用に戻す

SPFILE運用に戻したければ、spfileをもう一度作り直す。

SQL> create spfile from pfile;

 

 

SQL*Loader（sqlldr）でデータをまとめてインポート

データの移行など、たくさんあるデータをまとめてインポートするときに便利なのが、SQLローダー。

■データをテキストTABで用意

お客様データとして、customer.txtとして準備する。

■テーブルを作成

sqlplusなどで、テーブルを作成しておく。

■ロードする仕組みをシェルスクリプトで用意

入れ物（テーブル）と入れるもの（データ）が準備できたので、どうやっていれるか（制御ファイル）を用意する。

制御ファイルcustomer.ctlの中身

LOAD DATA
INFILE 'customer.txt'
TRUNCATE
INTO TABLE　customer
FIELDS TERMINATED BY X'09' trailing nullcols
(
ID,
CUSTOMER_NAME  ,
ZIP_CODE        ,
CUSTOMER_ADDRESS       ,
MODIFY_DATE "to_date(:MODIFY_DATE, 'yyyy.mm.dd')"    ,
MEMO
)

■実行

データのロード（インポート）を実行する。

$ sqlldr username control=customer.ctl skip=1

usernameは、実際にはOracleユーザ名を渡す。
1行目はタイトル行なので、スキップする。
制御ファイルとしてcustomer.ctlを使って、ロードを実行する。
sqlldrは、パスを通していない場合は、フルパス指定することを忘れずに。

データベースを削除して新規データベースを作成したら起動しなくなった

データベースを作り直す、というか削除してから新しいデータベースを作った。

DBCAを使ってデータベースorclを削除した。

DBCAを使ってデータベースiwd2を作成した。

するとインスタンスが起動しなくなってしまった。

 

最終的になおったが、試行錯誤したので、以下の順番はあくまで目安、ご参考までといったところ。 

 

■管理者でログインしてデータベースを起動する

SQL> conn / as sysdba

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/オーナー名/　次の行へ続く
product/10.2.0/dbs/initorcl.ora'

initorcl.oraが無いと言っている。DBCAを使ってデータベースorclを削除したので、init + SID名.oraも削除されたらしい。

 

■initorcl.oraを作る

init + SID名.ora ファイルは、$ORACLE_HOME/dbs/init.oraを元にして作ってあるらしい。

たまたま、バックアップ用の別のマシンにinitorcl.oraファイルがあったので、真似してviで作った。

$ORACLE_HOME/dbs/initorcl.ora

SPFILE=/export/home/u01/app/オーナー名/product/10.2.0/dbs/spfileorcl.ora

新しく作ったデータベースiwd2を削除したorclの代わりに使うので変更。

SPFILE=/export/home/u01/app/オーナー名/product/10.2.0/dbs/spfileiwd2.ora

 

■インスタンスを起動する

SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size		    2466304 bytes
Variable Size		  395861504 bytes
Database Buffers	 1207959552 bytes
Redo Buffers		    4325376 bytes
Database mounted.
Database opened. 

よかったぁ。

 

■$ORACLE_SIDを確認する

$HOME/.profileの$ORACLE_SIDを確認すると、orclのままだった。

データベースを作成する前にiwd2に変更しておけばよかったが...。しかたない。

$HOME/.profileの$ORACLE_SIDを新しいSID名に変更する。

.profile 

ORACLE_SID=iwd2
export ORACLE_SID

有効にして確認する。

$ source $HOME/.profile 
$ echo $ORACLE_SID
iwd2

 

■メモ（参考程度）

もうデタラメに作業してたので、とりあえずやったことや気づいたことをメモしておく。

順番や、必要性は全く無視してメモしてある。

 

新しいデータベース用にinit + SID名.ora ファイルを用意する。

$ cd $ORACLE_HOME/dbs/
$ cp -p initorcl.ora initiwd2.ora

 

 デフォルトの$ORACLE_SIDではない名称のpfileを利用する。

SQL> startup pfile=/u01/initxxxx.ora 

ただし、デフォルトSIDではないspfileは利用できないので、ファイル名を指定してspfileからpfileを作る。

SQL> create pfile='initiwd2.ora' from spfile='spfileorcl.ora' 

 

 

spfileからpfileを作る。

SQL> create spfile from pfile; 

 

prfileからspfileを作る。

SQL> create pfile from spfile;