Calculating a record length of a table

[Former Member]

Hi All,

In SAP 4.6C Oracle 9g I need to find a record length of a table.For that I am using :

Table name is ABC.

Tablespace in KBytes of ABC X

Total no records in ABC Y

Single record length in Kbytes (Z) = Tablespace (X) / Total no of records(Y)

I would like to know weather is this the correct way to calculate in approximation.I would also like to know any other methods of knowing it.

Any inputs are appreciated.

Thanks,

Priya.

[stefan_koehler · Accepted solution]

Hello Priya,

why calculating?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091

=> AVG_ROW_LEN* NUMBER Average length of a row in the table (in bytes)

But this is only an average (estimated and including row overhead).

The real length of a row depends on the character set... the character itself and the storage of the data (row chaining and so on...).

Regards

Stefan

[Former Member · Accepted solution]

Thank you Stefan,

Actually I am trying to find out how much space does a record occupies so that if I delete say 1 million records of that table how much space will be freed up. I am looking for only approx values.Being an analyst I do not have authorizations to DBA roles.

I would appreciate if you could share more thoughts.

Thanks,

Priya.

[stefan_koehler · Accepted solution]

Hello,

>> if I delete say 1 million records of that table how much space will be freed up

But "freed up" means not ... that all space is used again for next inserts (PCTFREE / PCTUSED in a NON-ASSM environment or PCTFREE with ASSM).

>> Being an analyst I do not have authorizations to DBA roles

It is not necessary to have "direct" database access to get these values.

DB02n or DB02 => Additional Functions => Display DBA-Views => ALL_TABLES

Regards

Stefan

[Former Member · Accepted solution]

Thankyou very much Stefan.Now I am clear..

I know that after major deletion frm db its necessary to do reorganization to enhance the db utilization.

Pls help me to understand your statement that freed space isnt used by next entries.

Thanks,

Priya.

[stefan_koehler · Accepted solution]

Hello,

>> I know that after major deletion frm db its necessary to do reorganization to enhance the db utilization.

Not mandatory ... it depends on many factors...

>> Pls help me to understand your statement that freed space isnt used by next entries.

It maybe not used for the next inserts... that depends on your tablespace (if it uses the ASMM feature) and essentially on the following parameters

- PCTUSED

- PCTFREE

Oracle Link: http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/logical.htm

=> The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block

=> The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by PCTFREE, Oracle Database considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle Database uses the free space of the data block only for updates to rows already contained in the data block.

Both parameters are used in a NON-ASSM tablespace and with an ASSM tablespace only PCTFREE is used.

Regards

Stefan

[Former Member · Accepted solution]

Hi,

You can view average row length in detailed ananlysis of the table from sap transaction db02. When you delete a milion records and reorg that table, it will release approximately AVG_ROW_LENGTH*Number of deleted records back to database.

Regards,

Abhishek Thota

[Former Member · Accepted solution]

Thankyou Stefan and Abhishek.