備忘録

SHOW ALL
SHOW PARAMETER
SET PAGES 1000
SET LINE 300

▼システム系を弾く
OWNER NOT IN ('SYS','SYSTEM','SYSMAN','CTXSYS','DMSYS','EXFSYS','TSMSYS','MDSYS','WMSYS','ORDSYS','DBSNMP','XDB','SH','OE','HR','IX','PM','OUTLN','OLAPSYS','DIP','PERFSTAT')

▼シェルスクリプト中のSQL*PLUS呼出し
tmp_file=/tmp/a.log
sqlplus system/${SID_ORAMANGER_PWD}<<EOF
def altercode = 9
whenever sqlerror exit altercode
set feedback 1
set linesize 9999
set pagesize 0
set trimspool on
spool $tmp_file
SELECT * FROM DBA_TABLES;
spool off
EOF

▼スキーマで検索
SELECT 
 SUBSTR(OWNER,1,8)|| '.' ||SUBSTR(TABLE_NAME,1,10)|| ' ' ||SUBSTR(TABLESPACE_NAME,1,15)|| '  ' ||TO_CHAR( LAST_ANALYZED, 'YYYY-MM-DD  HH24:MI:SS' ) AS TABLE_TABLESPACE_LASTANALYZED
FROM  DBA_TABLES 
WHERE 
 OWNER LIKE 'schemaName' 
ORDER BY 
 OWNER, 
 TABLE_NAME 
;

 AND TABLE_NAME='tableName' 

▼スキーマかつ索引一覧検索○
SELECT 
 SUBSTR(OWNER,1,8)|| '.' ||SUBSTR(TABLE_NAME,1,10)|| ' ' ||SUBSTR(INDEX_NAME ,1,11)|| ' ' ||SUBSTR(TABLESPACE_NAME,1,15)|| ' ' ||TO_CHAR( LAST_ANALYZED, 'YYYY-MM-DD  HH24:MI:SS' )|| ' ' ||SUBSTR(INDEX_TYPE,1,6)|| ' ' ||SUBSTR(UNIQUENESS,1,9) AS INDEX_TABLESPACE_LASTANALYZED
FROM  DBA_INDEXES 
WHERE 
 OWNER LIKE 'schemaName' 
ORDER BY 
 OWNER, 
 TABLE_NAME, 
 INDEX_NAME 
;

▼索引構成カラム検索
SELECT 
 SUBSTR(INDEX_OWNER,1,8)|| '.' ||SUBSTR(INDEX_NAME,1,11)|| ' ' ||SUBSTR(COLUMN_NAME,1,20) AS COLUMN_NAME, 
 COLUMN_POSITION, 
 COLUMN_LENGTH 
FROM  DBA_IND_COLUMNS 
WHERE 
 INDEX_NAME LIKE 'schemaName' AND 
 INDEX_OWNER LIKE 'tableName' 
ORDER BY 
 INDEX_OWNER, 
 INDEX_NAME, 
 COLUMN_POSITION 
;

▼オブジェクトの削除後のゴミ箱検索とゴミ箱削除コマンド
SELECT * FROM DBA_RECYCLEBIN
;
# やっぱり戻す
# FLASHBACK TABLE table_name TO BEFORE DROP;
# 完全削除
# conn owner/owner_passwd
# PURGE RECYCLEBIN;

▼統計情報がロックされているかどうか、確認するSQL
SELECT 
 SUBSTR(OWNER,1,8)|| '.' ||SUBSTR(TABLE_NAME,1,10)|| '   ' ||STATTYPE_LOCKED AS TABLE_NAME
FROM  DBA_TAB_STATISTICS 
WHERE 
 OWNER LIKE 'S_______' 
;

▼セグメント単位・容量確認コマンド
SELECT 
 SUBSTR(TABLESPACE_NAME,1,15) AS TABLESPACE_NAME,
 SUBSTR(PARTITION_NAME,1,20) AS PARTITION_NAME,
 SUBSTR(OWNER,1,8)|| '.' ||SUBSTR(SEGMENT_NAME,1,20) AS SEGMENT_NAME,
 SUBSTR(SEGMENT_TYPE,1,20) AS SEGMENT_TYPE,
 (BYTES/1024/1024) AS MB,
 BLOCKS,
 EXTENTS,
 INITIAL_EXTENT,
 NEXT_EXTENT,
 MAX_EXTENTS
