How should I redesign database to handle heavy transactions and reporting on Oracle Database

I tried to connect /as sysdba and I got the following error:

ERROR:
ORA-09817: Write to audit file failed.
SVR4 Error: 28: No space left on device
ORA-09945: Unable to initialize the audit trail file
SVR4 Error: 28: No space left on device


I am running Oracle 10g on Solaris 9 OS. I checked the partition where the Oracle binaries are stored and it is at 100% capacity. I don't know where to begin to fix this problem. I asked the SA to add more space to that partition and I was going to hopefully move some database files around on other partitions but I think whatever filled the /uol partition will fill any other partition I place the database files on. I know there is a lot of activity on one database. Here is what the files and their sizes look like:

-rw-r--r--   1 oracle   dba      7061504 Feb  2 22:34 control01.ctl
-rw-r--r--   1 oracle   dba      7061504 Feb  2 22:34 control02.ctl
-rw-r--r--   1 oracle   dba      7061504 Feb  2 22:34 control03.ctl
-rw----r--   1 oracle   dba      26222592 Feb  2 01:30 indx01.dbf
-rw-r--r--   1 oracle   dba      52429312 Feb  2 01:00 redo01.log
-rw-r--r--   1 oracle   dba      52429312 Feb  2 22:31 redo02.log
-rw-r--r--   1 oracle   dba      52429312 Feb  1 12:00 redo03.log
-rw-r--r--   1 oracle   dba      178266112 Feb  2 22:31 sysaux01.dbf
-rw-r--r--   1 oracle   dba      398467072 Feb  2 22:31 system01.dbf
-rw-r--r--   1 oracle   dba      20979712 Feb  2 06:02 temp01.dbf
-rw----r--   1 oracle   dba      10493952 Feb  2 01:30 tools01.dbf
-rw-r--r--   1 oracle   dba      209723392 Feb  2 22:31 undotbs01.dbf
-rw-r--r--   1 oracle   dba      5251072 Feb  2 01:30 users01.dbf

Any suggestions on how to better manage this space and the database would be much appreciated.
I spoke with the person performing work on this database and she is doing the following:

CREATE INDEX
SELECT ... ORDER BY
SELECT DISTINCT ...
SELECT ... GROUP BY
SELECT . . . UNION
SELECT ... INTERSECT
SELECT ... MINUS

This also includes importing millions of records daily and performing the above queries. The table These millions of records are imported into is truncated prior to the import.
sikyalaSenior Database AdministratorAsked:
Who is Participating?
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.

SmilingPixieCommented:
The errors that you are getting are related to the audit trails that have been set up on your system.  Can you tell me what the DB_AUDIT_TRAIL parameter is set to?  It looks like it might be pointing to  DB which would create all of your audit trail records into the system tablespace.  One suggestion, if possible is to change the audit trail to write your data to an external directory that is separate (but protected) from the rest of your database files.  
0
sonicefuCommented:
The audit trail must be deleted/archived on a regular basis to prevent the SYS.AUD$ table growing to an unnacceptable size.


http://www.oracle-base.com/articles/8i/Auditing.php
http://www.oracle-base.com/articles/10g/DatabaseSecurityEnhancements10g.php#audit_trail_contents

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

AUDIT_TRAIL = { db | os | none | true | false | db_extended }

http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10755/initparams015.htm#i1125027
0

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
sikyalaSenior Database AdministratorAuthor Commented:
When I emptied the audit files from the audit directory under /u01 where the audit logs were being written to I was able to regain enough space to log into the database.
0
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
Unix OS

From novice to tech pro — start learning today.