4 Replies Latest reply: May 5, 2014 11:01 AM by Mike Kutz RSS

    Table with less than 1Mb Data consuming 300Mb


      I am confused and needing some clarification on space utilization with a table.  Please forgive me if the question is a simple answer, I am drawing a blank on explaining it.


      I have performed a datapump export from Enterprise Edition and imported into XE.  I have a number of tables with this issue, but I am unsure about how to explain it.


      The table has 567 rows with the average row length of 85.  It shows 42 Blocks on what I believe is a 16K block database (I didn't modify the default for the Windows 11gXE).


      The table initial extent was set to 300.  I presume that was a problem with how it was originally created and datapump just replicated it's orginal sizing, but next extent is set to 1Mb.  The table indicates 10 Extents and a total size of 304Mb.


      Question 1:  If the data is only (42 blocks * 16K =) 672K then why would the system have an additional 9 extents added when initial table size was 300Mb? 


      Question 2:  If the initial size was 300 Mb and 9 additional extents are created why is the table size 304Mb instead of (300 + 1Mb * 9 extents=) 309Mb?


      Question 3:  Is there an easy way to resize a table that is setup like this, and if so how do we do it?  I'd like to get usage and growth rate statistics from the user and set it for 1 year with next extents representing 3 month growth rates.


      Thank you in advance,


        • 1. Re: Table with less than 1Mb Data consuming 300Mb
          Mike Kutz

          First, there is a difference between table and tablespace (and data files).

          (default block size is 8k, not 16k)


          It is very hard for me to understand exactly which one you are talking about.

          Please show the SQL statement(s) you used to get your numbers.


          Regarding Question 1

          What other data is in that tablespace?

          Did you include the size needed for LOBs?

          Did you include the size needed for Indexes?

          Did you purge the recycling bin prior to importing the data?


          Regarding Question 2

          Your assumption that Oracle actually does what you tell it to do is wrong.

          Just because you say "1MB extents" doesn't mean it will do that.

          By default, oracle does not assign extents in a uniform manner.

          The "4MB" increase is probably more like "1 block + 1 block + 20 blocks + 100 blocks + 1 block .... "



          Regarding Question 3a -- resize table

          again -- do you mean "TABLE", "TABLESPACE", or underlying data files?

          Disk drives are CHEAP.  Oracle XE is limited to a mere 11GB of data.  My smartphone holds more data than that.

          In other words - Don't worry about it.

          And.. when you start getting close to the 11GB limit, that just means its time to "step up your game" and upgrade to Standard Edition.


          Regarding Question 3b.i -- tracking growth rate

          There are a number of ways to do that.


          Regarding Question 3b.ii -- presetting the extent size

          Do not adjust the default values unless you have proven that you need to adjust them and have the scientific proof to back you up.  (ie benchmarks)

          Again, you are on XE.. with a 1 CPU, 1GB RAM, 11GB disk limit.

          There is no way you can stress the DB to the point where you need to adjust the defaults.



          • 2. Re: Table with less than 1Mb Data consuming 300Mb

            Thank you for your response.  In this scenario we are dealing with a production system that management has chosen to use Oracle Express for the next year or two before upgrading to SE.  As such I need to make sure that I have good control and awareness over what it is doing.  I currently manage about 40 different Oracle databases on XE, SE, and EE editions from version 9.2.0 through


            In this situation I had a manager who also codes decide to create the tables using a create table extracted in Toad without changing the initial and next sizing so we have 15 tables that are consuming 304Mb, some of which have a static data set of less than 1Mb.  This is a serious problem when utilizing XE which has a hard limit of 11Gb total datafile size on the instance.  What I was not understanding was why the extents were behaving the way they were.


            In response to your responses...


            Re Question 1:  No LOBS are involved and I am talking about tables, not tablespaces so the index is not a factor.


            Re Question 2:  This may explain the issue, especially if the original table was 304Mb, so the datapump created 300 and added misc extents to get 304.  That being said your response is very disconcerting.  The parameters are there for a purpose and if the Oracle binary is going to ignore them and do it's own thing then it takes that management ability away from the DBA and the parameter should be discontinued.  As it is not, I question your answer as there must be some logical reason which causes the system to ignore the parameter which we just don't understand.


            Re Question 3a:  I am referring to Table, not tablespace, and have come to the conclusion I have to recreate the tables sized appropriately.  In this case I am using Toad to create the script with all insert statements as there is very little data.  I will then modify the table definition to be appropriately sized, drop the old table, and run the script.  If there is a faster and cleaner way to resize a table I'm open to hear it, but I don't believe there is.


            Re Question 3b(i):  Tracking the data growth within a table appears to be only possible through multiplying the block consumption value from the data dictionary by the block size of the instance.  (Yes I know it is 8, I checked it after my last post.  I was assuming 16 as that is what I have my EE and SE instances set to).  If there are other ways to track the actual data storage growth inside the table object (other than line count which can be highly variable) I would be interested in knowing what they are or where I can find that documented.


            Re Question 3b(ii):  For years I have followed a best practice I was originally taught that suggested the Initial Table Size should reflect the row length * the number of rows anticipated during the next year of utilization, and the next extent size should reflect the next 3 months anticipated growth, or 6 months for an aggressively growing database.  Just because this is XE does not mean efficient and effective management of system resources is not important, on the contrary, I would say it is even more important due to the restricted size.  The exception would be if XE completely ignores these parameters and settings.  To my knowledge it does not, do you know otherwise?


            Again I appreciate your response, but I am not a new kid on the block, and the reason I am asking the question is becuase it is in regards to Table, not Tablespace.  I basically wanted to know if there was a tool or function that would allow me to resize the Table due to foolish developer mistakes.  Such a tool I could see being of great benefit in many situations.  As I indicated in my response 3a, I think I have to do this the old fashioned way and just recreate the table (I've already sent an email out to the whole department reminding them about table sizing and that they should run their scripts by me before they run any DDL into the database).

            • 3. Re: Table with less than 1Mb Data consuming 300Mb
              Mike Kutz

              For correcting the parameters, have you tried?

              ALTER TABLE ... MOVE;


              Beyond that, the only tool I'm aware of would be: DBMS_REDEFINITION

              (I'm only aware of that package.  I don't know if it exists for XE)


              For historical tracking, I would use the results of the DBMS_SPACE package.

              Tom Kyte has some sample code that uses it (show_space).  Just make sure you use the updated one that is for 11g+



              • 4. Re: Table with less than 1Mb Data consuming 300Mb
                Mike Kutz

                Adam wrote:

                Re Question 2:  ... I question your answer as there must be some logical reason which causes the system to ignore the parameter ....


                I did some research on the validity of my answer for Q2..

                It appears that, under some circumstances, Oracle will "ignore" the value you set for NEXT.

                storage_clause - NEXT


                "In locally managed tablespaces, the size of the NEXT is determined by Oracle if the tablespace is set for autoallocate extent management."

                (I believe both settings are defaults.)