How do I use AWS DMS mapping rules to migrate an Oracle schema to PostgreSQL in lowercase?
4 minute read
I want to move my Oracle database schema to PostgreSQL, but the casing isn't compatible.
Oracle stores metadata in its data dictionary in uppercase, and PostgreSQL stores it in lowercase. With either database, it's possible to encase object names in quotes when you create them to override these default formats. However, this isn't a best practice and can cause unexpected behavior. Because the AWS Database Migration Service (AWS DMS) can't autocorrect metadata storage formatting, AWS DMS creates the objects in quotes. As a workaround, you can override AWS DMS quotes with mapping rules. For more information, see Use AWS SCT to convert the Oracle schema to PostgreSQL.
The example in this resolution migrates the Oracle SCOTT schema to PostgreSQL. If you have an on-premises Oracle database, then run this command to create this schema:
If you have an Amazon Relational Database Service (Amazon RDS) DB instance that's running Oracle, then search online for a similar script.
This script creates four tables under the SCOTT schema: EMP, DEPT, BONUS, and SALGRADE. Collectively, they contain a small number of rows.
Verify that there are no tables in the Oracle database with lowercase letters in the table or column names. Some applications are accustomed to mixed-case table and column names. It's a best practice to allow AWS DMS to replicate the tables exactly as it finds them and not to use these mapping rules.
oracle> SELECT table_name from dba_tables where owner='SCOTT' and table_name <> upper(table_name);
oracle> SELECT table_name, column_name from dba_tab_columns where owner='SCOTT' and column_name <> upper(column_name);
3. In the Table mappings view, select the JSON tab, and then choose Enable JSON editing. After that, use code similar to this example to build the transformation rules for schema, tables, and column case handling. For more information, see Using table mapping to specify task settings.