10 Replies Latest reply: Jun 16, 2014 5:15 PM by Andrew Brennan RSS

    Excessive datafile space used

    chuckers

      I have been receiving an alert from Enterprise Manager about a tablespace being at 97% capacity. The schema which uses this tablespace has not been accumulating data in a year. If you look in EM, you can see that there are are 2 datafiles, both at just about the 32Gb limit, but one of the two actually has about 22Gb free within it due to data removal.

       

      I didn't have time to research why I was getting this alert when the second datafile had plenty of space, and so I just added another datafile to the tablespace last week to try & get it to go away. That was 2 weeks ago. I checked today, and the third datafile that I'd set up at a meager 10Mb, is now 32Gb and is completely empty. My only guess was that I'd accidentally specified this tablespace as a temporary tablespace for an active schema, but that's not the case.

       

      What would cause this expansion of a hollowed out tablespace, if no data is moving in & out? Is there a way I could track something like this?

       

      Thanks,

      --=Chuck

        • 1. Re: Excessive datafile space used

          'If no data is moving in and out ' ---> this can not be possibly true. Also: even if one would TRUNCATE a table or DROP it, this doesn't resize the datafile.

          Plus: the datafile is on autoextend. So if there is any rogue process consuming all space, nobody will notice it until the maximum size of the datafile is reached.

           

          ----------

          Sybrand Bakker

          Senior Oracle DBA

          • 2. Re: Excessive datafile space used
            Mark D Powell

            Chuck, any chance any of the tables assigned to the tablespace are work/report  tables where data is loaded then the table is truncated afterwards?  Also any chance the tablespace was used to copy another object into then the object was moved back?

            - -

            If the file extended then some object needed the space.  That or someone manually extended the data file for the talespace in question.

            - -

            Do you audit DDL?  If so, you can check the audit trail for activity against objects assigned to the tablespace.

            - -

            HTH -- Mark D Powell --


            • 3. Re: Excessive datafile space used
              chuckers

              Up until this point, the supposition that "no data was changing" was based on the Lead Analyst's comments for the application, so it could be that there's something that he missed.

               

              I checked DBS_USERS for the tablespace_name in DEFAULT_TABLESPACE and TEMPORARY_TABLESPACE, as well as DBA_TABLES for the TABLESPACE_NAME, and this tablespace is being used as expected, and only for this application ... and not for any temp tablespace.

               

              I do not audit DDL.

               

              I did look at the LAST_ANALYZED date for all tables in the single schema which use this tablespace, and only one lists last night as las_analyzed. There were 2 other tables in 2014, and all of the rest were in 2013.

               

              I suppose I could buy the fact that some data movement is occurring, but, if I sat through 3 months ignoring that "97% full" alert (in a tablespace only 1/3 full), and then within 2 weeks of setting up a new datafile Oracle auto-extended the file through to 32Gb, it seemed like something else was going on.

              --=Chuck

              • 4. Re: Excessive datafile space used
                Mark D Powell

                Chuck, if there is a production database then I suggest you consider auditing DDL operations.  DDL should be rare with the possible exception of truncate.  If you audit DDL be sure to write a purge job to clean up the audit trail.

                - -

                With 11g the default behavior of the DBCA is to turn auditing on for DDL, which I point out as support for my position that this is something you should do.

                - -

                You could try shrinking the file and then watching to see if it re-extends.

                - -

                HTH -- Mark D Powell --

                • 5. Re: Excessive datafile space used
                  jgarry

                  Maybe you thought you were specifying 10MB for the size, but put it in a NEXT statement instead?  Were you using command line or EM or something else?  There ought to be an "ADD DATAF" string in your text alert log.

                  • 6. Re: Excessive datafile space used
                    chuckers

                    I put in an next statement, but it was "NEXT 100M"

                     

                    ALTER TABLESPACE STRCTRDATA

                      ADD DATAFILE '/u02/oradata/drxmp/STRCTR_data03.dbf' SIZE 10M

                      AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

                     

                    I was assuming that since this was a dead application (in terms of data use) it wasn't worth constraining the max size, nor the allocation of additional space when needed. I thought it was going to stay 10M for forever.

                    • 7. Re: Excessive datafile space used
                      EdStevens

                      What would cause this expansion of a hollowed out tablespace, if no data is moving in & out? Is there a way I could track something like this?

                       

                      logminer

                      • 8. Re: Excessive datafile space used
                        thtsang

                        How did you work out that the datafile is empty? And is there anything in the recyclebin?

                        • 9. Re: Excessive datafile space used
                          rp0428

                          I have been receiving an alert from Enterprise Manager about a tablespace being at 97% capacity. The schema which uses this tablespace has not been accumulating data in a year. If you look in EM, you can see that there are are 2 datafiles, both at just about the 32Gb limit, but one of the two actually has about 22Gb free within it to to data removal.

                          1. How do you know that the one schema 'has not been accumulating data' for a year?

                          2. How do you know one of the data files has 22 GB free?

                          I didn't have time to research why I was getting this alert

                          You haven't posted ANY information that supports a conclusion that you even have a problem.

                           

                          The first step in troubleshooting is to verify that a PROBLEM actually exists.

                          • 10. Re: Excessive datafile space used
                            Andrew Brennan

                            Turn off autoextend and enable tracing for ORA-01652 errors. Wait for it to fill up and then you'll know what's doing it.

                             

                            alter system set events '1652 trace name errorstack level 12';