DBA Hub

📋Steps in this guide1/2

Multitenant : USER_TABLESPACES Clause in Oracle Database 12c

The USER_TABLESPACES clause of the CREATE PLUGGABLE DATABASE command gives control over which tablespaces are included in a PDB created from a clone or a plugin operation.

oracle 12cconfigurationintermediate
by OracleDba
17 views
1

The USER_TABLESPACES Clause in 12.1.0.2 Onward

In Oracle database 12.1.0.2 the clause was added to the command, allowing you to control which tablespaces are included in a PDB created from a clone or a plugin operation. Some simple examples are listed below. Remember, the SYSTEM, SYSAUX and TEMP tablespaces are always included. - : All user tablespaces will be included in the final PDB. This is the same as the default action when the clause is omitted. - : Only the "ts1" and "ts2" user tablespaces will be included in the final PDB. - : All user tablespaces except the "ts1" tablespace will be included in the final PDB. - : No user tablespaces will be included in the final PDB. Let's try some of these examples using regular clones. Although this functionality was available in 12.1.0.2, we're running testing it in 12.2, so we can do hot clones. In addition, we are using Oracle Managed Files (OMF), so we don't need to name datafiles, or provide file name conversion information. For examples of clones without using OMF, check out the article here . Create a pluggable database called "pdb2" to use as our starting point. The new pluggable database includes three additional tablespaces we will use for the tests. We can see the three tablespaces are online and there is a datafile associated with each of them. Create a new pluggable database called "pdb3" based on "pdb2", but only bringing across the "ts1" and "ts2" tablespaces. The result is the "ts3" tablespace is marked as offline, and there is no datafile present for it. Let's try again, but this time bringing across all tablespaces except the "ts1" tablespace. This time the "ts1" tablespace is offline, and there is no datafile associated with it. Let's try again, but this time bringing across none of the tablespaces. As expected, all three tablespaces are marked as offline, and none of them have datafiles.

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
USER_TABLESPACES =
  { ( 'tablespace' [, 'tablespace' ]... )
  | NONE
  | ALL [ EXCEPT ( 'tablespace' [, 'tablespace' ]... ) ]
  }

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_admin IDENTIFIED BY Password1;
ALTER PLUGGABLE DATABASE pdb2 OPEN;

ALTER SESSION SET CONTAINER = pdb2;

CREATE TABLESPACE ts1 DATAFILE SIZE 1M;
CREATE TABLESPACE ts2 DATAFILE SIZE 1M;
CREATE TABLESPACE ts3 DATAFILE SIZE 1M;


-- Check the status of the tablespaces.
SELECT tablespace_name, status
FROM   dba_tablespaces
WHERE  tablespace_name LIKE 'TS%'
ORDER BY 1;

TABLESPACE_NAME                STATUS
------------------------------ ---------
TS1                            ONLINE
TS2                            ONLINE
TS3                            ONLINE

SQL>


-- Check the status of the datafiles.
SET LINESIZE 110
COLUMN file_name FORMAT A90

SELECT file_name, status
FROM   dba_data_files
WHERE  tablespace_name LIKE 'TS%'
ORDER BY 1;

FILE_NAME                                                                                  STATUS
------------------------------------------------------------------------------------------ ---------
/u02/oradata/CDB1/8245598298A44438E055000000000001/datafile/o1_mf_ts1_g6rpst7l_.dbf        AVAILABLE
/u02/oradata/CDB1/8245598298A44438E055000000000001/datafile/o1_mf_ts2_g6rpt7gc_.dbf        AVAILABLE
/u02/oradata/CDB1/8245598298A44438E055000000000001/datafile/o1_mf_ts3_g6rpt7ht_.dbf        AVAILABLE

SQL>

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb3 FROM pdb2
USER_TABLESPACES=('ts1', 'ts2')
;
ALTER PLUGGABLE DATABASE pdb3 OPEN;

ALTER SESSION SET CONTAINER = pdb3;


-- Check the status of the tablespaces.
SELECT tablespace_name, status
FROM   dba_tablespaces
WHERE  tablespace_name LIKE 'TS%'
ORDER BY 1;

TABLESPACE_NAME                STATUS
------------------------------ ---------
TS1                            ONLINE
TS2                            ONLINE
TS3                            OFFLINE
SQL>


-- Check the status of the datafiles.
SET LINESIZE 110
COLUMN file_name FORMAT A90

SELECT file_name, status
FROM   dba_data_files
WHERE  tablespace_name LIKE 'TS%'
ORDER BY 1;

FILE_NAME                                                                                  STATUS
------------------------------------------------------------------------------------------ ---------
/u02/oradata/CDB1/824576FAA9994614E055000000000001/datafile/o1_mf_ts1_g6rq6og0_.dbf        AVAILABLE
/u02/oradata/CDB1/824576FAA9994614E055000000000001/datafile/o1_mf_ts2_g6rq6og0_.dbf        AVAILABLE

SQL>

CONN / AS SYSDBA

-- Drop the existing PDB.
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

CREATE PLUGGABLE DATABASE pdb3 FROM pdb2
USER_TABLESPACES=ALL EXCEPT('ts1')
;
ALTER PLUGGABLE DATABASE pdb3 OPEN;

