Home » RDBMS Server » Server Administration » how to get rowid (10.2.0.1)
how to get rowid [message #557156] |
Sat, 09 June 2012 13:22 |
andy huang
Messages: 498 Registered: July 2011
|
Senior Member |
|
|
Dear all,
How to get the rowids of the first and last row in every extent by dba_extents?
|
|
|
Re: how to get rowid [message #557157 is a reply to message #557156] |
Sat, 09 June 2012 13:27 |
|
Michel Cadot
Messages: 68716 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Try to do it and tell us.
It is a nice exercise on Oracle catalog and SQL, and I will give us for each extent the owner, name and type of the object as well as the extent number, and its file number with its first and last blocks.
Regards
Michel
[Updated on: Sat, 09 June 2012 13:39] Report message to a moderator
|
|
|
Re: how to get rowid [message #557159 is a reply to message #557157] |
Sat, 09 June 2012 14:00 |
andy huang
Messages: 498 Registered: July 2011
|
Senior Member |
|
|
hi,
Michel,is the flowing test right?
create table tb_log
(
id number,
name varchar2(100)
);
declare
begin
for i in 1 .. 100000 loop
insert into tb_log values (i, i);
commit;
end loop;
end;
SQL> select object_id from dba_objects where object_name ='TB_LOG';
OBJECT_ID
----------
70221
SQL> select dbms_rowid.rowid_create(rowid_type => 1,
2 object_number => 70221,
3 relative_fno => file_id,
4 block_number => block_id,
5 row_number => 0) begin_rowid,
6 dbms_rowid.rowid_create(rowid_type => 1,
7 object_number => 70221,
8 relative_fno => file_id,
9 block_number => block_id + blocks,
10 row_number => 0) end_rowid
11 from dba_extents t where t.segment_name = 'TB_LOG'
12 /
BEGIN_ROWID END_ROWID
------------------ ------------------
AAARJNAAEAAAACwAAA AAARJNAAEAAAAC4AAA
AAARJNAAEAAAAC4AAA AAARJNAAEAAAADAAAA
AAARJNAAEAAAADAAAA AAARJNAAEAAAADIAAA
AAARJNAAEAAAADIAAA AAARJNAAEAAAADQAAA
AAARJNAAEAAAADQAAA AAARJNAAEAAAADYAAA
AAARJNAAEAAAADYAAA AAARJNAAEAAAADgAAA
AAARJNAAEAAAADgAAA AAARJNAAEAAAADoAAA
AAARJNAAEAAAADoAAA AAARJNAAEAAAADwAAA
AAARJNAAEAAAADwAAA AAARJNAAEAAAAD4AAA
AAARJNAAEAAAAD4AAA AAARJNAAEAAAAEAAAA
AAARJNAAEAAAAEAAAA AAARJNAAEAAAAEIAAA
BEGIN_ROWID END_ROWID
------------------ ------------------
AAARJNAAEAAAAEIAAA AAARJNAAEAAAAEQAAA
AAARJNAAEAAAAEQAAA AAARJNAAEAAAAEYAAA
AAARJNAAEAAAAEYAAA AAARJNAAEAAAAEgAAA
AAARJNAAEAAAAEgAAA AAARJNAAEAAAAEoAAA
AAARJNAAEAAAAEoAAA AAARJNAAEAAAAEwAAA
AAARJNAAEAAAAGAAAA AAARJNAAEAAAAIAAAA
SQL> select count(1) from hxl.tb_log t
where rowid between 'AAARJNAAEAAAACwAAA'
and 'AAARJNAAEAAAAIAAAA';
COUNT(1)
----------
100000
|
|
|
|
Re: how to get rowid [message #557229 is a reply to message #557163] |
Mon, 11 June 2012 03:18 |
andy huang
Messages: 498 Registered: July 2011
|
Senior Member |
|
|
hi,
Michel, i have found way,is it right?
Declare
l_Job Number;
Begin
Dbms_Output.Put_Line('Begin_Rowid' || '------------' || 'End_Rowid');
For x In (Select Dbms_Rowid.Rowid_Create(1,
Data_Object_Id,
Lo_Fno,
Lo_Block,
0) Min_Rid,
Dbms_Rowid.Rowid_Create(1,
Data_Object_Id,
Hi_Fno,
Hi_Block,
10000) Max_Rid
From (Select Distinct Grp,
First_Value(Relative_Fno) Over(Partition By Grp Order By Relative_Fno, Block_Id Rows Between Unbounded Preceding And Unbounded Following) Lo_Fno,
First_Value(Block_Id) Over(Partition By Grp Order By Relative_Fno, Block_Id Rows Between Unbounded Preceding And Unbounded Following) Lo_Block,
Last_Value(Relative_Fno) Over(Partition By Grp Order By Relative_Fno, Block_Id Rows Between Unbounded Preceding And Unbounded Following) Hi_Fno,
Last_Value(Block_Id + Blocks - 1) Over(Partition By Grp Order By Relative_Fno, Block_Id Rows Between Unbounded Preceding And Unbounded Following) Hi_Block,
Sum(Blocks) Over(Partition By Grp) Sum_Blocks
From (Select Relative_Fno,
Block_Id,
Blocks,
Trunc((Sum(Blocks)
Over(Order By Relative_Fno, Block_Id) - 0.01) /
(Sum(Blocks) Over() / 8)) Grp
From Dba_Extents
Where Segment_Name = Upper('TB_LOG')
And Owner = User
Order By Block_Id)),
(Select Data_Object_Id
From User_Objects
Where Object_Name = Upper('TB_LOG'))) Loop
Dbms_Output.Put_Line(x.Min_Rid || '----' || x.Max_Rid);
End Loop;
End;
Begin_Rowid------------End_Rowid
AAAZWuAABAAAX8AAAA----AAAZWuAABAAAX9/CcQ
AAAZWuAABAAAYAAAAA----AAAZWuAABAAAYD/CcQ
AAAZWuAABAAAX0AAAA----AAAZWuAABAAAX1/CcQ
AAAZWuAABAAAX2AAAA----AAAZWuAABAAAX3/CcQ
AAAZWuAABAAAXuIAAA----AAAZWuAABAAAXyHCcQ
AAAZWuAABAAAX4AAAA----AAAZWuAABAAAX5/CcQ
AAAZWuAABAAAX+AAAA----AAAZWuAABAAAX//CcQ
AAAZWuAABAAAX6AAAA----AAAZWuAABAAAX7/CcQ
[Updated on: Mon, 11 June 2012 03:31] Report message to a moderator
|
|
|
|
Re: how to get rowid [message #557325 is a reply to message #557163] |
Mon, 11 June 2012 21:42 |
andy huang
Messages: 498 Registered: July 2011
|
Senior Member |
|
|
Michel Cadot wrote on Sat, 09 June 2012 14:28NEVER count(1), count(*).
hi,
Michel,what is the difference between count(1) and count(*),which is the better?
|
|
|
|
|
Re: how to get rowid [message #557356 is a reply to message #557330] |
Tue, 12 June 2012 02:18 |
andy huang
Messages: 498 Registered: July 2011
|
Senior Member |
|
|
Thanks,
Michel and BlackSwan, thank you your reply,I am not lazy,i have use GOOGLE before submit the question,in GOOGLE,there are too many pepole say, i was confused by them,i can not distinguish who is right and who is wrong,so i submit the issue in the forum,i think i can get a clear answer in the forum.
|
|
|
|
Re: how to get rowid [message #557363 is a reply to message #557357] |
Tue, 12 June 2012 02:39 |
andy huang
Messages: 498 Registered: July 2011
|
Senior Member |
|
|
Thanks,
Michel,I get the answer in the forum,count(1) and count(*) is no difference. but what does the meaning about what your saying?
NEVER count(1), count(*).
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 29 02:25:16 CST 2024
|