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.
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.
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.
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