--------------------------------------------------------------------------------
--
-- File name: rebuild_table
-- Purpose: Move/Shrink tables to reduce fragmentation. The procedure can be used with both ONLINE/OFFLINE mode.
--
-- Author: Anand Yadav
--
-- Schema: app schema
--
-- Usage: @gen_rebuild_table.sql
--The procedure gen_rebuild_table takes two arguements and generates output script accordingly. First arguements is table name and second arguement is type of operation.
--Permitted values for operation type is "ONLINE" or "OFFLINE". If "OFFLINE", table will be offline for users during move operation.
--
-- Other:
--If oracle EE version is 12.2 then table can be moved online in place of shrink
--------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE gen_rebuild_table (
tab_name IN VARCHAR2,
ops_type IN VARCHAR2 DEFAULT 'ONLINE')
AS
OBJECT_SIZE NUMBER := 0;
DEFAULT_DEGREE NUMBER := 16; --Considering 8 CPU's and enterprise edition
timestart NUMBER;
version VARCHAR2 (20) := NULL;
releas VARCHAR2 (1) := NULL;
-- keyTable user_tables%ROWTYPE;
cnt NUMBER (1) := 0;
pct_val NUMBER := 0;
TYPE keyTableType IS RECORD
(
iot_type user_tables.iot_type%TYPE,
row_movement user_tables.row_movement%TYPE
);
keyTable keyTableType;
BEGIN
DBMS_OUTPUT.PUT_LINE ('SET TIMING ON;');
SELECT CASE
WHEN INSTR (banner, 'Enterprise') > 0 THEN 'Enterprise'
ELSE 'Standard'
END,
CASE WHEN INSTR (banner, '12.2') > 0 THEN '1' ELSE '0' END
INTO version, releas
FROM v$version
WHERE ROWNUM < 2;
SELECT SUM (BYTES) / 1024 / 1024
INTO OBJECT_SIZE
FROM user_segments
WHERE segment_name IN (SELECT TABLE_NAME
FROM USER_TABLES
WHERE TABLE_NAME = tab_name
UNION ALL
SELECT INDEX_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = tab_name);
SELECT pct_free INTO pct_val
FROM user_tables where TABLE_NAME = tab_name;
DBMS_OUTPUT.PUT_LINE (
'--Total size of table '
|| tab_name
|| ' and associated indexes was: '
|| OBJECT_SIZE
|| ' MB');
timestart := DBMS_UTILITY.get_time;
SELECT iot_type,
DECODE (row_movement, 'DISABLED', 'DISABLE', 'ENABLED', 'ENABLE')
row_movement
INTO keyTable
FROM user_tables
WHERE table_name = tab_name;
IF pct_val > 10
THEN
DBMS_OUTPUT.put_line (
' ALTER TABLE ' || tab_name || ' PCTFREE 10'||';');
END IF;
IF UPPER (ops_type) = 'ONLINE'
THEN
IF version = 'Enterprise' AND releas = 1
THEN
DBMS_OUTPUT.put_line (
' ALTER TABLE ' || tab_name || ' MOVE ONLINE PARALLEL '||DEFAULT_DEGREE||';');
ELSE
BEGIN
-- DBMS_OUTPUT.put_line ('New code to support online shrink');
IF keyTable.iot_type IS NULL
AND keyTable.row_movement = 'DISABLE'
THEN
DBMS_OUTPUT.put_line (
'ALTER TABLE ' || tab_name || ' ENABLE ROW MOVEMENT;');
END IF;
DBMS_OUTPUT.put_line (
'ALTER TABLE ' || tab_name || ' SHRINK SPACE COMPACT CASCADE;');
DBMS_OUTPUT.put_line (
'ALTER TABLE ' || tab_name || ' SHRINK SPACE CASCADE;');
IF keyTable.iot_type IS NULL
THEN
DBMS_OUTPUT.put_line (
'ALTER TABLE '
|| tab_name
|| ' '
|| keyTable.row_movement
|| ' ROW MOVEMENT;');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (
'--Failed during shrink: ' || USER || '.' || tab_name);
--DBMS_OUTPUT.PUT_LINE (SQLERRM);
BEGIN
IF keyTable.iot_type IS NULL
THEN
DBMS_OUTPUT.put_line (
'ALTER TABLE '
|| tab_name
|| ' '
|| keyTable.row_movement
|| ' ROW MOVEMENT;');
END IF;
EXCEPTION
WHEN OTHERS
THEN
--DBMS_OUTPUT.PUT_LINE (SQLERRM);
RAISE;
END;
RAISE;
END;
END IF;
ELSE
IF version = 'Enterprise' THEN
DBMS_OUTPUT.put_line (
' ALTER TABLE ' || tab_name || ' MOVE PARALLEL '||DEFAULT_DEGREE||';');
ELSE
DBMS_OUTPUT.put_line (' ALTER TABLE ' || tab_name || ' MOVE;');
END IF;
END IF;
IF pct_val > 10
THEN
DBMS_OUTPUT.put_line (
' ALTER TABLE ' || tab_name || ' PCTFREE '||pct_val||';');
END IF;
IF UPPER (ops_type) = 'OFFLINE'
THEN
FOR ind_name
IN (SELECT index_name,
DECODE (LOGGING, 'YES', 'LOGGING', 'NOLOGGING')
LOGGING_FLAG,
DECODE (DEGREE,
'1', 'NOPARALLEL',
'DEFAULT', 'PARALLEL',
'PARALLEL' || ' ' || DEGREE)
PARALLEL_FLAG
FROM user_indexes
WHERE table_name = tab_name)
LOOP
BEGIN
timestart := DBMS_UTILITY.get_time;
IF version = 'Enterprise'
THEN
DBMS_OUTPUT.put_line (
'ALTER INDEX '
|| ind_name.index_name
|| ' REBUILD PARALLEL '
|| DEFAULT_DEGREE
|| ' NOLOGGING;');
DBMS_OUTPUT.put_line (
'ALTER INDEX '
|| ind_name.index_name
|| ' '
|| ind_name.PARALLEL_FLAG
|| ' '
|| ind_name.LOGGING_FLAG
|| ';');
ELSE
DBMS_OUTPUT.put_line (
'ALTER INDEX '
|| ind_name.index_name
|| ' REBUILD NOLOGGING;');
DBMS_OUTPUT.put_line (
'ALTER INDEX '
|| ind_name.index_name
|| ' '
|| ind_name.LOGGING_FLAG
|| ';');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'--Issue with Index : ' || ind_name.index_name);
-- DBMS_OUTPUT.PUT_LINE (SQLERRM);
CONTINUE;
END;
END LOOP;
END IF;
-- DBMS_OUTPUT.PUT_LINE (
-- 'begin dbms_stats.GATHER_TABLE_STATS('''
-- || USER
-- || ''','''
-- || tab_name
-- || ''','
-- || 'DEGREE=> '
-- || DEFAULT_DEGREE
-- || ',method_opt=> ''for all indexed columns size skewonly'',ESTIMATE_PERCENT=> DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=>TRUE, no_invalidate=>FALSE, force=>TRUE); end;');
-- DBMS_OUTPUT.PUT_LINE ('/');
version := NULL;
releas := NULL;
DBMS_OUTPUT.PUT_LINE ('SET TIMING OFF;');
EXCEPTION
WHEN OTHERS
THEN
version := NULL;
releas := NULL;
DBMS_OUTPUT.put_line ('--Issue with move/shrink table: ' || tab_name);
DBMS_OUTPUT.PUT_LINE ('SET TIMING OFF;');
--DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/
SHOW ERRORS;
SET SERVEROUTPUT ON SIZE UNLIMITED
SET FEEDBACK OFF;
SPOOL C:\D\Cloud\Utility\table_fragmentation\generated_rebuild_script.sql
EXEC gen_rebuild_table('&DUMMY_TABLE','OFFLINE');
--SET SERVEROUTPUT ON SIZE UNLIMITED
--SET FEEDBACK OFF;
--SPOOL C:\D\Cloud\Utility\table_fragmentation\generated_rebuild_script.sql
--begin
--for tab_name in (select table_name from user_tables where table_name in ('PROCESSSKU','DFU','SKU'))
--LOOP
--gen_rebuild_table(tab_name.table_name,'ONLINE');
--END LOOP;
--END;
--/
--
spool off;
DROP PROCEDURE gen_rebuild_table;
