Solutions Chapter 13: The Data Dictionary and Dynamic SQL Scripts Test Your Thinking

  1. Formulate the question that is answered by the following query.

    SELECT table_name, column_name, comments
     FROM user_col_comments
    

    Solution:

    Write a query to display all the column comments in the user's schema.

    TABLE_NAME COLUMN_NAME     COMMENTS
    ---------- --------------- --------------------
    COURSE     COURSE_NO       The unique ID for a
                               course.
    COURSE     DESCRIPTION     The full name for th
                               is course.
    ...
    ZIPCODE    CREATED_DATE    Audit column - indic
                               ates date of insert.
    ZIPCODE    MODIFIED_BY     Audit column - indic
                               ates who made last u
                               pdate.
    ZIPCODE    MODIFIED_DATE   Audit column - date
                               of last update.
    
    122 rows selected.
    

    The result of the query displays a list of column comments. It is useful to place comments on columns and/or tables describing the information found within the column or table. The following command creates a column comment for the INSTRUCTOR_ID column on the INSTRUCTOR table.

    COMMENT ON COLUMN INSTRUCTOR.INSTRUCTOR_ID IS 'The unique ID for an instructor.'
    Comment created.
    

    A table comment is stored in the data dictionary view USER_TAB_COMMENTS. The next statement creates a table comment for the instructor table.

    COMMENT ON TABLE INSTRUCTOR IS 'Profile information for an instructor.'
    Comment created.
    
  2. Describe the differences between the views USER_USERS, ALL_USERS, and DBA_USERS.

    Solution:

    The USER_USERS view shows information about the currently logged in user. You see useful information such as the default tablespace name and the temporary tablespace name as well as the date the user was created. The ALL_USERS view shows a list of all the users in the system and the date the user was created.

    The DBA_USERS view displays all the users in the system. The columns listed include the date the user was created, the default and temporary tablespaces, and the encrypted password.

  3. Name the underlying data dictionary views for the public synonyms TABS and COLS.

    Solution:

    The public synonyms are USER_TABLES and USER_TAB_COLUMNS. The queries to determine this solution are:

    SELECT synonym_name, table_name
      FROM all_synonyms
     WHERE synonym_name IN ('TABS', 'COLS')
    SYNONYM_NAME                   TABLE_NAME
    ------------------------------ ----------------------------
    TABS                           USER_TABLES
    COLS                           USER_TAB_COLUMNS
    
    2 rows selected.
    

    You can also query the DICT view with this statement.

    SELECT *
      FROM dict
     WHERE table_name IN ('TABS', 'COLS')
    TABLE_NAME              COMMENTS
    ----------------------- ------------------------------
    COLS                    Synonym for USER_TAB_COLUMNS
    TABS                    Synonym for USER_TABLES
    
    2 rows selected.
    
  4. Write a dynamic SQL script to drop all views in the STUDENT schema. If there are no views, create some to test your script.

    Solution:

    To create some views issue the following statements.

    CREATE OR REPLACE VIEW view1_v AS
    SELECT *
      FROM student
    
    CREATE OR REPLACE VIEW view2_v AS
    SELECT *
      FROM course
    

    Now create a file with the following commands. Save the file.

    SET ECHO OFF
    REM File name: drop_view.sql
    REM Purpose:   Drop all the views in a user's schema.
    REM Created:   17-Mar-2000 AR
    REM Version:   1.0
    SET PAGESIZE 0
    SET LINESIZE 80
    SET FEEDBACK OFF
    SET TERM OFF
    SPOOL drop_view.out
    SELECT 'DROP VIEW '||view_name||' ;'
      FROM user_views;
    SPOOL OFF
    SET PAGESIZE 24
    SET LINESIZE 80
    SET FEEDBACK ON
    SET TERM ON
    SET ECHO ON
    @drop_view.out
    

    Then execute the file from the SQL*Plus prompt with the @ command.

    SQL>@drop_view.sql 
    

Select Another Chapter

  1. SQL and Data
  2. SQL: The Basics
  3. Character and Number Functions
  4. Date, Conversion, and Miscellaneous Functions
  5. Aggregate Functions, GROUP BY, and HAVING
  6. Equijoins
  7. Subqueries
  8. Set Operators
  9. Complex Joins
  10. Insert, Update, and Delete
  11. Create, Alter, and Drop Tables
  12. Views, Indexes, and Sequences
  13. The Data Dictionary and Dynamic SQL Scripts
  14. Security
  15. Advanced SQL Queries
  16. SQL Optimization