Oracle physical structure and logical structure

oracle physical structure and logical structure physical structure to view the physical file path of the oracle database must be viewed with a command, unless the database is installed by you and done all the security configuration, it is very likely that your ex has made changes to the database, And accidentally made a big mistake.
 

View the control file DESC v $ controlfileView the status and name of the control file (display path) SELECT status, name FROM v $ controlfile;
 

View the data file DESC v $ datafile For example, view the file number and status of the data file SELECT file #, status, name FROM v $ datafile;
 

Look at the log file DESC v $ logfile to view the log file path information SELECT member FROM v $ logfile;
 

Oracle's file system: control file (.CTL), data file (.DBF), log file (.LOG)
 

These three file systems are generally found in the following path: (default installation path) / u01 / app / oracle / oradata / oracleSID
 

In addition to these three files, there is also a parameter file. The parameter file is not an effective part of the database system. When the database is started, the parameter file does not directly participate in the work, but the control file is found by the parameter file. Parameter file location: /u01/app/oracle/product/10.2.0/db_1/dbs/spfileorac leSID.ora The content of the control file will be displayed in the parameter file. The function of the parameter file is to find the control file.
 

There are two types of log files in Oracle, one is the online log file (redo log file), and the other is the archive log file. REDO01.LOG and REDO02.LOG, REDO03.LOG are typical online log files, which are characterized by writing files sequentially, writing one after they are full, writing the third one when they are full, and overwriting them without making a backup. Archiving log files is an archiving method to be selected during hot backup.
 

Control data files and log files by control files. After starting the corresponding instance when the database is started, first start the control file, and then open the data file from the control file. The phenomenon is: Database Mount: Database Mount, and then open the database: Database Open. In fact, first open the control file, and then open the data file.
 

下 Under the physical structure, these paths can be migrated at will, and can be stored under any storage, including raw devices.
 

Logical structure The logical structure of the database is much more complicated than the physical structure. Learning logical structure, not a day's work, I only learned relevant. I will have time to continue my studies in the future. Database storage space consists of one or more table spaces. (E.g. system, sysaux.)
1, table space (tablespace) organization of the logical structure of the database space, its physical structure is a data file, a table space physically composed of one or more data files, logically composed of one or more data segments.
2. The space occupied by the data segment (segment) logical objects, such as table segments, index segments, rollback segments, etc., segments exist in the table space and correspond to a certain storage space. The data segment is divided into one or more intervals.
3. The extent is a logically contiguous set of disk space (8 blocks by default) reserved for data at a time, and each extent occupies a certain number of data blocks. Zones cannot span data files.
4. The smallest storage unit of the block database (default 8k) is the basic allocation unit of all logical structures. The above is the basic structure of the logical structure.
 

1.Table space overviewTable space functionsOrganize data segment space and control the allocation of storage spaceControl the availability of data by making a single table space online or offline The specified user uses the specified table space to implement restrictions on the user. Perform backup and restore operations of some data. Table space characteristics. The largest logical unit of data. A database logically consists of at least one system table space. A data file structure. A table space includes at least one segment (control information). The size of the table space is equal to the sum of the size of all data files belonging to it. Query table space usage. SQL select * from dba_tablespaces; query all table space information in the database SQL select * from dba_data_files; query the data files contained in the table space, not including the temporary table space SQL select * from dba_temp_files; query the data files contained in the temporary table space Table space size SQL select tablespace_name, sum (bytes) from dba_free_space group by tablespace_name; Query the size of the tablespace free space. [page]

2.Create tablespace SQL create tablespace test datafile '/ u01 / a.dat'size 5m; Change tablespace SQL alter tablespace test add datafile' / u01 / b.dat'size 6m; Query tablespace SQL select tablespace_name, sum ( bytes) from dba_data_files group by tablespace_name; table space renamed SQL alter tablespace test rename to fff; table space offline SQL alter tablespace test offline; table space online SQL alter tablespace test online; set table space read-only SQL alter tablespace test read only; Set the table space to read and write SQL alter tablespace test read write; expand the table space SQL alter tablespace test add datafile '/ u01 / c.dat'size 500M; increase the number of data files to expand the table space (about 5 to 20 data files) SQL alter database datafile '/ u01 / a.dat'resize 80M; expand data file size to expand table space SQL alter database datafile' / u01 / a.dat 'autoextend on maxsize 100M; set automatic expansion parameters to automatically expand table space deletion Table space SQL drop tablespace test including contents and datafiles; delete tablespace and datafiles
 

3. Table space classification Table space is mainly divided into system table space (system, sysaux), data table space (user), rollback table space (undotbs), temporary table space (temp). 1.System table space Each database must have a system table space.The table space is automatically created when the database is created or installed.The name cannot be changed.It must be kept online at all times.It is used to store the system's data dictionary. Tables, program system units, process functions, packages, and triggers can also be used to store user data tables and index objects. In order to avoid the problems of magnetic field fragments and contention for system resources in the system table space, at least one independent table space should be created separately to extract user data separately. The sysaux table space is also created with the creation of the database. It is an auxiliary table space for the system table space. It mainly stores various tools that support oracle system activities such as logminer. sysaux reduces the load on the system table space. 2.Data and index table spaces are created by the user after the data is created.It is the most important part of the database space. There should be multiple data table spaces. When creating database objects of different users and properties, they should be specified to be stored in the specified data. In the table space, multiple index table spaces should be established, and the indexes of different objects should be specified and stored in the specified data table space according to size and access frequency. Under normal circumstances, data and index tablespaces should be set up appropriately, too small a single tablespace is too large, data is not secure and response time-consuming, too small is difficult to manage. The users tablespace is created by default when the database is created, and contains a data file user01.dbf. This tablespace is used by default when a new user does not specify a storage tablespace. 3, rollback table space undo data is also called rollback (rollback) data, users ensure data consistency, when performing DML operations, the data before the transaction operation is called undo records, undo tablespace is used to save undo records. The undo tablespace user saves undo records, which is the most critical part of the database space, and it has a great impact on the operation of the database. When the database is created, a rollback segment table space undotobs1 is created by default, which contains a data file undotobs01.dbs. SQL show parameter undo;
 

4, temporary table space temporary table space (temp tablespace) is mainly used to store historical data generated during the operation of the oracle database, and for sorting. After the database is closed, all data in the temporary table space will be cleared, so all other table spaces outside the temporary table space are permanent table spaces. A temporary table space temp is created by default when the database is created. Contains a data file temp01.dbf. For environments with large operations, multiple temporary table spaces should be established and assigned to different temporary table spaces to avoid multi-user and multi-task competition for the temporary table space. Check the system's default temporary table space and Corresponding data files SQL select tablespace_name from dba_tablespaces; SQL select * from dba_temp_files;