Extending a Tablespace

The R/3 System can be either active or closed when you extend a tablespace.

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

1. Select Tablespace and enter the name of the tablespace you want to extend. Select Alter tablespace Add data file. The following menu options are displayed, depending on whether you want to add a file (in file system) or a raw device (on raw device):

	File system	Raw devices
a -	File system or raw device	File system or raw device New data file name/Required raw device name
b -	File size	Enter raw device name
c -	Select path	Select raw device
d -	Alter suggested path	File size on raw device
e -	Display current files	Display current files
s -	Start (Add datafile)	Start (Add datafile)
q -	Return	Return

File system or raw device: SAPDBA displays whether you are working in a file system (file system) or on raw devices (raw device). At this time you can choose again between working in the file system or on raw devices. If you are working on raw devices, the ORACLE name of the raw device and the required raw device ID are displayed. See also SAPDBA Support for Raw Device Configuration.

2. You can change the default values for the size and path of the new data file.

File system:

• File size: SAPDBA calculates the size of the new data file from the previous size of the tablespaces using one of the following percentages:

– Percentage from the SAPDBA profile ( init.dba ) if parameter tspadd_tspname is set for this tablespace.

– Default value: 30 percent of the tablespace size when no entry exists for this tablespace in the SAPDBA profile.

See tspadd_tspname and Specifying the File Size.

• Select path: SAPDBA displays a list of all sapdata directories and highlights the directories that are already used by the tablespace and its corresponding D/I tablespace (
used ( x), number of files).

You can decide which of the displayed sapdata directories you want to select. If you specify a different directory and this results in the files being moved to a different disk, this may (when the data and index tablespace are on the same disk) cause performance losses due to competing I/O accesses.

See New Path.

• Alter suggested path: SAPDBA suggests a path for the data files according to the SAP naming conventions or displays the path which you selected with Select path . Only the first part of the complete target directory is suggested (
/sapdata). The other part of the directory and the file name itself are defined according to the SAP naming conventions.

See Naming Convention.

You can define another file name (see New Path). In this case SAPDBA checks whether there is sufficient storage space available in the file system which was suggested to store the new data file.

Raw Device:

• Enter raw device name:Enter the name of the required raw device. You can enter the complete path here. If you do not do so, the path defined under select raw device will be used.

SAPDBA checks whether:

- the raw device exists
- ora is the owner
- an ORACLE file of resides there
- a file system (mounted) resides there.

SAPDBA does not check whether the raw device is used by another application and whether the disk is partitioned and the partitions are reserved.

• Select raw device in path: SAPDBA displays a list of the raw devices which can be used to extend the tablespace (owner:
ora, CAUTION: It is up to the database administrator to check that there is no data from other applications in the selected raw device!). See also ORACLE Databases on Raw Devices: Overview.

You can decide which of the displayed raw devivces you would like to select. SAPDBA informs you if the naming convention does not correspond to the SAP norm.If necessary, change the raw device ID as user root .

• File size on raw device: You can reduce the size after selecting a raw device, but the space which remains will not be used.

Display current files: SAPDBA displays the data files of the tablespaces and their sizes.

Depending on the operating system, the database may be limited to a certain number of data files (usually at least 254 files with maximum 2GB per file). If an extension moves you closer to this limit, you should reorganize the tablespaces that contain a large number of data files. This will occur only rarely, however, since the number of available data files (254) is almost always large enough. Therefore, do not reorganize tablespaces of the database until you actually reach this limit.

See Limitations of the Database System.

1. Select Start (Add datafile) to accept the file specification.
2. At this point, SAPDBA checks the block sizes. The following values are displayed:

filesystem block size:
filesystem fragment size:
Oracle block size:

If the sizes do not agree, an appropriate warning message appears and SAPDBA will ask whether you want to continue.

Successful tablespace extension: When SAPDBA has created the new data file, it automatically branches to the Backup database menu, to enable you to back up the extended tablespace.

See Backup Recommended.

Failed tablespace extension: If the data file could not be added, SAPDBA deletes the corresponding directory or symbolic link and cancels the tablespace extension. Your system and database are unchanged.

See SAPDBA Troubleshooting.

Use the ORACLE ALERT and trace files to analyze and correct the problem, then repeat the tablespace extension procedure.

3. SAPDBA generates a log:
.ext. This log can be used to verify that the tablespace extension was successful.

Extending your SAP R/3 tablespaces

I am glad that a lot of Basis visiting my blog, asking me about Basis activity, tips, etc. Based on comments and email about how to extend SAP R/3 tablespace, I try to post this little notes. On this little tutorial, I am using Windows based SAP R/3 and Oracle 9.2. You can do it on UNIX based. Of course using different user. When you using UNIX based R/3 system, you have to use ora[sid] user.

Login to your SAP R/3 DB instance Windows using [sid]adm user. Open your command prompt.

Type BRTOOLS to enter BRTOOLS menu.

Choose menu number 2 by typing 2 and then ENTER.

There are many menus on SPACE Management including Extend tablespace, Create tablespace, etc.

Type 1 to Extend tablespace. To get information about tablespace list, you can use DB02 on your SAPGUI screen. For this tutorial, I want to extend PSAPIDS tablespace.

Type c to continue this step.

On this screen, you can see information about last added tablespace including datafile size, datafile name, etc. I use default (datafile size about 2470 MB). Type C to continue.

If you want to add more datafile, you can specified now by typing y. Right now, I just want to add 1 datafile so I type c.

Extending tablespace process is running.

Tablespace PSAPIDS has been extended successfully. Type c to continue.

Back to BRTOOLS menus. Type 9 and ENTER to exit BRTOOLS program.

To check your new datafile has been added to your tablespace you can use DB02 and then press REFRESH button.

Now, you can extend your tablespace easily and have a nice day.

NB : You don’t need to shutdown your SAP R/3 system or Oracle DB to extend tablespace. You can do this online while your SAP R/3 system and Oracle DB running.

Comparing locally managed and dictionary managed tablespaces

What is the difference between locally managed and dictionary managed tablespaces, and what are the benefits of using a locally managed tablespace?

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks called an extent is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary or in the tablespace itself. Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.

Locally managed tablespaces have the following advantages over dictionary-managed tablespaces:

• Local management of extents avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table.
• Local management of extents tracks adjacent free space, eliminating coalescing free extents.
• Reliance on data dictionary is reduced. This minimizes access to the data dictionary, potentially, improving performance and availability.

Due to these improvements, Oracle recommends using locally managed tablespaces for all new tablespaces if fragmentation is expected to be an issue The only drawback with locally managed tablespaces is that 'used-extent' information is not kept in the data dictionary. It must be read from the segment header blocks (and additional extent map blocks if any) whenever it is required, including queries against DBA_SEGMENTS and DBA_EXTENTS. If a tablespaces with a large number of mostly small segments is locally managed rather than dictionary managed, then access to these views can cause a lot more physical I/O and thus impact the cache retention of user data. Furthermore, if the segments are mostly constant in size then the risk of the tablespace contributing to ST enqueue contention if dictionary managed is low. Thus, there is little motivation to make such a tablespace locally managed and a minor performance risk in doing so.