How to reduce tablespace in Oracle?

[Former Member]

Hi Basis expert,

Please kindly guide me How to reduce tablespace in Oracle.

We have a lot of free size in tablespace because of spool deletion.

Our problem is a lot of disk size when we run full database backup .

Please kindly suggest.

Thank you very much. 🙂

My OS is Windows 2003

DB: Oracle 10g.

In DB02 table space shown below

Tablespace Name	Size (MB)	Free (MB)	Used (%)
PSAPSR3	250,000.00	173,731.38	31
PSAPSR3701	70,350.00	8,727.56	88
PSAPSR3USR	290.00	97.75	66
PSAPTEMP	2,220.00	2,220.00	0
PSAPUNDO	10,000.00	6,366.50	36
SYSAUX	1,284.00	798.94	38
SYSTEM	1,894.00	967.81	49
Sum	336,038.00	192,909.94

[former_member192421 · Accepted solution]

Hi Saiya,

There is one alternative to Reorganization. This is called "Segment Shrinking".
Segment Shrinking is available as of Oracle Release 10g. To do this few pre-requisites must be met.
1. Tables with LONG and LONG RAW fields cannot be shrunk.
2. The table you want to shrink must occur in an ASSM tablespace.
3. Compressed tables cannot be shrunk.

This method has few advantages over Reorganization:
1. Compared to an offline reorganization, there is no downtime.
2. While an online reorganization temporarily requires twice the space, segment shrinking does not require any additional space requirements.
3. The amount of redo log information during segment shrinking is less than with an online reorganization.

As of BR*Tools Release 7.00, segment shrinking can be performed using BRSPACE --> Shrink Tables.

There are various SAP notes also available in service market place. Just check them out.

910389 - FAQ: Oracle Segment Shrinking

11515 - Shrinking/deleting rollback tablespaces

821687 - FAQ: Space utilization and fragmentation in Oracle

541538 - FAQ: Reorganization

Hope this may help you a bit.

Regards,

Rajkumar

[Former Member]

Dear All Expert,

Thank you very much for your value suggestion.

Now I have to stop this task because of I have to wait new server is coming.

Thank you very much again.

Best Regards,

Saiya

[Former Member]

hi all,

i am new to the BASIS,

our Manager asked me to a procedure for reduce the table space. kindly help me for below issue.

PSAPSID size 2.5TB- 400 GB free(aftre table space reorganization)

means 2.1TB data fulled,

Our problem is a lot of disk size when we run full database backup .

we want only 100 GB free space, and we needs to delete remaining 300GB from oracle to filesystem .

kindly help me

[Former Member]

Hi all expert,

I have one question.

After Converting the LONG fields into LOB fields, Do I have to convert it back ( LOB to LONG)?

Thank you very much .

Saiya

[Former Member]

Note 835552 - Oracle Database 10g: LONG-LOB migration contains the following:

This online migration works only from LONG to LOB. It is not possible to migrate back from LOB to LONG in this way!

Also read recommendations for LONG-LOB migration in that note.

Regards,

Roman

[former_member192421]

Hi Saiya,

If you have plan for Oracle upgrade in near future, then you should not.

This is because, Oracle has announced that the support of LONG fields will be discontinued as of Oracle 11.

As of Oracle 10g, online redefinition will be possible for tables containing LONG fields; LONG fields will then be redefined as LOB (Large Object) fields.

SAP will support accessing LOB fields through LONG interface as of Oracle 10g, then it will be possible to redefine tables containing LONG fields into tables with LOB fields using BRSPACE.

SAP plans to support conversion from LONG to LOB fields.

Then conversion can be done online with BRSPACE (Oracle 10g). After the conversion, all SAP tables can be reorganized online.

Regards,

Rajkumar

[Former Member]

Hi Maria,

Thank you very much for your support.

Now I 'm facing LOB issue. Please kindly guide to convert

1. Creating a new tablespace

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

brspace -u / -f tscreate -t <new_tsp> -d both -l <reorg_tsp>

My command>> brspace -u / -f tscreate -t psapprd_new -d both -l psapprd

2. Reorganizing tables into the new online tablespace

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

brspace -u / -f tbreorg -s <reorg_tsp> -t "*" -n <new_tsp> [-p <par_degree>]

My command>> brspace -u / -f tbreorg -s psapprd -t "*" -n psapprd_new -p 3

BR1110W Table SAPPRD.ZRMCONT07 has a LONG (RAW) column

BR1111I Reorganization of table SAPPRD.ZRMCONT07 will be skipped

BR0280I BRSPACE time stamp: 2013-02-08 18.44.15

BR1112I Number of tables selected/skipped for reorganization: 45969/842

