Difference between revisions of "Snippets"

From dbawiki
Jump to: navigation, search
(→‎Write to the alert log from PL/SQL)
(→‎How to find ORACLE_HOME from pmon process for a SID if there is no entry in oratab)
Line 627: Line 627:
 
</pre>
 
</pre>
  
 +
===How to get value of ORACLE_HOME from shell environment into SQL*Plus or PL/SQL===
 +
<pre>
 +
var orahome varchar2(2000);
 +
exec dbms_system.get_env('ORACLE_HOME', :orahome) ;
 +
print orahome
 +
</pre>
 
===How to find ORACLE_HOME from pmon process for a SID if there is no entry in oratab===
 
===How to find ORACLE_HOME from pmon process for a SID if there is no entry in oratab===
 
If database is created without dbca (datafile and controlfile copy), no entry will be automatically made in /etc/oratab.<br />
 
If database is created without dbca (datafile and controlfile copy), no entry will be automatically made in /etc/oratab.<br />

Revision as of 12:53, 5 December 2018

Contents

Protecting an Apache Web Server directory with htaccess

Two files are needed.
The .htaccess Code 

AuthType Basic
AuthName "restricted area"
AuthUserFile /home/davidwalsh/html/protect-me-dir/.htpasswd
require valid-user

The .htpasswd Code 

davidwalsh:daWHfZrDLB88.
rodstewart:roFulYxC2.8ws
cssexpert:csmnmq.M8T5ho

Protecting a lighttpd directory with htaccess

Generate an MD5 hash with the tools above and paste into this file 

vi /etc/lighttpd/.htpasswd
stuart:3#$567890#$56789056789

Check modules.conf file to ensure mod_auth and mod_rewrite are enabled 

vi /etc/lighttpd/modules.conf

Alter the lighttpd.conf file to allow the authentication 

#auth directives
auth.backend = "htpasswd"
auth.backend.htpasswd.userfile = "/etc/lighttpd/.htpasswd"
auth.debug = 1
$HTTP["url"] =~ "^/cgi-bin" {
        auth.require = ( "" =>
        (
        "method" => "basic",
        "realm" => "DbaHawk access",
        "require" => "valid-user"
        ) )
}

Restart the lighttpd server 

systemctl stop lighttpd
systemctl start lighttpd

I tried it with htdigest but could not get it to protest the directory! 

#auth.backend = "htdigest"
#auth.backend.htdigest.userfile = "/etc/lighttpd/lighttpd.user.htdigest"
#auth.require = (
#    "/var/www/cgi-bin/" => (
#    "method"  => "basic",
#    "realm"   => "DbaHawk access",
#    "require" => "valid-user"
#    ),
#)

Rename a datafile if the filename contains junk / unprintable characters

alter tablespace ts_thaler_data offline;
  • Oracle have a document that describes a few ways of doing it in Note 824473.1

If the filename contains control characters and cannot be selected in the normal way, use ls -ali to find the node number and then use find with -inum to rename (or move) the file 

ls -baltri
total 47474984
12409 -rw-r-----    1 oracle   oinstall 1073750016 Sep 26 13:56 ts_thaler_data_02\1776.dbf
12350 -rw-r-----    1 oracle   oinstall 5242888192 Sep 26 13:56 ts_thaler_data_01.dbf

find . -inum 12409 -exec mv {} ts_thaler_data_06.dbf \;

Work out what the database thinks the name is and rename it there too! AskTom has a discussion on it here 

