Memo/Oracle

Oracle †

Oracleの起動/再起動 †

スクリプトを見ると、

• 停止
  

  lsnrctl stop
  dbshut

• 開始
  

  dbstart
  lsnrctl start

PC起動時に自動でOracleが起動するように設定 †

/etc/oratab~を編集し、末尾の'N'を'Y'に変更する。

orcl:/opt/app/oracle/product/10.1.0/db_1:Y

NVLの代わりにCOALESCEを使おう †

valがNULLの時、ゼロを返すならば、

nvl(val, 0)

ですが、Oracle固有の関数なので使わないほうが良いです。
同じ事は、COALESCE()関数でできます。

COALESCE(val, 0)

PostgreSQLでも使えるのでこちらの方が移植性が高くよいです。

SYSDATEはやめて、CURRENT_TIMESTAMPを使おう †

Oracle9i（R9.0.1）からANSI互換のCURRENT_TIMESTAMP関数がサポートされました。
これをSYSDATEの代わりに使うとSQLの互換性が向上します。
PostgreSQLでも使えます。

別ユーザーエクスポートしたデータをインポートする †

impの際、エラーが出る場合はsystemユーザーにて実行すると成功する場合がある。

imp system/パスワード fromuser=入力元のユーザー名 touser=入力先のユーザー名 file=ダンプファイル.dmp log=imp.log

EXP-00091: 不審な統計をエクスポートしています。 †

expコマンドを使用した際に、以下のエラーが出る場合がある。

EXP-00091: 不審な統計をエクスポートしています。

DBのcharasetがsjisだと出る模様。expのオプションで「STATISTICS=none」を付けると出なくなるが、DBのcharsetをEUC-JPにするのがよいだろう。

データのエクスポート †

expコマンドを用いる。

ユーザー単位でエクスポート

exp ユーザー名/パスワード@TNS名 file=出力先ファイル名 log=ログファイル名 STATISTICS=none INDEXES=n

データベース全てエクスポート(DBA権限が必要)

exp ユーザー名/パスワード@TNS名 file=出力先ファイル名 log=ログファイル名 STATISTICS=none INDEXES=Y FULL=Y

• Oracle データ オブジェクトの移行

SQL Analyzeを使用する †

SQL実行時のコストなどを計測できます。

1. ユーザーに必要な権限
   

   	CREATE USER scott
   	IDENTIFIED BY パスワード
   	DEFAULT TABLESPACE USERS
   	TEMPORARY TABLESPACE TEMP
   	PROFILE DEFAULT
   	/
   	GRANT CONNECT TO scott
   	/
   	GRANT RESOURCE TO scott
   	/
   	GRANT SELECT_CATALOG_ROLE TO scott
   	/
   	GRANT ANALYZE ANY TO scott
   	/
   	GRANT ANALYZE ANY DICTIONARY TO scott
   	/
   	GRANT EXECUTE ANY PROCEDURE TO scott
   	/
   	GRANT EXECUTE ANY TYPE TO scott
   	/
   	GRANT SELECT ANY DICTIONARY TO scott
   	/
   	GRANT SELECT ANY SEQUENCE TO scott
   	/
   	GRANT SELECT ANY TABLE TO scott
   	/
   	GRANT SELECT ANY TRANSACTION TO scott
   	/
   	GRANT UNLIMITED TABLESPACE TO scott
   	/

2. utlxplan.sqlを対象ユーザーで実行しておく。
   

   sqlplus scott/tiger @/oracle/product/10.1.0/db_1/rdbms/admin/utlxplan.sql

3. SI Object BrowerのSQL実行の「実行計画」タブにしてSQLを実行。

速度測定 †

sql*plusなら以下のコマンドで、SQLの経過時間を10ミリ秒('00:00:00.00')まで表示できる。

SET TIMING ON

Oracleの使い方

CSVファイルにエクスポート †

Oracleにはcsvエクスポートツールがないらしいので、sql*plusでエクスポートするようだ。

• 例：EMP表の特定項目をcsv出力
  
• 実行
  

  sqlplus scott/tiger @csv_export.sql

• csv_export.sql
  

  	set feedback       off
  	set heading        off
  	set verify         off
  	set feed           off
  	set pause          off
  	set echo           off
  	set termout        off
  	set pagesize       0
  	set linesize       9999
  	set trimspool      on
  
  	spool emp.csv;
  
  	select
  		EMPNO || ',' ||
  		ENAME || ',' ||
  		JOB   || ',' ||
  		DEPTNO
  	from
  		emp;
  	exit;

• 参考リンク
  
  • Works - 逆引きSQL比較
  • 技術情報
  • Windows用CSVインポート/エクスポートツール
    • Importer+

CSV等の固定長データのインポート †

