Problem Duplicating Logger to a different Schema Name

[albp-rmt]

It appears you can't copy a schema with logger installed from one schema name to another i.e. changing INSTALL_SCHEMA in the process.

Noticed the problem when LOGGER_PURGE_JOB started failing with the following in the duplicated schema:-

ORA-12012: error on auto execute of job "LOGGER_PURGE_JOB" ORA-20000: You are not authorized to call this procedure. Change Logger pref: PROTECT_ADMIN_PROCS to false to avoid this. ORA-06512: at "LOGGER", line 420 ORA-06512: at "LOGGER", line 2121 ORA-06512: at "LOGGER", line 2156 ORA-06512: at line 1

Then tried to reinstall Logger by running logger_install.sql however it did not fix the problem. The reinstallation appeared to partially work as GLOBAL_CONTEXT_NAME in LOGGER_PREFS reflected the new schema name however INSTALL_SCHEMA did not. It was still set to the original schema name.

I suspect I can uninstall and (re)install however I want to preserve the log.

[martindsouza]

Try to run this:

l_ctx_name varchar2(35) := substr(sys_context('USERENV','CURRENT_SCHEMA'),1,23)||'_LOGCTX';

execute immediate 'create or replace context '||l_ctx_name||' using logger accessed globally';

merge into logger_prefs p
    using (select 'GLOBAL_CONTEXT_NAME' pref_name, l_ctx_name pref_value, logger.g_pref_type_logger pref_type from dual) d
        on (1=1
            and p.pref_type = d.pref_type
            and p.pref_name = d.pref_name)
    when matched then
        update set p.pref_value = d.pref_value
    when not matched then
        insert (p.pref_name, p.pref_value, p.pref_type)
        values (d.pref_name, d.pref_value, d.pref_type);

[albp-rmt]

That addresses the context issue thanks but I also think INSTALL_SCHEMA needs changing.

Can I simply run this UPDATE logged in as the new, schema user: -

UPDATE logger_prefs
   SET pref_value = USER
 WHERE     pref_name = 'INSTALL_SCHEMA'
       AND pref_type = 'LOGGER'
       AND pref_value <> USER;