Oracle

• Oracle
  • SQL*plus
    • インストール
      • 参考
    • ログイン
    • 横幅を調整する
    • 項目表示時の横幅を調節する
    • ヘッダ行の表示タイミングを制御する
    • 「レコードが選択されませんでした」を出力しない
  • tnsnames.ora
    • SID確認
    • サービス名確認
  • ユーザ一覧
  • ユーザアカウントロック解除
    • ユーザアカウントステータス確認
    • 参考
  • ユーザ・テーブルごと権限確認
  • 実行計画取得
    • 対象テーブル一覧取得
    • 統計情報更新
    • 実行計画取得
  • オブジェクト一覧
  • テーブル一覧確認
    • 参考
  • テーブル定義取得
    • 参考
  • CSVデータ取得
    • 方法1
    • 方法2
    • カラム名の出力について
    • 参考
  • CSVデータ登録
    • ctlファイル
      • 説明
      • 登録方法
        • 参考：登録方法
      • trailing nullcols
      • 参考
    • 実行
  • Timestamp型 -> 秒変換する(EXTRACT)
    • 参考
  • ユーザオブジェクトの削除
    • 参考
  • Dockerで環境構築する
    • docker-compose.yml
    • コンテナ外からの接続
    • 注意点
    • 参考
  • 権限を確認する
  • expdp/impdp(エクスポート/インポート)
    • expdp
    • impdp
      • parファイル
  • パーティショニング
    • レンジ
    • リスト
    • ハッシュ
  • Oracle Database アーキテクチャ
    • 参考
  • ライセンス
    • OTN
  • 用語
    • グローバル・データベース名
    • SID

SQL*plus

インストール

Windowsにインストールする方法を記載。

1. ここからファイルダウンロード。
   • Basic Package
   • SQL*Plus Package
2. mkdir c:\oracle
3. 取得したファイルを展開し、c:\oracleに配置。
4. c:\oracle\tnsnames.oraを作成
5. 環境変数追加

     set ORACLE_HOME=c:\oracle
     set PATH=%PATH%;%ORACLE_HOME%
     set TNS_ADMIN=%ORACLE_HOME%
     set NLS_LANG = JAPANESE_JAPAN.JA16SJISTILDE
   

参考

• 【最新】Oracleクライアントのインストール手順:おじさんネットワークエンジニアの気まぐれ日記

ログイン

sqlplus /nolog
conn sys/oracle as sysdba

set ORACLE_LOGIN_USER=
set ORACLE_LOGIN_PASSWORD=
set ORACLE_SERVER_HOST=
set ORACLE_SERVER_PORT=
set ORACLE_CONNECT_WORD=
set EXECUTE_SQL_PATH=

sqlplus %ORACLE_LOGIN_USER%/%ORACLE_LOGIN_PASSWORD%@%ORACLE_SERVER_HOST%:%ORACLE_SERVER_PORT%/%ORACLE_CONNECT_WORD% @%EXECUTE_SQL_PATH%

rem tnsnames.oraでネットサービス名が指定されている場合
set ORACLE_NET_SERVICE_NAME=
sqlplus %ORACLE_LOGIN_USER%/%ORACLE_LOGIN_PASSWORD%@%ORACLE_NET_SERVICE_NAME%

export ORACLE_LOGIN_USER=
export ORACLE_LOGIN_PASSWORD=
export ORACLE_SERVER_HOST=
export ORACLE_SERVER_PORT=
export ORACLE_CONNECT_WORD=
export EXECUTE_SQL_PATH=
sqlplus ${ORACLE_LOGIN_USER}/${ORACLE_LOGIN_PASSWORD}@//${ORACLE_SERVER_HOST}:${ORACLE_SERVER_PORT}/${ORACLE_CONNECT_WORD} @${EXECUTE_SQL_PATH%}

# tnsnames.oraでネットサービス名が指定されている場合
export ORACLE_NET_SERVICE_NAME=
sqlplus ${ORACLE_LOGIN_USER}/${ORACLE_LOGIN_PASSWORD}@${ORACLE_NET_SERVICE_NAME}

