HELP! out of space in System tablespace 
Author Message
 HELP! out of space in System tablespace

I could really use some sage advice on this one.  I'm not really a DBA,
but I'm being asked to help solve a problem on a system that doesn't
really have any DBA.  The person who set up their databases knew just
enough to be dangerous, I'm afraid, and now she's gone anyway.

The application programmer trying to log in with a user-id that has DBA
priveleges.  When he does, he gets the following error messages:

Enter user-name: xxxxxxxx
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 12137 in tablespace SYSTEM
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 12137 in tablespace SYSTEM

OK, this tells me the System tablespace is full, I think.  I can read
the documentation, and it appears I need to add a new datafile to that
tablespace.  But to do that, I need to log in as a DBA, right?  And I
can't log in as a DBA without getting the error message.  Sounds like
Catch-22.

I get the same error messages trying to log into SVRMGR, too.

Is there any solution to this paradox?

Thanks much,

--
Bob M.

Step outside.  The graphics are AMAZING!

Sent via Deja.com http://www.***.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 HELP! out of space in System tablespace

Should be.
That dangerous DBA has set up auditing, and the AUD$ table, which probably
has useless info, is causing problems.
Disable auditing by changing the audit_trail parameter to false in
init<sid>.ora.
Bounce the database, probably by (NT) just stopping the service or (Unix)
kill the SMON process. You will force a crash, but Oracle should recover
from that as this is a 'power dip' only.
Then bring it up again.
You should be able to login now, and delete from sys.aud$ immediately.
If you don't do that the problem will repeat itself.

Hth,

--
Sybrand Bakker, Oracle DBA

Quote:
> I could really use some sage advice on this one.  I'm not really a DBA,
> but I'm being asked to help solve a problem on a system that doesn't
> really have any DBA.  The person who set up their databases knew just
> enough to be dangerous, I'm afraid, and now she's gone anyway.

> The application programmer trying to log in with a user-id that has DBA
> priveleges.  When he does, he gets the following error messages:

> Enter user-name: xxxxxxxx
> Enter password:
> ERROR:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01653: unable to extend table SYS.AUD$ by 12137 in tablespace SYSTEM
> ORA-02002: error while writing to audit trail
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01653: unable to extend table SYS.AUD$ by 12137 in tablespace SYSTEM

> OK, this tells me the System tablespace is full, I think.  I can read
> the documentation, and it appears I need to add a new datafile to that
> tablespace.  But to do that, I need to log in as a DBA, right?  And I
> can't log in as a DBA without getting the error message.  Sounds like
> Catch-22.

> I get the same error messages trying to log into SVRMGR, too.

> Is there any solution to this paradox?

> Thanks much,

> --
> Bob M.

> Step outside.  The graphics are AMAZING!

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 HELP! out of space in System tablespace

