/*
 * Title  : cr_seed_ts.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 29-Jul-2017
 * Release: 1.0.0
 * Purpose: Generates non system permanent seed tablespace creation script with auto extend.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout off

set linesize 120
set pagesize 0

set heading off
set headsep off
set underline off
set colsep " "

set verify off
set escape off
set embedded on

set serveroutput on
execute dbms_output.enable(10000000);

accept p_df_dir char prompt "Datafile Directory Location: "
accept p_df_size char prompt "Datafile Size: "

spool &&tmp_dir/cr_seed_ts.tmp

prompt set feedback on echo on
prompt spool &&rpt_dir/cr_seed_ts.rpt
prompt

select 
	'CREATE TABLESPACE ' || tablespace_name || chr(10) || 
	'DATAFILE ''&&p_df_dir' || lower(tablespace_name) || '_01.dbf'' SIZE &&p_df_size' || chr(10) ||
	'AUTOEXTEND ON LOGGING ONLINE PERMANENT' || chr(10) ||
	'EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT' || chr(10) ||
	'NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;'
from
	dba_tablespaces
where
	tablespace_name not in (
		'SYSTEM',
		'SYSAUX',
		'USERS',
		'TOOLS'
	)
and	contents='PERMANENT';

prompt
prompt spool off

spool off

prompt Run the script "&&tmp_dir/cr_seed_ts.tmp" manually.
prompt Note: This script generates only non system (SYSTEM, SYSAUX) permanent tablespaces. 
prompt TEMP and UNDO tablespaces are not included. Manually remove empty line(s) if exist.
prompt

set embedded off
set escape off
set verify on

set colsep " "
set underline on
set headsep on
set heading on

set pagesize 24
set linesize 80

set serverout off
set feedback on
set echo off