• Welcomeメッセージを削除したい場合は、-sオプションを指定する。
• 接続文字列 = ネットサービス名

横幅を調整する

set linesize <値>

項目表示時の横幅を調節する

column <列名> format a{値} [TRUNCATE]

ヘッダ行の表示タイミングを制御する

set pagesize <値>

「レコードが選択されませんでした」を出力しない

set FEEDBACK off

tnsnames.ora

ネットサービス名= 
    (DESCRIPTION =
        (ADDRESS =
            (PROTOCOL = TCP)
            (HOST = ホスト名)
            (PORT = 1521)
        )
        (CONNECT_DATA = 
            (SERVICE_NAME = サービス名)
        )
    )

• SID
  • データベースの識別子。
  • マシン内でデータベースを識別するときに使う。
  • ローカル接続するときにこれを指定する。
• SERVICE_NAME
  • 同一機能を提供するインスタンスの集合体の名前

SID確認

SELECT DBID,NAME,DB_UNIQUE_NAME,CURRENT_SCN,LOG_MODE FROM V$DATABASE

サービス名確認

show parameter service_name

ユーザ一覧

set linesize 200
column USERNAME format a30 TRUNCATE

SELECT * FROM ALL_USERS;

ユーザアカウントロック解除

ALTER USER <ユーザ名> ACCOUNT UNLOCK;

ユーザアカウントステータス確認

column username format a25 TRUNCATE
column profile format a25 TRUNCATE

select username, account_status, profile from dba_users where username = 'TEST';

参考

• Qiita:急にoracleに繋がらなくなった！？～ORA-28001:パスワードが期限切れです～
• Oracle使いのネタ帳。:ORA-28000：Oracleユーザのアカウントロックを解除する

ユーザ・テーブルごと権限確認

select 
  grantee,
  table_name,
  privilege
from
  user_tab_privu
;

実行計画取得

Oracleの統計情報更新と実行計画取得方法について記載。
（※誤字はチェックしていない。）

対象テーブル一覧取得

SELECT TABLE_NAME FROM USER_TABLES
ORDER BY TABLE_NAME

統計情報更新

set pagesize 400
set linsize 200
set echo on
set triming on

spool '<ログ出力パス>'

