How to change tablespace of sys.aud$ and how to shrink it?

To shrink the aud$ table, you have to enable "row movement". The default tablespace of aud$ is SYSAUX and because of that you need to change its tablespace first.

With 11g, changing the tablespace of aud$ table is supported.

begin

dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,          audit_trail_location_value => 'AUDTBS');

end;

alter table sys.aud$ enable row movement;

alter table sys.aud$ shrink space cascade;

How to reset an expired database user without changing the password?

The simple method to reset a user is changing the password. If you dont have chance to change the password and if you can not remember the old one, you can use the following method:

select dbms_metadata.get_ddl('USER', username), '''[^'']+''') pswd, username from dba_users;



You can find the hashed passwords for all users in the database with this query. 

After that you can set the old password and reset the account.


alter user HR identified by values 'S:ASSDSKDEIRUIERUTEUYTEIEORIEORIEPORI;ERIUERIUTY';