Transporting SQL Profiles Between Databases
Following 457531.1
Transporting the SQL Profile
====================
i) Creating a staging table to store the SQL Profiles
SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'TEST',schema_name=>'SCOTT');
PL/SQL procedure successfully completed.
table_name => name of the table to store the SQL Profiles.
schema_name => name of the user where the table is to be created.
ii) Packing the SQL Profiles to the staging table
SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'TEST',profile_name=>'my_sql_profile');
PL/SQL procedure successfully completed.
staging_table_name => name of the table to store the SQL Profiles.
profile_name => name of the SQL Profile to be packed.
Note: The table_name and schema_name are case-sensitive.
SQL> desc test
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
PROFILE_NAME VARCHAR2(30)
CATEGORY VARCHAR2(30)
SIGNATURE NUMBER
SQL_TEXT CLOB
DESCRIPTION VARCHAR2(500)
TYPE VARCHAR2(9)
STATUS VARCHAR2(8)
BOOLEAN_FLAGS NUMBER
ATTRIBUTES SQLPROF_ATTR
VERSION NUMBER
SPARE1 CLOB
SPARE2 BLOB
SQL> select count(*) from test;
COUNT(*)
----------
1
SQL> set autot on
SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 31 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7839)
Note
-----
- SQL profile "my_sql_profile" used for this statement
iii) Now export the table using Datapump or Export/Import.
[oracle@localhost oracle]$ exp scott/tiger tables=test
Export: Release 10.2.0.3.0 - Production on Fri Sep 7 01:18:06 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST 1 rows exported
Export terminated successfully without warnings.
iv) Execute the query in another database without SQL Profiles
SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 348 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 4 | 348 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7839)
Note: The SQL Profiles are not there.
v) Importing the SQL Profiles to the database
[oracle@localhost oracle]$ imp scott/tiger file=expdat.dmp full=y
Import: Release 10.2.0.3.0 - Production on Fri Sep 7 01:24:15 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "TEST" 1 rows imported
Import terminated successfully without warnings.
vi) Now Unpack the SQL Profiles
SQL> conn scott/tiger /* connect to scott schema where the staging table is imported and then unpack */
SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'TEST');
PL/SQL procedure successfully completed.
vii) Checking the SQL Profiles
SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 31 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7839)
Note
-----
- SQL profile "my_sql_profile" used for this statement
Now the SQL Profile is transported to another database.