DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2000
    I have noticed that I have a couple of tables in our database which experienced massive inserting and deleting on a daily basis.

    For example we have a table calls events_log I run this quest on that :

    select segment_name Table_Name, num_rows Number_of_Rows , sum(bytes)/1024 Size_in_KB
    from user_segments, user_tables
    where table_name = segment_name and
    segment_type = 'TABLE' and
    table_name = 'EVENTS_LOG'
    group by segment_name, num_rows

    It shows:

    EVENTS_LOG 13028 166138

    I think HWM is too high. Then I try Exprt table , import it again ==> Size didn't change and unused space didn't restore!!!!

    Then I try this :

    create table events_log2 as select * from events_log;
    truncate table events_log;
    insert into events_log select * from events_log3;
    analyze table events_log compute statistics;

    Then it works , result of above query this time is :

    EVENTS_LOG4 13445 10240


    1- I read that imp/exp must restore space for HWM high. Why it didn't do that ?
    2- Does my trucate / insert solution fine? Do you suggest that I schedule it to run on table periodically?
    3- What's your approach to this case?

    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Dec 2001
    The export/import will do the job if you (a) use the compress=y option and drop the table before the import, or (b) truncate the table before the import. Either way will cause all the extents to be removed and created as needed.

    The truncate/insert solution is fine. You might want to parallelise it to make it quicker. It's easier to automate this since you can do it all from within PL/SQL.

    If the number of rows is fairly constant over time I don't worry to much. The empty extents will be reused as they are needed. If you've got lots of full table scans you will be reading lots of empty blocks up to the high water mark when the table is less empty.

    You have to weigh up the cost of the extra blocks being read against the downtime of recreating the table. This will give you an indication of how regularly you can or should recreate the table.

    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    Jan 2001


    Won't the indexes get heavily fragmented if he does not truncate or drop and re-create?

    That is a concern for sure.

    I remember when this place was cool.

  4. #4
    Join Date
    Nov 2000
    So I understand that HWM comes to play onlt in FTS time.
    How can I paralleized the truncate/insert solution?
    Does Hanky comment is important. Do I need to rebuild all indexes for these tables everytime?

    An ounce of prevention is worth a pound of cure

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.