Migrate a Django project from MySQL to Oracle

Question

Hi,

I have a Django1.1 project that works with a legacy MySQL db. I'm trying to migrate this project to Oracle (xe and 11g). We have two options for the migration: - Use SQL developer to create a migration sql script. - Use Django fixtures.

The schema created with the sql script from sql developer doesn't match the schema created from syncdb. For example, Django expects TIMESTAMP columns while sql developer creates DATE columns.

Using syncdb with Django fixtures could be great but when trying to load the MySQL fixtures into Oracle, after using syncdb, I'm getting: IntegrityError: ORA-00001: unique constraint (USER.SYS_C004253) violated

How can I find what part create the integrity error?

update: Following an advice in the django mailing list I've tried:

1. dump everything while connected to MySQL: python manage.py dumpdata > fixture.json

2. Created a new Oracle user with an empty schema and switch the db connection to Oracle (didn't do syncdb)

3. loaddata to Oracle with: python manage.py loaddata fixture.json

I don't get an error when using loaddata but when running the server and navigating to localhost:8000 I'm getting: ORA-00942: table or view does not exist

Thanks

Answer 1

You can query the oracle information schema (called Oracle Datadictionary) to query the list of constraints on all tables to identify the table name and column for the said constraint.

select TABLE_NAME, COLUMN_NAME from user_cons_columns where CONSTRAINT_NAME = 'SYS_C004253'

Update

Due to security requirements the data dictionary is separated into distinct parts. User A cannot see tables and constraints from User B.

If you cannot find the information you can use the dba user-acount, who has global access and query the dba part of the data dictionary.

connect system/<the_secret_password>
select TABLE_NAME, COLUMN_NAME from dba_cons_columns 
where CONSTRAINT_NAME = 'SYS_C004253' and OWNER = 'USER'