きのふよりけふ、けふよりあした

Oracle 11g では掲題のレガシーエクスポート・インポート（imp と exp コマンド）ではなく、データポンプを使用するバックアップ取得方法が推奨されています。
ですが、この記事はレガシーエクスポート・インポートについて述べます。

○imp コマンドはテーブルも作成する

既にテーブルが存在する場合は、-ignore=y にしないとデータも全く作成されない。
なお、imp コマンドでは「テーブル作成」「インデックス作成」「データ作成」が実行される。
ダンプファイルもこれらSQL情報を保持したバイナリファイルである。

○ドメインインデックスが既に存在する場合（テーブルもインデックスもテキストも）

テーブルにデータをインポートしつつインデックスとテキストを再構築しようとしてエラーとなることがある模様。
そのため、-index=n としてインデックスは後で作成することでエラーに対応する。

○ドメインインデックスが存在しない場合（テーブルもインデックスもテキストも）

テーブルを作成、インデックスとテキストを作成しようとしてエラーとなることがある模様。
そのため、-index=n としてインデックスは後で作成することでエラーに対応する。

兎に角、Oracleテキストとドメインインデックスが絡む場合はレガシーインポートではエラーとなってしまうのでインデックスを後で作成することが必要なようです。

○imp、expコマンドでヘルプを見る

exp HELP=y
imp HELP=y

○imp、expコマンドの実行モジュール格納先（Linuxデフォルト）

/u01/app/oracle/product/11.1.0/client_1/bin/exp
/u01/app/oracle/product/11.1.0/client_1/bin/imp

export ORACLE_BASE="/u01/app/oracle/"
export ORACLE_HOME="/u01/app/oracle/product/11.1.0/client_1/"

---

Oracleデータベース内で DATE型で定義されたカラムはデフォルトでは「YYYY/MM/DD」として表示されます。

これを「YYYY/MM/DD HH:MM:SS」のように表示できないか、というと答えは出来ます。

