12 Managing Objects with Data Dictionary Views

What is the Oracle data dictionary?

A collection of tables and views automatically built and maintained by Oracle that stores metadata about all database objects. Who created them, when, etc.

Which data dictionary views store information about granted system privileges?

• USER_SYS_PRIVS - to current user

• DBA_SYS_PRIVS - to users and roles

• ROLE_SYS_PRIVS - to roles

Which data dictionary views store information about granted roles?

• USER_ROLE_PRIVS - to current user

• DBA_ROLE_PRIVS - to users and roles

Which data dictionary views store information about grants on objects?

• USER_TAB_PRIVS - Granted privileges on objects for which the user is the owner, grantor, or grantee

• DBA_TAB_PRIVS - All grants on objects in the database

• ROLE_TAB_PRIVS - Table privileges granted to roles

Which data dictionary view stores information about session privileges that the user currently has set?

SESSION_PRIVS

Where will user’s added comments to the table be stored in the data dictionary view?

In USER_TAB_COMMENTS, ALL_TAB_COMMENTS, and DBA_TAB_COMMENTS

Where will user’s added comments a column in a table be stored in the data dictionary view?

USER_COL_COMMENTS, ALL_COL_COMMENTS, and DBA_COL_COMMENTS

What is the syntax for adding a comment?

COMMENT ON objectType fullObjectName is c1;

Where objectType - TABLE, COLUMN, INDEXTYPE, OPERATOR, MATERIALIZED VIEW, and others

If it’s a table then name the TABLE_NAME

If it’s a column then TABLE_NAME.COLUMN_NAME

What are the views that store comments for all objects and columns of all tables and views in the database?

ALL_TAB_COMMENTS, ALL_COL_COMMENTS

What are the other USER_ views?

• USER_IND_COLUMNS - index columns of the user’s tables owned by the current user

• USER_SEQUENCES

• USER SYNONYMS - private synonyms owned by the current user (public synonyms are displayed in ALL_SYNONYMS and DBA_SYNONYMS)

• USER_TAB_COLUMNS - columns in user’s own tables and views

• USER_TAB_PRIVS - grants on objects owned by the current user

• and USER_VIEWS

What does USER_DEPENDENCIES show?

Dependencies to and from a user’s objects owned by the current user

What does USER_ERRORS show?

Current errors on stored objects owned by the current user

What does USER_INDEXES show?

Indexes owned by the current user

What does USER_COL_PRIVS show?

Grants on columns of tables owned by the current user

What does USER_CONS_COLUMNS show?

Accessible columns in constraint definitions for tables owned by the current user

What does the V_$ (for views), V$ (for public synonyms) prefix indicate in a data dictionary view?

Information about dynamic performance views, each of which has a public synonym counterpart. Stores information about the local database instance.

What does the GV_$ (for views), GV$ (for public synonyms) prefix indicate in a data dictionary view?

Global dynamic performance views.

What are the other data dictionary view prefixes?

eg. SM$, AUDIT_, CHANGE_, TABLE_, GLOBAL_, DICT_, NLS_, SESSION_, ROLE_, RESOURCE_, DATABASE_, DBMS_, COLUMN_, or no prefix, etc.

What is allowed to users when interacting with meta data?

To comment them

Is there any abstraction to the meta data?

Yes, many data dictionary objects are renamed via public synonyms. There are multiple levels of abstraction.

What do data dictionary store?

• Names of DB objects, their owners, when they were created

• Names of each table’s columns, data types, precision and scale

• Any constraints

• Views, indexes, and sequences

• And more

What is a SYS account?

It is a super user with master privileges to everything in the database. Mostly only DBA has access to the SYS account.

What does metadata mean in the context of Oracle?

It means 'data about data'; information describing database objects such as tables, views, indexes, constraints, sequences, etc. The result of each DDL statement is recorded.

Who owns the data dictionary base tables?

The SYS user account. No user should ever alter data owned by SYS or else the integrity of database may be compromised.

Can users directly modify data dictionary tables?

No, users cannot modify them; Oracle updates them automatically after DDL operations.

What is the purpose of data dictionary views?

They provide read-only access to metadata stored in the data dictionary.

What does the USER_ prefix indicate in a data dictionary view?

Information about objects owned by the current user.

What does the ALL_ prefix indicate in a data dictionary view?

Information about objects accessible to the current user, regardless of owner.

What does the DBA_ prefix indicate in a data dictionary view?

Information about all objects in the database, regardless of owner or privileges.

Why does USER_TABLES not include an OWNER column?

Because all rows refer to the current user's objects, so OWNER is implicit.

What are dynamic performance views?

Views beginning with V$ or GV$ that provide real-time information about database activity.

What is the public synonym prefix for dynamic performance views?

V$

What is the recommendation for querying V$ views?

