Oracle便利sqlまとめ（自分用）


■セッションを切る。
`ALTER SYSTEM KILL SESSION '(SID),(SERIAL)' ;`

■データファイルの配置場所確認
`SELECT * FROM DBA_DATA_FILES; `

■データファイルのリサイズ
`ALTER DATABASE DATAFILE 'ファイル名（フルパス）' RESIZE  ファイルサイズM(or G);`

■データファイルの追加
`ALTER TABLESPACE TABLESPACENAME ADD DATAFILE 'DATAFILEPATH' SIZE ファイルサイズG;`

◆自動拡張設定
１）現在の設定確認
`select substr(file_name, 1, 100),autoextensible,increment_by from dba_data_files;`

２）自動拡張機能のON／OFF
`alter database datafile '対象のDBFファイルの保存先' autoextend on;
alter database datafile '対象のDBFファイルの保存先' autoextend off;`

３）拡張値を変更
`alter database datafile '対象のDBFファイルの保存先' autoextend on next 拡張値`

４）設定が変更されていることを確認
`select substr(file_name, 1, 100),autoextensible,increment_by from dba_data_files;`


■特定のテーブルが属する表領域を割り出す。
`SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES`

■共有プールの削除
`ALTER SYSTEM FLUSH SHARED_POOL;`

■バッファキャッシュの削除
`ALTER SYSTEM FLUSH BUFFER_CACHE ;`

■オブジェクトのDDL分を取得する
`SET TRIMS ON
SET LONG 1000000
SET PAGESIZE 0
select 
   dbms_metadata.get_ddl('PROCOBJ','HOGE_JOB')
   ,dbms_metadata.get_ddl('TABLE','PIYO_TABLE')
from dual;
`

■ジョブの情報を取得するsql 
`select * from sys.dba_scheduler_jobs;`

■権限確認
`select * from USER_SYS_PRIVS`

■権限付与
`GRANT 	CREATE   SESSION         TO hoge_user;
GRANT  	INSERT 	 ANY TABLE 	 TO hoge_user;
GRANT  	SELECT 	 ANY TABLE 	 TO hoge_user;
GRANT  	UPDATE 	 ANY TABLE 	 TO hoge_user;
GRANT  	DELETE 	 ANY TABLE 	 TO hoge_user;
GRANT  	FLASHBACK 	 ANY TABLE 	 TO hoge_user;
GRANT  	LOCK 	 ANY TABLE 	 TO hoge_user;
GRANT  	CREATE 	 ANY TABLE 	 TO hoge_user;
GRANT  	CREATE 	 ANY TABLE 	 TO hoge_user;
GRANT  	ALTER 	 ANY TABLE 	 TO hoge_user;
GRANT  	BACKUP 	 ANY TABLE 	 TO hoge_user;
GRANT  	DROP 	 ANY TABLE 	 TO hoge_user;`

■コンパイルエラーの確認
`SHOW ERROR`
→同セッション内で生成したもののコンパイルエラー

`SHOW ERRORS [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [[schema.]name]`
→直指定することで過去のコンパイルエラーも確認できる

■ロックを行っているSQL一覧を参照する。
`SELECT
  'BLOCK' "B/R", B.USERNAME, B.SID || ',' || B.SERIAL# "SID/SERIAL", B.OSUSER, B.MACHINE, B.TERMINAL, B.PROGRAM, B.STATUS, A.TYPE, A.CTIME, DECODE(A.LMODE, 0,'NONE', 1,'NULL', 2,'行共有', 3,'行排他', 4,'共有', 5,'共有/行排他', 6,'排他', '?') HELD, DECODE(A.REQUEST, 0,'NONE', 1,'NULL', 2,'行共有', 3,'行排他', 4,'共有', 5,'共有/行排他', 6,'排他', '?') REQUESTED, D.OWNER, D.OBJECT_NAME, NVL(E.SQL_TEXT,'') SQL_TEXT
FROM
  V$LOCK A, V$SESSION B, V$LOCKED_OBJECT C, ALL_OBJECTS D , V$SQL E 
WHERE
  A.BLOCK > 0   AND A.SID = B.SID   AND A.ID2 = C.XIDSQN   AND C.OBJECT_ID = D.OBJECT_ID   AND B.SQL_ADDRESS = E.ADDRESS(+) 
UNION ALL 
SELECT 
  /*+ORDERED ORDERED_PREDICATES*/   
  'REQ' "B/R",   B.USERNAME,  B.SID || ',' || B.SERIAL# "SID/SERIAL",  B.OSUSER,   B.MACHINE,   B.TERMINAL,   B.PROGRAM,   B.STATUS,  A.TYPE,   A.CTIME,   DECODE(A.LMODE, 0,'NONE', 1,'NULL', 2,'行共有', 3,'行排他', 4,'共有', 5,'共有/行排他', 6,'排他', '?') HELD,  DECODE(A.REQUEST, 0,'NONE', 1,'NULL', 2,'行共有', 3,'行排他', 4,'共有', 5,'共有/行排他', 6,'排他', '?') REQUESTED,   D.OWNER,   D.OBJECT_NAME,   NVL(E.SQL_TEXT,'') SQL_TEXT
FROM
  V$LOCK A,   V$SESSION B,   V$LOCKED_OBJECT C,   ALL_OBJECTS D,  V$SQL E 
WHERE
  B.LOCKWAIT IS NOT NULL   AND B.LOCKWAIT = A.KADDR   AND A.SID = C.SESSION_ID   AND C.XIDSQN = 0   AND C.OBJECT_ID = D.OBJECT_ID  AND B.SQL_ADDRESS = E.ADDRESS(+)`

