Local management table space LMT (transferred from IT

Local management table space - everyone continues to discuss!

Local Management Table Space Locally Managed TableSpace 1, Overview 1. Understand the origin of the local management table space 2, understand what is a dictionary management table space and working principle 3, understand the advantages of local management table space (why to use local management table space) 4 Understand the internal structure of the local management table space 5. Understand the conversion of dictionary management tablespace and local management table space. Second, noun interpretation and aggregate table space (TableSpace) - provides a logical structure for the database to use space, its corresponding physical structure is Data file, a table space can contain multiple data file local management table spaces (LMALLY Managed TableSpace referred to as LMT) - 8i manages a new table space management mode, through the status map to manage space for tablespace . Dictionary-management table space (Dictionary-management tablespace referred to as DMT) - 8i previously included a tablespace management mode that can be used later, and is used by space management table spaces through the data dictionary. Segment (segment) - a database logical structure such as a table, an index section, a rollback segment, and the like, and the segment is in the tablespace and corresponds to a certain storage space. The interval, which can be referred to as the area (Extent)-segment storage can be divided into one or more intervals, each interval occupies a certain number of data blocks (block), in the local management table space, the EXTENT of the table space is corresponding to the corresponding segment EXTENT. Block - The smallest storage unit of the database, which is about 8192 bytes in this article. Bit (BIT) - The spatial management unit of the local management table space, one bit may be equal to one interval, or may form a range. Third, the origin of the local management table space is in the Oracle8i version, Oracle introduces a new table space management: localized management table space. The so-called localization is that Oracle no longer uses the data dictionary table to record the usage status of the area inside the Oracle table space, but joined a bit diagram area in the head of the data file of each table space, which records each The use of the area. Whenever a zone is used, or is released for re-use, Oracle will update this record of the header of the data file to reflect this change. Creation process of localized management table space: grammar: create TableSpace Table Space Name DataFile 'Data File Details' [EXTENT Management {Local {Autoallocate | Uniform [Size Inteltral [K | M]]}}] Keyword Extent Management Local Specify this is a localized manner. For system tablespaces, you can only specify Extent Management Local when you create a database, as it is the first table space created when the database is created. In 8i, Dictionary management is still the default management method, which is a local management table space when the Local keyword is selected. Of course, you can continue to choose a finer management method: Autoallocate or Uniform .. If Autoallocate, it indicates that Oracle will determine the use of blocks; if you select uniform, you can specify the size of each block in detail. If you do not specify, use 1M size for each zone. Oracle has introduced this new table space management method, let's take a look at the advantages of this tablespace organization method: 1. Local management table space avoids recurrent space management operations.

This situation often occurs frequently in the tablespace management of the data dictionary. When the usage status of the area in the table space changes, the information of the data dictionary changes, so that the return in the system table space is also used. Roll. 2. Localized table space avoids writing an idle space in the corresponding table of the data dictionary, using space information, thereby reducing the competition of the data dictionary table, enhancing the concurrency of space management 3. Localization management of the area Automatically track the idle blocks in the tablespace, reduce the needs of manual combined free space. 4. The size of the area in the tablespace can be selected by the Oracle system, or specify a unified size by the database administrator, avoiding a piece of debris that has been headache in the Dictionary watch space. 5. From the data dictionary to manage the idle block to manage the idle block by the header record of the data file, avoid rollback information, no longer use the returns in the system table space. Because it is managed by the data dictionary, it will record the relevant information in the table of the data dictionary, resulting in rollback information. Due to the above characteristics of this table space, it supports more concurrent operations in a table space and reduces the dependence on the data dictionary. Fourth, the local management table space management mechanism table space is a logical structure for segment (table, index, etc.) to provide space, so when adding, when deleting sections in table space, the database must track the use of these spaces.

As shown in the following example, it is assumed that a newly created table space contains five tables one ... Table 2 ... Table three ... Table four ... Table 5 ... Unused space When we delete the table four, there is As shown in the following results ... Table 2 ... Table 3 ... Idle space segment ... Table 5 ... Unused space is obvious, Oracle needs to have these assignments of each data file in the table space to manage or unallocated Space, in order to track these spaces that can be used (including unallocated and can be reused), for each space, we must know: 1, this free space is located in what data file 2, this space size is 3, If it is in use, which piece is occupied by this space until 8i, all table spaces are dictionary management mode, in order to ensure the above information, Oracle uses two data dictionary tables: UET $ (already Use of the range) or FET $ (free space): SQL> Desc UE $ NAME NULL? TYPE --------------------------- --------- Segfile # not null Number segblock # not null number | The segment this uses this space Ext # not null number | The tablespace ID and the file file # not null number | ID for That TableSpace Block # not null Number Length Not Null Number | The location and size of the chunk sql> desc fet $ name null? Type ------------------ ----------------- Ts # not null number | the tablespace id and the file file # not null number | id for what tablespace block # not null number length not null Number | The location and size of the chunk Query This table can be seen, each use space or idle space (not necessarily an extent, can be multiple extents) in this table. It works when a segment is deleted, Oracle will move the corresponding line to FET $ in UET $, which occurs continuous, and may occur. When it is highly high, the content of the data dictionary is coming. Another problem is that when the space of the table is very discontinuous or a large amount of debris in the table space causes the two tables, it will cause a decline in database performance. Local management table space is to solve this problem, and Oracle stores store information in bitmaps in the header of the table space in spatial management, Oracle is stored in the data dictionary. In this way, when allocating the recovery space, the table space can independently completion operations and does not have to be related to other objects. Let us enter the interior of the local management table space and see how Oracle achieves this.

The local management table space of UNIFORM mode 1, first created a local management table space, interval unified size assigned to 64KSQL> CREATE TABLESPACE DEMO DATAFILE '/ORA01/OEM/OemDemo01.dbf' size 10m Extent Management local uniform size 64k; 2 In this table space, create a table SQL> Create Table Demotab (X Number) TableSpace Demo Storage (Initial 1000K Next 1000K); we pass the table SQL> SELECT T.TABLE_NAME, T. Initial_EXTENT, T.NEXT_EXTENT from User_Tables T WHERE T.TABLE_NAME = 'Demotab'; table_name initial_extent next_extent -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------- Demotain 1024000 65536 You can find that the storage parameters of the table are not our designated initial_extent, but the uniform size of Uniform size, next_extent is equal to Uniform Size.

We can also see the following cases from this query SQL> select count (*) from user_extents where segment_name = 'demotab'; count (*) ---------- 16, this table is There have been 16 extents in the table space, not an extent (this is the difference with dictionary management, if it is a dictionary management table space, if the above table is created, the result of the query is 1) 3, get the data file Document IDSQL> Col ​​Name Format A30 Trunc SQL> SELECT FILE #, Name from V $ DataFile; File # name ------------------------ 1 / ORAS1 / OEM / OMSYSTEM01.DBF 2 / OS3/oem/oemundo01.dbf 3 / ORA01 / OEM / OEMOEM_REPOSTORY01 4 / ORA01/oem/oemrcat01.dbf 5 /ora01/oem/OemDemo01.dbf We can check UET $ with FET $ SQL > Select Count (*) from Ut $ Where File # = 5; Count (*) ---------- 0 SQL> SELECT Count (*) from Fet $ Where File # = 5; Count (*) ---------- 0 4, you can see that oracle does not save any information in both tables, now we dump the third block of the data file SQL> ALTER SYSTEM DUMP DATAFILE 5 block 3; SYSTEM Altered. View Dump file, there is the following information Start Dump Data Blocks TSN: 5 File #: 5 minblk 3 MaxBLK 3 Buffer TSN: 5 RDBA: 0x01400003 (5/3) SCN: 0x0000.202F7A6F SEQ: 0x01 flg: 0x00 tail: 0x7a6f1e01 frmt: 0x02 Chkval: 0x0000 Type: 0x1e = ktfb bitmapped file space Bitmap File Space Bitmap Block: Bitmap Control: relfno: 5, BeginBlock: 9, Flag: 0, First: 16, Free: 63472 FFFF000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 ..... note which FFFF00 ,, this is the performance of the method in hexadecimal, we converted to binary, There are 1111, 1111 ,1111 ,1111, 0000, 0000, found 16 1, each 1 is a bit (BIT), representing 64K, representing the 16 extents that have been allocated, if we Extend the table, what is the result? SQL> ALTER TABLE DEMOTAB Allocate Extent; Table Altered. SQL> ALTER TABLE Demotab Allocate Extent; Table Altered. SQL> ALTER TABLE Demotab Allocate Extent; Table Altered. In this way, we should have 19 extents.

DUMP third block Start Dump Data Blocks TSN: 5 File #: 5 Minblk 3 MaxBlk 3 Buffer TSN: 5 RDBA: 0x01400003 (5/3) SCN: 0x0000.202F7C64 SEQ: 0x01 FLG: 0x00 Tail: 0x7c641e01 fRMT: 0x02 chkval: 0x0000 type: 0x1e = KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 5, BeginBlock: 9, Flag: 0, First: 19, Free: 63469 FFFF07 0000000000 0000000000000000 0000000000000000 0000000000000000 addition to the previous FFFF, now More 07, how to explain? 07 Convert to binary is 0000, 0111, but still not explained the above case, here we do not take into account the case of byte exchange, because the above FF is exchanged or ff, but if it is 07, we must consider byte exchange (because The computer is a one byte word, one byte is of course the back, if we are from 01 to 0f to ff. If we understand, the FFFF07 is converted to a binary of 1111, 1111, 1111, 1111, 10000, 0111. Each byte is exchanged 1111, 1111, 1111, 1111, 1110,0000 can be found that there are 19 1, which is 19 bits, representing the current 19 extent.

5. Similarly, we dump Data file nine blocks, there are Start Dump Data Blocks TSN: 5 File #: 5 Minblk 9 MaxBLK 9Buffer TSN: 5 RDBA: 0x01400003 (5/3) SCN: 0x0000.202F7C64 SEQ: 0x01 flg : 0x00 Tail: 0x7c641e01 frmt: 0x02 chkval: 0x0000 Type: 0x1e = ktfb bitmapped file space Bitmap Extent Control header --------------------------- -------------------------------------- Extent Header :: spare1: 0 Space2: 0 #EXTENTS : 16 #Blocks: 127 Last Map 0x00000000 # Maps: 0 Offset: 4128 Highwater :: 0x01c0000A Ext #: 0 BLK #: 0 EXT Size: 7 #Blocks in Seg. HDR's FreeArs: 0 #Blocks Below: 0 Mapblk 0x00000000 Offset: 0 Disk Lock :: Locked by SCN: 0x0006.012.00000017Map header :: Next 0x00000000 #: 3090 Flag: 0x40000000Extent Map ---------------------------------------------------------------------------------------------------------------- --------------------------------------------- 0x01C0000a Length: 7 0x01c00011 length: 8 0x01c00019 length: 8 0x01c00021 length: 8 0x01c00029 length: 8 0x01c00031 length: 8 0x01c00039 length: 8 0x01c00041 length: 8 0x01c00049 length: 8 0x01c00051 length: 8 0x01c00059 length: 8 0x01c00061 leng th: 8 0x01c00069 length: 8 0x01c00071 length: 8 0x01c00079 length: 8 0x01c00081 length: 8 nfl = 1, nfb = 1 typ = 1 nxf = 0SEG LST :: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 End dump data blocks tsn: 5 File #: 5 minblk 9 Maxblk 9 This is the header (a block) message in the data file. From here you can see, the table starts using highwater :: 0x01c0000a from the 9th block is already 10th Block, from the above list, we can also see that the table spends 16 intervals because the table is the first table of the data file, so the bit map area occupies 6 blocks from 3 to 8 total, plus In front of the two file heads, that is, 8 blocks in the header of the data file are used for system consumption. If our db_block_size is 8192, it is obvious that the space occupied is 64K. Also because Just operate a few blocks of the header of the data file, not using the data dictionary, the Oracle is added to the local management table space, and the efficiency is faster than the dictionary. Especially in a spatial request in concurrency. Oracle makes all of the extents in the local management table space in the same size by mandatory means, although you may have custom different storage parameters.

6, add some dictionary management table spaces to different a. If it is a dictionary management, the size of the interval in the table space depends on the storage parameters of the table, and if not, the universal storage parameters of the table are removed. So the interval size of each table can be different. b. If you do not specify the minimum area number of the table, then the table is created by default, the table has only one range instead of multiple intervals. c. Dictionary management file header only occupies a block, the first table's HWM should be highwater :: X01C00003, about this can be viewed by Dump this data file. The local management table space of Autoallocate is automatically assigned locally managed tablespace, and the interval size may consist of 64k, 1m, 8m, 64m or even 256m. But no matter how big, there is a general size 64K, so 64K is the bit size of the table space.

SQL> create tablespace dummydatafile 'c: /dummy01.dbf' size 100mautoallocate; Tablespace created.SQL> create table x1 (x number) tablespace dummystorage (initial 50M); Table created.SQL> select file # from v $ datafile where name like '% Dummy%'; File # ---------- 12SQL> SELECT EXTENTS from user_segmentswhere segment_name = 'x1'; extents -------------------------------- System altered. *** session ID11.59) 2002-11-22 10: 37: 35.000Start Dump Data Blocks TSN: 19 File #: 12 Minblk 3 Maxblk 3 Butffer TSN: 19 RDBA: 0x03000003 (12/3) SCN: 0x0000.00f2959b seq: 0x01 flg: 0x00 tail: 0x959b1e01frmt: 0x02 chkval: 0x0000 type: 0x1e = KTFB Bitmapped File Space bitmapFile Space Bitmap Block: BitMap Control: RelFno: 12, BeginBlock: 9, Flag: 0, First: 800, Free : 62688FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFF00000000 0000000000000000 0000 00000000000000000000000000000000 00000000000000000000000000000000 000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 00000000000000000000000000000000 00000000000000000000000000000000 00000000000000000000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 actually only see the table section 50 (extent), but there are 800 bits (bit) 50 * 1024 = 800 * 64 can also be seen The bit size is not equal to the extent size. Migrate to the local management table space in many cases, if you want to convert between dictionary tablespace and local table space, you may need to transfer all of the table space Data, from the newly created tablespace, load the data.

However, after 816, there is a packet called dBMS_SPACE_ADMIN to become possible, embodied in the following two processes: dbms_space_admin.tables_space_migrate_to_local dbms_space_admin.tablespace_migrate_from_local But when you want to use this process, you must Pay attention to two things: 1, the database version must be 816 or more, the compatible version must be 8.1 or more 2, if it is converted into local management, there must be enough free space to do this status map space (8 blocks) Dictionary is managed to local management, all transformations are basically impossible. The actual situation is that for existing data and space, the process is not available, just simply puts the space and marks the space. So, this conversion table space can slow down the pressure of UET $ and FET $, but does not solve the debris problem.

From Query DBA_TABLESPACES, you can also see that the table space management method after the conversion is local, but the actual segment allocation is User (not uniform or automatic). It is clear that there are many different intervals in the tablespace management table space (Extent " ) Size, so when converted to local management, how does Oracle convert these existing space to a general size? In order to do this, Oracle must scan each data file of the table space, mainly to check the following three issues: 1. All existing intervals 2, all previously used, but now free space 3, by When the size of the MINIMUM EXTENT statement mark is in the conversion, Oracle tries to find out the size of the largest interval suitable for the above three standards as the interval size of the local management, that is, in the worst case, this biggest The interval may be a single block (if the interval size of a table is 7 blocks, the interval size of the other table is 8 blocks) We look at an example 1 from the dictionary management table space to the local management table space, first, we create a dictionary management table space SQL> create tablespace blah datafile 'G: /ORA9I/ORADATA/DB9/BLAH.DBF' size 10m reuse extent management dictionary; tablespace altered SQL> col bytes format 999,999,999 SQL> select * from dba_free_space where tablespace_name =. 'Blah'; TableSpace_name file_id block_id bytes block relative_fno -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ---------------------- Blah 8 2 10, 477, 568 1279 8 2, we create three tables above, the smallest common size is 400ksql > create table t1 (x number) storage (initial 400k) tablespace blah;. Table created SQL> create table t2 (x number) storage (initial 800k) tablespace blah;. Table created SQL> create table t3 (x number) storage ( Initial 1200K) TableSpace Blah; Table Created. SQL> Select * from DBA_Free_Space Where TableSpace_name = 'Blah'; TableSpace_name file_id block_id bytes block relative_fno -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------- BLAH 8 302 8,019,968 979 8 SQL> SELECT BYTES from DBA_EXTENTS WHERE TABLESPACE_NAME = 'Blah'; Bytes - -------- 409,600 819,200 1,228,800 3, now we start to convert the table space for local management table space, assume that each bitmap size is 400K, which is 50 blocks.