FROM DBA_SEGMENTS
WHERE
 OWNER LIKE 'schemaName' 
ORDER BY 1,2,3
;

▼パーティション・カラムリストコマンド
SELECT 
 SUBSTRB(OWNER,1,8),
 SUBSTRB(NAME,1,15),
 OBJECT_TYPE,
 SUBSTRB(COLUMN_NAME,1,25),
 COLUMN_POSITION 
FROM  DBA_PART_KEY_COLUMNS 
WHERE 
 OWNER='schemaName'
ORDER BY 
 OBJECT_TYPE,
 NAME
;

▼サブパーティション・カラムリストコマンド
SELECT 
 SUBSTRB(OWNER,1,8),
 SUBSTRB(NAME,1,15),
 OBJECT_TYPE,
 SUBSTRB(COLUMN_NAME,1,25),
 COLUMN_POSITION 
FROM  DBA_SUBPART_KEY_COLUMNS 
WHERE 
 OWNER='schemaName'
ORDER BY 
 OBJECT_TYPE,
 NAME
;

▼シーケンス
SELECT
 SUBSTR(SEQUENCE_OWNER,1,8)|| '.' ||SUBSTR(SEQUENCE_NAME,1,12) AS SEQUENCE, 
 MIN_VALUE, 
 MAX_VALUE, 
 INCREMENT_BY, 
 CYCLE_FLAG, 
 ORDER_FLAG, 
 CACHE_SIZE
FROM  DBA_SEQUENCES 
WHERE 
 SEQUENCE_OWNER LIKE 'schemaName' 
ORDER BY 
 SEQUENCE_OWNER, 
 SEQUENCE_NAME
;

▼トリガー
SELECT 
 SUBSTR(OWNER,1,8)|| '.' ||SUBSTR(TRIGGER_NAME,1,11)|| ' ' ||SUBSTR(TABLE_OWNER,1,8)|| '.' ||SUBSTR(TABLE_NAME,1,8)|| ' ' ||SUBSTR(STATUS,1,8)  AS TRIGGERS 
FROM  DBA_TRIGGERS 
WHERE 
 OWNER LIKE 'schemaName' 
ORDER BY 
 OWNER, 
 TRIGGER_NAME, 
 TABLE_OWNER, 
 TABLE_NAME 
;

▼トリガーＯＮ／ＯＦＦ
ALTER TRIGGER schema.table ENABLE
;
ALTER TRIGGER schema.table DISABLE
;

▼データファイル
SELECT 
 SUBSTR(FILE_NAME,1,50) AS FILE_NAME, 
 SUBSTR(TABLESPACE_NAME,1,15) AS TBSP,
 (BYTES/1024/1024) AS MB ,
 STATUS         ,
 AUTOEXTENSIBLE ,
 (MAXBYTES/1024/1024) AS MAX_MB ,
 INCREMENT_BY   ,
 USER_BYTES     ,
 ONLINE_STATUS  
FROM  DBA_DATA_FILES 
where FILE_NAME like '%'
ORDER BY 
 FILE_NAME
;

▼表領域
SELECT 
 * 
FROM  DBA_TABLESPACES
ORDER BY 
 TABLESPACE_NAME 
;

▼ユーザ
SELECT 
 SUBSTR(USERNAME,1,10) AS USERNAME, 
 USER_ID, 
 SUBSTR(DEFAULT_TABLESPACE,1,10) AS DEF_TBSP, 
 SUBSTR(TEMPORARY_TABLESPACE,1,10) AS TMP_TBSP, 
 TO_CHAR( CREATED, 'YYYY-MM-DD  HH24:MI:SS' ) AS CREATED,
 INITIAL_RSRC_CONSUMER_GROUP
FROM  DBA_USERS 
ORDER BY 
 USERNAME
;

▼ロール
SELECT 
 * 
FROM  DBA_ROLE_PRIVS 
ORDER BY 
 GRANTEE, 
 GRANTED_ROLE 
;

▼システム権限
SELECT 
 * 
FROM  DBA_SYS_PRIVS 
ORDER BY 
 GRANTEE, 
 PRIVILEGE
