きさま!見ているなッ!!

【Oracle】表領域・ユーザー・テーブルを作成する

  • 2019-09-13
  • 2019-09-13
  • Oracle

プラガブル・データベース(PDB)の管理タスクは、概ね、非 CDB と同じです。

今回は、PDB ローカル管理者で下記のタスクを実施してみましょう。

  • 表領域作成
  • ユーザー作成
  • テーブル作成

環境

・環境情報

OSRDBMS
CentOS 7.6Oracle Database 12c R2 Enterprise Edition

・CDB 情報

データベース名CDB1
データベースタイプ単一インスタンス
アーキテクチャマルチテナント
データベース記憶域ローカル・ストレージ
高速リカバリ領域ローカル・ストレージ
UNDOモードローカル
データベース・ログ・モードアーカイブ・モード

・PDB 情報

PDB名PDB1
PDBローカル管理者PDB1ADM
PDBのデータファイルの場所/u01/app/oracle/oradata/pdb1/

PDB の作成については、こちらの記事をどうぞ。

事前準備

環境変数の設定

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export ORACLE_HOSTNAME=testdb.oracle12c.jp
export ORACLE_SID=cdb1

必要な環境変数を設定します。

環境変数設定値説明
ORACLE_BASE/u01/app/oracleOracleディレクトリ・ツリーのルート
ORACLE_HOME/u01/app/oracle/product/12.2.0/dbhome_1Oracleソフトウェアのインストール先
ORACLE_HOSTNAMEtestdb.oracle12c.jpPCのホスト名
ORACLE_SIDcdb1インスタンス識別子(SID)

PDBローカル管理者にDBAロールを付与する

※既に PDB ローカル管理者に DBA ロールが付与されている場合は、実施する必要はありません。

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 金 9月 13 00:22:23 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.



Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。
SQL> 
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> 
SQL> alter session set container = pdb1;

セッションが変更されました。

SQL> 
SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL>

SYSDBA管理権限で CDB ルートに接続し、ALTER SESSION SET CONTAINER 文で PDB1 に切り替えます。

SQL> set lin 1000
SQL> col grantee for a20
SQL> col granted_role for a20
SQL> SELECT grantee, granted_role, admin_option
  2  FROM dba_role_privs
  3  WHERE grantee = 'PDB1ADM'
  4  ;

GRANTEE              GRANTED_ROLE         ADMIN_OPT
-------------------- -------------------- ---------
PDB1ADM              PDB_DBA              YES

SQL> 
SQL> col role for a20
SQL> col privilege for a30
SQL> SELECT role, privilege, admin_option, common, inherited
  2  FROM role_sys_privs
  3  WHERE role = 'PDB_DBA'
  4  ORDER BY privilege
  5  ;

ROLE                 PRIVILEGE                      ADMIN_OPT COMMON    INHERITED
-------------------- ------------------------------ --------- --------- ---------
PDB_DBA              CREATE PLUGGABLE DATABASE      NO        NO        NO
PDB_DBA              CREATE SESSION                 NO        NO        NO
PDB_DBA              SET CONTAINER                  NO        NO        NO

SQL> 

PDB1 の PDB ローカル管理者(PDB1ADM)には、PDB_DBA ロールが付与されています。PDB_DBA ロールに付与されているシステム権限も確認しておきます。

SQL> GRANT DBA TO PDB_DBA;

権限付与が成功しました。

SQL> 
SQL> col role for a20
SQL> col granted_role for a20
SQL> SELECT role, granted_role, admin_option, common, inherited
  2  FROM role_role_privs
  3  WHERE role = 'PDB_DBA'
  4  ORDER BY granted_role
  5  ;

ROLE                 GRANTED_ROLE         ADMIN_OPT COMMON    INHERITED
-------------------- -------------------- --------- --------- ---------
PDB_DBA              DBA                  NO        NO        NO

SQL> 

PDB_DBA ロールに DBA ロールを付与することで、PDB1ADM に権限を付与します。

表領域作成

$ sqlplus pdb1adm/oracle@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on 金 9月 13 01:51:02 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

最終正常ログイン時間: 金 9月  13 2019 00:38:56 +09:00


Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。
SQL> 
SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> 

PDB1ADM で PDB1 に接続します。

SQL> CREATE TABLESPACE example
  2  DATAFILE '/u01/app/oracle/oradata/pdb1/example01.dbf' SIZE 100M REUSE
  3  AUTOEXTEND ON NEXT 500K MAXSIZE UNLIMITED
  4  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  5  SEGMENT SPACE MANAGEMENT AUTO
  6  ;

