Concept:
The logical structure of Oracle can generally be divided into:
table Spaces (tablespace), segments (segment), zones (extent), data blocks. Oracle consists of table spaces, which consist of segments, segments composed of blocks, and extents composed of chunks.
Category:
table space:
1. Tablespace is the largest logical unit in the database, several table spaces make up the database.
2. Tablespaces are physically composed of one or more data files, a data file can only belong to one table space, where the table space is distributed across multiple disks through different data file locations.3. Depending on the segment type, the tablespace is divided into: Data segment table space, index segment table space, temporary segment table space, fallback segment table space.4. You can manage the tablespace size by modifying the size of the data file or by adding a delete data file.5. Tablespace can be online or offline (OFFLINE), or some of the tables in space can be online or offline. There are also properties for read only or read write (read only). Some special table spaces, such as--system, must not be set offline or read-only6. You can limit the size of the user's usage on the tablespace via the quota parameter。7. Special temporary table space, which is primarily stored in the temporary space required to sort or summarize using the order BY statement.after 9i, the temp table space is generally used as a temporary table space, it is not recommended to use special space such as system as temporary table space. Base tables cannot be created on a temporary table space. You must not use a different tablespace as a temporary table space after you specify the default temporary tablespace.
Category 1:system and non-system table spaces. The system tablespace is primarily used to store system data, and non-system tablespaces are used to store users and other non-system data. system tablespace:1. The system tablespace must exist for the database and cannot be offline or read-only. Once paralyzed, the entire database will not function properly. 2. The system table space contains all the data dictionaries, stored procedures, packages, and triggers. You can view all system objects through all_objects. 3. The system tablespace can contain non-system objects, such as user data, but is not recommended. 4. The system table space contains the system fallback segment. By querying the Select Segment_name,owner,tablespace_name from Dba_rollback_seg; Non-system tablespace:1. More flexible Management database,2. Enhance database security and optimize operational performance Classification 2: Data dictionary Management tablespace ( Dictionary-managed tablespaces) and Localization management table spaces (locally Managed tablespaced). data field Management table space: You must manually set various parameters for the table space, easy to cause disk fragmentation, management difficulty large localization management table space: 8i or above, the district management system automatically, the disk fragments automatically eliminate, management difficult and high efficiency. via statement: select Tablespace_name,extent_management,allocation_type from Dba_tablespaces; some queries about the tablespace: A. Querying data files in a tablespaceSelect File_id,bytes,maxbytes,tablespace_name,file_name from Dba_data_files;B. Querying free space-related information for table spacesSelect Tablespace_name,sum (bytes), sum (blocks) from Dba_free_space Group by Tablespace_name;C. Querying information for temporary filesSelect File#,status,name from V$tempfile;D. Querying temporary tablespace information used by the userSelect Username,temporary_tablespace from Dba_users;segment: Divided into 4 categories according to the different storage data:Data segment, index segment, fallback segment, temporary segment.Data segment:For storing data, creating a data segment within a table space for each table created (except for cluster aggregation, which aggregates 2 tables with only one data segment). The increase of the data in the table is the process of increasing the segment, which leads to an increase in the number of segments.query for data segment information: select Segment_name,tablespace_name,bytes from User_extents;some data segments are distributed across multiple table spaces (table partitions):Select Segment_name,partition_name,tablespace_name from User_extents;Index Segment:Used to store index data, and if an index is created, an index segment is automatically created within the index space.Query index and related information:Select A.table_name,a.index_name,uniqueness,column_nameFrom User_ind_columns b,user_indexes Awhere b.index_name=a.index_name and b.table_name = A.table_name and rownum<10;The index is divided into B + tree indexes, bitmap indexes, and reverse indexes. The generally uses the B + Tree index by default, and bitmap indexing can be used in some read-only databases or large table queries. The so-called reverse index is the key value in turn query. fallback segment: stores the values before the user data modification, generates read consistency, and rolls back uncommitted transactions when the database is resumed. A transaction can be stored with only one fallback segment, but a fallback segment may store multiple transactions. after oracle9i, managing the fallback segment from a very troublesome thing into a simple thing, because provides automatic management of the undo space, you just have to ensure that the size of the undo space is enough, you do not have to worry about the number of fallback segments, size and other things. Whether to use automatic management, you can see whether the value of the initialization parameter undo_management is auto. Yes, it is automatically managed. Conversely, manual is used for fallback segment management. The fallback segment is divided into the system fallback segment and the non-system fallback segment and the defered fallback segment. System fallback segment: After the database is created, Oracle automatically creates a fallback segment system that can only be used to store the fallback information for system tablespace systems. Non-system fallback segment: The database contains at least one non-system fallback segment that is used to store fallback information for non-system table spaces. defered Fallback segment: Automatically created when a tablespace is offline, automatically deleted when a tablespace is online, and used to store fallback information that is generated when it is offline. Querying the system fallback segment information: select Segment_name,owner,tablespace_name from Dba_rollback_segs; Select Segment_name,tablespace_name,bytes,blocks from sys.dba_segements where segment_type= ' ROLLBACK '; Temporary segment: is used to store the temporary table space needed to sort or summarize and perform operations such as connecting, grouping, creating indexes, and then automatically disappearing after use. The disk staging segment is automatically used when the size of the memory arrangement is not sufficient to sort. The memory sort area size can be viewed with the parameter sort_area_size. To view information for temporary files: select File#,status,name from V$tempfile;
area: Area or calledRange，is the smallest unit of disk allocation space. The disk is allocated at least one zone at a time. As the number of districts increases, the segment is constantly getting bigger, and the table space naturally becomes larger. When the number of districts exceedsMAXEXTENTSLimit, you can no longer increase the number of zones, the size of the segment can not be increased, the table data can no longer increase.Querying table space for storage parameters：Select Tablespace_name,contents,initial_extent,next_extent,min_extents,max_extents,extent_management,allocation _type from Dba_tablespaces;Management:IntoThe data dictionary tablespace (canceled in 9i Release2) and the localization management space. The extent_management of the above query statement indicates the way of management, and allocation_type the allocation of the area under the management mode. Uniform represents the size of the zone as a uniform size, and the system represents the size of the zone automatically managed. ThisThe difference in management methods determines the management of disk fragmentation. In the data dictionary mode, a lot of disk fragmentation will be caused, generally need to be ordered by the alter TABLESPACE TEMP COALESCE collation. In localization management, there is no need to worry about this disk because of the size or automatic management of the zone.。Query the storage space size and storage method of the base table created by the user:Select Initial_extent,next_extent,min_extents,max_extents,pct_increase from User_tables where table_name= ' XXXX ';Initial_extent represents the first area size assigned to a segment; Next_extent represents the size of the next allocation area.Min_extents represents the minimum number of extents allocated for the first time the segment was created, and max_extents represents the maximum number of segments in the segment.Pct_increase represents the increase in percentage ===>next=next* (1+pctincrease) relative to the previous zone, starting with the third zone.Query for area allocations in data segments:Select Segment_name,table_name,extent_id,bytes,blocks from user_extents where segment_name= ' XXXX ';data block: A data block is the smallest data organizational unit and administrative unit in a database, and also the smallest I/O unit. the size is determined by the parameter db_block_size , and the range is between 2k-64k . For the system tablespace with the default tablespace and only the data block specified by the parameter db_block_size, you can use different block combinations for the other table spaces. query data block size:1.show parameter db_block_size 2.select name,value from V$parameter where name like '%size% ; 3. Parameter file: data blocks are typically composed of block size, table directory, row directory, row data, and free space. a series of parameters can be used to control the behavior of the block, such as the idle rate (PCTFREE), the usage (pctused), the minimum number of transactions (Initrans), and the maximum number of transactions (Maxtrans). if the PCTFREE is used to specify the space rate to be reserved in a block, such as a value of 20%, then your block will store up to 80% of the block space. These idle spaces can be used when existing row modifications are possible. the pctused is used to specify a qualified value for the usage in the block. For example, take a value of 60%, you can continue to insert new row values when there is no storage full. It is mostly used for inserting statements. Initrans Specifies the initial number of transaction portals allocated in each data block, with a range of 1-255 and an initial of 1. Maxtrans Specifies the maximum number of transaction entries that are allocated in each data block, with a range of 1-255 and a default of 255. query data block parameter settings:select Table_name,pct_free,pct_used,ini_trans,max_trans from User_tables;
Oracle Data Logic Components
