How do I use AWS DMS mapping rules to migrate an Oracle schema to PostgreSQL in lowercase?
"I want to move my Oracle database schema to PostgreSQL, but the casing isn't compatible. What are my options?"
"I want to move my Oracle database schema to PostgreSQL, but the casing isn't compatible. What are my options?Short descriptionOracle stores metadata in its data dictionary in uppercase, and PostgreSQL stores it in lowercase. With either database, it's possible to override these default formats by encasing object names in quotes when you create them. However, this isn't a best practice, because doing so 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 the AWS Schema Conversion Tool (AWS SCT) to convert the Oracle schema to PostgreSQL.ResolutionThis example migrates the Oracle SCOTT schema to PostgreSQL. If you have an on-premises Oracle database, you can create this schema by running the following command:$ORACLE_HOME/rdbms/admin/utlsampl.sqlIf you have an Amazon Relational Database Service (Amazon RDS) DB instance that's running Oracle, 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 no tables in the Oracle database were created with lowercase letters in the table or column names. If the application is 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. For this reason, don't 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);Create endpoints and a replication instance:1.    Use the AWS DMS console or the AWS Command Line Interface (AWS CLI) to create the replication instance and then create the source and target endpoints.2.    Create a task. Be sure to choose Enable logging.3.    In the Table mappings view, select the JSON tab, and then choose Enable JSON editing. Then, use code similar to the following example to build the transformation rules for schema, tables, and column case handling. For more information, see Using table mapping to specify task settings.{  "rules": [    {      "rule-type": "selection",      "rule-id": "1",      "rule-name": "select-scott",      "object-locator": {        "schema-name": "SCOTT",        "table-name": "%"      },      "rule-action": "include"    },    {      "rule-type": "transformation",      "rule-id": "2",      "rule-name": "convert-schemas-to-lower",      "rule-action": "convert-lowercase",      "rule-target": "schema",      "object-locator": {        "schema-name": "%"      }    },    {      "rule-type": "transformation",      "rule-id": "3",      "rule-name": "convert-tables-to-lower",      "rule-action": "convert-lowercase",      "rule-target": "table",      "object-locator": {        "schema-name": "%",        "table-name": "%"      }    },    {      "rule-type": "transformation",      "rule-id": "4",      "rule-name": "convert-columns-to-lowercase",      "rule-action": "convert-lowercase",      "rule-target": "column",      "object-locator": {        "schema-name": "%",        "table-name": "%",        "column-name": "%"      }    }  ]}4.    Choose Create task.Note: You can also use a rename schema rule instead of converting the schema to lowercase.After the task is created, verify that all the objects are created in lowercase in PostgreSQL:postgres> select table_name from information_schema.tables where table_schema='scott'; table_name------------ bonus dept salgrade emp(4 rows)postgres> select table_name, column_name from information_schema.columns  where table_schema='scott' order by table_name; table_name | column_name------------+------------- bonus      | ename bonus      | job bonus      | sal bonus      | comm dept       | deptno dept       | dname dept       | loc emp        | deptno emp        | hiredate emp        | sal emp        | comm emp        | empno emp        | ename emp        | job emp        | mgr salgrade   | losal salgrade   | hisal salgrade   | grade(18 rows)Related informationAWS Database Migration Service step-by-step walkthroughsMigrate an on-premises Oracle database to Amazon RDS for PostgreSQL using an Oracle bystander and AWS DMSFollow"