Creating or Dropping a Tablespace (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Creating or Dropping a Tablespace

Use

You can use SAPDBA for Oracle to create or drop a tablespace. The SAP System can be either active or closed when you create or drop a tablespace.

You need not shut down the database system if you want to create or drop a tablespace.

Prerequisites

You have configured and started SAPDBA. Refer to Getting Started with SAPDBA.

SAPDBA only supports the creation of tablespaces that correspond to the naming conventions for SAP tablespaces ( PSAP<name> ). You can create the tablespaces PSAPROLL and PSAPTEMP with SAPDBA, but not SYSTEM . However, you can extend the SYSTEM tablespace.

For more information, see SAP Naming Conventions for Tablespaces and Data Files.

When you drop a tablespace, SAPDBA first checks that it is empty. You cannot drop a tablespace that is not empty. Do not normally delete the tablespace PSAPTEMP : if you need to do so for exceptional reasons, you must stop the SAP System first.

Procedure

Creating a Tablespace

Choose Tablespace Administration ® Tablespace and enter the name of the tablespace that you want to create.

SAPDBA validates the name you choose, making sure that it conforms to SAP naming conventions.

Choose Create tablespace <tablespace name>.

SAPDBA shows a new menu for creating the tablespace.

SAPDBA proposes a path and size for the new data file. It also displays the details of the file system or raw device and its free space, as in the following example for a file system:

Example

New path: '/oracle/LMA/sapdata1/btabd_2/btabd.data2'

New size: 121624 K

File system: '/dev/dsk/c1t1d0s2'

Free space: 121624 K

To change the path of the new file, choose New path, Select new path from a list of file systems, or Select new path from a list of raw devices.

You can only change the path, not the data file name. In the above example, you could change /oracle/LMA/sapdata .

For more information, see Specifying the Path for the New Data File.

To change the size for the new data file, choose New Size and enter the size in bytes, kilobytes, megabytes, gigabytes, or terabytes.

For more information, see Specifying the Size for the New Data File.

To define more than one new data file, do the following:

Choose Define more than one new data file and select the next free entry.

SAPDBA displays default values for the new data file, as shown above for the first new data file.

Accept the default values or change them as required.

To set Autoextend from the default OFF to ON, do the following:

Choose Autoextend.

SAPDBA prompts you for the incremental and maximum file sizes used in automatic table extension:

The incremental size is the size of the new file that is automatically added to the tablespace to prevent overflow.
The maximum size is the maximum size for automatic extension of the tablespace. Beyond the maximum size, you have to extend the tablespace manually.

Enter the incremental and maximum file sizes.

To change the default storage parameters, do the following:

Choose Default Storage Parameters.

SAPDBA displays the default values, such as in the following example:

a –	INITIAL	16 K
b –	NEXT	40 K

c –	MINEXTENTS	1
d –	MAXEXTENTS	100

e –	PCTINCREASE	0

For more information on the parameters, see the Oracle documentation.

Change the default values as required.

Choose Start to begin adding the data file.

SAPDBA checks that that there is enough space to add the new data file, showing a display like the following:

Example

SAPDBA: Checking space for data file of tablespace

-------------------------------------------------------------

File system : '/dev/dsk/c1t1d0s2'

Current free space : + 20792 K

New data files : - 1600 K

Left free space : = + 19192 K -> O.K.

-------------------------------------------------------------

SAPDBA also performs some other checks before adding the file. For more information, see Checks for Tablespace Extension and Creation.

Enter RETURN to add the data file(s).

SAPDBA adds the new data file(s) to create the tablespace and also generates a log, <timestamp>.ext . You can check this log to verify that the tablespace creation was successful.

SAPDBA creates a directory or a symbolic link for the data file. The control files (old and new) are backed up under the directory <timestamp> belonging to the log file <timestamp>.ext . SAPDBA then creates the data file using CREATE TABLESPACE WITH <tablespace details> . If any of these operations fail, SAPDBA stops immediately.

Note

If SAPDBA cannot add the new data file, it deletes the corresponding directory or symbolic link and cancels the tablespace creation. Your system and database are unchanged. Use the Oracle ALERT and trace files to analyze and correct the problem, then repeat the tablespace creation.

When SAPDBA has successfully created the new data file, it automatically goes to the Backup database menu, so that you can back up the new tablespace.

Be sure to back up the new tablespace. For more information, see Backing Up After Tablespace Operations.

Dropping a Tablespace

Choose Tablespace Administration ® Tablespace and enter the name of the tablespace that you want to drop.

Choose Drop tablespace <tablespace name>.

SAPDBA displays information about the tablespace, such as in the following example:

Tablespace PSAPBTABD consists of

3 files (total size 436384 K)

Enter y to drop the tablespace.

SAPDBA checks that the tablespace is empty and drops it.

Note

If SAPDBA cannot drop the tablespace, use the Oracle ALERT and trace files to analyze and correct the problem, then repeat the tablespace drop.

Restart the database to be sure of reclaiming the space occupied by the dropped tablespace.