(if that email address didn't require changing)

Quote:

>I could really use some sage advice on this one.  I'm not really a DBA,
>but I'm being asked to help solve a problem on a system that doesn't
>really have any DBA.  The person who set up their databases knew just
>enough to be dangerous, I'm afraid, and now she's gone anyway.

>The application programmer trying to log in with a user-id that has DBA
>priveleges.  When he does, he gets the following error messages:

>Enter user-name: xxxxxxxx
>Enter password:
>ERROR:
>ORA-00604: error occurred at recursive SQL level 1
>ORA-01653: unable to extend table SYS.AUD$ by 12137 in tablespace SYSTEM
>ORA-02002: error while writing to audit trail
>ORA-00604: error occurred at recursive SQL level 1
>ORA-01653: unable to extend table SYS.AUD$ by 12137 in tablespace SYSTEM

>OK, this tells me the System tablespace is full, I think.  I can read
>the documentation, and it appears I need to add a new datafile to that
>tablespace.  But to do that, I need to log in as a DBA, right?  And I
>can't log in as a DBA without getting the error message.  Sounds like
>Catch-22.

if you connect internal or as sys -- you won't be audited.  That way -- you can
either

- add space to the SYSTEM tablespace or...
- truncate the sys.aud$ table (if you don't care about the auditing information
that is in there)

Quote:
>I get the same error messages trying to log into SVRMGR, too.

>Is there any solution to this paradox?

>Thanks much,

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st


Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation



Wed, 18 Jun 1902 08:00:00 GMT
 HELP! out of space in System tablespace
Yeah.  Do truncate the AUD$ table, set the AUDIT_TRAIL=FALSE and then
issue AUDIT NONE owner.table_name for all the tables that have auditing
enabled.  What's probably filling up the AUD$ is DML audit trails from
ill-advised AUDIT ALL commands!
Quote:

> Should be.
> That dangerous DBA has set up auditing, and the AUD$ table, which probably
> has useless info, is causing problems.
> Disable auditing by changing the audit_trail parameter to false in
> init<sid>.ora.
> Bounce the database, probably by (NT) just stopping the service or (Unix)
> kill the SMON process. You will force a crash, but Oracle should recover
> from that as this is a 'power dip' only.
> Then bring it up again.
> You should be able to login now, and delete from sys.aud$ immediately.
> If you don't do that the problem will repeat itself.

> Hth,

> --
> Sybrand Bakker, Oracle DBA


> > I could really use some sage advice on this one.  I'm not really a DBA,
> > but I'm being asked to help solve a problem on a system that doesn't
> > really have any DBA.  The person who set up their databases knew just
> > enough to be dangerous, I'm afraid, and now she's gone anyway.

> > The application programmer trying to log in with a user-id that has DBA
> > priveleges.  When he does, he gets the following error messages:

> > Enter user-name: xxxxxxxx
> > Enter password:
> > ERROR:
> > ORA-00604: error occurred at recursive SQL level 1
> > ORA-01653: unable to extend table SYS.AUD$ by 12137 in tablespace SYSTEM
> > ORA-02002: error while writing to audit trail
> > ORA-00604: error occurred at recursive SQL level 1
> > ORA-01653: unable to extend table SYS.AUD$ by 12137 in tablespace SYSTEM

> > OK, this tells me the System tablespace is full, I think.  I can read
> > the documentation, and it appears I need to add a new datafile to that
> > tablespace.  But to do that, I need to log in as a DBA, right?  And I
> > can't log in as a DBA without getting the error message.  Sounds like
> > Catch-22.

> > I get the same error messages trying to log into SVRMGR, too.

> > Is there any solution to this paradox?

> > Thanks much,

> > --
> > Bob M.

> > Step outside.  The graphics are AMAZING!

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 HELP! out of space in System tablespace
I read in a manual that you should never turn on auditing unless you have
enough room in your tablespace because you can run into this endless loop.
Or set up the audit not not grow more then x and make sure x will not fill
up your tablespace.  The database is trying to audit your signon but can not
because the tablespace is full.  You can not dump the data or add to the
tablespace because you can not sign on.

You might try to do an export and then import the data into a new database.

Have you tried signing on as internal in svrmgr to see if you can get
somewhere?

Quote:

>I could really use some sage advice on this one.  I'm not really a DBA,
>but I'm being asked to help solve a problem on a system that doesn't
>really have any DBA.  The person who set up their databases knew just
>enough to be dangerous, I'm afraid, and now she's gone anyway.

>The application programmer trying to log in with a user-id that has DBA
>priveleges.  When he does, he gets the following error messages:

>Enter user-name: xxxxxxxx
>Enter password:
>ERROR:
>ORA-00604: error occurred at recursive SQL level 1
>ORA-01653: unable to extend table SYS.AUD$ by 12137 in tablespace SYSTEM
>ORA-02002: error while writing to audit trail
>ORA-00604: error occurred at recursive SQL level 1
>ORA-01653: unable to extend table SYS.AUD$ by 12137 in tablespace SYSTEM

>OK, this tells me the System tablespace is full, I think.  I can read
>the documentation, and it appears I need to add a new datafile to that
>tablespace.  But to do that, I need to log in as a DBA, right?  And I
>can't log in as a DBA without getting the error message.  Sounds like
>Catch-22.

>I get the same error messages trying to log into SVRMGR, too.

>Is there any solution to this paradox?

>Thanks much,

>--
>Bob M.

>Step outside.  The graphics are AMAZING!

>Sent via Deja.com http://www.deja.com/
>Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. How to decide how much space to allocate for SYSTEM tablespace

2. ANALYZE and space in SYSTEM tablespace

3. help with space in tablespaces

4. moving tables from the system tablespace to a new tablespace

5. System tablespace - help!

6. Help, system tablespace 99% full

7. Help Re: Size of DMS Tablespaces from IBM system tables

8. SYSTEM schema in SYSTEM tablespace

9. Altering system tablespace or system cluster

10. SYSTEM user not in SYSTEM tablespace

11. ***Help! System Table Space Data


 
Powered by phpBB® Forum Software