0

Last Modified dates for MV/Tables in a schema

Hi,

How do I retrieve the creation date, created by, modified by, modified date details for all the objects (such as tables/MVs/Alias/Incorta tables) with in a physical schema?

Thanks,

Srinivas Chava

7replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Incorta schema table is updated at the schema level, not at the table level.  We can get the OWNERID, CREATIONDATE, MODIFICATIONDATE, MODIFIEDID for a schema from the IncortaMetadata table SCHEMA.

    Incorta provides the IncortaMetadata Dashboards, the dashboard 0. Environment/Tenant Overview shows a list of physical schema and the Last Modified Time and Last Modified By:

    Like
      • Srinivas C
      • FBP
      • Srinivas_C
      • 1 mth ago
      • Reported - view

      Dylan Wan Thanks for the update. Is there any other alternative to bring in the last modified date from the audit file for the individual tables as we need it for SOX activity. And dashboard listed above is showing the last modified as the last schema load date and time, which is incorrect right?

      Like
    • Srinivas C , The Incorta metadata ACTION tables shows all changes occurred to a schema.  This table was for support SOX compliance reporting requirements.  You are right in that Incorta also modify the schema when the data is load just is ended with the rows, last refresh time, etc, to the SCHEMA table and it does not reflect the real manual change to the schema. 

      You can check the Incorta Metadata dashboard with the name 7. User Action.  Filter it by Action in "EDIT" and Object Type IN "SCHEMADEFINITION"

      Like
      • Srinivas C
      • FBP
      • Srinivas_C
      • 1 mth ago
      • Reported - view

      Dylan Wan Thanks for the update. My business case is that we have 30 MV's under single physical schema exclusive for SOX and we would like to know the individual last modified dates for each MV as required per SOX Compliance. Because above Action metadata dashboard tells me that this schema is modified but it does not provide details of which MV is modified and it would be a big gap to be filled per SOX requirements. So, is there any option of turning on the in-detail auditing which logs the changes at table level as well?

      Like
    • One of the best practices for Incorta development env is to schedule the backup of the tenant regularly.  A comparison script is available and we can compare two tenant backup files or two exported objects and see the differences.  If differences found, we view the details to see which MVs were changed between two days, if we have the daily backup.

      It is the closest I can think of for now.  Combining the schema level change log available from the ACTION table and the  comparison report on the the daily backup, we can show the changes at the detail level.  There is no option currently to show the table or MV level auditing for every changes during the day, but starting with Incorta release 5, the versioning feature will be available and the versions can be created at the schema level when a change is made for restoring.  

      Like
      • Srinivas C
      • FBP
      • Srinivas_C
      • 1 mth ago
      • Reported - view

      Dylan Wan We are currently doing the tenant backup daily. Can you please help me with the above approach on utilizing the ACTION table and comparison script to provide the changes at the detail level? Thanks for all the help!!!

      Like
  • Dylan Wan  Kindly share with us the script which will help to compare two tenant backup. 

    Like
Like Follow
  • 1 mth agoLast active
  • 7Replies
  • 37Views
  • 3 Following

Product Announcement

Incorta 5 is now Generally Available