Creation of new user in Oracle DB

Symptoms

When we try to enter the new user/password and select OK, we get this error:
Cannot configure database.
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SETTINGS_GETVALUE' must be declared
ORA-06550:line 1, column 7:
PL/SQL: Statement ignored in method { call Settings_GetValue(?, ?, ?, ?, ?, ?, ?, ?) }
Inner Exception: SOAP Server Application Faulted

Cause

The new user is bound to his schema and by default searches for objects in it.

Resolution

As a workaround for that:

  1. LOGON-trigger
    On our instance of BD we need to create a LOGON-trigger that will change a CURRENT_SCHEMA for a specified user:
CREATE OR REPLACE TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE
BEGIN
IF USER = 'flexicap' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = phsdba';
END IF;
EXCEPTION
WHEN OTHERS
THEN NULL;
END;
/
  1.  Import/export of an old schema to a new schema:
    You can do it with something like that:
expdp phsdba/<phsdba_password>@<server_name> schemas=phsdba dumpfile=phsdba.dmp logfile=exp_phsdba.log
impdp flexicap/<flexicap_password>@<server_name> dumpfile=phsdba.dmp logfile=imp_flexicap.log remap_schema=phsdba:flexicap TRANSFORM=oid:n

In a case that you will have some SEQUENCE issues with tables identifiers, you can resolve this by the script:

DECLARE  v_preview_only CHAR(1) := 'Y';
  v_max_id NUMBER;
  v_seq_num NUMBER;
BEGIN
  FOR seq IN (
    SELECT T.TABLE_NAME, S.SEQUENCE_NAME, S.LAST_NUMBER
    FROM (
      SELECT SEQUENCE_NAME, LAST_NUMBER, INCREMENT_BY,
        CASE WHEN SEQUENCE_NAME = 'DOCUMENTES_IDENTITY' THEN 'DOCUMENTEDITINGSTATISTIC'
          WHEN SEQUENCE_NAME = 'STATISTICBPARAMARCH_IDENTITY' THEN 'STATISTICBATCHPARAMETERARCHIVE'
          WHEN SEQUENCE_NAME = 'STATISTICBDTYPEARCH_IDENTITY' THEN 'STATISTICBATCHDOCUMENTTYPEARCH'
          ELSE UPPER(SUBSTR(SEQUENCE_NAME, 0, LENGTH(SEQUENCE_NAME) - LENGTH('_IDENTITY')))
        END AS TABLE_NAME
      FROM USER_SEQUENCES
      WHERE SEQUENCE_NAME LIKE '%!_IDENTITY' ESCAPE '!'
    ) S
    JOIN (
      SELECT TABLE_NAME
      FROM USER_TAB_COLUMNS
      WHERE COLUMN_NAME = 'ID'
        AND TABLE_NAME NOT IN (SELECT VIEW_NAME FROM USER_VIEWS)
    ) T ON UPPER(T.TABLE_NAME) LIKE (UPPER(S.TABLE_NAME) || '%')
  )
  LOOP
    v_seq_num := seq.LAST_NUMBER;
    EXECUTE IMMEDIATE 'SELECT NVL(MAX(ID), 0) FROM "'|| seq.TABLE_NAME || '"'INTO v_max_id;
    IF (v_max_id - v_seq_num) > -1 THEN
      IF v_preview_only = 'Y' THEN
        IF (v_max_id - v_seq_num) > 0 THEN
          DBMS_OUTPUT.PUT_LINE('ALTER SEQUENCE '|| seq.SEQUENCE_NAME || ' INCREMENT BY ' || (v_max_id - v_seq_num + 2) || ';');
          DBMS_OUTPUT.PUT_LINE('SELECT '|| seq.SEQUENCE_NAME || '.NEXTVAL FROM DUAL;');
          DBMS_OUTPUT.PUT_LINE('ALTER SEQUENCE '|| seq.SEQUENCE_NAME || ' INCREMENT BY 1;');
        ELSE
          DBMS_OUTPUT.PUT_LINE('SELECT '|| seq.SEQUENCE_NAME || '.NEXTVAL FROM DUAL;');
        END IF;
      ELSE
        IF (v_max_id - v_seq_num) > 0 THEN
          EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| seq.SEQUENCE_NAME || ' INCREMENT BY ' || (v_max_id - v_seq_num + 2);
          EXECUTE IMMEDIATE 'SELECT ' || seq.SEQUENCE_NAME || '.NEXTVAL FROM DUAL' INTO v_seq_num;
          EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq.SEQUENCE_NAME || ' INCREMENT BY 1';
        ELSE
          EXECUTE IMMEDIATE 'SELECT '|| seq.SEQUENCE_NAME || '.NEXTVAL FROM DUAL' INTO v_seq_num;
        END IF;
        DBMS_OUTPUT.PUT_LINE(seq.TABLE_NAME || ': max_id=' || v_max_id || '; seq_curr=' || v_seq_num || '; seq_prev=' || seq.LAST_NUMBER);
      END IF;
    END IF;
  END LOOP;
END;

 

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.

Recently viewed