Show database objects

SET HEADING OFF
SET NORECSEP

SELECT
  RPAD(owner,10) || ' ' ||
  RPAD('[' || LTRIM(RTRIM(object_name))|| ']',30) ||
  RPAD(LTRIM(RTRIM(object_type)),10) || ' ' ||
  status datablock
FROM
  dba_objects
WHERE
  object_name LIKE '%' || UPPER('&1') || '%'
ORDER BY
  object_name;

***************** OR *****************

SET HEADING OFF
SET LINESIZE 132

SELECT
LTRIM(RTRIM(owner)) || '.' || LTRIM(RTRIM(object_name)) obj_name,
LTRIM(RTRIM(object_type)) obj_type,
LTRIM(RTRIM(status)) obj_status,
TO_CHAR(created,'YYYY/MM/DD') created,
  TO_CHAR(last_ddl_time,'YYYY/MM/DD HH24:MI:SS') changed
FROM
all_objects
WHERE
object_name like UPPER('%&1%');
Advertisements