Oracle Database 10g SQL Fundamentals I - Practice 11

Oracle Database 10g SQL Fundamentals I - Practice 11
SUBJECT : 11. Managing Objects with Data Dictionary Views

DICTIONARY
USER_OBJECTS
USER_TABLES
USRE_TAB_COLUMNS
USER_CONSTRAINTS
USER_CONS_COLUMNS
USER_VIEWS
USER_SEQUENCES
USER_TAB_SYNONYMS

1.
SELECT COLUMN_NAME
, DATA_TYPE
, DATA_LENGTH
, DATA_PRECISION AS PRECISION
, DATA_SCALE SCALE
, NULLABLE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&TAB_NAME')
;

2.
SELECT UCC.COLUMN_NAME
, UC.CONSTRAINT_NAME
, UC.CONSTRAINT_TYPE
, UC.SEARCH_CONDITION
, UC.STATUS
FROM USER_CONSTRAINTS UC
, USER_CONS_COLUMNS UCC
WHERE UC.TABLE_NAME = UCC.TABLE_NAME
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = UPPER('&TAB_NAME')
;

3.
COMMENT ON TABLE DEPARTMENT IS
 'Company department information including name, code, and location'
;

SELECT COMMENTS
FROM USER_TAB_COMMENTS
WHERE TABLE_NAME  = 'DEPARTMENTS'
;

4.
SELECT *
FROM USER_SYNONYMS
;

5.
SET LONG 2000
SELECT VIEW_NAME
, TEXT
FROM USER_VIES
;

6.
SELECT SEQUENCE_NAME
, MAX_VALUE
, INCREMENT_BY
, LAST_NUMBER
FROM USER_SEQUENCES