How to get the last access time of any user created Schema?

[subhrsaha]

Hi,

Can anyone help me with any SQL query to find out the last access time of any user created schema? or is there any process to check when a specific schema is last used?

I am an SAP Basis consultant, there is a requirement to remove unused schemas to free up the space of the DB.

Thanks,

Subhradeep

[Abhishek_Hazra]

Hi,
You can check in M_SQL_PLAN_CACHE table. You might find the below script useful.

select "SCHEMA_NAME", TO_DATE(max("LAST_EXECUTION_TIMESTAMP")) from M_SQL_PLAN_CACHE
group by "SCHEMA_NAME" order by "SCHEMA_NAME";

Best Regards,
Abhi

[subhrsaha]

Hi Abhishek,

Appreciate your response.

i have tried the above mentioned SQL query but only few schema's last used time stamps are showing(not all of them) as you can see in the below picture.

[Abhishek_Hazra]

Hi Subhradeep,
The limitation is due to the retention based on system's configuration parameters, which holds the data based on a certain period/threshold. The last option I can think of would be to activate traces specific to the objects/schema/users you want to have the tests on.

Best Regards,
Abhi