Useful Database Scripts

How to revoke a privilege/role from a database user
SQL>REVOKE CREATE TABLE, accts_rec FROM tsmith;

How to find when a user last logged into a database
SQL>select username,machine,terminal,to_char(logon_time,'HH:MM :SS') from v$session where username='';

How to force a database user to change password
SQL>ALTER USER scott PASSWORD EXPIRE; -- Force user to choose a new password

How to unlock an account
SQL>ALTER USER "schema_name" IDENTIFIED BY "newpass" ACCOUNT UNLOCK;

Check database for check for Default Passwords being used for some common usernames
SQL>select username "User(s) with Default Password!", account_status "Status"
from dba_users
where password in
('E066D214D5421CCC', -- dbsnmp
'24ABAB8B06281B4C', -- ctxsys
'72979A94BAD2AF80', -- mdsys
'9AAEB2214DCC9A31', -- mdsys
'C252E8FA117AF049', -- odm
'A7A32CD03D3CE8D5', -- odm_mtr
'88A2B2C183431F00', -- ordplugins
'7EFA02EC7EA6B86F', -- ordsys
'9B616F5489F90AD7', -- ordcommon
'4A3BA55E08595C81', -- outln
'F894844C34402B67', -- scott
'3F9FBD883D787341', -- wk_proxy
'79DF7A1BD138CF11', -- wk_sys
'7C9BA362F8314299', -- wmsys
'88D8364765FCE6AF', -- xdb
'F9DA8977092B7B81', -- tracesvr
'9300C0977D7DC75E', -- oas_public
'A97282CE3D94E29E', -- websys
'AC9700FD3F1410EB', -- lbacsys
'E7B5D92911C831E1', -- rman
'AC98877DE1297365', -- perfstat
'66F4EF5650C20355', -- exfsys
'84B8CBCA4D477FA3', -- si_informtn_schema
'D4C5016086B2DC6A', -- sys
'5638228DAF52805F', -- sys
'D4DF7931AB130E37') -- system
/
NB. does not work in 11g upwards
/

Find users in a database with password set to username
SQL>create or replace procedure sys.find_joes as
-- Find users that have their password equal to their username
hexpw varchar2(30);
modpw varchar2(30);
un varchar2(30);
cursor c1 is select username,password from dba_users
where length(trim(password)) = 16; -- only consider db authenticated
begin
for i in c1 loop
hexpw := i.password;
un := i.username;
execute immediate 'alter user '||un||' identified by '||un;
select password into modpw from dba_users where username = un;
if modpw = hexpw then
dbms_output.put_line(un);
else
-- change password back to what it was
execute immediate
'alter user '||un||' identified by values '''||hexpw||'''';
end if;
end loop;
end;
/

SQL> set serveroutput on
SQL> exec sys.find_joes;

How to lock/unlock database user account
SQL> ALTER USER username ACCOUNT LOCK;
SQL> ALTER USER username ACCOUNT UNLOCK;

How to change a database users password
There are two different methods for doing so:
SQL>ALTER USER IDENTIFIED BY ;
Or
SQL> password
Changing password for SCOTT
Old password:
New password:
Retype new password:

How to create a new database user
SQL>CREATE USER scott
IDENTIFIED BY tiger -- Assign password
DEFAULT TABLESPACE tools -- Assign space for table and index segments
TEMPORARY TABLESPACE temp; -- Assign sort space

How to drop a database user
SQL>DROP USER scott CASCADE; -- Remove user

How to assign database user privileges
SQL>RANT CONNECT, RESOURCE TO Scott;
SQL>GRANT DBA TO scott; -- Make user a DB Administrator
SQL>ALTER USER scott QUOTA UNLIMITED ON tools;

How to find database version
SQL>select * from v$version;

How to find all users of a database
SQL>select * from all_users;

Find User Responsibilities

SQL> SELECT frt.RESPONSIBILITY_NAME, furg.end_date
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_name = 'SYSADMIN'
AND fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1;

To search for datafile location:
SQL> select file_name
2 , tablespace_name
3 , file_id
4 from dba_data_files
5 where tablespace_name like '';

To increase tablespace size, increase the datafile size:
SQL> Alter database datafile '' resize 24G;

To add a new datafile to a tablespace:
SQL> ALTER TABLESPACE app_data
ADD DATAFILE ''
SIZE 200M;

To query how big a tablespace is and how much freespace it has:
SQL> SELECT * FROM (
SELECT c.tablespace_name,
ROUND(a.bytes/1048576,2) MB_Allocated,
ROUND(b.bytes/1048576,2) MB_Free,
ROUND((a.bytes-b.bytes)/1048576,2) MB_Used,
ROUND(b.bytes/a.bytes * 100,2) tot_Pct_Free,
ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 tot_Pct_Used
FROM (SELECT tablespace_name,
SUM(a.bytes) bytes
FROM sys.DBA_DATA_FILES a
GROUP BY tablespace_name) a,
(SELECT a.tablespace_name,
NVL(SUM(b.bytes),0) bytes
FROM sys.DBA_DATA_FILES a,
sys.DBA_FREE_SPACE b
WHERE a.tablespace_name = b.tablespace_name (+)
AND a.file_id = b.file_id (+)
GROUP BY a.tablespace_name) b,
sys.DBA_TABLESPACES c
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name
) WHERE tot_Pct_Used >=0
ORDER BY tablespace_name;