Local DBF Record Count and Size

Sharing few SQL’s that can be used to get the Record count and Tablewise Memory size usage Report.

Very handy in case u r planning to reduce the Local DBF size.

==========================================================

SELECT

T.NAME, T.TYPE,TABLE_ID, CREATOR,TABLE_NAME,TABLE_TYPE,COUNT,FIRST_PAGE,LAST_PAGE,PRIMARY_ROOT,FIRST_EXT_PAGE,

LAST_EXT_PAGE, TABLE_PAGE_COUNT, EXT_PAGE_COUNT,PAGE_MAP_START

FROM

SYS.SYSTABLE S,

SIEBEL.S_TABLE T

WHERE

TABLE_TYPE=’BASE’ AND

CREATOR=101 AND

S.TABLE_NAME = T.NAME AND

T.TYPE NOT IN (‘Interface’, ‘Repository’, ‘Virtual Table’, ‘Warehouse’)

ORDER BY

COUNT DESC,

TABLE_NAME ASC;

OUTPUT TO

E:\temp\s_tables_count_After.txt;

====================================================================================================================

SELECT

S.TABLE_NAME AS TABLE_NAME,

CAST(S.COUNT AS DOUBLE) AS COUNT,

(TABLE_PAGE_COUNT + EXT_PAGE_COUNT)*(SELECT DB_PROPERTY ( ‘PageSize’ ))/(1024*1024) as TABLESIZE,

IF COUNT > 0 THEN ((TABLE_PAGE_COUNT + EXT_PAGE_COUNT) * (SELECT DB_PROPERTY ( ‘PageSize’ )))/COUNT ELSE NULL ENDIF AS BYTESPERROW

FROM

SYS.SYSTABLE S,

SIEBEL.S_TABLE T

WHERE

TABLE_TYPE=’BASE’ AND

CREATOR=101 AND

S.TABLE_NAME = T.NAME AND

T.TYPE NOT IN (‘Interface’, ‘Repository’, ‘Virtual Table’, ‘Warehouse’)

ORDER BY

COUNT DESC,

TABLE_NAME ASC;

OUTPUT TO

E:\temp\s_tables_size_After.txt;

==============================================================

Will post more on Docking rules….Stay tuned.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

Up ↑

%d bloggers like this: