0 Replies Latest reply on Dec 10, 2019 2:22 AM by Peter Lundqvist

    Pre-created database in 19.11

    Peter Lundqvist
      Share This:

      I just want to provide some feedback, and perhaps get some in return, towards the installation process.

      tl; dr; Can you please use uppercase schema names in view definition or omit them completely?

       

      When setting up the oracle environment I ran into some issues with the supplied database dump.

       

      Not wanting to use the supplied namespace and the supplied schema (due to in-house policies) I used the remap options and ran this command:

      impdp system/yabadabadoo remap_tablespace=DWPCSAAS:MYTABLESPACE remap_schema=DWPCSAASADMIN:MYSCHEMA directory=<mydirectory> dumpfile=20191114_DWPCSaaSAdmin.dpdmp logfile=imp_MYLOG.log full=y

       

      This resulted in 260 import errors due to views not compiling successfully.

       

      Investigating the invalid view definitions I noticed that that some, not all, view definitions refered to "DWPCSaaSAdmin" instead of "DWPCSAASADMIN".

       

      To fix this I had to create some pl/sql (never done this before) and handle exceptions (some views depends on other views that are invalid) and run it three times.

      set serveroutput on
      
      DECLARE
          vddl          varchar2(4000);
          err_cnt       number;
      BEGIN
          err_cnt:=0;
          FOR cur_rec in (select * from dba_objects where owner='MYSCHEMA' and object_type='VIEW' and status!='VALID') -- and object_name='ASSOCIATION')
          LOOP
          
              IF cur_rec.object_type = 'VIEW' THEN
      
                  FOR ddl_rec in (select dbms_metadata.get_ddl(cur_rec.object_type, cur_rec.object_name, cur_rec.owner) as foo from dual)
                  LOOP
                  
                      BEGIN
                          vddl := replace(ddl_rec.foo, 'DWPCSaaSAdmin', cur_rec.owner);
                          dbms_output.put_line(vddl);
                          EXECUTE IMMEDIATE vddl;
                      EXCEPTION WHEN OTHERS THEN
                          IF SQLCODE = -24344 THEN
                              err_cnt := err_cnt + 1;
                              CONTINUE;
                          ELSE
                              dbms_output.put_line('sqlcode: ' || SQLCODE);
                              RAISE;
                          END IF;
                      END;
                      
                  END LOOP;
                  
              END IF;
          END LOOP;
          dbms_output.put_line('Nr errors: ' || err_cnt);
      END;
      
      

       

      I feel this is not a good experience, especially considering the documentation. Can we do this better?

      I miss not having the pre-created database.