Problem solve Get help with specific problems with your technologies, process and projects.

Cleaning up after Oracle

Oracle uses up lots of disk space and sometimes this space needs to be cleaned out manually. Here's what to look for.

Regardless if you are the DBA or the sysadmin, you need to be concerned with the task of cleaning up the filesystems that Oracle uses. There are three areas to be concerned with, archive logs, trace files, and logs files.

When it comes to Oracle, the biggest space hog is the archive logs. Particularly on high volume system, you need to make sure to cleanup the archive destination. I like to keep archive logs around on disk for a couple days (if I have the space). This will facilitate a quick recovery, or to speed up a hot database copy if necessary. Create a post-exec pr corn job after your archive log backup that will compress the archive logs once you get a copy on tape. Next create another job that will delete the archive logs after the second day of backups.

Another problem area is the trace logs, these file are found in the background dump, core dump, and user dump destinations. You can determine the location for these parameters using the query:

 select name, value from v$parameter where name like '%dump_dest%';

Leave these files around for a couple weeks, in case these are needed by the DBA or Oracle support. Be aware that the core dump destinations are typically directories.

If the audit_trail init.ora parameter is to TRUE, Oracle will create a file in the audit_file_destination directory every time a users uses the connect internal command. These files are small, but they can add up after a while. Oracle log files can also be a area of concern, especially the listener.log, sqlnet.log and alert.log. Here I like to create a script to delete all lines before a certain date, or grab only the last couple hundred lines out of the file (once again depending on the transaction volume).

About the Author

James Giordano is an Oracle database administrator. He has been working with Oracle for about seven years, and also has experience with UNIX and PeopleSoft/Oracle financials.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.