Oracle DBA by Example. Code samples, Scripts, Reference.: August 2015

========================
General
========================
This is an example of reorganize table, and its indexes.

Real life example:
A table holds approx 10,000,000 records.
This is the core table of an application.
The data in this table is subject to massive DML.
Data older than N days is deleted.
The table was not partitioned.
In this example,N equals 30.
Data older than 30 days is deleted on a daily basis. So every 30 days the data in this table is replaced.
The table, which at the beginning occupies approx 6Gb, at the end of the month occupies 15Gb.
This has very bad effect on performance.
The DB is busy reading and writing data blocks, which are only 30% "full", which leads to Sequential Read Waits, to the point were application hangs.

After rebuilding the main table, the application runs smoothly.

========================
Code
========================

The reorganization of the table is a three step process:
A. SHRINK (online) or MOVE (offline)
B. REBUILD INDEXES
C. Gather statistics
The implementation is in PL/SQL code.

========================
Permissions
========================
The package owner should have the following privileges.
A. ALTER ANY INDEX - Permission for rebuilding the Indexes.
B. CREATE ANY TABLE - When rebuilding index online, Oracle creates temporary journal table.

CREATE OR REPLACE
PACKAGE BODY ADMIN_UTIL_PKG IS

-------------------------------------------------
PROCEDURE GATHER_STATS_TABLE(p_table_name USER_TABLES.table_name%TYPE) IS

v_module_name VARCHAR2(30) := 'GATHER_STATS_TABLE';
v_start_date_time DATE;
v_end_date_time DATE;

CURSOR get_index_name_cur(cp_table_name IN USER_TABLES.table_name%TYPE) IS
SELECT index_name FROM USER_INDEXES
WHERE table_name = cp_table_name;

BEGIN

v_start_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG (v_module_name,'Procedure Started at: ' ||
TO_CHAR(v_start_date_time, 'DD/MM/YYYY HH24:MI:SS'));

DBMS_STATS.UNLOCK_TABLE_STATS('USER_A',p_table_name);
UTIL_PKG.WRITE_LOG(v_module_name,'Start Gathering Index Stats on Table:
'||p_table_name);
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'USER_A',
tabname => p_table_name,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
degree => DBMS_STATS.DEFAULT_DEGREE,
granularity => 'AUTO',
cascade => DBMS_STATS.AUTO_CASCADE);
UTIL_PKG.WRITE_LOG(v_module_name,'Finished Gathering Index Stats on Table:
'||p_table_name);

FOR get_index_name_rec IN get_index_name_cur(p_table_name) LOOP
UTIL_PKG.WRITE_LOG(v_module_name,'Start Gathering Index Stats on Index:
'||get_index_name_rec.index_name);
DBMS_STATS.GATHER_INDEX_STATS('USER_A',get_index_name_rec.index_name);
UTIL_PKG.WRITE_LOG(v_module_name,'Finished Gathering Index Stats on Index:
'||get_index_name_rec.index_name);
END LOOP;

--DBMS_STATS.GATHER_INDEX_STATS('USER_A','INDEX_01');
--DBMS_STATS.GATHER_INDEX_STATS('USER_A','INDEX_02');

DBMS_STATS.LOCK_TABLE_STATS('USER_A',p_table_name);

v_end_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Finised Successfully at: ' ||
TO_CHAR(v_end_date_time, 'DD/MM/YYYY HH24:MI:SS'));
UTIL_PKG.WRITE_LOG(v_module_name,'Execution time: ' ||
ROUND( (v_end_date_time-v_start_date_time)*24*60,2) );
EXCEPTION
WHEN OTHERS THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Unexpected Error. Error Details: '||
SQLCODE || ' ' || SQLERRM);
RAISE;
END GATHER_STATS_TABLE;

---------------------------------------
PROCEDURE PREPARE_TABLE_FOR_SHRINK (p_table_name USER_TABLES.table_name%TYPE) IS

v_module_name VARCHAR2(30) := 'PREPARE_TABLE_FOR_SHRINK';
v_row_movement_ind USER_TABLES.row_movement%TYPE;
v_enable_row_movement VARCHAR2(1000);

BEGIN

SELECT ROW_MOVEMENT INTO v_row_movement_ind
FROM USER_TABLES
WHERE table_name = p_table_name;

v_enable_row_movement := 'ALTER TABLE '||p_table_name||' ENABLE ROW MOVEMENT';

IF v_row_movement_ind= 'DISABLED' THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Changing ROW_MOVEMENT to ENABLED for Table: '||
p_table_name);
EXECUTE IMMEDIATE v_enable_row_movement;
END IF;

