rem $DBA/crclusts.sql
rem
rem This script generates another script that will include all the CREATE
rem CLUSTER statements for those in the database (except for the SYS user).
rem
rem This script must be run by a user with the DBA role under Oracle7.
rem
rem Last Modified 04/17/97 by Brian Lomasky
rem
set verify off
set feedback off
set echo off
set pagesize 0
set termout on
select 'Creating cluster build script...' from dual;
create table clus_temp (	lineno NUMBER,
				text VARCHAR2(80));
declare
	cursor clu_cursor is select
		upper(owner),
		upper(cluster_name),
		tablespace_name,
		pct_free,
		pct_used,
		key_size,
		ini_trans,
		max_trans,
		initial_extent,
		next_extent,
		min_extents,
		max_extents,
		pct_increase,
		cluster_type,
		function,
		hashkeys
		from sys.dba_clusters
		where owner != 'SYS'
		order by owner, cluster_name;
	cursor col_cursor (c_own VARCHAR2, c_clu VARCHAR2) is select
		c.clu_column_name,
		c.table_name,
		c.tab_column_name,
		t.data_type,
		t.data_length,
		t.data_precision,
		t.data_scale
		from sys.dba_clu_columns c, sys.dba_tab_columns t
		where c.cluster_name = c_clu and c.owner = c_own
		and c.owner = t.owner and c.table_name = t.table_name
		and c.tab_column_name = t.column_name;

	lv_owner		sys.dba_clusters.owner%TYPE;
	lv_cluster_name		sys.dba_clusters.cluster_name%TYPE;
	lv_tablespace_name	sys.dba_clusters.tablespace_name%TYPE;
	lv_pct_free		sys.dba_clusters.pct_free%TYPE;
	lv_pct_used		sys.dba_clusters.pct_used%TYPE;
	lv_key_size		sys.dba_clusters.key_size%TYPE;
	lv_ini_trans		sys.dba_clusters.ini_trans%TYPE;
	lv_max_trans		sys.dba_clusters.max_trans%TYPE;
	lv_initial_extent	sys.dba_clusters.initial_extent%TYPE;
	lv_next_extent		sys.dba_clusters.next_extent%TYPE;
	lv_min_extents		sys.dba_clusters.min_extents%TYPE;
	lv_max_extents		sys.dba_clusters.max_extents%TYPE;
	lv_pct_increase		sys.dba_clusters.pct_increase%TYPE;
	lv_cluster_type		sys.dba_clusters.cluster_type%TYPE;
	lv_function		sys.dba_clusters.function%TYPE;
	lv_hashkeys		sys.dba_clusters.hashkeys%TYPE;
	lv_clu_column_name	sys.dba_clu_columns.clu_column_name%TYPE;
	lv_table_name		sys.dba_clu_columns.table_name%TYPE;
	lv_tab_column_name	sys.dba_clu_columns.tab_column_name%TYPE;
	lv_data_type		sys.dba_tab_columns.data_type%TYPE;
	lv_data_length		sys.dba_tab_columns.data_length%TYPE;
	lv_data_precision	sys.dba_tab_columns.data_precision%TYPE;
	lv_data_scale		sys.dba_tab_columns.data_scale%TYPE;
	lv_lineno		number := 0;
	initial_extent_size	varchar2(16);
	next_extent_size	varchar2(16);
	my_key_size		varchar2(16);
	a_lin			varchar2(80);
	col_num			number;

	function wri(x_lin in varchar2, x_str in varchar2,
		x_force in number) return varchar2 is
	begin
		if length(x_lin) + length(x_str) > 80
		then
			lv_lineno := lv_lineno + 1;
			insert into clus_temp values (lv_lineno, x_lin);
			if x_force = 0
			then
				return x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into clus_temp values (lv_lineno, x_str);
				return '';
			end if;
		else
			if x_force = 0
			then
				return x_lin||x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into clus_temp values (
					lv_lineno, x_lin||x_str);
				return '';
			end if;
		end if;
	end wri;