表領域が作成されました。

SQL> 

CREATE TABLESPACE 文で表領域を作成します。今回の仕様は、下記の通りです。

項目設定
表領域名EXAMPLE
データファイル/u01/app/oracle/oradata/pdb1/example01.dbf
ファイル・サイズ100MB
自動拡張オン(500KBずつ無制限)
エクステント管理ローカル管理
エクステント・サイズ自動
自動セグメント領域管理オン
SQL> set lin 1000
SQL> set null @
SQL> col tablespace_name for a20
SQL> col contents for a10
SQL> SELECT tablespace_name, block_size, initial_extent, next_extent, min_extents, max_extents, max_size
  2  , status, contents, extent_management, allocation_type, segment_space_management
  3  FROM dba_tablespaces
  4  ORDER BY tablespace_name
  5  ;

TABLESPACE_NAME      BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE   STATUS                      CONTENTS   EXTENT_MANAGEMENT              ALLOCATION_TYPE             SEGMENT_SPACE_MANA
-------------------- ---------- -------------- ----------- ----------- ----------- ---------- --------------------------- ---------- ------------------------------ --------------------------- ------------------
EXAMPLE                    8192          65536 @                     1  2147483645 2147483645 ONLINE                      PERMANENT  LOCAL                          SYSTEM                      AUTO
SYSAUX                     8192          65536 @                     1  2147483645 2147483645 ONLINE                      PERMANENT  LOCAL                          SYSTEM                      AUTO
SYSTEM                     8192          65536 @                     1  2147483645 2147483645 ONLINE                      PERMANENT  LOCAL                          SYSTEM                      MANUAL
TEMPTS1                    8192        1048576     1048576           1 @           2147483645 ONLINE                      TEMPORARY  LOCAL                          UNIFORM                     MANUAL
UNDOTBS1                   8192          65536 @                     1  2147483645 2147483645 ONLINE                      UNDO	     LOCAL                          SYSTEM                      MANUAL
USERS                      8192          65536 @                     1  2147483645 2147483645 ONLINE                      PERMANENT  LOCAL                          SYSTEM                      AUTO
USERTBS                    8192          65536 @                     1  2147483645 2147483645 ONLINE                      PERMANENT  LOCAL                          SYSTEM                      AUTO

7行が選択されました。

SQL> 
SQL> set lin 1000
SQL> set null @
SQL> col tablespace_name for a20
SQL> col file_name for a60
SQL> SELECT DTS.tablespace_name, DDF.file_name, DDF.bytes, DDF.status, DDF.online_status
  2  FROM dba_tablespaces DTS
  3  INNER JOIN dba_data_files DDF
  4  ON DDF.tablespace_name = DTS.tablespace_name
  5  ORDER BY DTS.tablespace_name
  6  ;

TABLESPACE_NAME      FILE_NAME                                                    BYTES      STATUS                      ONLINE_STATUS
-------------------- ------------------------------------------------------------ ---------- --------------------------- ---------------------
EXAMPLE              /u01/app/oracle/oradata/pdb1/example01.dbf                    104857600 AVAILABLE                   ONLINE
SYSAUX               /u01/app/oracle/oradata/pdb1/sysaux01.dbf                     241172480 AVAILABLE                   ONLINE
SYSTEM               /u01/app/oracle/oradata/pdb1/system01.dbf                     209715200 AVAILABLE                   SYSTEM
UNDOTBS1             /u01/app/oracle/oradata/pdb1/undotbs01.dbf                    225443840 AVAILABLE                   ONLINE
USERS                /u01/app/oracle/oradata/pdb1/users01.dbf                      524288000 AVAILABLE                   ONLINE
USERTBS              /u01/app/oracle/oradata/pdb1/usertbs01.dbf                    314572800 AVAILABLE                   ONLINE

6行が選択されました。

SQL> 

静的データ・ディクショナリ・ビューで EXAMPLE 表領域について確認します。

ユーザー作成

SQL> CREATE USER pdb1user
  2  IDENTIFIED BY pdb1user
  3  DEFAULT TABLESPACE example
  4  QUOTA UNLIMITED ON example
  5  TEMPORARY TABLESPACE tempts1
  6  ;

ユーザーが作成されました。

SQL> 

新しいユーザーを作成します。仕様は下記の通りです。

