Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    28

    Unanswered: rows still present after delete & commit?

    am attempting to clear out a production environment prior to an import.
    ran my clear_all.sql script successfully which is just a series of DELETE FROM commands.
    However after logging back into Enterprise Manager the tables in my tablespace are all reporting the same number of rows as before.
    In a sqlplus session any select statement performed on any tables returns 'no rows selected'.
    So i think all my tables are empty but i am confused by Enterprise Manager and i have to be sure as it is a production environment.
    Has anyone seen this before?
    i have logged this with metalink but the response time is quite long.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    First clue, which comes into my mind is: does the script COMMIT in the end or did you COMMIT after running the script?

  3. #3
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Where does Enterprise Manager "report" the number of rows? Are you looking at the stats page? If so, did you regather stats after you did the delete?

    I would believe that a select count(*) from <table_name> query would be more accurate than stats that were gathered some time ago.

  4. #4
    Join Date
    Dec 2007
    Posts
    28
    yep, got commit at the end.
    even rebuilt indexes.

    i was in the administration tab on tables for the relevant schema.
    it shows all tables in the tablespace, whether they are partitioned and how many rows they contain.
    i am now sure it's a problem with Enterprise Manager - and i've just had a response from metalinks. They say to trust the result of a sqlplus query.
    So i've been transferred to the Enterprise Manager section.

  5. #5
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    So, I'm guessing you are in Grid Control, not just the Enterprise Manager client. In Grid Control, the column next to "Rows" is "Last Analyzed". This column indicates the last time the table was analyzed and that stats were updated.

    I am pretty sure that the Rows column is not dynamic, but is the number of rows that were present the last time stats were gathered for the table. If you analyze one of the tables with no rows, the rows column would be updated and accurate.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    try a 'select * from dba_tables' on one of those that you truncated, and see if the NUM_ROWS column matches the count you're getting from Enterprise Manager.

    It would make sense to me that EM would get info from here rather than re-querying tables with a 'SELECT count(*)...', due to the expense.

Posting Permissions

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