Odi's astoundingly incomplete notes

One thing, Oracle's Enterprise Manager Webapp miserably fails at is executing shrink recommendations that are given by its Automatic Segment Advisor. Here is a little procedure and log table for your SYS schema that does that well.

It shrinks tables and indexes when it saves more than 100MB. It works correctly for tables with function indexes and LOBs. Also for LOBs in securefile storage. It works even for a large number of objects. A failure in one object doesn't prevent work on other objects. It stops during office hours. And you get a nice log of what happened. Run it from a periodic job in your development instance and forget about it.

Interesting that 50 lines of code can do better than the EM.

CREATE TABLE SHRINK_LOG (
  DT    DATE  DEFAULT current_date  NOT NULL,
  TEXT  VARCHAR2(4000)
);

CREATE OR REPLACE PROCEDURE SYS.SHRINK_DB IS
 v_err VARCHAR2(4000);
 v_memento VARCHAR2(4000);
BEGIN
delete from shrink_log where dt < current_date - 90;
insert into shrink_log (text) values ('starting');
commit;

for r in (SELECT segment_owner||'.'||segment_name as t, segment_owner as owner, segment_name as table_name, segment_type
            FROM TABLE (DBMS_SPACE.asa_recommendations ('TRUE', 'FALSE', 'FALSE'))
            where segment_type IN ('TABLE', 'INDEX')
            and reclaimable_space > 100E6
            order by segment_type desc, reclaimable_space asc) loop
  -- stop during the day
  if ((to_number(to_char(current_date, 'HH24')) >= 5) AND (to_number(to_char(current_date, 'HH24')) < 21)) then
    insert into shrink_log (text) values ('exiting');
    commit;
    return;
  end if;
  begin
    v_memento := NULL;
    if r.segment_type='TABLE' then
      for i in (SELECT di.owner||'.'||di.index_name as idx, ie.column_expression FROM dba_indexes di, DBA_IND_EXPRESSIONS ie
                 WHERE di.index_type LIKE 'FUNCTION-BASED%'
                   and di.owner=r.owner and di.table_name=r.table_name
                   and ie.index_owner=di.owner
                   and ie.index_name=di.index_name) loop
        v_memento := v_memento || 'execute immediate ''create index '|| i.idx ||' on '|| r.t ||' ('|| i.column_expression ||')'';';
        execute immediate 'drop index '|| i.idx;
      end loop;
      execute immediate  'alter table '|| r.t ||' enable row movement';
      execute immediate  'alter table '|| r.t ||' shrink space cascade';
      for l in (select l.column_name, l.tablespace_name
                from dba_segments s, dba_lobs l
                where s.segment_name = l.segment_name
                and s.tablespace_name = l.tablespace_name
                and s.owner = r.owner
                and l.table_name = r.table_name
                and s.segment_type='LOBSEGMENT'
                and s.segment_subtype='SECUREFILE') loop
        execute immediate 'ALTER TABLE '|| r.t ||' MOVE LOB('|| l.column_name ||') STORE AS (TABLESPACE '|| l.tablespace_name ||')';
      end loop;
      execute immediate  'alter table '|| r.t ||' disable row movement';
    else 
      -- INDEX
      execute immediate  'alter index '|| r.t ||' shrink space compact cascade';
    end if;
    if v_memento is not null then
      execute immediate 'begin '|| v_memento ||' end';
    end if;
    -- rebuild any UNUSABLE indexes again
    for r in (select * from dba_indexes where status!='VALID' and index_type='NORMAL' and partitioned='NO') loop
      execute immediate 'alter index '|| r.owner ||'.'|| r.index_name ||' rebuild';
    end loop; 
    insert into shrink_log (text) values (r.t||': OK');
  exception
    when others then
      v_err := substr(SQLERRM, 1, 3900);
      insert into shrink_log (text) values (r.t||': '||v_Err);
  end;
  commit;
end loop;
insert into shrink_log (text) values ('complete');
commit;
END SHRINK_DB;
/

posted on 2015-05-19 15:35 UTC in Code | 2 comments | permalink