Oracle標準のSQL*Loaderが使える。

以下の内容のテキストファイルをtest.ctl等の名前で作成し、

-- sql*loader (コマンド)
-- DBが稼働しているサーバー上で実行する(IPC接続なため)
-- sqlldr userid=ユーザー名/パスワード@TNS control=test.ctl log=log.txt
LOAD DATA
INFILE 'emp.csv'	       --対象ファイル名(相対パス可)
APPEND			 --追加モード
INTO TABLE emp		 --対象テーブル
FIELDS TERMINATED BY ','--カンマで区切り(タブ区切りは X'09')
TRAILING NULLCOLS	 --データの無い項目にNULLを挿入
(
	EMPNO           integer external,
	EMPNM           char,
	JOB             char,
	DEPTNO          integer external
)

sqlldrで実行する。

sqlldr userid=ユーザー名/パスワード control=test.ctl log=log.txt

• http://www.dotfoward.jp/DB/Oracle/Knowledge/SQLLoader/SQLLoader.html
  
• http://www.ne.jp/asahi/hishidama/home/tech/oracle/sqlldr.html
  

sshでポートフォワーディング †

大切なのは、接続先サーバのtnsnames.oraのSERVICE_NAMEと ローカルのtnsnames.oraに書いてあるSERVICE_NAMEを一致させること。

1. ローカルのtnsnames.oraに以下を追加

   vod =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 28800))
      )
      (CONNECT_DATA =
        (SERVICE_NAME = vod.idxvod.com)
      )
    )

1. VaraTermのポートフォワーディングツールの設定
   ローカルでlistenするポートは、空いている適当な番号を使う。
   

   SSHサーバ：	64.56.169.37
   アカウント:	oracle
   ローカルからリモートへのポートフォワーディング	チェック
   ローカルでlistenするポート：	28800
   転送先ホスト：	localhost
   転送先ポート：	1521

サービスの停止 †

自動的に開始されていたサービスを手動に変える

• OracleMTSRecoveryService
• OracleOraHome92Agent
• OracleOraHome92TNSListener
• OracleServiceOEMREP

忘れがちなSQL †

テーブルの列一覧を表示する †

desc テーブル名;

表を修正する †

新規列を追加する

alter table 表名 add (列名 データ型 default デフォルト値 制約)

表の列名、データ型などを変更する

alter table 表名 modify (列名, データ型, デフォルト値, 制約)

表の列を削除する

alter table 表名 drop column 列名

表の名前を変更する

rename 旧表名 to 新表名

表を高速に空にする †

delete from テーブル名では遅い。ロールバックしなくていいなら、

truncate table テーブル名;

ユーザーのパスワードを変更する †

alter user ユーザー名 identified by パスワード;

新規に作成したユーザーに、システム権限を与える †

grant システム権限 to ユーザー名;
--接続権限を与える
GRANT CONNECT TO ASD;
--リソースへのアクセス権限を与える
GRANT RESOURCE TO ASD;

ユーザーに対して、ロールを作成する †

create role ロール名;

ロールに対してセットする権限を決めるには、

grant システム権限 to ロール名;
grant オブジェクト権限 on オブジェクト名 to ロール名;

こうしておくと、新規ユーザーに権限を与える場合が楽になる。

grant role01 to 新規ユーザー名;

SQL †

rowidを使用せずに連番を発行する †

select
	ENAME,
	rownum as seq
from
	(select ENAME from EMP order by ENAME asc);

結果

テーブルの制約(Foreign key)を取得する †

select
  ucon.TABLE_NAME,substr(ucol.COLUMN_NAME,0,16) FOREIGN_KEY
from
  user_constraints ucon,user_cons_columns ucol
where
  ucon.CONSTRAINT_TYPE='R'
  and ucon.TABLE_NAME=ucol.TABLE_NAME
  and ucon.CONSTRAINT_NAME=ucol.CONSTRAINT_NAME
--   and ucon.TABLE_NAME=UPPER( 'テーブル名' ) -- テーブル名を指定する場合
order by
  ucon.table_name,ucol.COLUMN_NAME

テーブルの主キー(Primary key)名を取得する †

select
   ucon.TABLE_NAME,substr(ucol.COLUMN_NAME,0,16) PRIMARY_KEY
from
   user_constraints ucon,user_cons_columns ucol
where
   ucon.CONSTRAINT_TYPE='P'
   and ucon.TABLE_NAME=ucol.TABLE_NAME
   and ucon.CONSTRAINT_NAME=ucol.CONSTRAINT_NAME
--    and ucon.TABLE_NAME=UPPER( 'テーブル名' ) -- テーブル名を指定する場合
order by
   ucon.table_name,ucol.COLUMN_NAME

