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.