■実行中のSQL一覧を参照する。
`SELECT
  TO_CHAR(SES.LOGON_TIME, 'yyyy/mm/dd HH24:MI:SS') AS LOGON_TIME, SES.USERNAME AS LOGON_USER, SES.SID AS SID, SES.SERIAL# AS SERIAL, SES.STATUS AS STATUS, SQL.SQL_TEXT AS SQL_TEXT 
FROM
  V$SESSION SES, V$SQLAREA SQL 
WHERE
  SES.SQL_ADDRESS = SQL.ADDRESS(+) AND SES.SQL_HASH_VALUE = SQL.HASH_VALUE AND SES.TYPE = 'USER' `

■表領域使用率
`SELECT
  FREE.TABLESPACE_NAME , FLS.BYTES/1024/1024 "DBF(Mbite)" , FREE.BYTES/1024/1024 "FREE(Mbite)" , ROUND(FREE.BYTES/FLS.BYTES, 2) FREE_PRC 
FROM
  (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES , SUM(BYTES)/1024/1024 FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE 
  ,(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, SUM(BYTES)/1024/1024 FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) FLS 
  ,(SELECT TABLESPACE_NAME, SUM(HEADER_BLOCK) HD_BLOCK, SUM(BYTES) BYTES, SUM(BLOCKS) BLOCKS , SUM(EXTENTS) EXT,
       SUM(INITIAL_EXTENT)INIT_EXT, SUM(NEXT_EXTENT) NEXT_EXT FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME ) SEG 
  ,DBA_TABLESPACES TBSP 
WHERE
  FREE.TABLESPACE_NAME = FLS.TABLESPACE_NAME AND FREE.TABLESPACE_NAME = TBSP.TABLESPACE_NAME 
  AND FREE.TABLESPACE_NAME = SEG.TABLESPACE_NAME(+) AND FREE.TABLESPACE_NAME = TBSP.TABLESPACE_NAME(+) 
  AND FREE.TABLESPACE_NAME IS NOT NULL
ORDER BY
  TABLESPACE_NAME`
    
■表領域グラフ化
`SELECT
  D.TABLESPACE_NAME, D.FILE_NAME, D.FILE_ID, D.STATUS, 
  D.BYTES / 1024 / 1024 "BYTES(MB)", 
  F.BYTES / 1024 / 1024 "F_BYTES(MB)", 
  (D.BYTES - F.BYTES) / D.BYTES * 100 "使用率(%)"
  , nvl( rpad( rtrim( ltrim( rpad(' ',20 * ROUND((D.BYTES - F.BYTES) / D.BYTES,1) + 1,'■') ) ),20,'□' ), '□□□□□□□□□□' ) グラフ, 
  S.PHYRDS, S.PHYWRTS, S.PHYBLKRD, S.PHYBLKWRT 
FROM
  DBA_DATA_FILES D, V$FILESTAT S, 
  ( SELECT FILE_ID, SUM(BYTES) BYTES, SUM(BLOCKS) BLOCKS FROM DBA_FREE_SPACE GROUP BY FILE_ID ) F 
WHERE
  D.FILE_ID = F.FILE_ID AND D.FILE_ID = S.FILE# ORDER BY D.FILE_ID; `