;

▼Oracle JVMがインストールされており、有効であることの確認
  SQL*Plusを実行してSYSDBAとして接続し、次の問合せを発行して
  バージョンが正しく、状態がVALIDであることを確認します。
SELECT 
 VERSION, 
 STATUS 
FROM DBA_REGISTRY 
WHERE 
 COMP_ID='JAVAVM'
;

▼processes上限値検索
SELECT 
 CURRENT_UTILIZATION, 
 MAX_UTILIZATION, 
 LIMIT_VALUE
FROM V$RESOURCE_LIMIT 
WHERE 
 RESOURCE_NAME = 'PROCESSES'
;

○現在の値
SELECT 
 COUNT(*) 
FROM V$PROCESS
;

○上限値
SELECT 
 NAME,
 VALUE 
FROM V$PARAMETER 
WHERE 
 NAME = 'PROCESSES'
;

▼アーカイブログモード変更（アーカイブ⇒ノーアーカイブ）
sqlplus  /  as  sysdba
archive log list
		データベース・ログ・モード     アーカイブ・モード
		自動アーカイブ                 有効
		アーカイブ先                    /opt/oracle/SID/arch/archSID
		最も古いオンライン・ログ順序   x
		アーカイブする次のログ順序     y
		現行のログ順序                 z

shutdown  immediate; 
startup  mount  pfile=/opt/oracle/SID/dbsparam/initSID.ora
alter  database  noarchivelog;
alter  database  open;

archive log list
		データベース・ログ・モード     非アーカイブ・モード
		自動アーカイブ                 使用禁止
		アーカイブ先                    /opt/oracle/SID/arch/archSID
		最も古いオンライン・ログ順序   x
		アーカイブする次のログ順序     y
		現行のログ順序                 z

▼アーカイブログモード変更（ノーアーカイブ⇒アーカイブ）
sqlplus  /  as  sysdba
archive log list
		データベース・ログ・モード     非アーカイブ・モード
		自動アーカイブ                 使用禁止
		アーカイブ先                    /opt/oracle/SID/arch/archSID
		最も古いオンライン・ログ順序   x
		アーカイブする次のログ順序     y
		現行のログ順序                 z

shutdown  immediate; 
startup  mount  pfile=/opt/oracle/SID/dbsparam/initSID.ora
alter  alter  database  archivelog;
alter  database  open;

archive log list
		データベース・ログ・モード     アーカイブ・モード
		自動アーカイブ                 有効
		アーカイブ先                    /opt/oracle/SID/arch/archSID
		最も古いオンライン・ログ順序   x
		アーカイブする次のログ順序     y
		現行のログ順序                 z

▼ログスイッチ
alter system archive log current
;

▼オラクルユーザ　アカウントロック調査
SELECT USERNAME , ACCOUNT_STATUS FROM DBA_USERS ORDER BY USERNAME
;

▼オラクルユーザ　アカウントロック解除
ALTER USER SYSTEM ACCOUNT UNLOCK
;

▼オラクルユーザ　アカウントロック　ロック回数調査・無制限指定
SELECT * FROM DBA_PROFILES 
WHERE RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS'
;

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED
;

▼統計情報の最終更新日時確認SQL(テーブル)
SELECT OWNER ,TABLE_NAME ,TO_CHAR( LAST_ANALYZED, 'YYYY-MM-DD DAY HH24:MI:SS' ) 
FROM DBA_TABLES 
WHERE OWNER LIKE 'S_______' 
ORDER BY OWNER ,TABLE_NAME 
;

▼統計情報の最終更新日時確認SQL(索引)
SELECT OWNER ,TABLE_NAME ,INDEX_NAME ,TO_CHAR( LAST_ANALYZED, 'YYYY-MM-DD DAY HH24:MI:SS' ) 
FROM DBA_INDEXES 
WHERE OWNER LIKE 'S_______' 
ORDER BY OWNER ,TABLE_NAME ,INDEX_NAME
;

▼統計情報がロックされているかどうか、確認するSQL
SELECT 
 SUBSTR(OWNER,1,8)|| '.' ||SUBSTR(TABLE_NAME,1,10)|| '   ' ||STATTYPE_LOCKED AS TABLE_NAME
