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

start oracle.ini

set echo off
set feedback off
set serverout off

set linesize 200
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_option char prompt "Exclude USERS and TOOLS tablespaces (y/n): "

spool &&tmp_dir/cr_ts.tmp

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

DECLARE
  CURSOR cursor_exclts IS
  SELECT
	tablespace_name
  FROM
	dba_tablespaces
  WHERE
	contents<>'PERMANENT';

  tsHandle   NUMBER;
  tsTHandle  NUMBER;
  tsDDL      CLOB;
  rv_excltsname   VARCHAR2(30);
  lv_excltsnames  VARCHAR2(2000);
  tsOption CHAR;

BEGIN
  -- Create a list of tablespace names to exclude.
  tsOption := '&&p_option';

  if tsOption='Y' or tsOption='y' then
    lv_excltsnames := '''SYSTEM'',''SYSAUX'',''USERS'',''TOOLS''';
  else
    lv_excltsnames := '''SYSTEM'',''SYSAUX''';
  end if;
  
  -- Get the list of temp and undo tablespaces to exclude.
  OPEN cursor_exclts;
  LOOP
    FETCH cursor_exclts INTO rv_excltsname;
    EXIT WHEN cursor_exclts%NOTFOUND;
    lv_excltsnames := lv_excltsnames || ',''' || rv_excltsname || '''';
  END LOOP;
  CLOSE cursor_exclts;

  -- Generate the clause that will be included in the filter.
  lv_excltsnames := 'NOT IN (' || lv_excltsnames || ')';

  -- use DBMS_METADATA packge to generate the DDL statements.
  tsHandle := DBMS_METADATA.OPEN('TABLESPACE');
  DBMS_METADATA.SET_COUNT (tsHandle, 1);
  DBMS_METADATA.SET_FILTER (tsHandle, 'NAME_EXPR', lv_excltsnames);
  tsTHandle := DBMS_METADATA.ADD_TRANSFORM (tsHandle, 'DDL');
  DBMS_METADATA.SET_TRANSFORM_PARAM(tsTHandle, 'SQLTERMINATOR', TRUE);

  LOOP
    tsDDL := DBMS_METADATA.FETCH_CLOB (tsHandle);
    EXIT WHEN tsDDL IS NULL;
    dbms_output.put_line(tsDDL);
  END LOOP;

  DBMS_METADATA.CLOSE (tsHandle);

  EXCEPTION
    WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20000, SQLERRM);
END;
/

prompt
prompt spool off

spool off

prompt Run the script "&&tmp_dir/cr_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