テーブルのカラム一覧を取得する †

select * from USER_TAB_COLUMNS where TABLE_NAME='テーブル名'

テーブルの一覧を取得する †

• 情報全て取得
  

  select * from user_tables;

• テーブル名のみ取得
  

  select table_name from user_tables;

• 壊れたテーブル(BIN$...)？を除外

  select table_name from user_tables where table_name not like 'BIN$%'

テーブル他、オブジェクト一覧の取得 †

インデックス一覧を表示する †

select * from user_ind_columns;

インデックスの詳細情報を表示する †

select * from user_indexes;

ビューの一覧を取得する †

select * from user_views;

ユーザーの一覧を取得する †

systemユーザーなどで実行する。

select username from dba_users;

付与されたロールを調べる †

systemユーザーなどで実行する。

select username, granted_role from user_role_privs;

ロールにセットされたシステム権限を調べる †

systemユーザーなどで実行する。

select role, privilege from role_sys_privs;

付与されたオブジェクト権限を調べる †

systemユーザーなどで実行する。

select table_name, privilege from user_tab_privs;

初期化パラメーター一覧を表示する †

systemユーザーなどで実行する。

select * from v$parameter2;

表領域情報を表示する †

select * from dba_data_files;

httpでファイルを取得する †

SELECT utl_http.request('http://www.oracle.com/') FROM dual;

結果の200件目〜300件目を得る †

SELECT * FROM <TABLE_NAME> WHERE ROWNUM <= 300
MINUS 
SELECT * FROM <TABLE_NAME> WHERE ROWNUM <= 200
ORDER BY ソート

主キーを得る †

SELECT 
	TABLE_NAME, -- テーブル名
	INDEX_NAME, -- インデックス名(主キー名)
	COLUMN_NAME, -- 項目名
	COLUMN_POSITION, -- 項目順番
	COLUMN_LENGTH -- 列長 
FROM 
	USER_IND_COLUMNS 
WHERE 
	TABLE_NAME = 'テーブル名' --大文字、小文字は区別される。 
	ORDER BY 
	TABLE_NAME,
	INDEX_NAME,
	COLUMN_POSITION

重複行の取り出し †

--emp表からJOBが重複している人を得る
SELECT * from emp t1, 
   ( SELECT 
       job, 
       count(*)
       cnt 
   FROM 
       emp 
   GROUP BY 
       job 
   ) t2 
WHERE 
   t2.cnt > 1 AND 
   t1.job = t2.job 
ORDER BY 
   t1.empno

キャスト †

rowid を varchar2にキャスト

select cast(rowid as varchar2(255)) as text from com_com_mstr

日付型から月だけを取り出す †

SELECT EXTRACT( MONTH FROM 日付項目 ) FROM 表;

MONTH のところは ( YEAR | MONTH | DAY | HOUR | MINUTE | SECOND TIMEZONE_HOUR | TIMEZONE_MINUTE)

同じく SQL92 標準では、実装依存精度の numeric を返せばいいということに なっているのですが、float8 がこの要件を満たしているのかどうかは分かり ません。float8 で使いにくいばあいは cast して

select *  from header where cast (extract(month from date) as int) = 1;

テーブル作成とデータ挿入を同時に行う †

create table 作成テーブル名 as select * from 挿入元テーブル名 where 条件

BLOBフィールドの画像を表示する †

http://www.microsoft.com/JAPAN/support/kb/articles/JP173/3/08.asp

セッション一覧を見る †

select sid,serial#,osuser from v$session

セッションを切る †

alter system kill session '&SID,&SERIAL#';

現在のログインしているスキーマのテーブル一覧をみる †

select table_name from user_tables;

ORA-01465: 16進数の指定が無効です。 †

ociexecute(): OCIStmtExecute: ORA-01465: 16進数の指定が無効です。

テーブル定義を確認して下さい。RAWフィールドに通常の方法でテキスト等を挿入しようとすると発生するようです。
　RAWモードで挿入するか、フィールド定義をVARCHAR2など適切に変更することで解決できます。

インストール時にテストが終わらなくて、自動で設定ファイルが作られないとき †

$ORACLE_HOME/network/ADMIN 以下に tnsnames.ora をコピーする。 内容は以下の通り

サービスの名前 =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = ホスト名)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = サービス名)
   )
 )

tnspingは通っても、接続できない時。 †

NT認証しに行っている場合があるので、$ORACLE_HOME/network/ADMIN の中の tnsnames.ora 以外の*.oraファイルをリネームもしくは削除する。

sqlplusの表示を改行させない †

select時、項目が多すぎると自動改行されてみにくい。

オプション->環境->オプション設定->wrap：オフ にすれば、自動改行されないので見やすくなる。 ただ、sqlplusを再起動すると設定が消される。

