10 Replies Latest reply on Apr 8, 2015 2:51 PM by Mkirtley-Oracle

    SQLServer Migration

    Dan.A

      Doing online migration from MS-SQLServer all works fine, except when the new target oracle user gets created it has 0 quota.

       

      Migration task appears to hang there until manually grant some quota on its default tablespace. Then the task continues and eventually succeeds.

       

      I am using SQLDeveloper 4.0.0.13 Build MAIN-13.80

       

      Maybe this is already addressed in a later version?

        • 1. Re: SQLServer Migration
          Mkirtley-Oracle

          Hi,

            what is the create statement that is generated for the user ?
          On my system, it is in the format -

           

          PROMPT Create user test

          CREATE USER test identified by &&test_password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

          GRANT CREATE SESSION, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SYNONYM TO test;

           

          Do you have any rules or settings on your system that causes the default quota to be zero on tablespaces ?

           

          Regards,

          Mike

          • 2. Re: SQLServer Migration
            Dan.A

            Hi,

            Not sure how to get the create user statement... ?

            I am using online migration as recommended and the master,sql script gets deleted .

             

            The target database is a 12c PDB. I am quite new to it... it could have some default profile/rules as you mention...

             

            But even in your statement, there is no quota granting part...


            Maybe default profile or the resource role is different between 11.2 and 12.1 databases.

             

            Thanks,

            Dan

            • 3. Re: SQLServer Migration
              Mkirtley-Oracle

              Dan,

                You can go through the steps and generate the master.sql script without actually running it but I'll check if there is a difference between 12.1 and 11.2 for the quota grants. That could be the cause.

               

              Regards,

              Mike

              • 4. Re: SQLServer Migration
                Mkirtley-Oracle

                Dan,

                  Yes, for 12c by default, a user has no quota on any tablespace in the database.  Looks like Development need to follow up on this.  I'll check further.

                 

                Regards,

                Mike

                1 person found this helpful
                • 5. Re: SQLServer Migration
                  Dan.A

                  Mike,

                   

                  Based on my best recollection i would say that is how it happens in 11.2 as well. I will check again

                   

                  Trouble with master.sql generation: the product is too smart: it looks for the existence of target objects and skips the create part if they exist.

                  I need to create a new migration for a new SQL Server database... not sure if they gave me access to any other than what i was supposed to migrate...

                  Thx,

                  Dan

                  • 6. Re: SQLServer Migration
                    Mkirtley-Oracle

                    Hi Dan,

                      Yes, the default is 11.2 is also no quota.

                     

                    Regards,

                    Mike

                    • 7. Re: SQLServer Migration
                      Mkirtley-Oracle

                      Dan,

                         I just made a test and there is a difference between 11.2 and 12.1.
                      In both databases created a user -

                       

                      CREATE USER test2 identified by test2 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

                       

                      GRANT CREATE SESSION, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SYNONYM TO test2;

                       

                      In 11.2 I could create a table without giving a quota but in 12.1 it gave the error -

                       

                      SQL> create table test1 (col1 char(5));

                      create table test1 (col1 char(5))

                      *

                      ERROR at line 1:

                      ORA-01950: no privileges on tablespace 'USERS'

                       

                      Regards,

                      Mike

                      1 person found this helpful
                      • 8. Re: SQLServer Migration
                        Mkirtley-Oracle

                        Dan,

                          I've done some more testing.

                        During the migration at Step 8 - Target Database - click on the 'Advanced Option' tab at bottom right and in there is a box 'Target Oracle Database 12c'.  If you click on that then it adds 'QUOTA UNLIMITED' unlimited to the 'create user' statement and also 'UNLIMITED TABLESPACE' to the 'Grant' command.

                         

                        This should work round the problem for you.

                         

                        Regards,

                        Mike

                        1 person found this helpful
                        • 9. Re: SQLServer Migration
                          Dan.A

                          Mike,

                           

                          I clearly missed the box, sorry for the trouble.

                           

                          looks like 12c is actually doing the segment allocation on the first insert... did not know that either...

                           

                          The QUOTA UNLIMITED ON TABLESPACE USERS can also be added to the CREATE USER part, but your way gives more flexibility.

                           

                          Again, thank you for help.

                          Great to see and use a good thought product.

                          Feels like driving a well made car.

                           

                          Thanks,

                          Dan

                          • 10. Re: SQLServer Migration
                            Mkirtley-Oracle

                            Hi Dan,

                                  The option isn't very obvious but it is mentioned in the documentation in the User Guide.  One to remember !

                             

                            Glad you like the product and could you also mark the thread as 'answered' ?

                             

                            Regards,

                            Mike

                            1 person found this helpful