FROM  DBA_TAB_STATISTICS 
WHERE 
 OWNER like 'S_______' 
;

▼索引のステータス確認（DB LOAD INDEX UNUSABLE CHECK）
select 
 TABLE_OWNER,
 TABLE_NAME,
 owner, 
 INDEX_NAME ,
 STATUS 
FROM  dba_indexes 
where 
    status ='UNUSABLE' and 
    TABLE_OWNER not like '%SYS%' 
order by 
    TABLE_OWNER,
    TABLE_NAME,
    owner, 
    INDEX_NAME
;

▼上記のリターン例
TABLE_OWNER    TABLE_NAME     OWNER          INDEX_NAME     STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------
schemaName       tableName          schemaName     indexName    UNUSABLE

▼索引再作成
alter index schemaName.tableName rebuild online;

▼OPatchコマンド
${ORACLE_HOME}/OPatch/opatch lsinventory

▼列定義変更
ALTER TABLE tableID MODIFY ( column_NAME CHAR(xxx))
;

▼表領域サイズ変更
ALTER DATABASE DATAFILE '/opt/oracle/SID/dbsusrXXXX/XXXXXX.xxx' RESIZE xxxxM
;

▼表領域データファイル追加
ALTER TABLESPACE aaaaaaaa ADD DATAFILE '/opt/oracle/SID/dbsusrXXXX/XXXXXX.xxx' SIZE xxxM
;

▼列追加
ALTER TABLE tableID ADD    ( column_NAME CHAR(xxx))
;

▼v$sql
select * FROM
(SELECT
        sql_text,
        executions,
        buffer_gets,
        cpu_time,elapsed_time
FROM v$sql
WHERE executions>0 and  
 sql_text     like '%例えばテーブル名%'     and 
 sql_text not like '%select%'   and 
 sql_text not like '%BEGIN%'    and 
 sql_text not like '%explain%'  and 
 sql_text not like '%sql_text%' 
ORDER BY elapsed_time desc)
WHERE rownum <= 10000
;


▼漠然とした”待ち”を調査する
> −−−−−−−−−−−−−−−−−−−−−−−−
> V$SESSION_EVENT　セッションによるイベントの待機情報
> V$SYSTEM_EVENT　イベントの待機の合計の情報
> V$SESSION_WAIT　アクティブ・セッションが待機しているリソースまたはイベント
> V$SESSION_WAIT_HISTORY　各アクティブ・セッションの最後の10待機イベント
> −−−−−−−−−−−−−−−−−−−−−−−−
>
> ※statspackのスナップショット取得レベルですが、
> 　スナップレベル ”7 or 10”としてください。
> 　7で、セグメント統計まで取得できます。
> 　10ではラッチ関連まで。
>
> 　例）connect perfstat/perfstat
> 　　　execute statspack.snap(i_snap_level => 10);

▼スナップショットを取得するために
ＤＢ構築後（いつでもＯＫ）に下記スクリプト実行。
@/opt/oracle/app/oracle/product/11.2.0/rdbms/admin/spcreate.sql

　⇒そうすると以下の３つを聞かれる。
　　perfstatユーザのパスワード（ここで新規設定）
　　perfstatユーザのデフォルト表領域
　　perfstatユーザのデフォルト一時表領域

▼スナップショットを取る
"  perfstatユーザで、スナップショットの取得
SQL> connect perfstat/perfstat
SQL> execute statspack.snap

"  レポートを作成
"  開始/ 終了のスナップショットid、レポートのファイル名を入力
→ カレント・ディレクトリに出力ファイルが作成される
SQL> connect perfstat/perfstat
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql


▼SQLトレース＠特定セッション（ON,OFF）
EXECUTE SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SID,SERIAL#,TRUE)
;

EXECUTE SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SID,SERIAL#,FALSE)
;
su - oracle
cd /opt/oracle/SID/log/udump
tkprof ./xxx.trc ./xxx.prof
tkprof ./SID_ora_19980.trc ./SID_ora_19980.trc.prof aggregate=no sys=no

▼SQLトレース＠ＤＢ全体（ON,OFF）
ALTER SYSTEM SET SQL_TRACE=TRUE
;
ALTER SYSTEM SET SQL_TRACE=FALSE
;

