a collection of scripts to ease the day-to-day work of an Oracle DBA
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 
 

47 lines
1.7 KiB

-- =============================================================================
-- This script finds empty partitions (0 rows of data) and tries to de-allocate
-- its unused extents, saving some space
--
-- author: Andreas Itzchak Rehberg
-- run: Just execute in the schema to be cleaned-up.
-- Set v_dry_mode to TRUE to simply generate SQL,
-- set it to FALSE to actually perform the changes.
-- The script only processes the current schema (CURRENT_USER)
-- =============================================================================
DECLARE
v_dry_mode BOOLEAN := FALSE; -- in dry_mode code is only generated and dbms_output'ed
v_current_schema VARCHAR2(30);
CURSOR c_empty_parts IS
SELECT table_name,partition_name
FROM user_tab_partitions
WHERE ( num_rows = 0 OR num_rows IS NULL )
AND subpartition_count = 0
AND segment_created = 'YES';
PROCEDURE do_sql(p_sql VARCHAR2) IS
datum VARCHAR2(20);
BEGIN
IF v_dry_mode THEN
dbms_output.put_line(p_sql||';');
ELSE
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') INTO datum FROM DUAL;
dbms_output.put_line('-- '||datum||':'||CHR(10)||p_sql||';');
EXECUTE IMMEDIATE p_sql;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR executing SQL: '||p_sql);
dbms_output.put_line('ERROR: '||SQLERRM);
END;
BEGIN
v_current_schema := SYS_CONTEXT('USERENV','CURRENT_USER');
FOR rec IN c_empty_tabs LOOP
do_sql('ALTER TABLE '||v_current_schema||'.'||rec.table_name||
'MODIFY PARTITION '||rec.partition_name||
'DEALLOCATE UNUSED');
END LOOP
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR: '||SQLERRM);
END;
/