[Original] Oracle database logical migration steps

Oracle Database logical migration steps

1. First, query and obtain the exp export script for exporting all non-Oracle user data:

 1   Select   '  Exp system/password @ orcl file =  '   | Username |   '  . Dmp log =  '   |  2   3 Username |   ' . Log owner =  '   | Username |   '  4   5   '  6   7     From  All_users t  8   9    Where T. Created >  10  11 ( Select R. Created From All_users R Where R. Username =   '  System  ' ) +   1 ;

2. Then, obtain the SQL script created for non-oracle users in the new database:

 1   Select   '  Create user '   | Username |   '  Identified by password;  '   |   '  2   3   /  '   |   '  4   5   Grant connect, resource '   | Username |   '  ;  '   |   '  6   7   /  '  8   9     From  All_users t  10   11   Where T. Created >  12   13 ( Select R. Created From All_users R Where R. Username =   '  System  ' ) +   1 ;

3. Then, obtain the SQL script for creating the tablespace required for data import in the new database:

 1  Select   '  Create tablespace  '   | Name |   '  Logging datafile  '   | Name |  2   3          '  . Ora  '   | 4   5          '  Size 1000 m autoextend on next 300 m maxsize unlimited extent management local;  '  6   7     From  V $ tablespace  8   9    Where Name Not   In ( '  System ' , '  Undotbs1  ' , '  Sysaux  ' , '  Users  ' , '  Temp  ' );

4. Finally, query and obtain the IMP import script to import all non-Oracle user data:

 1   Select  '  IMP system/password @ orcl file =  '   | Username |   '  . Dmp log =  '   |  2   3 Username |   '  . Log fromuser =  '   | Username|   '  Touser =  '   | Username |   '  4   5   '  6   7     From  All_users t  8   9    Where T. Created>  10   11 ( Select R. Created From All_users R Where R. Username =   '  System  ' ) +   1 ;

Finally, query and confirm the data migration is successful.