Oracle Multitenant 6 PDB作成

事前にPDBシードのファイルパスを確認

SQL>  alter session set container=PDB$SEED;

Session altered.

SQL> select file_name from dba_data_files;
FILE_NAME
/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf

PDBシードからファイルネームコンバートをコマンドで指定して作成

SQL> create pluggable database orclpdb1
  2    admin user adminuser identified by password
  3    file_name_convert = (
  4    '/opt/oracle/oradata/ORCLCDB/pdbseed/'
  5  , '/opt/oracle/oradata/ORCLCDB/orclpdb1/'
  6  );

Pluggable database created.

初期状態

SQL> show pdbs
  CON_ID CON_NAME   OPEN MODE   RESTRICTED
       2 PDB$SEED   READ ONLY   NO
       3 ORCLPDB1   MOUNTED

起動、自動起動ON

SQL> alter pluggable database orclpdb1 open;

Pluggable database altered.

SQL> alter pluggable database orclpdb1 save state;

Pluggable database altered.

SQL> show pdbs
  CON_ID CON_NAME   OPEN MODE    RESTRICTED
       2 PDB$SEED   READ ONLY    NO
       3 ORCLPDB1   READ WRITE   NO

PDBに直接接続

# sql adminuser/password@orclpdb1
SQL> 

PDB作成時に指定する管理者ユーザにはユーザー作成権限がないらしい

SQL> create user scott identified by tiger;

create user scott identified by tiger
 *
ERROR at line 1:
ORA-01031: insufficient privileges

sysで接続
users表領域がないので作成
消したっけ

SQL> create tablespace users datafile '/opt/oracle/oradata/ORCLCDB/orclpdb1/users.dbf' size 10m autoextend on maxsize unlimited;
Tablespace created.

sysでユーザー作成
デフォルト表領域にusersを指定

# sql sys/password@orclpdb1 as sysdba
SQL> create user scott identified by tiger default tablespace users;

User created.

SQL> grant create session to scott;

Grant succeeded.

SQL> grant create table to scott;

Grant succeeded.

SQL> grant unlimited tablespace to scott;

Grant succeeded.

ユーザーで接続してテーブル作成
権限OK

SQL> create table test(col number);

Table created.

SQL> drop table test;

Table dropped.