Fix ORA-06508 / PLS-00201 DBMS_OUTPUT Errors and Invalid Objects After Oracle Database Migration

Oracle 12c: Resolving Invalid Objects After Windows-to-Linux Migration

Recently, I migrated an Oracle Database 11g Release 1 database from a Windows platform to Oracle Database 12c on Linux. The migration completed successfully, and the initial validation checks—including database availability, application connectivity, and object validation—were started after the migration.

During the sanity check, I found a large number of invalid database objects.

SELECT owner,

       object_type,

       COUNT(*) AS invalid_object_count

FROM   dba_objects

WHERE  status = 'INVALID'

GROUP  BY owner, object_type

ORDER  BY invalid_object_count DESC;

The database initially contained 226 invalid objects. I executed Oracle’s recompilation script, utlrp.sql, multiple times, but the invalid-object count did not reduce.

@?/rdbms/admin/utlrp.sql

This indicated that the issue was not a normal dependency recompilation problem. A missing dependency, invalid synonym, or privilege issue was likely preventing the objects from compiling.

Symptoms Observed

I selected one invalid object and attempted to compile it manually.

ALTER <object_type> <schema_name>.<object_name> COMPILE;

The compilation failed at the line containing:

DBMS_OUTPUT.PUT_LINE('Debug message');

The same pattern appeared in most of the invalid procedures, functions, packages, and triggers. This was important because the issue was not isolated to one application object—it affected all objects that referenced DBMS_OUTPUT.

As a test, I commented out the DBMS_OUTPUT.PUT_LINE line and compiled the object again. The object became valid.

-- DBMS_OUTPUT.PUT_LINE('Debug message');

This confirmed that the source code was otherwise valid. However, changing hundreds of application objects was not a practical or correct long-term solution.

Note: Commenting out DBMS_OUTPUT.PUT_LINE may help confirm the root cause, but it should be treated only as a diagnostic test—not as the production fix.

Initial Investigation

Because DBMS_OUTPUT is an Oracle-supplied PL/SQL package, I checked its status in the data dictionary.

SELECT owner,

       object_name,

       object_type,

       status

FROM   dba_objects

WHERE  object_name = 'DBMS_OUTPUT';

The SYS.DBMS_OUTPUT package was shown as VALID.

Technical Deep-Dive: Why Does DBMS_OUTPUT Fail Despite Being "VALID"?

In Oracle Cross-Platform Transportable Database / Data Pump migrations (especially across different OS endianness or OS architecture layers like Windows to Linux), C-callouts, wrapped PL/SQL library definitions, or C-type descriptors embedded inside Oracle-supplied system packages can become corrupt or misaligned.

 

Even though DBA_OBJECTS lists SYS.DBMS_OUTPUT as VALID, its internal interface metadata or package specification descriptor remains bound to the source OS platform's compilation state. When dependent application objects compile, the PL/SQL compiler cannot resolve the underlying type declarations, leading to PLS-00201.

At this point, the issue was clearer: the package body and specification could be valid, but application schemas could still fail to resolve or execute DBMS_OUTPUT if the public synonym or the required execute privilege was missing.

I checked the public synonym:

SELECT owner,

       synonym_name,

       table_owner,

       table_name

FROM   dba_synonyms

WHERE  owner = 'PUBLIC'

AND    synonym_name = 'DBMS_OUTPUT';

I also checked whether the required privilege existed:

SELECT grantee,

       owner,

       table_name,

       privilege

FROM   dba_tab_privs

WHERE  owner = 'SYS'

AND    table_name = 'DBMS_OUTPUT'

ORDER  BY grantee;

Architecture / Dependency Diagram

Root Cause

The migration environment had a problem with the DBMS_OUTPUT public synonym and/or package privilege configuration.

Many stored PL/SQL objects call DBMS_OUTPUT.PUT_LINE without explicitly prefixing the package name with SYS, for example:

DBMS_OUTPUT.PUT_LINE('Process started');

For this reference to compile successfully in application schemas, Oracle must be able to resolve DBMS_OUTPUT, normally through the public synonym, and the schema must have permission to execute the package.

Oracle provides the dbmsotpt.sql script for this setup. When executed as SYS, the script creates the public synonym DBMS_OUTPUT and grants EXECUTE on the package to PUBLIC.

Resolution

I connected as SYSDBA and executed the Oracle-supplied script:

SQL> CONNECT / AS SYSDBA

SQL> @?/rdbms/admin/dbmsotpt.sql

The script location is generally:

$ORACLE_HOME/rdbms/admin/dbmsotpt.sql

After executing the script, I manually compiled one of the previously invalid objects. This time, the compilation completed successfully.

ALTER <object_type> <schema_name>.<object_name> COMPILE;

I then ran the recompilation utility again to recompile the remaining invalid objects.

SQL> @?/rdbms/admin/utlrp.sql

The invalid-object count reduced from 226 to 8.

SELECT COUNT(*) AS invalid_objects

FROM   dba_objects

WHERE  status = 'INVALID';

The remaining eight objects should be investigated individually because they may have unrelated dependency, privilege, or application-code issues.

Validation Queries

After the fix, use the following checks.

Confirm DBMS_OUTPUT Is Valid

SELECT owner,

       object_name,

       object_type,

       status

FROM   dba_objects

WHERE  owner = 'SYS'

AND    object_name = 'DBMS_OUTPUT';

Confirm the Public Synonym Exists

SELECT owner,

       synonym_name,

       table_owner,

       table_name

FROM   dba_synonyms

WHERE  owner = 'PUBLIC'

AND    synonym_name = 'DBMS_OUTPUT';

Expected result:

OWNER   SYNONYM_NAME  TABLE_OWNER  TABLE_NAME

------  ------------  -----------  ----------

PUBLIC  DBMS_OUTPUT   SYS          DBMS_OUTPUT

Confirm Execute Permission

SELECT grantee,

       privilege

FROM   dba_tab_privs

WHERE  owner = 'SYS'

AND    table_name = 'DBMS_OUTPUT'

AND    privilege = 'EXECUTE';

Identify the Remaining Invalid Objects

SELECT owner,

       object_name,

       object_type,

       status

FROM   dba_objects

WHERE  status = 'INVALID'

ORDER  BY owner, object_type, object_name;

Display Compilation Errors

SELECT owner,

       name,

       type,

       line,

       position,

       text

FROM   dba_errors

WHERE  owner = UPPER('<SCHEMA_NAME>')

ORDER  BY name, sequence;

Post-Migration Best Practices for DBAs

1. Always Re-run System Scripts Post-Platform Migration: When migrating databases across different platforms (Windows to Linux, AIX to Linux), always run $ORACLE_HOME/rdbms/admin/utlirp.sql (to reset compiled PL/SQL code) followed by utlrp.sql.

2. Re-install Core RDBMS Packages if Needed: If multiple system utilities throw compilation errors post-migration, consider reloading standard administrative packages via @catproc.sql.

3. Check Public Synonyms: Verify that public synonyms for core packages (DBMS_OUTPUT, DBMS_SQL, UTL_FILE) point correctly to SYS ownership and hold valid PUBLIC grants.

References & Further Reading

https://docs.oracle.com/database/121/ARPLS/d_output.htm#ARPLS67301

****************************நன்றி****************************