Limit to simple queries; for complex joins, copy data into temporary tables first then perform joins on the temporary tables because of lack of read consistency.

What does V$DATABASE contain?

It’s a dynamic performance synonym that stores database name, the creation date, OS platform, etc.

What does V$INSTANCE contain?

It’s a dynamic performance synonym that stores instance name, host name, startup time, etc.

What does V$PARAMETER contain?

Dynamic performance synonym that stores the current system parameter settings such as NLS_LANGUAGE, NLS_DATE_LANGUAGE, SQL_TRACE, NLS_CURRENCY, etc

What does V$SESSION show?

Many current settings for each individual user session such as login times, machine names, and transaction states.

What does V$RESERVED_WORDS list?

Reserved SQL keywords and whether they are always or conditionally reserved.

What does V$OBJECT_USAGE monitor?

Usage of index objects.

What does V$TIMEZONE_NAMES contain?

Includes two columns: TZNAME - Time zone region name and TZABBREV - abbreviations.

What does the DICTIONARY view contain?

A list of all data dictionary views with their names and descriptions.

How many entries are typically in the DICTIONARY view?

Thousands (e.g., 3,228 in one sample installation).

How can you search DICTIONARY for views about indexes?

By running SELECT * FROM DICTIONARY WHERE COMMENTS LIKE '%index%';

What does USER_CATALOG show?

A summary of tables, views, synonyms, and sequences owned by the user.

There are two columns: TABLE_NAME and TABLE_TYPE

What is the synonym for USER_CATALOG?

CAT.

What does USER_OBJECTS show?

Information about all objects owned by the user.

What is the synonym for USER_OBJECTS?

OBJ.

What does USER_TABLES show?

Metadata about tables owned by the current user (e.g., table name, status, row movement, avg row length, owner).

What does USER_TAB_COLUMNS show?

Metadata about columns of tables owned by the user (name, data type, precision, scale, length).

What are synonyms for USER_TABLES and USER_TAB_COLUMNS?

TABS and COLS respectively.

What statement allows users to add comments to tables or columns?

COMMENT statement.

What are the views for table comments?

USER_TAB_COMMENTS, ALL_TAB_COMMENTS, DBA_TAB_COMMENTS.

What are the views for column comments?

USER_COL_COMMENTS, ALL_COL_COMMENTS, DBA_COL_COMMENTS.

How can you remove a comment?

By setting the comment to an empty string with COMMENT ON … IS '';

How can you check if a view is invalid?

Query the STATUS column in USER_OBJECTS.

Where can you find the SQL text of a view?

In the USER_VIEWS view, in the TEXT column.

What happens if a column used by a view is dropped from its base table?

The view status becomes INVALID.

What does USER_SYS_PRIVS show?

System privileges granted to the current user.

What does USER_TAB_PRIVS show?

Privileges on objects where the user is the owner, grantor, or grantee.

What does USER_ROLE_PRIVS show?

Roles granted to the current user.

What does DBA_SYS_PRIVS show?

System privileges granted to all users and roles.

What does DBA_TAB_PRIVS show?

All grants on objects in the database.

What does DBA_ROLE_PRIVS show?

Roles granted to users and roles.

What does ROLE_SYS_PRIVS show?

System privileges granted to roles.

What does ROLE_TAB_PRIVS show?

Table privileges granted to roles.

What does SESSION_PRIVS show?

Privileges that the user currently has in the session.

What does USER_CONSTRAINTS show?

Information about constraints on tables owned by the user.

What does CONSTRAINT_TYPE = P mean?

PRIMARY KEY constraint.

What does CONSTRAINT_TYPE = R mean?

FOREIGN KEY constraint (referential integrity).

What does CONSTRAINT_TYPE = U mean?

UNIQUE constraint.

What does CONSTRAINT_TYPE = C mean?

CHECK constraint or NOT NULL constraint.

Which column shows ON DELETE rules?

DELETE_RULE column.

Which column shows CHECK constraint conditions?

SEARCH_CONDITION column.

What does USER_CONS_COLUMNS show?

Columns involved in constraints and referenced tables/columns for foreign keys.

How can you find all tables with a specific column name?

Query ALL_TAB_COLUMNS or USER_TAB_COLUMNS with WHERE COLUMN_NAME = 'X'.

Which view lists all dictionary views with descriptions?

DICTIONARY.

Which view lists sequences accessible to the current user, regardless of owner?

ALL_SEQUENCES.

Who owns the data dictionary?

SYS.

Which objects can you comment on with COMMENT statement (exam relevant)?

TABLE and COLUMN.

Which view contains info about columns in all tables in the database?

DBATABCOLUMNS.

Which abbreviation in USER_CONSTRAINTS indicates FOREIGN KEY?

R.

What does metadata mean?

Data about data.

What happens to a view’s status if a column used by the view is dropped?

It becomes INVALID.