Oracle physical structure and logical structure

Physical structure of Oracle and logical Structure view the physical file path of the Oracle database must be viewed with commands, unless you have installed it yourself and have done all the security configuration, it is very likely that your predecessor made a change to the database and inadvertently made a mistake.

View Control File Desc v$controlfile View Control file status and name (show path) SELECT status,name from V$controlfile;

View the data file desc v$datafile such as viewing the file number and status of the data file select File#,status,name from V$datafile;

Look at the log file desc v$logfile View Log file path information select member from V$logfile;

Oracle's file System: Control file (. CTL), data files (. DBF), log file (. LOG)

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

In addition to these three files have a parameter file, the parameter file is not an effective part of the database system, in the start of the database, the parameter file is not directly involved in the work, but the control file is the parameter file for search. Parameter file location:/u01/app/oracle/product/10.2.0/db_1/dbs/spfileorac Lesid.ora The contents of the control file are 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 for online log files (redo log files) and one for archived log files. REDO01. LOG and redo02.log,redo03. Log is a typical online log file, characterized by sequential write files, write the next one after full, write the third loop write the first, and overwrite out of the backup. Archive log files, an archive method to choose when hot backup is available.

The control file controls the data files and log files. When the corresponding instance is started on the database startup, the control file is first started and the data file is opened in the control file. The phenomenon is: Database mount: DB mount, then open database: DB open. In fact, the first open the control file, in the Open data file.

Under the physical structure, these paths are free to migrate and can be stored in any storage, including bare devices.

The logical structure of the logical structure database is much more complicated than the physical structure. Learning logical structure, not a day's work, I only learned about the relevant. Have time to further study in the future. The database storage space is composed of one or more tablespace spaces. (such as system, Sysaux.) 1, table space (tablespace) organization of the logical structure of database space, its physical structure is a data file, a table space is physically composed of one or more data files, logically composed of one or more data segments. 2, the data section (segment) The logical object occupies the space, like the table section, the index section, the rollback paragraph and so on, Gencun lies in the table space, and corresponding certain storage space. The data segment is divided into one or more intervals. 3, the region (extent) interval is used for data one-time reservation of a logically contiguous set of disk space (default 8 block), each interval occupies a certain amount of data blocks. The zone cannot span data files. 4, block database, the smallest storage unit (default 8k), is the basic allocation unit of all logical structures. The basic structure of the logical structure above

1. Table Space Overview Table Space function • Organize data segment space, control the allocation of storage space • Control the availability of data by enabling a single tablespace to be online or offline • Allocate data storage across devices through table space partitioning to improve performance • Limit user restrictions by specifying a user's use of the specified table space · Perform partial data backup and recovery operation table space characteristics • Maximum logical units in data • A database logically at least by a system table space • A table space is physically at least composed of a data file • A tablespace consists of at least one segment (Control information) · The size of the table space equals all the sum of the data file size from which it is queried table space usage SQL Select*from dba_tablespaces, querying the database for all tablespace information SQL Select*from dba_data_files; Query table space contains data file information, does not contain temporary table space SQL Select*from dba_temp_files; data files included in temporary table spaces SQL Select Tablespace_name,sum (bytes) from Dba_data _files GROUP by Tablespace_name query table space Size SQL Select Tablespace_name,sum (bytes) from Dba_free_space GROUP by Tablespace_ Name; query table space free space size

Create Tablespace SQL create tablespace Test datafile '/u01/a.dat ' size 5m; change table space SQL alter TABLESPACE test add datafile '/u01/b.dat ' si Ze 6m query table space 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 tablespace read-only SQL Alter TABLESPAC e test read only; set tablespace to read and write SQL alter tablespace test read write; extended table space SQL alter TABLESPACE test add datafile '/u01/c.dat ' size 500M increase the number of data files to expand the table space (data files about 5~20) SQL ALTER DATABASE datafile '/u01/a.dat ' resize 80M; Extended Data File Size expansion table space SQL ALTER DATABASE DataFile '/u01/a.dat ' autoextend on maxsize 100M; Set automatic expansion parameters to automatically expand tablespace Delete table space SQL Drop tablespace test including contents and Datafiles, deleting tablespace and data files

The

Tablespace classification table space is mainly divided into system table spaces (systems, Sysaux), datasheet space (user), rollback table space (UNDOTBS), temporary tablespace (temp). 1, the system table space each database must have a system table space, this table space is created automatically when the database is created or when the database is installed, the name cannot be changed, the online status must be maintained at all times, the data dictionary table for the storage system, the program system unit, the process function, the package and the trigger, etc. can also be used to store user data tables, indexed objects. To avoid the problem of magnetic field fragmentation in system tablespaces and contention for system resources, you should create at least one separate tablespace to extract user data separately. The Sysaux tablespace is also created with the creation of the database and is the secondary tablespace of the system tablespace, with the main storage of various tools, such as Logminer, that support the activities of the Oracle system, Sysaux reduces the load on the system table space. 2, data and Index table space by the user in the creation of data, is the most important part of the database space, the data table space should be set up a number of different users and the nature of the database objects should be specified in the specified data table space, the index table space should also be set up multiple, and classify the indexes of different objects into the specified datasheet space by size and access frequency respectively. Usually, the data and index table space should be set up appropriate multiple, too little a single table space is too large, data insecure and recovery time consuming, too small is difficult to manage. The user tablespace is created by default when the database is created, contains a data file user01.dbf, and is used by default for new users who have not specified a storage tablespace. 3. Rollback table Space Undo data is also called rollback (rollback) data, the user ensures data consistency, when the DML operation is performed, the data before the transaction operation is called the Undo record, and the undo table space is used to save the undo record. Undo Table Space Users save the undo record, is the most critical part of the database space, and it has a great impact on the operation of the database. The database is created by default to create a rollback segment tablespace UNDOTBS1, containing a data file Undotbs01.dbs. SQL show parameter undo;

4. Temporary tablespace temporary table space (temp tablespace) is primarily used to store historical data generated during the Oracle database run and for sorting. When the database is closed, all the data in the temporary tablespace is cleared, so all other tablespaces in the temporary table space are part of the permanent tablespace database. Temp is created by default when creating a temporary table space. Contains a data file temp01.dbf, for large operation of the environment, should establish a number of temporary table space, and belong to different temporary table space, to avoid multi-user and multi-task competition this temporary table space View system default temporary tablespace and corresponding data file SQL Select Tablespace_ Name from Dba_tablespaces; SQL Select*from Dba_temp_files;

-