Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • oracle fast start failover best practice Oracle
  • shutdown linux Linux/Unix
  • To find explain plan for a statement that occurred in past. Oracle
  • find_err.sql for finding errors from dba_errors. Oracle
  • Paste command syntax Linux/Unix
  • New Latest Param.sql for finding all hidden parameters also Oracle
  • run this before doing any dbchange pvm_pre_change.sql Oracle
  • proc.sql Oracle
  • True Session Wait Activity in Oracle 10g Verygood Oracle
  • Metalink Note: Note:250655.1 : ADDM Basics USING THE AUTOMATIC DATABASE DIAGNOSTIC MONITOR Oracle
  • backspace in SQL Plus not working then..? Linux/Unix
  • move_arch_files.ksh Linux/Unix
  • Good Site for Oracle Internals Oracle
  • AMC10 Uncategorized
  • Creating a Container Database using dbaascli Uncategorized

Transfer SQL Profiles from One database to other database.

Posted on 05-Sep-202403-Sep-2026 By Admin No Comments on Transfer SQL Profiles from One database to other database.

In this guide, I’ll walk you through moving SQL profiles using a staging table. SQL profiles help databases optimize query execution plans.

Step#1 Create the Staging Table

First, create a staging table to hold SQL profiles that need to be transferred.

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name => 'SQL_STG_TAB', schema_name => 'SYS');

This will create a table SQL_STG_TAB that holds SQL profiles. Its structure matches the DBA_SQL_PROFILES table

Step#2 Find the Profile Name to Move

To pack a particular SQL profile, identify its name by querying the DBA_SQL_PROFILES table.

SQL> select name from DBA_SQL_PROFILES where SQL_TEXT like '%SELECT%EMPLOYEE%NAME%';
NAME
---------------------
SYS_SQLPROF_014b

Step#3 Pack the Custom Profile to Staging Table

To pack a specific profile into the staging table:

SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(staging_table_name => 'SQL_STG_TAB', profile_name => 'SYS_SQLPROF_014b');

-- For all profiles use '%' in profile_name.
SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(staging_table_name => 'SQL_STG_TAB', profile_name => '%');

To pack all profiles under the DEFAULT category:

SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(staging_table_name => 'SQL_STG_TAB');

Step#4 Export the Staging Table

Now, export the staging table using exp:

$ exp "'/ as sysdba'" tables=SQL_STG_TAB file=SQL_STG_TAB.dmp

Step#5 Import the Staging Table

Copy the dump file to the target database instance and use imp to import it:

$ imp "'/ as sysdba'" file=SQL_STG_TAB.dmp full=y

Step#6 Unpack the Profile

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE, staging_table_name => 'SQL_STG_TAB');

By following these steps, you’ll successfully move SQL profiles between databases.

Oracle, shell, SQL scripts

Post navigation

Previous Post: Load testing on Oracle 19C RAC with HammerDB
Next Post: Python class import from different folders

Related Posts

  • DBMS_UTILITY PACKAGE Oracle
  • Unix split command to split files Linux/Unix
  • catall.sh Linux/Unix
  • replacing ^M character when passing files from Windows to Unix Linux/Unix
  • Jai Shree Ram Oracle
  • SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AMC10 (0)
  • Ansible (0)
  • AWS (2)
  • Azure (1)
  • Django (0)
  • GIT (1)
  • Linux/Unix (150)
  • MYSQL (5)
  • Oracle (403)
  • PHP/MYSQL/Wordpress (10)
  • POSTGRESQL (1)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (18)
  • rman-dataguard (26)
  • shell (151)
  • SQL scripts (349)
  • SQL Server (6)
  • Uncategorized (9)
  • Videos (0)

Recent Posts

  • Tutornet.US — Source of excellent learning14-Oct-2026
  • OCI Infrastructure Patching14-Oct-2026
  • Free API credits for AI models14-Oct-2026
  • AMC1020-Sep-2026
  • Key Management in Oracle: The Core Issue: Missing Master Key12-May-2026
  • SAT Mathematics 10 questions and answer at the end.30-Apr-2026
  • top 10 AI news today30-Apr-2026
  • runon_allpdbs_show_conname.sh23-Apr-2026
  • runon_allcdbs_find_pdbs.sh23-Apr-2026
  • Running PDB on single node in RAC09-Apr-2026

Archives

  • 2026
  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • All About oracle password and security from metalink Oracle
  • 751131.1 New Article Error 2819 While Requesting a Systemstate Dump Oracle
  • CTAS with LONG Column for 7.x and 8 and 8i Oracle
  • New Latest Param.sql for finding all hidden parameters also Oracle
  • eplan.sql Oracle
  • Wait Based Tuning Step by step with SQL statement Oracle
  • Windows based Command line mailing program like mailx (Sednmail for windows) PHP/MYSQL/Wordpress
  • cur_sql.sql Oracle

Copyright © 2026 pvmehta.com.

Powered by PressBook News WordPress theme