Oracle Database 19c でテーブルを作成からの~、PDBにリスナー接続。SQL Developerからの接続も。

f:id:ts0818:20190706203334j:plain

「なくし物かね?(スコット軍曹:映画『ユニバーサル・ソルジャー』)」と、名言が飛び出たところで、はい、どうもボクです。

最近、

gigazine.net

ユニバーサルメモリとは、「高速だが電源供給がなければ記憶内容が消えてしまうRAM」と、「RAMより低速だが電源を切っても記憶内容が消えないフラッシュメモリ」の両方の長所を兼ね備えた半導体メモリです。

メモリとストレージの性質を兼ね備える夢のデバイス「ユニバーサルメモリ」がついに実現か - GIGAZINE

⇧ 「ユニバーサルメモリ」 なるものが!

これは、IoT の促進を加速させるかも~、ただし、IoTは、セキュリティーに関しても、課題が残ってるらしいですね...

というわけで、そんな話とは関係なく、今回は、Oracle Databaseのデータベースにテーブルの作成とリスナー接続の話ですかね。

んでは、レッツトライ~。

 

Oracle Database 19c にテーブルを作成...の前に?

何はともあれ、Javaでデータベース接続しようにもテーブルが存在しなければ、繋げたところですることないじゃん、ということで、テーブルの作成をしていきますか。

なのですが!

sql-oracle.com

Oracle Database が異色な存在なのか、他のRDBMS が特殊なのかは分かりませんが、Oracle Database には、「表領域」というものが存在すると。

Oracleのデータの格納先はテーブルです。では「テーブルはどこに格納されるか?」というと、それが表領域(TABLESPACE)です。表領域とはテーブルが格納される場所です。

表領域は「○○.DBF」としてWindowsエクスプローラーから確認できますよ。表領域は実態ファイル、つまり物理ファイルなんですね。

Oracleの表領域とは | Oracle初心者でもスッキリわかる

⇧  上記サイト様の説明がむちゃくちゃ分かりやすかったです!目からウロコ!

 

表領域があるのってOracle Database だけかと思いきや、

qiita.com

yakst.com

⇧  MySQL 5.6 から、表領域 は存在してたらしい。

他のDBについては、分からんです。

 

んで、表領域を作成したとすると、「ORACLE_HOME/oradata」に表領域が...無いやないか~い!

f:id:ts0818:20190706221430p:plain

と思ったら、場所が違ったらしい。 データベースのログインユーザー名のほうのディレクトリにできてました。

※テーブルスペースを指定しない場合、ユーザのデフォルトテーブルスペースに作成される

ORACLE/オラクルSQLリファレンス(CREATE TABLE)

⇧  ユーザのデフォルトのテーブルスペース、つまり「表領域」ということらしい。

んで、テーブルを作成する際に、事前にテーブルスペースとかを作成しておけば、そのテーブルスペースにテーブルの物理ファイルとかが格納されるようになると。

テーブルスペースを指定しない場合は、ユーザのデフォルトのテーブルスペースに作成されるらしい。

f:id:ts0818:20190706221750p:plain

⇧  ん~...よく分からん...けど、まだ、マルチテナント・コンテナ・データベース(CDB)である、「orcldb_19c」しか存在してないのかな?

「pdbseed」は、プラガブル・データベース(PDB)の雛型のはずなんで、PDBを作成していく感じなのかしら?

って思ったら、「orcldb_19c」ってディレクトリがPDBってことらしい、「ORCLDB」ってディレクトリがCDBに該当するらしい...分かりづらいわ!

この表領域○○.DBFの中に「テーブル」が一杯つまっているのです。そしてその「テーブル」の中に「データ」が一杯つまっています。

  • 「表領域」→「テーブル」→「データ」

この関係性はとても重要です。

Oracleの表領域とは | Oracle初心者でもスッキリわかる 

表領域はDBFファイルという「実態のファイル」ですが、「テーブル」や「データ」はファイルではないのでそこが違いますね。表領域という実態のDBFファイルの中にテーブルやデータが論理情報として保管されています。

Oracleの表領域とは | Oracle初心者でもスッキリわかる

なので、表領域の作成の前に、PDBをまずは作成なのかしらね...テーブル作成までの道のりが分かりづらいかな...

MySQL とかだったら、何にも考えずに、CREATE TABLE文すればOKな気がしたんだけど... 

 

PDB にテーブルを作成...の前に、PDB を作成

Oracle Database 12c から、データベースの構成が変わったみたいですと。

www.intellilink.co.jp

