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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #557163 is a reply to message #557159] Sat, 09 June 2012 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NEVER count(1), count(*).
Apart from that, the idea is there but the count in one the questions we didn't ask but you can add to the previous ones.

Note that block "block_id + blocks" is not part of the extent.

Regards
Michel
Re: how to get rowid [message #557229 is a reply to message #557163] Mon, 11 June 2012 03:18 Go to previous messageGo to next message
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 #557241 is a reply to message #557229] Mon, 11 June 2012 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need PL/SQL nor analytical functions, only classic aggregate one.
You only need 1 view: dbs_extents.
The query is far much simpler than the one you posted.

Regards
Michel
Re: how to get rowid [message #557325 is a reply to message #557163] Mon, 11 June 2012 21:42 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Michel Cadot wrote on Sat, 09 June 2012 14:28
NEVER count(1), count(*).


hi,
Michel,what is the difference between count(1) and count(*),which is the better?
Re: how to get rowid [message #557329 is a reply to message #557325] Mon, 11 June 2012 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Michel,what is the difference between count(1) and count(*),which is the better?
STILL too LAZY to use GOOGLE!
why do you insist on being such a DUNCE?

http://www.lmgtfy.com/?q=oracle+count(*)+vs+count(1)

[Updated on: Mon, 11 June 2012 22:40]

Report message to a moderator

Re: how to get rowid [message #557330 is a reply to message #557329] Mon, 11 June 2012 23:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, we answered this many times here, so you can use the Search link in the menu above.

Regards
Michel
Re: how to get rowid [message #557356 is a reply to message #557330] Tue, 12 June 2012 02:18 Go to previous messageGo to next message
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 #557357 is a reply to message #557356] Tue, 12 June 2012 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And as we already clearly answered to this you can find the answer using "Search" link above.

Regards
Michel
Re: how to get rowid [message #557363 is a reply to message #557357] Tue, 12 June 2012 02:39 Go to previous messageGo to next message
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(*).
Re: how to get rowid [message #557365 is a reply to message #557363] Tue, 12 June 2012 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because count(1) is stupid and just show you don't understand what you write.
And this is why Oracle opitmizer transforms the count(1) to count(*) because it thinks the person who writes this does stupidly not understand what he writes.
Is this clear now?

Regards
Michel
Re: how to get rowid [message #557368 is a reply to message #557365] Tue, 12 June 2012 02:58 Go to previous message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks,
Michel!
Previous Topic: grant of create any synonym on a user via DB link
Next Topic: how to recreate controlfile
Goto Forum:
  


Current Time: Thu Mar 28 07:24:28 CDT 2024