Find table size and last access date

Database

Find table size and last access date

Hello,

I am still learning TD so I apologize if this is an 'easy' one. I need the following information so I can see if there are any old tables I can drop to reclaim space. I think I need to join DBC.Tables and DBC.TableSize but having issues doing so. I would like to see tables from multiple databases, but if I have to do it one-by-one then that is fine. Any help is appreciated!

DatabaseName

TableName

CreatorName

LastAccessTimeStamp

CurrentPerm(mb)

Thanks

8 REPLIES
Senior Apprentice

Re: Find table size and last access date

What do you mean by "issues"?

You should simply join on (DatabaseName, TableName).

Dieter

Enthusiast

Re: Find table size and last access date

Hi,

We have tablename & Databasename in both dbc.tables & dbc.tablesize. Please find the Code, that will give you the Tablename,Databasenaem,Creatorname,Lastaccesstimestamp & Currentperm.

sel b.databasename,a.tablename,a.CreatorName,a.CreateTimeStamp,a.LastAccessTimeStamp,sum(currentperm)/(1024*1024)  from dbc.tables a
inner join
dbc.tablesize b
on a.tablename=b.tablename
and a.databasename=b.databasename
group by 1,2,3,4,5
;

Thanks & Regards,

Adharssh.

Re: Find table size and last access date

Thanks for the replies, this helped.

Enthusiast

Re: Find table size and last access date

Hi Dieter/Adharssh,

I checked earlier too but all the  entries of lastaccesstimestamp in dbc.tables comes as null in my application..

Is there any way to activate it or is it automatic ?

Cheers!

Nishant 

Enthusiast

Re: Find table size and last access date

I think you need to activate ObjectUseCountCollectRate in DBS control utility...

Re: Find table size and last access date

Excellent - this is exactly what I was looking for.

- Joshua

Enthusiast

Re: Find table size and last access date

All,

ObjectUseCountCollectRate is enabled(>0), Usecount is also enabled, but still i could find LastAccesstimestamp= null for few tables in a DB.

I observed if a table is defined on a NOPI, then LastAccesstimestamp=NULL, Somebody correct me here please.

Teradata Employee

Re: Find table size and last access date

If an object existed prior to turning on usecount, and it has not been accessed since usecount was turned on, then the last access will still be null.