Oracle physical and logical structures

To view the physical file path of an oracle database, you must run the command to view the physical file path of the oracle database. Unless the database is installed by yourself and all security configurations have been performed, otherwise, it is very likely that your predecessor made changes to the database, and accidentally caused a big mistake. View control file DESCv $ controlfile View Control File status and

To view the physical file path of an oracle database, you must run the command to view the physical file path of the oracle database. Unless the database is installed by yourself and all security configurations have been performed, otherwise, it is very likely that your predecessor made changes to the database, and accidentally caused a big mistake. View control file DESC v $ controlfile View Control File status and

To view the physical file path of an oracle database, you must run the command to view the physical file path of the oracle database. Unless the database is installed by yourself and all security configurations have been performed, otherwise, it is very likely that your predecessor made changes to the database, and accidentally caused a big mistake.

View control file DESC v $ controlfile View Control File status and name (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;

View log file DESC v $ logfile view log file path information SELECT member FROM v $ logfile;

Oracle File System: control file (. CTL), data file (. DBF), LOG file (. LOG)

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

There is also a parameter file for the three files. The parameter file is not an effective part of the database system. When the database is started, the parameter file is not directly involved in the work, only the control file is searched for by the parameter file. Parameter file location:/u01/app/oracle/product/10.2.0/db_1/dbs/spfileorac leSID. ora control file content is displayed in the parameter file. Parameter files are used to search for control files.

Oracle has two types of log files: Online log files (redo log files) and archive log files. REDO01.LOG, REDO02.LOG, and REDO03.LOG are typical online log files. They are characterized by sequential file writing. When they are full, they are written to the next one. When they are full, they are written to the first one in the third loop, and are overwritten without backup. Archive log files. You must select an archive Method for hot backup.

Data Files and log files are controlled by control files. When the database starts the corresponding instance, first start the control file and open the data file in the control file. The phenomenon is: Database load: Database Mount, and then Open the Database: Database Open. In fact, the control file is opened first, and the data file is opened.

Under the physical structure, these paths can be migrated at will and stored in any storage, including bare devices.

The logical structure of a logical structure database is much more complex than the physical structure. Learning the logical structure is not a one-day task. I only learned about it. I will continue to study in the future. The database storage space consists of one or more tablespaces. (Such as system, sysaux .) 1. a tablespace organizes the logical structure of a database space. Its physical structure is a data file. A tablespace is physically composed of one or more data files, it is logically composed of one or more data segments. 2. the space occupied by the Data segment (segment) logical object, such as the table segment, index segment, and rollback segment. The segment exists in the tablespace and corresponds to a certain amount of storage space. The data segment is divided into one or more intervals. 3. the extent interval is a logical continuous set of disk space (eight disks by default) reserved for one-time Data. Each interval occupies a certain amount of data blocks. Zones cannot span data files. 4. The smallest storage unit (8 k by default) of a block database is the basic allocation unit of all logical structures. The basic structure of the above Logic Structure

1. table space overview table space Function · organize data segment space and control the distribution of storage space · control data availability by making a single table space online or offline · implement data storage allocation across devices through table space division, to improve performance · Implement User restrictions by specifying the user's use of the specified tablespace · perform backup and recovery of part of the data features of the tablespace · The maximum logical unit in the Data · A database logically consists of at least one system tablespace: a tablespace is physically composed of at least one data file. A tablespace contains at least one segment (control information) · The table space size is equal to the total size of all data files belonging to the table space 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 file information contained in the table space, excluding the temporary tablespace SQL select * from dba_temp_files; query the data files contained in the temporary tablespace SQL select tablespace_name, sum (bytes) from dba_data_files group by tablespace_name; query the table space size SQL select tablespace_name, sum (bytes) from dba_free_space group by tablespace_name; query the table space free space size

Create a tablespace SQL create tablespace test datafile '/u01/. dat 'size 5 m; alter tablespace test add datafile'/u01/B. dat 'size 6 m; query table space SQL select tablespace_name, sum (bytes) from dba_data_files group by tablespace_name; tablespace renamed SQL alter tablespace test rename to fff; tablespace offline SQL alter tablespace test offline; table space online SQL alter tablespace test online; tablespace read-only SQL alter tablespace test read only; tablespace read/write SQL alter tablespa Ce test read write; extended tablespace SQL alter tablespace test add datafile '/u01/c. dat' size 500 M; increased the number of data files to expand the tablespace (data files about 5 ~ 20) SQL alter database datafile '/u01/. dat 'resize 80 M; expand the data file size and expand the tablespace SQL alter database datafile'/u01/. dat 'autoextend on maxsize 100 M; Set automatic expansion parameters to automatically expand tablespaces Delete tablespaces SQL drop tablespace test including contents and datafiles; Delete tablespaces and data files

Tablespaces are classified into system, sysaux, user, undotbs, and temp ). 1. Each database must have a system tablespace, which is automatically created during database creation or database installation. The name cannot be changed and must be online at any time, it is used to store system data dictionary tables, Program System units, process functions, packages, and triggers. It can also be used to store user data tables and index objects. To avoid magnetic field fragmentation and competition for system resources in the system tablespace, at least one independent tablespace should be created separately to extract user data separately. Sysaux tablespaces are also created along with the creation of databases. It is an auxiliary tablespace for system tablespaces. It mainly stores a variety of tools that support oracle system activities, such as logminer. sysaux reduces the load on system tablespaces. 2. Data and index tablespaces are created by the user after the data is created. They are the most important component of the database space. There should be multiple data table spaces, when creating database objects of different users and properties, you should specify the storage space of the objects in the specified data table space, and create multiple index tablespaces, the indexes of different objects are stored in the specified data table space according to the size and Access frequency. Generally, multiple data and index tablespaces should be created. If there are too few tablespaces, a single tablespace is too large. The data is insecure and the response time is too long, which is difficult to manage. The users tablespace is created by default when the database is created, including a data file user01.dbf. This tablespace is used by default when the new user does not specify a storage tablespace. 3. roll back the tablespace undo data, also known as rollback data, to ensure data consistency. When performing a DML operation, the data before the transaction operation is called an undo record, the undo tablespace is used to save undo records. Undo tablespace users store undo records, which is the most critical component of the database space and has a great impact on the operation of the database. When a database is created, a tablespace undotbs1 In the rollback segment is created by default, which contains a data file undotbs01.dbs. SQL show parameter undo;