Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    Paris, France
    Posts
    9

    Question Unanswered: ratio of use of a rollback segment

    Please help me,

    I'm looking for a request (or a PL/SQL block) that helps to know the |ratio of use of a rollback segment for one request.

    Moreover, I also want to know the request that permits to know the number of extents in a table or per table.

    Thanks to all

    Bye

  2. #2
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    "number of extents in a table":

    DBA_EXTENTS
    -----------------

    SELECT count(*)
    FROM dba_extents
    WHERE owner = '<user> ' AND
    segment_name = '<table_name>';

    You can also find the SUM of bytes and blocks for those extents:

    SELECT SUM(bytes), SUM(blocks)
    FROM dba_extents
    WHERE owner = '<user> ' AND
    segment_name = '<table_name>';


    "ratio of use of a rollback segment for one request"

    SELECT a.username,
    b.used_ublk,
    b.start_time
    FROM v$transaction b,
    v$session a
    WHERE b.addr = a.addr;

    it will give you number of blocks used to store the rollback for each transaction. Then you can calculate whtaever ratio you need.

    Check the latest Oracle DBA forums:

    http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi


    Hope that helps,



    Trifon Anguelov
    Senior Oracle DBA

  3. #3
    Join Date
    Apr 2002
    Location
    Paris, France
    Posts
    9

    Perfect

    thanks

    it is exactly what I was looking for

    bye

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •