Help us understand the problem. What is going on with this article?

Oracle便利sqlまとめ(自分用)

More than 1 year has passed since last update.

■セッションを切る。
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;

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

2)自動拡張機能のON/OFF
alter database datafile '対象のDBFファイルの保存先' autoextend on;
alter database datafile '対象のDBFファイルの保存先' autoextend off;

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

4)設定が変更されていることを確認
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;

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away