OraFAQ Forum: Client Tools » SQL Server to Oracle Migration

Home » Other » Client Tools » SQL Server to Oracle Migration (SQL server 2008 and Oracle 12C, Oracle SQL Developer 4.1.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Server to Oracle Migration [message #648016]

Mon, 15 February 2016 00:58

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
I involved in a migration project which will migratre sql server to oracle 12c.
I use oracle sql developer tool 4.1.3 with jTDS 1.3.1 driver installed for third party migration.
I have followed the steps provided in the sql developer tool for migration and the migration was successful.
But I'm not able to see the migrated table in my oracle user instead I'm able to see the system tables of SQL server which got
migrtated with MD_.

The following is the loginformation I got it from MIGRLOG table after migration.
Kindly let me know what is missing here and how to proceed further.

614751 15-FEB-16 11.23.11.954000000 AM Convert completed in 9 seconds
614701 15-FEB-16 11.22.31.040000000 AM Catalog DEMO_Mig, Schema dbo coalesced to single schema DEMO_Mig

Report message to a moderator

Re: SQL Server to Oracle Migration [message #648031 is a reply to message #648016]

Mon, 15 February 2016 04:13

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Post output from:

sho user

select owner, count(*) from dba_tables where upper(owner) = 'DEMO_MIG';

Report message to a moderator

Re: SQL Server to Oracle Migration [message #648035 is a reply to message #648031]

Mon, 15 February 2016 05:16

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,

Please find the output below. The migrated user shows only the system tabels that are migrated and not the application table.
And I'm not able to run this query on dba_tables. As I don't have the privilege to select the dba_tables. So I selected it from all_tables. For migration from SQL server to Oracle is it necessary that I should have the oradba with ADM privilege?
Is that preventing to create tables in the migration user?

Kindly provide some solution.

USER is "DEMO_MIG"
OWNER
--------------------------------------------------------------------------------
COUNT(*)
----------
DEMO_MIG
44

Report message to a moderator

Re: SQL Server to Oracle Migration [message #648042 is a reply to message #648035]

Mon, 15 February 2016 07:01

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

That means you're logged on as DEMO_MIG, so you have to be able to see the tables.

Or do you mean the tables are there but have no data?

Report message to a moderator

Re: SQL Server to Oracle Migration [message #648062 is a reply to message #648042]

Mon, 15 February 2016 23:02

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
The table count shows the system tables that were migrated from SQL Server to Oracle.
The actual application tables were not available in the Oracle user.
When I see in the migration repository, I can see the application tables, views, procedures on the prescribed user.
But when I log in to the prescribed oracle user, I can see only the system tables and not the application tables.

Kindly show some light on this.

Thanks.

Regards,
Anand.T

Report message to a moderator

I don't see any tables [message #648063 is a reply to message #648016]

Mon, 15 February 2016 23:12

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
I am doing MSSQL server to Oracle migration in SQL Developer 4.1.3 and MS SQL Server 2008 with Oracle 12c.
Having a connection with ORADBA privileges and created a migration user and granted all the privileges.
After migration, I received migration have been completed successfully.
In the migration repository too, I can see the tables under the user.

I followed the following steps.

1. Repository - Repository name is given
2. Project - project name provided
3. Source Database - source database as SQL Server DB chosen
4. Capture -- Selected the particular db under SQL Server DB to be migrated
5. Convert --
6. Translate - Selected all the ALL_CONSTRAINTS, ALL_FUNCTIONS,ALL_PROCEDURES,ALL_TRIGGERS, ALL_VIEWS
7. Target Database --- Target database as Oracle Database chosen
8. Move Data.

In the migration log file also I got the following message.

Catalog DEMO_mig, Schema dbo coalesced to single schema DEMO_mig
Convert completed in 9 seconds.

But when I checked in the (DEMO_mig)oracle user I don't see any tables listed under the user except the SQL Server system tables which starts with MD_.

Did I missed anything in the migration?

Kindly provide me a solution.

Thanks.
Regards,
Anand.T

[EDITED by LF: this message was moved here from - in my opinion - unrelated topic: Error to Associate Migration Repository; as if the author missed the topic he opened and posted a message in another one]

[Updated on: Tue, 16 February 2016 14:06] by Moderator

Report message to a moderator

Re: SQL Server to Oracle Migration [message #648080 is a reply to message #648062]

Tue, 16 February 2016 02:32

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

It sounds like your import only partially completed.

What do your logs say?

Have you tried reimporting?

Report message to a moderator

Re: SQL Server to Oracle Migration [message #648084 is a reply to message #648080]

Tue, 16 February 2016 04:09

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

My MIGRLOG table shows the below result. I logged into DEMO_Mig user and checked for the tables.
But I'm not getting the actual application tables.

614751 15-FEB-16 11.23.11.954000000 AM Convert completed in 9 seconds
614701 15-FEB-16 11.22.31.040000000 AM Catalog DEMO_Mig, Schema dbo coalesced to single schema DEMO_Mig

Is it could be any privilege issue that prevents me to view the tables,views got migrated from MS SQL Server to Oracle.

Kindly let me know.

Regards,
Anand.T

Report message to a moderator

Re: SQL Server to Oracle Migration [message #648085 is a reply to message #648084]

Tue, 16 February 2016 04:18

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

You said the tables aren't there though.

The oracle schema owner will be able to see its own tables.

If you have some but not all, it's sounding like a migration problem rather than a database problem.

Report message to a moderator

Re: SQL Server to Oracle Migration [message #648093 is a reply to message #648085]

Tue, 16 February 2016 06:57

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
Attached is the steps I took during the migration. I have captured all the steps until checking the oracle user for tables that were migrated from MS SQL Server. Kindly go through it and provide a solution.
Also let me know where I'm missing.

Thanks.

REgards,
Anand.T

Attachment: Migration Steps.pdf
(Size: 988.97KB, Downloaded 2697 times)

Report message to a moderator

Re: SQL Server to Oracle Migration [message #648094 is a reply to message #648093]

Tue, 16 February 2016 07:28

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

I'm honestly not sure. your OP sounded like they were there but you didnt have permissions to see them; I don't know why only some of the stuff you expect isnt there.

Report message to a moderator

Re: SQL Server to Oracle Migration [message #648097 is a reply to message #648062]

Tue, 16 February 2016 08:39

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

pstanand wrote on Mon, 15 February 2016 23:02

Hi,
The table count shows the system tables that were migrated from SQL Server to Oracle.
The actual application tables were not available in the Oracle user.

In your understanding, what is the difference between a "system" table and an "application" table?

Report message to a moderator

Re: SQL Server to Oracle Migration [message #648134 is a reply to message #648097]

Wed, 17 February 2016 03:29

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
System tables are the tables that gets created during migration on background process. Application tables are the actual tables that contains data.

Thanks

Regards,
Anand.T

Report message to a moderator

Previous Topic:	Error to Associate Migration Repository
Next Topic:	ORA-44412: XE edition memory parameter invalid or not specified

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 08 01:16:41 CDT 2025