Oracle
Oracle 表領域の縮小
参考
Oracle ブロンズ資格問題サイト
DB2 UDB：JDBC での READ COMMITTED分離レベルの解釈 / IBMサポート
Q2) SUN の JDBC の仕様では、http://java.sun.com/products/jdk/1.2/ja/docs/ja/api/java/sql/Connection.html より) public static final int TRANSACTION_READ_COMMITTED ダーティー読み取りは抑制されます。繰り返し不可の読み取りおよびファントム読み取りは起こります。このレベルは、コミットされていない変更がある行をトランザクションが読み取ることを禁止するだけです。 確かに Uncommit な更新行を読み込んでいないので違反してはいないのですが、 （ア）そこでロック待ちする （イ）ロック待ちせずに、更新前の値を読み込んで値を返す についてはどちらになるか、不明です。 このあたりの実装については各ドライバーを作成するベンダーに任されていると考えて良いのでしょうか？
A2) ご質問で書かれています通り、READ COMMITTED の実現方法は Vendor の Implementation に依存していると思います。SQL99標準規格[ISO-IEC 9075-02 SQL Part02 Foundation (SQL-Foundation)]でスペックを調べてみても、READ COMMITTED は以下の引用の様に、"Dirty read"を抑止すれば良いだけで、そのロジックは規定されていません。
DB2 と Oracle の違いは、実は READ COMMITTED の実現方法の違いでは無く、RDB としての根本的な設計思想の違いの現れだと考えられます。
Oracle は「未コミットの情報は見せない設計」になっているのに対して、DB2 は「パフォーマンスを重視した為に未コミットであっても更新後の情報を見せる設計」になっているからです。
oracle TEMP領域のリサイズ（拡張、縮小）
select NAME,BYTES from v$tempfile;
- サイズ変更
alter database tempfile '/data/TEMP01.dbf' resize 15M;
- 縮小できないとき、新TEMP追加+旧TEMP削除
２．TEMP表領域にファイルを一時的に追加します。
SQL> alter tablespace TEMP add tempfile '/data/TEMP02.dbf' size 10M;
３．現在の一時表領域に割当てているファイルをオフラインにします。
SQL> alter database tempfile '/data/TEMP01.dbf' offline;
４.古いTEMP表領域のファイルを削除
SQL> alter database tempfile '/data/TEMP01.dbf' drop including datafiles;
oracle データファイル移動（ファイル名変更）
- http://europe.ciao.jp/oracle/datafile_rename/index.html
- 1.DATABASEを normal shutdown します。
SQL> shutdown
データベースがクローズされました。
データベースがアンマウントされました。
ORACLEインスタンスがシャットダウンされました。
- 2.DATABASEを startup mount します。
SQL> startup mount
ORACLEインスタンスが起動しました。
データベースがマウントされました。
- 3.名前を変更するDATAFILEを変更先にコピーします。
SQL> !cp -p /data/data01.dbf /data/data02.dbf
- 4.oracleが管理するファイル名を変更します。
SQL> alter database rename file '/data/data01.dbf' to '/data/data02.dbf';
- 5.データベースを停止します。
SQL> shutdown
ORA-01109: ？？？？？？？？？？？？？？？？？？？
データベースがアンマウントされました。
ORACLEインスタンスがシャットダウンされました。
※ORA-01109がでますが、oracleのデータベースをopenしなかったためです。問題はありません。
- 6.データベースを起動します。
SQL> startup
ORACLEインスタンスが起動しました。
データベースがマウントされました。
データベースがオープンされました。
- 7.変更されたことを確認します。
SQL> select name from v$datafile where name like '%data02%';
NAME
----------------------------------------------------------------------
/data/data02.dbf
oracle UNDO領域のリサイズ（拡張、縮小）
オラクル・インスタンスを手動で起動、停止する / SHIFT the Oracle
# sqlplus /nolog
- インスタンスの段階的開始
SQL> CONN / AS SYSDBA
SQL> STARTUP [FORCE] [RESTRICT] [PFILE=filename] NOMOUNT
SQL> STARTUP MOUNT [dbname]
SQL> STARTUP OPEN [ READ {ONLY|WRITE [RECOVER]}|RECOVER] [dbname]
- インスタンスの開始
SQL> STARTUP
- インスタンスの4種類の終了
SQL> SHUTDOWN [NORMAL] SQL> SHUTDOWN TRANSACTIONAL [LOCAL] SQL> SHUTDOWN IMMEDIATE SQL> SHUTDOWN ABORT
Oracle Tips
ただの覚書です。間違っていたらすみません。
- 表の全データを読み取り統計情報を収集する
- コストベースの実行計画ができる
ANALYZE TABLE 表名 COMPUTE STATISTICS;
analyze table orderheader compute statistics; analyze table orderdetail compute statistics;
- 統計情報を削除し、ルールベースの実行計画に戻す
ANALYZE TABLE 表名 DELETE STATISTICS;
Oracle・Tips集
SQL# select * from tab;
- 表の項目一覧
SQL# desc 表名;（;は省略可）
- インデックスの一覧
SQL# select * from user_ind_columns;
- 指定した表のインデックスのみ表示
SQL# select column_name from user_ind_columns where table_name = '表名';
- ユーザ一覧
SQL# select username from user_users;
- データベースの文字コード
SQL# select * from NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_NCHAR_CHARACTERSET';
- 表のコピー方法
create table "新表名" as select * from "旧表名";
ユーザと表領域の削除方法
-- ユーザー削除 DROP USER USER01 [CASCADE];
CASCADE オプションを使用する事で、ユーザーの作成しているオブジェクトを一緒に削除してくれる。
インポートしても表領域が切り替わらない
- ユーザーのデフォルト表領域は変更済み
これは、ユーザーを作成する際にRESOURCE/DBAロールを付与した場合、RESOURCE/DBAロールに含まれる「UNLIMITED TABLESPACE」権限が効いてしまい、
ユーザーのデフォルト表領域ではなく元の表領域にテーブルが作成されるためです。
対策として、IMPORTする前にこの「UNLIMITED TABLESPACE」権限を取り消します。
「UNLIMITED TABLESPACE」権限を取り消すと、ユーザーはどの表領域にも表領域にも領域獲得ができなくなるためデフォルト表領域に対し領域獲得権（QUOTA）を与えます。
-- UNLIMITED TABLESPACE権限の取消 REVOKE UNLIMITED TABLESPACE FROM <username>; -- 領域獲得権（QUOTA）を付与 ALTER USER <username> QUOTA UNLIMITED ON <default_tablespace>;
- エキスポート・インポート
- 権限復帰
GRANT UNLIMITED TABLESPACE TO <username>;
表の新規コピー
- 高速
- DDLだから?
CREATE TABLE tablename_new TABLESPACE tablespace_new AS SELECT * FROM tablename@dblink;
特殊テーブル
| 名前 | 用途 |
|---|---|
| V$open_Cursor |
- セッション表示
select SID,SERIAL#,MACHINE,STATUS from v$session where MACHINE like 'ffnb2b%';
別セッションのTRACE開始
- SID,SERIAL# の取得
select SID,SERIAL#,MACHINE from v$session where MACHINE like '%SR02%';
- TRACE開始プロシージャの実行
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(68, 9848, FALSE);
statspack 領域の整理
- /oracle/product/9.2.0/rdbms/admin/sptrunc.sql
- /oracle/product/9.2.0/rdbms/admin/sppurge.sql
STATSPACKの使い方 / adgjm.net
LogMinerについて / OTN
・アーカイブが有効になっていることは確認した。
archive log list
・V$ARCHIVED_LOGには200件程度のレコードがあるが、nameはすべてnullになっていた。
SELECT NAME FROM V$ARCHIVED_LOG;
・サプリメンタル・ロギングは以下を実行した。
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
便宜的にNLS_DATE_FORMATを以下のように指定。
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Oracle のヒント句
OracleのアーカイブREDOログについて / @IT 会議室
RMAN の認識では、カタログ情報としては（OS コマンドで削除した）アーカイブは残っているつもりです。なので、カタログ情報を一旦現状を反映させてやれば対処できます。
RMAN> crosscheck archivelog all; RMAN> delete expired archivelog all;
Oracle アカウントロックの解除
アカウントロックされることがあります。
SYSユーザーなどで、下記のコマンドを実行することで解除できます。
alter user <ユーザー名> account unlock;
画面表示サイズを広げる /
SQLPLUSのデフォルトの画面表示サイズは半角８０文字です。
「SET LINESIZE」コマンドを使用して、画面の表示サイズを変更することが出来ます。
例１） 表示サイズを２００文字に変更します。
set linesize 200
SELECT結果で列名を表示させない場合、「set heading off」で非表示にすることができます。
Oracle Database 10g Release 2 インストレーションガイド Enterprise Linux5編
実行計画ができない
このデータベースのバージョンに該当する PLAN_TABLE は作成できません。
再試行する前に ultxplan.sql 管理スクリプトを使用して PLAN_TABLE を作成してください。
ORA-28674
ORA-28674: cannot reference transient index-organized table
- 10.2
SQL> alter system set recyclebin=off scope=both;
- 10.1
SQL> alter system set "_recyclebin"=false scope=both; System altered.
recyclebin
- show recyc
- purge recyclebin;
Oracle マスター
UNDO 領域不足
ORA-30036: 8192(UNDO表領域'UNDO1'内)でセグメントを拡張できません
- 一時しのぎで、UNDO保持期間を短縮して処理後に戻す
- UNDO_RETENTION 現在値の習得
sqlplus> show parameter undo_retention; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ undo_retention integer 10800
- UNDO_RETENTION の一時設定
sqlplus> alter system set undo_retention=60; システムが変更されました。
- UNDO_RETENTION の復帰
sqlplus> alter system set undo_retention=10800;
sqlplus でのIP指定
- How to connect SQLPlus without tnsnames.ora
- http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:45033135081903
sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora9ir2.kyte.com)))'
Sqlnet.oraとORA-12541 TNS:リスナーがありません の関係 / nikki
Net8でつながりつつ、Sqlplusで、ORA-12541 TNS:リスナーがありません としかられてしまう。。。
http://www.nihon-eng.co.jp/c-break/TechNote/tips/TIPS_Main.htm
↑そんなときは、Sqlnet.oraにTNSNAMESを追加してやるといいらしい。
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
SQLの実行時間を表示する / 忘れっぽいエンジニアのオラクルSQLリファレンス
ＳＱＬ単位で、時間を計る場合は、SET TIMINGコマンドで計ります。
複数のＳＱＬを纏めて実行した時間を計る場合は、TIMINGコマンドを使います。
SET TIMING ON
SET TIMING OFF
ユニークキーの追加
どのユーザがどれだけ表領域を使っているか調べる SQL スクリプト / ablog
- yoheia/show_segment_size_summary.sql at master ? yoheia/yoheia ? GitHub
- https://github.com/yoheia/yoheia/blob/master/oracle/sql/show_segment_size_summary.sql
set pagesize 1000 set linesize 300 col tablespace_name for a20 col owner for a10 col segment_cnt for 999999 col blocks for 99999999999 col kb for 999999
select sg.tablespace_name, sg.owner, sg.segment_type, count(segment_name) segment_cnt, sum(blocks) blocks, round(sum(bytes)/1024/1024) mb from dba_segments sg, dba_tablespaces ts where sg.tablespace_name = ts.tablespace_name group by sg.tablespace_name, sg.owner, sg.segment_type order by sg.tablespace_name, sg.owner, sg.segment_type;
TODO
プライマリキーの切替え
- 複数フィールドでプライマリキーを利用しているヤツを単一フィールドに切り替える