EXCEPTION
WHEN OTHERS THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Unexpected Error. Error Details: '||
SQLCODE || ' ' || SQLERRM);
RAISE;
END PREPARE_TABLE_FOR_SHRINK;

---------------------------------------

PROCEDURE SHRINK_SPACE_PART_TABLE (p_table_name USER_TABLES.table_name%TYPE) IS
v_module_name VARCHAR2(30) := 'SHRINK_SPACE_PART_TABLE';
v_start_date_time DATE;
v_end_date_time DATE;
v_logging_ind USER_TABLES.logging%TYPE;

v_disable_logging VARCHAR2(1000);
v_enable_logging VARCHAR2(1000);

v_shrink_space VARCHAR2(1000);

CURSOR get_partitions_cur (cp_table_name USER_TABLES.table_name%TYPE) IS
SELECT partition_name FROM USER_TAB_PARTITIONS
WHERE table_name = cp_table_name;

BEGIN

v_disable_logging := 'ALTER TABLE '||p_table_name||' NOLOGGING';
v_enable_logging := 'ALTER TABLE '||p_table_name||' LOGGING';

v_start_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Processing Table '||p_table_name);
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Started at: ' || TO_CHAR(v_start_date_time, 'DD/MM/YYYY HH24:MI:SS'));

SELECT LOGGING INTO v_logging_ind FROM USER_TABLES WHERE table_name = p_table_name;
IF v_logging_ind = 'YES' THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Changing LOGGING to NO for Table '||
p_table_name);
EXECUTE IMMEDIATE v_disable_logging;
END IF;

PREPARE_TABLE_FOR_SHRINK(p_table_name);

FOR get_partitions_rec IN get_partitions_cur(p_table_name) LOOP

v_shrink_space := 'ALTER TABLE '|| p_table_name||' MODIFY PARTITION '||
get_partitions_rec.partition_name ||' SHRINK SPACE';
EXECUTE IMMEDIATE v_shrink_space;

END LOOP;

IF v_logging_ind = 'YES' THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Changing LOGGING back to YES for Table '||
p_table_name);
EXECUTE IMMEDIATE v_enable_logging;
END IF;

v_end_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Finised Successfully at: ' ||
TO_CHAR(v_end_date_time, 'DD/MM/YYYY HH24:MI:SS'));
UTIL_PKG.WRITE_LOG(v_module_name,'Execution time: ' ||
ROUND( (v_end_date_time-v_start_date_time)*24*60,2) );

EXCEPTION
WHEN OTHERS THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Unexpected Error. Error Details: '||
SQLCODE || ' ' || SQLERRM);
RAISE;
END SHRINK_SPACE_PART_TABLE;

------------------------------------------
PROCEDURE SHRINK_SPACE_TABLE (p_table_name USER_TABLES.table_name%TYPE) IS
v_module_name VARCHAR2(30) := 'SHRINK_SPACE_TABLE';
v_start_date_time DATE;
v_end_date_time DATE;

v_logging_ind USER_TABLES.logging%TYPE;
v_disable_logging VARCHAR2(1000);
v_enable_logging VARCHAR2(1000);
v_shrink_space VARCHAR2(1000);

BEGIN

v_start_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Processing Table '||p_table_name);
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Started at: ' || TO_CHAR(v_start_date_time, 'DD/MM/YYYY HH24:MI:SS'));

SELECT LOGGING INTO v_logging_ind FROM USER_TABLES WHERE table_name = p_table_name;
v_disable_logging := 'ALTER TABLE '||p_table_name||' NOLOGGING';
v_enable_logging := 'ALTER TABLE '||p_table_name||' LOGGING';

IF v_logging_ind = 'YES' THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Changing LOGGING to NO for Table '||p_table_name);
EXECUTE IMMEDIATE v_disable_logging;
END IF;

