DB version: 19c
OS : RHEL 7.8
I frequently refresh development (UAT) schemas from production. That is, I drop the UAT schema and recreate it from production using expdp,impdp.
But, I want to retain the schema passwords in UAT environment (None of the UAT schemas should be using production passwords)
In 11g days, I can just save the password of my UAT schema in hash format by running the following query in UAT.
SET LONG 2000000
SET PAGESIZE 0
set lines 600
col ddl format a500
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) DDL
FROM DBA_USERS
where username = 'SCOTTY' ;
And the following will be returned which shows SCOTTY's password in hash format.
This way, I can retain the password of SCOTTY user in UAT.
CREATE USER "SCOTTY" IDENTIFIED BY VALUES 'S:MM48AEC57B75C15D8506DB3CF0BF9CC354E780E94B162B3C0603CEB6534D;45DFFE223F631D49'
DEFAULT TABLESPACE "SCOTTY_DATA"
TEMPORARY TABLESPACE "TEMP"
PROFILE "UNLIMITED"
PASSWORD EXPIRE
ACCOUNT LOCK;
Unfortunately, in 19c, the above query on DBMS_METADATA.GET_DDL returns the following
CREATE USER "SCOTTY" IDENTIFIED BY VALUES 'S:000000000000000000000000000000000000000000000000000000000000'
DEFAULT TABLESPACE "SCOTTY_DATA"
TEMPORARY TABLESPACE "TEMP"
PROFILE "UNLIMITED"
PASSWORD EXPIRE
ACCOUNT LOCK; ----> Not sure why 'ACCOUNT LOCK' is displayed when the account is in OPEN state
SQL> select username, account_status from dba_users where username = 'SCOTTY';
USERNAME ACCOUNT_STATUS
------------------------- --------------------------------
SCOTTY OPEN
--- Checked spare4 column found in the internet too. No luck. It returns null
SQL> col spare4 for a100
SQL> select spare4 from sys.user$ where name='SCOTTY';
SPARE4
----------------------------------------------------------------------------------------------------
SQL>
Any workaround for this ?
If no workaround, then the only way left for me is to not drop the UAT schema before refresh and drop all the objects individually.
But, there are hundreds of objects of different object types in these schemas that need to dropped before I run the impdp.