Identify invalid objects

select object_name, object_type, status from dba_objects where owner = 'TRAX' AND STATUS = 'INVALID';

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


OBJECT_NAME OBJECT_TYPE STATUS OWNER
--------------- ------------------- ------- -------
TRAX_GETCALL PROCEDURE INVALID TRAX

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

SQL> ALTER PROCEDURE TRAX.TRAX_GETCALL COMPILE;

Warning: Procedure altered with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE TRAX.TRAX_GETCALL COMPILE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3 PL/SQL: SQL Statement ignored
9/39 PL/SQL: ORA-00942: table or view does not exist

Then execute the Get DDL query given in the blog to view the full sqltext body.
=================================================================================

Get table and index DDL( Full SQL text)

############Get table and index DDL the easy way###############

set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

spool off;


============================================================================================


Now we can modify the syntax to punch a whole schema.

It us easily done by selecting dbms_metadata. get_ddl and specifying USER_TABLES and USER_INDEXES. :



set pagesize 0
set long 90000
set feedback off
set echo off

spool scott_schema.sql

connect scott/tiger;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;

spool off;


=========================================================================