Structure of Data Dictionary Views with SQL

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.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Facebook
Twitter
LinkedIn
WhatsApp
Email
Print