How to Know When Oracle User Password Changed?

Oracle database internal table SYS.USER$ contains useful information about users and roles. Some of the columns have got following meaning:

  • NAME – name for user or role
  • TYPE# – 0 for role or 1 for user
  • CTIME – the date of creation
  • PTIME the date the password was last changed
  • EXPTIME – the date the password has last expired
  • LTIME – the date the resource was last locked
  • LCOUNT – number of failed logon
SQL> SELECT
        name,
       ctime,
       ptime
FROM
      sys.user$
WHERE
      name = 'SYS';

NAME                 CTIME     PTIME
-------------------- --------- ---------
SYS                  31-MAR-16 11-MAY-21

ORA-00942: table or view does not exist ORA-01031: insufficient privileges while creating materialized view

ORA-00942 or ORA-01031 error occurs while creating a materialized view from a table of another schema.

Source Table : USERA.TABLE_A
Materialized View : USERB.TABLE_A_MV

SQL> show user
USER is "USERB"
SQL>  create materialized view TABLE_A_MV
BUILD IMMEDIATE
REFRESH FAST ON commit
with primary key
as select * from usera.table_a ;
as select * from usera.table_a
                           *
ERROR at line 5:
ORA-12018: following error encountered during code generation for
"USERB"."TABLE_A_MV"
ORA-00942: table or view does not exist

But USERB has no problem to access USERA.TABLE_A:

SQL> show user
USER is "USERB"
SQL> select count(*) from  usera.table_a;
  COUNT(*)
----------
   1623583

If creating the materialized view by sys user, then gets another different error:

SQL> show user
USER is "SYS"
SQL> create materialized view USERB.TABLE_A_MV
BUILD IMMEDIATE
REFRESH FAST ON commit
with primary key
as select * from usera.table_a ;
as select * from usera.table_a
*
ERROR at line 4:
ORA-01031: insufficient privileges

Subscribe to get access

Read more of this content when you subscribe today.

ORA-4098 from After Logon Trigger

SQL> connect testuser
Enter password:
ERROR:
ORA-04098: trigger 'SYS.CHECK_USERS_TRIGGER' is invalid and failed
re-validation


Warning: You are no longer connected to ORACLE.

SOLUTION

There is an after logon trigger which is invalid.  To fix the issue by either way of the following :

1)Fix the after logon trigger to make it valid.

2)Disable the invalid after logon trigger.

SQL>ALTER TRIGGER SYS.CHECK_USERS_TRIGGER DISABLE ;

3)Drop the invalid after logon trigger.

SQL>drop trigger SYS.CHECK_USERS_TRIGGER;

We must be careful when using AFTER LOGON triggers, because if after logon trigger becomes invalid or failed to execute due to some other errors, it prevents the users (even DBA users) from logging in. The only way to resolve this error would be to connect with SYSDBA privileges.

ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, line 536 ORA-29283: invalid file operation

Connected to Oracle database through service name, then get  errors by running the below sample code.  There is no problem to run the sample code while connecting to database through IPC.

$sqlplus testuser/****@DBTEST

SQL>DECLARE
    l_output utl_file.file_type;
BEGIN
    l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
    utl_file.fclose( l_output ); 
END;
/

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4
$sqlplus  / as sysdba
SQL> DECLARE
     l_output utl_file.file_type;
BEGIN
     l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
     utl_file.fclose( l_output );
END;

 /

PL/SQL procedure successfully completed.

CAUSES

New group “apex” is just added into oracle user for being able to read/write MY_DIR directory.

$ id oracle
uid=100(oracle)gid=200(oinstall)groups=200(oinstall),300(dba),400(apex)

RESOLUTION

Bounce the listener to make the changes effective.

$ ls -ltr|grep apex
drwxrwx---. 6 apexuser apex 4096 May 3 16:08 apex

$lsnrctl stop

$lsnrctl start

$sqlplus testuser/****@DBTEST

SQL>DECLARE
       l_output utl_file.file_type;
   BEGIN
       l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
       utl_file.fclose( l_output ); 
END;
/

PL/SQL procedure successfully completed.