Oracleめも

備忘用。

SEQUENCEのリセット（Oracle18c以降）

ALTER SEQUENCE {シーケンス名} RESTART START WITH {開始数値};

SQL文のキャンセル（Oracle18c以降）

ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID';

テーブルのカラム一覧をカンマ繋ぎで取得

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と対象レコードを特定

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
;

現在のスキーマを確認

-- 現在のスキーマ
SELECT SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA' ) FROM DUAL;

実行された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;

フラッシュバックテーブルでテーブル復元

-- フラッシュバックテーブル
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の可能性がある項目の比較（これってどうなの？）

SELECT * FROM DUAL WHERE NVL(<COLUMN>, 'X') = NVL(''※パラメタ, NVL(<COLUMN>, 'X'))

パスワード有効期限を無期限に設定

Dosバッチで設定

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

アカウント状態確認・パスワード関連

-- アカウント状態とロック日時
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';

スキーマ単位で統計情報を取得

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)の設定めも

# 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)より

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レコードのテストデータ作製ワンライナー

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;

テスト用のランダム値生成

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;

表領域の使用率確認用

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の履歴を確認する

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;