How to Get DDL of the Procedure or any object in oracle

use below sql to get the DDL of a specific object:

set long 32000

set long 90000

set heading off;

set echo off;

set pages 1000;

SQL> spool object_name.sql

SQL> select dbms_metadata.get_ddl('object_type','object_name','Owner of the object') from dual;

SQL> spool off

Thanks...

ICMP host xx.y.xxx.xyz unreachable - admin prohibited, length 68 (linux)

run tcp dump on the server that is having an issue....  run below command and try to connect from other servers and see....


> tcpdump -r ensXXX -n host xx.y.xxx.xyz and  not port ssh and not port domain


if you see this below message then

error:- ICMP host xx.y.xxx.xyz unreachable - admin prohibited, length 68 (linux).

solution:-

. make sure firewall rules are setup between the servers you want to listen...
. make sure the firewalld process is shutoff or enable it with the ports that  you want to listen on.. and try again....

Import Data into table using Toad(sql*loader)

1) Remove the first line (which usually has the column names mentioned) and save the file in the .csv (delimited) format.
2) And make sure the table is created(if it is new) in the database where you are loading this csv file.
3) Make sure you have same number of columns in file and table.

4) Open Toad and connect to the database/schema that you want to load this data in to the table.
5) In Toad -> Database tab -> SQL *Loader Wizard
6) since, we are doing first time. check -> Build control file and Specify fields and then click Next.
7) click Add 
8) load the Input file (that is in csv format). -> choose file of type to -> All files.
9) click OK.
10)click next.
11) make sure you see the columns and data format that need to be here... if not go back and load the corrected file.
12) click next.
13) Under tables tab, click ADD -> select schema name -> table name-> and load method type(insert).
14) click next after confirming the fields.
15) click next.
16) specify the control file and log file name... and the location to be created so that it can be used if you have to do the same thing again.
17) click next.
18) check -> just build the control file and click FINISH. this will create control file for this task.
19) Now check -> Execute now and click FINISH. This will load the data in to the tab le.
20) validate the data in the table. you should be all set.


Hope this helps!!!!!!!!!!!!!!!!!!!!!!!

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....