Extending a Tablespace (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Extending a Tablespace

Use

You can use SAPDBA for Oracle to extend a tablespace, that is, add a data file. The SAP System can be either active or closed when you extend a tablespace.

You need not shut down the database system if you want to add a data file to a tablespace.

Note

Instead of adding a new data file to extend a tablespace, you can also use SAPDBA to:

Resize existing data files.

Switch on the AUTOEXTEND option for existing data files

Prerequisites

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

SAPDBA only supports the addition of data files that correspond to the SAP naming conventions. For more information, see SAP Naming Conventions for Tablespaces and Data Files.

Procedure

Choose Tablespace Administration ® Tablespace and enter the name of the tablespace that you want to extend by adding a data file.

Choose Alter tablespace <tablespace name> add data file.

SAPDBA shows a new menu for altering 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 show the details of existing data files, choose Show existing data files.

SAPDBA displays the details of the file (or files), including its sequence number, size, and full name with path. For example:

# Size in K Data file name (file system)

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

1 335656 /oracle/LMA/sapdata1/btabd_1/btabd.data1 (/dev/dsk/c1t1d0s2)

To change the path for the new data 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 add 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.

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 files of tablespace

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

File system : '/dev/dsk/c1t1d0s2'

Current free space : + 20856 K

New data files : - 56 K

Left free space : = + 20800 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 extend the tablespace and also generates a log, <timestamp>.ext . You can check this log to verify that the tablespace extension 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 with ALTER TABLESPACE ADD DATAFILE . 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 extension. Your system and database are unchanged. Use the Oracle ALERT and trace files to analyze and correct the problem, then repeat the tablespace extension.

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

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