Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

2007/08/27

Problem: ORA-10631 errors encountered during "SHRINK SPACE" operation

OEM was nice the other day and offered some Segment Adviser Recommendations to shrink a few tables that had become fragmented. However, when I ran the shrink job, it failed.

PROBLEM:
  • "ALTER TABLE my_test_table SHRINK SPACE;" results in "ORA-10631: SHRINK clause should not be specified for this object"
SOLUTION:
  • Tables that have function-based indexes cannot be compacted using the SHRINK clause
  • Apparently, someone forgot to inform OEM of this fact (thanks the bogus recommendations! :-)
  • See MetaLink Bug No.:5968539
Here's a SQL statement that shows all the tables in a given schema and whether they are candidates for the SHRINK clause:

SELECT   dt.owner, dt.table_name,
         (CASE
            WHEN NVL(ind.cnt, 0) < 1 THEN 'Y'
            ELSE 'N'
         END) AS can_shrink
    FROM dba_tables dt,
         (SELECT   table_name, COUNT(*) cnt
              FROM dba_indexes di
             WHERE index_type LIKE 'FUNCTION-BASED%'
          GROUP BY table_name) ind
   WHERE dt.table_name = ind.table_name(+)
     AND dt.table_name NOT LIKE 'AQ$%'
     AND dt.table_name NOT LIKE 'BIN$%'
     AND dt.owner = 'BRENT'
ORDER BY 1, 2;

2007/05/31

SQL of the Day: How to view progress of DML operations

So, you're running an INSERT/UPDATE/DELETE statement and it's just sitting there processing. Then your boss comes by and says, "When will this be done?". Well now you can tell them.

Run this SQL to show the progress of DML operations:
SELECT SUBSTR(sql_text, 1, 60) "SQL Text",
       rows_processed "Total Rows Processed",
       ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)",
       TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min",
       TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec"
  FROM SYS.v_$sqlarea
 WHERE sql_text LIKE 'INSERT INTO some_table%'
   AND open_versions > 0
   AND rows_processed > 0;