Monday, June 11, 2018

Migrate SQL Profiles from One Instance to Another Instance

1. Creating a staging table to store the SQL Profiles

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'SQL_PROFILES_MOVE',schema_name=>'SYSTEM');

2. Packing the SQL Profiles to the staging table

SQL> SELECT name FROM dba_sql_profiles;

NAME
---------
SYS_SQLPROF_612f26136ae78001
SYS_SQLPROF_712f260d3e1a1400
SYS_SQLPROF_812ed90a425b2303
SYS_SQLPROF_912f2622a4622103

SQL>

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_MOVE',profile_name=>'SYS_SQLPROF_612f26136ae78001');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_MOVE',profile_name=>'SYS_SQLPROF_712f260d3e1a1400');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_MOVE',profile_name=>'SYS_SQLPROF_812ed90a425b2303');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_MOVE',profile_name=>'SYS_SQLPROF_912f2622a4622103');

3. Export table using exp tool from the source database

exp SYSTEM/password file=SQL_PROFILES.dmp tables=SQL_PROFILES_MOVE

4. Import table using imp tool into the target database

imp SYSTEM/password file=SQL_PROFILES.dmp full=y

5. Unpack the SQL Profiles in the target database

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name => 'SQL_PROFILES_MOVE');

6. SQL profiles names available in the Target database.

SQL> SELECT name FROM dba_sql_profiles;

NAME
---------
SYS_SQLPROF_612f26136ae78001
SYS_SQLPROF_712f260d3e1a1400
SYS_SQLPROF_812ed90a425b2303
SYS_SQLPROF_912f2622a4622103

No comments:

Oracle EBS integration with Oracle IDCS for SSO

Oracle EBS integration with Oracle IDCS for SSO Oracle EBS SSO? Why is it so important? Oracle E-Business Suite is a widely used application...