* [[Oracleメモ]] - SQLメモ [#q8adc448]
#setlinebreak(on);
【DDLを取得する】
SELECT DBMS_METADATA.GET_DDL('TABLE', 'テーブル名') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('VIEW', 'ビュー名') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX', '索引名') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'トリガー名') FROM DUAL;
など
【テーブルの列定義を確認する】
SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME = 'テーブル名';
【テーブルについている索引を確認する】
SELECT * FROM USER_INDEXES_COLUMNS WHERE TABLE_NAME = 'テーブル名';
SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'テーブル名' ORDER BY COLUMN_POSITION;
【文字コードから文字を取得】
SELECT CHR(13) FROM DUAL; -- CR取得
SELECT CHR(10) FROM DUAL; -- LF取得
SELECT CHR(13) || CHR(10) FROM DUAL; -- CRLF取得
/* UTL_FILEで PUTLINE でなく PUT を使用して改行コードを出力する場合は注意が必要！
UTL_FILEはプラットフォームに依存する改行を出力するための文字としてLFを使用する様子。
ファイルをRAWモードで開いていない限り、必ず末尾に改行が付いてしまうらしい。。
*/
【表領域のフルパスと自動拡張の設定値の確認】
SELECT TABLESPACE_NAME,
FILE_NAME,
AUTOEXTENSIBLE
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '表領域名';
【表領域の空き容量の確認】
SELECT SUM(BYTES) / 1024 || 'Kbytes' AS 空き領域サイズ
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = '表領域名';
【連続する空き領域の結合】
ALTER TABLESPACE APP COALESCE;
【インデックス再構築】
ALTER INDEX インデックス名 REBUILD ONLINE;
【インデックス再構築方法（別のテーブルスペースに作成）】
ALTER INDEX インデックス名 REBUILD TABLESPACE テーブルスペース名;
【データベース・バッファ・キャッシュを取得する】
SET SERVEROUTPUT ON;
DECLARE
d_gets NUMBER;
c_gets NUMBER;
p_reads NUMBER;
result NUMBER;
BEGIN
SELECT VALUE INTO d_gets FROM V$SYSSTAT
WHERE NAME = 'db block gets';
SELECT VALUE INTO c_gets FROM V$SYSSTAT
WHERE NAME = 'consistent gets';
SELECT VALUE INTO p_reads FROM V$SYSSTAT
WHERE NAME = 'physical reads';
result :=
ROUND((1 - (p_reads / (c_gets + d_gets))),3) * 100;
DBMS_OUTPUT.PUT_LINE(
'データベース・バッファキャッシュヒット率->' ||
result || '%');
END;
/
※一般的に、このヒット率が90％以上を維持できるようなデータベース・バッファ・キャッシュ・サイズを
作成すべきとされている。
※ヒット率が90％に満たない場合は、初期化パラメータファイルのデータベース・バッファ・キャッシュのサイズを変更する。
【バッファ・キャッシュの空き比率を調べる】
SET SERVEROUTPUT ON;
DECLARE
cnt_total NUMBER;
cnt_free NUMBER;
result NUMBER;
message VARCHAR2(256);
BEGIN
SELECT COUNT(*) INTO cnt_total FROM X$BH;
SELECT COUNT(*) INTO cnt_free FROM X$BH
WHERE STATE = 0;
result := ROUND((cnt_free / cnt_total), 3) * 100;
SELECT DECODE(SIGN(result), 1,
'バッファキャッシュ余裕有り',
'バッファキャッシュ余裕なし') INTO message
FROM DUAL;
DBMS_OUTPUT.PUT_LINE('バッファ空き比率->' ||
result || '%');
DBMS_OUTPUT.PUT_LINE(message);
END;
/
【共有プールのメモリサイズ情報を取得】
SET SERVEROUTPUT ON;
DECLARE
sum_db_object_cache NUMBER;
sum_s_sqlarea NUMBER;
sum_u_sqlarea NUMBER;
now_parameter NUMBER;
result NUMBER;
BEGIN
SELECT SUM(SHARABLE_MEM) INTO sum_db_object_cache
FROM V$DB_OBJECT_CACHE;
SELECT SUM(SHARABLE_MEM) INTO sum_s_sqlarea
FROM V$SQLAREA;
SELECT SUM(250 * USERS_OPENING) INTO sum_u_sqlarea
FROM V$SQLAREA;
result :=
sum_db_object_cache + sum_s_sqlarea + sum_u_sqlarea;
SELECT value INTO now_parameter
FROM V$PARAMETER WHERE NAME = 'shared_pool_size';
DBMS_OUTPUT.PUT_LINE(
'パラメータSHARED_POOL_SIZE->' || now_parameter);
DBMS_OUTPUT.PUT_LINE(
'現在最低限必要なSHARED_POOL_SIZE->' || result);
DBMS_OUTPUT.PUT_LINE(
'適正なSHARED_POOL_SIZE(×1.3)->' || result * 1.3);
END;
/
【ライブラリ・キャッシュ・ヒット率取得】
SELECT SUM(PINS) AS キャッシュヒット合計,
SUM(RELOADS) AS キャッシュミス合計,
ROUND((1 - SUM(RELOADS) / SUM(PINS)) * 100, 2) || '%' AS ライブラリキャッシュヒット率
FROM V$LIBRARYCACHE;
【ディクショナリヒット率の算出】
SELECT SUM(GETS) AS キャッシュヒット合計,
SUM(GETMISSES) AS キャッシュミス合計,
ROUND((1 - SUM(GETMISSES) / SUM(GETS)) * 100, 2) || '%' AS ディクショナリヒット率
FROM V$ROWCACHE;
※ヒット率は95％以上が望ましいとされています。
【初期化パラメータを参照する】
SELECT NAME, VALUE FROM V$PARAMETER ORDER by NAME;
【DBの文字コードを調べる】
select dump(col_name,1016) from table_name where rownum <= 1;
/* col_name、table_nameは存在するものであれば何でもいい。
col_nameはもちろん全角文字が入ってる列がよい。と思う。*/
【参照制約設定用のSQLを作るSQL】
SET DEFINE ON
SET LINESIZE 200
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
USER_NAME VARCHAR2(20) DEFAULT 'ユーザID';
CURSOR DISABLE_CSR IS
SELECT 'ALTER TABLE ' || A.TABLE_NAME ||
' DISABLE CONSTRAINT "' ||
A.CONSTRAINT_NAME || '";' "SQLTXT"
FROM ALL_CONSTRAINTS A,
ALL_CONSTRAINTS B
WHERE B.TABLE_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = USER_NAME)
AND A.CONSTRAINT_TYPE = 'R'
AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
UNION
SELECT 'ALTER TABLE ' || C.TABLE_NAME ||
' DISABLE CONSTRAINT "' ||
C.CONSTRAINT_NAME || '";'
FROM ALL_CONSTRAINTS C
WHERE C.TABLE_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = USER_NAME)
AND C.CONSTRAINT_TYPE = 'R'
ORDER BY 1 ASC;
CURSOR ENABLE_CSR IS
SELECT 'ALTER TABLE ' || A.TABLE_NAME ||
' ENABLE VALIDATE CONSTRAINT "' ||
A.CONSTRAINT_NAME || '";' "SQLTXT"
FROM ALL_CONSTRAINTS A,
ALL_CONSTRAINTS B
WHERE B.TABLE_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = USER_NAME)
AND A.CONSTRAINT_TYPE = 'R'
AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
UNION
SELECT 'ALTER TABLE ' || C.TABLE_NAME ||
' ENABLE VALIDATE CONSTRAINT "' ||
C.CONSTRAINT_NAME || '";'
FROM ALL_CONSTRAINTS C
WHERE C.TABLE_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = USER_NAME)
AND C.CONSTRAINT_TYPE = 'R'
ORDER BY 1 ASC;
BEGIN
DBMS_OUTPUT.PUT_LINE('-- 参照制約無効化用ＳＱＬ -- ');
FOR DISABLE_SQL IN DISABLE_CSR LOOP
DBMS_OUTPUT.PUT_LINE(DISABLE_SQL."SQLTXT");
END LOOP;
DBMS_OUTPUT.PUT_LINE('-- 参照制約復活用ＳＱＬ -- ');
FOR ENABLE_SQL IN ENABLE_CSR LOOP
DBMS_OUTPUT.PUT_LINE(ENABLE_SQL."SQLTXT");
END LOOP;
END;
/
【ユーザ権限の確認】
select 'USER_ROLE_PRIVS' "PRIVILEGES",GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE,OS_GRANTED from USER_ROLE_PRIVS;
select 'USER_SYS_PRIVS' "PRIVILEGES",PRIVILEGE,ADMIN_OPTION from USER_SYS_PRIVS;
select OWNER || ' の ' || TABLE_NAME || ' を ' || PRIVILEGE || ' 可能' from USER_TAB_PRIVS where USER not like 'SYS%' and OWNER like 'SYS%';
【AUTOトレースのON】
SET AUTOTRACE TRACEONLY
【実行計画の表示幅を変更】
COLUMN plan_plus_exp format a60
【実行計画の調査】
-- PLAN_TABLEを一旦削除
delete from plan_table;
-- 対象SQLの実行計画の取得
explain plan
set statement_id = '調査1' for
select * from ・・・・・・;
-- 表示設定
SET PAGESIZE 50
SET LINESIZE 200
COLUMN "QUERY PLAN" FORMAT A50
COLUMN OBJECT_NEME FORMAT A30
-- 簡易表示
SELECT LPAD(' ',2*(LEVEL-1))||OPERATION||' '||OPTIONS "QUERY PLAN" ,OBJECT_NAME
FROM PLAN_TABLE
START WITH ID = 0
CONNECT BY PRIOR ID = PARENT_ID
AND STATEMENT_ID = '&STATEMENT_ID';
-- ちょっと詳細表示
set markup html preformat on
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
【統計情報の取得】
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME => 'USERID'
,TABNAME => 'テーブル名'
,METHOD_OPT => 'FOR ALL INDEXED' -- 索引列のカラム統計を取る
,CASCADE => TRUE -- 関連する索引の統計も取る
);
END;
/
【統計情報の削除】
BEGIN
DBMS_STATS.DELETE_TABLE_STATS('USERID','テーブル名');
END;
/
【ロックセッション抽出】
/* ロックをかけているプログラム、ユーザの表示 */
SELECT SADDR,SID,SERIAL#,USERNAME,PROGRAM
FROM V$SESSION
WHERE SID = ANY(SELECT SID FROM V$LOCK WHERE TYPE IN ('TX','TM'));
/* 問題のSQLを表示 */
SELECT A.SQL_TEXT,A.ADDRESS
FROM V$SQLAREA A,V$SESSION B
WHERE A.ADDRESS = B.SQL_ADDRESS
AND B.SID = ANY(SELECT SID FROM V$LOCK WHERE TYPE IN ('TX','TM'));
/* ロックをかけているセッションの情報を表示 */
SELECT a.SID sid,
a.USERNAME username,
a.SERIAL# serialno,
b.TYPE type,
a.PROGRAM program,
TO_CHAR(b.CTIME/60,'999990.9') lock_time,
c.SQL_TEXT SQL
FROM V$SESSION a,
V$LOCK b,
V$SQLAREA c
WHERE a.SID = b.SID
AND b.TYPE IN ('TX','TM')
AND a.SQL_ADDRESS = c. ADDRESS;
/* ロックのため待ちが発生しているセッション情報 */
SELECT a.USERNAME username,
a.PROGRAM program,
a.SERIAL# serialno,
a.SID sid,
b.TYPE type,
TO_CHAR(b.CTIME/60,'999990.9') lock_time
FROM V$SESSION a,
V$LOCK b
WHERE a.SID = b.SID
AND b.TYPE = 'TM'
AND b.SID = (SELECT SID FROM V$LOCK c WHERE c.TYPE = 'TX' AND c.REQUEST > 0);
/* 問題のセッションをKILL ※SYSDBA権限が必要 */
alter system kill session 'sid,serial'
** oracleセッションID取得 [#o438c36c]
select userenv('SESSIONID') from dual
** SQLID取得(v$session) [#caea1664]
** SQL文取得(v$sqltext) [#xdc3da3a]
** SQLコスト取得(v$sqlarea) [#a4b64d6c]
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
