Oracle 19c Server

Tablespaces

Creating a Tablespace for LeasePak Data

1. Log on the DBMS host as the Oracle software owner.
2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to the PDB by entering at the sqlplus prompt:

   SQL> alter session set container = PDB-name;

4. Create a tablespace with one initial datafile by entering at the sqlplus prompt:

   SQL> CREATE TABLESPACE tablespace-name
         DATAFILE '/PDB-datafile-directory/tablespace-name_01.dbf'
         SIZE size-of-datafile AUTOEXTEND OFF;

   The 01 in the datafile name is the datafile sequence number.

   For example, if the CDB name is orcl, the PDB name is lpak, the PDB datafile directory is /opt/dbdata/oradata/ORCL/lpak, the tablespace name is lpcommon, the datafile sequence number is 01, the datafile name is lpcommon_01.dbf, and the datafile size is 4G:

   SQL> CREATE TABLESPACE lpcommon
         DATAFILE '/opt/dbdata/oradata/ORCL/lpak/lpcommon_01.dbf'
         SIZE 4G AUTOEXTEND OFF;

   The datafile created by the above command does not have the AUTOEXTEND attribute enabled. Autoextend is not recommended, because a runaway process could mistakenly increase the size of the datafile. Autoextend also makes it more difficult to measure the actual amount of free space in a filesystem.

Adding a New Datafile to an Existing Tablespace

1. Log on the DBMS host as the Oracle software owner.
2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to the PDB by entering at the sqlplus prompt:

   SQL> alter session set container = PDB-name;

4. Add a new datafile to an existing tablespace by entering at the sqlplus prompt:

   SQL> ALTER TABLESPACE tablespace-name
         ADD DATAFILE '/PDB-datafile-directory/tablespace-name_datafile-sequence-number.dbf'
         SIZE size-of-new-datafile AUTOEXTEND OFF;

   For example, if the CDB name is orcl, the PDB name is lpak, the PDB datafile directory is /opt/dbdata/oradata/ORCL/lpak, the tablespace name is lpcommon, the datafile sequence number is 02, the name of the new datafile is lpcommon_02.dbf, and the size of the new datafile is 4G:

   SQL> ALTER TABLESPACE lpcommon
         ADD DATAFILE '/opt/dbdata/oradata/ORCL/lpak/lpcommon_02.dbf
         SIZE 4G AUTOEXTEND OFF;

Increasing the Size of an Existing Datafile

1. Log on the DBMS host as the Oracle software owner.
2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to the PDB by entering at the sqlplus prompt:

   SQL> alter session set container = PDB-name;

4. Increase the size of an existing datafile by entering at the sqlplus prompt:

   SQL> ALTER PLUGGABLE DATABASE
         DATAFILE 'path-to-datafile'
         RESIZE new-size-of-datafile;

   For example, if the CDB name is orcl, the PDB name is lpak, the PDB datafile directory is /opt/dbdata/oradata/ORCL/lpak, the tablespace name is lpcommon, the datafile name is lpcommon_01.dbf, the current size of the datafile is 4G, and the new size of the datafile is to be 8G:

   SQL> ALTER PLUGGABLE DATABASE
         DATAFILE '/opt/dbdata/oradata/ORCL/lpak/lpcommon_01.dbf'
         RESIZE 8G;

Optimal Size and Number of Datafiles

The optimal size and number of datafiles for LeasePak data depends on the projected size of the database. The datafile size should be large enough to limit the total number of datafiles to a manageable number. For a small database, a 4 GB size for datafiles is probably sufficient. For medium to large databases, a datafile size larger than 4 GB may be warranted. The maximum datafile size is 32 GB.

Tablespace Naming Conventions

LeasePak database type	Recommended tablespace name
production	lptsuffix-of-LDB-name
large test	lptsuffix-of-LDB-name
small test	lpcommon

where the suffix-of-LDB-name refers to the characters after the lpr prefix in the LeasePak database name.

  Each lpt* tablespace should be dedicated to a single LeasePak database. The lpcommon tablespace may be shared by multiple small test LeasePak databases.