Home » SQL & PL/SQL » Client Tools » migration from windows-mssql to linux-oracle. (linux:2.6.18-308.13.1.el5,oracle:10.2.0.1 , windows mssql 10.2.0.1.)
migration from windows-mssql to linux-oracle. [message #572309] Mon, 10 December 2012 06:23 Go to next message
washide
Messages: 7
Registered: December 2012
Location: Transormator
Junior Member
Env:
destination-linux:2.6.18-308.13.1.el5,oracle:10.2.0.1 ,source:windows.mssql v:8.00760

I was try migrate one sample database from mssql "Pubs" using Oracle SQL Developer Tool.

After migraton i've got prefixes in tables like MD_<table_name> but i can not see tables from Pubs only a lot of tables like :
MD_COLUMNS
MD_GROUPS
etc...

How to solve it ?
--
ANTY GMO
Re: migration from windows-mssql to linux-oracle. [message #572311 is a reply to message #572309] Mon, 10 December 2012 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Aren't they in the database or are you just unable to see them (lack of privileges)?

Regards
Michel

[Edit: typo]

[Updated on: Mon, 10 December 2012 07:56]

Report message to a moderator

Re: migration from windows-mssql to linux-oracle. [message #572312 is a reply to message #572311] Mon, 10 December 2012 06:49 Go to previous messageGo to next message
washide
Messages: 7
Registered: December 2012
Location: Transormator
Junior Member
Hmm i have too check but i was migrating from user/schema like FOO so probably i have to see ... But i will check from sysdba
Re: migration from windows-mssql to linux-oracle. [message #572313 is a reply to message #572312] Mon, 10 December 2012 07:11 Go to previous messageGo to next message
washide
Messages: 7
Registered: December 2012
Location: Transormator
Junior Member
SQL> select owner,table_name FROM sys.all_tables WHERE owner like '%MIKA%';

OWNER TABLE_NAME
------------------------------ ------------------------------
MIKA MD_PROJECTS
MIKA MD_MIGR_PARAMETER
MIKA MD_INDEX_DETAILS
MIKA MD_OTHER_OBJECTS
MIKA MD_CONSTRAINTS
MIKA MD_USER_DEFINED_DATA_TYPES
MIKA MIGR_DATATYPE_TRANSFORM_MAP
MIKA MD_GROUPS
MIKA MD_ADDITIONAL_PROPERTIES
MIKA MD_REPOVERSIONS
MIKA MD_USERS

OWNER TABLE_NAME
------------------------------ ------------------------------
MIKA MD_GROUP_MEMBERS
MIKA MD_CATALOGS
MIKA MD_SEQUENCES
MIKA MD_COLUMNS
MIKA MD_PARTITIONS
MIKA MD_CONNECTIONS
MIKA MD_APPLICATIONS
MIKA MD_APPLICATIONFILES
MIKA MD_FILE_ARTIFACTS
MIKA MD_TABLESPACES
MIKA MD_USER_PRIVILEGES

OWNER TABLE_NAME
------------------------------ ------------------------------
MIKA MIGRLOG
MIKA MD_TABLES
MIKA MD_PACKAGES
MIKA MD_GROUP_PRIVILEGES
MIKA MD_VIEWS
MIKA MD_INDEXES
MIKA MIGR_DATATYPE_TRANSFORM_RULE
MIKA MD_SCHEMAS
MIKA MD_REGISTRY
MIKA MD_DERIVATIVES
MIKA MD_SYNONYMS

OWNER TABLE_NAME
------------------------------ ------------------------------
MIKA MD_TRIGGERS
MIKA MD_MIGR_DEPENDENCY
MIKA MD_PRIVILEGES
MIKA MIGR_GENERATION_ORDER
MIKA MD_MIGR_WEAKDEP
MIKA MD_CONSTRAINT_DETAILS
MIKA MD_STORED_PROGRAMS
MIKA MD_NUMROW$SOURCE
MIKA MD_NUMROW$TARGET
MIKA STAGE_MIGRLOG
MIKA MIGRATION_RESERVED_WORDS


So i don't have tables from Pubs.Above SqL statment was executed from sys as sysdba
Re: migration from windows-mssql to linux-oracle. [message #572315 is a reply to message #572313] Mon, 10 December 2012 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Do not use SYSDBA for this, use a DBA account, SYSTEM if you didn't create one (but you should)
2/ If they are not there then you did not create them.

Also Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.


Regards
Michel

[Updated on: Mon, 10 December 2012 07:56]

Report message to a moderator

Re: migration from windows-mssql to linux-oracle. [message #572637 is a reply to message #572315] Fri, 14 December 2012 05:32 Go to previous messageGo to next message
washide
Messages: 7
Registered: December 2012
Location: Transormator
Junior Member
Ad2.) I have prepared migration in this way :

- i created connection to mssql in Oracle SQL Developer Tool (autentication via OS user) on windows machine
- i created connection to oracle 10g(other server/machine ) in Oracle SQL Developer Tool (autentication via OS user)
- i highlight pubs and press export to oracle
- result is :i can't see in oracle on mika user any table from pubs and in DBO_PUBS user i cannot see table from mssql (take a look in picture like below)
DBO_PUBS was created in ORACLE during migration process.

SQL> select * from dba_sys_privs where grantee like '%MIKA%';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MIKA                           CREATE VIEW                              NO
MIKA                           CREATE USER                              YES
MIKA                           ALTER ANY SEQUENCE                       NO
MIKA                           UNLIMITED TABLESPACE                     YES
MIKA                           ALTER TABLESPACE                         NO
MIKA                           ALTER ANY TRIGGER                        YES
MIKA                           CREATE ANY TABLE                         YES
MIKA                           DROP ANY SEQUENCE                        NO
MIKA                           CREATE ROLE                              YES
MIKA                           CREATE TABLESPACE                        NO
MIKA                           ALTER ANY TABLE                          NO

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MIKA                           SELECT ANY TABLE                         NO
MIKA                           INSERT ANY TABLE                         NO
MIKA                           GRANT ANY ROLE                           NO
MIKA                           DROP ANY TRIGGER                         NO
MIKA                           DROP TABLESPACE                          NO
MIKA                           UPDATE ANY TABLE                         NO
MIKA                           CREATE ANY SEQUENCE                      NO
MIKA                           COMMENT ANY TABLE                        NO
MIKA                           DROP ANY ROLE                            NO
MIKA                           CREATE SESSION                           NO
MIKA                           DROP USER                                NO

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MIKA                           DROP ANY TABLE                           NO
MIKA                           ALTER ANY ROLE                           NO
MIKA                           CREATE ANY TRIGGER                       NO

./fa/10558/0/
Re: migration from windows-mssql to linux-oracle. [message #573050 is a reply to message #572637] Thu, 20 December 2012 04:20 Go to previous messageGo to next message
washide
Messages: 7
Registered: December 2012
Location: Transormator
Junior Member
grant dba to mika it solve my problem.

SELECT grantee, granted_role, default_role
FROM dba_role_privs
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY 1,2;  2    3    4  

GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---

MIKA                           DBA                            YES
MIKA                           EXP_FULL_DATABASE              YES
MIKA                           IMP_FULL_DATABASE              YES
     

[Updated on: Thu, 20 December 2012 04:21]

Report message to a moderator

Re: migration from windows-mssql to linux-oracle. [message #573051 is a reply to message #573050] Thu, 20 December 2012 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Everybody should be a DBA then there will be no more problem to see and access any object.

Regards
Michel
Re: migration from windows-mssql to linux-oracle. [message #573117 is a reply to message #573051] Fri, 21 December 2012 01:31 Go to previous messageGo to next message
washide
Messages: 7
Registered: December 2012
Location: Transormator
Junior Member
Michel,

Before i add DBA to mika i had try :

grant exp_full_database to mika;
grant imp_full_database to mika; 


but user mika couldn't see tables in DBO_PUBS .DBO_PUBS user in oracle space was created during migration process from account mika.

I haven't got any other idea so that was reason that i add DBA to mika.You know the privilages of mika so if you know have to help you will be nice welkom .

Best Regards.
Re: migration from windows-mssql to linux-oracle. [message #573122 is a reply to message #573117] Fri, 21 December 2012 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You randomly pick up the roles you grant to your users?

Regards
Michel
Re: migration from windows-mssql to linux-oracle. [message #573132 is a reply to message #573122] Fri, 21 December 2012 05:19 Go to previous message
washide
Messages: 7
Registered: December 2012
Location: Transormator
Junior Member
I think no , i grant roles acording to manual\help cener from Oracle SQL Developer Tool:



"For example, you can create a user called migrations with the minimum required privileges required to migrate a database by using the following commands:
CREATE USER migrations IDENTIFIED BY password
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PUBLIC SYNONYM TO
migrations WITH ADMIN OPTION;

GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE, ALTER ANY TABLE, ALTER TABLESPACE,
ALTER ANY TRIGGER, COMMENT ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY TABLE,
CREATE ANY TRIGGER, CREATE ROLE, CREATE TABLESPACE, CREATE USER, DROP ANY
SEQUENCE, DROP ANY TABLE, DROP ANY TRIGGER, DROP TABLESPACE, DROP USER, DROP ANY
ROLE, GRANT ANY ROLE, INSERT ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE TO
migrations;
"
On my site migrations user is mika.
Aditionally i add exp_full_database and imp_full_database.

--
Best Regards
washide
Previous Topic: How to insert data from Sql server to oracle server.
Next Topic: substr invalid number of parameters
Goto Forum:
  


Current Time: Sun Sep 21 15:21:07 CDT 2014

Total time taken to generate the page: 0.07168 seconds