Wednesday, 13 August 2025

Tablespace reorganization on Oracle

 How to get back free space in the database / OS Level

In some cases if the DB size too huge but actually many free space available on DB and you need the free space on OS level but difficult to do the reorg for all tables on a tablespace

In this situation we have to do the reorg for entire tablespace, to do the that follow the steps below

Here i am using using PSAPSR3 as the example

1. Creating new tablespace

Use SIDADM / ORASID user to execute the BR* command

brspace -u / -f tscreate -t <new tablespace> -d both -l <old tablespace>

Example -

brspace -u / -f tscreate -t PSAPSR3NEW -d both -l PSAPSR3

Extened tablespace size using below command or using brtools

SQL> alter database datafile '/oracle/OL3/sapdata1/sr3usrnew_1/sr3usrnew.data1' autoextend on;

2. Reorganizing tables into the new online tablespace

brspace -u / -f tbreorg -s <exiting tablespace> -t "*" -n <old tablespace> -p 2

Example -

brspace -u / -f tbreorg -s PSAPSR3 -t "*" -n PSAPSR3NEW -p 2

3. Checking for any objects still placed in 'old' tablespace

confirm all objects got moved into new tablespace using below commands

SQL> select owner,segment_name from dba_segments where TABLESPACE_NAME = 'PSAPSR3';

SQL> select owner,table_name from dba_tables where tablespace_name = 'PSAPSR3';

SQL> select owner,index_name from dba_indexes where tablespace_name = 'PSAPSR3';

Confirm the all commands showing "no rows selected" output, which means no pending object on tablespace, you can drop the table

If some tables are pending (Row tables), use following command to move them to new tablespace

brspace -u / -f tbreorg -a long2lob -t "*" -n  [-n <new tablespace> [-i <old tablespace>]] [-p <par_degree>]

Example -

brspace -u / -f tbreorg -a long2lob -t "*" -n PSAPSR3NEW -i PSAPSR3NEW -p 8

Execute again the commands mentioned on point 3 and confirm all objects got moved into new tablespace

4. Deleting the old tablespace (PSAPSR3USR) including data files

brspace -u / -f tsdrop -t <old tablespace>

Example -

brspace -u / -f tsdrop -t PSAPSR3

If the tablespace dropped but the data files deletion failed, you can manually delete the files also.

But careful while doing the manual deletion.

5. Renaming the new tablespace

brspace -u / -f tsalter -a rename -t <new tablespace> -n <old tablespace>

Example -

brspace -u / -f tsalter -a rename -t PSAPSR3NEW -n PSAPSR3

6. Create new statistics for the reorganized tables

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

Example -

brconnect -u / -c -f stats -t PSAPSR3 -f collect -p 4

For some case the above command will though you the following error,

BR0301E SQL error -1017 at location db_connect-2, SQL statement:
'CONNECT /'
ORA-01017: invalid username/password; logon denied 

On that situation follow the command below,

brconnect -u <user>/<password> -c -f stats -t <old tablespace>  -f collect -p 4

Example -

brconnect -u SAPSR3/admin123 -c -f stats -t PSAPSR3 -f collect -p 4

Once all the above steps completed the free space added to your OS

Share this

0 Comment to "Tablespace reorganization on Oracle"

Post a Comment