Reclaim space for Oracle database

I have a production oracle database (instance) where the disk space is full a 100%. how can I reclaim some space in the meantime that I figure out how to add some disk space.

Thank for your help.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
What space is full?  Typically, this is due to archive logs.  Is that the case here?  If it is, back them up using your normal backup method and remove them.  There are too many ways to back them up, so without a lot of additional information, we couldn't give you exact commands.
slightwv (䄆 Netminder) Commented:
I agree that it is likely archived redo logs.

You might also look at trace files and other log files.  In 11g and above those are located in the folder pointed to by the diagnostic_dest spfile parameter.

You also want to check your TEMP datafile.  If it is set to autoextend, one bad query can fill it up.

Other than that, you'll need to tell us where you feel the space is being used.
Mark GeerlingsDatabase AdministratorCommented:
What kind of storage does your Oracle database use?  Is it: NAS, SAN, ASM, local disk, a combination of these, etc.?  Are all parts of your Oracle database, including: data files,  archived redo log files, temp tablespace files, etc. on the same disk drive (or directory, or file system, etc.?)

Without some more-specific information from you, we can't give you specific instructions.  But, I agree with the other suggestions that the likely first places to look are: archived redo log files, trace files, and the database "temp" file(s) which is/are for the temporary tablespace(s) in your database.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

CCVOIPAuthor Commented:
the space that is getting filled is the oradata space ( we have a specific mount for that).
slightwv (䄆 Netminder) Commented:
>>the space that is getting filled is the oradata space

Typically you have a naming convention for file names and tablespaces.

What specific files are using the space?
Should they be using that much space?  In other words, is one of the files MUCH larger than you think it should be?

If so, what caused it to use that much space?
Did a large data load happen?

For example:
If the file is for the USERS tablespace:
select segment_name, segment_type, bytes from dba_segments where tablespace_name='USERS';

It if is the TEMP datafile, then drop and recreate the TEMP tablespace with a smaller file and monitor it.  You might have TEMP set to auto extend with unlimited space.  This can be good or bad depending on your specific database setup.
Mark GeerlingsDatabase AdministratorCommented:
Was this space gradually growing, and it just recently filled the volume (or LUN, or disk drive, etc.) completely?  Or, did some part suddenly expand greatly to fill the space?

Are your Oracle data and temp files on the same volume (or LUN or disk drive)?  What about your alert.log and trace files?  Are these also on the same volume (or LUN or disk drive)?
CCVOIPAuthor Commented:
just got hired and don't know much about the previews space statistics!! it seems that it was gradually filling. checking the space on the OS level with "df -h" shows that my oradata partition is full at 100%, but when I checked the free space for each tablespace, it showed that I have ~=87GB.