SELCT TO_CHAR( カラム名もしくは SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM テーブル名

カラム名には、DATE型のカラム名、TIMESTAMP型のカラム名を指定できます。

---

自分で書いた以下の記事があまりにも使えないので、内容を編集しなおしました。

http://isann.blog2.fc2.com/blog-entry-91.html
http://isann.blog2.fc2.com/blog-entry-98.html


だいたい下記のような手順をする場合参考になるよう編集しました。
１．使えそうな表領域を探す
２．表領域を作成する
３．ユーザを作成する（削除する）
４．権限を確認する（自ユーザ、他ユーザ）
５．権限を付与する
６．表領域をあとで弄る

■表領域の確認

表領域として使用できる表領域名を検索する。

SELECT TABLESPACE_NAME, CONTENTS FROM DBA_TABLESPACES;

※PERMANENT は永続表領域、UNDO はUNDO表領域、TEMPORARY は一時表領域である。

■データファイル確認
○データファイルのうち、永続表領域やUNDO表領域のデータファイルをまず検索。

SELECT F.TABLESPACE_NAME, F.FILE_NAME 
FROM DBA_TABLESPACES T, DBA_DATA_FILES F
WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME;

○データファイルのうち、一時表領域のデータファイルを検索。

SELECT F.TABLESPACE_NAME, F.FILE_NAME 
FROM DBA_TABLESPACES T, DBA_TEMP_FILES F
WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME;

■表領域作成

使用できそうな表領域がない場合、作成する必要があります。

CREATE TABLESPACE  表領域名 DATAFILE 'ファイル名（フルパス）' SIZE ファイルサイズ;

　CREATE TABLESPACE 表領域名
　　DATAFILE 'ファイルパス'
　　SIZE 初期サイズ
　　AUTOEXTEND ON NEXT 拡張サイズ MAXSIZE 上限サイズ

　上記のパラメータの意味は、以下の通り。
　　TABLESPACE　…　表領域の名称を指定
　　DATAFILE　…　物理ファイル名（ファイルパス）
　　SIZE　…　表領域の初期サイズ
　　AUTOEXTEND ON NEXT　…　表領域不足時の拡張サイズ
　　MAXSIZE　…　拡張上限サイズ
　　
　※サイズにUMLIMITEDを指定した場合には、上限は無制限となる。
　　拡張サイズの上限は、記憶媒体の容量に依存する。
　　例えば、100GBのハードディスクを利用している場合の最大サイズは100GBとなる。

参考URL：http://blog.livedoor.jp/akf0/archives/51149678.html
http://www2.atwiki.jp/chobi/pages/20.html

■ユーザ作成SQL

実際に追加するユーザを作成する。

CREATE USER #{USER_NAME} 
IDENTIFIED BY #{PASSWORD} 
DEFAULT TABLESPACE #{永続表領域名} 
TEMPORARY TABLESPACE #{一時表領域名};

■ユーザ削除

間違えてしまった場合などにユーザを削除する。

drop user ユーザ名;

※ユーザを他のクライアントが使用しており、ユーザ削除がエラーになる場合はセッションを確認するため以下。

参考URL：http://oracle.se-free.com/dic/E1_session.html

select
sid,serial#,username,osuser,program,machine,terminal
from v$session
where type = 'USER'; -- ユーザープロセス 

■権限の確認

他ユーザの権限を確認し、同じような権限を付加する。（だいたい周りに合わせるため）

SELECT * FROM user_sys_privs;
-- または
SELECT * FROM session_privs;
-- または
SELECT * FROM dba_sys_privs;

ユーザーに与えられている権限を確認するには、 USER_SYS_PRIVSまたはSESSION_PRIVSまたはDBA_SYS_PRIVSに問い合わせます。
USER_SYS_PRIVS ではデータベースに接続しているユーザー自身に直接付与された権限、
SESSION_PRIVS ではセッションで使用可能な権限（ロール経由で付与されたものを含む）、
DBA_SYS_PRIVS ではすべてのユーザーの情報を確認するができます。
DBA_SYS_PRIVS にアクセスするには SELECT ANY TABLE権限が必要です。
この権限は、デフォルトでDBAロールに割り当てられます。

参考URL：http://www.atmarkit.co.jp/fdb/ref/ref_oracle/privi.html


■権限付与

追加したユーザに権限を付与していく。
その際、オブジェクト権限なのかシステム権限なのかで付与の仕方が異なる。

○オブジェクト権限

GRANT ※オブジェクト権限 ON テーブル名 TO ユーザ名; 

※オブジェクト権限
    DML 関連

        * INSERT
        * SELECT
        * UPDATE
        * DELETE

    DDL 関連

        * ALTER (ALTER TABLE)
        * DEBUG (デバッガ経由のトリガーなどへのアクセス）
        * INDEX (CREATE INDEX)
        * REFERENCES (表参照制約 の作成）

    以上すべて

        * ALL

○システム権限

GRANT ※※システム権限 TO ユーザ名; 

※※システム権限
テーブルのシステム権限一覧
参考URL：http://www.shift-the-oracle.com/privilege/system-privilege-list.html

        * INSERT ANY
        * SELECT ANY
        * UPDATE ANY
        * DELETE ANY
        * FLASHBACK ANY
        * LOCK ANY

        * CREATE
        * CREATE ANY
        * ALTER ANY
        * BACKUP ANY
        * DROP ANY


参考URL：http://www.shift-the-oracle.com/privilege/

■ユーザのデフォルト表領域割り当て制限

ユーザに割り当てた表領域において、そのユーザがどこまで表領域を使用してもよいかを設定できる。
以下はその設定を無制限（表領域の最大サイズまで）割り当てる方法。

参考URL：http://noro2-diary.at.webry.info/200808/article_28.html

alter user スキーマ名
default tablespace テーブルスペース名  --さっき作った表領域とか指定。
quota unlimited on テーブルスペース名; --さっき作った表領域とか指定。

■表領域にデータファイルを追加する

表領域のデータファイル容量が不足してきた場合、その表領域に割り当てられているデータファイルを追加することで回避できる。
その場合のSQLは下記となる。

ALTER TABLESPACE テーブルスペース名 
ADD DATAFILE データファイルパス --'/oracle/SFO/user1_3.dbf' 
SIZE サイズ;

参考URL：http://www.atmarkit.co.jp/flinux/rensai/oracle04/oracle04.html

■表領域を自動拡張に変更する

表領域をあとで自動拡張に変更する場合は以下。

ALTER DATABASE DATAFILE 'ファイル名'
AUTOEXTEND ON [NEXT サイズ [K | M]]
MAXSIZE {UNLIMITED | サイズ [K | M]};

参考URL：http://www2.atwiki.jp/chobi/pages/20.html

---

sql*plus プロセスにて Oracleデータベースにアクセスした際、
@SQLファイル名 で実行するための SQLファイル を探す＆ディレクトリを移動するなどといった場合に、
sql*plus 上で cd ディレクトリ などとしてもコマンドを実行できない。

ところが、下記のようにすることで可能。

host <OS コマンド>

ディレクトリ移動でもファイルコピーでもファイル検索でもプロセス確認でも、OS のコマンドを実行できる。

知らなかったので今度使ってみようと思います。

---

テーブルのインデックスやらデータを格納する表領域を変更する方法メモ

alter table hogehoge move tablespace tablespacenamehoge

それだけでした。
なお、インデックスは再作成が必要？だと思うので念のためリビルドしておいた。

---