DBA Hub

📋Steps in this guide1/2

Multitenant : Default Tablespace Clause During PDB Creation in Oracle Database 12c Release 2 (12.2)

This article describes the change in behaviour of the DEFAULT TABLESPACE clause of the CREATE PLUGGABLE DATABASE command between Oracle database 12.1 and 12.2.

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

Default Tablespace Clause in 12.1

In both Oracle database 12.1 and 12.2 the clause of the command can be used to create a new default tablespace for a pluggable database created from the seed. The following example gives both the Oracle Managed Files (OMF) and non-OMF syntax. All further examples will assume you are using OMF. You can add the appropriate or settings if you need them. Once the PDB is created you can see the presence of the extra tablespace and the database default tablespace setting in the PDB. The clause can't be used when creating a pluggable database from a user-defined PDB. In the examples below we attempt to use it both to specify a new default tablespace and reference an existing tablespace. Both result in an error.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
CONN / AS SYSDBA

-- Oracle Managed Files (OMF) syntax.
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
  DEFAULT TABLESPACE users DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

-- Non-OMF syntax.
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/')
  DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/cdb1/pdb2/users01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;


ALTER PLUGGABLE DATABASE pdb2 OPEN;

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb2;

SELECT tablespace_name
FROM   dba_tablespaces
ORDER BY 1;

TABLESPACE_NAME
------------------------------
SYSAUX
SYSTEM
TEMP
USERS

SQL>


SELECT property_value
FROM   database_properties
WHERE  property_name = 'DEFAULT_PERMANENT_TABLESPACE'; 

PROPERTY_VALUE
----------------------------------------------------------------------------------------------------
USERS

SQL>

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb3 FROM pdb2
  DEFAULT TABLESPACE users2 DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

  DEFAULT TABLESPACE users2 DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M
  *
ERROR at line 2:
ORA-00922: missing or invalid option

SQL> 


CREATE PLUGGABLE DATABASE pdb3 FROM pdb2
  DEFAULT TABLESPACE users;

  DEFAULT TABLESPACE users
  *
ERROR at line 2:
ORA-00922: missing or invalid option

SQL>
2

Default Tablespace Clause in 12.2

In Oracle database 12.2 the clause can be used regardless of the source of the clone. If the source is the seed PDB, the clause is used to create a new default tablespace, as it was in Oracle 12.1. If the source is a user-defined PDB, the clause specifies which existing tablespace in the new PDB should be set as the default tablespace. To see this in action, create a new pluggable database from the seed as we did before. The tablespace will be the default tablespace in this PDB and any others cloned from it, so let's try something different. Create a new tablespace in the PDB, but don't set it as the database default tablespace. Create a new PDB as a clone of this user-defined PDB, but tell it to use the tablespace as the database default tablespace. We can see the tablespaces in the new PDB are the same as the source, but it's now using the tablespace, rather than the tablespace, as the database default tablespace. Attempting to create a new tablespace, rather than reference an existing tablespace, during the creation process still results in an error. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
  DEFAULT TABLESPACE users DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

ALTER PLUGGABLE DATABASE pdb2 OPEN;

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb2;

CREATE TABLESPACE test_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb3 FROM pdb2
  DEFAULT TABLESPACE test_ts;

ALTER PLUGGABLE DATABASE pdb3 OPEN;

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb3;

SELECT tablespace_name
FROM   dba_tablespaces
ORDER BY 1;

TABLESPACE_NAME
------------------------------
SYSAUX
SYSTEM
TEMP
TEST_TS
UNDOTBS1
USERS

SQL>


SELECT property_value
FROM   database_properties
WHERE  property_name = 'DEFAULT_PERMANENT_TABLESPACE'; 

PROPERTY_VALUE
----------------------------------------------------------------------------------------------------
TEST_TS

SQL>

CONN / AS SYSDBA
-- Clean up.
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

CREATE PLUGGABLE DATABASE pdb3 FROM pdb2
  DEFAULT TABLESPACE another_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

  DEFAULT TABLESPACE another_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M
                                *
ERROR at line 2:
ORA-00922: missing or invalid option
SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!