Memo/Oracle

https://dexlab.net:443/pukiwiki/index.php?Memo/Oracle
 

Oracle

Oracleの起動/再起動

起動/etc/init.d/dbora start
停止/etc/init.d/dbora stop
再起動/etc/init.d/dbora restart
状態確認/etc/init.d/dbora status

スクリプトを見ると、

  • 停止
    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

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;
  • 参考リンク

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

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

port_forward.gif

サービスの停止

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

  • 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);

結果

ENAMESEQ
ADAMS1
ALLEN2
BLAKE3

テーブルの制約(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$%'

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

TABLEselect * from user_tables
SEQUENCEselect * from user_sequences
VIEWselect * from user_views
PROCEDURESselect * from user_procedures

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

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」でリスナー構成、ネーミング・メソッド構成、ローカル・ネット・サービス名構成を実行した所、接続可能になりました。

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

tnsping サービス名

接続情報ファイル

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

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

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

使用メモリ量の変更

Enterprise Manager Consoleで変更できる

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

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

コマンドで行うには

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

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

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

SHOW PARAMETER設定を表示
SHOW PARAMETER CACHE_SIZECACHE_SIZE の詳細
alter system set db_cache_size = 3M;設定の変更

参考リンク


添付ファイル: fileport_forward.gif 933件 [詳細] filesqlnet.ora 1149件 [詳細] filetnsnames.ora 1238件 [詳細] fileoracle_ent_mngr.jpg 806件 [詳細] filelistener.ora 1148件 [詳細]

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2018-09-15 (土) 07:31:38 (91d)