System Views in Oracle

 

In Oracle Database, the concepts of "system view" typically refer to different types of views that provide access to metadata and information about the database objects. Let's delve into each of these concepts:

System Views:

System views are also known as data dictionary views or catalog views. These views are part of the Oracle data dictionary, which is a set of tables and views that store metadata about the database. System views provide information about database objects, user accounts, privileges, and various aspects of the database configuration.

Examples of system views include:

DBA_TABLES: Provides information about all tables in the database.

In Oracle Database, the DBA_TABLES view is a system view that belongs to the data dictionary. Data dictionary views, often referred to as system views or catalog views, store metadata information about the database objects. Specifically, DBA_TABLES provides information about tables in the database.

Here are some of the key columns in the DBA_TABLES view:

OWNER: The owner or schema to which the table belongs.

TABLE_NAME: The name of the table.

TABLESPACE_NAME: The tablespace in which the table is stored.

CLUSTER_NAME: The name of the cluster to which the table belongs (if any).

IOT_NAME: The name of the Index-Organized Table (IOT) if the table is an IOT.

LAST_ANALYZED: The timestamp of the last time the table was analyzed.

NUM_ROWS: The approximate number of rows in the table.

You can query the DBA_TABLES view to retrieve information about all tables in the database or filter the results based on specific criteria, such as the owner or table name.

DBA_USERS: Contains information about all users in the database.

In Oracle Database, the DBA_USERS view is a system view that belongs to the data dictionary. Data dictionary views, also known as system views or catalog views, store metadata information about various aspects of the database. Specifically, the DBA_USERS view provides information about database users.

Here are some of the key columns in the DBA_USERS view:

USERNAME: The name of the user.

USER_ID: A unique identifier for the user.

CREATED: The timestamp indicating when the user was created.

DEFAULT_TABLESPACE: The default tablespace for the user.

TEMPORARY_TABLESPACE: The default temporary tablespace for the user.

PROFILE: The name of the security profile assigned to the user.

ACCOUNT_STATUS: The status of the user's account (e.g., OPEN, LOCKED).

You can query the DBA_USERS view to retrieve information about all users in the database or filter the results based on specific criteria.

Select username, account_status, last_login from dba_users where username =’TARIF’;

DBA_VIEWS: Contains information about all views in the database.

In Oracle Database, the DBA_VIEWS view is a system view that belongs to the data dictionary. Data dictionary views, often referred to as system views or catalog views, store metadata information about various database objects. Specifically, the DBA_VIEWS view provides information about views in the database.

Here are some of the key columns in the DBA_VIEWS view:

OWNER: The owner or schema to which the view belongs.

VIEW_NAME: The name of the view.

TEXT_LENGTH: The length of the text defining the view.

TEXT: The text of the SELECT statement that defines the view.

TYPE_TEXT_LENGTH: The length of the type text (usually 'VIEW').

TYPE_TEXT: The type text (usually 'VIEW').

You can query the DBA_VIEWS view to retrieve information about all views in the database or filter the results based on specific criteria.

To query these views, you typically need elevated privileges such as DBA (Database Administrator) or select access on specific views