-- $Id: sym_util.body.sql,v 1.4 2004/03/11 21:26:04 jnasby Exp $

CREATE OR REPLACE PACKAGE BODY sym_util AS
	TYPE t_config_partitions IS TABLE OF config_partitions%rowtype;
	TYPE varchar2_table IS TABLE OF varchar(30);

	PROCEDURE rotate_partitions
	AS
		v_partitions t_config_partitions;
		v_sql varchar(4000);
		v_next_partition number;
		v_partition_name varchar(40);
		v_partitions_to_drop varchar2_table;
	BEGIN
		debug.f('rotate_partitions enter (current_user=%s, session_user=%s, session_schema=%s)'
				, sys_context('userenv','current_user')
				, sys_context('userenv','session_user')
				, sys_context('userenv','session_schema')
			);
		BEGIN
			SELECT *
				BULK COLLECT INTO v_partitions
				FROM config_partitions
				WHERE last_run at time zone 'GMT' + numtodsinterval(length_minutes, 'minute')
						<= current_timestamp at time zone 'GMT'
			;
		EXCEPTION
			WHEN no_data_found THEN
				null;
		END;

		IF v_partitions.COUNT=0 THEN
			debug.f('rotate_partitions no tables need rotating');
		ELSE
			debug.f('rotate_partitions %s tables to update', v_partitions.COUNT);

			FOR i IN v_partitions.FIRST .. v_partitions.LAST
			LOOP
				debug.f('rotate_partitions adding partition to table %s', v_partitions(i).table_name);
				-- Add new partition (current time (rounded down) + length_minutes)
				-- Calculate next partition split point. We add 2 intervals:
				-- First, we want to add the partition for the next interval, not the current one.
				-- Second, the split point is the end of the interval we want.
				SELECT CAST( time.t_s(current_timestamp) / v_partitions(i).length_minutes / 60 AS number(38,0))
						* v_partitions(i).length_minutes * 60
					INTO v_next_partition
					FROM dual
				;

				-- Before doing DDL or adding any intervals, update the table
				UPDATE config_partitions
					SET last_run = time.s_tz(v_next_partition)
					WHERE table_name = v_partitions(i).table_name
				;

				-- Add one interval, then generate partition name
				v_next_partition := v_next_partition + v_partitions(i).length_minutes * 60;
				SELECT 'part_' || to_char(time.s_t(v_next_partition), 'YYYYMMDD_HH24MISS')
					INTO v_partition_name
					FROM dual
				;

				-- Add one more interval, so we know where to split at
				v_next_partition := v_next_partition + v_partitions(i).length_minutes * 60;

				SELECT 'ALTER TABLE ' || v_partitions(i).table_name
						|| ' SPLIT PARTITION the_rest at ( ''' || time.s_t(v_next_partition) || ''' )'
						|| ' INTO ( PARTITION ' || v_partition_name || ', PARTITION the_rest )'
					INTO v_sql
					FROM dual
				;
				debug.f('rotate_partitions %s', v_sql);
				EXECUTE IMMEDIATE v_sql;

				-- Drop extra partitions
				BEGIN
					v_partitions_to_drop := NULL;
					SELECT partition_name
						BULK COLLECT INTO v_partitions_to_drop
						FROM (SELECT partition_name, row_number() OVER (ORDER BY partition_name DESC) AS rn
									FROM user_tab_partitions
									WHERE table_name = upper(v_partitions(i).table_name)
										AND partition_name != 'THE_REST'
								)
						-- We're adding one partition ahead of where we need to, so keep one extra partition
						WHERE rn > v_partitions(i).partitions_keep + 1
					;
				EXCEPTION
					WHEN no_data_found THEN
						null;
				END;

				IF v_partitions_to_drop.COUNT = 0 THEN 
					debug.f('rotate_partitions no partitions to drop for table %s', v_partitions(i).table_name);
				ELSE
					FOR d IN v_partitions_to_drop.FIRST .. v_partitions_to_drop.LAST
					LOOP
						v_sql := 'ALTER TABLE ' || v_partitions(i).table_name
									|| ' DROP PARTITION ' || v_partitions_to_drop(d)
						;

						debug.f('rotate_partitions %s', v_sql);
						EXECUTE IMMEDIATE v_sql;
					END LOOP;
				END IF;
			END LOOP;
		END IF;
		debug.f('rotate_partitions exit');
	END;

	PROCEDURE exec_immediate(
		statement varchar
	)
	AS
	BEGIN
		debug.f('EXECUTE IMMEDIATE %s', statement);
		EXECUTE IMMEDIATE statement;
	END;
END sym_util;
/
show err

DECLARE
	v_job_id user_jobs.job%TYPE;
	v_what user_jobs.what%TYPE := 'sym_util.rotate_partitions;';
BEGIN
	BEGIN
		SELECT job
			INTO v_job_id
			FROM user_jobs
			WHERE what = v_what
		;
		p('Rotate partitions job exists, job #' || v_job_id || ', deleting.');
		DBMS_JOB.remove(v_job_id);
	EXCEPTION
		WHEN no_data_found THEN
			null;
	END;

	DBMS_JOB.submit(v_job_id, v_what, interval=>'trunc(sysdate, ''MI'') + 1/24/60');
	p('Rotate partitions job added, job #' || v_job_id);
	COMMIT;
END;
/
show err

	
-- vi: noexpandtab sw=4 ts=4