We are planning to have 7 different schemas (region-wise) with same code base.
Oracle DB Version: 11.2.0.4.0 (2 Node RAC)
Are there any dis-advantages of having same object name in multiple schema?
E.g. STORE_MASTER table in 7 different schemas viz GSM_LATAM, GSM_GI, GSM_GC, etc.
Note - The table definition can be different in different schemas. STORE_MASTER table definition in GSM_LATAM can be different than the same in GSM_GI.
The idea is to distribute the tables/objects per region into multiple schemas, instead of keeping all the data in single schema.
The data volume is huge and even partitioned tables can lead to some concurrency problems.
The various high level steps can be: ETL, Comparison, Versioning and Archiving
1) Data extraction from source system as a file
2) Loading the data from file into database for processing – Landing tables
3) Staging tables for pre-processing, validation and De-duplication – Staging tables
4) Loading the transformed data into repository, maintaining golden copy – Repo Tables
5) Maintaining version history – Version Tables
6) Archiving - External Tables