PREPARE_TABLE_FOR_SHRINK(p_table_name);
v_shrink_space := 'ALTER TABLE '|| p_table_name||' SHRINK SPACE';
EXECUTE IMMEDIATE v_shrink_space;

IF v_logging_ind = 'YES' THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Changing LOGGING back to YES for Table '||p_table_name);
EXECUTE IMMEDIATE v_enable_logging;
END IF;

v_end_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Finised Successfully at: ' || TO_CHAR(v_end_date_time, 'DD/MM/YYYY HH24:MI:SS'));
UTIL_PKG.WRITE_LOG(v_module_name,'Execution time: ' ||
ROUND( (v_end_date_time-v_start_date_time)*24*60,2) );

EXCEPTION
WHEN OTHERS THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Unexpected Error. Error Details: '||SQLCODE || ' ' || SQLERRM);
RAISE;
END SHRINK_SPACE_TABLE;
--------------------------------------

------------------------------------------
PROCEDURE REBUID_ONLINE_PART_INDEX(p_table_name IN USER_TABLES.table_name%TYPE) IS
v_module_name VARCHAR2(30) := 'REBUID_ONLINE_PART_INDEX';
v_start_date_time DATE;
v_end_date_time DATE;

v_rebuild_ix_sql VARCHAR2(1000);

CURSOR get_index_name_cur(cp_table_name IN USER_TABLES.table_name%TYPE) IS
SELECT USER_IND_PARTITIONS.index_name AS index_name,
USER_IND_PARTITIONS.partition_name AS partition_name
FROM USER_IND_PARTITIONS, USER_PART_INDEXES
WHERE USER_PART_INDEXES.table_name = 'SFI_CUSTOMER_OPTIONS'
AND USER_IND_PARTITIONS.index_name = USER_PART_INDEXES.index_name
ORDER BY USER_IND_PARTITIONS.index_name, USER_IND_PARTITIONS.partition_name;
BEGIN

-- 'ALTER INDEX FI_CUSTOMER_OPTIONS_PK REBUILD PARTITION SYS_P521 ONLINE';
-- 'ALTER INDEX FI_CUSTOMER_OPTIONS_PK REBUILD PARTITION SYS_P522 ONLINE';
-- 'ALTER INDEX FI_CUSTOMER_OPTIONS_PK REBUILD PARTITION SYS_P523 ONLINE';
-- 'ALTER INDEX FI_CUSTOMER_OPTIONS_PK REBUILD PARTITION SYS_P524 ONLINE';
-- 'ALTER INDEX FI_CUSTOMER_OPTIONS_PK REBUILD PARTITION SYS_P525 ONLINE';
-- 'ALTER INDEX FI_CUSTOMER_OPTIONS_PK REBUILD PARTITION SYS_P526 ONLINE';
-- 'ALTER INDEX FI_CUSTOMER_OPTIONS_PK REBUILD PARTITION SYS_P527 ONLINE';
-- 'ALTER INDEX FI_CUSTOMER_OPTIONS_PK REBUILD PARTITION SYS_P528 ONLINE';

UTIL_PKG.WRITE_LOG(v_module_name,'Handling Partitioned Indexes for table: ' || p_table_name);
v_start_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Started at: ' || TO_CHAR(v_start_date_time, 'DD/MM/YYYY HH24:MI:SS'));

FOR get_index_name_rec IN get_index_name_cur(p_table_name) LOOP
UTIL_PKG.WRITE_LOG(v_module_name,'Starting Rebuild Index: ' || get_index_name_rec.index_name||' Partition: '||get_index_name_rec.partition_name);
v_rebuild_ix_sql := 'ALTER INDEX '||get_index_name_rec.index_name||' REBUILD PARTITION '||get_index_name_rec.partition_name||' ONLINE';
EXECUTE IMMEDIATE v_rebuild_ix_sql;
END LOOP;

v_end_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Finised Successfully at: ' || TO_CHAR(v_end_date_time, 'DD/MM/YYYY HH24:MI:SS'));
UTIL_PKG.WRITE_LOG(v_module_name,'Execution time: ' ||
ROUND( (v_end_date_time-v_start_date_time)*24*60,2) );

