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;