前の職場の先輩に教えていただいた、Avicii(本名:ティム・バークリング) というDJの方が2018/4/20(金)に亡くなったそうな...ご冥福をお祈り申し上げます。
⇧ 若いのに才能あふれるAviciiのルールを少しでも真似して頑張っていきたいですね。
前回までで、仮想マシン(CentOS7.4)内のOracle Database 12c Release 2 (12.2.0.1.0) に対して、EclipseからもDBViewerでJDBC接続ができました。
今回は、JavaプログラミングからのDB接続へトライしていくためのテーブルの作成を行っていきたいと思います。(本当は、Javaからの接続まで行いたかったのですが、時間の都合上、今回はテーブル作成までで。)
⇧ 上記サイト様を参考に、まずはテーブルの作成などを行っていきたいと思います。
今回もハマりにハマっています(涙)。
毎度毎度、情報を見つけるまでの時間の取られっぷりを考えると、 疲れてきますね....。参考書とか買いたいですね、どの参考書を買って良いかが分からんですが。
テーブルの作成
Oracle Databaseでは、テーブルの作成・削除(CREATE、DROPなど)は管理者みたいな権限のユーザーが担当して、テーブルの操作(SELECT、UPDATE、INSERT、DELETEなど)は、別のユーザーを作成して任せるのが良いようです。
ということで、まずは、仮想マシンをheadlessモードのオプションで起動し、
VBoxManage startvm [仮想マシン名または仮想マシンID] [オプション]
ssh oracle@[仮想マシンのホスト名またはIPアドレス]
sysdba権限でsysユーザーにてDBにログイン。
sysdba / as sysdba
startup
後の祭りですが、ここからは盛大に間違ったことを行っていたっぽいです。
そんでは、テーブルの作成ですかね。(⇐ CDBにテーブル作るのは間違いっぽい、たぶん。)
create table book_authors ( id NUMBER, author_last_name VARCHAR(100), author_first_name VARCHAR(100), author_last_name_kana VARCHAR(100), author_first_name_kana VARCHAR(100), author_birthplace VARCHAR(100), author_birthday DATE, author_history VARCHAR2(4000), primary key(id) );
create table books ( id NUMBER, book_name VARCHAR(100), book_name_kana VARCHAR(100), author_id NUMBER, book_timestamp DATE, primary key(id), foreign key(author_id) references book_authors(id) );
create table reading_record ( id NUMBER, book_id NUMBER, reading_date DATE, primary key (id) );
データはまだ空の状態ですね。
テーブルを操作するためのユーザを作成したいけど...
Oracle Database 12c から、ユーザー作成の方法が変わったみたいです。
Oracle Database 12c で利用できるようになったOracle Multitenantという機能を使っている場合、データベースの構成が、
という感じになってるらしく、PDBに接続(ログイン)していないとユーザーの作成が行えないようです。
※Oracle Database 12c でも、マルチテナントでデータベースを作成していなければ、12c以前の方法でユーザー作成ができるようです。
というわけで、いま、どっちに接続してるのか確認します。
show con_name;
⇧ CDB に接続されてしまっています。
PDBに接続していかねばならないのですが、PDBが起動してない場合があるので、状態を確認します。
select name, open_mode from v$pdbs;
v$pdsについては、
V$PDBS
ビューは、現在のデータベース・インスタンスに関連付けられたPDBに関する情報を提供します。共通ユーザーは、現在のコンテナがルートまたはPDBである場合にこのビューを問い合せることができます。現在のコンテナがPDBである場合、このビューには現在のPDBに関する情報のみが表示されます。
⇧ Oracleの説明では、となっていますね。
⇧ Oracleの説明では、Seed(PDB$SEED)は、PDBを作成するための雛型(テンプレート)であるって言ってますね。
ということは、CDBに存在するPDB一覧を確認してSeed(PDB$SEED)しか表示されないということは、自分の環境では、まだPDBは作成されていないってことっぽいですね。
PDB(プラガブル・データベース)の新規作成
というわけで、作成していきます。
⇧ Oracleの説明によりますと、
SQL*Plusを使ってPDBを作成する方法として、CREATE PLUGGABLE DATABASE文を使用してPDBを作成するそうですが、
の4つの方法があって、それぞれでCREATE PLUGGABLE DATABASE文を実行するときの「句」が変わってくるようです。
PDB作成の方法 | 対応する句 |
---|---|
シードを使用したPDBの作成 | create_pdb_from_seed句 |
既存のPDBまたは非CDBのクローニングによるPDBの作成 | create_pdb_clone句 |
CDBへの切断されたPDBの接続によるPDBの作成 | create_pdb_from_xml句 |
非CDBを使用したPDBの作成 | create_pdb_from_xml句 |
で、構文が
となっていて、 今回利用しようと考えてるcreate_pdb_from_seed句の説明が、
「図create_pdb_from_seed.gif」の説明
⇧ リンク先にSQLの構文が表示されていて、
⇩ それが、こちら。
ADMIN USER admin_user_name IDENTIFIED BY password [ pdb_dba_roles ] [ default_tablespace ] [ file_name_convert ] [ pdb_storage_clause ] [ path_prefix_clause ] [ tempfile_reuse_clause ] [ user_tablespaces_clause ] [ standbys_clause ] [ logging_clause ] [ create_file_dest_clause ]
となっているんですが、具体的にどんな値を指定すれば良いとかが全然載っていない...安定の不親切さ。
ちなみに、SQLの構文を表現した図のことを「構文図」というようです、まんまやんけ~。
で、 create_pdb_from_seed句ですが、
ADMIN USER admin_user_name IDENTIFIED BY password
が最低限あれば良いらしく、
CREATE PLUGGABLE DATABASE [作成するPDB名] ADMIN USER [作成するPDBの管理者ユーザー名] IDENTIFIED BY [作成するPDBの管理者ユーザーのパスワード]
という構成になっていればOKのようです。
では、いざ、PDBを新規作成。
CREATE PLUGGABLE DATABASE diary ADMIN USER diary_adm IDENTIFIED BY pass;
追加されてるか確認。
select name, open_mode from v$pdbs;
⇧ 追加されています。
PDB(プラガブル・データベース)の起動と接続
新しく作成したPDBの状態が、MOUNTEDモードとなっているので、READ WRITEモードに変更する必要があります。
⇧ Oracleの説明によりますと、
PDBのモード | 説明 |
---|---|
OPEN READ WRITE | 読取り/書込みオープン・モード。問合せおよびユーザー・トランザクションを実行でき、ユーザーはREDOログを生成できます。 |
OPEN READ ONLY | 読取り専用オープン・モード。問合せは実行できますが、ユーザー変更を実行することはできません。 |
OPEN MIGRATE | 移行オープン・モード。PDBでデータベース・アップグレード・スクリプトを実行できます。 |
MOUNTED | マウント・モード。どのオブジェクトに対する変更も許可されず、データベース管理者のみがアクセスできます。データベース・ファイルの読取りまたは書込みができません。PDBに関する情報は、メモリー・キャッシュから削除されます。PDBのコールド・バックアップを実行できます。 |
4つのモードがあるようです。
で、ややこしいのが、
FORCE
キーワードを指定する場合を除き、PDBのオープン・モードを読取り専用または読取り/書込みに変更するには、マウント・モードである必要があります。
ってなっていて、FORCEキーワードを使わない場合の「READ WRITE モード」への変更の仕方については 記載がないという。
他サイト様の情報を信じて、
ALTER PLUGGABLE DATABASE [PDB名] OPEN;
⇧ 変更されました。「READ WRITE モード」に変更したPDBに接続したいと思います。
ALTER SESSION SET CONTAINER = diary;
ユーザー(ローカル・ユーザー)の作成
新たにユーザー(ローカル・ユーザー)を作成します。
-- CREATE USER ユーザ名 IDENTIFIED BY パスワード; CREATE USER ts0818 IDENTIFIED BY ppp_pwd;
⇧ コマンドプロンプトがバグって表示がおかしくなってるけど、ユーザー(ローカル・ユーザー)が作成されました。
ロールとは、ユーザーに権限を素早く簡単に付与するために便利なものです。Oracle Databaseで定義されているロールを使用することもできますが、必要な権限のみを含む独自のロールを作成すると、より継続的な制御が可能になります。Oracle Database定義ロールの権限は変更または削除される場合があります。たとえば CONNECT
ロールの場合、現在持っている権限は CREATE SESSION
権限のみです。以前は、CONNECT
ロールには他に8個の権限がありました。
新たに作成したユーザー(ローカル・ユーザー)に、作成したPDBへ接続(ログイン)する権限(CONNECTロール)を付与します。
GRANT CONNECT TO [PDBに作成したユーザー名];
で、作成したテーブルへの操作権限を与えようとして、エラー。
「ORA-00942:表またはビューが存在しません。」って言われても、表(テーブル)作成しましたけど?
調べてるけど情報が見つからず...駄目だ、翌日に持ち越しで。
迷走
で 、CDB接続時は、作成したテーブルが存在していると。いますね。
DBA_OBJECTSはデータベースの全オブジェクトを確認することができる。 オブジェクトとは表、索引、プロシージャ等、データベースに作成されるものとなる
⇧ 上記サイト様によると、DBA_OBJECTSには、表(テーブル)も含まれると。
⇩ 見つからず...
と思ったら、ルールがあったらしい...
- OBJECT_NAMEを検索条件とする場合、必ず大文字で指定してください。
- DBA_OBJECTSを検索する場合は、すべてのOBJECTを参照する権限が必要です。
- USER_OBJECTSは、「obj」シノニムでもアクセス可能です。
(例)SELECT * FROM obj;
⇧ 上記サイト様を参考に、大文字にしたところ、存在してるようです...大文字ルールとか言われてもね...。
そんでは、作成していたPDB(ここではDIARY、何故かテーブルの情報は大文字になって表示されますが)に接続(ログイン)します。
接続先がCDBからPDBに切り替わってますね。
はい、エラー。「ORA-00942:表またはビューが存在しません。」。
大文字にしたけど、結果は変わらず。
ユーザー名(スキーマ)とか付けてみても効果なし。
DBA_OBJECTSで探しても見つからず。
⇧ 上記サイト様だと、普通にできてるんですが...。
というか、
- sqlplus / as sysdba
- connect / as sysdba
の違いが分からんけど、このへんが影響してる?
と思ったら、共通ユーザーの問題っぽい?
⇧ 上記サイト様によりますと、あるPDBで共通ユーザーを利用可能にするためには、あるPDBに対するCREATE SESSION権限を、共通ユーザーに対して付与してあげる必要があるようです。
で、共通ユーザーとは?
共通ユーザーは、ルートと、既存および将来のすべてのプラガブル・データベース(PDB)で同じIDを持つデータベース・ユーザーです。どの共通ユーザーも、ルートに接続し、ルートと権限を持つすべてのPDBで操作を実行できます。
すべての共通ユーザーは、Oracle提供またはユーザー作成のいずれかです。Oracle提供の共通ユーザーの例は、SYS
やSYSTEM
です。
Oracle Databaseのデフォルトの共通ユーザーとして、SYSとSYSTEMというユーザーが用意されていると。
共通ユーザーは、CREATE SESSION
権限を持つどのコンテナ(CDB$ROOT
も含む)にもログインできます。
共通ユーザーは、すべてのコンテナで同じ権限を持つ必要はありません。たとえば、c##dba
ユーザーは、ルートおよび1つのPDBでセッション作成の権限を持っているが、別のPDBではセッション作成の権限を持っていないという場合があります。適切な権限を持つ共通ユーザーはコンテナの切替えができるため、ルートの共通ユーザーはPDBを管理できます。
で、CREATE SESSION権限はロールの説明に載ってた、
ロールとは、ユーザーに権限を素早く簡単に付与するために便利なものです。Oracle Databaseで定義されているロールを使用することもできますが、必要な権限のみを含む独自のロールを作成すると、より継続的な制御が可能になります。Oracle Database定義ロールの権限は変更または削除される場合があります。たとえば CONNECT
ロールの場合、現在持っている権限は CREATE SESSION
権限のみです。以前は、CONNECT
ロールには他に8個の権限がありました。
CONNECTロールに含まれる権限の1つらしいと...、ってことは、共通ユーザーの問題じゃないってことですかね。
マルチテナントの場合、共通ユーザーの説明は、
マルチテナント環境では、共通ユーザー とは、IDおよびパスワードがルートに、また既存と将来のすべてのプラガブル・データベース(PDB)に知られているデータベース・ユーザーです。共通ユーザーは、ルートに接続して操作を実行できます。共通ユーザーに適切な権限がある場合、ローカル・ユーザーへの権限の付与など、PDBで操作を実行できます。
共通ユーザーは、PDBの接続および切断、状態の変更、マルチテナント・コンテナ・データベース(CDB)の一時表領域の指定など、ルートまたはPDBに固有の管理タスクを実行できます。適切な権限を持つ共通ユーザーのみ、CDBに属するコンテナ間を操作できます。たとえば、共通ユーザーは複数のPDBで次の操作を実行できます。
ってなってますね。
そして、ローカル・ユーザーは、
マルチテナント環境では、ローカル・ユーザー とは、単一のPDBにのみ存在するデータベース・ユーザーです。ローカル・ユーザーは管理権限を持つことができますが、これらの権限はローカル・ユーザー・アカウントが作成されたPDBにのみ適用されます。
ってなってますね。
で、散々、調べたけど答えが見つからない。問題となっているのは、
Oracle提供の共通ユーザーであるSYSユーザーで、CDB接続時には存在しているテーブルが、PDB接続時には存在しないって言われてると。
謎なのは、SYSユーザーが作成したテーブルなのに、なぜSYSユーザーで見つけられないのか?
ってことですかね。
で、一番、疑問なのは、そもそもPDBにテーブル作ってないっぽいけど、それで良いの?ってことですかね。
迷走の果てに、データ・ディクショナリ とは?
ちょいと気になる情報が。
マルチテナント・コンテナ・データベース(CDB)では、データ・ディクショナリ表およびビュー定義のメタデータがルートのみに格納されます。ただし、各プラガブル・データベース(PDB)には、そのPDBに含まれているデータベース・オブジェクトに関する、独自のデータ・ディクショナリ表とビューのセットがあります。
で、その続きで、
現在のコンテナがPDBである場合、ユーザーは現在のPDBのデータ・ディクショナリ情報のみを表示できます。
特定のPDBに接続されているアプリケーションからは、データ・ディクショナリは非CDBであるかのように見えます。データ・ディクショナリでは、PDBに関連する情報のみを示します。また、PDBでは、対応するDBA_
ビューで表示されるデータベース・オブジェクトに関する情報がCDB_
ビューにのみ示されます。
⇧ えっ? データ・ディクショナリが何を指すかが分からないけど、ものすごく嫌な予感...。
データ・ディクショナリとは?
データ・ディクショナリはOracle Databaseの重要な部分であり、データベースに関する管理メタデータを提供する読取り専用の表の集合です。データ・ディクショナリには次のような情報が含まれています。
-
列のデフォルト値、整合性制約情報など、データベース内の各スキーマ・オブジェクトの定義
-
Oracle Databaseユーザーの名前、ユーザーに付与された権限とロール、およびユーザーに関連する監査情報(「ユーザー・アカウント」を参照)
⇧ スキーマ・オブジェクトって、表(テーブル)も含まれてたよね...
⇩ で、いきなり、スキーマ・オブジェクトいなくなってるし...
データ・ディクショナリは、次のタイプのオブジェクトで構成されています。
-
実表
データベースに関する情報を格納する、基礎となる表です。これらの表に読取り/書込みできるのはOracle Databaseのみです。これらの表は正規化され、データのほとんどは、暗号形式で格納されているため、ユーザーが直接アクセスすることはほとんどありません。
-
ビュー
これらのビューは、実表にあるデータを、ユーザー名や表の名前などの実用的な情報にデコードし、結合と
WHERE
句を使用して情報を簡略化します。これらのビューには、データ・ディクショナリ内のすべてのオブジェクトの名前と説明が含まれています。すべてのユーザーがアクセスできるビューもいくつかありますが、その他のビューは管理者のみが使用するように設計されています。
⇧ なるほど、
「実表」に、暗号化された
- データ・ディクショナリの情報(暗号化)
が格納されてて、
「ビュー」には、復号化された
- データ・ディクショナリの情報(復号化)
が格納されていると。 「実表」のデータを「ビュー」で見れるようにしてるってことですかね。
で、
⇧ これは、正確には『データ・ディクショナリ情報の「ビュー」オブジェクトのみを表示』ってことになるんでしょうね。
迷走の果てに、スキーマ・オブジェクトとは?
スキーマ・オブジェクトとは?
スキーマはデータベース・オブジェクトの集合です。スキーマはデータベース・ユーザーによって所有され、ユーザー名と同じ名前を共有します。スキーマ・オブジェクトは、ユーザーによって作成される論理構造です。表や索引などのいくつかのオブジェクトはデータを保持します。ビューやシノニムなどのその他のオブジェクトは定義のみで構成されます。
⇧ スキーマ == ユーザー名 って言ってますね。
データベースのすべてのオブジェクトは1つのスキーマに属し、スキーマ内に一意の名前を持っています。異なるスキーマにある場合、複数のデータベース・オブジェクトは同じ名前を共有できます。スキーマ名を使用して、確実にオブジェクトを参照できます。たとえば、hr.employees
は、hr
スキーマでemployees
という名前の表を参照します。(employees
表はhr
によって所有されます。)データベース・オブジェクトおよびスキーマ・オブジェクトという用語は同じ意味で使用されます。
⇧ スキーマ名を使用して、確実に参照できるって言ってるのに、
ってなっていたってことは、
非CDB ってことは、
特定のPDBに接続されているアプリケーションからは、データ・ディクショナリは非CDBであるかのように見えます。
データ・ディクショナリでは、PDBに関連する情報のみを示します。また、PDBでは、対応するDBA_
ビューで表示されるデータベース・オブジェクトに関する情報がCDB_
ビューにのみ示されます。
PDBに接続(ログイン)してる状態で、CDBで作った表(テーブル)とかを表示できないのは当然ってことになるってことですかね?
でも、SYSユーザーが、データ・ディクショナリのすべてを司ってるような...。
データ・ディクショナリのすべての実表とユーザー・アクセス可能ビューは、Oracle DatabaseユーザーSYS が所有しています。データ・ディクショナリの実表内のデータは、Oracle Databaseを機能させるために必要です。このため、データ・ディクショナリの情報はOracle Databaseによってのみ書込みまたは変更される必要があります。
SYSユーザー以外がSYSユーザーのものを変更できないと。
したがって、 データ整合性が損われることがあるため、Oracle DatabaseのユーザーはSYS
スキーマに含まれている行またはスキーマ・オブジェクトを変更できません。セキュリティ管理者は、この重要なアカウントを厳しく管理し続ける必要があります。
※ データ・ディクショナリ表のデータを変更したり操作すると、データベースの操作に永続的な悪影響を与えるおそれがあります。
迷走の果てに、スキーマ・オブジェクトの管理権限とは?
スキーマ・オブジェクトの管理権限とは?
データベース管理者(DBA)として、所有するスキーマおよびその他のスキーマでスキーマ・オブジェクトを作成、変更および削除できます。この目的のために、データベース管理者は DBA
ロールを付与されているユーザーとして定義されます。これにはSYS
およびSYSTEM
ユーザーがデフォルトで含まれます。DBA
ロールは、管理タイプ・アクセスを必要とするユーザーのみに付与することをお薦めします。
⇧ スキーマ・オブジェクトに変更を加えるには、DBAロールが付与されてる必要があるようです。
DBAロールを付与しない場合、
他のユーザーに、DBA権限を付与することなくスキーマ・オブジェクトを管理させることができます。たとえば、通常は、アプリケーション開発者がスキーマ・オブジェクトを作成、変更、削除できます。そのため、RESOURCE
ロールをアプリケーション開発者に付与します。
⇧ RESOURCEロールを付与してあれば、スキーマ・オブジェクトに変更を加えられるようです。
動的パフォーマンス・ビューとは?
もう少しだけ脱線して、
動的パフォーマンス・ビューって?
Oracle Databaseは、操作中ずっと、カレント・データベース・アクティビティを記録する一連の仮想表を保持しています。これらの ビューは、データベースがオープンで使用中の間は継続的に更新されるため、動的です。
ってなっていて、『v$~』って名前のものを『動的パフォーマンス・ビュー』というようですね。
これらのビュー(名前がV$
で始まるため、場合によってはV$ビューとも呼ばれます)には、次のような情報が含まれています。
⇧ 『select name, open_mode from v$pdbs;』で使われてた、v$pds とかも、動的パフォーマンス・ビュー ということですね。
動的パフォーマンス・ビューは、データベース管理者が変更または削除できないため、固定ビューと呼ばれることもあります。しかし、データベース管理者は、これらの表のビューに問合せを発行したり、ビューを作成したり、それらのビューにアクセスする権限を他のユーザーに付与できます。
⇧ データベース管理者って、何を指してるのか説明して欲しいですね...。(『データベース管理者は DBA
ロールを付与されているユーザーとして定義されます。これにはSYS
およびSYSTEM
ユーザーがデフォルトで含まれます。』を信じますか。)
どうやって権限を付与するのかの記載もないという...安定の不親切さ。
動的パフォーマンス表は SYS
が所有し、その名前はV_$
で始まります。これらの表に対してビューが作成され、V$
という接頭辞を持つパブリック・シノニムが作成されます。たとえば、V$DATAFILE
ビューにはデータファイルに関する情報が格納されます。V$FIXED_TABLE
ビューにはすべての動的パフォーマンス表およびビューに関する情報が格納されます。
⇧ 基本、SYSユーザーを使っておけば問題ない感じですかね?
ほとんどのV$
ビューに対して、対応するGV$
ビューが存在します。Oracle Real Application Clusters(Oracle RAC)では、GV$
ビューに対して問合せを発行すると、すべての修飾データベース・インスタンスからのV$
ビュー情報が戻されます(「データベース・サーバー・グリッド」を参照)。
⇧ V$ ⊂ G$ みたいなイメージですかね?
Database Configuration Assistant(DBCA)を使用してデータベースを作成する場合は、データ・ディクショナリがOracleによって自動的に作成されます。Oracle Databaseでは、自動的にcatalog.sql
スクリプトが実行され、このスクリプトには、その動的パフォーマンス・ビューの、ビューおよびパブリック・シノニムの定義が含まれます。 これらのビューとシノニムを作成するには、catalog.sql
を実行する必要があります。
⇧ DB作成時に、catalog.sqlを実行しておかないと、データ・ディクショナリができないってことですかね?
アプリケーションコンテナとは?
アプリケーション・コンテナとは?
アプリケーション・コンテナは、アプリケーション・ルートとそれに関連付けられたすべてのアプリケーションPDBから成るCDBのオプション・コンポーネントです。アプリケーション・コンテナは、1つ以上のアプリケーションのデータを格納し、アプリケーションのメタデータおよび共通データを共有します。
⇧ まったく分からん...
アプリケーション・コンテナは、CDB内のルートから分離されたアプリケーション・ルートを持つ特殊なタイプのPDBであり、1つ以上のPDBを持つことができます。PDBがアプリケーション・ルートに属する場合、それはアプリケーションPDBと呼ばれます。
⇧ さらに、アプリケーション・ルートとか出てきてるし...
アプリケーション・ルートは、アプリケーションPDBが属することのできるコンテナです。アプリケーション・ルートはそのCDBルートに属し、CDBルート内のオラクル社が提供する共通オブジェクトの説明を共有します。
また、アプリケーション・ルートにより、アプリケーション・ルートとアプリケーション・ルートに属するアプリケーションPDBによってのみ共有されるアプリケーション共通オブジェクトを作成できます。
アプリケーション共通オブジェクトはCDBルート、他のアプリケーション・ルート、またはアプリケーション・ルートに属さないPDBには表示されません。アプリケーション・ルートは別のアプリケーション・ルートに属することができず、PDBはアプリケーション・ルートに属さないか、1つのアプリケーション・ルートにのみ属することができます。
アプリケーション・コンテナのアプリケーション・シードを作成できます。アプリケーション・シードはオプションですが、存在する場合は、アプリケーション・コンテナの要件を満たすアプリケーションPDBを迅速に作成するために使用できます。アプリケーション・シードにより、そこから作成されるアプリケーションPDBを瞬時にプロビジョニングできます。
⇧ 分からんけど、
⇩ Oracleの提供してる図によると、
⇧ って感じで、各アプリケーション・コンテナ内でのみ参照できるアプリケーション共通オブジェクトってものを持つことができるようですが、目的がサッパリ分からず...。
アプリケーション共通オブジェクトとは?
アプリケーション共通オブジェクトは、アプリケーション・コンテナで共有されている、ユーザーにより作成されたデータベース・オブジェクトです。アプリケーション共通オブジェクトは、アプリケーション・ルートに作成されます。
⇧ 堂々巡りや~ん、アプリケーション・コンテナ内のすべてのデータベース・オブジェクト == アプリケーション共通オブジェクト って考えて良いのかしら?
CONTAINERS
句を使用すると、CDB内のすべてのコンテナ間で表およびビューを問い合せることができます。アプリケーション・コンテナ内のすべてのコンテナでアプリケーション共通オブジェクトを問い合せることもできます。
⇧ container句を使用すると、アプリケーション共通オブジェクトにアクセスできるとなってるので、CDB$ROOTの中で、Seed(PDB$SEED)を除くすべてのPDBにアクセスできるってことですかね。
で、結局?
たいへん長らく脱線してしまったのですが、結局どうすれば?
どうも、テーブルをPDBに作成する必要があるんじゃないですかね?
調べ方が悪いとは思うのですが、PDBからCDBの表(テーブル)にアクセスする方法が一向に見つからないので。
で、CDBからPDBへの接続(ログイン)方法は皆さん紹介しまくってくれてますが、PDBからCDBへ接続(ログイン)する方法については、ほとんどない...。
共通ユーザーとしてコンテナに接続している場合、ALTER SESSION
文を使用して、別のコンテナおよびアプリケーション・サービスに切り替えることができます。
次の文を使用して、別のコンテナおよびアプリケーション・サービスに切り替えることができます。
ALTER SESSION SET CONTAINER = container_name [SERVICE = service_name]
container_nameについては、次のいずれかを指定します。
⇧ Oracleさんが載せてくれてました。
長らく脱線してましたが、いまログインしているユーザーはSYSユーザーです。
ALTER SESSION SET CONTAINER = CDB$ROOT;
とりあえず、誤ってCDBに作成してしまったテーブルを削除していきますか。
⇧ 上記サイト様を参考に、削除していきます。
DROP TABLE books PURGE
DROP TABLE book_authors PURGE
DROP TABLE reading_record PURGE
そんじゃ、作成してたPDB(ここでは、diary)に戻ってテーブル作成しますか。
ALTER SESSION SET CONTAINER = diary;
共通ユーザーである、SYSユーザーでテーブルの作成。
作成していたローカル・ユーザーに、SYSユーザーで作成したテーブルへの操作を行うための権限を与えます。
GRANT select,insert,update,delete ON [表(テーブル名)] TO [ユーザー名];
ローカル・ユーザーでPDBにログイン...エラー
ローカル・ユーザーでログインしようとしたら、エラー。
大文字の問題かと思いきや、そうでないらしい。
どうやら、接続の仕方によっていろいろ必要な情報が異なるようです。
-
オペレーティング・システム認証を使用したローカル接続
-
簡易接続を使用したデータベース接続
-
サービス名を使用したデータベース接続
で、前提条件として、
次の前提条件を満たしている必要があります。
となっているようです。自分のやろうとした接続の仕方(サービス名を使用したデータベース接続)だと、tnsnames.oraなどでPDBのネット・サービス名 を記述してないとまずかったようです。
⇧ 上記サイト様でも仰っていますが、PDBの情報をtnsnames.oraなどに記述しておくことで、アプリケーションから接続することができるようです。
DBCA(Database Configuration Assistant)を利用してPDBとかまで作っておけば、PDBのリスナーも自動で作成されるようです。
で、ややこしいのが、ネットワーク経由でない場合、tnsnames.ora にPDBの情報を記述すれば接続できますが、ネットワーク経由での接続の場合、リスナーも作成してないと接続できないかと。
試しにリスナーが起動してない状態で、CDBに接続しようとしたら、
ってなったので、外部から(クライアントとサーバーが別マシンに配置さてれいるような場合)の接続はリスナーが必須かと思われます、たぶん。
ちなみに、今まで通りCDBへ、SYSユーザーで接続しようとすると、権限がないみたいで怒られる。SYSユーザーとSYSTEMユーザーで役割が異なるってことですかね。
サービスの確認には、srvctlコマンドが必要?
まずは、sqlplusでネットワーク経由をしない場合の接続を行えるように、tnsnames.oraを編集していきます。
• PDB ごとに必要なサービスを作成して接続に利用することを推奨
– PDB 作成時に PDB 名と同じ名前のサービスが作成される
• サーバー管理 (SRVCTL) ユーティリティを使用したサービス管理を推奨
http://www.oracle.com/technetwork/jp/ondemand/branch/dosanko-3rd-mta-2844750-ja.pdf
LSNRCTL SERVICES コマンドで、サービスの状態を確認できるそうです...と思ったら、リスナーがある場合のサービスの確認方法でした。
リスナーが無い場合は、srvctlコマンドで...って、Oracle Clusterwareってのがないと利用できないと...。
勉強の用途であれば、無料で利用できそうです。
Oracle Clusterwareは、完全なエンタープライズ・クラスのクラスタリング・ソリューションであり、無料です。アップグレードやパッチのサポートを受けるにはOracle Linux、Oracle Solaris、または、その他のサポート対象のOracleアプリケーションの有効なサポート・ライセンスが必要です。
Oracleの説明によると、
Oracle Clusterwareを使用すると、複数のサーバーが相互に通信することにより、1つの集合単位として機能するように見えます。このようなサーバーの組合せは、一般にクラスタと呼ばれます。これらのサーバーはスタンドアロン・サーバーですが、各サーバーには他のサーバーと通信する追加のプロセスがあります。このように、個々のサーバーはアプリケーションおよびエンド・ユーザーから1つのシステムとして認識されます。
Oracle Clusterwareの構成
tnsnames.oraにPDB接続のための情報を記述
というわけで、tnsnames.oraファイルを編集しようと思ったら、
ファイルがないと...。
ちなみに、
⇧ 上記サイト様でも仰っていますが、
- listener.ora
- サーバー側の設定
- tnsnames.ora
- クライアント側の設定
ということのようです。
自分は、クライアント、サーバーを同一のマシン(仮想マシン)にインストールしてしまってるので、ややこしいことになっていますが。
$ORACLE_HOME/network/admin/samples フォルダの中の雛型を$ORACLE_HOME/network/admin/ にコピってしまいましょう。(コピー元とコピー先で同じファイル名でもよい場合は、コピー先のファイル名を省略できます。)
cp /opt/app/oracle/product/12.2.0.1/dbhome_1/network/admin/samples/tnsnames.ora /opt/app/oracle/product/12.2.0.1/dbhome_1/network/admin
tnsnames.oraファイルを編集します。
vi /opt/app/oracle/product/12.2.0.1/dbhome_1/network/admin/tnsnames.ora
ファイルが開けたらば、一旦、中身を全削除。
ggVGd
そしたらば、「i」でINSERTモードになって、下記を追記。
[適当な名前(一意である必要がある)] = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = [ホスト名、またはIPアドレス])(PORT = [ポート番号])) (CONNECT_DATA =(SERVICE_NAME = [PDBの名前])) )
⇧ ポート番号を間違えたっぽい。なんか、CDBのリスナーのポート番号(PORT = 1521)と同じにしないとマズイのかもしれない。そのへんの説明が無さ過ぎて分からんです。
というわけで、CDBのリスナーのポート番号(PORT = 1521)と同じにしときました。
「:wq」で保存して、viエディターを終了します。
別に、もう1つコマンドプロンプトを起ち上げ、sshログインで仮想マシンに接続し、DBにログインし、ORACLEインスタンスの起動とDBをマウントしたら、PDBを起動しておきます。
alter pluggable database all open;
これで、接続できると思いきや、エラー。リスナー必要...
connect [ユーザー名]@[PDBのサービス名];
試しに、もう1つのコマンドプロンプトに戻って、
sqlplus [ユーザー名]/[パスワード]@[PDBのサービス名];
⇧ こちらも、リスナーが必要...
どうやら、サービス名を利用した接続の場合は、リスナーが必須のようです。
@(アットマーク)を付けて接続した場合は通常、tnsnames.ora ファイルの接続情報を元にリスナー経由での接続になります。
というか、ネットで見れる範囲の情報で「オラクルマスター教科書 Gold Oracle Database 12c Upgrade」の説明では、PDBへの接続は、常にOracle Netを使用したリモート接続になるそうです。
つまり、リスナーが必須のようです。
やっぱ、参考書とかないと厳しいですね。
tnsnames.oraの情報を元に接続するのはリスナーが必要ということで、listener.oraを編集しようと思ったけど
なんか、1つのリスナーで、CDB、PDBへの接続を兼用してるっぽいです。
構造的には、
という感じで、リスナーにサービス名を登録していく感じになるようです。
PDBにサービスを追加、リスナーへ登録
サービスを追加したいPDBへ接続します。(接続前にPDBは起動しておきましょう。)
ちなみに、sqlplus / as sysdba でログインして、PDBは起動してる状態で、下記コマンドを実行してます。
alter session set container = [接続したいPDB名];
で、サービスの追加しようとして、エラー。
-- サービスを追加('管理用の名前', 'リスナー登録用の名前') exec DBMS_SERVICE.CREATE_SERVICE('manage_pdb_diary','pdb_diary')
DBMS_SERVICEパッケージの説明は、
DBMS_SERVICE
パッケージを使用して、シングル・インスタンスのサービスを作成、削除、アクティブ化および非アクティブ化できます。
ってなっていて、
権限
このパッケージを使用するクライアントは、ALTER
SYSTEM
実行権限、およびV$SESSION
表読込み権限を持っている必要があります。
このパッケージは、SYS
スキーマの下に作成する必要があります。
ロール
このパッケージのEXECUTE
権限は、DBA
ロールにのみ付与されます。
ってなっているんですが、
- ORA-06550
- PLS-00201
というエラーが出ていると。
そして、接続が途切れると、CDBから接続し直しという...PDBのサービスを登録してしまえば、PDBに直接に接続とかもできるんでしょうけど。
SYSユーザーでSYSDBA権限でログインしてるから、DBMS_SERVICEパッケージのEXECUTE権限を満たしてると思ったんですが、エラー。
SYSユーザーの権限がいまいちよく分からんのだけども、
⇧ 上記サイト様によりますと、スキーマ(ユーザー名)を付けて実行すれば上手くいくみたいですね。
駄目でした。
-- サービスを追加('管理用の名前', 'リスナー登録用の名前') exec SYS.DBMS_SERVICE.CREATE_SERVICE('manage_pdb_diary','pdb_diary')
先に、SYSユーザーに対してEXECUTE権限を付与してみます。はい、エラー。
grant execute on DBMS_SERVICE.CREATE_SERVICE to SYS;
どうやら、「SP2-0640: 接続されていません」のエラーが出た状態で、PDBが停止してしまっていたようです...。
PDBに接続してる状態なので、startupで起動できるようです。
startup;
そしたら、普通に実行できました。ありがとう、ロックな人!
-- サービスを追加('管理用の名前', 'リスナー登録用の名前') exec SYS.DBMS_SERVICE.CREATE_SERVICE('manage_pdb_diary','pdb_diary')
そんでは、サービスを起動。
-- サービスの開始 exec SYS.DBMS_SERVICE.START_SERVICE('manage_pdb_diary')
サービスの状態を確認しようとして、怒られる。
! lsnrctl services
どうやら、リスナーを起動してないと、サービスの状態を確認はできないようです。
というわけで、リスナーを起動。
DBに接続してる時は、「!」を先頭に付ける必要があるんですかね?
⇧ PDBのサービスが追加されました。
PDBにローカルユーザーで接続
では、ローカルユーザーでPDBに接続...エラー。
Oracleさんの説明では、
SQL*Plusで、SYSTEMアカウント内の表の1つであるPRODUCT_USER_PROFILE(PUP)表を使用した製品レベルのセキュリティが提供されています。
で、 ユーザーのログイン時にPUB表の情報が使われると。
SQL*Plusでは、ユーザーがSQL*Plusにログインすると、PUP表から制限が読み込まれ、セッションが終了するまでその制限が保持されます。PUP表の変更は、次に対象ユーザーがSQL*Plusにログインするときに有効になります。
ただし、
SYSTEM、SYS、またはSYSBACKUP、SYSDBA、SYSDG、SYSKMまたはSYSOPER権限で認証するユーザーが接続またはログインする場合、SQL*PlusではPUP表は読み込まれません。そのため、これらのユーザーに制限は適用されません。
「SYSTEM、SYS、またはSYSBACKUP、SYSDBA、SYSDG、SYSKMまたはSYSOPER権限で認証するユーザーでのログイン」では、PUB表は読み込まれないと。
で、今回は、作成したローカルユーザーでログインしようとしてるけど、そのローカルユーザーのPUB表が作成されてないってことですかね?
で、PUB表の作成ですが、
PUP表を作成するには、拡張子がSQLのPUPBLDという名前のスクリプトを、SYSTEMで実行します。ファイル拡張子の正確な形式およびファイルの位置は、システムによって異なります。詳細は、DBAに問い合せてください。
⇧ 丸投げ感が半端ないですね...安定の不親切さ。
⇧ 上記サイト様によりますと、$ORACLE_HOME/sqlplusadmin/pupbld.sql を実行すれば良さそうです。
⇧ ファイルがおりました。
で、Oracle Databae 12c系のマルチサイト特有の問題が。
CDB、PDBのどっちで実行すれば?
分からんけど、とりあえず、CDBに接続し直して、実行してみますか。と思って、CDBに接続し直そうとして、エラー。「ORA-01031: 権限が不足しています。」
⇧ 「接続されました。」と出ていたので、ユーザーを確認するとローカルユーザーでログインできていたようです。
PUB表は、またの機会ですかね~。
テーブルに別名を与えるシノニム
アクセスしようとしてるテーブルが、SYSユーザーが作成したテーブルであるため、
明示的に、SYS.テーブル名 というような形でしかアクセスできない状態です。
この、SYS.テーブル名 に対して別の名前(別名)を付けて、その別名で操作できるようにしてあげる機能のことを、シノニム というそうです。
とりあえず、SYSユーザーでSYSDBA権限で、PDBに接続し直します。
シノニムの作成。
create public synonym [テーブルの別名] for [テーブル名];
ローカルユーザーでログインし直して、無事、シノニムの機能でテーブルの別名を操作できています。
なんとか、テーブルの作成と、ローカルユーザーでの操作(SELECT文までしか試してないですが)までできました。
ちなみに、EclipseのDBViewerでテスト接続したところ、ローカルユーザーでつながりました!
⇧ VirtualBox のネットワーク設定でNATにしてポートフォワーディングしてるため、
127.0.0.1:3333:pdb_diaryへのアクセスが、10.0.2.15:1521@pdb_diaryへ転送されて繋がるイメージですかね?
今回もハマりにハマってますが、参考書とか購入したほうが絶対に効率的かと思われます。職場の先輩とかに聞ける環境であれば、先輩におすすめの技術書とか聞いた方が良いかと。
誰か、おすすめの技術書を教えてください!という気持ちに日々モヤモヤ感を募らせているわけですが。
今回はこのへんで。
番外編:NG集
複数のテーブルに対して、まとめて権限の付与をユーザーにできないもんかと探してたら、
⇧ 上記サイト様で紹介されていたので、深く考えずに実行してエライ目にあいました。(もし業務でやったらと考えると...ゾッとします。)
※ SYSユーザーやSYSTEMユーザーなど、ORACLEのデフォルトで用意されたユーザーで下記のコマンドを実行するのは止めましょう。
set serveroutput on declare objectOwner varchar2(64) := 'SYS'; grantee varchar2(64) := 'ts0818'; begin for cur in ( select table_name from dba_tables where owner=objectOwner ) loop dbms_output.put_line('grant select on ' || objectOwner || '.' || cur.table_name || ' to ' || grantee || ';'); end loop; end; /
⇧ SYSユーザーの所持する全てのテーブルに対して、ユーザーts0818にSELECT文の実行権限を与えるって感じですかね...
一応、rollback を実行したけど、無かったことにできるのか分からずです....。
⇧ 上記サイト様によりますと、
というように、まずは、誤った処理を止めることが大事 のようです。
今回は、SELECT文を実行できる権限を与えるとかだったので大事には至りませんでした?が、実際のデータを書き換えてしまう処理とかだったら...恐ろしいですね。