Loading...
administration
Corporate Training
Database Administration
Interview Questions
Online Training
Oracle
Oracle DBA
Overview
PLSQL
VirtualNuggets
https://bestoracledbaonlinetraining.blogspot.com/2016/05/how-to-migrate-sql-profiles.html
You can migrate SQL profile using export and import from one database to another database just like stored outline. Prior to oracle 10g you can migrate SQL profiles with the dbms_sqltune.import_sql_profile procedure where as in oracle 10g release 2 and beyond using dbms_sqltune package. In both case you have to create a staging table on the source database and populate that staging table with the relevant data. Below is the step to migrate SQL profile in 10g release 2.
Step1. Create the staging table to store SQL Profiles in source database
SQL> sys/oracle@sadhan as sysdba
SQL> BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF
(table_name => ‘SQL_PROFILES’,schema_name=>’HRMS’);
END;
/
PL/SQL procedure successfully completed.
Step2. Now Copy SQL profiles from SYS to the Staging table
SQL> BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF
(profile_category => ‘%’,
staging_table_name => ‘SQL_PROFILES’,
staging_schema_owner=>’HRMS’);
END;
/
PL/SQL procedure successfully completed.
Note: As you need to copy all SQL profiles on my database ‘%’ value for profile_category was the best option.
Step3. Export the staging table at source
SQL> select count(*) from HRMS.sql_profiles;
COUNT(*)
3
expdp system/***** dumpfile=expdp_sql_profiles.dmp TABLES=HRMS.SQL_PROFILES DIRECTORY=DPUMP
Step4. Restore the database with the backup taken before all SQL profiles were generated and import the staging table at target database.
impdp system/***** dumpfile=expdp_sql_profiles.dmp TABLES=HRMS.SQL_PROFILES DIRECTORY=DPUMP TABLE_EXISTS_ACTION=REPLACE
Note: Do not forget to create staging table on destination database. Use replace = TRUE if you need to have same SQL_Profiles on both the database.
Step5. Finally Unpack the SQL profiles from the staging table on destination database.
SQL> BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF
(staging_table_name => ‘SQL_PROFILES’,
staging_schema_owner=>’HRMS’, replace=>FALSE);
END;
/
PL/SQL procedure successfully completed.
administration,
Corporate Training,
Database Administration,
Interview Questions,
Online Training,
Oracle,
Oracle DBA,
Overview,
PLSQL,
VirtualNuggets
VirtualNuggets
4032910976176572398
Post a Comment
Home
item
Popular Posts
-
The types of users and their roles and responsibilities depend on the database site. A small site can have one database administrator who ...
-
11) How to know how much free memory available in sga? select * from v$sgastat where name =’free memory'; ...
-
Let us see about Oracle DBA significance in this CRB Tech reviews. Details Technological innovation (IT) is the buy, handling, storage are...
-
1) How to set pga size, can you change it while the database is running? show parameter pga_aggregate_target; alter system set pga_a...
-
To perform the all administrative tasks of an Oracle Database DBA, you need specific privileges within the database and possibly in the o...
-
Export (exp), Import (imp) are Oracle utilities which allow you to write data in an ORACLE-binary format from the database into operating ...
-
You can migrate SQL profile using export and import from one database to another database just like stored outline. Prior to oracle 10g yo...
-
Simply creating one main table where initially data will store then simply create some test tables and a sequence for later use in this ex...