Shulou(Shulou.com)06/02 Report--

1, create a test table, test, and insert 10000 rows of data

SQL > create table test (id int)

SQL > begin

2 for i in 1..10000 loop

3 insert into test values (I)

4 end loop

5 end

6 /

SQL > commit

2, create a stored procedure SHOW_SPACE:

File:

Show_spaceprocedure.rar

Size:

0KB

Download:

download

Create or replace procedure show_space

(p_segname in varchar2

P_owner in varchar2 default user

P_type in varchar2 default 'TABLE'

P_partition in varchar2 default NULL)

As

L_total_blocks number

L_total_bytes number

L_unused_blocks number

L_unused_bytes number

L_LastUsedExtFileId number

L_LastUsedExtBlockId number

L_last_used_block number

Procedure p (p_label in varchar2, p_num in number)

Is

Begin

Dbms_output.put_line (rpad (paired labeld40pr.') | |

P_num)

End

Begin

Dbms_space.unused_space

(segment_owner = > p_owner

Segment_name = > p_segname

Segment_type = > p_type

Partition_name = > p_partition

Total_blocks = > l_total_blocks

Total_bytes = > l_total_bytes

Unused_blocks = > l_unused_blocks

Unused_bytes = > l_unused_bytes

Last_used_extent_file_id = > l_LastUsedExtFileId

Last_used_extent_block_id = > l_LastUsedExtBlockId

Last_used_block = > l_last_used_block)

P ('Total Blocks', l_total_blocks)

P ('Total Bytes', l_total_bytes)

P ('Unused Blocks', l_unused_blocks)

P ('Unused Bytes', l_unused_bytes)

P ('LastUsedExtFileId', l_LastUsedExtFileId)

P ('LastUsedExtBlockId', l_LastUsedExtBlockId)

P ('Last Used Block', l_last_used_block)

End

/

3. Check the space usage of table test:

SQL > exec show_space ('TEST')

Total Blocks..24

Total Bytes..196608

Unused Blocks..3

Unused Bytes..24576

Last Used Ext FileId.1

Last Used Ext BlockId.62177

Last Used Block.5

From the above, the table test occupies a total of 24 data blocks, 196608 bytes, and the file ID is 1.

4. Get the distribution of the table test in the data block:

SQL > select fjorb from (

2 select dbms_rowid.rowid_relative_fno (rowid) f

3 dbms_rowid.rowid_block_number (rowid) b

4 from test) group by fjorb order by b

F B

--

1 62162

1 62163

1 62164

1 62165

1 62166

1 62167

1 62168

1 62169

1 62170

1 62171

1 62172

1 62173

1 62174

1 62175

1 62176

1 62177

16 rows selected.

Thus, the data in the table test occupies a total of 16 blocks, but in the previous step 3, it was found that the table occupied 24 blocks. This is normal because oracle itself uses eight data blocks to record additional information such as segment headers, bitmap blocks, and so on. All we need to know now is that the table test occupies a total of 24 data blocks, of which 16 are data and 8 are table information.

5. Check for updates to x$bh and v$bh:

SQL > select file#,dbablk,tch from x$bh where bj=

2 (select data_object_id from dba_objects

3 where wner='SYS' and object_name='TEST')

4 order by dbablk

FILE# DBABLK TCH

1 62161 6

1 62162 3

1 62163 3

1 62164 3

1 62165 3

1 62166 3

1 62167 3

1 62168 3

1 62169 3

1 62170 3

1 62171 3

1 62172 3

1 62173 3

1 62174 3

1 62175 3

1 62176 3

1 62177 3

1 62178 3

1 62179 3

1 62180 3

1 62181 3

21 rows selected.

SQL > select file#,block#,status from v$bh where bjd=

2 (select data_object_id from dba_objects

3 where wner='SYS' and object_name='TEST')

4 order by block#

FILE# BLOCK# STATUS

1 62161 xcur

1 62162 xcur

1 62163 xcur

1 62164 xcur

1 62165 xcur

1 62166 xcur

1 62167 xcur

1 62168 xcur

1 62169 xcur

1 62170 xcur

1 62171 xcur

1 62172 xcur

1 62173 xcur

1 62174 xcur

1 62175 xcur

1 62176 xcur

1 62177 xcur

1 62178 xcur

1 62179 xcur

1 62180 xcur

1 62181 xcur

21 rows selected.

Here, you can see that the data blocks obtained in v$bh and x$bh are 21 records from 62161 to 62181, but in the fourth step, we know that the data occupies 16 databases from 62162to 62177. Here, 62161 data blocks store segment header information, which can be verified by the following command:

SQL > select header_file,header_block from dba_segments

2 where wner='SYS' and segment_name='TEST'

HEADER_FILE HEADER_BLOCK

--

1 62161

In the v$bh view, we can see that the 21 data blocks are in xcur state, indicating that these data blocks are exclusive and are being used. There are other types of this field, see the state type of the data block.

(

Oracle buffer block (data block) state type

The buffer block management mechanism of oracle has never been officially released, so many experienced oracle engineers infer the buffer block management mechanism of oracle through experience or comments in oracle documents.

In fact, oralce uses the v$bh view to record information related to data buffering (data buffer), which records in detail the state information of each block (data block) in the data cache.

In the status field in the v$bh view, the status of the data block is recorded. In a cluster environment such as non-OPS and non-RAC, the status of the data block will be one of the following: xcur,cr,read,free. You can get the status information of the database with the following command:

SQL > select unique status from v$bh

The meaning of its state is as follows:

Xcur: (exclusive current), indicating that the data block is in exclusive mode

Cr: indicates that the data block is a clone database that can perform shared read-only operations

Free: indicates that this is a restricted block, which oracle is not using right now

Read: indicates that the block is reading data from disk

Write: indicates that the database is writing data to disk

During database recovery, there are two other descriptions for this field: mrec and irec:

Mrec: (media recovery) indicates that the data block is in media recovery mode

Irec: (instance recovery) indicates that the data block is in instance recovery mode

In a RAC environment, there is another mode for data blocks:

Scur (shared current), which indicates that the database is sharing data with other instances.

)

6. Clear the data cache:

SQL > alter system flush buffer_cache

(in Oracle9i, Oracle provides an internal event to force a refresh of the Buffer Cache, whose syntax is:

Alter session set events' immediate trace name flush_cache level 1'

Or:

Alter session set events = 'immediate trace name flush_cache'

Similarly, you can use alter system system-level settings:

Alter system set events = 'immediate trace name flush_cache'

In Oracle10g, Oracle provides a new feature that refreshes Buffer Cache with the following command:

Alter system flush buffer_cache

)

7. Re-check the contents of v$bh and x$bh:

SQL > select file#,dbablk,tch from x$bh where bj=

2 (select data_object_id from dba_objects

3 where wner='SYS' and object_name='TEST')

4 order by dbablk

FILE# DBABLK TCH

1 62161 0

1 62162 0

1 62163 0

1 62164 0

1 62165 0

1 62166 0

1 62167 0

1 62168 0

1 62169 0

1 62170 0

1 62171 0

1 62172 0

1 62173 0

1 62174 0

1 62175 0

1 62176 0

1 62177 0

1 62178 0

1 62179 0

1 62180 0

1 62181 0

21 rows selected.

SQL > select file#,block#,status from v$bh where bjd=

2 (select data_object_id from dba_objects

3 where wner='SYS' and object_name='TEST')

4 order by block#

FILE# BLOCK# STATUS

1 62161 free

1 62162 free

1 62163 free

1 62164 free

1 62165 free

1 62166 free

1 62167 free

1 62168 free

1 62169 free

1 62170 free

1 62171 free

1 62172 free

1 62173 free

1 62174 free

1 62175 free

1 62176 free

1 62177 free

1 62178 free

1 62179 free

1 62180 free

1 62181 free

21 rows selected.

At this time, we can see that the tch field in x$bh has changed from 3 to 0, and the block state of the v$bh view has also changed to free, but the recorded data block has not changed, still in the data blocks of 62161-62181, that is to say, although the data has been written to disk, the pointer of the database record has not been cleared, just its state has changed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.