Sharing Knowledge by Mithun Ashok

Often when we use dbms_output.putline in our sql's to print on the console after setting serveroutput on, it throws an error saying buffer overflow. ERROR at line 1: ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 35 ORA-06512: at "SYS.DBMS_OUTPUT", line 198 ORA-06512: at "SYS.DBMS_OUTPUT", line 139 ORA-06512: at line 9 Option to get rid of this issue are, 1. SET SERVEROUTPUT ON SIZE 100000000 You might get an error on 9i as below, SP2-0547: size option 276447232 out of range (2000 through 1000000) In 10g you can specify UNLIMITED for SIZE SET SERVEROUTPUT ON SIZE UNLIMITED 2. exec DBMS_OUTPUT.ENABLE(10000000); This works for any value on 9i and it accepts UNLIMITED on 10gR2. Else the other option is to use UTL_FILE to flush the output to a file. Pre-requisite to use UTL_FILE is to create a database directory. CREATE DIRECTORY ' DIRNAME ' AS ' PATH ' ' '; ...

Lately i wanted to try out an upgrade from 11.5.10.2 to R12.1.1. For this i needed to clone APPS from Linux 4 to Linux 5. I tried to search for some document on metalink but nothing concrete. All i got was 287453.1 Oracle Applications 11.5.10 - Installation Update Notes for Linux x86, Solaris SPARC, MS Windows, HP-UX PA-RISC, HP-Tru64, IBM AIX - B13590-01 Apart from this they do not even have a document on metalink/support portal certification matrix. So here it is. OS Version: Source: Red Hat Enterprise Linux AS release 4 (Nahant) 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005 i686 i686 i386 GNU/Linux Destination: Red Hat Enterprise Linux Server release 5.3 (Tikanga) 2.6.18-128.el5 #1 SMP Wed Dec 17 11:42:39 EST 2008 i686 i686 i386 GNU/Linux DB Cloning: -------------------------- Copy all the files from source to destination. Set the following env variables. FILES: ORACLE_HOME, ALL DB FILES, CONTROL FILES, REDO FILES Now set the env to start the database, As...

I keep forgetting the syntax for create control file, thought it would help me and others for the syntax and the steps to create. STEPS: 1. Shutdown the database 2. sqlplus '/as sysdba' 3. startup nomount 4. Create Control file (syntax below) 5. alter database open CREATE CONTROLFILE REUSE DATABASE "anydatabasename" NORESETLOGS NOARCHIVELOG MAXLOGFILES 50 MAXLOGMEMBERS 3 MAXDATAFILES 1000 MAXINSTANCES 8 MAXLOGHISTORY 500 LOGFILE GROUP 1 'REDLOG PATH' SIZE 10M, GROUP 2 'REDLOG PATH' SIZE 10M DATAFILE ; EXAMPLE: CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG MAXLOGFILES 50 MAXLOGMEMBERS 3 MAXDATAFILES 1000 MAXINSTANCES 8 MAXLOGHISTORY 500 LOGFILE GROUP 1 '/APPS/proddata/log01a.dbf' SIZE 10M, GROUP 2 '/APPS/proddata/log02a.dbf' SIZE 10M DATAFILE '/APPS/proddata/a_txn_data01.dbf' , '...