Forum Stats

  • 3,726,642 Users
  • 2,245,234 Discussions
  • 7,852,335 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Partial table size of oracle table

User_N0D2U
User_N0D2U Member Posts: 48 Red Ribbon
edited September 2018 in General Database Discussions

Hi All,

I am on oracle apps R12. I want to know the total space consumed by few tables of oracle apps.

I know we can find out the total space from dba_segments table but I have a condition. I want to know the partial size of the table but i dont have any partitions on the table.

For eg. I want to know how much space SCOTT.EMP table is consuming for the records where date_of_joining > '01-jan-2018'.

Is it possible to put a condition while calculating the space ?

Tagged:
User_WVSC7

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited September 2018
    3177489 wrote:Hi All,I am on oracle apps R12. I want to know the total space consumed by few tables of oracle apps.I know we can find out the total space from dba_segments table but I have a condition. I want to know the partial size of the table but i dont have any partitions on the table.For eg. I want to know how much space SCOTT.EMP table is consuming for the records where date_of_joining > '01-jan-2018'.Is it possible to put a condition while calculating the space ?

    If statistics are representative enough you can count the rows that satisfy your condition and multiply that by the avg_row_length of the table. This will not take into account the normal overheads like the headers of blocks, this will typically add another 25-50 %

    Remember that just because they might be using up this space, doesn’t mean it’s easy to reclaim the space just by deleting rows. And realise that rows are stored in blocks, your rows might be located one in each block so you might consider them as taking up the full size of the table

    User_N0D2U
  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,529 Bronze Crown
    edited September 2018

    If you can count the number of rows that satisfy your conditions and the total number of rows, you can get a  of the total that those rows represent. Let's say your table has 1.000.000 rows and 220.000 of them satisfy your condition, then 22% of the rows are those that interest you.

    You can calculate how much space the table and indexes use, and then apply that 22% to each of them. That will take into account al the overhead introduced by Oracle (free space, rowids size in indexes, etc) but also all the space that is wasted (if you deleted a lot of rows, the segments would still keep that space, and this approach would take this "wasted" space as part of the tables / indexes).

    Hope this helps!

    Lisandro

    User_WVSC7
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited September 2018

    user3177489, here is another way to estimate how much space your rows of interest require.  Count the number of rows that meet your requirement.  Divide by the total number of rows and take this percentage against the DBA_SEGMENTS BYTES or BLOCKS and/or against the DBA_TABLES.BLOCKS depending on if you want allocated vs used space.  These figures includes overhead.  Comparing these numbers to the avg_row_len X num_rows with and without overhead allowance may also be useful depending on your ultimate goal: extract into separate tables, into csv files, get numbers to support argument for purge, etc....

    - -

    HTH -- Mark D Powell --

  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited September 2018

    Hello 3177489,

    you might do:

       CREATE TABLE just_to_know AS SELECT * FROM original_table WHERE the_conditions_that_you_want;

    and check the size of this table (then drop it), but maybe this is not very useful... This data is more or less "intricated" in the rest of the data... And BTW (if your interest is "academical"), you should maybe also take into account part of the space used by the indexes if any...

    Something else: may I suggest you to modify your "display name" to something more human-friendly than 3177489? Plase have a look at

    Best regards,

    Bruno Vroman.

  • Unknown
    edited September 2018
    For eg. I want to know how much space SCOTT.EMP table is consuming for the records where date_of_joining > '01-jan-2018'.

    Why? WHAT PROBLEM are you trying to solve?

    In general knowing that value won't be of ANY use at all.

    1. deleting those rows does NOT mean you can recapture all of that space for some other purpose

    2. copying those rows to another table does NOT mean they will take up that same amount of space in that other table. They might take us MUCH MORE or much less.

    3. the space needed for those rows in the table does NOT take into account the space needed in the indexes on that table

    So rather than try to generate a seemingly useless value why don't you just tell us WHY you are trying to do this?

  • jgarry
    jgarry Member Posts: 13,842
    edited September 2018

    In addition to what the others said, you could do an original export of the table with a where clause to limit it to the rows you want.  This will give a fairly accurate size estimate of the rows without the index size.

    However, there is a lot of overhead in Oracle that may or may not affect how accurate any of the suggestions are, so that's why it is important to know why you are asking.

    Such things may include:

    pct free (extra space in tables to allow for new rows)

    block splits (indices)

    compression (which may vary within a table)

    column order (trailing null columns)

    row migration (updating rows so they don't fit and go somewhere else)

    LOB's may have special considerations

    wasted space

    deleted space (oracle is actually fairly good about reclaiming space, but there are some data patterns that can have odd effects)

    backup size

    propagated data size

    archived log size

    bugs

    So, it depends.

  • User_N0D2U
    User_N0D2U Member Posts: 48 Red Ribbon
    edited September 2018

    Hi Jgarry,

    We are creating a server that will be shared among other applications.

    So I need to define how much space I will be using(not exact but approximate), only that will be allocated to our project.

  • User_N0D2U
    User_N0D2U Member Posts: 48 Red Ribbon
    edited September 2018

    Hi Lisandro/Mark,

    I did the same thing for calculating the space. I was wondering if there is any other method.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited September 2018
    3177489 wrote:Hi Jgarry,We are creating a server that will be shared among other applications.So I need to define how much space I will be using(not exact but approximate), only that will be allocated to our project. 

    Are different applications using the same tables but the rows are not to be shared by applications? That's very very very weird.

    At least use different tables. Probably even better off to use different schema.

  • User_N0D2U
    User_N0D2U Member Posts: 48 Red Ribbon
    edited September 2018

    Applications are sharing storage not tables or schema.

    You can consider it like I need to create partitions on a hard drive and all applications will use different partitions but I need to define the size of my partition.

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,529 Bronze Crown
    edited September 2018

    Right now, I do not see any other way to estimate a table size taking into account the normal wasted space. You can export the table, filtering the rows you want to preserve, and import into a new schema on a new tablespace (where you can easily calculate the actual size), but you will get data and indexes reorganized so it may not be the same size as the data currently is using (in average).

    You can rebuild the table / indexes to tidy up the storage space so you can (probably) reduce it, but running those calculations on the current data and current storage consumption will give you a more realistic estimate.

  • Unknown
    edited September 2018
    Applications are sharing storage not tables or schema.You can consider it like I need to create partitions on a hard drive and all applications will use different partitions but I need to define the size of my partition.

    That is why you need to do TESTING - that is the ONLY way to get a good size estimate - I gave you many of the reasons for that in my previous reply.

    You also need to take into account things you haven't mentioned at all - particularly  expected growth rates based on any DML or loads that might be performed.

  • jgarry
    jgarry Member Posts: 13,842
    edited September 2018

    As has been mentioned, testing is the only way to know for sure.  There are some common issues to watch out for.

    If you use export/import, you may find that some tables that don't have much actual data have an initial value that reflects the largest the table has ever been.  You want to modify this before the export.

    Edit: I just remembered a big one; using parallel load, especially with append may waste some space. See https://blogs.oracle.com/optimizer/space-management-and-oracle-direct-path-load

    Some tables may have fairly efficiently filled up their blocks on updates, and so expand to the size + pctfree*size when imported as pctfree is honored.

    Generally check the tables parameters, you'll probably want to pre-create them before import with proper parameters anyways.  You probably want to do repetitive tests as you find anomalies, especially very volatile or updated tables with special settings.

    Sometimes it makes sense to pre-create tables with lots of old data that won't be updated, with a smaller pctfree.  Then modify them to the proper pctfree for new data.  Of course, you have to watch out for subsequent reorganizations undoing this.

    There may be other considerations having to do with time windows and other site-specific details.

    I've generally done this with archiving data, but it is another possibility if you want to do the opposite, that is, keep newer data: put the data in different tablespaces, then transport those.  You could also do that after cloning the db to the new system, if you need to keep processing off the old system.

This discussion has been closed.