column tname Format a25 truncate;
select substr(table_name, 1, 25) as tname, to_char( last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as last_analyzed, num_rows from user_tables;

-- 対象テーブルはuser_tablesで取得できるテーブルを全部並べたほうが良い。
analyze table <対象テーブル> compute statistics;

select substr(table_name, 1, 25) as tname, to_char( last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as last_analyzed, num_rows from user_tables;

column iname Format a10 truncate;
select substr(table_name, 1, 25) as tname, substr(index_name, 1, 10) as iname, to_char( last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as last_analyzed, from user_indexes order by tname, iname;

spool off

実行計画取得

※事前に評価対象のSQLを用意する。

set pagesize 400
set linsize 200
set echo on
set triming on

spool '<ログ出力パス>'

explain plan SET STATEMENT_ID = '<任意のID>' FOR
<評価対象のSQL>

select * from table(dbms_xplan.display('PLAN_TABLE','<任意のID>','TYPICAL'));

spool off

exit

オブジェクト一覧

select * 
from USER_OBJECTS 
where object_type = '${任意のタイプ}' 
;

テーブル一覧確認

準備

set pagesize 40
set linesize 250
column TABLE_NAME format a30 TRUNCATE

ログインユーザのテーブル一覧を取得する。

SELECT *
FROM USER_TABLES
ORDER BY TABLE_NAME

ログインユーザがアクセスできるテーブル一覧を取得する。

SELECT *
FROM   ALL_TABLES
ORDER BY OWNER,TABLE_NAME

データベース内のすべてのテーブルを取得する。

SELECT *
FROM   DBA_TABLES
ORDER BY OWNER,TABLE_NAME

指定した表領域にあるテーブル一覧を取得する。

SELECT TABLE_NAME
FROM   *
WHERE  TABLESPACE_NAME = ‘＜表領域名＞’
ORDER BY TABLE_NAME

参考

PROJECT GROUP:テーブルの一覧を取得する（USER_TABLES / ALL_TABLES）

テーブル定義取得

BLOB出力なので、set longとset pages(set pagesizeの省略形)が必要。

set long 10000
set pages 0

set linesize 250
set dbms_metadata.get_ddl('オブジェクトの種類','オブジェクト名','スキーマ名') format a250

select dbms_metadata.get_ddl('オブジェクトの種類','オブジェクト名','スキーマ名') from dual;

• オブジェクトの種類
  • TABLESPACE
  • TABLE
  • INDEX
  • VIEW
  • SYNONYM
  • PACKAGE
  • PROCEDURE
  • etc…
• スキーマ名
  • 省略可能。省略した場合は現在のスキーマ。

参考

• Oracle使いのネタ帳:Oracle DDL取得(DBMS_METADATA.GET_DDL)
• SQL*Plus®ユーザーズ・ガイドおよびリファレンス:リリース1 (12.1):SET LONG {80 | n}
  • set longについて

CSVデータ取得

方法1

SQL書くのが楽だが、値に半角スペースが含まれるのでトリムしなければならない。

-- 検索結果をCSVへ出力する

-- コンソールメッセージを非表示にする
SET ECHO OFF

-- 1行に出力するバイト数
-- 少ないと見切れるので最大に設定する
SET LINESIZE 32767

-- 1ページの行数
-- 少ないとヘッダーが多くて見づらいので無制限に設定する
-- SET PAGESIZE 0
-- ヘッダが欲しい場合は下記の設定。
SET PAGESIZE 50000
SET HEADING ON
SET UNDERLINE OFF

-- 「○○行が選択されました」メッセージを非表示にする
SET FEEDBACK OFF

-- 区切り文字をカンマに指定する
SET COLSEP ','

-- 各行の右端にあるスペースを削除する
SET TRIMSPOOL ON

-- 出力パスは適宜変更する
SPOOL <任意の出力パスに設定する>

-- 検索文は適宜変更する
SELECT *
  FROM {テーブル名}
;

-- ④出力終了
SPOOL OFF

方法2

カラム一覧とヘッダ行は別途確認しておく必要がある。

SET ECHO OFF
SET LINESIZE 32767
SET PAGESIZE 0
SET FEEDBACK OFF
SET TRIMSPOOL ON
SPOOL <任意の出力パスに設定する>

select <カラム1> || ',' ||
       <カラム2> || ',' ||
       ... || ',' ||
       <カラムn>
from テーブル名;

SPOOL OFF

カラム名の出力について

SET PAGESIZE 0するとヘッダ行が表示されないので、別途生成する必要がある。 （select cound(*) from テーブル名;で行数を確認してから指定してもいいが…）

SELECT
  column_name
  , data_type
  , data_length
FROM
  all_tab_columns 
WHERE
  owner = 'HOGE'
  AND table_name = 'FUGA'
ORDER BY
  column_id; 

参考

• Qiita:SELECT文の検索結果をCSVへ出力するSQL(Oracle)
• Qiita:【Oracle】テーブルのカラム情報をSQLで取得する方法

CSVデータ登録

sql*lorder を使う。

ctlファイル

options(skip = 1)
load data
infile '<ファイルパス>'
<登録方法> into table <対象テーブル>
fields terminated by ',' 
optionally enclosed by '"'
trailing nullcols(
  EMPNO DECIMAL EXTERNAL,
  ENAME CHAR,
  JOB CHAR,
  MGR DECIMAL EXTERNAL,
  HIREDATE DATE "YYYYMMDD",
  SAL DECIMAL EXTERNAL,
  COMM DECIMAL EXTERNAL,
  DEPTNO DECIMAL EXTERNAL
)

説明

• options(skip = 1)
  • 1行目を読み飛ばす
• fields terminated by ‘,’
  • カンマ区切り
• optionally enclosed by ‘”’
  • 項目をダブルクォーテーションで囲む（かもしれない）。
• trailing nullcols
  • 読み込んだとき、CSVの対象項目が空文字だった場合、NULLで埋める。

登録方法

• APPEND
  • テーブルが空のときに登録
• REPLACE
  • Delete - Insertする。失敗したときはRollbackする。
• TRUNCATE
  • Delete - Insertする。失敗してもRollbackしない。

参考：登録方法

忘れっぽいエンジニアのオラクルSQLリファレンス:４種類のロードタイプ（INSERT/APPEND/REPLACE/TRUNCATE）

trailing nullcols

• ここに項目が書かれていると、CSVが空文字のときにNullを指定する。
• 型を指定できる。

指定	型
DECIMAL EXTERNAL	数値（文字列として扱われていても、数値に変換して登録する。）
CHAR	文字列
DATE “YYYYMMDD”	日付とフォーマット

型は指定したいがNULLにしたくない場合は、デフォルト値を書く。

HIREDATE DATE "DD-MM-YYYY" DEFAULTIF HIREDATE = BLANKS "01-01-1900",

参考

テーブル定義

CREATE TABLE EMP (
 EMPNO               NUMBER(4) not null,
 ENAME               varchar2(10),
 JOB                 varchar2(9),
 MGR                 NUMBER(4),
 HIREDATE            DATE,
 SAL                 NUMBER(7,2),
 COMM                NUMBER(7,2),
 DEPTNO              NUMBER(2) );

emp.ctl

options(skip = 1)
load data
infile './emp.csv'
replace
into table emp
fields terminated by ','
optionally enclosed by '"'
trailing nullcols(
  EMPNO DECIMAL EXTERNAL,
  ENAME CHAR,
  JOB CHAR,
  MGR DECIMAL EXTERNAL,
  HIREDATE DATE "YYYYMMDD",
  SAL DECIMAL EXTERNAL,
  COMM DECIMAL EXTERNAL,
  DEPTNO DECIMAL EXTERNAL
)

emp.csv

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902.0,19801217,800,,20
7499,ALLEN,SALESMAN,7698.0,19810220,1600,300.0,30

実行

sqlldr <接続情報> <ctlファイルパス>

※接続情報はsql*plusと同じ。

Timestamp型 -> 秒変換する(EXTRACT)

SQL.md参照。

参考

• DockerでOracle動かしたついでに、公式サンプルデータを突っ込んでから、ER図を自動生成してみようぜ:Zenn

ユーザオブジェクトの削除

Table, View, Index, Sequence… 等を削除する。

-- 重複が発生する。事前に通常のSelectを実行して確認する。
SELECT 'DROP ' || OBJECT_TYPE ||' '|| OBJECT_NAME || ';'
FROM USER_OBJECTS;

参考

• オラクルのオブジェクトを全部削除したいけど、一つずつ書くのが面倒くさい。って場合。:zero0nine

Dockerで環境構築する

git clone https://github.com/oracle/docker-images.git
cd docker-images/OracleDatabase/SingleInstance/dockerfiles/
# 任意のバージョンを選択する。lsして確認。
ORACLE_VERSION=23.5.0
./buildContainerImage.sh -v $ORACLE_VERSION -f

# コンテナ起動
docker run --name oracle-db -e ORACLE_PWD=password oracle/database:$ORACLE_VERSION-free

# コンテナログイン
docker exec -it oracle-db bash

# sqlplus起動
sqlplus system/password@//localhost:1521/FREEPDB1

docker-compose.yml

version: '3'
services:
  oracle-db:
    image: oracle/database:23.5.0-free
    container_name: oracle-db
    port:
      - 1521:1521
    environment:
      - ORACLE_PWD=password
    volumes:
      - ./plsql:/home/oracle/plsql

コンテナ外からの接続

接続できるようになるまで、少し時間がかかる。

項目名	値
認証タイプ	デフォルト
ロール	デフォルト
ユーザ名	system
パスワード	環境変数:ORACLE_PWDの値
ホスト名	localhost
ポート	1521
タイプ	サービス名
サービス名	FREEPDB1

注意点

Oracleに限らないが、コンテナ内でディレクトリに対してファイル作成を行う場合は、ホスト側のディレクトリに書き込み権限をつけること。

chmod 777 ${dir_path}

参考

• DockerでOracle Databaseを構築してみる:Qiita

権限を確認する

SELECT * FROM SYS.DBA_SYS_PRIVS;

expdp/impdp(エクスポート/インポート)

expdp

sqlplus system/password@//localhost:1521/FREEPDB1

create or replace directory BACKUP_DIR as '/home/oracle/datas';
commit;
exit

expdp system/password@//localhost:1521/FREEPDB1 directory=backup_dir dumpfile=test.dmp;

• Dockerコンテナ内でOracleを起動していて、directoryにホストから見えるディレクトリを指定する場合、ホスト側のディレクトリの権限を777にする必要がある。

impdp

impdp system/password@//localhost:1521/FREEPDB1 directory=backup_dir dumpfile=test.dmp;

parファイル

インポート設定ファイル。引数として渡してもインポートできるが、設定ファイルとして外に出しておくのが望ましい。

作成例

directory=DATA_PUMP_DIR
dumpfile=mydumpfile.dmp
logfile=import.log
schemas=MY_SCHEMA
tables=MY_SCHEMA.EMPLOYEES, MY_SCHEMA.DEPARTMENTS
table_exists_action=REPLACE

• table_exists_action
  • SKIP
    • 既存のテーブルがあれば、そのテーブルをスキップしてインポートしません（デフォルト）。
  • APPEND
    • 既存のテーブルに新しいデータを追加（追記）します。
    • エラーになるテーブル、レコードがあった場合、エラーにならない範囲で登録される。
  • TRUNCATE
    • 既存のテーブルのデータを削除（TRUNCATE）してから、データをインポートします。
  • REPLACE
    • 既存のテーブルをドロップしてから、新しくテーブルを作成し、データをインポートします。

パーティショニング

テーブル自体と、パーティショニングの宣言が必要。

レンジ

CREATE TABLE sales (
    sale_id NUMBER,
    product_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date)
(
    PARTITION sales_jan2025 VALUES LESS THAN (TO_DATE('2025-02-01', 'YYYY-MM-DD')),
    PARTITION sales_feb2025 VALUES LESS THAN (TO_DATE('2025-03-01', 'YYYY-MM-DD')),
    PARTITION sales_mar2025 VALUES LESS THAN (TO_DATE('2025-04-01', 'YYYY-MM-DD')),
    PARTITION sales_apr2025 VALUES LESS THAN (TO_DATE('2025-05-01', 'YYYY-MM-DD')),
    -- 他の月のパーティションも同様に追加
    PARTITION sales_dec2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);

リスト

CREATE TABLE sales_by_region (
    sale_id NUMBER,
    region_code VARCHAR2(10),
    sale_date DATE,
    amount NUMBER
)
PARTITION BY LIST (region_code)
(
    PARTITION north VALUES ('NORTH'),
    PARTITION south VALUES ('SOUTH'),
    PARTITION east VALUES ('EAST'),
    PARTITION west VALUES ('WEST')
);

ハッシュ

CREATE TABLE sales_by_hash (
    sale_id NUMBER,
    product_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY HASH (product_id)
PARTITIONS 4;  -- パーティションの数を指定

Oracle Database アーキテクチャ

Oracleデータベースサーバは1つのデータベースと、1つ以上のデータベース・インスタンスで構成される。

• データベース
  • ディスク上に配置された、ユーザ・データを格納する一覧のファイル。データベース・インスタンスとは別に存在できる。
  • Oracle Database 20cから下記をさすようになった。
    • マルチテナント・コンテナ・データベース(CDB)
    • プラガブル・データベース(PDB)
    • アプリケーション・コンテナのデータファイル
• データベース・インスタンス
  • データベース・ファイルを管理する一連の名前付きメモリー構造
  • システムグローバル領域(SGA)と呼ばれる共通領域と、一連のバックグラウンド・プロセスで構成される。
  • データベース・ファイルとは別に存在できる。

参考

• Oracle Databaseアーキテクチャ:Oracle Databaseの概要

ライセンス

OTN

自社内または個人的なアプリケーション開発、テスト、プロトタイプ作成、デモンストレーション目的であれば無償で使用できる。

用語

グローバル・データベース名

リモート接続において、接続先のデータベースを識別するために使用される。

SID

マシン内でデータベースを識別する際に使われる。
ローカル接続で使用される。