We have 842 table has LONG (RAW) column to be skipped. Please kindly guide me how to convert them before to this step. Or We can ignore this message.

Thank you very much. 🙂

How to reduce tablespace in Oracle?

[Former Member]

Please read Note 646681 - Reorganizing tables with BRSPACE

III. Converting the LONG fields into LOB fields online as of Oracle 10g

Regards,
Roman

[Former Member]

Could you tell me step by step.

For Example.

1. Go to brtools.

2. Enter 3 Segment management

3. ....

Thank you very much.

[sunny_pahuja2]

Hi,

Please do google and you will find lot of documents on this.

Thanks

Sunny

[Former Member]

Hi,

Please see the below link and check whether it helps you.

http://help.sap.com/saphelp_nw04/helpdata/en/1b/4e8f0d38a8f4419436d608a36b6581/content.htm

it contains the step by steps procedure.

I Hope it helps..

Thanks and Regards,

Vimal

[Former Member]

Hi Sunny,

I'm trying 2 day ago for google.

I use brtools and reorg table: TST03 already with successful

but disk space does not return.

Please kindly guide me step by step.

[sunny_pahuja2]

Hi,

If you will do table  reorg then space will freed up in tablespace not in the disk. You want to free space in disk then do tablespace reorganization.

Thanks

Sunny

[Former Member]

Could you guide me for Tablespace reorganization?

Thank you very much.

[sunny_pahuja2]

Check below link:

http://help.sap.com/saphelp_bw30b/helpdata/en/0d/d2fa7b4a0c11d182b80000e829fbfe/content.htm

Thanks

Sunny

[Former Member]

Tablespace reorganization with BRSPACE as of Oracle 10g

=======================================================

What is known as a "tablespace reorganization" (reorganizing all tables, while simultaneously creating the tablespace) is relatively simple as of an Oracle 10g environment. This uses the new Oracle 10g feature of renaming tablespace. The prerequisite, however, is that the affected tablespace has no tables with LONG fields, or else they must be converted beforehand into LOB fields (see section III). As a result, you must continue to use the procedure described in section II for tablespaces such as those from a SAP Basis Release 4.6C system that include LONG fields. However, this procedure can be used in higher releases as of Oracle 10g for migrating to locally managed tablespaces.

A tablespace reorganization is carried out in following steps, where:

<reorg_tsp> - is the tablespace to be reorganized

<new_tsp> - is the new tablespace that has another name temporarily

1. Creating a new tablespace

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

The new tablespace <new_tsp> is created in such a way that the tab types (table data class) are transferred from the tablespace <reorg_tsp>:

brspace -u / -f tscreate -t <new_tsp> -d both -l <reorg_tsp>

Of course, the new tablespace must be large enough to admit all the tables from the tablespace to be reorganized (you may need to use Auto Extend).

2. Reorganizing tables into the new online tablespace

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

brspace -u / -f tbreorg -s <reorg_tsp> -t "*" -n <new_tsp> [-p <par_degree>]

3. Deleting the old tablespace including data files

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

brspace -u / -f tsdrop -t <reorg_tsp>

4. Renaming the new tablespace

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

brspace -u / -f tsalter -a rename -t <new_tsp> -n <reorg_tsp>

The data files are also renamed in this process. This action requires the new tablespace to be set "offline" for a short period of time, which can disturb the running of the SAP system. Therefore, you can carry out this process asynchronously, when the system load is at its lowest.

5. Create new statistics for the reorganized tables

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

brconnect -u / -c -f stats -t <reorg_tsp> -f collect -p 4

Or if the new tablespace has not yet been renamed:

brconnect -u / -c -f stats -t <new_tsp> -f collect -p 4

[Former Member]

Hi Maria,

Thank you very much. I'll try as your step by step.

Thank you again.

Best Regards,

Saiya

[Former Member]

Okay. Please have a backup in place before performing the tablespace re-org.

Please check and let us know if you are facing any issues.

[Former Member]

Hi

Pleasse take this and  adhere to your scenario

As guided in note 35952, when the clients are deleted the blocks are
allocated as free, however these are not yet visible as freespace yet.
You will need to perform a reorganization to resolve this.
Regarding reorganization, please kindly check the following notes:

Note 541538 - FAQ: Reorganization
Note 646681 - Reorganizing tables with BRSPACE
Note 53062 - Database reorganization and data archiving

See also SAP Note 821687 - FAQ: Space utilization and fragmentation in Oracle

for details.

Regards,

Mahipal

[sunny_pahuja2]

Hi,

In order to release the free space from the tablespace, you should perfom reorganization. Please check below link:

http://help.sap.com/saphelp_nw70/helpdata/en/11/06a53fa6503c17e10000000a114084/content.htm

Thanks

Sunny