Find word count from string

select (length('mmohsamohsdmohsg')-length(replace('mmohsamohsdmohsg','mohs',''))) / length('mohs') from dual;

undo retention for ORA-01555

1) alter system set undo_retention = 1500 scope=both; 2) Check undot tablespace size 3) Disk size where datafile of tablespaces are located 4) Query optimiztion if required which causes this issue

creating dbconsole service for OEM

#drop
emca -deconfig dbcontrol db -repos drop

After drop check in regedit dbconsole service in path "HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\SYSMAN\OracleDBConsoleorcl" if not removed then remove OracleDBConsoleorcl then create

#create (use this)
emca -config dbcontrol db -repos create

To find locked oracle query

SELECT sql_text FROM   v$sql WHERE  sql_id IN (SELECT sql_id                   FROM   v$session                   WHERE  sid IN (SELECT sid                                  FROM   v$lock                                  WHERE  (id1,id2,TYPE) IN (SELECT id1,                                                                   id2,                                                                   TYPE                                                            FROM   v$lock                                                            WHERE  request > 0)))

ORA-12012 ORA-27468: "SYS.PURGE_LOG" is Locked by Another Process ORA-29516: Aurora assertion failure

SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> @?/rdbms/admin/utlrp.sql

ORA-01092 ORA-00704: bootstrap process failure / ORA-39700

while starting db giving ORA-01092: ORACLE instance terminated. Disconnection forced & in alert log file  ORA-00704: bootstrap process failure / ORA-39700

Solution:

1. Open a command prompt and browse to /rdbms/admin
2. Start sqlplus (sqlplus / as sysdba)
3. Run "startup upgrade"
4. Run @catproc.sql and SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql

SQL> SPOOL OFF