8 Replies Latest reply: Apr 13, 2012 9:37 AM by John-MK RSS

    System tablespace getting full very fastly....ply help urgently

    John-MK
      Hi,

      System tablespace is getting full very fastly and i am extending the data file right now.....what is this FGA_LOG$ TABLE....is it audit ??? should I truncate it ?? Please help....its very urgent.. ....{11g 11.2.0.2 RHL}

      Thanks and regards
      SQL> select owner, segment_name, segment_type, bytes/1024/1024 "MB" from dba_segments 
        2  where tablespace_name = 'SYSTEM' AND rownum <=100
        3  AND bytes/1024/1024 > 1 order by bytes desc;
      
      OWNER                      SEGMENT_NAME           SEGMENT_TY         MB
      ------------------------------ ------------------------- ---------- ----------
      SYS                      FGA_LOG$            TABLE            1849
      SYS                      IDL_UB1$            TABLE             248
      SYS                      SOURCE$                TABLE              63
      SYS                      C_OBJ#_INTCOL#           CLUSTER         36
      SYS                      IDL_UB2$            TABLE              29
      SYS                      SYS_LOB0000000394C00013$$ LOBSEGMENT         24
      SYS                      C_TOID_VERSION#           CLUSTER         23
      SYS                      I_H_OBJ#_COL#           INDEX              16
      SYS                      JAVA$MC$            TABLE              16
        • 1. Re: System tablespace getting full very fastly....ply help urgently
          EmaxG
          Yes its auditing. Revisit your policies
          • 2. Re: System tablespace getting full very fastly....ply help urgently
            sybrand_b
            As this is a forum of volunteers and is not free support. using 'very urgent' qualifies as rude.

            -----------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: System tablespace getting full very fastly....ply help urgently
              mseberg
              Hello;


              Oracle 11g Release 2 allows better management of the audit trail using the DBMS_AUDIT_MGMT package. Because you can move the Standard audit trail (AUD$) table it makes sense to put audit in it own tablespace. The tablespace for audit can be checked by issuing the following SQL:
              SELECT 
                  table_name, tablespace_name
              FROM   
                dba_tables
              WHERE  
                table_name IN ('AUD$', 'FGA_LOG$')
              ORDER BY 
                table_name;
              CREATE TABLESPACE AUDIT_AUX......


              Then move the standard audit trail to the new tablespace. (as sysdba)

              Connected to:
              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              SQL> BEGIN
                    DBMS_AUDIT_MGMT.set_audit_trail_location(
                      audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
                      audit_trail_location_value => 'AUDIT_AUX');
                  END;
                  /
              Best Regards

              mseberg
              • 4. Re: System tablespace getting full very fastly...
                John-MK
                Hi,

                Thanks a lot for replying. You are always quick.. I appreciate it really.

                I have heard that we have a job to regularly clearn this FRA_LOG$ log table. But right now I was thinking to TRUNCATE it, but before truncating I wanted to EXPORT it ..as a safe side...but not able to export
                [oracle@db1 ~]$ expdp system tables=FGA_LOG$ dumpfile=fra_logs.dmp logfile=fra_logs_audit.log
                
                ORA-39166: Object SYS.FGA_LOG$ not found.
                ORA-31655: No data or Metadata ....
                Thank you again.

                Regards,

                @sybrand
                I am sorry if you mind it....I was and am checking simultaneously different options...checked MOS...and they suggested to truance it or deletel for example 2 weeks data...so so when somebody is nervous or worried,,,,he doesnt know what he's doing.. and I dont think its rude....Its a kind request....its Please not order....and because I respect the Volunteers by heart and rely on them a lot ..

                Edited by: 910385 on Apr 13, 2012 6:19 AM
                • 5. Re: System tablespace getting full very fastly...
                  713555
                  typo in your expdp tables command. FRA ... not FGA.

                  edit: sorry it is FGA, make sure you can see it as system in the db.

                  Edited by: deebee_eh on 13-Apr-2012 06:25
                  • 6. Re: System tablespace getting full very fastly...
                    mseberg
                    Hello;

                    SYS.FGA_LOG$ is correct. If I had the disk space I would just move it as shown.

                    The other option would full export, or maybe OWNER.

                    And thanks for this - "Thanks a lot for replying. You are always quick.. I appreciate it really." ( if I have time I try to make an effort )

                    Best Regards

                    mseberg


                    Export Test

                    vi out.parm

                    userid=system/<password>
                    tables=sys.FGA_LOG$, sys.AUD$
                    file=test.dmp
                    log=log.out
                    STATISTICS=none


                    exp parfile=out.parm


                    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    Export done in US7ASCII character set and AL16UTF16 NCHAR character set
                    server uses WE8MSWIN1252 character set (possible charset conversion)

                    About to export specified tables via Conventional Path ...
                    Current user changed to SYS
                    . . exporting table FGA_LOG$ 0 rows exported
                    . . exporting table AUD$ 546120 rows exported
                    Export terminated successfully without warnings.

                    Edited by: mseberg on Apr 13, 2012 9:11 AM
                    • 7. Re: System tablespace getting full very fastly...
                      John-MK
                      I think it's correct ...its FGA ...not FRA...FRA is something else
                      SQL> select owner, segment_name, segment_type, bytes/1024/1024 "MB" from dba_segments
                        2  where segment_name='FGA_LOG$';
                      
                      OWNER               SEGMENT_NAME      SEGMENT_TYPE               MB
                      ------------------------------ ------------------------------------- ------------------------------ ----------
                      SYS                FGA_LOG$            TABLE           1849
                      • 8. Re: System tablespace getting full very fastly...
                        John-MK
                        You know what .. :) I have truncated it and now the space usage has down to 24% from 94%....Initially I didn't see this log table or AUD table...so I extend the datafile in the system tablespace....because I had very less time for investigating..

                        I tried many time to use expdp before truncating...but I dont know why it was not working for me...

                        Actually you prefer to edit you post instead of posting new ones....sometimes one can miss it....so did I......

                        Now I tried

                        SQL>!exp
                        - -
                        username: system
                        ..
                        Tables=sys.AUD$
                        SO SO.....

                        but now I tried it and it worked fine...

                        Thanks a lot for your help...

                        And yes...that moving to different tablespace is great idea....I will consider it ..

                        Regards.