cancel
Showing results for 
Search instead for 
Did you mean: 

Need to gain space @ OS level after deleting data in Tables

Former Member
0 Kudos
199

Hello Experts,


We are running SAP application (R/3 4.6C) with Oracle 9.2.0.5.

OS version AIX - 5.3


We are out of space to add in OS level for data files so we have selected few tables like "SOC3, SOST, SOOD, SOES, SOCS, SOFM, SOOS, NAST,APQD, TST03 and TBTCP" and deleted huge unusable data expecting to reorg them and gain space in OS level.

We used tool "SAPDBA" to perform reorg on those tables and finished successfully. (BRTOOLS not available to perform reorg)

While performing the reorg we received few errors like below:


missing space in Tablespace PSAPBTABD

for 2 not fitting extents:


So, we added datafiles in different directories (Locally mounted) and proceeded with reorg activity expecting to reduce the size of the newly added datafiles and move to sapdata directories (As in Oracle 9.2 we dont have an option to delete datafile so we are able to resize the datafiles but not all the files we addded. May be data os spread even to those newly added datafiles) sad.gif sad.gif

Though after reorg, the size of the tables decreased and the Allocated space decreased. However, we are unable to reduce the space of all the datafiles we added. Please find below screenshot of Tablespace (Before and After the activity):

Before the Reorg Activity:

TABLESPACE Total Allocated %-Alloc. Files Free areas Largest

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

PSAPBTABD 139929440 128579240 92 90 611 1425904

After the Reorg Activity:

TABLESPACE Total Allocated %-Alloc. Files Free areas Largest

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

PSAPBTABD 155658144 117793184 76 109 856 665600

I couldnt find an easy option with this DB version to perform reorg of tablespace and datafiles to gain space.

Could someone please help me how can we gain space in OS level

Thanks,

Subbu

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hello Experts,

Adding to below:

When we trying to Resize the newly added Datafiles (From 2 GB to 1.5 GB atleast), we are seeing below error for most of the datafiles:

SAPDBA: ALTER DATABASE DATAFILE '/oracle/<SID>/sapdata6/btabd_91/btabd.data91'

        RESIZE 1572856K

ORA-03297: file contains used data beyond requested RESIZE value

           (2016-09-15 23.33.59)

SAPDBA: Error altering size of data file

        '/oracle/<SID>/sapdata6/btabd_91/btabd.data91'

Can someone please help me if there is any other option with minimum downtime of the system to gain some space?

Please assist

Thanks,

Subbu

andreas_herzog
Active Contributor
0 Kudos

The HIGH WATER MARK prevents you from resizing the tablespace to the desired size; you will not be able to succeed unless you find out which table prevents you from downsizing...this/these table(s) need to be reorganized before you will be able to resize the tablespace...

GreetZ, AH

Former Member
0 Kudos

Hi, try to read that good article