To be more proactive in prevention of account expirations, create an automated report identifying those nearing expiration. Using the scripts outlined below, a report can be set up to run on startup. This provides awareness with ease. The following were excecuted using the SYS account.

Create an account responsible for the report process. Be sure to grant connect privilege to this account.

CREATE USER Expire_Report_User IDENTIFIED BY “EXAMPLE”;
GRANT CONNECT TO Expire_Report_User;

Next, create a directory object specifying the destination for the report. Assign write privilege to account created.

CREATE DIRECTORY DIR_EXPIRE_REPORT AS ‘C:UsersmbondDesktop’;
GRANT WRITE ON DIRECTORY DIR_EXPIRE_REPORT TO Expire_Report_User;

Create a view using fields from the DBA_USERS table, including USERNAME, ACCOUNT_STATUS, and EXPIRY_DATE. Grant select privilege to the execution account and create a private synonym the account.

CREATE OR REPLACE VIEW V_ACCOUNT_EXPIRATION AS (
SELECT
USERNAME,
ACCOUNT_STATUS,
EXPIRY_DATE
FROM
DBA_USERS) WITH READ ONLY;
GRANT SELECT ON V_ACCOUNT_EXPIRATION TO Expire_Report_User;
CREATE SYNONYM Expire_Report_User.V_ACCOUNT_EXPIRATION FOR SYS.V_ACCOUNT_EXPIRATION;

Write a stored procedure that generates the report using the previously created view.

CREATE OR REPLACE PROCEDURE SP_EXPIRE_REPORT
AS
O_FILE UTL_FILE.FILE_TYPE;
l_fmt1 CONSTANT VARCHAR2(34) := ‘

‘;
l_fmt3 CONSTANT VARCHAR2(19) := ‘