つまり、

  • CDB ← 共通のオブジェクトやメタデータの情報を管理
    • pdbseed ← PDB を作成する際のテンプレート
    • PDB ← データベース。最大252個作成可能

という構成になっているんだと。

なので、この、PDBというデータベースのほうにテーブルを作成していくことになるらしいですと。

その前に、PDB というデータベースを、Oracle Database というインスタンス内に作成する必要があるのですと。

んで、PDB を作成する方法については、Oracle 12c の情報だけど、

docs.oracle.com

⇧  結構、いろいろ種類があると。

今回は、pdbseed を元に作成するということで。

SQL*Plusを使用したPDBの作成および削除

⇧  つまり、こんな感じですね。 

というわけで、まずは、コマンドプロンプトで、Oracle Databaseのインスタンスに接続で。

その前に、自分は、Oracle Database 12c と、Oracle Database 19c を入れているのであった。

f:id:ts0818:20190915170156p:plain

つまり、接続先を指定するにはどうすれば? と思ったけど、そもそも、Oracle Database 12c のほうが起動してなくて、起動しようとすると、

f:id:ts0818:20190915172608p:plain

はい、エラー。

kuttsun.blogspot.com

どうやら、Windowsのログオンパスワードが変更されたため、Jenkinsがサービスを開始できなかったようです。

[Jenkins] エラー 1069: ログオンに失敗したため、サービスを開始できませんでした。|へっぽこプログラマーの備忘録

そういえば、Windowsのログインにパスワードを設けたんだっけ。

パスワードを変えて、

f:id:ts0818:20190915173132p:plain

再度、起動。

f:id:ts0818:20190915172945p:plain

Oracle Database 12c の方も起動されました~。

f:id:ts0818:20190915173040p:plain

Oracle リスナー のほうも同様に。

f:id:ts0818:20190915175440p:plain

f:id:ts0818:20190915175558p:plain

とりあえず、Windows のサービスの状態を一覧表示

f:id:ts0818:20190915173518p:plain

んで、Oracle Database のインスタンスは、

f:id:ts0818:20190915173714p:plain

「OracleServiceORCL」が、PIDの19000 で動いていて、「OracleServiceORCLDB」が、PIDの8804 で動いていると。

Oracle リスナーのほうは、

f:id:ts0818:20190915175728p:plain

こんな感じと。んで、

netstat -nao

すると、プロセスが、どのポートを使用しているか確認できると。

まぁ、でも、結局、Oracle Databaseのインスタンスにログインしてユーザを作成してないと、ポート指定とかの接続が使えないらしい。

 

そんな場合は、使用したい sqlplus.exe を明示的に指定するしかなさそう。たぶん、環境変数にパスを通すのは、1つしかでき無さそうだし。

コマンドプロンプトからsqlplus を利用して接続先のインスタンスを変えたい場合は、フルパス指定しかないのかな、分からんけど。

ちなみに、sqlplus の場所は、「ORACLE_HOME/bin/sqlplus.exe」になりますかね。

f:id:ts0818:20190915190413p:plain

 

sqlplus.exe のフルパスを指定しないで普通に接続すると、19c のほうに接続されると。

f:id:ts0818:20190915165124p:plain

 

まぁ、とりあえず、Oracle Database のインスタンスに接続できたということで。今回は、Oracle Database 19c のほうを利用していきます。

 

PDB にテーブルを作成と思ったけど...

とりあえず、今の接続先を見てみる。

f:id:ts0818:20190915193810p:plain

⇧  CDB に接続されてるらしい。

 んで、PDBはというと、

f:id:ts0818:20190915194604p:plain

 

 「ORCLDB_19C」ってものが作成されていると。Oracle Database をインストールした時に作成したのかも...覚えてないな~、はい、属人化~。って言っても、作った本人も記憶にないという...

 

作成されてない方は、

xn--w8j8bac3czf5bl7e.com

⇧  上記サイト様を参考に作成してみてください。

 

 

テーブル作成で

では、PDB が用意されているということで、テーブル作成しちゃいますか。

その前に、PDBの状態が、「MOUNTED」ってなっている場合は、PDB が起動していないので、起動する必要があるようです。

replication.hatenablog.com

f:id:ts0818:20190915200048p:plain

⇧  起動されていませんね。

そんでは、起動で。

alter pluggable database [pdbの名前] open;

f:id:ts0818:20190915200557p:plain

PDB が起動しました~。

そんでは、PDBに接続します。

alter session set container = [pdbの名前];

f:id:ts0818:20190915200821p:plain

 

そんでは、まずは、外部から接続する際のユーザを作成しておきましょう。

