Oracle Magazine, July/August 2017
Database Application Developer and DBA BEYOND SQL 101 Storing statistics about a table statistics that will be used by Oracle Optimizer Checking whether a column is indexed Examining which constraints exist and whether they are enabled Determining the validity of table and column names during SQL statement parsing Retrieving the privileges of a particular user The data dictionary includes both dynamic and static sets of tables and views The dynamic views are used primarily to examine database performance The static views store details about database objects such as tables indexes and views and are generally categorized into one of three prefixed categories USER_ ALL_ and DBA_ Whenever a new database object is created or altered metadata about the object is recorded in a static data dictionary view Static views can also be used by Oracle Database to retrieve information about indexed columns and referential integrity constraints The USER_ views display information about objects belonging to the currently logged in user The ALL_ views return the same information as the USER_ views but they also display information about any objects granted to the currently logged in user by another user and display any public objects such as public synonyms You will learn about synonyms and how to grant receive and revoke access privileges and roles in subsequent articles in this series The DBA_ views provide information about every object in the database To query any of the DBA_ views you need to have special database administrator privileges such as the DBA role or the SELECT ANY DICTIONARY as in data dictionary privilege granted to you Consider the query in Listing 1 Code Listing 1 Query the USER_ TABLES static data dictionary view SQL set lines 10000 SQL select table_ name num_ rows to_ char last_ analyzed ORACLE MAGAZINE JULY AUGUST 2017 103
You must have JavaScript enabled to view digital editions.