--Scripts for customer to create exadata analytics user
set serveroutput on FORMAT WORD_WRAPPED
set echo on
set linesize 512
set serveroutput on size unlimited
 
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
 
declare
 
    l_user      varchar2(1024) := '&1'; -- replace with your user
    l_password  varchar2(1024) := '&2'; -- replace with your pwd
 
    function schema_exists(p_user in varchar2) return boolean as
        n      number;
    begin
        select count(1) into n from dba_users where username=upper(p_user);
        return (n != 0);
    end;
 
    procedure create_domain_user (p_user in varchar2, p_passwd in varchar2) is
        l_sql          varchar2(4000);
 
    begin
        dbms_output.put_line(' Checking if schema ' || p_user || '  exists');
        if not schema_exists(p_user) then
            dbms_output.put_line(' creating Schema ');
            l_sql := 'CREATE USER '|| p_user ||' IDENTIFIED BY "'|| p_passwd ||'"';
            execute immediate l_sql;
            dbms_output.put_line(' Schema user created ' || p_user);
        else
            dbms_output.put_line(' Schema exists ');
        end if;
    exception when others then
        dbms_output.put_line(' Error creating schema ' || p_user );
        dbms_output.put_line(' Error ' || SQLCODE  || '   Msg: ' || substr(SQLERRM,1,1024) );
        raise;
    end;
 
    procedure exec_sql(p_sql in varchar2) is
    begin
        execute immediate p_sql;
    exception when others then
        dbms_output.put_line(' Error running SQL  : ' || p_sql || '  ErrCode: ' || SQLCODE ||  '    Msg: ' || substr(SQLERRM,1,512));
    end;
 
    procedure grant_access_to_user (p_user in varchar2) is
        l_sql          varchar2(4000);
    begin
 
       l_sql := 'GRANT CREATE SESSION TO ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE TABLE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE  PROCEDURE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE SEQUENCE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE TYPE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE SESSION TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT UNLIMITED TABLESPACE to  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT RESOURCE, CONNECT TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT CREATE ANY JOB TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT DWROLE TO  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT EXECUTE ON DBMS_CLOUD to  ' || l_user;
        exec_sql(l_sql);
 
       l_sql :='GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO  ' || l_user;
        exec_sql(l_sql);
 
        -- grants to the schema user
        l_sql := 'GRANT CREATE SESSION TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CREATE TABLE TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CREATE PROCEDURE TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CREATE JOB TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CREATE TYPE TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT CTXAPP TO  ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.V_$PGASTAT TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.V_$SESSION TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.V_$PARAMETER TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.V_$RESERVED_WORDS TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT SELECT ON SYS.DBA_SEGMENTS TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_LOB TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_OUTPUT TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_LOCK TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_UTILITY TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_ASSERT TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_SCHEDULER TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_APPLICATION_INFO TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_RLS TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'GRANT EXECUTE ON SYS.DBMS_INMEMORY TO ' || l_user;
        exec_sql(l_sql);
 
        l_sql := 'ALTER USER ' || l_user || ' QUOTA UNLIMITED ON DATA';
        exec_sql(l_sql);
    end;
 
    procedure enable_hints_to_system is
        l_sql          varchar2(4000);
        l_value        varchar2(4000);
    begin
        select upper(value) into l_value from V$PARAMETER where name = 'optimizer_ignore_hints';
 
        if l_value = 'TRUE' then
            l_sql := 'ALTER SYSTEM SET OPTIMIZER_IGNORE_HINTS = FALSE';
            dbms_output.put_line('Enabling Optimizer hints');
            exec_sql(l_sql);
        end if;
 
        select upper(value) into l_value from V$PARAMETER where name = 'optimizer_ignore_parallel_hints';
        if l_value = 'TRUE' then
            l_sql := 'ALTER SYSTEM SET OPTIMIZER_IGNORE_PARALLEL_HINTS = FALSE';
            dbms_output.put_line('Enabling Optimizer parallel hints');
            exec_sql(l_sql);
        end if;
    end;
 
begin
 
    dbms_output.put_line('Enabling Hints if not enabled');
    enable_hints_to_system();
 
    dbms_output.put_line('Schema processing  ' || l_user ||  '     Pwd ' || l_password);
    create_domain_user(l_user, l_password);
 
    grant_access_to_user(l_user);
    dbms_output.put_line('Schema processed ');
end;
/