Oracle SSMA : Problems with hidden columns (DDL optimization)

Question

Hi,
Under Oracle (v12), i add a new column :
ALTER TABLE TABLE_TEST ADD (TST_FLG_PMO NUMBER(1) DEFAULT 0);

When I use SSMA, the script SQL show :
CREATE TABLE SCHEMA.TABLE_TEST
(
...
TST_FLG_PMO NUMBER(1, 0) DEFAULT 0,
SYS_NC00109$ RAW(126)
);
To make the migration I use the functionality "data migration" without "Convert schema" because I have already the schema.
During the migration, I have an error on this table because the definitions are differents.

Have you a solution ?

Thank

Answer 1

Hello,

I'm not really sure what is the exact issue, so let me try to provide the information on both.

1. The title says "Problem with hidden columns" and in the text I see you mention SYS_NC00109$. These columns are auto-created in Oracle to support other features, like function-based indexes, archiving, etc. In some cases SSMA will handle them just fine, for example in case of the function-based index it will be marked as virtual (aka computed) and it will be ignored during migration (implemented in SSMA from v8.17). In some cases it may not be marked as virtual and SSMA does not yet support that pattern, so data migration might fail. You can drop the column in the SQL Server and use custom SELECT query for data migration to ensure SSMA does not try to move the column. In order to do that, you need to enable "Extended data migration options" under Tools -> Project Settings -> General -> Migration, then select a table in question and navigate to new "Data Migration Settings" tab and check Use custom select checkbox. On a side note, can you please provide more details as to what is your scenario where SYS_NC columns were created and what is the definition of the column from sys.all_tab_cols?
2. In the question body you mention that you have added a new column, but did not run "Convert schema" after doing so. This is not really a scenario that we support out of the box, as SSMA can adjust the name of the columns, if they use characters not supported in SQL Server, etc. What is the expected behavior? SSMA just ignores new column? You should be able to use custom SELECT query, as described in (1) to ignore the new column.

Hope this helps,
Alex.

Answer 2

Hi Alex,
Sorry for my silence ...
I used Oracle v12, Sql Server v19 and SSMA v8.21

You will find below informations that I can give :

Response 1
When I play the query before the update,

select * from user_tab_cols where column_name LIKE 'SYS_NC00%';  

I am not viewing a row relating to the table in question.

The table has 108 fields and I add a new field with the following syntax:

ALTER TABLE LM_LIGLIQ ADD (LLI_FLG_MAJ_ENG NUMBER (1) DEFAULT 0);  

The select on "user_tab_cols" then gives the following result (visualization by Oracle Sql Developer):

I tried your solution by removing in the "Data migration" tab, the following information (yellow only, or yellow and green) :

but it does not work. I got the error "The given key was missing from the dictionary (in french for me)"

Info: When this new field is neither present in Oracle nor Sql Server, the data is successfully migrated.

Response 2 :
For your second question, update scripts are of course played on each DBMS. The new field therefore exists for each DBMS. That’s why I don’t play "Convert schema".

I hope my explanations are clear and thank you for your comments.

Answer 3

Hi Alex,

I am testing SSMA and I know that I am not an expert of this tool 😉
To detail the working context, we have applications that work either with Oracle or with Sql Server. The schemas exist since a long time and over time evolve (adding tables and fields mainly). It is the developers who update the schemas.
For my tests, therefore, I inherit existing schemas, which have been previously updated and which normally are equivalent.
Of course, I reload the Oracle and Sql Server schemas before any further attempt.

I have tested your first example. In this case, I don't have any additional column. For me it's normal because it's a creation and not an update.

otherwise, I have tested this two scripts (under Oracle) :

1. CREATE TABLE LM_TESTTABLE2 (
   PMO_ID NUMBER (10,0) NOT NULL
   );
2. ALTER TABLE LM_TESTTABLE2 ADD (PMO_FLG NUMBER(1) DEFAULT 0);

The first script is ok. After the second script, I see one row in user_tab_cols :

I looked another schema (of another client) and I have the same comportement (Oracle v12)

Thank you for your help.
Best regards.
Philippe

Answer 4

Hi Alex,
Have you new informations of this topic ?
Best regards.
Philippe