Log all database errors to a table

drop trigger log_errors_trig
/
drop table   apps.log_errors_tab
/

create table apps.log_errors_tab (
    error     varchar2(30),
    timestamp date,
    username  varchar2(30),
        osuser    varchar2(30),
        machine   varchar2(64),
    process   varchar2(8),
    program   varchar2(48))
 /

create or replace trigger log_errors_trig
    after servererror on database
declare
    var_user     varchar2(30);
    var_osuser   varchar2(30);
    var_machine  varchar2(64);
    var_process  varchar2(8);
    var_program  varchar2(48);
begin

    select username, osuser, machine, process, program
    into   var_user, var_osuser, var_machine, var_process, var_program
    from   sys.v_$session
    where  audsid = userenv('sessionid');

    insert into log_errors_tab
      values(dbms_standard.server_error(1),sysdate,var_user,
             var_osuser,var_machine,var_process,var_program);
end;
/

Create New User Based on Existing User

Here the below values are hard coded for testing purpose,you can replace them with bind variables.

Old User Name : XIBMO61
New User Name: CHANDRA
New Password : WELCOME

Note: Please make sure that you have sufficient privileges to execute the below statement.

-- Create user...
SELECT    'create user CHANDRA identified by values '''
              || 'WELCOME''' ||
                  ' default tablespace '
       || default_tablespace
       || ' temporary tablespace '
       || temporary_tablespace
       || ' profile '
       || PROFILE
       || ';'
  FROM SYS.dba_users
 WHERE username = UPPER('XIBMO61');



-- Grant Roles...
SELECT 'grant ' || granted_role || ' to CHANDRA' || DECODE(admin_option, 'YES', ' WITH ADMIN OPTION') || ';'
  FROM SYS.dba_role_privs
 WHERE grantee = UPPER('XIBMO61');

-- Grant System Privs...
SELECT 'grant ' || PRIVILEGE || ' to CHANDRA' || DECODE(admin_option, 'YES', ' WITH ADMIN OPTION') || ';'
  FROM SYS.dba_sys_privs
 WHERE grantee = UPPER('XIBMO61');

-- Grant Table Privs...
SELECT 'grant ' || PRIVILEGE || ' on ' || owner || '.' || table_name || ' to CHANDRA;'
  FROM SYS.dba_tab_privs
 WHERE grantee = UPPER('XIBMO61');

-- Grant Column Privs...
SELECT 'grant ' || PRIVILEGE || ' on ' || owner || '.' || table_name || '(' || column_name || ') to CHANDRA;'
  FROM SYS.dba_col_privs
 WHERE grantee = UPPER('XIBMO61');

-- Tablespace Quotas...
SELECT 'alter user ' || username || ' quota ' || DECODE(max_bytes, -1, 'UNLIMITED', max_bytes) || ' on ' || tablespace_name || ';'
  FROM SYS.dba_ts_quotas
 WHERE username = UPPER('XIBMO61');

-- Set Default Role...
SET serveroutput on

DECLARE
   defroles                      VARCHAR2(4000);
BEGIN
   FOR c1 IN (SELECT *
                FROM SYS.dba_role_privs
               WHERE grantee = UPPER('XIBMO61') AND default_role = 'YES')
   LOOP
      IF LENGTH(defroles) > 0
      THEN
         defroles                   := defroles || ',' || c1.granted_role;
      ELSE
         defroles                   := defroles || c1.granted_role;
      END IF;
   END LOOP;

   DBMS_OUTPUT.put_line('alter user CHANDRA default role ' || defroles || ';');
END;
/