Tuning Script

CREATE OR REPLACE PACKAGE BODY tuning
AS
PROCEDURE dpr_moved
IS
CURSOR c_table_name
IS
SELECT tablespace_name, table_name
FROM user_tables
WHERE TEMPORARY = 'N';

stmt VARCHAR2 (1024);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

FOR i IN c_table_name LOOP
BEGIN
stmt :=
'ALTER TABLE '
|| i.table_name
|| ' MOVE TABLESPACE '
|| i.tablespace_name;

EXECUTE IMMEDIATE (stmt);

DBMS_OUTPUT.put_line ( 'Table moved completed: '
|| i.table_name
|| '.'
|| i.tablespace_name
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'Table can not moved: '
|| i.table_name
|| '.'
|| i.tablespace_name
);
END;
END LOOP;
END;

PROCEDURE dpr_rebuild (index_tablespace IN VARCHAR2)
IS
CURSOR c_index
IS
SELECT table_name, index_name
FROM user_indexes;

stmt VARCHAR2 (1024);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

FOR i IN c_index LOOP
BEGIN
stmt :=
'ALTER INDEX '
|| i.index_name
|| ' REBUILD TABLESPACE '
|| index_tablespace
|| ' PARALLEL';

EXECUTE IMMEDIATE (stmt);

DBMS_OUTPUT.put_line ( 'Index rebuild completed: '
|| i.table_name
|| '.'
|| i.index_name
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'Index can not rebuild: '
|| i.table_name
|| '.'
|| i.index_name
);
END;
END LOOP;
END;

PROCEDURE dpr_analyze (owner_name IN VARCHAR2)
IS
CURSOR c_table_name
IS
SELECT table_name
FROM dba_tables
WHERE UPPER (owner) = UPPER (owner_name) AND TEMPORARY = 'N';

CURSOR c_index_name (table_name1 IN VARCHAR2)
IS
SELECT index_name
FROM dba_indexes
WHERE UPPER (owner) = UPPER (owner_name)
AND table_name = table_name1
AND status = 'VALID';
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

FOR i IN c_table_name LOOP
DBMS_STATS.unlock_table_stats (ownname => UPPER (owner_name),
tabname => i.table_name,
stattype => 'ALL'
);