CREATE USER [ユーザ名] IDENTIFIED BY [パスワード];

f:id:ts0818:20190915201819j:plain

ユーザが作成されたか確認。

SELECT USERNAME,PASSWORD FROM DBA_USERS;

f:id:ts0818:20190916174401p:plain

f:id:ts0818:20190916174301p:plain

⇧  ユーザは作成されたようです。

 

作成したユーザに権限を付与します。なぜならば、権限がないと何もできないからみたいです。
権限をまとめた、role というものも存在するそうですが、

nkurilog.blogspot.com

注意:
CONNECTおよびRESOURCEロールは、将来のOracle Databaseのリリースで非推奨になる予定のため、使用しないでください。 CONNECTロールが現在保持している権限は、CREATE SESSIONのみです。

認可: 権限、ロール、プロファイルおよびリソースの制限 - Oracle Database セキュリティ・ガイド 10g リリース2(10.2)

となっているようです。

んで、権限には、

www.saka-en.com

「システム権限」と「オブジェクト権限」が存在するらしい。role は、それらをある程度まとめたものらしい。まとめて権限を付与したい場合に、role を使えば良いのかもしれないけど、どの role に、どんな権限が含まれているのか分からんしな~、福袋じゃないんだからね~。

まぁ、とりあえず、Java から接続できるようにしたいので、

  • データベースへの接続
  • テーブルへの、SELECT、INSERT、UPDATE、DELETE

ぐらいは、最低限できるようにしたいんだが。あと、テーブルの作成・削除の権限も付けときますか。

ちょっと、付与する権限が多くなりそうなんで、SQLファイルで実行しますか。適当にSQLファイルを作成。

f:id:ts0818:20190916165852p:plain

内容は、こんな感じで。(ユーザ名が、ts0818 って場合。ユーザ名は、ご自分の環境に合わせてください。)

GRANT CREATE SESSION, 
      CREATE SYNONYM, 
      CREATE ANY INDEXTYPE, 
      CREATE ANY OPERATOR, 
      CREATE ANY PROCEDURE, 
      CREATE ANY MATERIALIZED VIEW, 
      CREATE ANY SEQUENCE,  
      CREATE ANY TRIGGER, 
      CREATE ANY TYPE, 
      CREATE ANY VIEW, 
      CREATE DATABASE LINK, 
      CREATE ANY TABLE, 
      INSERT ANY TABLE, 
      SELECT ANY TABLE, 
      UPDATE ANY TABLE, 
      DELETE ANY TABLE, 
      DEBUG ANY PROCEDURE, 
      DEBUG CONNECT SESSION, 
      EXECUTE ANY LIBRARY, 
      EXECUTE ANY PROCEDURE, 
      UNLIMITED TABLESPACE, 
      EXP_FULL_DATABASE, 
      IMP_FULL_DATABASE, 
      ALTER ROLLBACK SEGMENT, 
      ALTER TABLESPACE 
TO ts0818;

そしたらば、SQLファイルを実行で。

@[SQLファイルまでのパス]

f:id:ts0818:20190916170247p:plain

そんでは、一旦、ログアウトして、作成したユーザでログインし直します。

sqlplus [ユーザ名]/[パスワード] as sysdba;

f:id:ts0818:20190916194643j:plain

 

ちなみに、PDBが起動しているのであれば、直接、PDBにログインすることもできるみたいです。

sqlplus [ユーザ名]/[パスワード]@[ホスト]:[Oracleリスナーのポート]/[PDBインスタンス];

f:id:ts0818:20190916195102j:plain

⇧  接続先が、PDBになっていますね。

んで、アプリケーションで使用するテーブルの作成をしていきたいんだけど、表領域を新しく作って、そこにテーブルを作成すべきなのかが分からない...このへんの情報が見つから無いのよね...
しかも、表領域を作成 って、

shimi-dai.com

表領域作成後に変更ができないパラメータが多く存在するため作成SQLを間違えないようにしましょう。

表領域の作成方法と作成時の注意点を紹介 | shimidai2100

 

xn--w8j8bac3czf5bl7e.com

⇧  上記サイト様によりますと、相当、考慮しなけりゃいけないことが満載らしいという...まじか...

まぁ、でも、よっぽど大きなサイズのカラムを持つテーブルを作成するっていうんでなければ、大丈夫らしいけど...って、表領域は...作りますか。

テーブルは、Oracle Database 19c を使っているということで、

cosol.jp

⇧  上記サイト様で紹介されている、JSON機能 を利用できるようなテーブルを作成していきますか。