select substr(name,i,1),ascii(substr(name,i,1)) from ( select name from v$datafile where file# = 9), ( select rownum i from dual connect by level <= 50);
SUBS ASCII(SUBSTR(NAME,I,1))
---- -----------------------
t                        116
s                        115
_                         95
t                        116
h                        104
a                         97
l                        108
e                        101
r                        114
_                         95
d                        100
a                         97
t                        116
a                         97
_                         95
0                         48
2                         50
                         127
2                         50
.                         46
d                        100
b                         98
f                        102

You can see there's a 127 just before the .dbf... this crept in a the backspace key was not working correctly. 

set serveroutput on
declare
   fname1 varchar2(100);
   fname2 varchar2(100);
begin
  select name into fname1 from v$datafile where file# = 9;
  fname2 := replace(fname1,chr(127));
  
  dbms_output.put_line('alter database rename file '''||fname1||''' to '''||fname2||'''');
  execute immediate 'alter database rename file '''||fname1||''' to '''||fname2||'''';
end;
/

alter tablespace ts_thaler_data_online;

Generate creation of directory names from dba_directories

Before deleting or dropping directories in the database, use this script to generate the create statements 

select 'create or replace directory '||directory_name||' as '''||directory_path||''';' from dba_directories;

Check if current (dot) directory is in PATH variable

Using bareword comparison to check PATH variable
if [[ :$PATH: == *:".":* ]] ; then
    echo "in path"
else
    echo "not in path"
fi

tail a logfile from within the script you are writing it to

#
# display logfile in real time
#
( tail -0f ${LOGFILE} ) &
tailPID=$!

trap "kill $tailPID" 0 1 2 3 5 9 15

Dump package, procedure etc. from dba_source in a way that it can be used to recreate it

From Ask Tom 

set lines 1000 pages 1000 feedb off verif off
select decode( type||'-'||to_char(line,'fm99999'), 'PACKAGE BODY-1', '/'||chr(10), null) ||
       decode(line,1,'create or replace ', '' ) ||
       text text
from   dba_source
where  owner = upper('&owner')
and    name  = upper('&object_to_show')
order  by type
,      line
/

A trick to exit out of SQL*Plus (using divide by zero) depending on answer to a question

This example is taken from ReviewLite. 

-- Settings for customized functionality
define SCRIPT_OO=_OPTIONS_ONLY -- collect only options information
define SCRIPT_OO=''            -- collect all information [default behavior]

define SCRIPT_TS=_TIME_STAMP   -- include timestamp in names of the output directory and output files: YYYY.MM.DD.HH24.MI.SS; script does not prompt for license agreement
define SCRIPT_TS='\home\oracle\options'            -- standard names for output directory and output files; script prompts for license agreement [default behavior]

-- PROMT FOR LICENSE AGREEMENT ACCEPTANCE
DEFINE LANSWER=N
SET TERMOUT ON
ACCEPT &SCRIPT_TS LANSWER FORMAT A1 PROMPT 'Accept License Agreement? (y\n): '

-- FORCE "divisor is equal to zero" AND SQLERROR EXIT IF NOT ACCEPTED
-- WILL ALSO CONTINUE IF SCRIPT_TS SUBSTITUTION VARIABLE IS NOT NULL
SET TERMOUT OFF
WHENEVER SQLERROR EXIT
select 1/decode(' &LANSWER', 'Y', null, 'y', null, decode(' &SCRIPT_TS', null, 0, null)) as " " from dual;
WHENEVER SQLERROR CONTINUE
SET TERMOUT ON

How to uninstall optional components such as OWB, APEX, EM, OLAP, OWM from an Oracle database

Find unique indexes / primary index / key columns in a table

col owner       for a12
col table_name  for a32
col column_name for a32
col position    for 99
col status      for a10
set lines 1000 pages 100

SELECT cons.owner
,      cols.table_name
,      cols.column_name
,      cols.position
,      cons.status
FROM   all_constraints  cons
,      all_cons_columns cols
WHERE  1=1
and    cols.owner           = '&TABLE_OWNER'
and    cols.table_name      = '&TABLE_NAME'
AND    cons.constraint_type = 'P'
AND    cons.constraint_name = cols.constraint_name
AND    cons.owner           = cols.owner
ORDER  BY cols.table_name
,      cols.position
/

Display the oracle instances running on the local server

sed could be shorter but this one works cross-platform 

alias oenv='ps -ef|grep pmon|grep -v grep|awk -F_ "{print \$NF}"|sort|uniq|sed -e ":a" -e "N" -e "\$!ba" -e "s/\n/ /g"'

Display which databases are scheduled for backup in cron

for i in `ps -ef | grep [o]ra_pmon | awk -F_ '{print $NF}'`; do
    crontab -l | grep $i | grep backup_export | awk '{print $2":"$1" - "$17}'
done

Trace SQL statements using autotrace and explain plan

If the plustrace (plustrc.sql) role has been granted, explaining sql statements (without running the statement) is as easy as 

alter session set sql_trace=true;
set autotrace traceonly explain

Write to a trace log from PL/SQL

dbms_system.ksdwrt(1,'message sent to a trace file')

Invisible / hidden / virtual columns

Article showing how to dump blocks also.
Tips and Tricks: Invisible Columns in Oracle Database 12c by Alex Zaballa, Oracle Ace and Daniel Da Meda (OCM)

Write to the alert log from PL/SQL

Writes to trace file 

dbms_system.ksdwrt(1,'message sent to trace log')

Writes to the alertlog 

dbms_system.ksdwrt(2,'message sent to the alertlog')

Writes to the alertlog and trace file 

dbms_system.ksdwrt(3,'message sent to trace file and the alert log')

Also available, KSDIND, KSDDDT and KSDFLS

Do distributed SQL transactions exist?

select * from dba_2pc_pending;

if yes, and to commit them... 

select local_tran_id from dba_2pc_pending;
exec dbms_transaction.purge_lost_db_entry('');
commit;

Setup optional parameters and default values in an SQL*Plus script

Some forgotten SQL*Plus tricks from the good old days and some new ones... 

set termout off
col p1 new_value 1
col p2 new_value 2
col p3 new_value 3
select null p1, null p2, null p3 from dual where  1=2;
select nvl('&1','def1') p1, nvl('&2','def2') p2, nvl('&3','def3') p3 from dual;
set termout on
prompt 1="&1"
prompt 2="&2"
prompt 3="&3"
undef 1
undef 2
undef 3

Display / show users with sysdba & sysoper privileges

select * from v$pwfile_users;

Show path names of data files using instr

Use to find the pathnames of all the filesystems/directories in which a database is housed. 

select distinct substr(name,1,(instr(name,'/',-1,1)-1)) path_name from v$datafile
union
select distinct substr(name,1,(instr(name,'/',-1,1)-1)) path_name from v$tempfile
/

Does a dataguard standby exist for this database?

SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM   v$parameter
WHERE  name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

gives something like this if found... 

SUBSTR(VALUE,INSTR(VALUE,'=',INSTR(UPPER(VALUE),'SERVICE'))+1)
--------------------------------------------------------------------------------
"jdbeop1_standby", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable m
ax_failure=0 max_connections=1 reopen=300 db_unique_name="jdbeop1_standby" ne
t_timeout=60, valid_for=(all_logfiles,primary_role)

List values for all init parameters in v$parameter (including default values for undocumented (hidden) parameters

select i.ksppinm||';'||sv.ksppstvl
from   x$ksppi  i
,      x$ksppsv sv
where  i.indx = sv.indx
order  by i.ksppinm;

Log file reports pipe errors

Found these in the log file 

solax005:root[/opt/IBM/ITM/logs]# tail solax005_or_RMANV11_col.out
CIR1880E (024955) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30024832_5346_pipe
CIR1880E (030938) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30030830_5406_pipe
CIR1880E (031923) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30031832_5434_pipe
CIR1880E (033934) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30033831_5495_pipe
CIR1880E (034918) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30034833_5521_pipe
CIR1880E (041927) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30041835_5610_pipe
CIR1880E (042931) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30042832_5642_pipe
CIR1880E (045928) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30045835_5730_pipe
CIR1880E (052927) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30052838_5819_pipe
CIR1880E (090929) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30090833_6487_pipe

This could be due to a timeout on the pipe.
Increase parameters and restart agent. Add following lines to or.config: 

export COLL_WAIT_TIMEOUT='200'
export WAIT_TIMEOUT='200'

Find the most recent archived SCN number

col next_change# for 999999999999999
set numwidth 15

select max(next_change#)
from   v$archived_log
where  (thread#, next_change#) in ( select thread#, max(next_change#)
                                    from   v$archived_log
                                    where  archived     = 'YES'
                                    and    status       = 'A'
                                    and    resetlogs_id = ( select resetlogs_id
                                                            from   v$database_incarnation
                                                            where  status = 'CURRENT'
                                                          )
                                    group by thread#
                                  )
and    status = 'A'
and    resetlogs_id = ( select resetlogs_id
                        from   v$database_incarnation
                        where  status = 'CURRENT'
                      )
order  by next_change# asc;

Monitoring says database inactive

  • Check the candle user password has not expired in the database.
  • There may be a clue in the log file. Check /opt/IBM/ITM/logs/`hostname`_or_${SID}_col.out
  • Check limits for itmora account in /etc/security/limits. Should be same as oracle user.
  • Check the config file. We had this issue when a new ORACLE_HOME was installed but the cfg file still had the old one in it.
export ITM_HOME=/opt/IBM/ITM
cd $ITM_HOME/config
vi solax005_or_SRV2R.cfg

#  IBM Tivoli Monitoring for Databases: Oracle Agent
#  Configuration file: solax005_or_SRV2R.cfg
#  Written by CandleDBconfig version: 1.4 on 31Jul13 13:39:41

#  Note: this is a Korn-shell script that is "sourced" to create the
#  environment for a Monitoring Agent for Oracle.  It can be used to create
#  the environment to run the IBM-supplied database grant scripts.

#  IBM does not recommend that you modify this file, but you can change the
#  data values or add new exported variables as long as the file is a valid ksh
#  script that executes with zero return code, and values remain quoted with "".

#  Following environment variables are set for convenience in running grants.
   export ORACLE_SID="SRV2R"
   export ORACLE_HOME="/oracle/product/11.2.0.1"
   # IY92195  COLL_DISABLE_CURSORS setting
   export TNS_ADMIN="/oracle/product/11.2.0.1/network/admin"

#  Following variables are set for the setup scripts
   db_sid="SRV2R"
   db_home="/oracle/product/11.2.0.1"
   db_initfilename=""
   db_login="tivoli"
   db_password="795D8822BC49477323815CD21C1E66E966E48EEE19C6A98056224D649B0FE316E6A11DC3F4E9BB5E226B65A8"
   db_ver="11.x"
   db_type="kor"
   db_tns="/oracle/product/11.2.0.1/network/admin"
   db_extparms=""
   db_hostname="solax005"
   db_reason="AIX_ps_ceww"
   db_pipedir=""
   db_installstatus="CONFIGSUCCESS"

#  User-defined environment variables

Solution:

  • Change all occurrences of 11.2.0.1 to 11.2.0.3
  • Restart agent

Alerts due to password expiring (or being changed in the database but not in ITM!)

cd $ITM_HOME/logs
ls -altr | grep WM820T
tail -20 solax005_or_WM820T_col.out

CGN1521E (161614) Interval collection failed for cursor KORHART2
RCD0110S (161614) Invalid Oracle logon id (tivoli) or password given
 Please contact DBA to correct userid or password
CGN1521E (161614) Interval collection failed for cursor KORHART4
CGN1525E (161614) One or more interval cursors failed
RCD0110S (161714) Invalid Oracle logon id (tivoli) or password given
 Please contact DBA to correct userid or password
CGN1521E (161714) Interval collection failed for cursor KORHART2
RCD0110S (161714) Invalid Oracle logon id (tivoli) or password given
 Please contact DBA to correct userid or password
CGN1521E (161714) Interval collection failed for cursor KORHART4
CGN1525E (161714) One or more interval cursors failed

Solution:

  • Run agent reconfigure to setup the new password for the candle user (tivoli)
export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/bin/itmcmd config -A or
  • Restart agent
export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/smitools/scripts/StartAgent.sh restart or -o WM820T

Many agents started with root user instead of itmora

This can happen if the server is rebooted before the agents have been added to the itmora list using kdyedit / kciedit 

export CANDLEHOME=/opt/IBM/ITM
export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/smitools/scripts/kdyedit list
Type Runas      Inst                
or   root       AGPDEV              
or   root       BO1R                
or   root       DEV3                
or   root       EMREP               
or   root       ICR                 
or   itmora     MAPDEV              
or   itmora     MAPER               
or   itmora     PED                 
or   itmora     PER                 
or   itmora     RMANV11             
or   itmora     SRV2R               
or   itmora     WM820Q              
or   itmora     WM820T              

$ITM_HOME/smitools/scripts/kciedit list
Type Runas      Inst                
or   root       AGPDEV              
or   root       BO1R                
or   root       DEV3                
or   root       EMREP               
or   root       ICR                 
or   itmora     MAPDEV              
or   itmora     MAPER               
or   itmora     PED                 
or   itmora     PER                 
or   itmora     RMANV11             
or   itmora     SRV2R               
or   itmora     WM820Q              
or   itmora     WM820T

Solution: Add these instances to the itmora list 

./kdyedit -t or -i AGPDEV -r itmora add
./kciedit -t or -i AGPDEV -r itmora add
...
./kdyedit -t or -i ICR -r itmora add
./kciedit -t or -i ICR -r itmora add

$ITM_HOME/smitools/scripts/kciedit list
Type Runas      Inst                
or   itmora     AGPDEV              
or   itmora     BO1R                
or   itmora     DEV3                
or   itmora     EMREP               
or   itmora     ICR                 
or   itmora     MAPDEV              
or   itmora     MAPER               
or   itmora     PED                 
or   itmora     PER                 
or   itmora     RMANV11             
or   itmora     SRV2R               
or   itmora     WM820Q              
or   itmora     WM820T

Kill any agents still running as root 

ps -ef |grep kor | grep root
kill -9 <process id>

Because these agents were owned by root, there will be permissions issues when trying to restart the agents with itmora so these need to be fixed before restarting the agents.
The simplest way of doing this is to run lockdown. 

export CANDLEHOME=/opt/IBM/ITM
$CANDLEHOME/smitools/scripts/lockdown.sh

See which processes are causing paging

svmon -Pt20 | perl -e 'while(<>){print if($.==2||$&&&!$s++);$.=0 if(/^-+$/)}'

How to return several variables from SQL*Plus back to Korn shell script

Return multiple columns/elements from Oracle to shell script at the same time 

sqlplus -s sys/${SYS_PASSWORD}@${SID} as sysdba<<EOSQL | read SPACE_AFTER_INSTALL TOTAL_SPACE_AVAILABLE
set numwid 15 headi off newpa none feedb off
select sign(${SPACE_USED} + ${SPACE_AVAILABLE} - ${DATABASE_SIZE}), trim(${SPACE_USED} + ${SPACE_AVAILABLE})
from dual
/
EOSQL

How to get a shell environment variable from the operating system into the SQL or PL/SQL environment

This has been bugging me for years. Something you would think simple... what is ORACLE_HOME? I know it's a question mark at the SQL prompt but how to get it's O/S value? 

declare
    var1 varchar2(200);
begin
    dbms_system.get_env ('ORACLE_HOME', var1);
    dbms_output.put_line ('ORACLE_HOME: '||var1);
end;
/

or in SQL*Plus 

variable var1 varchar2(200);
exec dbms_system.get_env ('ORACLE_HOME', :var1);
select :var1 from dual;

That got me thinking... if this just gets stuff from the environment, it can fetch any exported variable?!?!
From the shell: 

export running_sids=`ps -ef | grep [p]mon | awk -F_ '{print $NF}'`

and now in SQL*Plus: 

variable sids varchar2(240);
exec dbms_system.get_env ('running_sids', :sids);
select :sids from dual;

Wow!

How long/wide can a database name/sid be?

Depends on where you look but taking the minimum here as "safe", it should still be limited to 8 characters... 

col table_name for a30
col data_type  for a12
select table_name, data_type, data_length from dba_tab_columns where column_name = 'DB_NAME';

This is 11gR2 

TABLE_NAME                     DATA_TYPE    DATA_LENGTH
------------------------------ ------------ -----------
LOGMNRG_DICTIONARY$            VARCHAR2               9
SYS_FBA_CONTEXT_AUD            VARCHAR2             256
V_$LOGMNR_DICTIONARY           VARCHAR2               9
V_$LOGMNR_LOGS                 VARCHAR2               8
GV_$LOGMNR_DICTIONARY          VARCHAR2               9
GV_$LOGMNR_LOGS                VARCHAR2               8
V_$LOGMNR_LOGFILE              VARCHAR2               8
V_$LOGMNR_SESSION              VARCHAR2             128
GV_$LOGMNR_LOGFILE             VARCHAR2               8
GV_$LOGMNR_SESSION             VARCHAR2             128
GV_$ASM_CLIENT                 VARCHAR2               8
V_$ASM_CLIENT                  VARCHAR2               8
GV_$IOS_CLIENT                 VARCHAR2              64
V_$IOS_CLIENT                  VARCHAR2              64
DBA_PDB_HISTORY                VARCHAR2             128
CDB_PDB_HISTORY                VARCHAR2             128
SSCR_CAP$                      VARCHAR2            4000
SSCR_RES$                      VARCHAR2            4000
DBA_SSCR_CAPTURE               VARCHAR2            4000
CDB_SSCR_CAPTURE               VARCHAR2            4000
DBA_SSCR_RESTORE               VARCHAR2            4000
CDB_SSCR_RESTORE               VARCHAR2            4000
WRM$_DATABASE_INSTANCE         VARCHAR2               9
INT$DBA_HIST_DATABASE_INSTANCE VARCHAR2               9
DBA_HIST_DATABASE_INSTANCE     VARCHAR2               9
CDB_HIST_DATABASE_INSTANCE     VARCHAR2               9
LOGMNR_DICTIONARY$             VARCHAR2               9

27 rows selected.

What character set (characterset) was the database built with?

col name  for a40
col value for a60
select * from nls_database_parameters;

or 

col name   for a40
col value$ for a60
select name,value$ from props$ where name = 'NLS_CHARACTERSET';

How to get value of ORACLE_HOME from shell environment into SQL*Plus or PL/SQL

var orahome varchar2(2000);
exec dbms_system.get_env('ORACLE_HOME', :orahome) ;
print orahome

How to find ORACLE_HOME from pmon process for a SID if there is no entry in oratab

If database is created without dbca (datafile and controlfile copy), no entry will be automatically made in /etc/oratab.
So the problem is that you need to find out which of the ORACLE_HOMEs is being used by the running instances.

  • Solaris, Linux
$ ps -ef | grep [p]mon
  oracle 10848     1   0 00:21:20 ?           2:11 ora_pmon_JDBEOP1
$ pwdx 10848
10848:  /oracle/JDBEOP1/product/11204/dbs
  • AIX
$ ps -ef | grep [p]mon
  oracle 13893878        1   0   Aug 12      -  7:53 ora_pmon_GOAQ1
$ ls -al /proc/13893878/cwd
lr-x------    2 oracle   dba               0 Aug 12 13:37  cwd -> /data/opt/app/product/11.2.0.4/db_1/dbs/

or
This command (ps eww) gives lots of information about the given process (pmon in this case). Tells you where the instance was started, whether it was started from SQL*Plus or RMAN, the http_proxy and loads more useful bits! 

ps -ef | grep [p]mon | awk '{print $2}' | xargs -I {} ps eww {} | awk '{print $1 " " $5 " " $6 " " $0}' | sed 's/\(S*\) \(S*\) .*ORACLE_HOME/\1 \2/g' | cut -f1,2,3 -d" "

Escape the underscore(_) or percent(%) character in an SQL statement

The underscore matches any single character so to return data containing an underscore means having to use an escape character.
Here I use the backslash(\) so it is easy for Unix people to understand the mechanism. 

select username from dba_users where username like 'ENDUR\_DEV%' escape '\';

Validate, analyse and rebuild indexes

set feedback off
set linesize 132
set serveroutput on
set trimspool on

declare
    LOCKED exception;
    pragma exception_init (LOCKED, -54);

    dbase         char(8);
    dbuser        varchar2(30);
    index_name    varchar(30);
    free_text     varchar2(50);
    pctused       number;
    height    number;
    index_mb      number;
    analyze_sql   varchar2(100);
    pct_used_sql  varchar2(150);
    rebuild_sql   varchar2(150);
 
    cursor c1 is
    select segment_name, bytes
    from   user_segments
    where  segment_type = 'INDEX'
    and    segment_name like 'DW%'
    and    segment_name not like 'BIN%'
    and    bytes/1024   >= 1024
    order  by 2;

-- validate the index
procedure validate_index (v_ind in varchar2) is
begin
    analyze_sql := 'analyze index '||v_ind||' validate structure';
    begin
        execute immediate analyze_sql;
    exception
    when LOCKED then
        dbms_output.put_line (v_ind||' locked - skipping');
        pctused := 100;
        return;
    when others then
        dbms_output.put_line (analyze_sql);
        raise;
    end;

    pct_used_sql := 'select pct_used, round(blocks*8192/(1024*1024)), height from index_stats where name = '''||v_ind||'''';
    execute immediate pct_used_sql into pctused, index_mb, height;
    if pctused is null then
        pctused := 0;
    end if;
    dbms_output.put_line (rpad(v_ind,35)||' (pct used '||pctused||'% size '||index_mb||'Mb height ' || height ||')');
end validate_index;
 
-- execute SQL and trace if errors
procedure run_sql (v_sql in varchar2) is
begin
    execute immediate v_sql;
exception
when others then
    dbms_output.put_line (v_sql);
    raise;
end run_sql;
 
--
-- Script starts here
--
begin
    dbms_output.enable(1000000);
    -- Set up database name and schema
    -- select name into dbase  from v$database;
    select user into dbuser from dual;
    dbms_output.put_line('============================================================================= *');
    -- dbms_output.put_line('* Indexes rebuild report for '||dbuser||' on '||dbase);
    dbms_output.put_line('============================================================================= *');

    -- Loop around Indexes
    for x in c1 loop
        index_name := x.segment_name;
        validate_index (index_name );
        if pctused < 81 then
            if x.bytes/1024/1024 < 100 then
                rebuild_sql := 'alter index '||x.segment_name||' rebuild online';
            else
                rebuild_sql := 'alter index '||x.segment_name||' coalesce';
            end if;
            dbms_output.put_line (rebuild_sql);
            run_sql (rebuild_sql);
            validate_index (index_name);
        end if;
    end loop;
end;
/

set feed on

Rebuild unusable indexes on a table

select 'alter index '||owner||'.'||index_name||' rebuild;'
from   dba_indexes
where  1=1
and    status    != 'VALID'
and    owner     not in ('SYS','SYSTEM')
--and    owner      = 'WM822PP'
--and    table_name = 'WF_PLANTS';

Pull the latest cfengine changes now instead of waiting for scheduled time

For one host 

/opt/cfengine/bin/cfagent -ID dbaTime

On all hosts at the same time 

/home/ibmtools/scripts/oracle/dosh -c "/opt/cfengine/bin/cfagent -ID dbaTime"

Check status of datafiles without dba_data_files or v$datafile

If the database is in mount mode, most tables are unavailable. This query uses the x$ (c based) tables and are always available 

set linesize 200;
set pagesize 100;
col inst_id for 9999999 heading 'Instance #'
col file_nr for 9999999 heading 'File #'
col file_name for A50 heading 'File name'
col checkpoint_change_nr for 99999999999999 heading 'Checkpoint #'
col checkpoint_change_time for A20 heading 'Checkpoint time'
col last_change_nr for 99999999999999 heading 'Last change #'
SELECT
      fe.inst_id,
      fe.fenum file_nr,
      fn.fnnam file_name,
      TO_NUMBER (fe.fecps) checkpoint_change_nr,
      fe.fecpt checkpoint_change_time,
      fe.fests last_change_nr,
      DECODE (
              fe.fetsn,
              0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
              DECODE (BITAND (fe.festa, 18),
                      0, 'OFFLINE',
                      2, 'ONLINE',
                      'RECOVER')
      ) status
FROM x$kccfe fe,
     x$kccfn fn
WHERE    (   (fe.fepax != 65535 AND fe.fepax != 0 )
          OR (fe.fepax = 65535 OR fe.fepax = 0)
         )
     AND fn.fnfno = fe.fenum
     AND fe.fefnh = fn.fnnum
     AND fe.fedup != 0
     AND fn.fntyp = 4
     AND fn.fnnam IS NOT NULL
     AND BITAND (fn.fnflg, 4) != 4
ORDER BY fe.fenum
;

Check highest allocated extent in datafile (likely slow when having many extents)

  • Script to Detect Tablespace Fragmentation ( Doc ID 1020182.6 )
  • How to shrink or reduce the datafile size by finding the high water mark (HWM) ( Doc ID 1600774.1 )
  • How to find Objects Fragmented below High Water Mark ( Doc ID 337651.1 )
column file_name format a50;
column tablespace_name format a15;
column highwater format 9999999999;
set pagesize 9999

select a.tablespace_name
,a.file_name
,(b.maximum+c.blocks-1)*d.db_block_size highwater
from dba_data_files a
,(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b
,dba_extents c
,(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name
/

Runup to this was... 

set markup html on spool on
spool db_info.html
set echo on
select * from dba_tablespaces where tablespace_name = '&&tablespace_name';
select * from dba_data_files where tablespace_name = '&tablespace_name';
select * from (select * from dba_extents where tablespace_name = '&tablespace_name' order by block_id desc) where rownum <301;
spool off
set markup html off

select owner, segment_type, count(*) from dba_segments where tablesapce_name='&tablespace_name' group by owner, segment_type;

begin
for ii in (select owner, segment_name, segment_type from dba_segments where tablespace_name = '&tablespace_name' and segment_type like 'TABLE%' and segment_name not like '%DATAPUMP%')
loop
if ii.segment_type='TABLE' then
begin
execute immediate('alter table '||ii.owner||'.'||ii.segment_name||' enable row movement');
execute immediate('alter table '||ii.owner||'.'||ii.segment_name||' shrink space cascade');
exception when others then
dbms_output.put_line('FAILED 1: alter table '||ii.owner||'.'||ii.segment_name||' shrink space cascade : '||SQLERRM);
end;
end if;
end loop;
end;
/

Procedure to shrink space in tables and indexes

Written by Tom Kyte
Depending on the Oracle version, this procedure may or may not work! It does not include the compact clause (neither does it re-analyse to prove it worked).
Here is a worked example that shows the complete process - oracle-wiki.net 

create or replace procedure shrink_all
as
    l_sql        varchar2(4000);
    l_sql2       varchar2(4000);
    row_movement exception;
    pragma       exception_init( row_movement, -10636 );
begin
    for x in (select table_name
              ,      owner
              from   t
              where  sgm_space_management = 'AUTO')
    loop
        l_sql := 'alter table "' || x.owner || '"."' || x.table_name || '" shrink space';
        dbms_output.put_line( l_sql );

        begin
            execute immediate l_sql;
        exception
        when row_movement
        then
            dbms_output.put_line( 'failed due to row movement...' );
            l_sql2 := 'alter table "' || x.owner || '"."' || x.table_name || '" enable row movement';
            dbms_output.put_line( l_sql2 );
            execute immediate l_sql2;
            dbms_output.put_line( l_sql );
            execute immediate l_sql;
        end;

        for y in (select owner
                  ,      index_name
                  from   dba_indexes
                  where  table_owner = x.owner
                  and    table_name  = x.table_name )
        loop
            l_sql := 'alter index "' || y.owner || '"."' || y.index_name || '" shrink space';
            dbms_output.put_line( l_sql );
            execute immediate l_sql;
        end loop;

    end loop;
end;
/

How to delete/kill a distributed transaction

From pouwiel.com 

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID
----------------------
GLOBAL_TRAN_ID
--------------------------------------------------------------------------------
TO_CHAR(FAIL_TIME,'D STATE MIX
-------------------- ---------------- ---
8.19.321913
1463898948.0000013CDE8005110000000179F68840A089FFFEE644B640AED6B02438B2F39D9665F
AFB
15-feb-2013 16:37:41 prepared no


SQL> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID IN_ I
---------------------- --- -
DATABASE
--------------------------------------------------------------------------------
8.19.321913 in N
jdbc_11


SQL> rollback force '8.19.321913';

Rollback complete.

SQL> execute dbms_transaction.purge_lost_db_entry('8.19.321913');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;

no rows selected

Be aware that the support document 159377.1 does not speak of the rollback force. If you don’t execute that particular command it will not work.

ORACLE_BASE is getting set to the same value as ORACLE_HOME when using . oraenv

Permissions problem. Make sure the user has write access to oraclehomeproperties.xml file! 

ls -al $ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml

Change permissions as appropriate. A different approach which should give the same end result would be to grant the orabase executable the setuid bit. 

chmod u+s $ORACLE_HOME/bin/orabase

This allows any user to set the environment as the orabase executable will be run with the permissions of its owner.

Move table partitions to a different tablespace

Are you getting errors due to partitions belonging to a different tablespace and you want to drop the current tablepace?
ORA-14404: partitioned table contains partitions in a different tablespace

Find tables with partitions in more than one tablespace

set lines 2000 pages 50
col table_owner for a30
col table_name for a30
select table_owner
 ,     table_name
,      count(*)
from   dba_tab_partitions
where  table_owner != 'SYS'
group  by table_owner
,      table_name
having count(*) > 1
/

See which tables have partitions across multiple tablespaces... 

set lines 1000 pages 100
col table_name for a50
select distinct b.table_owner||'.'||b.table_name table_name
,      a.tablespace_name ts1
,      b.tablespace_name ts2
from   (
       select distinct tablespace_name
       ,      table_name
       ,      partition_name
       from   dba_tab_partitions
       )                  a
,      dba_tab_partitions b
where  a.table_name       = b.table_name
and    a.tablespace_name != b.tablespace_name
/

Generate statements to move the partitions from one tablespace to another... 

select 'alter table '||b.table_owner||'.'||b.table_name||' move partition '||b.partition_name||' tablespace &NEW_TABLESPACE;'
from   (
       select distinct table_name
       ,      partition_name
       from   dba_tab_partitions
       where  tablespace_name = '&&OLD_TABLESPACE'
       )                  a
,      dba_tab_partitions b
where  a.table_name       = b.table_name
and    b.tablespace_name != '&OLD_TABLESPACE'
/
undef OLD_TABLESPACE

What's left hanging around?

col SEGMENT_TYPE for a20
col OWNER for a20
col SEGMENT_NAME for a40
col PARTITION_NAME for a15
col TABLESPACE_NAME for a30
select segment_type
,      owner
,      segment_name
,      partition_name
,      tablespace_name
from   dba_segments
where  tablespace_name in ('TS_THALER_IOT_OLD','TS_THALER_CU','TS_THALER_BACKUP','TS_THALER_PART_OLD')
/

select table_owner
,      table_name
from   dba_indexes
where  index_name = '&index_name'
/

What edition is my database (Standard, Enterprise, XE...)?

Only works from 12c :-( 

select edition from sys.registry$ where cid='CATPROC'

Is database a CDB with PDBs?

Needs a bit of work. Won't work pre-12c obviously and also not on Standby databases if they are in MOUNT mode 

for db in $(ps -ef | grep [p]mon|awk -F_ '{print $NF}')
do
    export ORACLE_SID=${db}
    ORAENV_ASK=NO
    . oraenv >/dev/null 2>&1
    sqlplus -s / as sysdba<<'EOSQL'
set head off newpa none
select name
,      (select count(*) from v$pdbs) npdbs
from   v$database
/
EOSQL
done

version 2 

function is_db_cdb return boolean
is
    b_is_cdb        boolean := false;
    l_is_cdb        varchar2(3) := 'NO';
    e_col_not_found exception;
    pragma          exception_init(e_col_not_found, -904);
begin
    begin
        execute immediate 'select cdb from v$database' 
        into l_is_cdb;
    exception
        when e_col_not_found then l_is_cdb := 'NO';  -- ORA-00904
    end;

    if (l_is_cdb = 'YES') then
        return TRUE;
    else
        return FALSE;  -- either not a cdb or pre-12.1 database
    end if;

end is_db_cdb;

List triggers in the database

This query specifically lists after login triggers 

select obj.con_id
,      pdb.name
,      obj.owner
,      obj.object_name
,      to_char(obj.created, 'DD/MM/YYYY HH24:MI:SS')       created_str
,      to_char(obj.last_ddl_time, 'DD/MM/YYYY HH24:MI:SS') last_ddl_str
from   cdb_objects   obj
join   cdb_triggers trgs
       on (   obj.con_id      = trgs.con_id
          and obj.owner       = trgs.owner
          and obj.object_name = trgs.trigger_name
          )
join   v$pdbs pdb
       on ( obj.con_id  = pdb.con_id )
where  trgs.trigger_type     = 'AFTER EVENT'
and    trgs.triggering_event like 'LOGON%'
and    trgs.status           = 'ENABLED'
order  by object_name
,      obj.last_ddl_time
/

List the pluggable databases (PDB) in a container database (CDB)

The network_name column shows what you should find in tnsnames.ora for this db connection 

select name,network_name,pdb from v$services;

or 

select * from v$pdbs;

Start and stop a pluggable database

alter pluggable database pdb1 open;

alter pluggable database pdb1 close immediate;

Swith to a pluggable database

alter session set container=pdb1;

Reopen pluggable databases at container startup

By default pluggable databases in a container remain in a MOUNT state when the container starts up. 

select name, open_mode from v$pdbs where name = 'PDB1';

To modify this, open it and save its state 

alter pluggable database pdb1 open;
alter pluggable database pdb1 save state;

To see a log of issues with pluggable databases 

select * from pdb_plug_in_violations where type = 'ERROR' and status != 'RESOLVED' and name = 'PDB1';

What is the current container id in a cdb?

CDB$ROOT is container id 1. User containers start from 2. 

select sys_context('USERENV','CON_ID') from dual

What is the current container name in a cdb?

select sys_context('USERENV','CON_NAME') from dual

or just 

show con_name

Switching Between Containers

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> alter session set container=tstklok;

Session altered.

SQL>  show con_name

CON_NAME
------------------------------
TSTKLOK

Direct connection to pluggable database

These must be made using a service (defined in tnsnames.ora). Each pluggable database automatically registers a service with the listener (v$services). 

sqlplus appuser/apppwd@tstklok

or using ezconnect 

SQL> conn appuser/apppwd@//localhost:1521/tstklok

What platform is the database running on?

Could be useful for handling line endings... 

select platform_name from v$database

if instr(db_platform, 'WINDOWS') != 0 then
    crlf := CHR(13) || CHR(10);       -- Windows gets the \r and \n
else
    crlf := CHR (10);                 -- Just \n for the rest of the world
end if;

Is the database 32 or 64 bit?

If the answer is 1,7,10,15,16 or 17, then it is 32bit, everything else should be 64bit. 

select platform_id from v$database

What features is my database using?

Before converting to Standard Edition, check the features here - some may be Enterprise specific. 

col name for a45
col description for a85
set lines 2000
select name
,      detected_usages
,      description
from   dba_feature_usage_statistics
where  1=1
and    currently_used = 'TRUE'
/

How many redo log switches per hour?

set lines 100
col "YYYYMMDD HH24" for a14
select to_char(first_time,'yyyymmdd hh24') "YYYYMMDD HH24"
,      count(1)                            num_switches
from   v$log_history
group  by to_char(first_time,'yyyymmdd hh24')
order  by 1

or a bit fancier version... 

rem *********************************************************** 
rem
rem	File: log_history.sql 
rem	Description: Log switch rates from v$log_history 
rem   
rem	From 'Oracle Performance Survival Guide' by Guy Harrison
rem		Chapter 21 Page 637
rem		ISBN: 978-0137011957
rem		See www.guyharrison.net for further information
rem  
rem		This work is in the public domain NSA 
rem   
rem
rem ********************************************************* 


col min_minutes format 9999.99 
col max_minutes format 9999.99 
col avg_minutes format 9999.99 
set pagesize 1000
set lines 70
set echo on 

WITH log_history AS
       (SELECT thread#, first_time,
               LAG(first_time) OVER (ORDER BY thread#, sequence#)
                  last_first_time,
               (first_time
                - LAG(first_time) OVER (ORDER BY thread#, sequence#))
                    * 24* 60   last_log_time_minutes,
               LAG(thread#) OVER (ORDER BY thread#, sequence#)
                   last_thread#
        FROM v$log_history)
SELECT ROUND(MIN(last_log_time_minutes), 2) min_minutes,
       ROUND(MAX(last_log_time_minutes), 2) max_minutes,
       ROUND(AVG(last_log_time_minutes), 2) avg_minutes
FROM log_history
WHERE     last_first_time IS NOT NULL
      AND last_thread# = thread#
      AND first_time > SYSDATE - 1;

Show redo log groups/members

col instance       for a8
col thread         for 999
col groupno        for 999
col member         for a35
col redo_file_type for a15
col log_status     for a10
col logsize_m      for 99999
col archived       for a12

SELECT i.instance_name   instance
,      i.thread#         thread
,      f.group#          groupno
,      f.member          member
,      f.type            redo_file_type
,      l.status          log_status
,      l.bytes/1024/1024 logsize_m
,      l.archived        archived
FROM   gv$logfile  f
,      gv$log      l
,      gv$instance i
WHERE  1=1
and    f.group#  = l.group#
AND    l.thread# = i.thread#
AND    i.inst_id = f.inst_id
AND    f.inst_id = l.inst_id
ORDER  BY i.instance_name
,      f.group#
,      f.member;

Korn shell timestamp function

Example usage: echo "`ts`: Checking backup status" 

function ts {
    date +'%d-%b-%Y %H:%M:%S'
}

Drop a database

Dropping a database including backups should be considered criminal in a production environment! 

sqlplus / as sysdba
startup force mount exclusive restrict
exit
rman target /
drop database including backups noprompt;
exit

or 

RMAN> connect target sys/pass@test
connected to target database: test (dbid=123456789)

RMAN> startup force mount
RMAN> sql 'alter system enable restricted session';
RMAN> drop database including backups noprompt;

Use sub-select or in-line views to update base tables

Inline views can be used to update base table data 

update
    (
    select p.list_price
    from   products           p
    ,      product_categories pc
    where  1=1
    and    p.category_id    = pc.category_id
    and    pc.category_name = 'CPU'
    )
set list_price = list_price * 1.15;

Use sub-select or in-line views to delete base table data

A different way to delete rows in base tables, using Inline views or subselect 

delete
    (
    select p.list_price
    from   products           p
    ,      product_categories pc
    where  1=1
    and    p.category_id    = pc.category_id
    and    pc.category_name = 'CPU'
    )
where list_price < 1000;

Mass update of files using perl inline script

for server in `cat /home/tools/etc/oracle/oracle_servers`; do
    ssh $server “perl -p -i -e 's/T01/D01/' /home/tools/scripts/rman/inclexcl.lst”
done

How to use vi-style editing in SQL*Plus

These instructions are for Redhat but other versions will be very similar

  • Download rlwrap from [1] or [2]
  • sudo yum install rlwrap
  • build a dictionary file for autocomplete on pressing Tab
vi $HOME/sql.dict
select
from
dba_users
dba_data_files
dba_tablespaces

alias sysdba='rlwrap -f $HOME/sql.dict sqlplus / as sysdba'

or for general command use... 

alias sqlplus='rlwrap -D2 -ic sqlplus'
alias dgmgrl='rlwrap -D2 -ic dgmgrl'
alias rman='rlwrap -D2 -ic rman'
alias asmcmd='rlwrap -D2 -ic asmcmd'
alias lsnrctl='rlwrap -D2 -ic lsnrctl'
alias adrci='rlwrap -D2 -ic adrci'
alias impdp='rlwrap -D2 -ic impdp'
alias expdp='rlwrap -D2 -ic expdp'

Execute an SQL statement on all databases on all servers for a customer

Run from customer's management server 

./dosh -c "su - oracle -c '/home/tools/scripts/oracle/all_db_do -v \"select username, account_status, profile from dba_users order by 3;\"'" >user_status.lst

or 

SCRIPTS_DIR=/home/tools/scripts/oracle
$SCRIPTS_DIR/dosh -c "su - oraibm -c \"$SCRIPTS_DIR/all_db_do 'alter user ops\\\$oraibm profile oracle_system_user;'\""
$SCRIPTS_DIR/dosh2 -c "su - oraibm -c \"$SCRIPTS_DIR/all_db_do 'alter user ops$oraibm profile oracle_system_user;'\""
$SCRIPTS_DIR/dosh2 -c "su - oraibm -c \"$SCRIPTS_DIR/all_db_do 'alter system set control_file_record_keep_time=31;'\""

Where is the alert log/alertlog?

Older systems 

select value from v$parameter where name='background_dump_dest';

Newer systems 

select value from v$diag_info where name='Diag Trace';

Grant normal users access to the alertlog table (sys.x$dbgalertext)

create view x$dbgalertext_v as select * from x$dbgalertext;
grant select on x$dbgalertext_v to &&grantee;
create or replace synonym &grantee..x$dbgalertext for sys.x$dbgalertext_v;
undef grantee

Search alert log for ORA-00600 and ORA-07445 errors

Scan the database table version of the alertlog for errors without reporting what was already found. 

select indx, message_text from x$dbgalertext where message_text like '%ORA-00600%' or message_text like '%ORA-07445%' and indx > &last_queried_indx order by indx desc;

Simulating errors in alert log

Sometimes you need to inject error messages into the alertlog to test your monitoring system. This generates the entries in the alertlog and in x$dbgalertext table. 

exec sys.dbms_system.ksdwrt(2, 'ORA-07445: Testing this error code for Zenoss, do not investigate.');

This additionally creates a trace file. 

exec sys.dbms_system.ksdwrt(3, 'ORA-00600: Testing this error code for Zenoss, do not investigate.');

tail the database alert log

If an ADR error is displayed, then ORACLE_SID is probably not set 

alias alertlog='adrci exec="set home diag/rdbms/$(echo $ORACLE_SID | tr A-Z a-z)/$ORACLE_SID; show alert -tail -f"'

What is my WAN ip address?

curl -A "Mozilla/4.0" http://checkip.dyndns.org/ 2>/dev/null | perl -ne 'print $1 if /Current IP Address: (\d+\.\d+\.\d+\.\d+)/'

or 

curl -A "Mozilla/4.0" http://myip.dnsdynamic.org/ 2>/dev/null

Wait for child pid to finish in background and report its status

# submit a process to the background
export_schema1.ksh &
bgpid=$!

while (ps -ef | grep $bgpid | grep -v grep); do
    # still running...
    sleep 600
done

# should be finished
wait $bgpid
bgstatus=$?
echo background process ended with status $bgstatus

or 

#!/usr/bin/ksh
# submit a few processes to the background
# wait for them all to finish
# concat their logfiles

(./sleeper.sh;echo "sleeper 1 finished")>sleeper1.log &
PIDLIST="$PIDLIST $!"
echo "PIDLIST is $PIDLIST ..."
sleep 5

(./sleeper.sh;echo "sleeper 2 finished")>sleeper2.log &
PIDLIST="$PIDLIST $!"
echo "PIDLIST is $PIDLIST ..."
sleep 5

(./sleeper.sh;echo "sleeper 3 finished")>sleeper3.log &
PIDLIST="$PIDLIST $!"
echo "PIDLIST is $PIDLIST ..."
sleep 5


echo "Now 15 seconds later, we have 3 pids: $PIDLIST"
for PID in $PIDLIST; do
    wait $PID
    echo "$PID exited with status $?"
done

# all jobs should have ended, concat their logs
cat sleeper*log > sleepers.log
cat sleepers.log

Real world example 

#!/usr/bin/ksh
# ==============================================================================
# Name         : export_JDBEOP1.ksh
# Description  : Run export in pieces for performance reasons
#
# Parameters   : 
#
# Notes        : Also decide whether to exclude PDARC schema or not
#                depending on what day it is
#
#
# Modification History
# ====================
# When      Who               What
# ========= ================= ==================================================
# 15-OCT-13 Stuart Barkley    Created
# ==============================================================================

STARTTIME=`date '+%Y%m%d%H%M%S'`
DAYNO=`date +%u`
PROGNAME=`basename $0`
SCRIPTS_DIR=/home/tools/scripts/oracle
DATA_DIR=/oracle/JDBEOP1/admin/change
EXPORT_DIR=/oracle/export/JDBEOP1

# marker so we can find our files later
MARKERFILE=/tmp/start_$PROGNAME_$$
touch $MARKERFILE

$SCRIPTS_DIR/expdp_PARFILE.ksh -s JDBEOP1 -u PRODDTA -p 4 -k 2 -f $DATA_DIR/JDBEOP1_PRODDTA.parfile &
PIDLIST="$PIDLIST $!"
echo "PIDLIST is $PIDLIST ..."

$SCRIPTS_DIR/expdp_PARFILE.ksh -s JDBEOP1 -u PRODCTL -p 2 -k 2 -f $DATA_DIR/JDBEOP1_PRODCTL.parfile &
PIDLIST="$PIDLIST $!"
echo "PIDLIST is $PIDLIST ..."

$SCRIPTS_DIR/expdp_PARFILE.ksh -s JDBEOP1 -u NOPRODNOPDARC -p 4 -k 2 -f $DATA_DIR/JDBEOP1_NOPROD_NOPDARC.parfile &
PIDLIST="$PIDLIST $!"
echo "PIDLIST is $PIDLIST ..."

if [[ $DAYNO -eq 1 ]]; then
    $SCRIPTS_DIR/expdp_PARFILE.ksh -s JDBEOP1 -u NOPROD -p 4 -k 2 -f $DATA_DIR/JDBEOP1_NOPROD.parfile &
    PIDLIST="$PIDLIST $!"
    echo "PIDLIST is $PIDLIST ..."
fi

# now sit and wait for the exports to finish
for PID in $PIDLIST; do
    echo "waiting for pid $PID to finish"
    wait $PID
    echo "$PID exited with status $?"
done

# collect all log files up into one
cd $EXPORT_DIR
for i in `find . -name "expdp_JDBEOP1*log" -newer $MARKERFILE`; do
    cat $i > expdp_JDBEOP1_D_FULL_${STARTTIME}.log
done

rm $MARKLERFILE

Create private DB link for a user without knowing his password

It uses a clever package, dbms_sys_sql.
Also useful for executing anything on behalf of another user.
Here is an example on how to create private database link for a user. 

create or replace procedure link_creator ( p_owner varchar2, p_dblink_name varchar2, p_username varchar2, p_password varchar2, p_address varchar2 ) as
    uid number;
    sqltext varchar2(1000) := 'create [public] database link [link_name] connect to [username] identified by [password] using ''[address]''';
    myint integer;
begin
    if upper(p_owner) = 'PUBLIC' then
        sqltext := replace(sqltext, '[public]', 'public');
    else
        sqltext := replace(sqltext, '[public]', '');
    end if;
    sqltext := replace(sqltext, '[linkname]', p_dblink_name);
    sqltext := replace(sqltext, '[username]', p_username);
    sqltext := replace(sqltext, '[password]', p_password);
    sqltext := replace(sqltext, '[address]', p_address);

    select user_id
    into   uid
    from   dba_users
    where  username = decode(upper(p_owner), 'PUBLIC', 'SYS', p_owner)
    ;
    myint:=sys.dbms_sys_sql.open_cursor();
    sys.dbms_sys_sql.parse_as_user ( myint, sqltext, dbms_sql.native, uid );
    sys.dbms_sys_sql.close_cursor(myint);
end;
/

Here we can generate SQL to rebuild the links for future use. Note the password is no longer stored in this column. 

set pages 1000
select 'begin link_creator ( '''|| u.name ||''','''|| l.name ||''','''|| l.userid ||''','''|| l.password ||''','''|| l.host ||'''); end;'|| chr(10) ||'/' link_text
from   sys.link$ l
,      sys.user$ u
where  l.owner# = u.user#
;

Another method to create a db_link from sys on behalf of a user is to create a procedure under that users name that does the db link creation

grant create database link to &&user;

create or replace procedure &user..create_db_link as
begin
    execute immediate 'create database link &link_name connect to &user identified by &users_password using ''&tns_connect_identifier''';
end create_db_link;

exec &user..create_db_link;

revoke create database link from &user;

drop procedure &user..create_db_link;
undef user

Create a comma separated list of columns from a select statement

Method 1: 

SELECT parent_id,
       RTRIM(XMLAGG(XMLELEMENT(e,child_id || ',')).EXTRACT('//text()'),',') AS "Children"
FROM   parentChildTable
WHERE  parent_id = 0
GROUP  BY parent_id
/

or 

SELECT parent_id,
       LISTAGG(child_id, ',') WITHIN GROUP (ORDER BY child_id) AS "Children"
  FROM parentChildTable
 WHERE parent_id = 0
 GROUP BY parent_id
/

Method 2 (undocumented and cannot therefore be relied on to continue working in the same manner): 

SELECT wmsys.wm_concat(<column_name>)
FROM <table_name>
/

Start an instant one-line webserver in current directory

Need to quickly share/copy a file or read an html file on Linux? Start a web server! 

python -m SimpleHTTPServer 8000

and point your browser to http://localhost:8000
or 

while true; do nc -l -p 80 -q 1 < index.html; done

Get SQL*Plus to generate HTML

One way to do it... use -m(arkup) option to specify:
html on/off - specifies whether to output html output or not head - specify your own customised head contents
spool off - as a part of the -m tag means do not print the default <html>, <head> and <body> tags

sqlplus -m "html on head '<link rel="stylesheet" href="/dbamon_golden.css" type="text/css" />' spool off" / as sysdba

or 

sqlplus / as sysdba
set markup html on head '<link rel="stylesheet" href="/dbamon_golden.css" type="text/css" />' body "" table "" spool off

besides html and head, you can also specify:
body - specify your own customised body attributes
table - override the default table options
entmap - turn on or off the html replacement characters (&lt, &gt, etc...)
preformat - uses the <pre> tag to format output exactly as required

  • another little gem...
----------------------------------------
-- get the last SQL*Plus output in HTML
-- after Tanel Poder
----------------------------------------

set termout off

set markup HTML ON HEAD " -
 -
" -
BODY "" -
TABLE "border='1' align='center' summary='Script output'" -
SPOOL ON ENTMAP ON PREFORMAT OFF

spool myoutput.html

l
/

spool off
set markup html off spool off
host firefox myoutput.html
set termout on

HTML SELECT - Trigger JavaScript ONCHANGE event even when the option is not changed

<select onchange="jsFunction()">
  <option value="" disabled selected style="display:none;">Label</option>
  <option value="1">1</option>
  <option value="2">2</option>
  <option value="3">3</option>
</select>

Display horizontal bar graph in HTML table data cell

Using Perl 

print ('<td class="left">' .
            '<div style="background-color:#8097BE;width:"',$allocpercused  ,'%;" />' .
            '<div style="background-color:red;    width:"',$automaxpercused,'%;" />' .
            $tspace .
       '</td>'
);

Depending on which way around the divs are, you get different interpretations 

print ("<td class=\"left\">" .
            "<div style=\"border-right:2px solid red; width:",($automaxpercused>99)?(100):($automaxpercused),"%;\" />" .
            "<div style=\"background-color:#8097BE;width:",$allocpercused  ,"%;\" />" .
            $tspace .
       "</td>"
);

References

Retrieved from "https://mailnest.com/dbawiki/index.php?title=Snippets&oldid=3396"