Handy scripts
From dbawiki
Contents
- 1 SQL
- 1.1 List invalid objects
- 1.2 Recompile all invalid objects
- 1.3 See how much space is left in the flash recovery area (FRA)
- 1.4 DBA privs tables
- 1.5 Schedule a shell job
- 1.6 Start the job
- 1.7 What statement is a user running?
- 1.8 See what statements all users are running
- 1.9 Pretty longops listing
- 1.10 Kill an Oracle session
- 1.11 Get statistics for LOB columns in a table
- 1.12 badprivs.sql
- 1.13 Generate a list of tablespaces ready for a migration
- 1.14 List the privileges assigned to users on directories
- 1.15 List the privileges assigned to users on packages/procedures
- 1.16 List the system privileges assigned to a user
- 1.17 Move files between disks
- 1.18 Pass parameter into perl one-liner from shell
- 1.19 Return epoch seconds in Perl
- 1.20 Return date from epoch seconds in Perl
- 1.21 Return epoch seconds from julian day number in Perl
- 1.22 Extract specific pieces of a date from an epoch timestamp in Perl
- 1.23 Return epoch seconds in Shell (linux)
- 1.24 Return date from epoch seconds in Shell (linux)
- 1.25 Return epoch seconds in PL/SQL
- 1.26 Return date from epoch seconds in SQL
- 1.27 A database overview using the sys.plato package
- 1.28 List datafiles for a tablespace
- 1.29 Show graph of available and free space in tablespaces
- 1.30 Another method for calculating free space in tablespace using segments
- 1.31 Work out maximum possible size of a datafile
- 1.32 Show size and maxsize at datafile level
- 1.33 Increase the existing size of a datafile
- 1.34 Increase the maximum size of a datafile
- 1.35 Add a datafile to a tablespace
- 1.36 List all datafiles with their size
- 1.37 Drop datafile from temp tablespace
- 1.38 List files that are in hot-backup mode
- 1.39 waiting for snapshot control file enqueue
- 1.40 Info on blocking processes
- 1.41 Show locked objects
- 1.42 Show which row is locked
- 1.43 Check what is audited on a database
- 1.44 How old is the oldest audit record?
- 1.45 Procedure to delete old audit records
- 1.46 Job setup to run the audit purge
- 1.47 A list of all SIDs in oratab that should restart after a server boot
- 1.48 Move the admin directories to a new location
- 1.49 Check if the Standby database is up-to-date with the primary database
- 1.50 Check for gaps on the Standby database
- 1.51 Ignore case-sensitive passwords in 11g
- 1.52 External table for the Alert log (pre 11g)
- 1.53 Alert log (11g+)
- 1.54 Write to a TCP/IP socket from PL/SQL
- 1.55 Handling single quote marks inside SQL statements
- 1.56 Run a script on all databases in /etc/oratab
- 1.57 Schedule a cron job to run on the last day of each month
- 1.58 How to tell if a year is a leap year or not - using cal instead of reinventing the wheel
- 1.59 Reset an expired (and/or locked) user password to the same value
- 2 shell
- 2.1 Count the number of background processes for a particular SID
- 2.2 Find Oracle errors in the most recent DataPump log files
- 2.3 Find the 10 largest directories on a particular filesystem
- 2.4 Find the 10 largest files on a particular mount point
- 2.5 Split a file in pieces using sed
- 2.6 Split a file in pieces using Perl
- 2.7 Find and display RMAN errors in an RMAN log file
- 3 cmd
- 4 Perl
SQL
- Database Overview - from idevelopment online version
- Extract ddl with "dbms metadata.get ddl"
- Connecting as another user via proxy in SQL*Plus
- Password cracker and role and priv tools from Pete Finnigan
List invalid objects
set lines 200 set pages 200 col obj format a40 select owner||'.'||object_name obj , object_type from dba_objects where 1=1 and status = 'INVALID';
Recompile all invalid objects
@?/rdbms/admin/utlrp.sql
See how much space is left in the flash recovery area (FRA)
select name
, to_char (space_limit, '999,999,999,999') as space_limit
, to_char (space_limit - space_used + space_reclaimable
, '999,999,999,999')
as space_available
, round ( (space_used - space_reclaimable) / space_limit * 100, 1)
as pct_full
from v$recovery_file_dest;
DBA privs tables
DBA_AQ_AGENT_PRIVS DBA_COL_PRIVS DBA_PRIV_AUDIT_OPTS DBA_REPGROUP_PRIVILEGES DBA_ROLE_PRIVS DBA_RSRC_CONSUMER_GROUP_PRIVS DBA_RSRC_MANAGER_SYSTEM_PRIVS DBA_SYS_PRIVS DBA_TAB_PRIVS DBA_WM_SYS_PRIVS DBA_WORKSPACE_PRIVS
Schedule a shell job
begin
dbms_scheduler.create_program ( program_name => 'myshelljob'
, program_action => '/home/oracle/scripts/shell.sh'
, program_type => 'EXECUTABLE'
, comments => 'Run a shell'
, enabled => TRUE
);
end;
/
Start the job
begin
dbms_sheduler.create_job ( job_name => 'myshelljob'
, program_name => 'myshelljob'
, start_date =>
);
/
What statement is a user running?
select a.sid , a.serial# , b.sql_text from v$session a , v$sqlarea b where a.sql_address = b.address and a.username = '&username' /
See what statements all users are running
Useful to see what is happening when process limit is reached
set lines 300 pages 1000 col username for a10 col command for 999 head "CMD" col lockwait for a10 col status for a8 col schemaname for a12 col osuser for a10 col process for a10 col program for a20 col machine for a10 col action for a10 col module for a10 col identifier for a10 col event for a27 col state for a8 col service_name for a10 col serial# for 999999 select a.username , a.command , a.lockwait , a.status , a.schemaname , a.osuser , a.process , a.machine , a.program , a.type --, a.sql_exec_start --, a.plsql_object_id --, a.plsql_subprogram_id --, a.module --, a.action , a.logon_time , a.event , a.wait_time , a.seconds_in_wait , a.state --, a.service_name , a.serial# , b.sql_text from v$session a , v$sqlarea b where a.sql_address = b.address order by a.logon_time desc / set lines 80
Pretty longops listing
col opname for a34 head "Job" col target for a20 col message for a40 col perc_done for a10 head "Done" col started for a18 col killer for a15 col mins_busy head "Mins busy" col mins_left head "Mins left" set lines 2000 pages 1000 select sid||','||serial# killer , opname , target , message , round ((sofar/totalwork),4)*100||'%' perc_done , to_char(start_time,'DD-MON-YY HH24:MI') started , floor(elapsed_seconds/60) mins_busy , ceil(time_remaining/60) mins_left from v$session_longops where 1=1 and sofar != totalwork and totalwork != 0 and opname like 'RMAN%' /
select * from v$session where module='Data Pump Worker'
or attach to the impdp job and it shows percentage done.
Kill an Oracle session
disconnect attempts to kill the o/s processes too. kill doesn't.
col killer for a60 set lines 2000 select 'alter system disconnect session '''||s.sid||','||s.serial#||''' immediate;' killer , s.inst_id , s.sid , s.serial# , p.spid , s.username , s.program from gv$session s , gv$process p where 1=1 and p.addr = s.paddr and p.inst_id = s.inst_id and s.type != 'BACKGROUND' and s.sid like '&session_id%' / KILLER INST_ID SID SERIAL# SPID USERNAME PROGRAM ----------------------------------------------------- ------- ---------- ---------- ------------ --------------- ------------------------------------------------ alter system disconnect session '1015,271' immediate; 1 1015 271 64488062 DBSNMP emagent@solax023 (TNS V1-V3) alter system disconnect session '1010,4' immediate; 1 1010 4 21627762 WMCORE712 JDBC Connect Client alter system disconnect session '933,2432' immediate; 1 933 2432 45220466 WM712 imp@solax023 (TNS V1-V3) alter system disconnect session '1076,3204' immediate; 1 1076 3204 48562912 SYS rman@solax023 (TNS V1-V3) alter system disconnect session '1012,6' immediate; 1 1012 6 16646158 WMCORE712 JDBC Connect Client
Get statistics for LOB columns in a table
select max(dbms_lob.GETLENGTH(lob_content)) max_len , avg(dbms_lob.getlength(lob_content)) avg_len , min(dbms_lob.getlength(lob_content)) min_len , sum(CASE WHEN dbms_lob.GETLENGTH(lob_content) <= 4000 THEN 1 ELSE 0 END) "<4k" , sum(CASE WHEN dbms_lob.GETLENGTH(lob_content) > 4000 THEN 1 ELSE 0 END) ">4k" , sum(CASE WHEN dbms_lob.GETLENGTH(lob_content) IS NULL THEN 1 ELSE 0 END) "is null" from &TABLENAME /
badprivs.sql
select grantee, privilege, admin_option
from sys.dba_sys_privs
where (privilege like '% ANY %'
or privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')
or admin_option = 'YES')
and grantee not in ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
'TIMESERIES_DBA')
Generate a list of tablespaces ready for a migration
set echo off
set lines 300
set pages 0
Set headi off
set feedb off
set long 32000
spool migration_create_target_tablespaces.sql
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
select DBMS_METADATA.GET_DDL('TABLESPACE',tablespace_name) from dba_tablespaces
/
spool off
List the privileges assigned to users on directories
table_name is the name of the directory...
SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE table_name = 'DATA_PUMP_DIR';
List the privileges assigned to users on packages/procedures
table_name is the name of the package...
SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE upper(table_name) like upper('%UTL_FILE%');
List the system privileges assigned to a user
SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
SELECT NULL grantee, username granted_role
FROM dba_users
WHERE username LIKE UPPER('%&uname%')
UNION
SELECT grantee, granted_role
FROM dba_role_privs
UNION
SELECT grantee, privilege
FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;
or
SELECT path
FROM (
SELECT grantee,
sys_connect_by_path(privilege, ':')||':'||grantee path
FROM (
SELECT grantee, privilege, 0 role
FROM dba_sys_privs
UNION ALL
SELECT grantee, granted_role, 1 role
FROM dba_role_privs)
CONNECT BY privilege=prior grantee
START WITH role = 0)
WHERE grantee IN (
SELECT username
FROM dba_users
WHERE lock_date IS NULL
AND password != 'EXTERNAL'
AND username != 'SYS')
OR grantee='PUBLIC'
or
for a migration from 10g...
set headi off
set feedb off
set long 9999999
set pages 0
set lines 300
spool migration_create_target_users_grants.sql
select dbms_metadata.get_ddl('USER', username) || '/' ddl
from dba_users
where 1=1
and default_tablespace not in ('SYSTEM','SYSAUX')
/
select 'grant '||privilege|| ' to ' || grantee || ';'
from dba_sys_privs
where 1=1
and grantee not in (
select username
from dba_users
where 1=1
and default_tablespace in ('SYSTEM','SYSAUX')
)
union all
select 'grant '||granted_role|| ' to ' || grantee || ';'
from dba_role_privs
where 1=1
and grantee not in (
select username
from dba_users
where 1=1
and default_tablespace in ('SYSTEM','SYSAUX')
)
/
spool off
or for a migration on 11g...!
set head off
set pages 0
set long 9999999
col ddl for a1000
spool migration_create_users_grants.sql
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || ';' DDL
FROM DBA_USERS
where 1=1
and default_tablespace not in ('SYSTEM','SYSAUX')
and upper(username) like '%'||upper('&&username')||'%'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || ';' DDL
FROM DBA_USERS
where 1=1
and default_tablespace not in ('SYSTEM','SYSAUX')
and upper(username) like '%'||upper('&&username')||'%'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || ';' DDL
FROM DBA_USERS
where 1=1
and default_tablespace not in ('SYSTEM','SYSAUX')
and upper(username) like '%'||upper('&&username')||'%'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || ';' DDL
FROM DBA_USERS
where 1=1
and default_tablespace not in ('SYSTEM','SYSAUX')
and upper(username) like '%'||upper('&&username')||'%'
/
spool off
Move files between disks
set pages 1000
set lines 1000
select 'alter database rename file ''' ||
name || ''' to ''' ||
replace(name || ''';', '/u01', '/u02')
from v$datafile
/
select 'alter database rename file ''' ||
member || ''' to ''' ||
replace(member || ''';', '/u01', '/u02')
from v$logfile
/
Pass parameter into perl one-liner from shell
Trick? Just use ARGV to pass them in...
FREQ=$(perl -e 'my @CAL=split(";",$ARGV[0]);shift @CAL;print $CAL[$ARGV[1]];' -- "$LINE" $offset)
or
DUMP_DATE=`perl -MTime::Local -e 'print(timelocal(0,$ARGV[0],$ARGV[1],$ARGV[2],$ARGV[3],$ARGV[4]))' -- $MI $HH $DD $MM $YYYY`
Return epoch seconds in Perl
perl -e 'print time();'
or to convert a specific day of the year to epoch seconds
use POSIX; my $epochsecs = mktime(0,0,0, $jday, 0, $year-1900);
or for a specific date (careful though - month is 0-11 not 1-12!...
use Time::Local; # SS MM HH DD MM YYYY my $epochsecs = timelocal (30,10,19,03,04,2014);
or more long-winded but delimited by anything...
Supply the date/time in any delimited format. Eg. YYYY-MM-DD:HH:MI
#!/usr/bin/perl -w
use Time::Local;
sub date2epoch {
my($s) = @_;
my($year, $month, $day, $hour, $minute, $second);
if($s =~ m{^\s*(\d{1,4})\W*0*(\d{1,2})\W*0*(\d{1,2})\W*0*
(\d{0,2})\W*0*(\d{0,2})\W*0*(\d{0,2})}x) {
$year = $1; $month = $2; $day = $3;
$hour = $4; $minute = $5; $second = $6;
$hour |= 0; $minute |= 0; $second |= 0; # defaults.
$year = ($year<100 ? ($year<70 ? 2000+$year : 1900+$year) : $year);
return timelocal($second,$minute,$hour,$day,$month-1,$year);
}
return -1;
}
Return date from epoch seconds in Perl
perl -e 'print scalar (localtime (1243269270))'
Return epoch seconds from julian day number in Perl
use POSIX; my ($year, $jday) = (2012,60); my $epochsecs = mktime(0,0,0, $jday, 0, $year-1900);
Extract specific pieces of a date from an epoch timestamp in Perl
use POSIX; # return localtime into an array my @tm = localtime $epochsecs; my $yyyymmdd = strftime "%Y%m%d", @tm; print $yyyymmdd."\n";
Return epoch seconds in Shell (linux)
date -d "2014/04/03 18:34:30" "+%s"
Return date from epoch seconds in Shell (linux)
date -d @1396542870
Return epoch seconds in PL/SQL
You cannot just use sysdate as that is timezone sensitive... (epoch seconds are in UTC/GMT)
select round((CAST(sys_extract_utc(SYSTIMESTAMP) AS DATE) - to_date('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI:SS'))*24*60*60) from dual;
How long has this instance been up (in minutes)?
select round((CAST(sys_extract_utc(SYSTIMESTAMP) AS DATE) - startup_time)*24*60) up_mins from v$instance;
Return date from epoch seconds in SQL
select to_char(to_date('19700101','YYYYMMDD') + ((&epoch)/24/60/60),'DD-MON-YYYY HH24:MI:SS') utc_date from dual;
A database overview using the sys.plato package
exec plato.help;
or
exec plato.complete('HTM');
List datafiles for a tablespace
select * from dba_data_files where 1=1 and tablespace_name ='&ts_name';
Show graph of available and free space in tablespaces
-- =============================================================================
-- File Name : http://www.oracle-base.com/dba/monitoring/ts_free_space.sql
-- Author : Tim Hall
-- Description : Displays a list of tablespaces and their used/full status.
-- Requirements : Access to the DBA views.
-- Call Syntax : @ts_free_space.sql
-- Last Modified: 13-OCT-2012 - Created. Based on ts_full.sql
--
-- Modification History
-- ====================
-- When Who What
-- ========= ================= =================================================
-- 13-NOV-13 Stuart Barkley Added cols free_pct and used_pct
-- =============================================================================
SET PAGESIZE 140 lines 180
COLUMN used_pct FORMAT A11
COLUMN max_used_pct FORMAT A11
SELECT tablespace_name tablespace_name
, size_mb size_mb
, free_mb free_mb
, TRUNC((free_mb/size_mb) * 100) free_pct
, RPAD(' '|| RPAD('X',ROUND((size_mb-free_mb)/size_mb*10,0), 'X'),11,'-') used_pct
, max_size_mb max_size_mb
, max_free_mb max_free_mb
, TRUNC((max_free_mb/max_size_mb) * 100) max_free_pct
, RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') max_used_pct
from (
select a.tablespace_name tablespace_name
, b.size_mb size_mb
, a.free_mb free_mb
, b.max_size_mb max_size_mb
, a.free_mb + (b.max_size_mb - b.size_mb) max_free_mb
from (
select tablespace_name tablespace_name
, trunc(sum(bytes)/1024/1024) free_mb
from dba_free_space
group by tablespace_name
) a
, (
select tablespace_name tablespace_name
, trunc(sum(bytes)/1024/1024) size_mb
, trunc(sum(greatest(bytes,maxbytes))/1024/1024) max_size_mb
from dba_data_files
group by tablespace_name
) b
, v$instance vi
where a.tablespace_name = b.tablespace_name
)
order by tablespace_name;
set lines 80
Another method for calculating free space in tablespace using segments
select df.tablespace_name "Tablespace"
, totalusedspace "Used MB"
, (df.totalspace - tu.totalusedspace) "Free MB"
, df.totalspace "Total MB"
, round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
from (
select tablespace_name
, round(sum(bytes) / 1048576) totalspace
from dba_data_files
group by tablespace_name
) df
, (
select round(sum(bytes)/(1024*1024)) totalusedspace
, tablespace_name
from dba_segments
group by tablespace_name
) tu
where df.tablespace_name = tu.tablespace_name;
Work out maximum possible size of a datafile
select round( (4*1024*1024*value) / (1024*1024*1024) ,2) maxfilesizeingigabytes from v$parameter where name='db_block_size';
or, as tablespaces can have block sizes differing from that of the init.ora parameter...
select round( (4*1024*1024*block_size) / (1024*1024*1024) ,2) maxfilesizeingigabytes from dba_tablespaces where tablespace_name='&tablespace_name';
Show size and maxsize at datafile level
datafiles
set pages 100 lines 200 col file_name for a60 select sum(bytes)/1024/1024 mbytes_alloc , sum(maxbytes)/1024/1024 mbytes_max , file_name from sys.dba_data_files group by file_name /
tempfiles
set pages 100 lines 200 col file_name for a60 select sum(bytes)/1024/1024 mbytes_alloc , sum(maxbytes)/1024/1024 mbytes_max , file_name from sys.dba_temp_files group by file_name /
Increase the existing size of a datafile
alter database datafile '&full_path_of_datafile' resize &new_meg_size.M;
Increase the maximum size of a datafile
Although this can be set lower than existing size, try not to. It makes the reports look weird!
alter database
datafile '&full_path_of_datafile'
autoextend on
maxsize &new_meg_size.m;
Add a datafile to a tablespace
'unlimited' will default to the maximum datafile size for the current db_block_size
alter tablepace &tablespace_name
add datafile '&full_path_of_datafile'
size 100M
autoextend on
next 100M
maxsize unlimited
List all datafiles with their size
select filetype
, name
, gig
from (
select 'datafile' filetype
, name
, bytes/1024/1024 gig
from v$datafile
union all
select 'tempfile' filetype
, name
, bytes/1024/1024
from v$tempfile
union all
select 'logfile' filetype
, lf.member "name"
, l.bytes/1024/1024
from v$logfile lf
, v$log l
where 1=1
and lf.group# = l.group#
) used
, (
select sum(bytes)/1024/1024
from dba_free_space
) free
Drop datafile from temp tablespace
Database is completely stuck because the temporary tablespace has grown to fill all available room.
Look around for at least some log files to delete in order to get connected (audit files, sqlnet logs, listener logs)
Once connected, change the default temporary tablespace to a small one so we can remove the original.
create temporary tablespace temp2 tempfile '/oracle/RMANV12/oradata2/temp2.dbf' size 5M;
alter database default temporary tablespace temp2;
I found trying to drop the datafile like this didn't work, just hung there (probably active tranactions)...
drop tablespace temp including contents and datafiles;
So did it like this...
alter database tempfile '/oracle/RMANV12/oradata2/temp.dbf' drop including datafiles; Database altered. drop tablespace temp including contents and datafiles; Tablespace dropped.
Then to be neat and tidy, rename the temp2 tablespace back to temp.
create temporary tablespace temp tempfile '/oracle/RMANV12/oradata2/temp.dbf' size 256M reuse autoextend on next 128M maxsize 1G; alter database default temporary tablespace temp; drop tablespace temp2 including contents and datafiles;
Although the last statement will not work until any transactions using it have been rolled back or committed.
List files that are in hot-backup mode
set lines 100 set pages 100 col name format a60 select df.name , b.status , to_char(time, 'hh24:mi:ss dd/mm/yyyy') time from v$datafile df , v$backup b where 1=1 and df.file# = b.file# and b.status = 'ACTIVE' order by b.file#;
waiting for snapshot control file enqueue
select s.sid , username , program , module , action , logon_time , l.* from v$session s , v$enqueue_lock l where 1=1 and l.sid = s.sid and l.type = 'cf' and l.id1 = 0 and l.id2 = 2
Info on blocking processes
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
-- session doing the blocking select * from v$lock l1 where 1=1 and block = 1 ;
-- sessions being blocked select * from v$lock l1 where 1=1 and id2 = 85203
-- info on session doing the blocking select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid = 234
select do.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) row_id from v$session s, dba_objects do where sid=234 and s.ROW_WAIT_OBJ# = do.OBJECT_ID;
Show locked objects
set lines 100
set pages 999
col username format a20
col sess_id format a10
col object format a25
col mode_held format a10
select oracle_username||' ('||s.osuser||')' username
, s.sid||','||s.serial# sess_id
, owner||'.'||object_name object
, object_type
, decode ( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global'
) status
, decode ( v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)
) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where 1=1
and v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id;
Show which row is locked
select do.object_name , row_wait_obj# , row_wait_file# , row_wait_block# , row_wait_row# , dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) from v$session s , dba_objects do where 1=1 and s.ROW_WAIT_OBJ# = do.OBJECT_ID and sid = &sid;
Check what is audited on a database
set pagesize 100 set linesize 256 select audit_option,success,failure from dba_stmt_audit_opts;
How old is the oldest audit record?
select extract (day from (systimestamp - min(ntimestamp#))) days_old from aud$;
Procedure to delete old audit records
select text from all_source where lower(name)='purge_audit_trail';
TEXT
------------------------------------------------------------------------------------------------
PROCEDURE purge_audit_trail IS
BEGIN
-- set the last archive timestamp
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => systimestamp - interval '365' day,
rac_instance_number => null
);
-- purge audit trail till last archive timestamp
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
, use_last_arch_timestamp => TRUE);
END purge_audit_trail;
Job setup to run the audit purge
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."PURGE_AUDIT_TRAIL_JOB"',
job_type => 'STORED_PROCEDURE',
job_action => '"SYS"."PURGE_AUDIT_TRAIL"',
repeat_interval => 'FREQ=DAILY;BYHOUR=20;BYMINUTE=0;BYSECOND=0',
start_date => to_timestamp_tz('2012-02-22 Europe/Paris', 'YYYY-MM-DD TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'purge audit trails until last archive timestamp',
auto_drop => FALSE,
enabled => FALSE
);
sys.dbms_scheduler.set_attribute( name => '"SYS"."PURGE_AUDIT_TRAIL_JOB"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
sys.dbms_scheduler.set_attribute( name => '"SYS"."PURGE_AUDIT_TRAIL_JOB"', attribute => 'job_weight', value => 1);
sys.dbms_scheduler.enable( '"SYS"."PURGE_AUDIT_TRAIL_JOB"' );
END;
/
A list of all SIDs in oratab that should restart after a server boot
Using awk
awk -F: '$NF=="Y" && $1 !~/[#]/ {print $1}' /etc/oratab
Using perl
perl -F: -ne 'print "$1\n" if /(\w+):(.+):Y$/' /etc/oratab
Move the admin directories to a new location
./all_db_do "alter system set audit_file_dest='/oracle/&SID/admin/adump' scope=spfile;"
./all_db_do "shutdown immediate"
for db in `awk -F: 'NF && $1 !~/[#*]/ {print $1}' /etc/oratab`
do
echo $db
mkdir -p /oracle/${db}/admin
echo "/oracle/${db}/admin created"
mv /oracle/admin/${db}/adump /oracle/${db}/admin/
mv /oracle/admin/${db}/dpdump /oracle/${db}/admin/
echo "Moved /oracle/admin/${db}/adump to /oracle/${db}/admin/"
done
./all_db_do startup
./all_db_do "create pfile from spfile;"
Check if the Standby database is up-to-date with the primary database
select arch.thread# "Thread"
, arch.sequence# "Last Sequence Received"
, appl.sequence# "Last Sequence Applied"
, (arch.sequence# - appl.sequence#) "Difference"
from (
select thread#
, sequence#
from v$archived_log
where 1=1
and ( thread#,first_time ) in (
select thread#, max(first_time)
from v$archived_log
group by thread#
)
) arch
,
(
select thread#
, sequence#
from v$log_history
where 1=1
and ( thread#,first_time ) in ( select thread# ,max(first_time)
from v$log_history
group by thread#
)
) appl
where 1=1
and arch.thread# = appl.thread#
order by 1;
Check for gaps on the Standby database
select thread#, low_sequence#, high_sequence# from v$archive_gap;
Ignore case-sensitive passwords in 11g
alter system set SEC_CASE_SENSITIVE_LOGON = false scope=both
External table for the Alert log (pre 11g)
CREATE OR REPLACE DIRECTORY bdump AS 'c:\oracle\product\diag dbms\orabase\orabase race\';
CREATE TABLE system.log_table (TEXT VARCHAR2(400))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY bdump
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOBADFILE NODISCARDFILE NOLOGFILE
FIELDS TERMINATED BY '0x0A'
MISSING FIELD VALUES ARE NULL)
LOCATION ('alert_orabase.log'))
REJECT LIMIT unlimited;
SELECT * FROM system.log_table;
CREATE OR REPLACE VIEW last_200_alerts AS
WITH alert_log AS (
SELECT rownum as lineno, text FROM system.log_table)
SELECT text
FROM alert_log
WHERE lineno > (SELECT count(*)-200 FROM alert_log)
ORDER BY lineno;
SELECT * FROM last_200_alerts;
Alert log (11g+)
This reads the log.xml file (which contains the same information as alert.log)
Show the messages put in the alert log in the last 24 hours
select substr(MESSAGE_TEXT, 1, 300) message_text , count(*) cnt from X$DBGALERTEXT where 1=1 and (MESSAGE_TEXT like '%ORA-%' or upper(MESSAGE_TEXT) like '%ERROR%') and cast(ORIGINATING_TIMESTAMP as DATE) > sysdate - &days_ago/1440 group by substr(MESSAGE_TEXT, 1, 300)
There is also a fixed table X$DBGDIREXT which returns all file and directory names under [diagnostic_dest]/diag directory:
select lpad(' ',lvl,' ')||logical_file file_name
from x$dbgdirext
where rownum <=20;
Write to a TCP/IP socket from PL/SQL
declare
bt_conn utl_tcp.connection;
retval binary_integer;
l_sequence varchar2(30) := 'Sequence Number 101021';
begin
bt_conn := utl_tcp.open_connection(remote_host => '10.10.1.1'
,remote_port => 5000
,tx_timeout => 1
)
;
retval := utl_tcp.write_line(bt_conn,l_sequence)
;
utl_tcp.flush(bt_conn)
;
utl_tcp.close_connection(bt_conn)
;
exception
when others then
raise_application_error(-20101, sqlerrm)
;
utl_tcp.close_connection(bt_conn)
;
end
;
/
and read from it using netcat in another session
nc -lk 5000
produces
Sequence Number 101021
Handling single quote marks inside SQL statements
SQL> SELECT 'D''COSTA' name FROM DUAL; NAME ------- D'COSTA Alternatively, use the 10g+ quoting method: SQL> SELECT q'$D'COSTA$' NAME FROM DUAL; NAME ------- D'COSTA
Run a script on all databases in /etc/oratab
export ORAENV_ASK=NO
for db in `awk -F: 'NF && $1 !~/[#*]/ {print $1}' /etc/oratab`
do
echo $db
ORACLE_SID=$db
. oraenv
sqlplus -V
done
Schedule a cron job to run on the last day of each month
0 23 28-31 * * [ $(date -d +1day +%d) -eq 1 ] && su - oracle -c "dsmc arch -server=SO_U_`hostname`_ORX_M_SOL '/oracle/export/PV6/expdp_PV6_D_FULL_`date +\%Y\%m\%d`*'"
How to tell if a year is a leap year or not - using cal instead of reinventing the wheel
function leap {
# This function tells you if the year passed in as a parameter is a leap year or not...
if [[ $(cal 2 ${1:-$(date '+%Y')} | egrep '\<[[:digit:]]{1,2}\>' | wc -w) -eq 29 ]]; then
leap='Y'
else
leap='N'
fi
echo ${leap}
}
Reset an expired (and/or locked) user password to the same value
Simple, quick and easy version. Run this then pick and choose the statements you want to run.
set lines 200 pages 200 col user_list for a200 head "Expired User List" select 'alter user ' || su.name || ' identified by values' || ' ''' || spare4 || ';' || su.password || ''' account unlock;' user_list from sys.user$ su , dba_users du where 1=1 and su.name = du.username and (account_status like 'EXPIRED%' or account_status like '%LOCKED') and du.username not in ( 'ORACLE_OCM' , 'SCOTT' , 'DIP' , 'FLOWS_FILES' , 'TSMSYS' , 'MDDATA' , 'ANONYMOUS' , 'CTXSYS' , 'MDSYS' , 'ORDDATA' , 'ORDPLUGINS' , 'SI_INFORMTN_SCHEMA' , 'OLAPSYS' , 'XDB' , 'ORDSYS' , 'WMSYS' , 'DMSYS' , 'DSSYS' , 'EXFSYS' , 'SYS' , 'SYSTEM' , 'MGMT_VIEW' , 'PERFSTAT' , 'TRACESVR' , 'LBACSYS' , 'OWBSYS' , 'OWBSYS_AUDIT' , 'APEX_PUBLIC_USER' , 'XS$NULL' , 'APEX_030200' , 'SPATIAL_CSW_ADMIN_USR' , 'SPATIAL_WFS_ADMIN_USR' , 'APPQOSSYS' , 'OUTLN' );
More industrialised version. Create a package.
create or replace package dba_utils
as
procedure reset_password ( p_userlist in varchar2 );
end dba_utils;
/
create or replace package body dba_utils
as
procedure reset_password ( p_userlist in varchar2 )
is
cursor c1 ( p_userlist in varchar2 ) is
select username
, regexp_substr ( dbms_metadata.get_ddl('USER', username), '''[^'']+''' ) pwd
from dba_users
where 1=1
and username in upper( p_userlist )
;
begin
dbms_output.enable(null);
for r1 in c1 ( p_userlist )
loop
begin
execute immediate 'alter user ' || r1.username || ' account unlock';
execute immediate 'alter user ' || r1.username || ' identified by values ' || r1.pwd;
exception
when others then
dbms_output.put_line('Unable to modify user ' || r1.username);
end;
end loop;
end reset_password;
end dba_utils;
/
Call it
set serveroutput on
exec dba_utils.reset_password ('STUART');
shell
Count the number of background processes for a particular SID
SID=RAVJDE1
ps -ef|cut -c54-100|awk -v SID=$SID '$0 ~ /'$SID'$/ {print}'|wc -l
or ...this will work with any OS (so long as the process is at the end of the ps -ef or ps -aux listing
SID=RAVJDE1
ps -ef|awk -v SID=$SID -F_ '{ $NF ~ /'$SID'$/ {print} }'|wc -l
Pick the columns you want in the ps listing
ps -efo user,pid,ppid=MOM -o args
Find Oracle errors in the most recent DataPump log files
00 09 * * * /home/tools/scripts/oracle/dosh 'find /oracle/export -name "expdp*log" -mtime -1 -exec grep ORA- {} \; -ls' | mailx -s 'Datapump errors for <customer>last night' [email protected]
Find the 10 largest directories on a particular filesystem
du -ag /oracle | sort -nr | head -n 10
Find the 10 largest files on a particular mount point
find /home -size +1G -ls | sort -nr +6 | head -10
or
find /home -xdev -ls | sort -nr +6 | head -10
Split a file in pieces using sed
Using fixed line numbers
sed -ne '1,12p' file > text.part1 sed -ne '13,$p' file > text.part2
Using line with a /pattern/ on it
sed -e '/hello/q' tt.dat | grep -v hello > text.part1 sed -n `sed -n '/hello/=' tt.dat`',$p' tt.dat | grep -v hello > text.part2
Split a file in pieces using Perl
perl -ne 'print if 1 .. /marker line/' text.dat >text.part1 perl -ne 'print if /marker line/ .. eof()' text.dat >text.part2
Find and display RMAN errors in an RMAN log file
sed -ne '/^RMAN-/,/^$/p' RMAN_<SID>_<DATE>.log
cmd
Perl
For all files in a directory, replace a keyword (SSIIDD) in a file with another (a parameter)
for i in `ls`
do
perl -pi -e 's/SSIIDD/'$SID'/g' $i
done
Parse tnsnames.ora to get just the service names
perl -ne 'print if m/^([^#()\W ][a-zA-Z.]*(?:[.][a-zA-Z]*\s?=)?)/' $TNS_ADMIN/tnsnames.ora
and use that to check the listener status
for svc in `perl -ne 'print if m/^([^#()\W ][a-zA-Z.]*(?:[.][a-zA-Z]*\s?=)?)/' $TNS_ADMIN/tnsnames.ora | sed -e 's/=//'` ;do
tnsping $svc
done