BEGIN
DBMS_STATS.gather_table_stats
(ownname => UPPER (owner_name),
tabname => i.table_name,
estimate_percent => DBMS_STATS.auto_sample_size,
CASCADE => DBMS_STATS.auto_cascade,
DEGREE => DBMS_STATS.auto_degree,
no_invalidate => DBMS_STATS.auto_invalidate,
granularity => 'AUTO',
method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);
DBMS_OUTPUT.put_line ( 'Statistics gathered for table: '
|| UPPER (owner_name)
|| '.'
|| i.table_name
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line
( 'Statistics can not gathered for table: '
|| UPPER (owner_name)
|| '.'
|| i.table_name
);
END;

FOR j IN c_index_name (i.table_name) LOOP
BEGIN
DBMS_STATS.gather_index_stats
(ownname => UPPER (owner_name),
indname => j.index_name,
estimate_percent => DBMS_STATS.auto_sample_size,
DEGREE => DBMS_STATS.auto_degree,
no_invalidate => DBMS_STATS.auto_invalidate,
granularity => 'AUTO'
);
DBMS_OUTPUT.put_line ( 'Statistics gathered for index: '
|| UPPER (owner_name)
|| '.'
|| i.table_name
|| '.'
|| j.index_name
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line
( 'Statistics can not gathered for index: '
|| UPPER (owner_name)
|| '.'
|| i.table_name
|| '.'
|| j.index_name
);
END;
END LOOP;

DBMS_STATS.lock_table_stats (ownname => UPPER (owner_name),
tabname => i.table_name
);
END LOOP;
END;

PROCEDURE dpr_shrink
IS
CURSOR c_table_name
IS
SELECT table_name
FROM user_tables
WHERE TEMPORARY = 'N' AND row_movement = 'DISABLED';

CURSOR c_table_shrink
IS
SELECT table_name
FROM user_tables
WHERE TEMPORARY = 'N' AND row_movement = 'ENABLED';

stmt VARCHAR2 (1024);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

FOR i IN c_table_name LOOP
stmt := 'ALTER TABLE ' || i.table_name || ' ENABLE ROW MOVEMENT';

EXECUTE IMMEDIATE (stmt);
END LOOP;

FOR i IN c_table_shrink LOOP
BEGIN
stmt := 'ALTER TABLE ' || i.table_name || ' SHRINK SPACE';

EXECUTE IMMEDIATE (stmt);

DBMS_OUTPUT.put_line ('Table shrink completed: ' || i.table_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Table can not shrink: ' || i.table_name);
END;
END LOOP;
END;

PROCEDURE dpr_rebuild_indexes (index_tablespace IN VARCHAR2)
IS
table_name user_indexes.table_name%TYPE;
index_name user_indexes.index_name%TYPE;
index_height index_stats.height%TYPE;
leaf_rows index_stats.lf_rows%TYPE;
del_leaf_rows index_stats.del_lf_rows%TYPE;
analyze_stmt VARCHAR2 (1024);
index_stmt VARCHAR2 (1024);
num_rows INTEGER;
del_leaf_pct INTEGER;
max_tree_height INTEGER := 3;
max_del_leaf_pct INTEGER := 20;
/* Define maximums. This section can be customized. */
index_id INTEGER;
index_status INTEGER;

CURSOR c_index
IS
SELECT table_name, index_name
FROM user_indexes
WHERE status = 'VALID';

CURSOR c_index_notvalid
IS
SELECT table_name, index_name
FROM user_indexes
WHERE status != 'VALID';
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

OPEN c_index;

LOOP
FETCH c_index
INTO table_name, index_name;

EXIT WHEN c_index%NOTFOUND;
analyze_stmt :=
'ANALYZE INDEX ' || index_name || ' VALIDATE STRUCTURE';

EXECUTE IMMEDIATE (analyze_stmt);

SELECT height, lf_rows, del_lf_rows
INTO index_height, leaf_rows, del_leaf_rows
FROM index_stats;

IF del_leaf_rows = 0 THEN
/* handle case where div by zero */
del_leaf_pct := 0;
ELSE
del_leaf_pct := (del_leaf_rows / leaf_rows) * 100;
END IF;

IF (index_height > max_tree_height)
OR (del_leaf_pct > max_del_leaf_pct) THEN
BEGIN
index_stmt :=
'ALTER INDEX '
|| index_name
|| ' REBUILD TABLESPACE '
|| index_tablespace
|| ' PARALLEL';

EXECUTE IMMEDIATE (index_stmt);

DBMS_OUTPUT.put_line ( 'Index rebuild completed: '
|| table_name
|| '.'
|| index_name
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'Index can not rebuild: '
|| table_name
|| '.'
|| index_name
);
END;
END IF;
END LOOP;

CLOSE c_index;

FOR i IN c_index_notvalid LOOP
BEGIN
index_stmt :=
'ALTER INDEX '
|| i.index_name
|| ' REBUILD TABLESPACE '
|| index_tablespace
|| ' PARALLEL';

EXECUTE IMMEDIATE (index_stmt);

DBMS_OUTPUT.put_line ( 'Index rebuild completed: '
|| i.table_name
|| '.'
|| i.index_name
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'Index can not rebuild: '
|| i.table_name
|| '.'
|| i.index_name
);
END;
END LOOP;
END;

PROCEDURE dpr_user_locks
IS
CURSOR c_lock
IS
SELECT s.SID, s.serial#, s.username, s.machine, s.osuser
FROM v$lock l, v$session s, dba_objects o
WHERE l.SID = s.SID AND o.object_id(+) = l.id1 AND l.BLOCK = 1;

stmt VARCHAR2 (1024);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

FOR i IN c_lock LOOP
BEGIN
stmt :=
'ALTER SYSTEM KILL SESSION '
|| ''''
|| i.SID
|| ','
|| i.serial#
|| '''';

EXECUTE IMMEDIATE (stmt);

DBMS_OUTPUT.put_line ( 'User killed for locking '
|| 'SID: '
|| i.SID
|| ', '
|| 'Serial No: '
|| i.serial#
|| ', '
|| 'User Name: '
|| i.username
|| ', '
|| 'Machine Name: '
|| i.machine
|| SUBSTR (i.osuser, INSTR (i.osuser, '\'))
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'User can not killed for locking '
|| 'SID: '
|| i.SID
|| ', '
|| 'Serial No: '
|| i.serial#
|| ', '
|| 'User Name: '
|| i.username
|| ', '
|| 'Machine Name: '
|| i.machine
|| SUBSTR (i.osuser,
INSTR (i.osuser, '\')
)
);
END;
END LOOP;
END;
END;
/