Table size

[Former Member]

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.

[former_member404244]

HI,

Check the table DD09L.

Regards,

Nagaraj

[Former Member]

Refer table DD09L for table size details of other tables.

Regards,

Joan

[Former Member]

Hi Chris

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

Ram.

[Former Member]

DBSTATTORA does not appear to be populated on our system.

[Former Member]

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

[former_member404244]

Hi,

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

Regards,

Nagaraj

[former_member222860]

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

[Former Member]

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.

[former_member222860]

Chris,

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

thanks\

Mahesh

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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