Oracle Tips

キャッシュ・ヒット率（70%を下回る場合、バッファ数を増やす必要あり）
SELECT substr(to_char(( 1-(C.VALUE/(A.VALUE+B.VALUE)) ) * 100),1,5)
|| ' %' "Cache Hit Ratio"
FROM V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C
WHERE A.NAME = 'db block gets' AND
B.NAME = 'consistent gets' AND
C.NAME = 'physical reads';


バージョン
SELECT * FROM v$version;

テーブルスペース
SELECT tablespace_name,status FROM dba_tablespaces;

テーブルスペースの空き領域とサイズ
SELECT tablespace_name　"表領域名", SUM(bytes)/1024/1024 "空き領域(MB)"
FROM dba_free_space GROUP BY tablespace_name;
SELECT tablespace_name　"表領域名", SUM(bytes)/1024/1024 "サイズ(MB)"
FROM dba_data_files GROUP BY tablespace_name;

現在接続中のユーザ
SELECT substr(username,1,10),substr(machine,1,15),substr(module,1,10) FROM v$session
WHERE username IS NOT NULL;
SELECT substr(username,1,10),substr(machine,1,15),substr(module,1,10),TYPE FROM v$session
WHERE username IS NOT NULL;
SELECT username,machine,module,TYPE FROM v$session
WHERE username IS NOT NULL;

現在接続中のユーザ、開始時間、ステータス
SELECT substr(username,1,15),to_char(LOGON_TIME,'yyyymmdd hh24:mi:ss'),STATUS
FROM v$session
WHERE username IS NOT NULL;
SELECT substr(username,1,15),to_char(LOGON_TIME,'yyyymmdd hh24:mi:ss'),STATUS
FROM v$session
WHERE username IS NOT NULL and MACHINE = 'SEISICSTEST01';

現在接続中のセッション数
SELECT count(*) FROM v$session
WHERE username IS NOT NULL;
SELECT count(*) FROM v$session
WHERE username IS NOT NULL and MACHINE = 'CSSPC01';

自分は誰？
SHOW USER

初期化パラメータの表示
SHOW PARAMETER

列の詳細
DESC オブジェクト名

表領域の使用量
SELECT segment_name, sum(bytes) bytes FROM user_segments GROUP BY segment_name;

主キー
SELECT a.table_name "テーブル名" ,a.constraint_name "主キー名",a.column_name "列名"
FROM user_cons_columns a,user_constraints b
WHERE a.constraint_name=b.constraint_name AND b.constraint_type='P';

テーブルの使用サイズ
SELECT substr(SEGMENT_NAME,15) "テーブル名", SUM(BYTES) BYTE
FROM USER_SEGMENTS
GROUP BY SEGMENT_NAME;

OPEN_CURSORS パラメータ(INIT.ORA)の値を確認
show parameter open_cursors;

現在の OPEN_CURSORS 数
SELECT count(*) FROM v$open_cursor o, v$session s
WHERE o.sid=s.sid ;

指定ユーザの OPEN_CURSORS 数
SELECT O.SID, osuser, machine, count(*) num_curs
FROM v$open_cursor o, v$session s
WHERE o.sid=s.sid and user_name = 'ユーザ名'
GROUP BY o.sid, osuser, machine
ORDER BY num_curs desc;

グローバル名取得
SELECT * FROM global_name;

コメント（日本語）でテーブル検索
SELECT table_name,comments FROM user_tab_comments
WHERE comments like '%コメント%';



セッションを終了させるには
SELECT substr(username,1,15),to_char(LOGON_TIME,'yyyymmdd hh24:mi:ss'),STATUS,sid,serial#
FROM v$session
WHERE username IS NOT NULL and MACHINE = 'CSSPC01';
SELECT sid,serial#,username,osuser,program,machine FROM v$session;
ALTER SYSTEM KILL SESSION 'sid,serial#';

ORACLEのTABLE削除[10g以降の場合]
DROP TABLE ***** PURGE;
PURGE RECYCLEBIN;



テーブルスペースにデータファイルを追加しサイズを拡張
ALTER TABLESPACE tablespace_name ADD DATAFILE filespec SIZE nnm;

テーブルスペースの既存のデータファイルを拡張
ALTER TABLESPACE tablespace_name DATAFILE filespec RESIZE nnm;



西暦→和暦
SELECT JPDATE(SYSDATE) as 本日 FROM dual ;

曜日の表示
SELECT to_char(sysdate,'D') FROM dual; -- 数字
SELECT to_char(sysdate,'DY') FROM dual; -- 一文字
SELECT to_char(sysdate,'DAY') FROM dual; -- フル文字

日時表示
SELECT to_char(systimestamp,'YYYY/MM/DD HH24:MI:SS.FF3') FROM DUAL;

DBMS_OUTPUT.PUT_LINEの出力
SET SERVEROUTPUT ON;

ストアドのソースを表示する
SELECT text FROM dba_source
WHERE name = upper('プログラム名') AND owner = upper('所有者')
ORDER BY line;
--または
SELECT text FROM user_source
WHERE name = upper('プログラム名') ORDER BY line;

ストアドの実行
SELECT プログラム名 FROM DUAL;



SQL*Plus内容をファイル出力
SPOOL ファイル名
SPOOL OFF

ファイル内容をCSV化
SET COLSEP ","

A-TABLE → B-TABLE にコピー
INSERT INTO A-TABLE SELECT * FROM B-TABLE;

A-TABLE → B-TABLE に構造とデータをコピー
CREATE TABLE B-TABLE AS SELECT * FROM B-TABLE;

A-TABLE → B-TABLE に構造のみをコピー
CREATE TABLE B-TABLE AS SELECT * FROM B-TABLE WHERE 1=2;