I have different Oracle DB versions with which I work with Toad 11.6 - 10.2, 11.2 and 12.1. The problem is that when I access the Users tab on the Oracle DB 12.1 I am getting the following error message:
ORA-01841: (full) year must be between -4713 and +9999 and not be 0
I was able to figure out that it is caused by the new filed LAST_LOGIN introduced in Oracle DB 12c in dba_users, which was not existing in previous Oracle DB versions. I spooled SQL and found taht when I go to Users tab Toad executes this query:
Select u., v.
from sys.DBA_USERS u, sys.v_$pwfile_users v
where 1=1
and u.username = v.username(+)
What can I do in order to be able to see users in the Users tab in Toad 11.6 for the Oracle 12c databases? For other versions of Oracle databases that I work with I don’t have this problem. Shall I put any filter on displaying Users tab or maybe to amend the above query somehow and make Toad to execute the amended query instead the above mentioned?
I don’t think this is a Toad problem. Toad 11.6 does not know about the new column so it is not referencing it.
It sounds like the error is coming from the DBA_USERS view inside the database. If so, you should be able to reproduce the problem by running that query in SQL*Plus.
Select u., v. from sys.DBA_USERS u, sys.v_$pwfile_users v where 1=1 and u.username = v.username(+)
which Toad executes when I go to Users tab in sqlplus in my 12.1 DB as sys user and it is not giving error, but the same query returns the error ORA-01841 when executed in Toatd 11.6.
Also when I run the query in sqlplus as the user who is owner of the schema it returns 75 records (users) and ends with the error ORA-01841. Total number of users in my 12.1 database are 278
Might it be something with the rights for the DBA_USERS view? But if the above query works in sqlplus as sys user, why it doesn’t work in Toad 11.6 as sys user?
Is it possible to replace the standard query that Toad executes when I go to Users tab (mentioned above) with this one for example?
Select u.username,u.user_id,u.password,u.account_status,u.lock_date,u.expiry_date,u.default_tablespace,u.temporary_tablespace,u.created,u.profile,u.initial_rsrc_consumer_group,external_name, v.* from sys.DBA_USERS u, sys.v_$pwfile_users v where 1=1 and u.username = v.username(+)
When I run the query that Toad is executing when I am going to Users tab - Select u., v. from sys.DBA_USERS u, sys.v_$pwfile_users v where 1=1 and u.username = v.username(+) in sqlplus as sys user in my 12.1 database it is not giving any error. But when I run this query in sqlplus as the user who is the owner of the database it gives 75 records (users) and then returns the error ORA-01841: (full) year must be between -4713 and +9999, and not be 0. So, the query is successful in sqlplus only when it is run as sys user. But in Toad 11.6 even as sys when I go to Users tab it returns the error ORA–01841.
Might it be something with the rights to the DBA_USERS view that causes this error?
Is it possible to replace the standard query that Toad 11.6 runs when I go to Users tab with this query for example:
Select u.username,u.user_id,u.password,u.account_status,u.lock_date,u.expiry_date,u.default_tablespace,u.temporary_tablespace,u.created,u.profile,u.initial_rsrc_consumer_group,external_name, v.* from sys.DBA_USERS u, sys.v_$pwfile_users v where 1=1 and u.username = v.username(+)