Temp files/ alert.log and trace files are all under the same volume.
johnsoneSenior Oracle DBACommented:
Oracle pre-allocates space.  It sounds like you have plenty of free space inside the database, so you are good.  The database has plenty of space to grow into if there is 87GB free.
Mark GeerlingsDatabase AdministratorCommented:
It is certainly possible in an Oracle system, for Oracle to report some freespace inside one or more Oracle tablespaces, but the O/S (and/or storage system) may see all of the space as "used" (or at least allocated to Oracle.  In that case, you may be able to shrink one (or more) Oracle datafiles, so the O/S and/or storage system can see some freespace again.   Would that be a good idea?  Maybe.

You haven't told us anything about your server O/S or your storage system(s).  Which server O/S do you have for Oracle?  And, what kind of disk or storage system do you have?  Is that local disks in a server with a RAID controller, or a NAS or a SAN, or Oracle ASM, or some combination of these?

Is your database running in archivelog mode?  Since almost all production Oracle databases should be, I assume that is yes for you.  Do you have RMAN or another backup process running regularly to back up then delete the archived redo logs from the directory where Oracle writes them?  Do you have these archived redo log files and the Oracle alert and trace files going to a different disk device than your "oradata" partition?
slightwv (䄆 Netminder) Commented:
A word of caution:
Reported free space may be worthless.  Contiguous free space and the next extent size is what is important.  I worked on a database where we had hundreds of gigs of 'free' space reported from DBA_FREE_SPACE but nothing large enough for the next extent size of the objects SO, we kept running out of space.
johnsoneSenior Oracle DBACommented:
This appears to be running out of space at the OS level, not within the database.  Then someone is panicking because they show no free space at the OS level and don't know how the database works, so they scramble to get space that they don't need.

I would wonder if the space was all allocated and never used.  With that much free space, I would almost believe that someone allocated it on purpose.  Look in DBA_FREE_SPACE for very large pieces of free space.

Most places where I have been, we strive to have our data file drives nearly full at all times.  We don't want to be wasting resources.  There is capacity planning that is done and space is allocated for planned growth.  But we run on huge systems with massive disk arrays and getting more space in an emergency doesn't usually take more than a few days.
slightwv (䄆 Netminder) Commented:
>>Then someone is panicking because they show no free space at the OS level and don't know how the database works,

I agree.  I've seen that as well.

>>just got hired and don't know much about the previews space statistics!!

Hopefully you brought your scripts along with you.  If not there are many examples out there.

You'll need to dive into the Data Dictionary to see if you have a real problem or not.
CCVOIPAuthor Commented:
now I have another problem, I opened standby on flashback mode, then turned off the flash back,

checking if the primary and the secondary are in syc using the following query showed:
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                 137288                    37     137251

Open in new window

any idea on how to get the database back in sync!

this is really urgent11111111111111
Mark GeerlingsDatabase AdministratorCommented:
That looks like a separate issue that should be posted in a separate question.  Did you get your space question/problem solved ?
CCVOIPAuthor Commented:
the space issue, almost solved. cleared some space on the database side (up to 200G) by truncating old huge table that no longer needed. that allowed me to free up to 34G for os space. all other space is being allocated by oracle database => need some shrinking to reclaim that space, but before that need to fix the standby issue.
our storage gay is looking on how to add disk space, in the mean time we're safe for few days!!

ANOTHER question on this subject:  can we add another mount  and point oradata files to the new mount when it's asking for more space to be allocated? please advise that would help in making the process of adding space faster and won't requires reboot of the host/database instance
Mark GeerlingsDatabase AdministratorCommented:
"can we add another mount [without a restart of the database]"

"can we ... point oradata files to the new mount"?
New datafiles: yes.
Exisiting datafiles: only if you take them offline first.  (This may require application downtime.)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
Yes, you can add another mount point.  To allocate space on the new mount point, you need to add an additional data file to the tablespace.

You would use the ALTER TABLESPACE ADD DATAFILE, documentation here ->

There are a few examples in the documentation of add a datafile.
CCVOIPAuthor Commented:
so oracle doesn't care to have all it's datafiles located within the same path (path/oradata)
==> we can have
datafile1 => path1/oradata/datafile1
datafile2 => path2/oradata/datafile2

is that right?
Mark GeerlingsDatabase AdministratorCommented:
"so oracle doesn't care to have all it's datafiles located within the same path"
Correct!  Oracle can use *ANY* storage device that is available to the server O/S.  There is *NO* requirement that all Oracle datafiles be on the same path (or directory, or mount point, or physical disk, etc.)  In fact, most Oracle databases in the past 20 years have had Oracle data files spread across a number of different disks/partitions/mount points/LUNs, etc.

Recently, as more people move to SAN or NAS storage, and those storage systems offer multiple physical disks, and multiple paths to the data, plus RAID protection but present these large chunks of space as a single, logical unit, some Oracle databases now may have all of the Oracle datafiles on a single, logical mount point/LUN (or whatever) but that is not an Oracle requirement.
CCVOIPAuthor Commented:
thanks for the quick response, that would help if we surpass the issue of adding another mount without having to reboot the host( and of course not causing a downtime)!!
CCVOIPAuthor Commented:
Thank you all for you r help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.