Structure of Data Dictionary Views
Views on Tables
ALL_TABLES
Structure:
- OWNER: The schema (user) that owns the table.
- TABLE_NAME: The name of the table.
- TABLESPACE_NAME: The tablespace in which the table is stored.
- CLUSTER_NAME: Name of the cluster if the table is part of a cluster (may be NULL).
- IOT_NAME: Name of the index-organized table (if applicable).
Example Query:
-- List details of all tables accessible by the current user SELECT owner, table_name, tablespace_name, cluster_name, iot_name FROM all_tables;
Description:
- OWNER: The schema where the table resides. Important for determining access and ownership.
- TABLE_NAME: The actual name of the table used in queries.
- TABLESPACE_NAME: The storage location for the table’s data. Helps in understanding where the data is physically stored.
- CLUSTER_NAME: Shows if the table is part of a cluster (used for performance optimization by grouping tables).
- IOT_NAME: Displays the name of the index-organized table if the table is organized by its primary key index.
USER_TABLES
Structure:
- TABLE_NAME: The name of the table.
- TABLESPACE_NAME: The tablespace where the table is stored.
Example Query:
-- List tables owned by the current user SELECT table_name, tablespace_name FROM user_tables;
Description:
- TABLE_NAME: The name of tables owned by the current user.
- TABLESPACE_NAME: Indicates where the table’s data is stored, helping manage storage resources.
DBA_TABLES
Structure:
- OWNER: The schema that owns the table.
- TABLE_NAME: The name of the table.
- TABLESPACE_NAME: The tablespace where the table is stored.
- CLUSTER_NAME: The name of the cluster if the table is part of one.
- IOT_NAME: The name of the index-organized table (if applicable).
Example Query:
-- List all tables in the database SELECT owner, table_name, tablespace_name, cluster_name, iot_name FROM dba_tables;
Description:
- Provides a comprehensive view of all tables in the database, including those owned by different users.
Views on Columns
ALL_TAB_COLUMNS
Structure:
- OWNER: The schema (user) that owns the table.
- TABLE_NAME: The name of the table containing the column.
- COLUMN_NAME: The name of the column.
- DATA_TYPE: The data type of the column (e.g., VARCHAR2, NUMBER).
- DATA_LENGTH: The length of the column’s data type.
- NULLABLE: Indicates if the column allows NULL values.
- COLUMN_ID: The position of the column in the table.
Example Query:
-- List column details for the table 'EMPLOYEES' SELECT owner, table_name, column_name, data_type, data_length, nullable, column_id FROM all_tab_columns WHERE table_name = 'EMPLOYEES';
Description:
- COLUMN_NAME: The actual name of the column.
- DATA_TYPE: Defines the type of data the column can hold, such as string, number, or date.
- DATA_LENGTH: Specifies the maximum length of the column’s data.
- NULLABLE: Shows whether the column can accept NULL values.
- COLUMN_ID: Indicates the column’s position in the table, important for understanding the order of columns.
USER_TAB_COLUMNS
Structure:
- TABLE_NAME: The name of the table.
- COLUMN_NAME: The name of the column.
- DATA_TYPE: The data type of the column.
- DATA_LENGTH: The length of the column’s data type.
- NULLABLE: Indicates if the column allows NULL values.
- COLUMN_ID: The position of the column in the table.
Example Query:
-- List columns for tables owned by the current user SELECT table_name, column_name, data_type, data_length, nullable, column_id FROM user_tab_columns;
Description:
- Focuses on columns in tables owned by the current user, showing detailed information relevant to schema design and management.
DBA_TAB_COLUMNS
Structure:
- OWNER: The schema that owns the table.
- TABLE_NAME: The name of the table.
- COLUMN_NAME: The name of the column.
- DATA_TYPE: The data type of the column.
- DATA_LENGTH: The length of the column’s data type.
- NULLABLE: Indicates if the column allows NULL values.
- COLUMN_ID: The position of the column in the table.
Example Query:
-- List all columns in the database SELECT owner, table_name, column_name, data_type, data_length, nullable, column_id FROM dba_tab_columns;
Description:
- Provides a complete view of columns in all tables across the database, which is useful for comprehensive schema analysis.
Views on Indexes
ALL_INDEXES
Structure:
- OWNER: The schema that owns the index.
- INDEX_NAME: The name of the index.
- TABLE_NAME: The name of the table being indexed.
- UNIQUENESS: Indicates if the index is unique (UNIQUE) or non-unique (NONUNIQUE).
- INDEX_TYPE: The type of index (e.g., NORMAL, BITMAP).
Example Query:
-- List indexes on the table 'EMPLOYEES' SELECT owner, index_name, table_name, uniqueness, index_type FROM all_indexes WHERE table_name = 'EMPLOYEES';
Description:
- INDEX_NAME: The name of the index, used to identify the index in queries.
- INDEX_TYPE: Specifies the type of index, which affects performance and storage.
USER_INDEXES
Structure:
- INDEX_NAME: The name of the index.
- TABLE_NAME: The name of the table being indexed.
- UNIQUENESS: Indicates if the index is unique.
- INDEX_TYPE: The type of index.
Example Query:
-- List indexes on tables owned by the current user SELECT index_name, table_name, uniqueness, index_type FROM user_indexes;
Description:
- Shows details for indexes on tables owned by the current user, useful for optimizing query performance and understanding indexing strategies.
DBA_INDEXES
Structure:
- OWNER: The schema that owns the index.
- INDEX_NAME: The name of the index.
- TABLE_NAME: The name of the table being indexed.
- UNIQUENESS: Indicates if the index is unique.
- INDEX_TYPE: The type of index.
Example Query:
-- List all indexes in the database SELECT owner, index_name, table_name, uniqueness, index_type FROM dba_indexes;
Description:
- Provides a comprehensive view of all indexes in the database, useful for database-wide performance tuning and analysis.
Views on Constraints
ALL_CONSTRAINTS
Structure:
- OWNER: The schema that owns the constraint.
- CONSTRAINT_NAME: The name of the constraint.
- TABLE_NAME: The name of the table associated with the constraint.
- CONSTRAINT_TYPE: The type of constraint (e.g., C for CHECK, P for PRIMARY KEY, F for FOREIGN KEY, U for UNIQUE).
Example Query:
-- List constraints on the table 'EMPLOYEES' SELECT owner, constraint_name, constraint_type, table_name FROM all_constraints WHERE table_name = 'EMPLOYEES';
Description:
- CONSTRAINT_TYPE: Defines the type of constraint applied, crucial for understanding the rules enforced on the table’s data.
USER_CONSTRAINTS
Structure:
- CONSTRAINT_NAME: The name of the constraint.
- TABLE_NAME: The name of the table associated with the constraint.
- CONSTRAINT_TYPE: The type of constraint.
Example Query:
-- List constraints on tables owned by the current user SELECT constraint_name, constraint_type, table_name FROM user_constraints;
Description:
- Provides information on constraints for tables owned by the current user, important for managing data integrity.
DBA_CONSTRAINTS
Structure:
- OWNER: The schema that owns the constraint.
- CONSTRAINT_NAME: The name of the constraint.
- TABLE_NAME: The name of the table associated with the constraint.
- CONSTRAINT_TYPE: The type of constraint.
Example Query:
-- List all constraints in the database SELECT owner, constraint_name, constraint_type, table_name FROM dba_constraints;
Description:
- Offers a complete view of all constraints in the database, useful for database-wide integrity checks and design.
Views on Users
ALL_USERS
Structure:
- USERNAME: The name of the user.
- USER_ID: The user ID.
- CREATED: The creation date of the user.
Example Query:
-- List all users accessible by the current user SELECT username, user_id, created FROM all_users;
Description:
- USERNAME: The name of each user, important for managing user permissions.
- CREATED: Helps in tracking when users were added to the database.
DBA_USERS
Structure:
- USERNAME: The name of the user.
- USER_ID: The user ID.
- CREATED: The creation date of the user.
Example Query:
-- List all users in the database SELECT username, user_id, created FROM dba_users;
Description:
- Provides a comprehensive list of all users in the database, which is essential for overall user management and security audits.
Views on Privileges
ALL_TAB_PRIVS
Structure:
- OWNER: The schema that owns the table.
- TABLE_NAME: The name of the table.
- GRANTEE: The user or role that has been granted the privilege.
- PRIVILEGE: The type of privilege granted (e.g., SELECT, INSERT, UPDATE).
Example Query:
-- List privileges on the table 'EMPLOYEES' SELECT owner, table_name, grantee, privilege FROM all_tab_privs WHERE table_name = 'EMPLOYEES';
Description:
- GRANTEE: Identifies who has been given the specified privilege.
- PRIVILEGE: Specifies the type of access or action allowed on the table.
USER_TAB_PRIVS
Structure:
- TABLE_NAME: The name of the table.
- GRANTEE: The user or role that has been granted the privilege.
- PRIVILEGE: The type of privilege granted.
Example Query:
-- List privileges on tables owned by the current user SELECT table_name, grantee, privilege FROM user_tab_privs;
Description:
- Focuses on privileges related to tables owned by the current user, which helps in managing permissions effectively.
DBA_TAB_PRIVS
Structure:
- OWNER: The schema that owns the table.
- TABLE_NAME: The name of the table.
- GRANTEE: The user or role that has been granted the privilege.
- PRIVILEGE: The type of privilege granted.
Example Query:
-- List all privileges on the table 'EMPLOYEES' in the database SELECT owner, table_name, grantee, privilege FROM dba_tab_privs WHERE table_name = 'EMPLOYEES';
Description:
- Provides a complete view of all privileges on tables across the database, essential for managing and auditing access control.
Conclusion
Understanding the structure of data dictionary views allows for efficient querying and management of database objects. Each view provides a specific set of information crucial for database administration, performance tuning, and security management. Mastery of these views enhances your ability to manage and analyze database metadata effectively.