/*
 * Title  : acp_pls.sql
 * Author : EXADIME LLC
 * Created: 3-JUL-2017
 * Updated: 3-JUL-2017
 * Release: 1.0
 * Purpose: Auto Capacity Planner
 * History: N/A
 */

set echo off
set feedback on
set verify off

prompt Grant select on dba_tablespaces,dba_data_files,dba_free_space,dba_extents to current schema.
prompt Package $ora_dir/util/smpl_logger should be created.

create or replace package pkg_acp
is
	function get_db_link(p_db_run_key number) return varchar2;
	procedure pop_db_sgs(p_db_run_key number);
	procedure pop_ts_sgs(p_db_run_key number);
	procedure pop_us_sgs(p_db_run_key number);
	procedure pop_sgs(p_db_run_key number);
	procedure pop_sgs;
	procedure gen_sgr;
end pkg_acp;
/

create or replace package body pkg_acp
is
	type rc is ref cursor;
	function get_db_link(p_db_run_key number)
	return varchar2 is
		v_db_link	acp_db_run.db_link%type;
	begin
		select 
			db_link into v_db_link
		from
			acp_db_run
		where
			acp_db_run_key=p_db_run_key;
		if v_db_link is null then
			v_db_link:='';
		else
			v_db_link:= '@' || v_db_link;
		end if;
		return v_db_link;
	end get_db_link;

	procedure pop_db_sgs(p_db_run_key number)
	is
		v_sql		varchar2(2000);
		v_total_bytes	number;
		v_used_bytes	number;
		v_pct_used	number;
	begin
		v_sql:='select 
				sum(total_bytes)		   total_bytes,
				sum(total_bytes-nvl(free_bytes,0)) used_bytes,
				round((sum(total_bytes-nvl(free_bytes,0))/sum(total_bytes))*100,2) pct_used
			from
				(
					select 
						sum(bytes) free_bytes,
						tablespace_name
					from  
						sys.dba_free_space' || get_db_link(p_db_run_key) || '
					group by 
						tablespace_name
				) a,
				(
					select 
						sum(bytes) total_bytes,
						tablespace_name
					from 
						sys.dba_data_files' || get_db_link(p_db_run_key) || '
					group by 
						tablespace_name
				) b,
				(
					select
						tablespace_name,
						contents
					from
						sys.dba_tablespaces' || get_db_link(p_db_run_key) || '
				) c	
			where 
				a.tablespace_name (+) = b.tablespace_name
			and	b.tablespace_name=c.tablespace_name
			and	c.contents<>''UNDO''
			order by 
				1';

		execute immediate v_sql into v_total_bytes,v_used_bytes,v_pct_used;
		delete from acp_db_sgs
		where 
			to_char(day,'yyyymmdd')=to_char(sysdate,'yyyymmdd')
		and	acp_db_run_key= p_db_run_key;
		insert into acp_db_sgs(
			acp_db_sgs_key,
			acp_db_run_key,
			day,
			total_bytes,
			used_bytes,
			pct_used
		)values(
			(select decode(max(acp_db_sgs_key)+1,null,1,max(acp_db_sgs_key)+1) from acp_db_sgs),
			p_db_run_key,
			sysdate,
			v_total_bytes,
			v_used_bytes,
			v_pct_used
		);
		commit;
	exception
		when others then
			smpl_logger.write_log(1,'pkg_acp.pop_db_sgs','ACP-1.0.0',v_sql,sqlerrm);
	end;

	procedure pop_ts_sgs(p_db_run_key number)
	is
		csr_ts_used	rc;
		v_sql		varchar2(2000);
		v_ts_name	varchar2(30);
		v_total_bytes	number;
		v_used_bytes	number;
		v_pct_used	number;
	begin
		v_sql:='select 
				nvl(b.tablespace_name,nvl(a.tablespace_name,''UNKOWN'')) name,
				total_bytes,
				total_bytes-nvl(free_bytes,0) used_bytes,
				round((total_bytes-nvl(free_bytes,0))/total_bytes*100,2) pct_used
			from
				(
					select 
						sum(bytes) free_bytes,
						tablespace_name
					from  
						sys.dba_free_space' || get_db_link(p_db_run_key) || '
					group by 
						tablespace_name
				) a,
				(
					select 
						sum(bytes) total_bytes,
						tablespace_name
					from 
						sys.dba_data_files' || get_db_link(p_db_run_key) || '
					group by 
						tablespace_name
				) b,
				(
					select
						tablespace_name,
						contents
					from
						sys.dba_tablespaces' || get_db_link(p_db_run_key) || '
				) c	
			where 
				a.tablespace_name (+) = b.tablespace_name
			and	b.tablespace_name=c.tablespace_name
			and	c.contents<>''UNDO''
			order by 
				1';
		delete from acp_ts_sgs
		where 
			to_char(day,'yyyymmdd')=to_char(sysdate,'yyyymmdd')
		and	acp_db_run_key=p_db_run_key;
		open csr_ts_used for v_sql;
		loop
			fetch csr_ts_used into v_ts_name,v_total_bytes,v_used_bytes,v_pct_used;
			exit when csr_ts_used%notfound;
			insert into acp_ts_sgs(
				acp_ts_sgs_key,
				acp_db_run_key,
				ts_name,
				day,
				total_bytes,
				used_bytes,
				pct_used
			)values(
				(select decode(max(acp_ts_sgs_key)+1,null,1,max(acp_ts_sgs_key)+1) from acp_ts_sgs),
				p_db_run_key,
				v_ts_name,
				sysdate,
				v_total_bytes,
				v_used_bytes,
				v_pct_used
			);
		end loop;
		close csr_ts_used;
		commit;
	exception
		when others then
			smpl_logger.write_log(1,'pkg_acp.pop_ts_sgs','ACP-1.0.0',v_sql,sqlerrm);
	end;
	
	procedure pop_us_sgs(p_db_run_key number)
	is
		csr_us_used	rc;
		v_sql		varchar2(2000);
		v_user_name	varchar2(30);
		v_used_bytes	number;
	begin
		v_sql:='select
				owner,
				sum(bytes) used_bytes
			from 
				dba_extents ' || get_db_link(p_db_run_key) || '
			where
				owner not in (
					''SYS'',
					''SYSTEM'',
					''DBSNMP'',
					''OUTLN'',
					''MDSYS'',
					''ORDSYS'',
					''ORDPLUGINS'',
					''CTXSYS'',
					''DSSYS'',
					''WKPROXY'',
					''WKSYS'',
					''WMSYS'',
					''XDB'',
					''ANONYMOUS'',
					''ODM'',
					''ODM_MTR'',
					''OLAPSYS'',
					''TRACESVR'',
					''OPS$ORACLE'',
					''ORADBA'',
					''PERFSTAT'',
					''REPADMIN'',
					''EXFSYS'',
					''DMSYS'',
					''SYSMAN'',
					''PUBLIC''
				)			
			group by 
				owner';
		delete from acp_us_sgs
		where 
			to_char(day,'yyyymmdd')=to_char(sysdate,'yyyymmdd')
		and	acp_db_run_key=p_db_run_key;
		
		open csr_us_used for v_sql;
		loop
			fetch csr_us_used into v_user_name,v_used_bytes;
			exit when csr_us_used%notfound;
			insert into acp_us_sgs(
				acp_us_sgs_key,
				acp_db_run_key,
				user_name,
				day,
				used_bytes
			)values(
				(select decode(max(acp_us_sgs_key)+1,null,1,max(acp_us_sgs_key)+1) from acp_us_sgs),
				p_db_run_key,
				v_user_name,
				sysdate,
				v_used_bytes
			);
		end loop;
		close csr_us_used;
		commit;
	exception
		when others then
			smpl_logger.write_log(1,'pkg_acp.pop_us_sgs','ACP-1.0.0',v_sql,sqlerrm);
	end;
	
	procedure pop_sgs(p_db_run_key number)
	is
	begin
		pop_db_sgs(p_db_run_key);
		pop_ts_sgs(p_db_run_key);
		pop_us_sgs(p_db_run_key);
		commit;
	exception
		when others then
			smpl_logger.write_log(1,'pkg_acp.pop_sgs(p_db_run_key number)','ACP-1.0.0','Unknown error. See error details.',sqlerrm);
	end;
	
	procedure pop_sgs
	is
		cursor csr_db_run is
		select
			acp_db_run_key
		from
			acp_db_run
		where
			is_run='Y';	
	begin
		for r in csr_db_run
		loop
			pop_db_sgs(r.acp_db_run_key);
			pop_ts_sgs(r.acp_db_run_key);
			pop_us_sgs(r.acp_db_run_key);
		end loop;
	exception
		when others then
			smpl_logger.write_log(1,'pkg_acp.pop_sgs','ACP-1.0.0','Unknown error. See error details.',sqlerrm);
	end;
	
	procedure gen_sgr
	is
		cursor csr_dbw_sgr is
		select
			acp_db_run_key,
			year,
			week#,
			(
				(
					select 
						used_bytes 
					from 
						acp_db_sgs 
					where 
						day=max_day
					and	to_char(day,'yyyy')=t.year
					and	to_char(day,'WW') =t.week#
					and	acp_db_run_key=t.acp_db_run_key
				) -
				(
					select 
						used_bytes 
					from 
						acp_db_sgs 
					where 
						day=min_day
					and	to_char(day,'yyyy')=t.year
					and	to_char(day,'WW') =t.week#
					and	acp_db_run_key=t.acp_db_run_key
				)
			) growth_bytes
		from
			(
				select 
					acp_db_run_key		acp_db_run_key,
					to_char(day,'yyyy') 	year,
					to_char(day,'WW') 	week#,
					min(day)		min_day,
					max(day)		max_day
				from 
					acp_db_sgs a
				where	
					(to_char(day,'yyyy'),to_char(day,'WW')) not in (
						select 
							year,
							week#
						from
							acp_dbw_sgr
					)
				and	used_bytes is not null
				group by 
					acp_db_run_key,to_char(day,'yyyy'),to_char(day,'WW')
			) t;
		cursor csr_tsw_sgr is
		select
			acp_db_run_key,
			ts_name,
			year,
			week#,
			(
				(
					select 
						used_bytes 
					from 
						acp_ts_sgs 
					where 
						day=max_day
					and	ts_name=t.ts_name
					and	to_char(day,'yyyy')=t.year
					and	to_char(day,'WW') =t.week#
					and	acp_db_run_key=t.acp_db_run_key
				) -
				(
					select 
						used_bytes 
					from 
						acp_ts_sgs 
					where 
						day=min_day
					and	ts_name=t.ts_name
					and	to_char(day,'yyyy')=t.year
					and	to_char(day,'WW') =t.week#
					and	acp_db_run_key=t.acp_db_run_key
				)
			) growth_bytes
		from
			(
				select 
					acp_db_run_key		acp_db_run_key,
					ts_name			ts_name,
					to_char(day,'yyyy') 	year,
					to_char(day,'WW') 	week#,
					min(day)		min_day,
					max(day)		max_day
				from 
					acp_ts_sgs a
				where	
					(to_char(day,'yyyy'),to_char(day,'WW')) not in (
						select 
							year,
							week#
						from
							acp_tsw_sgr
					)
				and	used_bytes is not null
				group by 
					acp_db_run_key,ts_name,to_char(day,'yyyy'),to_char(day,'WW')
			) t;
		cursor csr_usw_sgr is	
		select
			acp_db_run_key,
			user_name,
			year,
			week#,
			(
				(
					select 
						used_bytes 
					from 
						acp_us_sgs 
					where 
						day=max_day 
					and	user_name=t.user_name	
					and	to_char(day,'yyyy')=t.year
					and	to_char(day,'WW') =t.week#
					and	acp_db_run_key=t.acp_db_run_key
				) -
				(
					select 
						used_bytes 
					from 
						acp_us_sgs 
					where 
						day=min_day 
					and	user_name=t.user_name	
					and	to_char(day,'yyyy')=t.year
					and	to_char(day,'WW') =t.week#
					and	acp_db_run_key=t.acp_db_run_key
				)
			) growth_bytes
		from
			(
				select 
					acp_db_run_key		acp_db_run_key,
					user_name		user_name,
					to_char(day,'yyyy') 	year,
					to_char(day,'WW') 	week#,
					min(day)		min_day,
					max(day)		max_day
				from 
					acp_us_sgs a
				where	
					(to_char(day,'yyyy'),to_char(day,'WW')) not in (
						select 
							year,
							week#
						from
							acp_usw_sgr
					)
				and	used_bytes is not null
				group by 
					acp_db_run_key,user_name,to_char(day,'yyyy'),to_char(day,'WW')
			) t;
	begin
		for r in csr_dbw_sgr
		loop
			insert into acp_dbw_sgr(
				acp_dbw_sgr_key,
				acp_db_run_key,
				year,
				week#,
				growth_bytes
			)values(
				(select decode(max(acp_dbw_sgr_key)+1,null,1,max(acp_dbw_sgr_key)+1) from acp_dbw_sgr),
				r.acp_db_run_key,
				r.year,
				r.week#,
				r.growth_bytes			
			);
		end loop;
		for r in csr_tsw_sgr
		loop
			insert into acp_tsw_sgr(
				acp_tsw_sgr_key,
				acp_db_run_key,
				ts_name,
				year,
				week#,
				growth_bytes
			)values(
				(select decode(max(acp_tsw_sgr_key)+1,null,1,max(acp_tsw_sgr_key)+1) from acp_tsw_sgr),
				r.acp_db_run_key,
				r.ts_name,
				r.year,
				r.week#,
				r.growth_bytes			
			);
		end loop;
		for r in csr_usw_sgr
		loop
			insert into acp_usw_sgr(
				acp_usw_sgr_key,
				acp_db_run_key,
				user_name,
				year,
				week#,
				growth_bytes
			)values(
				(select decode(max(acp_usw_sgr_key)+1,null,1,max(acp_usw_sgr_key)+1) from acp_usw_sgr),
				r.acp_db_run_key,
				r.user_name,
				r.year,
				r.week#,
				r.growth_bytes			
			);
		end loop;
		commit;	
	exception
		when others then
			smpl_logger.write_log(1,'pkg_acp.gen_sgr','ACP-1.0.0','Unknown error. See error details.',sqlerrm);
	end;	
end pkg_acp;
/