んで、まずは、表領域からなんだけど、 

  • extent management
    →エクステント管理方式とエクステントサイズを指定
    • エクステント管理方式
      • ローカル管理方式
      • ディクショナリ管理方式
    • エクステントサイズ
      • UNIFORM ALLOCATE
      • AUTO ALLOCATE
  • segment space management
    →セグメント領域管理方式を指定
    • AUTO
    • MANUAL
  • autoextend on next XXX maxsize XXX→データファイルの自動拡張
  • bigfile→BIGFILEのデータファイルを指定
    • BIGFILE
    • SMALLFILE

と、指定しといたほうが良いのが、4つぐらいあるみたいですね。

んで、表領域って、CDB、PDB のどっちに作れば?

dbalone.com

⇧  上記サイト様によりますと、PDB に作れば問題なさそうです。

そんでは、SQLファイルを用意。

f:id:ts0818:20190923154124p:plain

ファイルの中身はこんな感じ。

-- 表領域の作成
CREATE TABLESPACE TEST
  DATAFILE 'C:\app02\app\ts0818\oradata\ORCLDB\orcldb_19c\test01.dbf' SIZE 100M REUSE
  AUTOEXTEND ON NEXT 500K MAXSIZE UNLIMITED
  EXTENT MANAGEMENT local UNIFORM size 1m
  SEGMENT SPACE MANAGEMENT AUTO;