EXCEPTION
WHEN OTHERS THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Unexpected Error. Error Details: '||SQLCODE || ' ' || SQLERRM);
RAISE;
END REBUID_ONLINE_PART_INDEX;

------------------------------------------
PROCEDURE REBUID_ONLINE_INDEX(p_table_name IN USER_TABLES.table_name%TYPE) IS
v_module_name VARCHAR2(30) := 'REBUID_ONLINE_INDEX';
v_start_date_time DATE;
v_end_date_time DATE;

v_rebuild_ix_sql VARCHAR2(1000);

CURSOR get_index_name_cur(cp_table_name IN USER_TABLES.table_name%TYPE) IS
SELECT index_name FROM USER_INDEXES
WHERE table_name = cp_table_name;

BEGIN

--'ALTER INDEX SSU_MSISDN_IDX REBUILD ONLINE';
--'ALTER INDEX SSU_IMSI_IDX REBUILD ONLINE';
UTIL_PKG.WRITE_LOG(v_module_name,'Handling Indexes for table: ' || p_table_name);
v_start_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Started at: ' || TO_CHAR(v_start_date_time, 'DD/MM/YYYY HH24:MI:SS'));

FOR get_index_name_rec IN get_index_name_cur(p_table_name) LOOP
UTIL_PKG.WRITE_LOG(v_module_name,'Starting Rebuild Index: ' || get_index_name_rec.index_name);
v_rebuild_ix_sql := 'ALTER INDEX '||get_index_name_rec.index_name||' REBUILD ONLINE';
EXECUTE IMMEDIATE v_rebuild_ix_sql;
END LOOP;

v_end_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Finised Successfully at: ' || TO_CHAR(v_end_date_time, 'DD/MM/YYYY HH24:MI:SS'));
UTIL_PKG.WRITE_LOG(v_module_name,'Execution time: ' ||
ROUND( (v_end_date_time-v_start_date_time)*24*60,2) );

EXCEPTION
WHEN OTHERS THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Unexpected Error. Error Details: '||SQLCODE || ' ' || SQLERRM);
RAISE;
END REBUID_ONLINE_INDEX;

--------------------------------------------
-- OFFLINE - not used - just a reference code
--------------------------------------------
PROCEDURE REBUILD_OFFLINE_TABLE IS
v_module_name VARCHAR2(30) := 'REBUILD_OFFLINE_TABLE';
v_start_date_time DATE;
v_end_date_time DATE;

v_move_tbs VARCHAR2(1000);

BEGIN
v_move_tbs := 'ALTER TABLE MAIN_TABLE MOVE';

v_start_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Started at: ' || TO_CHAR(v_start_date_time, 'DD/MM/YYYY HH24:MI:SS'));

EXECUTE IMMEDIATE v_move_tbs;

v_end_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Finised Successfully at: ' || TO_CHAR(v_end_date_time, 'DD/MM/YYYY HH24:MI:SS'));
UTIL_PKG.WRITE_LOG(v_module_name,'Execution time: ' ||
ROUND( (v_end_date_time-v_start_date_time)*24*60,2) );

EXCEPTION
WHEN OTHERS THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Unexpected Error. Error Details: '||SQLCODE || ' ' || SQLERRM);
RAISE;
END REBUILD_OFFLINE_TABLE;
--------------------------------------------
PROCEDURE DROP_INDEXES IS
v_module_name VARCHAR2(30) := 'DROP_INDEXES';
v_start_date_time DATE;
v_end_date_time DATE;

v_drop_index_01 VARCHAR2(1000);
v_drop_index_02 VARCHAR2(1000);

BEGIN
v_drop_index_01 := 'DROP INDEX MY_TABLE_IDX_01';
v_drop_index_02 := 'DROP INDEX MY_TABLE_IDX_02';

v_start_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Started at: ' || TO_CHAR(v_start_date_time, 'DD/MM/YYYY HH24:MI:SS'));

EXECUTE IMMEDIATE v_drop_index_01;
EXECUTE IMMEDIATE v_drop_index_02;

v_end_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Finised Successfully at: ' || TO_CHAR(v_end_date_time, 'DD/MM/YYYY HH24:MI:SS'));
UTIL_PKG.WRITE_LOG(v_module_name,'Execution time: ' ||
ROUND( (v_end_date_time-v_start_date_time)*24*60,2) );

