Listing inactive database accounts without auditing turned on (Oracle V11)

I sometimes get asked for information on when users last logged into a database so that the application owner can identify inactive accounts. This is especially useful if they are licensed by the number of users.

This is fine if you are on V12:

select username,last_login from dba_users;

USERNAME             LAST_LOGIN
--------             ----------
ANDY                 10-JAN-17 10:53:22

However, V11 doesn't have this column, and unless you have auditing on you can't use the audit trail.

Due to a quirk with the accounts, an account_status will show as OPEN if the user hasn't logged in since the password expired.

So this query:

select a.name,a.ctime,a.ptime,b.account_status,b.profile,c.limit
from sys.user$ a,dba_users b,dba_profiles c
where c.resource_name='PASSWORD_LIFE_TIME'
and a.name=b.username
and b.profile=c.profile
and c.limit != 'UNLIMITED';

will show the last time the password was changed (ptime) and the number of days after which it will expire (limit).

 NAME CTIME     PTIME    ACCOUNT_STATUS     PROFILE            LIMIT
--------------- -------  ---------          --------           ---- 
ANDY 02-FEB-15 02-FEB-15 OPEN         APP_ADM_DEFAULT          40

So this shows that even though the user was created and password set on the 02-FEB-15, and the password expires after 40 days, the account is still OPEN, not LOCKED or EXPIRED.

According to this Oracle Support note -


Why Account Status Is Open When Expiry Date is Old Date in DBA_USERS (Doc ID 291195.1)

unless the user attempts to connect after the password has expired, the account will show as OPEN.

So any account that is in OPEN status and is past the expiry_date for the profile is likely to be unused.

Adding FontAwesome Icons to Buttons in APEX V5

I found that you can add FontAwesome icons to buttons on another blog, but can't find it now. The blog in question just had the icons but didn't explain how to implement them, so I've written this.

It makes buttons look a lot more professional.

From this:

To this:

You can also change the colours:

APEX V5 has FontAwesome support built in, although it's not the latest version, so some of the more recent icons won't work. If that is the case, you won't see the icon on the button, so pick one that works.

Go here to see the list of the FontAwesome Icons : http://fontawesome.io/cheatsheet/

So, to create a button with these is very easy, you just need to use the 'text with icon' template and tell it the icon you want.

Assuming you have a page with a region, click on the 'add button' icon.

Go through the prompts as usual, but when you get to the 'Button Attributes' select the 'Text with Icon' template:

Add the button as normal, selecting the position and name. I called mine 'Disk Space'.

Once added, select the button from the design page:

Scroll down to the 'Icon CSS Classes' property, and enter the name of the icon from the FontAwesome list in the link above.

In this case, I used 'fa-database':

Apply the changes, and play the page, and the icon will be shown on the button:

Here, the icon is to the right of the text - I prefer it on the left. This can be changed by updating the 'Icon Position' property.

Changing the colour is just a matter of updating the 'Type' property:

Danger is red, Primary is blue, Success is green and Warning is orange.

So, pretty easy to make the buttons look professional - here are some from one of my applications: