Oracle Tips and Tricks I

Check the user profile and set it to unlimited if you have password expired issue. Please execute following SQL statements to set password life time unlimited.

Login as the dba user. In this case I have logged in as the user SYS with OS authentication.

sqlplus / as sysdba

desc dba_users;

Search for the user's profile and alter the profile to set password life time unlimited.

select profile from dba_users where username in ('USER1','USER2');

alter profile default limit password_life_time unlmited;

UNICODE Support on the insert command using Windows CMD.
chcp 65001
set NSL_LANG=.AL32UTF8

Check the Temp segments used by the sessions.

SELECT *
FROM v$sort_usage;

SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE, su.segtype, su.CONTENTS
FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr;

Check the Undo segments used by the sessions.

SELECT s.username, s.SID, s.serial#, s.logon_time, t.xidusn, t.ubafil, d.tablespace_name,
t.ubablk, t.used_ublk, t.start_date, t.status
FROM v$session s, v$transaction t ,dba_rollback_segs d
WHERE s.saddr = t.ses_addr
and t.xidusn=d.segment_id;

Resize Datafiles

ALTER DATABASE DATAFILE '' | FILEID  RESIZE 2M;

AWR generating script

#!/bin/bash

start_snap_id=21997
end_snap_id=22009
db_id=2409431886
file_name_prefix="AWR_"$(hostname);


while [ ${start_snap_id} -le ${end_snap_id} ]
do
        echo ${start_snap_id}
        let stop_snap_id=start_snap_id+1
sqlplus -S utility/oracle< ${file_name_prefix}_${start_snap_id}_${stop_snap_id}.html
        set feedback off;
        set heading off;
        select output from table (dbms_workload_repository.awr_report_html (${db_id},1,${start_snap_id},${stop_snap_id}));
        exit;
EOF
        let start_snap_id=stop_snap_id

done

Getting hostname of the machine

SELECT UTL_INADDR.get_host_name FROM dual;

Oracle setting table column unused and drop unused column later.

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);

List all unused columns

SELECT * FROM DBA_UNUSED_COL_TABS;

Drop unused columns

ALTER TABLE table_name drop unused columns;