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
0 Comment to "Tablespace reorganization on Oracle"
Post a Comment