Rem  Copyright (c) Oracle Corporation 2014. All Rights Reserved.
Rem
Rem    NAME
Rem      ords_verify_pdbs_tablespaces.sql
Rem
Rem    DESCRIPTION
Rem      This script checks if the tablespaces exist in the PDBs.
Rem
Rem    REQUIREMENTS
Rem      - Oracle Database 12.1 or later
Rem
Rem    NOTES
Rem
Rem    Arguments:
Rem      1  : Default tablespace for ORDS_METADATA
Rem      2  : Temporary tablespace for ORDS_METADATA
Rem      3  : Default tablespace for ORDS_PUBLIC_USER
Rem      4  : Temporary tablespace for ORDS_PUBLIC_USER
Rem
Rem    MODIFIED    (MM/DD/YYYY)
Rem     epaglina    06/01/2015 Created.
Rem

set define '^'
set verify off
set serveroutput on

define ORDS_DATATBS = '^1'
define ORDS_TEMPTBS = '^2'
define PUB_DATATBS  = '^3'
define PUB_TEMPTBS  = '^4'

declare
  l_is_cdb varchar2(10) := 'NO';
  
  procedure check_pdbs_tablespaces(p_def_tablespace in varchar2, p_temp_tablespace in varchar2) is
   l_pdbs varchar2(4000) := null;
  begin
   $IF sys.dbms_db_version.ver_le_12 $THEN
      -- Check that the pdbs default tablespace exist      
	    for c1 in (select c.name name, c.con_id con_id from v$pdbs c
                 where c.name != 'PDB$SEED' and
                       c.open_mode not in ('MOUNTED','READ ONLY') and
                       c.name not in (select x.name from v$pdbs x, cdb_tablespaces y
                                      where x.name != 'PDB$SEED' and
                                            x.open_mode not in ('MOUNTED','READ ONLY') and
                                            x.con_id = y.con_id and
                                            y.tablespace_name = upper(p_def_tablespace)))
      loop
        if (l_pdbs is null) then
          l_pdbs := c1.name;
        else
          l_pdbs := l_pdbs || ' ' || c1.name;
        end if;
      end loop;

    if (l_pdbs is not null) then
       raise_application_error(-20022, 'ERROR: The default tablespace ' || p_def_tablespace || 
                                            ' does not exist in ' || l_pdbs || 
                                            '. An existing tablespace is required for ORDS installation.');
    end if;

      -- Check that the pdbs temporary tablespace exist      
	    for c1 in (select c.name name, c.con_id con_id from v$pdbs c
                 where c.name != 'PDB$SEED' and
                       c.open_mode not in ('MOUNTED','READ ONLY') and
                       c.name not in (select x.name from v$pdbs x, cdb_temp_files y
                                      where x.name != 'PDB$SEED' and
                                            x.open_mode not in ('MOUNTED','READ ONLY') and
                                            x.con_id = y.con_id and
                                            y.tablespace_name = upper(p_temp_tablespace)))
      loop
        if (l_pdbs is null) then
          l_pdbs := c1.name;
        else
          l_pdbs := l_pdbs || ' ' || c1.name;
        end if;
      end loop;

    if (l_pdbs is not null) then
       raise_application_error(-20022, 'ERROR: The temporary tablespace ' || p_temp_tablespace || 
                                            ' does not exist in ' || l_pdbs || 
                                            '. An existing tablespace is required for ORDS installation.');
    end if;
    
    dbms_output.put_line('Completed verifying PDB(s) default tablespace '|| p_def_tablespace || 
                          ' and temporary tablespace ' || p_temp_tablespace ||
                          ' for ' || sys_context('USERENV','CON_NAME') );
   $ELSE
      null;
   $END	  
  end check_pdbs_tablespaces;
  
begin
   $IF sys.dbms_db_version.ver_le_12 $THEN
      -- If container is CDB$ROOT, check if the PDBs contain the user-specified tablespaces
      select cdb into l_is_cdb from v$database;
      
      if (l_is_cdb = 'YES') AND (sys_context('USERENV','CON_NAME') = 'CDB$ROOT') then      
        if ('^ORDS_DATATBS' <> '-DEFAULT' and '^ORDS_TEMPTBS' <> '-DEFAULT') then
           -- dbms_output.put_line('Checking ORDS tablespace:');
	       check_pdbs_tablespaces('^ORDS_DATATBS','^ORDS_TEMPTBS');
	    end if;
           
        if ('^PUB_DATATBS' <> '-DEFAULT' and '^PUB_TEMPTBS' <> '-DEFAULT') then
           -- dbms_output.put_line('Checking PUBLIC tablespace:');
	       check_pdbs_tablespaces('^PUB_DATATBS','^PUB_TEMPTBS');
	    end if;
      end if;
  
   $ELSE
      null;
   $END
	
end;
/
