Show object info

SET LINESIZE 135
SET PAGESIZE 9999

COLUMN owner           FORMAT A15    HEADING "Owner"
COLUMN object_name     FORMAT A35    HEADING "Object Name"
COLUMN object_type     FORMAT A18    HEADING "Object Type"
COLUMN created                       HEADING "Created"
COLUMN status                        HEADING "Status"

SELECT
    owner
  , object_name
  , object_type
  , TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') created
  , LPAD(status, 7) status
FROM all_objects
WHERE object_name like UPPER('%&OBJECT_NAME%')
ORDER BY owner, object_name, object_type
/

Objects summary

SET LINESIZE 135
SET PAGESIZE 9999

COLUMN owner           FORMAT A15          HEADING "Owner"
COLUMN object_type     FORMAT A18          HEADING "Object Type"
COLUMN obj_count       FORMAT 999,999,999  HEADING "Object Count"

break on report on owner skip 2
compute sum label ""               of obj_count on owner
compute sum label "Grand Total: "  of obj_count on report

SELECT
    owner
  , object_type
  , count(*)    obj_count
FROM
    dba_objects
GROUP BY
    owner
  , object_type
/