EXCEPTION
WHEN OTHERS THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Unexpected Error. Error Details: '||SQLCODE || ' ' || SQLERRM);
RAISE;
END DROP_INDEXES;
--------------------------------------------
PROCEDURE CREATE_INDEXES IS
v_module_name VARCHAR2(30) := 'CREATE_INDEXES';
v_start_date_time DATE;
v_end_date_time DATE;

v_create_ix_sga_sub1 VARCHAR2(1000);
v_create_ix_sga_sub2 VARCHAR2(1000);

BEGIN
v_create_ix_sga_sub1 := 'CREATE INDEX MY_TABLE_IDX_01 ON MAIN_TABLE (IMSI) '||
' PCTFREE 10 INITRANS 2 MAXTRANS 255 '||
' STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) '||
' TABLESPACE IGT_INDEX';

v_create_ix_sga_sub2 := 'CREATE UNIQUE INDEX MY_TABLE_IDX_02 ON MAIN_TABLE (MSISDN) '||
' PCTFREE 10 INITRANS 2 MAXTRANS 255 '||
' STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) '||
' TABLESPACE IGT_INDEX';

v_start_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Started at: ' || TO_CHAR(v_start_date_time, 'DD/MM/YYYY HH24:MI:SS'));

EXECUTE IMMEDIATE v_create_ix_sga_sub1;
EXECUTE IMMEDIATE v_create_ix_sga_sub2;

v_end_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Finised Successfully at: ' || TO_CHAR(v_end_date_time, 'DD/MM/YYYY HH24:MI:SS'));
UTIL_PKG.WRITE_LOG(v_module_name,'Execution time: ' ||
ROUND( (v_end_date_time-v_start_date_time)*24*60,2) );

EXCEPTION
WHEN OTHERS THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Unexpected Error. Error Details: '||SQLCODE || ' ' || SQLERRM);
RAISE;
END CREATE_INDEXES;

--------------------------------------------
PROCEDURE REBUILD_OFFLINE_PART_TABLE IS
v_module_name VARCHAR2(30) := 'REBUILD_OFFLINE_PART_TABLE';
v_start_date_time DATE;
v_end_date_time DATE;

v_move_tbs_part1 VARCHAR2(1000);
v_move_tbs_part2 VARCHAR2(1000);
v_move_tbs_part3 VARCHAR2(1000);
v_move_tbs_part4 VARCHAR2(1000);
v_move_tbs_part5 VARCHAR2(1000);
v_move_tbs_part6 VARCHAR2(1000);
v_move_tbs_part7 VARCHAR2(1000);
v_move_tbs_part8 VARCHAR2(1000);

BEGIN
v_move_tbs_part1 := 'ALTER TABLE MY_PARTITIONED_TABLE MOVE PARTITION SYS_P521';
v_move_tbs_part2 := 'ALTER TABLE MY_PARTITIONED_TABLE MOVE PARTITION SYS_P522';
v_move_tbs_part3 := 'ALTER TABLE MY_PARTITIONED_TABLE MOVE PARTITION SYS_P523';
v_move_tbs_part4 := 'ALTER TABLE MY_PARTITIONED_TABLE MOVE PARTITION SYS_P524';
v_move_tbs_part5 := 'ALTER TABLE MY_PARTITIONED_TABLE MOVE PARTITION SYS_P525';
v_move_tbs_part6 := 'ALTER TABLE MY_PARTITIONED_TABLE MOVE PARTITION SYS_P526';
v_move_tbs_part7 := 'ALTER TABLE MY_PARTITIONED_TABLE MOVE PARTITION SYS_P527';
v_move_tbs_part8 := 'ALTER TABLE MY_PARTITIONED_TABLE MOVE PARTITION SYS_P528';

v_start_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Started at: ' || TO_CHAR(v_start_date_time, 'DD/MM/YYYY HH24:MI:SS'));

EXECUTE IMMEDIATE v_move_tbs_part1;
EXECUTE IMMEDIATE v_move_tbs_part2;
EXECUTE IMMEDIATE v_move_tbs_part3;
EXECUTE IMMEDIATE v_move_tbs_part4;
EXECUTE IMMEDIATE v_move_tbs_part5;
EXECUTE IMMEDIATE v_move_tbs_part6;
EXECUTE IMMEDIATE v_move_tbs_part7;
EXECUTE IMMEDIATE v_move_tbs_part8;

