tech_memo/ORACLE
連番生成（LEVEL擬似列） †
- 1〜10の連番を取得
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10;
- 現在から1週間分の日付を取得
SELECT TRUNC(SYSDATE, 'DD') + LEVEL - 1 AS WEEKLY_DAY FROM DUAL CONNECT BY LEVEL <= 7;
結合して、テーブルAの値をテーブルBの値でUPDATE †
MERGEを使う †
MERGE INTO TBL1 A
USING (
SELECT
subA.ID AS ID
,subA.ZZZ AS ZZZ
FROM
TBL2 subA
) B
ON (A.ID = B.ID)
WHEN MATCHED THEN
UPDATE SET
A.XXX = B.ZZZ
UPDATEを使う †
UPDATE
(
SELECT
A.COL1 A_COL,
B.COL2 B_COL
FROM
table1 A
INNER JOIN
table2 B
ON
A.CD1 = B.CD1
)
SET A_COL = B_COL
ロール・権限 †
ユーザに付与されているロールの確認 †
ユーザに付与されているオブジェクト権限の確認 †
- 現在のユーザーに付与されているすべてのオブジェクト権限
select * from USER_TAB_PRIVS;
- ユーザーに対してPUBLICロールから継承されるUSER_TAB_PRIVSとオブジェクト権限の両方の結果。これにより、ユーザーに付与されるすべてのオブジェクト権限が表示される。
select * from ALL_TAB_PRIVS;
- すべてのユーザーに付与される、PUBLICロールから継承されるオブジェクト権限。要ADMIN権限
select * from DBA_TAB_PRIVS;
ユーザに付与されているシステム権限の確認 †
ロールに付与されている権限の確認 †
- ADMIN_OPTION列がYESの場合
- 権限がGLOBALロールでない場合、その権限をほかのユーザーに付与可能
- 権限をほかのユーザーから取り消すことが可能
- 権限へのアクセスに必要な認可を変更するため、権限を変更可能
- 権限を削除可能
表領域への権限 †
テーブル、列の論理名取得 †
ストアドプロシージャのソース確認 †
CREATE TABLE †
- CREATE TABLE時にPRIMARY KEYとUNIQUE INDEXを生成する方法
データブロックサイズの取得 †
select df.tablespace_name, df.file_name,dt.block_size
from dba_data_files df, dba_tablespaces dt
where df.tablespace_name = dt.tablespace_name;
エポックタイム<-->タイムスタンプ †
エポック --> タイムスタンプ †
- UTC
select to_char(to_date('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + (submission_ts/(24*60*60*1000)), 'YYYY/MM/DD HH24:MI:SS') as submission_ts from job_queue;
- 日本時間にする場合は9時間足す
select to_char(to_date('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + (submission_ts+(9*60*60*1000))/(24*60*60*1000), 'YYYY/MM/DD HH24:MI:SS') as submission_ts from job_queue;
タイムスタンプ --> エポック †
select 24*60*60*(cast(sys_extract_utc(systimestamp) as date) - date '1970-01-01') epoc_time from dual;
group by の1件目を取得 †
列情報取得 †
SELECT * FROM USER_TAB_COLUMNS WHERE (TABLE_NAME='FE_JOB_QUEUE' AND COLUMN_NAME='PRIORITY');
列の追加・削除・変更 †
DBMS_RANDOM †
Randomな文字列を取得 †
SQL> select dbms_random.string('U',36) from dual;
DBMS_RANDOM.STRING('U',36)
-------------------------------------------
TVLWUCWHITMBTJJQOPNZRQQCTFGNUHSSILDP
Sleep †
Dualを使用して複数レコードの結果を取得する †
group by + countで、レコードカウント0を取得する †
- 上記を応用して、group by句で0件のカウントを取得できる
select
dummy.job_state,
decode(jq.cnt, null, 0, jq.cnt) as count
from
(select job_state, count(*) as cnt from job_queue group by job_state) jq,
( select 0 as job_state from dual union all
select 1 from dual union all
select 2 from dual
) dummy
where
jq.job_state(+) = dummy.job_state
order by 1;
JOB_STATE COUNT
---------- ----------
0 0
1 0
2 50
Tablespace, DataFile?の使用率表示 †
set linesize 160
set pagesize 30
col tablespace_name format a23
col file_name format a45
select
tablespace_name,
'++++ TABLE_SPACE ++++' file_name,
round(sum(bytes)/1024/1024/1024, 2) bytes_gb,
round(sum(bytes-nvl(c3,0))/1024/1024/1024, $ROUND_KETA) used_gb,
round(sum(nvl(c3,0))/1024/1024/1024, $ROUND_KETA) free_gb,
round((sum(bytes-nvl(c3,0))/1024)/(sum(bytes)/1024) *100,2) "ts_used_%",
to_number(null) "df_used_%"
from dba_data_files a,
(select tablespace_name c1,file_id c2,sum(bytes) c3
from dba_free_space
group by tablespace_name,file_id) b
where a.tablespace_name=b.c1(+)
and a.file_id=b.c2(+)
group by tablespace_name
union
select
tablespace_name,
' ' || file_name as file_name,
round(bytes/1024/1024/1024, 2) bytes_gb,
round((bytes-nvl(c3,0))/1024/1024/1024, $ROUND_KETA) used_gb,
round(nvl(c3,0)/1024/1024/1024, $ROUND_KETA) free_gb,
to_number(null) "ts_used_%",
round(((bytes-nvl(c3,0))/1024)/(bytes/1024)*100,2) "df_used_%"
from dba_data_files a,
(select tablespace_name c1,file_id c2,sum(bytes) c3
from dba_free_space
group by tablespace_name,file_id) b
where a.tablespace_name=b.c1(+)
and a.file_id=b.c2(+)
order by 1, 2 desc;
ランダムに行を取得する †
- sample関数を使うと高速に取得できる。ただし、sampleの引数の値が大きいと値があまりバラけない。小さいと、ROWNUMで指定した値より小さい行数しか取得できない。
正規表現を使ったQuery †
Datafileの追加 †
Create tablespace †
- crate tablespace時に、複数のdatafileを指定 + 暗号化
CREATE TABLESPACE MYAPP_LOB_DATA_099
DATAFILE
'&&myapp_diskgroup' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,
'&&myapp_diskgroup' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,
'&&myapp_diskgroup' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,
'&&myapp_diskgroup' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,
'&&myapp_diskgroup' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,
'&&myapp_diskgroup' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);
Parallel SQL †
Performance †
Memroy sort率 †
SELECT A.RATIO "MEMORY SORT RATIO",
DECODE(SIGN(A.RATIO - 0.95),1,
'SORT_AREA_SIZE is enough',
'SORT_AREA_SIZE is not enough. You shoud increase it.') "NOTES"
FROM (SELECT M.VALUE / (M.VALUE + D.VALUE) RATIO
FROM V$SYSSTAT M, V$SYSSTAT D
WHERE M.NAME = 'sorts (memory)'
AND D.NAME = 'sorts (disk)') A;
Sessionの接続元特定 †
select OSUSER,MACHINE from v$session
自セッションのSID (V$SESSION上のAUDSID) 取得 †
SELECT SYS_CONTEXT('USERENV', 'SID') FROM DUAL;
V$SESSIONのSID、Serial#、AUDSIDについて †
- SID
- SERIAL＃
- カウンタ。SIDが別のセッションで再利用されるたびに増加します（セッションが終了し、別のセッションが開始して同じSIDを使用する場合）
- AUDSID
- 監査セッションIDは、データベースの存続期間中にセッションを一意に識別します。また、クエリ・コーディネータ用のパラレル問合せスレーブを見つけるときにも便利です（PQ実行中は同じAUDSIDを使用します）
- 前項目の自セッションのセッションIDと一致する。
Tableから指定行のレコード取得 †
select myapp_id from
(
select myapp_id, rownum as line from myapp_table order by myapp_id
)
where line = 2000;
LOBデータのサイズ取得 †
select DBMS_LOB.GETLENGTH(RESULT_DATA) from fe_results
最新の20件
2023-01-26
2022-11-11
2022-03-02
2022-02-03
2021-11-18
2021-11-11
2021-10-27
2021-10-04
2021-09-30
2021-08-24
2021-04-01
2021-02-25
2020-08-13
2020-05-22
2020-04-16
2020-04-15
2020-03-23
2020-03-12
2020-02-27
今日の8件
- counter: 733
- today: 1
- yesterday: 1
- online: 2
