Oracle: How to find patches installed on Oracle

Launch command prompt

1) Navigate to Oracle Home Directory

Your Command prompt should look like

c:\app\machine_name\product\11.x.x.\db_1> for 11g

c:\oracle\product\10.x.x\db_1> for 10g.

2) Now set Oracle_HOME to the Oracle homes path, In my case for 10g

c:\oracle\product\10.x.x\db_1> SET ORACLE_HOME= c:\oracle\product\10.x.x\db_1

3) Navigate to Opatch directory

c:\oracle\product\10.x.x\db_1> cd opatch

c:\oracle\product\10.x.x\db_1\opatch>

4) type opatch lsinventory

c:\oracle\product\10.x.x\db_1\opatch>opatch lsinventory

You will get a report on the patches installed.

how to export all filenames in a folder to a text file

Well, I found a very simple solution to this.

Launch Windows command prompt, Navigate to the root folder ( say c:\temp)

C:\Temp> dir /s /b > filenames.txt

/s -> includes all subdirectories

/b -> will inlcude only file name ( no attributes, date created etc)

Oracle: Sql to find table and column comments

Select t.table_name,tc.comments table_comments,c.column_name,data_type,cc.comments column_comments
from user_tables t, user_tab_columns c,user_tab_comments tc,user_col_comments cc
where t.table_name = c.table_name
and t.table_name = tc.table_name(+)
and c.table_name = cc.table_name (+)
and c.column_name = cc.column_name (+)
order by table_name,column_id

enq: TM - contention

It took me a while to resolve this issue on my database. Every single resource on the Net pointed to "missing indexes on foreign key columns", but I found it was due to another session locking my database.

The following query gave me a list of blocking sessions. Once I killed those sessions,
"enq: TM - contention" went away.

Select v.sid,s.blocking_session, s.event, v.type,v.lmode,v.request,o.object_name, o.object_type
from v$lock v ,dba_objects o, v$session s
where v.id1 = o.object_id
and v.sid = s.sid
and owner ="Put_your_Owner_Name_Here"

enq: TM - contention

It took me a while to resolve this issue on my database. Every single resource on the Net pointed to "missing indexes on foreign key columns", but I found it was due to another session locking my database.

The following query gave me a list of blocking sessions. Once I killed those sessions,
"enq: TM - contention" went away.

Select v.sid,s.blocking_session, s.event, v.type,v.lmode,v.request,o.object_name, o.object_type
from v$lock v ,dba_objects o, v$session s
where v.id1 = o.object_id
and v.sid = s.sid
and owner ="Put_your_Owner_Name_Here"

How to clean your monitor

ORA-39149: cannot link privileged user to non-privileged user

When you use Network_link parameter to import a database dump, you will get the error
"ORA-39149: cannot link privileged user to non-privileged user", if the EXP_FULL_DATABASE privilege is missing on the source schema.

Log in as SYS/System and grant the following privilege.

Grant EXP_FULL_DATABASE TO schema_owner;