-- テーブルの作成
-- ユーザ一覧
CREATE TABLE USERS (
  user_id             CHAR(13) NOT NULL,
  insert_user         VARCHAR(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  primary key(user_id)
)
TABLESPACE TEST;

-- ユーザの退会履歴
CREATE TABLE USER_LEAVE (
  user_id             CHAR(13) NOT NULL,
  insert_user         VARCHAR(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  primary key(user_id)
)
TABLESPACE TEST;

-- ユーザの詳細
CREATE TABLE USER_DETAIL (
  user_id             CHAR(13) NOT NULL,
  last_name           VARCHAR(255) NOT NULL,
  first_name          VARCHAR(255) NOT NULL,
  gender              VARCHAR(50) NOT NULL,
  birthday            DATE NOT NULL,
  address_id          CHAR(20),
  insert_user         VARCHAR(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  update_user         VARCHAR(50),
  update_date         TIMESTAMP,
  delete_flg          CHAR(1) NOT NULL, -- 0:未削除、1:削除
  delete_date         TIMESTAMP,
  primary key(user_id),
  foreign key(user_id) references USERS(user_id)
)
TABLESPACE TEST;

-- 住所の詳細
CREATE TABLE ADDRESS_DETAIL (
  address_id          CHAR(20) NOT NULL,
  post_code           CHAR(7) NOT NULL,
  prefecture          VARCHAR2(50) NOT NULL, -- 都道府県
  cities              VARCHAR2(50) NOT NULL, -- 市区町村
  address_01          VARCHAR2(255) NOT NULL, -- ●(丁目)-●●(番地)-●●●(号)
  address_02          VARCHAR2(255),          -- 建物名など
  room_number         NUMBER,            -- 部屋番号など
  floor               NUMBER,            -- 階数
  user_id             CHAR(13) NOT NULL, -- ユーザID
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  update_user         VARCHAR2(50),
  update_date         TIMESTAMP,
  primary key(address_id, user_id)
)
TABLESPACE TEST;

-- ユーザの購買履歴
CREATE TABLE ORDERS (
  order_id            CHAR(13) NOT NULL,
  order_date          DATE NOT NULL,
  user_id             CHAR(13),
  json_data_first     CLOB CHECK (json_data_first IS JSON (STRICT)),  -- 購入した商品の一覧(初回)
  json_data_modify    CLOB CHECK (json_data_modify IS JSON (STRICT)), -- 購入した商品の一覧(確定)
  trade_id            CHAR(1),
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  update_user         VARCHAR2(50),
  update_date         TIMESTAMP,
  delete_flg          CHAR(1) NOT NULL, -- 0:未削除、1:削除
  delete_date         TIMESTAMP,
  primary key(order_id, order_date, user_id)
)
TABLESPACE TEST;

-- 購買履歴の詳細
CREATE TABLE ORDER_DETAIL (
  trade_id            CHAR(13) NOT NULL,
  order_date          DATE NOT NULL,
  user_id             CHAR(13),
  product_id          CHAR(20) NOT NULL,
  trade_count         NUMBER NOT NULL,
  trade_price         DECIMAL NOT NULL,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  update_user         VARCHAR2(50),
  update_date         TIMESTAMP,
  delete_flg          CHAR(1) NOT NULL, -- 0:未削除、1:削除
  delete_date         TIMESTAMP
)
TABLESPACE TEST;

-- 取引状態
CREATE TABLE ORDER_STATUS (
  order_id            CHAR(20) NOT NULL,
  order_date          DATE NOT NULL,
  user_id             CHAR(13) NOT NULL,
  trade_count_total   DECIMAL NOT NULL, -- 取消件数
  cancel_count_total  DECIMAL NOT NULL, -- 売上金額
  trade_price_total   DECIMAL NOT NULL, -- 売上金額
  cancel_price_total  DECIMAL NOT NULL, -- 取消金額
  trade_status        CHAR(1), -- 0:売上、1:取消、2:返品
  trade_date          TIMESTAMP,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  primary key(order_id, order_date, user_id),
  foreign key(order_id, order_date, user_id) references ORDERS(order_id, order_date, user_id)
)
TABLESPACE TEST;

-- 商品の一覧
CREATE TABLE PRODUCT (
  product_id          CHAR(20) NOT NULL,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  primary key(product_id)
)
TABLESPACE TEST;

-- 商品の詳細
CREATE TABLE PRODUCT_DETAIL (
  product_id          CHAR(20) NOT NULL,
  product_name        VARCHAR2(255) NOT NULL,
  product_price       DECIMAL NOT NULL,
  product_text        VARCHAR2(255) NOT NULL,
  category_id         CHAR(3) NOT NULL,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  update_user         VARCHAR2(50),
  update_date         TIMESTAMP,
  delete_flg          CHAR(1) NOT NULL, -- 0:未削除、1:削除
  delete_date         TIMESTAMP,
  primary key(product_id),
  foreign key(product_id) references PRODUCT(product_id)
)
TABLESPACE TEST;

-- カテゴリーの一覧
CREATE TABLE CATEGORY (
  category_id         CHAR(3) NOT NULL,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  primary key(category_id)
)
TABLESPACE TEST;

-- カテゴリーの一覧
CREATE TABLE CATEGORY_DETAIL (
  category_detail_id  CHAR(3) NOT NULL,
  category_name       VARCHAR2(255) NOT NULL,
  category_text       VARCHAR2(255) NOT NULL,
  category_id         CHAR(3) NOT NULL,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  update_user         VARCHAR2(50),
  update_date         TIMESTAMP,
  delete_flg          CHAR(1) NOT NULL, -- 0:未削除、1:削除
  delete_date         TIMESTAMP,
  primary key(category_detail_id),
  foreign key(category_id) references CATEGORY(category_id)
)
TABLESPACE TEST;

そんでは、SQLを実行で。何回かやり直した影響で、表領域の作成でエラーって出てますが、テーブル自体は作成できたようです。

f:id:ts0818:20190925232522j:plain



表領域のファイルも作成されたようです。

f:id:ts0818:20190923173916p:plain

 

Oracle SQL Developer で確認

そんでは、実際にテーブルができてるか確認するために、Oracle SQL Developer っていうGUIツールを導入していきますか。

ちなみに、SQL Developer を実行するには、JDK 8 以上がPCにインストールされている必要があります。

https://www.oracle.com/technetwork/jp/developer-tools/sql-developer/downloads/index.html にアクセスで。 

www.oracle.com

「ライセンスに同意する」にチェックします。

f:id:ts0818:20190923192509p:plain

そしたらば、今回は、Oracle Database 19c で使うSQL Developerをダウンロードしたいので、SQL Developer 19.1の「Windows 64-bit with JDK 8 included」ってのの「Download」で。(事前にOracleアカウントでログインしておきましょう。)

f:id:ts0818:20190923192628p:plain

zipファイルがダウンロードできましたら、 展開していきます。

f:id:ts0818:20190923193456p:plain

展開場所は、

SQL Developerを既存のORACLE_HOMEにインストールしないでください。Oracle Universal Installerを使用して削除することができなくなります。

また、既存のsqldeveloperフォルダまたはディレクトリにSQL Developerをインストールしないでください。最初に既存のsqldeveloperフォルダまたはディレクトリを削除するか、異なる場所に新しいバージョンのSQL Developerをインストールしてください。

Oracle SQL Developerのインストール - リリース4.1 E62046-03

⇧  上記以外なら、問題ないらしい。

以前のリリース(Early Adopter)バージョンのSQL Developerを使用している場合で、正式版のリリース・キットをインストールした後も、この以前のリリース・バージョンを使用し続ける場合は、以前のリリース・バージョンに使用したディレクトリとは別のディレクトリに正式版のリリース・キットを解凍する必要があります

Oracle SQL Developerのインストール - リリース4.1 E62046-03

⇧  いろいろややこしい。

仕方ないので、デスクトップに適当なディレクトリを作成して展開しました。

f:id:ts0818:20190923195653p:plain

f:id:ts0818:20190923195807p:plain

展開できたようなので、「sqldeveloper.exe」をダブルクリックで。 

f:id:ts0818:20190923200603p:plain

f:id:ts0818:20190923200707p:plain

前に使ってたSQL Developerの設定を引き継ぐ場合は「はい」。今回は「いいえ」で。

f:id:ts0818:20190923200916p:plain

まぁ、「OK」で大丈夫かと。

f:id:ts0818:20190923201054p:plain

f:id:ts0818:20190923201204p:plain の▼アイコンをクリックで、「新規データベース接続...」を選択。

f:id:ts0818:20190923201143p:plain

んで、接続できないと。

f:id:ts0818:20190923205654p:plain

まじで、sqlplus では接続できるのに、SQL Developer になったとたんに接続できないとか止めて欲しい。

リスナーとかもちゃんと動いてるし。

f:id:ts0818:20190923224615p:plain

f:id:ts0818:20190923224703p:plain

ホストが、IPアドレスじゃなくて、コンピューター名って...

んで、リスナーは起動しているのに、リスナー接続できないんだが...

何回やっても、何回やっても~、エアーマンが倒せないよ~♪って...

「ORA-01017:ユーザー名/パスワードが無効です。ログオンは拒否されました。」って...ユーザーは作成しとるわ、この大うつけが!

f:id:ts0818:20190925234004j:plain

はい、取り乱しました、すみませんでした。

んで、1週間ぐらいネットサーフィンして調査したりと、むちゃくちゃ泥沼にハマった挙句、理由はよく分からんのだけれど、PDBに作成したユーザだと、CDBにはリスナー接続できないらしい...そんなん知らんし。

まじで、あたいの時間を返して欲しいザマス(涙)。

totech.hateblo.jp

⇧  上記サイト様によりますと、tnsnames.ora ファイルに、PDBへの接続情報を追記すれば良いらしい。

Oracle Database をインストールした場所にもよりますが、『ORACLE_HOME/network/admin/tnsnames.ora』にあります。自分の場合は、『"C:\app02\oracle\product\19.0.0\dbhome_1\network\admin\tnsnames.ora"』になります。

CDBへの接続情報しかないですが、

f:id:ts0818:20190925225201p:plain

PDBへの接続情報を追記します。

f:id:ts0818:20190925225130p:plain

んで、リスナー接続できました。(もちろん、PDBが起動している必要はあります。)

sqlplus [ユーザ]/[パスワード]@[tnsnames.oraに追記したPDBのSERVICE_NAME]    

f:id:ts0818:20190925225802j:plain

 

試せてないんだけど、

qiita.com

⇧  上記サイト様が仰るように、共通ユーザーなるものをCDBに作成すれば、CDBにリスナー接続できるのかもしれない。

 

SQL Developerでも接続してみます。

f:id:ts0818:20190925230801p:plain

接続情報を入力し、「テスト(T)」で。『ステータス:成功』となればOK。

今度は、実際に「接続(O)」で。パスワード入力を求められるので、入力で「OK」。

f:id:ts0818:20190925230941p:plain

PDBに接続できました~。作成したテーブルが確認できましたね。

f:id:ts0818:20190925231111p:plain

次回は、Eclipseで、Javaプロジェクトから、DB接続して参りたいと思います。

結局、今回も、CDBにリスナー接続できるユーザを作成できるのか分からないし、PDBに直接リスナー接続するのが正しいのか、よく分からず、モヤモヤ感しか残りませんでしたが... 

今回はこのへんで。

 

NG集

間違ったSQLを実行してしまった時の現象です。 SQL初心者なんだもん、許してちょんまげ。ということで、私のように、ウッカリ八兵衛的に、こんな現象に出会ったらご注意をば。

 

-- 表領域の作成
CREATE TABLESPACE TEST
  DATAFILE 'C:\app02\app\ts0818\oradata\ORCLDB\orcldb_19c\test01.dbf' SIZE 100M REUSE
  AUTOEXTEND ON NEXT 500K MAXSIZE UNLIMITED
  EXTENT MANAGEMENT local UNIFORM size 1m
  SEGMENT SPACE MANAGEMENT AUTO;

-- テーブルの作成
-- ユーザ一覧
CREATE TABLE USERS (
  user_id             CHAR(13) NOT NULL,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  primary key(user_id)
);

-- ユーザの退会履歴
CREATE TABLE USER_LEAVE (
  user_id             CHAR(13) NOT NULL,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  primary key(user_id)
);

-- ユーザの詳細
CREATE TABLE USER_DETAIL (
  user_id             CHAR(13) NOT NULL,
  last_name           VARCHAR2(255) NOT NULL,
  first_name          VARCHAR2(255) NOT NULL,
  gender              VARCHAR2(50) NOT NULL,
  birthday            DATE NOT NULL,
  address_id          CHAR(20),
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  update_user         VARCHAR2(50),
  update_date         TIMESTAMP,
  delete_flg          CHAR(1) NOT NULL, -- 0:未削除、1:削除
  delete_date         TIMESTAMP,
  primary key(user_id),
  foreign key(user_id) references USERS(user_id)
);

-- 住所の詳細
CREATE TABLE ADDRESS_DETAIL (
  address_id          CHAR(20) NOT NULL,
  post_code           CHAR(7) NOT NULL,
  prefecture          VARCHAR2 NOT NULL, -- 都道府県
  cities              VARCHAR2 NOT NULL, -- 市区町村
  address_01          VARCHAR2 NOT NULL, -- ●(丁目)-●●(番地)-●●●(号)
  address_02          VARCHAR2,          -- 建物名など
  room_number         NUMBER,            -- 部屋番号など
  floor               NUMBER,            -- 階数
  user_id             CHAR(13) NOT NULL, -- ユーザID
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  update_user         VARCHAR2(50),
  update_date         TIMESTAMP,
  primary key(address_id, user_id)
);

-- ユーザの購買履歴
CREATE TABLE ORDERS (
  order_id            CHAR(13) NOT NULL,
  order_date          TIMESTAMP,
  json_data           CLOB CHECK (data IS JSON (STRICT)), -- 購入した商品の一覧
  user_id             CHAR(13),
  trade_id            CHAR(1),
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  update_user         VARCHAR2(50),
  update_date         TIMESTAMP,
  delete_flg          CHAR(1) NOT NULL, -- 0:未削除、1:削除
  delete_date         TIMESTAMP,
  primary_key(order_id, order_date, user_id)
);

-- 購買履歴の詳細
CREATE TABLE ORDER_DETAIL (
  trade_id            CHAR(13) NOT NULL,
  product_id          CHAR(20) NOT NULL,
  trade_count         NUMBER NOT NULL,
  trade_price         BIGDECIMAL NOT NULL,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  update_user         VARCHAR2(50),
  update_date         TIMESTAMP,
  delete_flg          CHAR(1) NOT NULL, -- 0:未削除、1:削除
  delete_date         TIMESTAMP,
  foreigh key(trade_id) references ORDERS(order_id)
);

-- 取引状態
CREATE TABLE ORDER_STATUS (
  order_id            CHAR(20) NOT NULL,
  trade_date          DATE NOT NULL,
  trade_total         BIGDECIMAL NOT NULL,
  trade_status        CHAR(1), -- 0:未購入、1:購入済、2:キャンセル
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  primary key(trade_id, trade_date),
  foreigh key(trade_id) references ORDERS(order_id)
);

-- 商品の一覧
CREATE TABLE PRODUCT (
  product_id          CHAR(20) NOT NULL,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  primary key(product_id)
);

-- 商品の詳細
CREATE TABLE PRODUCT_DETAIL (
  product_id          CHAR(20) NOT NULL,
  product_name        VARCHAR2 NOT NULL,
  product_price       BIGDECIMAL NOT NULL,
  product_text        VARCHAR2 NOT NULL,
  category_id         CHAR(3) NOT NULL,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  update_user         VARCHAR(50),
  update_date         TIMESTAMP,
  delete_flg          CHAR(1) NOT NULL, -- 0:未削除、1:削除
  delete_date         TIMESTAMP,
  primary_key(product_id),
  foreign key(product_id) references PRODUCT(product_id)
);

-- カテゴリーの一覧
CREATE TABLE CATEGORY (
  category_id         CHAR(3) NOT NULL,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  primary_key(category_id)
);

-- カテゴリーの一覧
CREATE TABLE CATEGORY_DETAIL (
  category_detail_id  CHAR(3) NOT NULL,
  category_name       VARCHAR2 NOT NULL,
  category_text       VARCHAR2 NOT NULL,
  category_id         CHAR(3) NOT NULL,
  insert_user         VARCHAR2(50) NOT NULL,
  insert_date         TIMESTAMP NOT NULL,
  update_user         VARCHAR2(50),
  update_date         TIMESTAMP,
  delete_flg          CHAR(1) NOT NULL, -- 0:未削除、1:削除
  delete_date         TIMESTAMP,
  primary_key(category_detail_id),
  foreign key(category_id) references CATEGORY(category_id)
);

を実行して、はい、エラー。

f:id:ts0818:20190923160423p:plain

 

ht-jp.net

とりあえず目を皿のようにして SQL文を舐めるように見直したのですが、何度数えなおしてもカッコの数は合ってます。あるえ?

とはいえ、受け付けてくれないのでは先に勧めないので、Webで検索してみたところ、なんとこのメッセージ、右カッコが無い場合以外でも出るみたいじゃないですか!

例えば、

  • DEFAULT句が NOT NULL の後にある
  • 不要な箇所にカンマが入っている
  • カラムの型の桁数を指定しない
  • ON とか AND が正しくない使われ方をしている
  • CASE文の中で WHEN が抜けている
  • 副問い合わせに order by が入っている
  • クォーテーションが正しく使われていない

などなど。カッコ関係ないじゃん!

Oracleはカッコがあっても 「ORA-00907: 右カッコがありません。」 - 知らないことがあってもへっちゃらさ

⇧  上記サイト様によりますと、もはや、Oracleのエラーメッセージが適当過ぎて、どの現象が起こってるのか特定できないという...いや~、Oracleさん、流石です。

で、

知ってる人は気づくけど、知らない人は気づくまでに時間がかかる罠・・・

VARCHAR2には、バイト数か文字数を指定する必要がありましたw

Oracleで「ORA-00906: 左カッコがありません。」のエラーが出た時の対処法 |

と。

そして、Oracle Database は、MySQLみたいに、テーブルが存在したら削除するってのが、簡単にはできないらしい(涙)。

qiita.com

akabanessa.blog73.fc2.com

⇧  上記サイト様を参考にさせていただきました。

set serverout on

declare

    w_num_dropped PLS_INTEGER := 0;

    procedure my_drop_table(in_table_name IN VARCHAR2)
    is
        w_num_tables PLS_INTEGER;
    begin
        select count(table_name) into w_num_tables from user_tables where table_name = in_table_name;
        if (w_num_tables > 0) then 
            execute immediate 'drop table ' || in_table_name || ' CASCADE CONSTRAINTS';
--          dbms_output.put_line('drop ' || in_table_name || ' was succeeded.');
            w_num_dropped := w_num_dropped + 1;
        end if;
    end my_drop_table;
begin

    --以下、削除したいテーブルを並べておく
    my_drop_table('USERS');
    my_drop_table('USER_LEAVE');
    my_drop_table('USER_DETAIL');
    my_drop_table('ADDRESS_DETAIL');    
    my_drop_table('ORDERS');
    my_drop_table('ORDER_DETAIL');    
    my_drop_table('ORDER_STATUS');
    my_drop_table('PRODUCT');
    my_drop_table('PRODUCT_DETAIL');
    my_drop_table('CATEGORY');
    my_drop_table('CATEGORY_DETAIL');
            
    dbms_output.put_line(to_char(w_num_dropped) || '個のテーブルを削除しました');

end;
/

んで、実行。

f:id:ts0818:20190923165531p:plain

なんか、コマンドプロンプトだけなのかしもらんが、SQLファイルを保存する際に、文字コードSJIS で保存しておかないと、日本語が文字化けしてしまいます。

そして、

外部キー(フォーリンキー)(参照整合性制約)。テーブルの値が、別のテーブルに存在している値であることを保証する。

この制約を付けるとデータを作成する順序(どのテーブルから先にデータを入れないといけないか)を考えなければならないし、テストデータを作る際に 実際に使いたいのとは無関係なテーブルにもデータを入れる必要が出てきたりするので、あえて外部キー制約を付けないことも多い(それで良いのか悪いのかは別として)。

外部キー制約は、目的の外部テーブルのユニークキー(普通はプライマリキー)に対してしか指定できない。

たまに「コード」「開始日」「終了日」をキーに持ち、「(コード=) AND (当日 BETWEEN 開始日 AND 終了日)」で一意になるような意図のテーブル(コードに対する有効期間・履歴を持つ)が設計されるが、この場合、ユニークキーが「コード」と「開始日」なら 「コード」のみで外部キー制約とすることは出来ない。
「開始日」まで含むとしても、「コード」と「開始日」そのものがぴったり一致する制約ということになってしまうので、たぶん望んだ結果ではないだろう。

オラクル整合性制約メモ(Hishidama's Oracle constraint Memo)

と、複合主キーに対する外部キーは、項目数を合わせないといけないらしい...