Notifying Database Change Events (ORACLE)

http://docs.oracle.com/cd/B28359_01/java.111/b31224/dbmgmnt.htm

Example 29-2 


Very useful.

Procedure compiled with errors

select * from SYS.USER_ERRORS where NAME = 'PROCEDURE NAME' and type = 'PROCEDURE'


show errors procedure sonica.PALLET_DESTACKER_TEST

Select Nth Row From A Table In Oracle

Original link:
http://stackoverflow.com/questions/4509167/select-nth-row-from-a-table-in-oracle


select * 
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE  ) a
          where rownum <= N_ROWS )
 where rnum >= N_ROWS

Search a string in clob ---ORACLE

replace table_name, column_name and string

select * from table_name where dbms_lob.instr(column_name,'string')>0;

PLSQL developer 8, oracle client 32 bit 10g and windows 7

Problem:

Even I use the 32 bit oracle client 10g on my windows 7 machine, I still can't use PLSQL developer 8 correctly. Every time I try to log in, it pops up an empty window with no error message.

Solution:

Right solution is found here:
http://stackoverflow.com/questions/3895354/allround-automations-pl-sql-developer-on-windows-7-tns-over-ip-connect-to-remo

RUN IT AS ADMINISTRATOR!!!!

Oracle jobs

SELECT JOB_NAME, OWNER, ENABLED FROM DBA_SCHEDULER_JOBS;

When I tried to recompile a package in sqlplus, the sqlplus window freezes and has not responding.

When I tried to recompile a package in sqlplus, the sqlplus window freezes and has not responding.

The reason is that some objects you are trying to use is locked. Go and check what locks the object.

SP2-1503: Unable to initialize Oracle call interface

Problem:

Install everything correctly, but can't use sqlplus from both double-click and command line.

Solution:

Run as adminstrator

java.lang.NoClassDefFoundError: oracle/sysman/eml/app/Console

This issue and solution is stated in ORACLE document at http://docs.tpu.ru/docs/oracle/en/oas/10.1.2.0.2/aix.1012/relnotes.1012/relnotes/install.htm

check OEM status

 ..../bin/emctl status agent -details

Jinitiator working on Windows 7

refer to
http://notsomany.wordpress.com/2010/03/30/jinitiator-working-on-windows-7/

Thread: ORA-01552: cannot use system rollback segment for non-system tablespace

The undo_ xxx is set to manual in my pfile and init-ora. So I changed it to AUTO.

ORA-30012: undo tablespace ‘UNDOTBS01′ does not exist or of wrong type

I modified the undo_tablespace in init-ora file and spfile by changing undotbs01 to undotbs02 and it worked.

liboraInstaller.so (Cannot open or remove a file containing a running program.)

 Erroin writing to file
'................/oracle/product/10.2.0/db_1/oui/lib/aix/liboraInstaller.so'.
[................/oracle/product/10.2.0/db_1/oui/lib/aix/liboraInstaller.so (Cannot open or remove a file containing a running program.)]

You will see four options.

click Help
click Retry
click Ignore
click Cancel to stop this installation

The solution is that:

Go to that directory and rename the old file, for example: mv liboraInstaller.so liboraInstaller.so.bak

OUI-18001: The operating system 'AIX Version 6100.05' is not supported.

I am installing ORACLE 10.2.0.2 today on an AIX 6.1 machine. This error poped up when the software is checking the prerequisites of the system. Since AIX 6.1 comes after ORACLE 10.2.0.2, so just ignore the error and keep installing.

Can't shutdown oracle database

Last time, I accidentally deleted the control file and can't shutdown the database by using "shutdown immediate". I was so panic....The solution is that using "shutdown abort"...

TOAD windows 7 (ORA-06413 : Connection Not Open)

Problem:
When using TOAD on Windows 7, it pops up an error:

ORA-06413 : Connection Not Open

Solution:

Copy Quest Software from C:\Program(x86) to C:\Program

alert log

 Check Alert Log for the detailed error messages under ORACLE_HOME/admin/db_name/bdump/alert.log

refer to http://www.adp-gmbh.ch/ora/concepts/alert_log.html

Easy way to clone a ORACLE db

Refer to

http://www.dba-oracle.com/oracle_tips_db_copy.htm 

and

http://dbaforums.org/oracle/index.php?showtopic=2917

if you want to clone a database TEST -> DENEME 
1 take a backup of your controlfile 
2 take online / offline backup of all your datafiles 
3 make a relevant copy of your init.ora file or make changes to you are deneme initora
4 delete your control file, redo logs and datafiles on DENEME
5 move these files from TEST -> DENEME
6 using the back up of control file create the new control file for deneme 
NOTE the database name needs to be changed from TEST -> DENEME with the 
SET Clause and option resetlogs (depending on hot /cold backup)
7 create the redo log files 
8 startup mount 
9 recover the log files until cancel
10 open database

create initXXX.ora from spfile

SQL> create pfile='initFDCESW.ora' from spfile='spfilefdcesw.ora';