Overview
I am getting some questions about the hvr_sys_table:
a. Is table part of SAP schema or under hvr user schema of source system? or it can be part of any schema as long as hvr db user can read it?
b. What privileges does hvr db user should require on this table?
This table gets created during Initialize in the schema of the user logging in to the Oracle source at the source location.
If we want to create the table ourselves, so can you confirm for me which schema it should be placed in, and what privileges the database user needs when accessing it?
Answer
a) It should be in the schema for the HVR user. However, it should also be optional since we can work against a standby database. It could be in a different schema as long as there is a synonym for the HVR user to use the table.
b) The hvr db user should have create table and all the DML privileges on this table if its in a different schema. It's used to greatly improve performance during metadata queries by storing some temporary results and then joining further queries on this table. Note the DBA can also create the temporary table for the HVR user and not give out the create table privilege.