Oracle Report Page Headers Footers - Exercise

You are the newest DBA on the team. Your manager wants a clean SQL*Plus report that summarizes the objects in the database:

• One row per (owner, object type)
• Object count and most recent DDL change date
• Readable formatting, headers, footers, and grouping

Write the SQL*Plus commands needed to generate a report with these characteristics:

1. One row per combination of user (owner) and object type.
2. Show the number of objects owned by each user for each object type, and the most recent modification date for that group.
3. Format the number and date columns for easy reading.
4. Use clear, professional column titles.
5. Sort by owner and object type.
6. Suppress repeating owner values (so the owner appears once per group).
7. Place the page number at the bottom of each page.
8. Place a report title at the top of each page. The title must include your company name and read “Database Objects by Type”.
9. Skip a line or two between each owner group.

What to submit: SQL*Plus formatting commands such as TTITLE, BTITLE, BREAK, and COLUMN (and any related settings you need, like line/page sizing).

SELECT owner,
       object_type,
       COUNT(*) object_count,
       TO_CHAR(MAX(last_ddl_time),'dd-Mon-yyyy') last_ddl_time
FROM   dba_objects
GROUP  BY owner, object_type
ORDER  BY owner, object_type;

This query returns the object counts by type and the most recent DDL time for each (owner, object_type) group. All that remains is to format the output using SQL*Plus reporting commands.