begin
	a_lin := '';
	open clu_cursor;
	loop
		fetch clu_cursor into
			lv_owner,
			lv_cluster_name,
			lv_tablespace_name,
			lv_pct_free,
			lv_pct_used,
			lv_key_size,
			lv_ini_trans,
			lv_max_trans,
			lv_initial_extent,
			lv_next_extent,
			lv_min_extents,
			lv_max_extents,
			lv_pct_increase,
			lv_cluster_type,
			lv_function,
			lv_hashkeys;
		exit when clu_cursor%notfound;

		if (to_char(lv_ini_trans) = '0') then
			lv_ini_trans := 1;
		end if;
		if (to_char(lv_max_trans) = '0') then
			lv_max_trans := 1;
		end if;
		/* Calculate extent sizes in Mbytes or Kbytes, if possible */
		if mod(lv_initial_extent, 1048576) = 0 then
			initial_extent_size :=
				to_char(lv_initial_extent / 1048576) || 'M';
		elsif mod(lv_initial_extent, 1024) = 0 then
			initial_extent_size :=
				to_char(lv_initial_extent / 1024) || 'K';
		else
			initial_extent_size := to_char(lv_initial_extent);
		end if;
		if mod(lv_next_extent, 1048576) = 0 then
			next_extent_size :=
				to_char(lv_next_extent / 1048576) || 'M';
		elsif mod(lv_next_extent, 1024) = 0 then
			next_extent_size :=
				to_char(lv_next_extent / 1024) || 'K';
		else
			next_extent_size := to_char(lv_next_extent);
		end if;
		if mod(lv_key_size, 1048576) = 0 then
			my_key_size := to_char(lv_key_size / 1048576) || 'M';
		elsif mod(lv_key_size, 1024) = 0 then
			my_key_size := to_char(lv_key_size / 1024) || 'K';
		else
			my_key_size := to_char(lv_key_size);
		end if;

		a_lin := wri(a_lin, 'create cluster ', 0);
		a_lin := wri(a_lin, lv_owner || '.' || lv_cluster_name, 0);
		a_lin := wri(a_lin, ' (', 0);
		col_num := 0;
		open col_cursor(lv_owner, lv_cluster_name);
		loop
			fetch col_cursor into
				lv_clu_column_name,
				lv_table_name,
				lv_tab_column_name,
				lv_data_type,
				lv_data_length,
				lv_data_precision,
				lv_data_scale;
			exit when col_cursor%notfound;
			col_num := col_num + 1;
			if col_num <> 1 then
				a_lin := wri(a_lin, ',', 0);
			end if;
			a_lin := wri(a_lin,
				chr(34) || lv_clu_column_name || chr(34), 0);
			a_lin := wri(a_lin, ' ' || lv_data_type, 0);
			if lv_data_type = 'CHAR' or lv_data_type = 'VARCHAR2' or
				lv_data_type = 'RAW'
			then
				a_lin := wri(a_lin,
					'(' || lv_data_length || ')', 0);
			end if;
			if lv_data_type = 'NUMBER' and
				nvl(lv_data_precision, 0) != 0
			then
				if nvl(lv_data_scale, 0) = 0 then
					a_lin := wri(a_lin,
						'(' || lv_data_precision || ')',
						0);
				else
					a_lin := wri(a_lin,
						'(' || lv_data_precision ||
						',' || lv_data_scale || ')', 0);
				end if;
			end if;
		end loop;
		close col_cursor;
		a_lin := wri(a_lin, ')', 1);
		a_lin := wri(a_lin, ' PCTFREE ' || to_char(lv_pct_free), 0);
		a_lin := wri(a_lin, ' PCTUSED ' || to_char(lv_pct_used), 0);
		a_lin := wri(a_lin, ' INITRANS ' || to_char(lv_ini_trans), 0);
		a_lin := wri(a_lin, ' MAXTRANS ' || to_char(lv_max_trans), 0);
		a_lin := wri(a_lin, ' SIZE ' || my_key_size, 0);
		a_lin := wri(a_lin, ' TABLESPACE ' || lv_tablespace_name, 1);
		a_lin := wri(a_lin, ' STORAGE (', 0);
		a_lin := wri(a_lin, ' INITIAL ' || initial_extent_size, 0);
		a_lin := wri(a_lin, ' NEXT ' || next_extent_size, 0);
		a_lin := wri(a_lin, ' MINEXTENTS ' || to_char(lv_min_extents),
			0);
		a_lin := wri(a_lin, ' MAXEXTENTS ' || to_char(lv_max_extents),
			0);
		a_lin := wri(a_lin, ' PCTINCREASE ' || to_char(lv_pct_increase),
			0);
		a_lin := wri(a_lin, ')', 1);
		if lv_cluster_type = 'INDEX' then
			a_lin := wri(a_lin, ' INDEX', 0);
		else
			if lv_function != ' ' then
				a_lin := wri(a_lin, ' HASH IS', 0);
				a_lin := wri(a_lin, lv_function, 0);
			end if;
			a_lin := wri(a_lin, ' HASHKEYS', 0);
			a_lin := wri(a_lin, ' ' || to_char(lv_hashkeys), 0);
		end if;
		a_lin := wri(a_lin, ';', 1);
	end loop;
	close clu_cursor;
	commit;
exception
	when others then
		rollback;
		raise_application_error(-20000,
			'Unexpected error on ' || lv_cluster_name ||
			', ' || lv_tab_column_name || ': ' ||
			to_char(SQLCODE) || ' - Aborting...');
end;
/

set termout off
set heading off
spool cr_clust.sql
select 'rem cr_clust.sql' from dual;
select 'rem' from dual;
select 'rem ***** All clusters for database ' || name from v$database;
select 'rem' from dual;
select 'set feedback off' from dual;
select text from clus_temp order by lineno;
spool off
drop table clus_temp;
set termout on
select 'Created cr_clust.sql...' from dual;
set termout off
exit