Saturday 4 March 2017

[Solved]Automatically objects are getting invalid after some days.

Issue:
we have facing some issue that some objects are automatically gets invalid after some days.
after running script utlrp.sql it will get valid but issue comes again after some days.

Solution:
Please follow the below steps.
1) Run 'Re-create grants and synonyms for APPS schema' in adadmin.
2) As sysdba, run the following twice in order to compile any dependent objects
SQL > @?/rdbms/admin/utlrp.sql
3) Run 'Compile APPS Schema' in adadmin
4) Run 'Validate APPS Schema' in adadmin
5) Run the following report:
$AD_TOP/sql/adutconf.sql


I hope issue has been resolved invalid objects.

Friday 3 March 2017

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

Question: 
I am using a procedure to try to pull data for reporting purposes.  I am getting the following ORA-20000 with ORU-10027 error:

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

I need the output for a report, so I cannot just disable it to eliminate the ORA-20000 ORU-10027.

What can I do to get rid of the ORA-20000 ORU-10027 error?

Answer: 
DBMS_OUTPUT has different default buffer sizes, depending on your Oracle version.  For your system, the limit is 2000 bytes.You can increase the buffer up to 1,000,000 using the following PL/SQL statement:

 DBMS_OUTPUT.ENABLE(1000000);

The SQL*Plus statement to achieve the same result is:

set serveroutput on size 1000000

Starting with Oracle release 10g, it is possible to use the following unlimited buffer settings:
  • PL/SQL: DBMS_OUTPUT.ENABLE (buffer_size => NULL);
  • SQL*Plus: set serveroutput on size unlimited
Since I do not have the code you are running, I cannot do a full diagnostic on it.  If the DBMS_OUTPUT settings in the code are acceptable for your version of Oracle and the client (SQL*Plus, etc.) you are using and you are still getting the ORA-20000 ORU-10027 buffer overflow error for the stated limits, you should check your full code to determine whether there are any other calls that include conflicting DBMS_OUTPUT.ENABLE settings that might be overriding yours.