copy sql profiles from one env/db to another env/db

In this post we are trying to move the sql profiles that we created in one environment to another. as we now we cretae these sql profiles to make the sql performance better. And once this is tested we want to use the same profile to all env....

to do this we need to do the below steps...

1. Creating a staging table to store the SQL Profiles

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'SQL_PROFILES_TT',schema_name=>'schema_name');

2) Packing the SQL Profiles to the staging table

SELECT name FROM dba_sql_profiles;  ---once you have created the profile with OEM/db.

SYS_SQLPROF_013c88540c760000'

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_013c88540c760000');

3. Export table using “expdp” tool from the source database

4. Export table using “impdp” tool to the destination database

5)  SQL> conn / as sysdba

Connected.

SQL> grant administer sql management object to schema_name;

Grant succeeded.

Note: you will get below error if you dont have not given the   "administer sql management object " privilege...

SQL> conn schema_name/passd

Connected.

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

BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name=> 'SQL_PROFILES_TT'); END;

*

ERROR at line 1:

ORA-38171: Insufficient privileges for SQL management object operation

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_SMB", line 83

ORA-06512: at "SYS.DBMS_SQLTUNE", line 7657

ORA-06512: at "SYS.DBMS_SQLTUNE", line 6349

ORA-06512: at line 1

SQL> conn schema_name/passd

Connected.

SQL> show user;

USER is "schema_name"

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

PL/SQL procedure successfully completed.

SQL> conn / as sysdba

Connected.

SQL> select name from dba_sql_profiles;

NAME

------------------------------

SYS_SQLPROF_010c760000

Hope this helps....

using two different locations in expdp/impdp

sometimes we want to use a differnet location for logfile different to that of dumpfile location. to do that we need to create 2 directory locations one for dumpfile and one for log file. this is same for expdp/impdp

create or replace directory dpdir_dmp as 'tmp/exp';
create or replace directory dpdir_log as '/tmp/log';

expdp username/paswd directory=dpdir_dmp dumpfile=exp_backup_user.dmp logfile=dpdir_log:exp.log full=y


hope this helps...

expdp ORA-31633 while running the export script...

when ever we come across the "expdp ORA-31633" error... this means we need to remove the outstanding jobs that are already in there or using that table....

"ORA-00955: name is already used by an existing object"

we need to find the tables that are already been there and are not dropped once the expdp/impdp jobs are performed.(usually these are gone if the jobs exits normally without any errors/interruptions..)

select owner_name, job_name, operation, job_mode,state, attached_sessions from   dba_datapump_job  where  job_name not like 'BIN$%  order  by 1, 2;

If you see the tables... drop them.... and then try the expdp job again. This time it should work...

Hope this helps...

"sed" helpful commands

Below are some useful commands for daily use......

1) To delete lines between specified words....and save to the same file.

sed -i '/from/,/produced/d' find.txt

sed -e '/from/,/produced/d' find.txt | tee find.txt (try this if above one is not working to save to the same file)...if  this is not removing the first line of the word...

sed -e '/from/,/produced/d' find.txt | sed '1d' | tee find.txt


2) To count the number of lines between specified words...

sed -n '/from/,/produced/p' oracle | sed -n '$='

3) To delete "nth" line in the file and save to the same file....

 sed -e 'nd' find.txt | tee find.txt

4) To delete last line in the file and save to the same file..

sed -e '$d' find.txt | tee find.txt

5) to delete the specific lines and save to the same file....

sed -e '2d;4d' find.txt | tee find.txt

6) to delete the lines that have a specific word...

sed -e '/ABC/d' find.txt | tee find.txt

if want to delete the lines other that the find ones...then do "/!d" instead of "/d"...

7)  to delete the lines which are starting/begining with a specific word.

sed -e '/^LOG/d' find.txt | tee find.txt

8)   to delete the lines which end with the specific word..

,sed -e '/LOG$/d' find.txt | tee find.tx


will add some more going forward....

Hope this helps....