iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 12c New Features

Online Oracle Training

LESSON 15

How to manage tablespaces in a CDB (Multitenant Container) and PDBs (Pluggable Databases)

 

Let us view permanent and temporary tablespaces properties in mycdb using the DATABASE_PROPERTIES view.

 

Login to mycdb

SQL> connect / as sysdba

SQL> SELECT property_value, property_name

   2   FROM database_properties

   3   WHERE property_name LIKE ‘DEFAULT%TABLE%’;

 

PROPERTY_VALUE

PROPERTY_NAME

TEMP

DEFAULT_TEMP_TABLESPACE

USERS

DEFAULT_PERMANENT_TABLESPACE

 

List all tablespaces in your CDB.

SQL> SELECT con_id, tablespace_name FROM cdb_tablespaces ORDER by 1;

 

CON_ID

TABLESPACE_NAME

1

SYSTEM

1

SYSAUX

1

UNDOTBS1

1

TEMP

2

SYSTEM

2

SYSAUX

3

SYSTEM

3

SYSAUX

4

SYSTEM

4

SYSAUX

5

SYSTEM

5

SYSAUX

5

TEMP

5

USERS

6

SYSTEM

6

SYSAUX

6

TEMP

6

USERS

6

EXAMPLE

Notice that you have repeating SYSTEM and SYSAUX in all the containers. Also, you can have as many tablespaces on any pluggable databases.

 

For example, you may have the TEMP tablespace in all PDBs.

List all tablespaces in your CDB that are using the TEMP tablespace.

SQL> SELECT con_id, tablespace_name FROM cdb_tablespaces

   2   WHERE tablespace_name = ‘TEMP’

   3   ORDER by 1;

 

CON_ID

TABLESPACE_NAME

1

TEMP

2

TEMP

3

TEMP

4

TEMP

5

TEMP

6

TEMP

All PDBs have the TEMP tablespace.

 

Create a tablespace (mytbs) in the root container.

SQL> CREATE TABLESPACE mytbs

   2   DATAFILE ‘/oradata/mycdb/mytbs01.dbf’

   3   SIZE 150M;

 

List the tablespaces in your CDB.

SQL> SELECT con_id, tablespace_name FROM cdb_tablespaces

   2   WHERE tablespace_name = ‘MYTBS’;

 

CON_ID

TABLESPACE_NAME

1

MYTBS

Notice that the con_id value is 1. That means the tablespace was created the root container.

 

Change your default tablespace from USERS to MYTBS.

SQL> ALTER DATABASE DEFAULT TABLESPACE mytbs;

 

List your default tablespaces.

SQL> SELECT property_value, property_name

   2   FROM database_properties

   3   WHERE property_name LIKE ‘DEFAULT%TABLE%’;

 

PROPERTY_VALUE

PROPERTY_NAME

TEMP

DEFAULT_TEMP_TABLESPACE

MYTBS

DEFAULT_PERMANENT_TABLESPACE

Notice that your default permanent tablespace was changed to MYTBS.

 

Now, let us create a permanent tablespace (mytbs4local) in one of your pluggable database (mypdb1).

First, you need to login to the mypdb1 pluggable database.

SQL> connect sys/mypass@mypdb1 as sysdba

Connected.

 

Create a tablespace (mytbs4local) in mypdb1.

SQL> CREATE TABLESPACE mytbs4local

   2   DATAFILE ‘/oradata/mycdb/mypdb1/mytbs4local01.dbf’

   3   SIZE 150M;

Tablespace created.

 

Make the mytbs4local tablespace the default tablespace in the mypdb1 container.

SQL> ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE mytbs4local;

Pluggable database altered.

 

List your default tablespaces in the mypdb1 container.

SQL> SELECT property_value, property_name

   2   FROM database_properties

   3   WHERE property_name LIKE ‘DEFAULT%TABLE%’;

 

PROPERTY_VALUE

PROPERTY_NAME

TEMP

DEFAULT_TEMP_TABLESPACE

MYTBS4LOCAL

DEFAULT_PERMANENT_TABLESPACE

Notice that your default permanent tablespace is MYTBS4LOCAL in the mypdb1 container.

 

Create a temporary tablespace (temp4mypdb1) in the mypdb1 container.

SQL> CREATE TEMPORARY TABLESPACE temp4mypdb1

   2   TEMPFILE ‘/oradata/mycdb/mypdb1/temp4mypdb1_01.dbf’

   3   SIZE 200M;

Tablespace created.

 

List your default tablespaces in the mypdb1 container.

SQL> SELECT property_value, property_name

   2   FROM database_properties

   3   WHERE property_name LIKE ‘DEFAULT%TABLE%’;

 

PROPERTY_VALUE

PROPERTY_NAME

TEMP4MYPDB1

DEFAULT_TEMP_TABLESPACE

MYTBS4LOCAL

DEFAULT_PERMANENT_TABLESPACE

Notice that your default permanent tablespace is MYTBS4LOCAL and its default temporary tablespace is TEMP4MYPDB1 in the mypdb1 container.

 

Good Luck!

 

 

Google
 
Web web site