Database Monitoring
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Database Monitoring

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Hi Gurus ,

    Situation Here is we have increased initial extents of each table to 10 MB in Production server . now we would like to monitor how much space has been occupied in each table .
    could u suugest me the sql query .

    Thanks in advance .
    siva prakash
    DBA

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    use dbms_space to find real usage, or free blocks in your segments

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    could u suggest me the dictionary table name or the sql query

    thanks
    siva prakash
    DBA

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    dbms_space is a package

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    Oracle DBA?

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    ok ok let's dont be sarcastic

  7. #7
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Hi stecal ,
    (senior member)

    I very well know that DBMS_space is a package . i was looking for the dictionary tables from which this package is prepared . Anyway sorry for not projecting the problem the way i wanted.
    siva prakash
    DBA

  8. #8
    Join Date
    May 2002
    Posts
    37
    You can use this SQL to monitor table size and #rows by schema.

    SELECT substr(OWNER||'.'||TABLE_NAME,1,35) "Table",
    ceil(decode (blocks,0,initial_extent*min_extents/(1024*1024),
    null,initial_extent*min_extents/(1024*1024),
    (initial_extent*min_extents + ((blocks*4096) - (initial_extent*min_extents)))
    /(1024*1024))) "Size (MB)" , NUM_ROWS
    FROM DBA_TABLES
    WHERE OWNER in ('')
    and ceil(decode (blocks,0,initial_extent*min_extents/(1024*1024),
    null,initial_extent*min_extents/(1024*1024),
    (initial_extent*min_extents + ((blocks*4096) - (initial_extent*min_extents)))
    /(1024*1024))) <= 4
    ORDER BY 2 desc

    Replace with actual schema name and blocks*4096 with your db_block_size.

    Regards.

  9. #9
    Join Date
    Dec 2001
    Posts
    320
    hi guys,
    when exactly do we need to increase the initial extent?
    what difference does it make if the initial extent is increased or no?
    another question, if i increase the initial extent and i have set the pctincrease to a number greater than zero, does that mean that the next extents will grow also? ( i mean the extents already created).

    Thanks in advance.

    [Edited by hany on 07-16-2002 at 01:50 AM]

Posting Permissions

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



Click Here to Expand Forum to Full Width