項目設定
ユーザー名PDB1USER
パスワードpdb1user
デフォルト表領域EXAMPLE
割当て制限無制限(EXAMPLE)
一時表領域TEMPTS1
SQL> GRANT create session
  2  , unlimited tablespace
  3  , resource
  4  TO pdb1user
  5  ;

権限付与が成功しました。

SQL> 

PDB1USER に権限を付与します。

名称タイプ説明
CREATE SESSIONシステム権限データベースへの接続権限
UNLIMITED TABLESPACEシステム権限任意の表領域を無制限に使用できる権限
RESOURCEロール事前定義ロール(複数のCREATE系システム権限が付与されている。将来のリリースでは自動生成されない可能性がある。)
SQL> set lin 1000
SQL> col grantee for a20
SQL> col privilege for a30
SQL> SELECT grantee, privilege, admin_option, common, inherited
  2  FROM dba_sys_privs
  3  WHERE grantee = 'PDB1USER'
  4  ORDER BY privilege
  5  ;

GRANTEE              PRIVILEGE                      ADMIN_OPT COMMON    INHERITED
-------------------- ------------------------------ --------- --------- ---------
PDB1USER             CREATE SESSION                 NO        NO        NO
PDB1USER             UNLIMITED TABLESPACE           NO        NO        NO

SQL> 
SQL> set lin 1000
SQL> col grantee for a20
SQL> col granted_role for a20
SQL> SELECT grantee, granted_role, admin_option
  2  FROM dba_role_privs
  3  WHERE grantee = 'PDB1USER'
  4  ;

GRANTEE              GRANTED_ROLE         ADMIN_OPT
-------------------- -------------------- ---------
PDB1USER             RESOURCE             NO

SQL> 
SQL> set lin 1000
SQL> col grantee for a20
SQL> col privilege for a30
SQL> SELECT grantee, privilege, admin_option, common, inherited
  2  FROM dba_sys_privs
  3  WHERE grantee = 'RESOURCE'
  4  ORDER BY privilege
  5  ;

GRANTEE              PRIVILEGE                      ADMIN_OPT COMMON	INHERITED
-------------------- ------------------------------ --------- --------- ---------
RESOURCE             CREATE CLUSTER                 NO        YES       YES
RESOURCE             CREATE INDEXTYPE               NO        YES       YES
RESOURCE             CREATE OPERATOR                NO        YES       YES
RESOURCE             CREATE PROCEDURE               NO        YES       YES
RESOURCE             CREATE SEQUENCE                NO        YES       YES
RESOURCE             CREATE TABLE                   NO        YES       YES
RESOURCE             CREATE TRIGGER                 NO        YES       YES
RESOURCE             CREATE TYPE                    NO        YES       YES

8行が選択されました。

SQL> 

PDB1USER に付与した権限と RESOURCE ロールについて確認しています。

テーブル作成

SQL> conn pdb1user/pdb1user@pdb1
接続されました。
SQL> 
SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> 

新しいユーザー(PDB1USER)に切り替えて、テーブルを作成します。

SQL> CREATE TABLE t_user_info (
  2    user_id    VARCHAR2(20) NOT NULL
  3  , user_name  VARCHAR2(50)
  4  , reg_date   DATE DEFAULT SYSDATE
  5  , CONSTRAINT pk_user_info PRIMARY KEY(user_id)
  6  )
  7  TABLESPACE example
  8  ;

表が作成されました。

SQL> 

T_USER_INFO テーブルを下記の仕様で作成します。

カラム名データ型NOT NULL制約デフォルト値
USER_IDVARCHAR2(20)ありなし
USER_NAMEVARCHAR2(50)なしなし
REG_DATEDATEなしSYSDATE

プライマリーキーは、USER_ID で、作成する表領域は、EXAMPLE です。

SQL> set lin 1000
SQL> col table_name for a20
SQL> col tablespace_name for a20
SQL> SELECT table_name, tablespace_name, pct_free, pct_used, status 
  2  FROM user_tables
  3  ;

TABLE_NAME           TABLESPACE_NAME      PCT_FREE   PCT_USED   STATUS
-------------------- -------------------- ---------- ---------- ------------------------
T_USER_INFO          EXAMPLE                      10            VALID

SQL> 

静的データ・ディクショナリ・ビューで確認します。

まとめ

接続の切り替えが少しありましたが、PDB でも非 CDB と同じように、DB オブジェクトの作成ができました。

PDB ローカル管理者は、他の PDB に影響を与えませんので、従来通りの管理タスクができますね。

【Oracle】表領域・ユーザー・テーブルを作成する
最新情報をチェックしよう!