RelationalDBDesign RelationalDBDesign


SQL* Plus CLI  «Prev  Next»

Oracle Report Page Headers Footers - Exercise

Database objects by type


Objective:Format the columns and titles of a SQL *Plus report.

Exercise scoring

This exercise is worth a total of 30 points. Once you have completed your answer you will submit your answer.

Background/overview

You are the new person on a team of DBAs, and your boss wants you to become familiar with the database that you are managing.
He also wants an updated summary of the objects contained within that database. He asks you to produce a report listing the number of objects owned by each user and to sort it by object type.

Instructions

Your job is to write the SQL*Plus commands to generate a report with the following characteristics:
  1. It should contain one row for each combination of user and object type.
  2. For each user-object type combination, the report should show the number of objects of that type owned by the user and the most recent date any of those objects was modified.
  3. The number and date fields should be formatted for easy reading.
  4. Columns should have appropriate column titles.
  5. The report should be sorted by owner and object type.
  6. Repeating occurrences of the same owner name should be suppressed.
  7. The page number should go at the bottom of the page.
  8. The report title should read "Database Objects by Type" and should go at the top of the page. Your company name should appear in the title.
  9. Skip a line or two between each user.

Hints


Since you have not taken the Oracle Data Dictionary course yet, your boss has supplied you with the following SQL query to use for this report:
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 satisfies the ordering and grouping requirements. It also returns the required count of objects by type, and the most recent modification date for each group of objects. All that remains is for you to use the TTITLE, BTITLE, BREAK and COLUMN commands to format the output so that it looks good.

Submitting your exercise

Enter your answer into the text box below. Click the Submit button to submit the code.