▼イベント10053
ALTER SESSION SET EVENTS '10053 trace name context forever'
;
（＝＝SQL実行＝＝）
alter session set events '10053 trace name context off'
;

▼バッファキャッシュクリア
alter system flush buffer_cache
;

▼共有プールクリア
alter system flush shared_pool
;

▼REDOログスイッチ
select * from v$log 
;
alter system switch logfile
;
select * from v$log 
;

▼実行計画（アクセスパス）
プラン表作成
ＳＱＬ実行
実行計画抽出
SQL> @/opt/oracle/app/oracle/product/11.2.0/rdbms/admin/utlxplan
SQL> explain plan for SELECT a FROM table;
SQL> @/opt/oracle/app/oracle/product/11.2.0/rdbms/admin/utlxpls

▼セッション情報（全画面、linesize 240）
SELECT 
 SUBSTRB(SCHEMANAME,1,10) AS SCHEMANAME,
 TYPE,
 TO_CHAR(LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') AS LOGON_TIME,
 SID,
 SERIAL#,
 PROCESS,
 STATUS,
 LOCKWAIT,
 SUBSTRB(OSUSER,1,10) AS OSUSER,
 SUBSTRB(MACHINE,1,20) AS MACHINE,
 SUBSTRB(TERMINAL,1,20) AS TERMINAL,
 SUBSTRB(SERVICE_NAME,1,20) AS SERVICE_NAME,
 PROGRAM
FROM V$SESSION
WHERE TYPE <> 'BACKGROUND'
ORDER BY SCHEMANAME,TYPE,LOGON_TIME
;

--alter system kill session 'SID,SERIAL#' IMMEDIATE;

▼NLS_LANG確認
SELECT 
 NLS_CHARSET_NAME(NLS_CHARSET_ID('CHAR_CS')),
 NLS_CHARSET_NAME(NLS_CHARSET_ID('NCHAR_CS')) 
FROM DUAL;

▼オブジェクトに対するロック情報
SELECT 
 L.ORACLE_USERNAME,
 L.OS_USER_NAME,
 T.OWNER,
 T.TABLE_NAME
FROM 
    V$LOCKED_OBJECT L, 
    DBA_OBJECTS O, 
    DBA_TABLES T
WHERE 
      L.OBJECT_ID   = O.OBJECT_ID
  AND O.OWNER       = T.OWNER
  AND O.OBJECT_NAME = T.TABLE_NAME
  AND T.TABLE_NAME  = 'xxxxxxxxxxxxxxxx'
;

▼カーソル　同じSQL文のカーソルが大量にオープンされていないかどうかを調べる
SELECT OC.NUM_CURSORS,ST.SQL_TEXT, OC.USER_NAME, OC.SID
FROM V$SQL ST
INNER JOIN (
        SELECT SID, USER_NAME, ADDRESS, HASH_VALUE, COUNT(*) NUM_CURSORS
                FROM V$OPEN_CURSOR
                GROUP BY SID, USER_NAME, ADDRESS, HASH_VALUE
                HAVING COUNT(*) > 1
) OC
        ON OC.ADDRESS = ST.ADDRESS
                AND OC.HASH_VALUE = ST.HASH_VALUE
ORDER BY OC.SID
;

▼VIEWの依存関係を調べる
SELECT * FROM USER_DEPENDENCIES WHERE TYPE LIKE '%VIEW%' AND REFERENCED_NAME LIKE '%VIEWの名前%'

▼VIEWの依存関係を調べる（DB全体を再帰的検索）
SELECT OWNER,NAME,TYPE,REFERENCED_OWNER,REFERENCED_NAME,LEVEL
FROM DBA_DEPENDENCIES
WHERE TYPE like '%VIEW%'
START WITH REFERENCED_NAME IN (select TABLE_NAME from user_tables /*all_tables*/ ) AND OWNER = USER
CONNECT BY PRIOR NAME = REFERENCED_NAME 
AND PRIOR OWNER = REFERENCED_OWNER
AND PRIOR TYPE = REFERENCED_TYPE
ORDER SIBLINGS BY OWNER,NAME,REFERENCED_OWNER,REFERENCED_NAME
;

▼使用したUNDOブロックサイズを調べる（データ更新後に）
select used_ublk from v$transaction;