July, 2012
Set date+time (NLS_DATE_FORMAT)
SQL> alter session set nls_date_format=’DD/MON/YY HH:MI:SS PM’; Session altered. SQL> select sysdate from dual; SYSDATE ——————— 27/MAY/11 10:33:31 AM
Create a DBMS_JOB
–schedules this job to run immediately and then every hour after that DECLARE JobNo dba_jobs.job%TYPE; BEGIN DBMS_JOB.SUBMIT ( job => JobNo, what => ‘begin SESSIONS_RM(minutes=>180); end;’, next_date => SYSDATE, interval => ‘SYSDATE + 1/24’); COMMIT; END; /
TAR / TAR.Z
How to create and extract zip, tar, tar.gz and tar.bz2 files in Linux Data compression has been extremely useful to us over the years. Whether its a zip file containing images to be sent in a mail or a compressed data backup stored on a server, we use data compression to save valuable hard drive […]
COLD BACKUP Explained
Determine the files you will need: The datafiles: SQL> select name from v$datafile The control files: SQL> select name from v$controlfile The temp datafiles: SQL> select name from v$tempfile The online redo logs: SQL> select member from v$logfile The parameter file (spfile): SQL> show parameter spfile; Take a few backup precautions: Backup the control file: […]
view_fra.sql
set lines 185 col df format 999,999,999 heading “Datafile Size (in MB)” col tf format 999,999,999 heading “Tempfile Size (in MB)” col rd format 999,999,999 heading “Redo Logfile Size (in MB)” col nm format a50 heading “Location” col sl format 999,999,999 heading “Size (in MB)” col su format 999,999,999 heading “Used (in MB)” col sr […]
Get good idea for open_cursors value
SQL> select value, name from v$sysstat where name like ‘%cursor%’; SQL> select ‘session_cached_cursors’ parameter, lpad(value, 5) value, decode(value, 0, ‘ n/a’, to_char(100 * used / value, ‘990’) || ‘%’) usage from ( select max(s.value) used from v$statname n, v$sesstat s where n.name = ‘session cursor cache count’ and s.statistic# = n.statistic# ), ( select value […]
Resize tablespaces including UNDO workaround
We can resize the database datafiles with: SQL> ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs01.dbf’ RESIZE 500M; Most of the times when I try to resize the undo tablespace I encounter ORA-3297 error: file contains used data beyond the requested RESIZE value. This means that some undo information stills stored above the datafile size we want to set. […]
Test a Disk for I/O write timing.
time dd if=/dev/zero of=sample.data bs=8192 count=131072 131072+0 records in 131072+0 records out real 0m8.98s user 0m0.10s sys 0m6.79s
show_users.sql
column mu format a40 heading “Machine – User” select machine||’ – ‘||nvl(username,'[BKGD PROCESS]’) mu, count(*) from v$session group by machine||’ – ‘||nvl(username,'[BKGD PROCESS]’); select count(*) “Total Count of Sessions” from v$session;