topic Re: Table size in Application Development and Automation Discussions

Table size

Former Member — Tue, 14 Apr 2009 08:56:12 GMT

Is there a table which stores the size attribute of other tables? I'm looking to query with a list of table names and get their total size in bytes.

Re: Table size

former_member404244 — Tue, 14 Apr 2009 09:03:37 GMT

HI,

Check the table DD09L.

Regards,

Nagaraj

Re: Table size

Former Member — Tue, 14 Apr 2009 09:11:57 GMT

Refer table DD09L for table size details of other tables.

Regards,

Joan

Re: Table size

Former Member — Tue, 14 Apr 2009 09:13:57 GMT

Hi Chris

DBSTATTORA is the table to store Actual size of tables on the database.

Ram.

Re: Table size

Former Member — Tue, 14 Apr 2009 09:14:53 GMT

Forgive me if I'm wrong but does this not just show you the size category? Is there nothing more precise?

Re: Table size

former_member404244 — Tue, 14 Apr 2009 09:16:59 GMT

Hi,

TABKAT is the field for size category....in DD09L table.

Regards,

Nagaraj

Re: Table size

Former Member — Tue, 14 Apr 2009 09:17:12 GMT

DBSTATTORA does not appear to be populated on our system.

Re: Table size

former_member222860 — Tue, 14 Apr 2009 09:24:23 GMT

Can get it from DBA_SEGMENTS table at the Database level.

Check this:

DATA: BEGIN OF SIZE OCCURS 0,
          KB LIKE SEGMENTS_S-KBYTES,
          BL LIKE SEGMENTS_S-BLOCKS,
        END OF SIZE.

EXEC SQL.
    SELECT SUM(BYTES)/1024, SUM(BLOCKS) FROM DBA_SEGMENTS
                          WHERE SEGMENT_NAME = 'MARA'
                           INTO :SIZE
ENDEXEC.

LOOP AT SIZE.
  WRITE:/ SIZE-KB, SIZE-BL.
ENDLOOP.

Mahesh

Re: Table size

Former Member — Tue, 14 Apr 2009 09:30:35 GMT

Hi Mahesh

This appears to be the kind of solution I'm looking for, however your code short dumps for me for some reason. We are using MSSQL.

Re: Table size

former_member222860 — Tue, 14 Apr 2009 09:44:33 GMT

Chris,

I've Oracle database at the back-end , and this query is working well.

thanks\

Mahesh

Re: Table size

Former Member — Tue, 14 Apr 2009 11:13:07 GMT

Upon further investigation I've discovered the MSSQL stored procedure sp_spaceused, however I'm unsure as to how I would execute this in an abap program. I've read up on executing procedures in native SQL, ie:

EXEC SQL
   EXECUTE PROCEDURE proc1 ( IN :x, OUT :y )
ENDEXEC.

But i'm still unsure of the exact syntax to use for the sp_spacedused procedure in order to print the size out in an abap program: http://msdn.microsoft.com/en-us/library/ms188776.aspx

Re: Table size

Former Member — Wed, 01 Jul 2009 15:24:54 GMT

Hi Rammohan,

what does field OCCBL (Used blocks of a table in KB) in table DBSTATTORA means. Is this the complete size of the table in KB?

Regards

Tobias

Re: Table size

Former Member — Thu, 02 Jul 2009 05:50:45 GMT

Hi,

You can use the function module

"DB_GET_TABLE_SIZE"

to get the size fo each table.

This FM will give you the size of the table in KBs. (kilo bytes)

Hope this will help you.

Regards,

Smart Varghese

Re: Table size

Former Member — Thu, 02 Jul 2009 06:42:06 GMT

Hello Smartsvarghese,

thats what I`ve been looking for. But could you explain the Import Parameters of the FM, especially "LIMIT_KB". Is it necessary to fill all parameters?

Regards,

Tobias

Re: Table size

Former Member — Thu, 02 Jul 2009 09:32:01 GMT

Hi Tobias,

Check this.

CALL FUNCTION 'DB_GET_TABLE_SIZE'

EXPORTING

tabname = MARA or AFVC or RESB ( as per your requirement )

LIMIT_KB = 16384 ( not mandatory )
PRID = 0 ( not mandatory )
SELECT_COUNT = ' ' ( not mandatory )

IMPORTING

SIZE_KB = v_size ( this one type I )

EXCEPTIONS

WRONG_SCHEMA = 1

NO_OF_POOLS_EXCEEDED = 2

OP_FAILURE = 3

OTHERS = 4

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Hope this will help you.

Regards,

Smart Varghese

Re: Table size

Former Member — Fri, 03 Jul 2009 07:46:53 GMT

Hello Smartsvarghese,

this sounds good, but when I am executing the function module without maintaining the attribute LIMIT-KB I get no values.

I have entered the following parameters:

TABNAME: TCURR

LIMIT_KB: (no entry)

PRID: (no entry)

SELECT_COUNT: (no entry)

My result is:

SIZE_KB: 1

REAL_SIZE: (no entry)

ROWCOUNT: -1

SUBRC: 1

If the field LIMIT_KB is filled with the value of 50.000, I get the following result:

SIZE_KB: 35.895

REAL_SIZE: X

ROWCOUNT: 706.861

SUBRC: 0

First, what went wrong in the first case, if the field LIMIT_KB is not mandatory? And second, do I understand the result of case two correct: The table TCURR has a real size of 35.895 KB and contains 706.861 data records?

Regards,

Tobias