Oracleめも

備忘用。

## OracleXeインストール時めも
```sql
1. sysでログイン 
sqlplus sys/{インストール時に設定したパスワード} as sysdba 
  
2. C##USER の作成（CDBの共通ユーザー作成）
CREATE USER C##USER IDENTIFIED BY "USER1234" TEMPORARY TABLESPACE TEMP; 
  
3. 権限付与 
GRANT CONNECT TO C##USER; 
GRANT RESOURCE TO C##USER; 
ALTER USER C##USER QUOTA UNLIMITED ON USERS; 
  
4. 接続 
sqlplus /nolog 
conn C##USER/USER1234@xe
```

## Listagg(リスタッグ)関数
[Listagg(リスタッグ)関数まとめ](https://qiita.com/pinebook/items/07f8e73e4890b837e63b)

## v_$sessionを参照したい
```sql
一般ユーザでv_$sessionを参照するには、sysユーザでgrant文を実行して、  
v_$sessionのselect権限を付与してあげれば良い。  
  
connect sys/oracle as sysdba  
grant select on v_$session to ユーザ名  
>権限付与が成功しました。  
```

## 遅延用プロシージャ（テスト用）
```plsql
CREATE OR REPLACE PROCEDURE sleep
IS 
  t TIMESTAMP := SYSTIMESTAMP;
BEGIN
  WHILE SYSTIMESTAMP - t < INTERVAL '5' SECOND LOOP NULL; END LOOP;
END;
/

DROP PROCEDURE sleep;
```
```xml:MyBatisから呼び出し
<select id="callSleep" statementType="CALLABLE" timeout="3">
    {
        call sleep()
    }
</select>
```

## SEQUENCEのリセット（Oracle18c以降）
```sql
ALTER SEQUENCE {シーケンス名} RESTART START WITH {開始数値};
```


## SQL文のキャンセル（Oracle18c以降）
```sql
ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID';
```


## テーブルのカラム一覧をカンマ繋ぎで取得
```sql
SET LONG 2000000000
SELECT RTRIM(XMLAGG(XMLELEMENT(e, column_name || ',').EXTRACT('//text()') ORDER BY COLUMN_ID).GetClobVal(), ',') AS RESULT FROM all_tab_columns where owner = {ユーザID} AND table_name = {テーブル名};
```


## 行ロックで待たされているSQLと対象レコードを特定
```sql
SELECT
  s.INST_ID,
  s.SID,
  s.SERIAL#,
  -- ,s.USERNAME
  -- ,s.STATUS
  s.SQL_ID,
  o.OWNER,
  o.OBJECT_NAME,
  DBMS_ROWID.ROWID_CREATE(
    rowid_type = > 1,
    object_number = > s.ROW_WAIT_OBJ#,
    relative_fno = > s.ROW_WAIT_FILE#,
    block_number = > s.ROW_WAIT_BLOCK#,
    row_number = > s.ROW_WAIT_ROW#
  ) AS M_ROWID
  ,
  decode(l.LOCKED_MODE, 0, 'ロック要求中'
    , 1, 'NULL', 2, '行共有(SS)' -- 行共有ロック
    , 3, '行排他(SX)' -- 行排他表ロック
    , 4, '共有(S)' -- 共有表ロック
    , 5, '共有行排他(SRX)' -- 共有行排他表ロック
    , 6, '排他(X)' -- 排他表ロック
    , '???') locked_mode_dec
FROM
  GV$SESSION s
  INNER JOIN
    GV$LOCKED_OBJECT l
  ON  s.INST_ID = l.INST_ID
  AND s.sid = l.session_id
  INNER JOIN
    DBA_OBJECTS o
  ON  l.object_id = o.object_id
WHERE
  s.blocking_session_status = 'VALID'
ORDER BY
  s.SID
;
```


## 現在のスキーマを確認
```sql
-- 現在のスキーマ
SELECT SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA' ) FROM DUAL;
```


## 実行されたSQLの履歴を確認
```sql
SELECT last_active_time, parsing_schema_name, sql_text FROM v$sqlarea WHERE parsing_schema_name <> 'SYS' AND parsing_schema_name <> 'SYSMAN' AND parsing_schema_name <> 'DBSNMP' AND parsing_schema_name <> 'MDSYS' AND parsing_schema_name <> 'EXFSYS' ORDER BY last_active_time DESC;
```


## フラッシュバックテーブルでテーブル復元
```sql
-- フラッシュバックテーブル
ALTER TABLE <TABLE_NAME> ENABLE ROW MOVEMENT; -- 行移動できるようにする必要あり
FLASHBACK TABLE <TABLE_NAME> TO TIMESTAMP TO_TIMESTAMP('2021-09-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
```


## Nullの可能性がある項目の比較（これってどうなの？）
```sql
SELECT * FROM DUAL WHERE NVL(<COLUMN>, 'X') = NVL(''※パラメタ, NVL(<COLUMN>, 'X'))
```


## パスワード有効期限を無期限に設定
<details><summary>Dosバッチで設定</summary><div>

```sql
Rem ^
/*
@Echo Off
cls
echo 「Oracleのパスワード有効期限を無期限に設定」を行います。
pause

SET CONN={ユーザID}/{パスワード}@{Oracle Net接続識別子}
sqlplus %CONN% @"%~f0"

Pause
Exit /b
*/
-- SQL*PLUS設定.
SET ECHO OFF
SET HEADING OFF

-- 実行前確認.
SELECT '実行前パスワード有効期限：' || LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME' AND PROFILE = 'DEFAULT';

-- パスワード有効期限を無期限に設定.
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

-- パスワードを再設定.
ALTER USER {ユーザID} IDENTIFIED BY {パスワード};

-- 実行後確認.
SELECT '実行後パスワード有効期限：' || LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME' AND PROFILE = 'DEFAULT';

EXIT
```
</div></details>
<br/>


## アカウント状態確認・パスワード関連
```sql
-- アカウント状態とロック日時
select username , to_char(lock_date,'yyyy/mm/dd HH24:MI:SS'), account_status from dba_users;
-- パスワード関連のプロファイル情報
select PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT from DBA_PROFILES where PROFILE = 'DEFAULT' and RESOURCE_TYPE = 'PASSWORD';
```


## スキーマ単位で統計情報を取得
```sql
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS (
    OWNNAME => {スキーマ名}
    ,OPTIONS => 'GATHER'
    ,DEGREE => 4
    ,CASCADE => TRUE
  );
END
```

## [Oracle Database Express Edition (XE) Release 18.4.0.0.0 (18c)](https://www.oracle.com/technetwork/jp/database/database-technologies/express-edition/downloads/index.html)の設定めも
```text
# sqlplus で PDBに直接接続
sqlplus system/[password]@localhost:1521/XEPDB1

# なんかのツールの時はJDBCで
jdbc:oracle:thin:@//localhost:1521/XEPDB1

# その他めも
sqlplus /nolog
conn sys@xe as sysdba
select name, open_mode from v$pdbs;
-- PDBオープン
alter pluggable database pdborcl open;
-- コンテナ変更？
alter session set container = XEPDB1;
-- 確認
show con_name
```


## スキーマ内のテーブルそれぞれのテーブル行数をカウント
[ダイナミックSQLを生成し実行結果を得る単一SQL ステートメントの作り方(Oracle)](https://qiita.com/tlokweng/items/ead23e3ebd194f0fe851)より

```sql
SELECT table_name, cnt count
FROM   user_tables,
       XMLTABLE (
           '/ROWSET/ROW' 
           PASSING DBMS_XMLGEN.getxmltype (
               'SELECT COUNT(*) CNT FROM ' || table_name) 
           COLUMNS cnt NUMBER);
```


## 100,000レコードのテストデータ作製ワンライナー
```sql
create table customer (first_name varchar2(20), last_name varchar2(20));
insert into customer select dbms_random.string('U', 20), dbms_random.string('U', 20) from dual connect by level <= 100000;
```


## テスト用のランダム値生成
```sql
select 
  --0以上1未満、小数点以下が38桁(精度38桁)の乱数
  DBMS_RANDOM.VALUE() as a1,
  --1以上100以下の整数の乱数
  FLOOR(DBMS_RANDOM.VALUE(1, 101)) as a2,
  --マイナス100以上100以下の整数の乱数
  FLOOR(DBMS_RANDOM.VALUE(-100, 101)) as a3,
  --大文字のアルファベット文字と数字で文字列      ex 5KNX06FYFV
  DBMS_RANDOM.STRING('X', 10) as a4,
  --大/小文字が混在したアルファベット文字で文字列 ex psTZFnNGFe
  DBMS_RANDOM.STRING('A', 10) as a5,
  --印刷可能な任意の文字で文字列                  ex S:d{6qUQ>m
  DBMS_RANDOM.STRING('P', 10) as a6,
  --文字列の左側をゼロ埋め                        ex 000000b{mqVr#K"7
  lpad(DBMS_RANDOM.STRING('P', 10),16,'0'),
  --文字列の右側をゼロ埋め                        ex V~>5xI\&1M000000
  rpad(DBMS_RANDOM.STRING('P', 10),16,'0'),
  --文字列の左側を空白埋め                        ex [      31$^l[zHF']
  '[' || lpad(DBMS_RANDOM.STRING('P', 10),16) || ']'
from dual;
```


## 表領域の使用率確認用
```sql
SELECT
      D.tablespace_name,
      現サイズ                                     "現サイズ[MB]",
      Round(現サイズ - 空き容量)                    "使用量[KB]",
      Round(( 1 - ( 空き容量 / 現サイズ ) ) * 100)  "使用率(%)",
      空き容量                                     "空き容量[KB]"
FROM  (SELECT tablespace_name,
              Round(SUM(bytes) / ( 1024 ))        "現サイズ"
       FROM   dba_data_files
       GROUP  BY tablespace_name) D,
      (SELECT tablespace_name,
              Round(SUM(bytes) / ( 1024 ))        "空き容量"
       FROM   dba_free_space
       GROUP  BY tablespace_name) F
WHERE  D.tablespace_name = F.tablespace_name;
```


## Oracleで実行されたSQLの履歴を確認する
```sql
select last_active_time,parsing_schema_name,sql_text from v$sqlarea where parsing_schema_name <> 'SYS' and parsing_schema_name <> 'SYSMAN' and parsing_schema_name <> 'DBSNMP' and parsing_schema_name <> 'MDSYS' and parsing_schema_name <> 'EXFSYS' order by last_active_time desc;
```