v_end_date_time := SYSDATE;
UTIL_PKG.WRITE_LOG(v_module_name,'Procedure Finised Successfully at: ' || TO_CHAR(v_end_date_time, 'DD/MM/YYYY HH24:MI:SS'));
UTIL_PKG.WRITE_LOG(v_module_name,'Execution time: ' ||
ROUND( (v_end_date_time-v_start_date_time)*24*60,2) );

EXCEPTION
WHEN OTHERS THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Unexpected Error. Error Details: '||SQLCODE || ' ' || SQLERRM);
RAISE;
END REBUILD_OFFLINE_PART_TABLE;
--------------------------------------------
-- MAIN
--------------------------------------------

PROCEDURE MAIN_ORGANIZE_TABLES IS
v_module_name VARCHAR2(30) := 'MAIN_ORGANIZE_TABLES';
v_table_name USER_TABLES.table_name%TYPE;
BEGIN

UTIL_PKG.WRITE_LOG(v_module_name,'Staring');

v_table_name := 'MAIN_TABLE';
SHRINK_SPACE_TABLE(v_table_name);
REBUID_ONLINE_INDEX(v_table_name);

v_table_name := 'MY_MAIN_PARTITIONED_A';
SHRINK_SPACE_PART_TABLE(v_table_name);
REBUID_ONLINE_PART_INDEX(v_table_name);

v_table_name := 'MY_MAIN_PARTITIONED_B';
SHRINK_SPACE_PART_TABLE(v_table_name);
REBUID_ONLINE_PART_INDEX(v_table_name);

v_table_name := 'MAIN_TABLE';
GATHER_STATS_TABLE(v_table_name);

v_table_name := 'MY_MAIN_PARTITIONED_A';
GATHER_STATS_TABLE(v_table_name);

v_table_name := 'MY_MAIN_PARTITIONED_B';
GATHER_STATS_TABLE(v_table_name);

UTIL_PKG.WRITE_LOG(v_module_name,'Finished');
EXCEPTION
WHEN OTHERS THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Unexpected Error. Error Details: '||SQLCODE || ' ' || SQLERRM);
RAISE;

END MAIN_ORGANIZE_TABLES;
--------------------------------------------
PROCEDURE MAIN_ORGANIZE_TABLES_TEST IS
v_module_name VARCHAR2(30) := 'MAIN_ORGANIZE_TABLES_TEST';
v_table_name USER_TABLES.table_name%TYPE;
BEGIN

UTIL_PKG.WRITE_LOG(v_module_name,'Staring');

v_table_name := 'MAIN_TABLE_TEST';
SHRINK_SPACE_TABLE(v_table_name);
REBUID_ONLINE_INDEX(v_table_name);
GATHER_STATS_TABLE(v_table_name);

UTIL_PKG.WRITE_LOG(v_module_name,'Finished');
EXCEPTION
WHEN OTHERS THEN
UTIL_PKG.WRITE_LOG(v_module_name,'Unexpected Error. Error Details: '||SQLCODE || ' ' || SQLERRM);
RAISE;

END MAIN_ORGANIZE_TABLES_TEST;

END ADMIN_UTIL_PKG;

Simple SQL to Rebuild Partition Indexes

SELECT 'ALTER INDEX '||USER_IND_PARTITIONS.index_name||' REBUILD PARTITION '||USER_IND_PARTITIONS.partition_name||' ONLINE;'

FROM USER_IND_PARTITIONS ,

USER_PART_INDEXES

WHERE USER_PART_INDEXES.table_name = 'GA_W_COUNTERS_HISTORY'

AND USER_IND_PARTITIONS.index_name = USER_PART_INDEXES.index_name

ORDER BY USER_IND_PARTITIONS.index_name, USER_IND_PARTITIONS.partition_name;

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Monday, August 17, 2015

ORA-00600: internal error code, arguments: [17059], [], [],

Friday, August 14, 2015

PL/SQL code for Reorganizing Table and Indexes.