Oracle System Administrator basics: Oracle Data Dictionary

Oracle System Administrator basics: Oracle Data Dictionary for Oracle system administrators, Oracle Data Dictionary is like a treasure house: it is an important part of ORACLE Database, provides information about databases, such as database structure, database object space allocation, and database users. This article attempts to start from several basic concepts of the database management system and introduce the ORACLE Data dictionary to understand the ORACLE method. Different operating systems and versions of ORACLE data dictionaries are different. This document uses the UNIX operating system and ORACLE 7 as examples. Database System instances and database identifiers the ORACLE database system includes two aspects: ORACLE Database and ORACLE instance. users access the ORACLE database through ORACLE instances. 1. a database is a collection of data as a whole. It is usually created at the final stage of installing the ORACLE software and identified by the DATABASE name (1 ~ (8 characters). The database name is confirmed when the database is created and saved in the control file. 2. An INSTANCE is a software mechanism for accessing and controlling databases. It consists of the System Global Area (SGA) and the ORACLE process. Like the database name, the instance must be uniquely identified and confirmed when the ORACLE software is installed. We can use the following methods to understand the instance identity. Some System Administrators intentionally or unintentionally retrieve the same identifier for the database name and instance id. Of course, this is acceptable, and sometimes it may bring some convenience. However, I think it is best to retrieve different identifiers. You must read an initialization parameter file during database creation and operation. It is a text file and can be edited by a general editing program. Each database must have at least one initialization parameter file named INIT. ORA, in UNIX systems, according to the default rules, the naming principle of its initialization parameter file name is INIT followed by the instance id, plus the ORA suffix, for example, an instance id is TEST, the initialization parameter file name is INITTEST. ORA. There are more than 100 database initialization parameters. The DB_NAME mentioned above is one of them. In addition, there are many other parameters, for example, the CONTROL-files control file name ROLLBACK-SEGMENTS is assigned to the instance's ROLLBACK segment name the physical structure of the database for the maximum number of PROCESSES in the PROCESSES multi-process system the command file that is executed when the INIT-SQL-FILES database is established the structure is for the operating system, it describes the storage format of data in the database. Physically, the oa cle database files include data files, log files, and control files. 1. data files in ORACLE databases are logically stored in tablespaces, but physically stored in data files. data files have the following features, that is, each data file is associated with only one database. Once a database file is created, the size cannot be changed. A tablespace can contain one or more data files. We can use the following methods to understand all the data files in the database. (1) SQLDBA> SELECT * FROM DBA-DATA-FILES; In the result, the column name file-NAME is the data file name. (2) SQLDBA> SELECT * from v $ DATAFILE; In the result, the column NAME is the data file NAME. (3) SQLDBA> SELECT * from v $ DBFILE; In the result, the column NAME is the data file NAME. 2. The redo log files are used to record all changes made to the database for recovery in case of system failure. Each database has at least two log files. 3. control files the CONTROL file is a small binary file, but it is very important. If the control file is damaged, the database cannot be operated. To prevent control files from being damaged, generally, a database should have at least two control files on different disks. The name of the control file is recorded in the CONTROL_FILES parameter. The logical structure of a database is user-oriented. The logical structure of a database includes tablespaces, segments, ranges, data blocks, and schema objects. Logically, databases have the following features. A database is composed of one or more tablespaces. A TABLESPACE is composed of segments and serves as the system tablespace. It consists of multiple segments, such as the auto-lifting segment, rollback segment, data segment, and temporary segment, rather than the system tablespace, different segments are used for different purposes. A segment is composed of a group of ranges. A range (EXTENT) is composed of a group of continuous data blocks. A data BLOCK corresponds to one or more physical blocks on the disk. In the logical structure of the database, tablespaces and rollback segments are important. 1. Each database must contain a tablespace named SYSTEM. This tablespace is automatically created by the SYSTEM when a database is created. To ensure normal operation of the database, the SYSTEM tablespace must be online. To enhance the control and maintenance of the database, a database generally contains multiple tablespaces. Using multiple tablespaces has many advantages. For example, you can separate user data from the data dictionary and store data files of different tablespaces on different disks to reduce I/O conflicts, it can also make some tablespaces online, while other tablespaces offline. 2. rollback segments each tablespace is composed of segments. The segments in the ORACLE database include the data segment, index segment, temporary segment, rollback segment, and self-lifting segment. More importantly, rollback segments Record Database Change information to achieve database read consistency and recovery. There is a SYSTEM rollback segment in the SYSTEM tablespace, which is generated when the database is created. If multiple tablespaces are used, there should be at least one other rollback segment. Rollback segments are classified into private segments and public segments. Dedicated rollback segments must be used, you need to write the segment name of the dedicated ROLLBACK segment on the ROLLBACK-SEGMENTS parameter of the initialization parameter file and restart the database, or use the online command of the ROLLBACK segment to make it online. Database user ORACLE database is a multi-user system. To ensure the security of the database system, the ORACLE database management system has a sound security mechanism. For example, each ORACLE database has a user table that records information about each user. Once a user enters the system, the ORACLE system checks the validity of the user. Another example is that the ORACLE system manages users by reasonably assigning user permissions. Generally, ORACLE divides users into three types: DBA, RESOUCE, and CONNECT, so that different users have different permissions. When creating a database, the SYSTEM automatically creates two users, namely SYS and SYSTEM users, and grants the database permissions to these two users. Since DBAs have the highest permissions, we recommend that you modify their passwords in a timely manner, this prevents individual users from entering the system as DBAs and intentionally or unintentionally causing damage to the system. All users except SYS and SYSTEM are created one by one. The method for creating a USER is as follows: assume that the USER name to be created is RSXT and the password is RSPASS; the default tablespace is USER, the temporary tablespace is TEMP, And the tablespace quota is 3 mb. By default, you can use the following method to create a new user: SQLDBA> create user rsxt identified by rspass default tablespace user temporary tablespace temp quota 3 m on user; note that the USER must be authorized in a timely manner after the USER is created; otherwise, users just created as above, you cannot even connect to the database. For general users, the CONNECT and RESOURCE roles are usually granted with the following command: SQLDBA> grant connect, resource to rsxt; for a database system with many users, we can use the following methods to understand the database user situation. 1. for non-dba users, there are two Commands: (1) SQL> SELECT * from all-USERS; (2) SQL> SELECT * FROM USER-USERS; the USER-USERS table shows the USER's default tablespace and temporary tablespace. 2. for dba users, there are three commands: (1) SQLDBA> SELECT * from all-USERS; (2) SQLDBA> SELECT * from user-USERS; (3) SQLDBA> SELECT * FROM DBA-USERS; where, FROM the DBA-USERS, you can learn the details of all users, so this command is useful for database administrators. For non-DBA users, since DBA users have the highest permissions, for security reasons, only the Oracle system administrator can grant DBA permissions. Most users are non-DBA users. Although there are fewer permissions, you can still get a lot of useful information about non-DBA users from the ORACLE Data dictionary. We can use the following methods to learn about non-DBA users. 1. SQL> SELECT * FROM USER-USERS; you can view the USER's default tablespace, temporary tablespace, and USER Creation Time. 2. SQL> SELECT * FROM USER-TABLES; you can view the details of all the TABLES created by the USER. Due to the large number of column names, several TABLES cannot be viewed on the screen. If you only want to view the table name created by the USER, run the following command: SQL> select table-name from user-TABLES; 3. SQL> SELECT * FROM USER-VIEWS; you can view the view created by the user, including the text of the created view. Similarly, if you only want to VIEW the view name created by the USER, you can use the following command: SQL> select view-name from user-VIEWS; 4. SQL> SELECT * FROM USER-TABLESPACES; you can view information about the tablespaces that the user can access. 5. SQL> SELECT TABLESPACE-NAME, SUM (BYTES), SUM (BLOCKS) FROM USER-FREE-SPACE GROUP BY TABLESPACE-NAME; you can view the SPACE remaining for the tablespace that the USER can access. 6. SQL> SELECT * FROM USER-TS-QUOTAS; you can view the table space shares of that user. 7. SQL> SELECT * FROM USER-ROLE-PRIVS; you can view the role that the user is granted. 8. SQL> SELECT * FROM USER-SYS-PRIVS; can view the user's system permissions and can be granted to other users. 9. SQL> SELECT * FROM USER-TAB-PRIVS-RECD; you can view the object permissions that the user can access other users, such as tables, views. 10. SQL> SELECT * FROM USER-TAB-PRIVS-MADE; you can view the object permissions that the user grants to other users, such as tables and views.