サービスを追加する †

Net8 Assistant スタート メニュー\プログラム\Oracle - OraHome81\Network Administration\Net8 Assistant

ユーザーの追加 †

Database Administration -> DBA studio

1. 表領域の作成
2. ユーザーの作成

listener.oraとtnsnames.oraの関係について †

ネットワーク接続の設定ファイルは $ORACLE_HOME/network/admin にあります。

• listener.oraはリスナー設定ファイル。あまり変更する事はありません。
  
• tnsnames.oraはクライアント側の設定ファイルです。接続先を追加するにはこちらを変更します。
  

で、クライアント側はサーバー側のtnsnames.oraを参考にSERVICE_NAMEを設定する必要があります。
以下の例で言えば、ORCLをサーバー、クライアント側ともに一致させる事が大切です。

サーバー側：tnsnames.ora

ORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = example.com)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl)
   )
 )

クライアント側：tnsnames.ora

ORCL =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = example.com)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = ORCL)
   )
 )

簡単なのはサーバー側のtnsnames.oraの対象箇所をコピー＆ペーストする事です。

PHP+Oracleで「oci8」関数が使えない場合の対処方法 †

Fatal error: Call to undefined function: ocilogon() in /var/www/html/CenterSystem/axDatabase.php on line 590

などと言うエラーがでる場合、httpdとの連携で、oci8がDSO版でインストールされている場合があります。
　phpinfo();などで、Configureオプションに「--without-oci8, --with-oci8=shared」が指定されている場合、DSOモードでインストールされています。
　この場合、httpd起動時にOracleの環境変数を参照する設定が必要です。
(/etc/sysconfig/httpd などに書いておく)
参考URL：PHP4.2.2＋Oracle8iのインストール（DSO版）

/etc/httpd/conf/httpd.conf の編集

	AddType application/x-httpd-php .php
	AddType application/x-httpd-php-source .phps
	DirectoryIndex index.html index.htm index.cgi index.php

/etc/sysconfig/httpd の編集

	#!/bin/sh
	export ORACLE_BASE=/opt/app/oracle
	export ORACLE_SID=orcl
	export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
	export NLS_LANG=Japanese_Japan.JA16EUC
	export PATH=$ORACLE_HOME/bin:$PATH
	export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

実行権限を与え、httpdサービスの再起動

	# chmod u+x /etc/sysconfig/httpd

環境変数はrestartでは反映されないので、注意

	# service httpd  stop
	# service httpd  start

sqlplusでサービス名で繋がらなくなった場合の対処方法 †

Oracle9iで接続する際に、SERVICENAMEが解決できませんでした。というエラーがでた場合の対処方法です。

• 現象(○：接続できる、×：接続できない)
  

  ○ sqlplus ユーザー名/パスワード
  × sqlplus ユーザー名/パスワード@サービス名

• 解決方法
  C:\oracle\ora92\network\admin にある*.oraファイルを全て削除し、「Net Configuration Assistant」でリスナー構成、ネーミング・メソッド構成、ローカル・ネット・サービス名構成を実行した所、接続可能になりました。
  

• 設定
  
  • Oracle9iをインストールしたPCのホスト名：username
  • デフォルトSID：OEMREP
    
  • listener.ora
    
  • tnsnames.ora
    
  • sqlnet.ora
    

サービス名でリスナープロセスに接続できるかを確認する †

tnsping サービス名

接続情報ファイル †

通常、以下のディレクトリに置かれる

C:\oracle\ora92\network\admin\tnsnames.ora

新環境にも接続設定を移行したい場合、設定済みのtnsnames.oraを上記の位置にコピーすれば繋がるようになる。

使用メモリ量の変更 †

Enterprise Manager Consoleで変更できる

プログラム -> Oracle - OraHome92 -> Enterprise Manager Console

接続モードをSYSDBAにして、対象データベースにログイン
インスタンス -> 構成 -> メモリータブ
で変更し、適応ボタンを押す。

コマンドで行うには †

sqlplus /nolog
SQL> connect sys/password as sysdba
SQL> alter system set db_cache_size = 3M;

等

コマンドからシステム設定を変更するには †

systemまたは、sysdbaでログインする。

参考リンク †

• 逆引きSQL比較
• オラクル9iインストール メモ(Linux)
  
• へなちょこLinux
  
• Oracleアーキテクチャ
  
• とってもOracle
• phpOraAdmin
  phpでOracleを管理。phpPgAdminの方が高機能。

Software

• 自作ソフト
• wiki自作プラグイン
• 利用規定
• ランキング
• 雑誌等掲載履歴

Programming

Game

雑記

連絡先

---

• MenuBar
• RightBar
• :admin
  

人気の10件

最新の10件

---