Oracle Learning Notes-data dictionary and Common Commands (reprint)

Reprinted from:

Summary of Oracle Common data dictionaries and SQL statements

Oracle Common Command Encyclopedia (useful, take notes)

I. Oracle DATA dictionary

A data dictionary is where Oracle stores information about a database and uses it to describe the data. For example, the creator information of a table, create time information, the table space information, user access rights information, and so on. When users encounter difficulties in manipulating data in the database, they can access the data dictionary to view detailed information.
Data dictionaries in Oracle have static and dynamic points. The static data dictionary is mostly not changed when the user accesses the data dictionary, but the Dynamic Data dictionary is dependent on the performance of the database and reflects some intrinsic information of the database running, so it is often not invariable to access such data dictionary. These two types of data dictionaries are discussed below.

1. Static Data dictionary

This type of data dictionary consists primarily of tables and views, and it should be noted that tables in the data dictionary are not directly accessible, but can access the views in the data dictionary. Views in a static data dictionary are divided into three categories, each of which is made up of three prefixes: user_*, all_*, dba_*.

• User_*: This view stores information about the objects that are owned by the current user. (That is, all objects in this user mode)
• All_*: This attempts to store information about the objects that the current user can access. (all_* does not need to have the object as compared to user_*, only the right to access the object)
• Dba_*: This view stores information about all objects in the database. (If the current user has permission to access these databases, you must generally have Administrator privileges)

As can be seen from the above description, the data stored between the three will certainly overlap, in fact, they are in addition to the scope of access (because the permissions are different, so the scope of access to the object is not the same), others have consistency. Specifically, because the data dictionary view is owned by the SYS (System user), in the case of a state, only SYS and users with DBA system privileges can see all views. Users who do not have DBA authority can only see user_* and all_*. If the relevant SELECT permission is not granted, they cannot see the dba_* view.
Because of the similarity of the three, the following example introduces several common static views in User_:
User_users View:

Mainly describes the current user's information, including the current user name, account ID, account status, tablespace name, creation time, and so on. For example, you can return this information by executing the following command.

Select *  from User_users

User_tables View:

This paper mainly describes the information of all the tables owned by the current user, including the table name, table space name, cluster name and so on. This view provides a clear understanding of what tables the current user can manipulate. The Execute command is: SELECT * from User_tables

User_objects View:
It mainly describes the information of all objects owned by the current user, including tables, views, stored procedures, triggers, packages, indexes, sequences, and so on. This view is more comprehensive than the User_tables view. For example, to obtain information about an object type named "Package1" and its status, you can execute the following command:

Select Object_type,status  from user_objects where object_name = Upper (' Package1 ');

Note: The use of upper, all objects in the data dictionary are uppercase, and PL/SQL is not case sensitive, so be sure to pay attention to case matching in practice.

User_tab_privs View:
The view is primarily to store permission information for all tables under the current user. For example, to understand the current user's permissions to table1, you can execute the following command:

Select *  from WHERE table_name=Upper('table1')

Knowing the current user's permissions on the table gives you a clear idea of what actions can be performed and what actions cannot be performed.

The previous view is User_, but the All_ beginning is exactly the same, but the information listed is the object that the current user can access instead of the current user. For Dba_ to start with administrator privileges, other uses are exactly the same, here is not to repeat.

2. Dynamic Data dictionary

Oracle contains a number of potential tables and views that are maintained by system administrators such as SYS, which are called Dynamic Data dictionaries (or dynamic performance views) because they are constantly updated when the database is running. These views provide information about memory and disk operations, so we can only read access to them and not modify them.
These dynamic performance views in Oracle are views that start with v$, such as v$access. Here are a few of the main dynamic performance views.

• v$access: This view displays the database objects that are locked in the database and the session objects that access them (the sessions object).
• V$session: This view lists the details of the current session. Because the view field is large, the detail field is not listed here, for more information, you can type it directly under the Sql*plus command line: Desc v$session.

• V$active_instance: This view primarily describes the information for an instance of an activity under the current database. You can still use the SELECT statement to observe this information.
• V$context: This view lists the properties information for the current session. such as namespaces, property values, and so on.
  
  The above is the basic content of Oracle data dictionary, the use of good data dictionary technology, you can enable database developers to better understand the full picture of the database, so that the database optimization, management, etc. have a great help.

Ii. Common Commands1. User

View the default tablespace for the current user

Select  from User_users;

View the role of the current user

Select *  from

View system and table-level permissions for the current user

Select *  from  Select* from

2. Table

View all the tables under the user

Select *  from

To view the creation time of a table

Select object_name  from where object_name = Upper ('&table_name'

View the size of a table

Select sum (bytes) / (1024x768*1024x768 as from user_segments                 where Segment_name=Upper('&table_name');

View tables placed in Oracle's memory area

Select  from where InStr (Cache,'Y')>0;

3. Index

View index number and category

Select  from Order  by

View the fields indexed by the index

Select *  from where index_name=Upper('&index_name'

To view the size of an index

Select sum (bytes) / (1024x768*1024x768 as from user_segments                 where Segment_name=Upper('&index_name' )

4. Serial number

View serial number, Last_number is the current value

Select *  from

5. View

View the name of the view

Select  from User_views;

6. Stored functions and procedures

View the status of functions and procedures

Select object_name  from where object_type='FUNCTION'Selectobject_ Namefromwhere object_type='PROCEDURE'

View source code for functions and procedures

Select text  from where owner=user and name=Upper('&plsql_ Name'

7. Table Space

To view the name and size of a tablespace

Select round (sum(bytes/(1024x768)),0) ts_size       from dba_tablespaces T, dba_data_files D      where = d.tablespace_name      Group  by

To view the name and size of a table space physical file

Select file_id file_name ,      round (Bytes/(1024x768*1024x768),0) total_space       from dba_data_files      Order  by Tablespace_name;

To view the usage of a tablespace

Select sum (bytes) / (1024x768*1024x768 as free_space,tablespace_name     from  Dba_ Free_space     Group by

Oracle Learning Notes-data dictionary and Common Commands (reprint)