ALTER SESSION SET CONTAINER = pdb3;


-- Check the status of the tablespaces.
SELECT tablespace_name, status
FROM   dba_tablespaces
WHERE  tablespace_name LIKE 'TS%'
ORDER BY 1;

TABLESPACE_NAME                STATUS
------------------------------ ---------
TS1                            OFFLINE
TS2                            ONLINE
TS3                            ONLINE

SQL>


-- Check the status of the datafiles.
SET LINESIZE 110
COLUMN file_name FORMAT A90

SELECT file_name, status
FROM   dba_data_files
WHERE  tablespace_name LIKE 'TS%'
ORDER BY 1;

FILE_NAME                                                                                  STATUS
------------------------------------------------------------------------------------------ ---------
/u02/oradata/CDB1/8245970E95E3480CE055000000000001/datafile/o1_mf_ts2_g6rqp2ql_.dbf        AVAILABLE
/u02/oradata/CDB1/8245970E95E3480CE055000000000001/datafile/o1_mf_ts3_g6rqp2ql_.dbf        AVAILABLE

SQL>

CONN / AS SYSDBA

-- Drop the existing PDB.
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

CREATE PLUGGABLE DATABASE pdb3 FROM pdb2
USER_TABLESPACES=NONE
;
ALTER PLUGGABLE DATABASE pdb3 OPEN;

ALTER SESSION SET CONTAINER = pdb3;


-- Check the status of the tablespaces.
SELECT tablespace_name, status
FROM   dba_tablespaces
WHERE  tablespace_name LIKE 'TS%'
ORDER BY 1;

TABLESPACE_NAME                STATUS
------------------------------ ---------
TS1                            OFFLINE
TS2                            OFFLINE
TS3                            OFFLINE
SQL>


-- Check the status of the datafiles.
SET LINESIZE 110
COLUMN file_name FORMAT A90

SELECT file_name, status
FROM   dba_data_files
WHERE  tablespace_name LIKE 'TS%'
ORDER BY 1;

no rows selected

SQL>
2

The Extended USER_TABLESPACES Clause in 12.2.0.1 Onward

In Oracle 12.2 the clause was extended to allow you to specify how those tablespaces are processed during clone and plugin operations. These are independent of how the rest of the clone or plugin operation is processed. It is just relevant to the affected tablespaces. The new options are explained below. For clone operations. - : Create the specified tablespaces using snapshot clones. - : Create the specified tablespaces, but don't bring the data. For plugin operations. - : Copy the datafiles to the new location. - : Move the datafiles to the new location. - : Don't copy or move the datafiles to the new location. The , and plugin processing is independent of that specified for the PDB plugin operation. These might be useful when splitting a multi-application non-CDB instance into multiple separate PDBs. Imagine we had a non-CDB instance containing three user tablespaces, each supporting a separate application. We might convert this to three separate PDBs by first describing the non-CDB instance as an XML file. What is happening here on a per-PDB basis? - The datafiles to support the main tablespaces (SYSTEM, SYSAUX, TEMP) are copied from the non-CDB location into the new PDB location to create the equivalent tablespaces in the new PDB. - The datafiles to support the specified user tablespace are moved into place, which means they are no longer in the original location. This is fine as each PDB will require separate datafiles anyway. - Once the new PDB is created we run the "noncdb_to_pdb.sql" script to clean it the data dictionary of the PDB. - We open the PDB. Once the plugins were complete, we would go through each PDB, cleaning out the unwanted tablespace definitions and schema definitions etc. 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
USER_TABLESPACES =
  { ( 'tablespace' [, 'tablespace' ]... )
  | ALL [ EXCEPT ( 'tablespace' [, 'tablespace' ]... ) ]
  | NONE
  }
[ SNAPSHOT COPY | NO DATA | COPY | MOVE | NOCOPY ]

export ORACLE_SID=orcl
sqlplus / as sysdba <<EOF

SHUTDOWN IMMEDIATE;
STARTUP OPEN READ ONLY;

BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/tmp/orcl.xml');
END;
/

SHUTDOWN IMMEDIATE;

EXIT;
EOF

-- Application 1
CONN / AS SYSDBA
CREATE PLUGGABLE DATABASE app1_pdb USING '/tmp/orcl.xml' COPY
USER_TABLESPACES=('ts1') MOVE
;

ALTER SESSION SET CONTAINER=app1_pdb;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
ALTER PLUGGABLE DATABASE app1_pdb OPEN;


-- Application 2
CONN / AS SYSDBA
CREATE PLUGGABLE DATABASE app2_pdb USING '/tmp/orcl.xml' COPY
USER_TABLESPACES=('ts2') MOVE
;

ALTER SESSION SET CONTAINER=app2_pdb;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
ALTER PLUGGABLE DATABASE app2_pdb OPEN;


-- Application 3
CONN / AS SYSDBA
CREATE PLUGGABLE DATABASE app3_pdb USING '/tmp/orcl.xml' COPY
USER_TABLESPACES=('ts3') MOVE
;

ALTER SESSION SET CONTAINER=app3_pdb;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
ALTER PLUGGABLE DATABASE app3_pdb OPEN;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!