Skip to Content
avatar image
Former Member

Tablesize ??

Hello Everybody

We build a testdatabase on a testserver with "restore with initialization" from our produktion database.(maxdb 7.6.00.34 no sap)

Everything works as expected.

When connecting to the testdatabase with sqlstudio, i found a view "tablesize" under tables/sysinfo

Columns: schemaname,tablename,usedsize,rowcount

We want use the rowcount column to avoid a "select count(*) from xyz" when we need information about the amount of records in a table.

This view is missing in our produktion database ???? Or to stupid to find it,-)

Do i have to create this view ???

If yes,how to do it? Doubleclick on the view of the testdatabase tells me

"Can not read view definition"

Any help welcomed

Best regards

Albert

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 20, 2008 at 12:14 PM

    Hi Albert,

    the mentioned system view TABLESIZE is owned by the schema SYSINFO; you should be able to see it when logged on as your SYSDBA user. The view is created by:

    CREATE VIEW "SYSINFO"."TABLESIZE"
     (
    	"SCHEMANAME", 
    	"TABLENAME", 
    	"USEDSIZE", 
    	"ROWCOUNT"
    )
    AS SELECT DOMAIN.TABLES.SCHEMANAME, DOMAIN.TABLES.TABLENAME, 
    FILES.TREELEAVESSIZE + FILES.TREEINDEXSIZE + FILES.LOBSIZE USEDSIZE, 
    FILES.ENTRYCOUNT ROWCOUNT 
    FROM DOMAIN.TABLES, SYSINFO.FILES 
    WHERE DOMAIN.TABLES.TABLEID = SYSINFO.FILES.FILEID AND 
    SYSINFO.FILES.TYPE = 'TABLE' INTERNAL   
    

    You should be able to retrieve information like this from the Filedirectory (i.e. (SYSINFO.)FILES) joined with TABLES aswell.

    Regards,

    Roland

    Add comment
    10|10000 characters needed characters exceeded

    • Hello Albert,

      the dbmserver stores the dbm user and the sysdba user in the upc file. This is done during first authorization with the correct user (dbm) and during load_systab (sysdba). This stored user information is used to make some administrative tasks easier - when the user is already stored you don't have to specify it again during load_systab.

      However, when it was not yet stored or when the upc file gets lost/corrupted, you have to store the entries again.

      (For further information please check https://wiki.sdn.sap.com/wiki/x/UC8.)

      So on your system it seems that the sysdba user was not stored in the upc file - therefore DBMGUI asked for the user information.

      When the database was upgraded from an older version, which did not have the file directory counters, these counters are created in background - when there is no other activity on the system. When there is a lot of workload on the system, the counters get not updated. Tables created with the new version automatically get the counters from the beginning.

      How many entries do you have in table sysupdatecounterwanted?

      Please check, if the file directory counters exist after you executed the "CHECK TABLE <TableName> WITH SHARE LOCK" for these tables.

      Best regards,

      Melanie