Table creation and deletion date, user ID in HANA

[Former Member]

Hi Experts,

I have a small question, probably, it might be a very simple system view, but have sifted through a lot of different guides, I just cannot seem to find a table/view that will tell me the information about table creation in HANA and also if it was deleted when it was deleted and by which user ID?

The purpose for asking this question is that it seems one of the crucial tables in our environment got deleted and possibly re-created and we are trying to figure out when and how that occured.

All help appreciated.

Thanks.

MD

[justin_molenaur2]

As Lucas mentioned, that is the most accurate way to achieve what you are looking for.

However, if you know that the table deletion/modifidation was recent, you can run a query on the sql plan cache that might help you.

SELECT * FROM "M_SQL_PLAN_CACHE"

WHERE "STATEMENT_STRING" LIKE '%<TABLE_NAME>%'

This would give you any DDL/DML performed on this table.

Regards,

Justin

[lucas_oliveira]

Good call Justin!

Adding up to what Justin mentioned you could have that information in the historic data contained in table _SYS_STATISTICS.HOST_SQL_PLAN_CACHE. Exact same query Justin mentioned but with the table above.

BRs,

Lucas de Oliveira

[justin_molenaur2]

I learned something here ... what is the difference between these two tables? I know SQL plan cache gets cleared out periodically, is the same true for the table you mention?

I checked MIN(LAST_EXECUTION_TIMESTAMP) column and I am getting the same results, so in the system I am looking at it does not seem that there is any difference in content?

Regards,

Justin

[Former Member]

Thank you both my replying to my query. We do have auditing turned on and when I look into the table AUDIT_LOG all I see is information on the connection by a given user.

I also looked at both the tables mentioned above, but in these tables I do not see any DDL statements of the like of create or drop. I see a number of UPSERT and UPDATE statements.

Seems HANA probably does not keep creation information that can be accessed so easily... .

Thanks.

BR,

MD

[lucas_oliveira]

Hi Justin,

This stats table will be fed with snapshot collection/update in an hourly frequency (for this table specifically). So you'll have up to 30 days worth of data. I think this has been pushed to 42 days in sp09. Either way, you have historic info there... for 30-42 days at most.

BRs,

Lucas de Oliveira

[lucas_oliveira]

Hello Manish,

The auditing information depends on your auditing policy. In other words: if you're not auditing DROP / DELETE statements then you won't see those logged on your audit logs. That's probably why this is not on your audit log. Chances someone did what was done and cleared the audit log maybe?

And yes, you're right. DDL won't show up in SQL Plan Cache while DML will. So both M_SQL_PLAN_CACHE and _SYS_STATISTICS.HOST_SQL_PLAN_CACHE wont have that info.

BRs,

Lucas de Oliveira

[lucas_oliveira]

Hi ,

I forgot to answer the other question: yes, it is ok not to have a difference on the min(last_execution_timestamp) of m_sql_plan_cache and host_sql_plan_cache.

I'd guess you tested that on a fairly new system and there isn't much going on there in terms of sql plan cache usage and that's probably reflecting on the historic data. Once cache evictions start happening then you should start seeing a difference between those.

Here's what it looks like on one of the systems I have access to:

BRs,

Lucas de Oliveira

[lucas_oliveira]

Hi,

Information such as who/tried to do/did what/when can be achieved with auditing. If that's not enabled and configured you won't get that information from the database as far as I can tell.

There are lots of material here in the community in that regard:

http://scn.sap.com/docs/DOC-51098

And, of course, in the official SAP HANA documentation:

Auditing Activity in SAP HANA Systems - SAP HANA Administration Guide - SAP Library

BRs,

Lucas de Oliveira