39 lines
1.1 KiB
SQL
39 lines
1.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Search for INVALID objects
|
|
* Parameters : 1 - Object name owner.objectname format. (% - wildcard, \ - escape char)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 05-Aug-04 Vishal Gupta First Draft
|
|
*/
|
|
|
|
DEFINE object_name="&&1"
|
|
|
|
COLUMN owner FORMAT a20
|
|
COLUMN object_name FORMAT a30
|
|
COLUMN object_type FORMAT a20
|
|
COLUMN status FORMAT a10
|
|
COLUMN last_ddl_time HEADING "Last DDL Time" FORMAT a20
|
|
COLUMN created HEADING "Create Time" FORMAT a20
|
|
|
|
SELECT do.owner
|
|
, do.object_name
|
|
, do.object_type
|
|
, do.status
|
|
, to_char(do.last_ddl_time,'DD-MON-YY hh24:MI:SS') last_ddl_time
|
|
, to_char(do.created,'DD-MON-YY hh24:MI:SS') created
|
|
FROM dba_objects do
|
|
WHERE do.status = 'INVALID'
|
|
AND do.owner || '.' || do.object_name LIKE UPPER('&object_name') ESCAPE '\'
|
|
ORDER BY do.owner
|
|
, do.object_name;
|
|
|
|
|
|
@@footer
|