- 浏览: 187645 次
- 性别:
- 来自: 杭州
文章分类
最新评论
First of all let’s create a new tablespace and a table:
SQL> create tablespace my_tbs datafile '/u01/oradata/chenlin/my_tbs.dbf' size 200m ;
Tablespace created.
then:
SQL> create table my_tab tablespace my_tbs as select * from dba_objects ;
Table created.
Now let’s get more information about this table from DBA_SEGMENTS view:
SQL> select header_file, header_block, bytes, blocks, extents from dba_segments where segment_name='MY_TAB';
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
----------- ------------ -------- ---------- ----------
12 17 4194304 1024 19
From the output we can say that the header block of the table is stored in the datafile 12, block 17.
The size of the table 4194304 bytes (4.2 Mb) and it contains 1024 blocks and 19 extents.
After getting block size of the database, let’s do some math:
SQL> show parameter db_block_size
NAME TYPE VALUE
-------------- ----------- --------------------------
db_block_size integer 4096
BLOCKS (dba_segments) * DB_BLOCK_SIZE (parameter file) = BYTES (dba_segments)
so : 1024 * 4096 = 4194304
Now let’s query DBA_EXTENTS view to get some information on extents and data blocks of this table:
SQL> col segment_name format a20
SQL> select segment_name, extent_id, block_id, blocks, bytes from dba_extents where segment_name='MY_TAB';
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS BYTES
-------------------- ---------- ---------- ---------- -------
MY_TAB 0 17 16 65536
MY_TAB 1 33 16 65536
MY_TAB 2 49 16 65536
MY_TAB 3 65 16 65536
MY_TAB 4 81 16 65536
MY_TAB 5 97 16 65536
MY_TAB 6 113 16 65536
MY_TAB 7 129 16 65536
MY_TAB 8 145 16 65536
MY_TAB 9 161 16 65536
MY_TAB 10 177 16 65536
MY_TAB 11 193 16 65536
.............................
19 rows selected.
we do this test by follow info :
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS BYTES
-------------------- --------- --------- ---------- ----------
MY_TAB 0 17 16 65536
MY_TAB 1 33 16 65536
Here we see that the first extent (0) contains 16 blocks which id range between 17 and 33 ,
so: 17 (block_id) + 16 (number of blocks) = 33 (block_id of the next extent)
And now let’s start dumping the header block of the table. For this we need data file and header block number.
It can be taken from the DBA_SEGMENTS
SQL> select header_file, header_block from dba_segments where segment_name='MY_TAB';
HEADER_FILE HEADER_BLOCK
----------- ------------
12 17
See more about dba_segments:
http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4097.htm#i1626854
Now, dump the data block 12 of the datafile 17:
notice :you should rm your udump file by force first , and relogin again
SQL> alter system dump datafile 12 block 17;
System altered.
If you use SecureCRT , you can use the command : rz or sz to download or upload files
Clone a new session and change to oracle user by using : su - oracle
[oracle@chance udump]$ ll
total 4
-rw-r----- 1 oracle oinstall 2106 May 6 22:15 chenlin_ora_7831.trc
[oracle@chance udump]$sz chenlin_ora_7831.trc
rz
Starting zmodem transfer. Press Ctrl+C to cancel.
Transferring chenlin_ora_7831.trc...
100% 2 KB 2 KB/s 00:00:01 0 Errors
Let's find the file chenlin_ora_7831.trc at C:\Users\Administrator\Downloads and see the infomation in it :
#---------------------------------begin--------------------------
/u01/admin/chenlin/udump/chenlin_ora_7831.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /u01/oracle
System name:Linux
Node name:chance
Release:2.6.9-89.EL
Version:#1 Mon Jun 22 12:19:40 EDT 2009
Machine:i686
Instance name: chenlin
Redo thread mounted by this instance: 1
Oracle process number: 14
Unix process pid: 7831, image: oracle@chance (TNS V1-V3)
*** SESSION ID:(11.17) 2012-05-06 22:15:42.270
Start dump data blocks tsn: 14 file#: 12 minblk 17 maxblk 17
buffer tsn: 14 rdba: 0x03000011 (12/17)
scn: 0x0000.000a40da seq: 0x01 flg: 0x04 tail: 0x40da1001
frmt: 0x02 chkval: 0x28a8 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 19 #blocks: 1023
last map 0x00000000 #maps: 0 offset: 2080
Highwater:: 0x03000351 ext#: 18 blk#: 64 ext size: 256
#blocks in seg. hdr's freelists: 0
#blocks below: 831
mapblk 0x00000000 offset: 18
Unlocked
Map Header:: next 0x00000000 #extents: 19 obj#: 30558 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x03000012 length: 15
0x03000021 length: 16
0x03000031 length: 16
0x03000041 length: 16
0x03000051 length: 16
0x03000061 length: 16
0x030000c1 length: 16
0x030000d1 length: 16
0x030000e1 length: 16
0x030000f1 length: 16
0x03000101 length: 16
0x03000111 length: 256
0x03000211 length: 256
0x03000311 length: 256
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
End dump data blocks tsn: 14 file#: 12 minblk 17 maxblk 17
#---------------------------------------end------------------------------------------
* Start dump data blocks tsn: 14 file#: 12 minblk 17 maxblk 17
This means that the following lines is the output of datafile 17, block 17. Range of blocks can be given to dump:
SQL> alter system dump datafile 1 block min 1 block max 17
* rdba: 0x03000011
rdba is the Data Block Address which is internal representation of the block address.The first 10 bits contains file number, and the rest 22 bits contains block id.
if we want to convert 0x03000011 to decimal, we would get 12/17 which is shown between brackets.
Moreover, DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE and DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK can be used to get the file and block number based on RDA value. But before using these functions, rda should be converted to the decimal value.
So here, we convert the rda value (0x03000011) to decimal and got 50331665.
In order to convert hexadecimal value to the decimal online, you can use the following link:http://www.statman.info/conversions/hexadecimal.html
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(50331665) as fileNo , DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(50331665) as blockNo from dual;
FILENO BLOCKNO
---------- ----------
12 17
By using DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS function and passing file and block value, we can get RDA value:
SQL> select DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(12,17) from dual;
DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(12,17)
-------------------------------------------
50331665
* scn: 0x0000.000a40dais the SCN value of the block which defines when it was last modified. We can compare it with each transaction SCN value inside the dump file. The SCN value of the data block is 0×0000.02eeaf02. If second part of this value (02eeaf02) is converted to the decimal, then we got – 49196802. If we query the current_scn value we got:
The SCN value of the data block is 0×0000.02eeaf02.
If second part of this value (000a40da) is converted to the decimal, then we got – 671962. If we query the current_scn value we got:
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
683658
* tail is used to keep track of consistency information between the beginning and end of the block against the possibility of distribution of oracle blocks over multiple OS blocks
* frmt – is the block format which tells whether it’s Oracle 9 or Oracle 10 and higher block. 0×02 represents that it’s Oracle 8 and higher block. (0×01 represents that it’s Oracle 7 block)
* chkval – which is checksum written to the blocks when it is set and used by Oracle in part to check the consistency and validity of the block, and you show set the parameter db_block_checksum to true in init.ora file
* type : Type defines which type of block it is. In this example it is “Data Segment Header”
* Extent Header: spare1: 0 spare2: 0 #extents: 19 #blocks: 1023
Then in the Extent Control Header part we can get information about extents. So it has 19 extents and 1023 blocks.
* Highwater: 0x03000351 ext#: 18 blk#: 64 ext size: 256
#blocks in seg. hdr's freelists: 0
#blocks below: 831
Highwater mark ( 50332497 ) is at extent 18, block 64. This extent has 256 blocks. And there’re 831 data block below it. Let’s do some math:
As it has 831 blocks below and the number of block is 1023 (dump file didn’t count the header block), then there’re 1023-831=192 data blocks free (and above the hightwater mark).
Moreover, as the highwater mark is at block 64 and the extent has 256 data blocks, then 256-64 = 192 data blocks are free,
So this means that there’s 192*4096 = 786432 bytes (768Kb) free space left
* Map Header: next 0x00000000 #extents: 19 obj#: 30558 flag: 0x40000000
Next, we got object id. To find more details on that object, query sys.obj# view:
SQL> set lines 180
SQL> select obj#, owner#, name, status, ctime, type# from sys.obj$ where obj#=30558;
OBJ# OWNER# NAME STATUS CTIME TYPE#
------- ------- ------- ---------- --------- ----
30558 0 MY_TAB 1 06-MAY-12 2
SQL> select username from dba_users where user_id=0;
USERNAME
------------------------------
SYS
So we can see the owner id is 0 (SYS user) and type is 2 (it means table)
In order to show the free space, we can use DBMS_SPACE.UNUSED_SPACE procedure. Let’s create procedure :
SQL> 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 )
authid current_user
as
l_free_blks number;
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(p_label,40,'.') ||
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( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end show_space;
/
SQL> set serveroutput on
SQL> exec show_space('MY_TAB','SYS');
Total Blocks............................1024
Total Bytes.............................4194304
Total MBytes............................4
Unused Blocks...........................192
Unused Bytes............................786432
Last Used Ext FileId....................12
Last Used Ext BlockId...................785
Last Used Block.........................64
PL/SQL procedure successfully completed.
As it has 831 blocks below and the number of block is 1023 (dump file didn’t count the header block), then there’re 1023-831=192 data blocks free (and above the hightwater mark)
Moreover, as the highwater mark is at block 64 and the extent has 256 data blocks, then 256-64 = 192 data blocks are free,
So this means that there’s 192*4096 = 786432 bytes (768Kb) free space left .
As you see comparison of both values led us to the same results .
So in this post you’ve learned how to dump a header block of the segment and how to read it.
ps: 如果你想真正知道oracle的block块里面真正有些什么,请耐心读完这篇文章~
发表评论
-
oracle数组游标批量插入
2013-12-07 16:40 3351declare type ary is table ... -
使用PL/SQL DEVELOPER 经常会碰到“DYNAMIC PERFORMANCE TABLES NOT ACCESSIBLE”错误
2012-10-16 08:45 899在使用PL/SQL Developer工具登陆一个新 ... -
buffer busy waits 等待事件(热点块竞争)
2012-08-31 16:31 1699*************************** ... -
ora-00054:resource busy and acquire with nowait specified解决方法
2012-06-16 09:35 908当某个数据库用户在数据库中插入、更新、删除一个表的数据, ... -
Oracle 9i 使用Statpack snap 步骤和说明
2012-06-14 08:40 17131、telnet到远程的数据库服务器 CMD>t ... -
oracle Statspack 报告解析之 Shared Pool Statistics(共享池统计信息)
2012-06-21 12:57 1186Shared Pool Statistics ... -
ORACLE里锁模式v$locked_object , locked_mode
2012-06-16 09:37 1412ORACLE里锁有以下几种模式: 0:none ... -
Oracle全面优化:如何对Oracle进行系统的全面优化?
2012-06-13 08:36 1155数据库系统和操作系统一样,在计算机上安装成功后,还需要进 ... -
oracle 参数 FAST_START_MTTR_TARGET
2012-06-14 08:41 1245参数FAST_START_MTTR_TARGET ... -
ORA-12519: TNS:no appropriate service handler found 解决
2012-06-15 08:35 812ORA-12519: TNS:no appropriat ... -
oracle 10g dataguard搭建步骤
2012-06-17 08:49 1684一、主库操作 1、修改主库属性: alter sys ... -
oracle Statspack 报告解析之 Top 5 Timed Events(等待事件)
2012-06-20 08:29 1965常见等待事件说明: ... -
index cluster/hash cluster/sort hash cluster
2012-06-15 08:36 1232簇是一个或多个表的组合,这些表的数据存储在相同的数据块 ... -
Oracle flashback几种方式
2012-06-20 08:26 661flashback(闪回)是Oracle10 ... -
ORACLE Audit 审计
2012-06-21 12:58 13101、什么是审计 审计(A ... -
Oracle建立用户 ,限制用户访问资源
2012-06-14 08:38 1062一、目的: Oracle系 ... -
oracle block 格式
2012-06-19 08:35 1056create table t(n number); ... -
oracle Statspack 报告解析之 Load Profile
2012-06-21 12:57 2057该部分提供每秒和每个事物的统计信息,是监控系统吞吐量和负载变化 ... -
数据库突然变慢,怎么办?
2012-06-16 09:38 2841第一章 检查系统的状态 1.1 使用sar来检查操作系 ... -
oracle library cache 中的hash算法原理
2012-06-18 08:24 1085详细文章:http://bkeep.blog.163.com/ ...
相关推荐
Oracle数据块结构分析说明BLOCK结构详解,讲解块结构,并且附有实例说明,对块的存储方式进行详细说明
oracle数据块解析
NULL 博文链接:https://hbyuan.iteye.com/blog/850709
Oracle数据块原理分析
讲解块结构,并且附有实例说明,对块的存储方式进行详细说明
对data_block物理结构的认识,详细介绍data block的结构
oracle数据块损坏及恢复,模拟了坏块报错,调整事件绕过坏块
oracle 11g 数据文件头block 1解析 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ ##powered by :黄林杰_Huanglinjie ##version : 2023-v11 ##联系方式:17767151782 ##blog: https://blog.csdn.net/lixora/ ##info: ...
Oracle数据块原理深入理解,带你了解ORACLE底层的知识
全国省市区oracle数据结构包含上下级结构可以做树查询
Oracle数据块损坏知识Oracle数据块损坏知识Oracle数据块损坏知识Oracle数据块损坏知识Oracle数据块损坏知识
利用delphi 7通过Oracle.Data.Access组件开发oracle数据库表清理数据操作及运行工具含Oracle.Data.Access.Components.v6.80.0.47.FS.forD5-D2009组件及安装方法 里面有自我理解的oracle清理数据思路 运行平台:...
oracle数据仓库国宝级资料(全套) 1、Oracle+10g数据仓库实践--数据仓库基础.pdf 2、Oracle+10g数据仓库实践--总体方案.pdf 3、Oracle+10g数据仓库实践--方案...8、Oracle+10g数据仓库实践--数据挖掘(DATAMINING).pdf
很简答很详细的讲解Oracle的一款资料,值得去看
想了解oracle数据字典的,就赶紧下载吧
oracle 11g data guard
BBED(Oracle Block Brower and EDitor Tool),用来直接查看和修改数据文件数据的一个工具,是Oracle一款内部工具,可以直接修改Oracle数据文件块的内容,在一些极端恢复场景下比较有用。
批量操作插入oracle数据集的Oracle.DataAccess.dll,区分x64与x86 各种版本
oracle数据字典中各列字段的官方解析
Oracle Database 10g Data Warehousing Oracle 数据仓库