Snippets

From dbawiki
Revision as of 10:36, 20 May 2015 by Stuart (talk | contribs) (→‎Find unique / primary index columns / keys in a table)
Jump to: navigation, search

Contents

Restart ITM Monitoring agent

To see if the Oracle module is installed

export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/bin/cinfo -i

and see which agents are running 

export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/bin/cinfo -r

See which instances have been setup to be monitored

export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/smitools/scripts/kdyedit -h /opt/IBM/ITM list

export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/smitools/scripts/kciedit -h /opt/IBM/ITM list

Add an instance to be monitored

$ITM_HOME/smitools/scripts/kdyedit -h /opt/IBM/ITM -r itmora -t or add -i $ORACLE_SID

This one should also be done in order that rc.d scripts know to start the agent as itmora instead of root 

$ITM_HOME/smitools/scripts/kciedit -h /opt/IBM/ITM -r itmora -t or add -i $ORACLE_SID

Restart the Oracle agents

To avoid issues with orphaned processes, restart is best done with a combination of stop, cleanup, start.
Stop all Oracle agents 

export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/smitools/scripts/StartAgent.sh stop or

Check agent processes have stopped 

ps -ef | grep [k]or

Any remaining processes (containing a SID) should be killed.
It is normal to have agents for 61, ul and ux still running.

Check agent RunInfo file is correct
If this file is corrupted, the StartAgent.sh script does not function correctly 

vi $ITM_HOME/config/.ConfigData/RunInfo

...and remove any lines containing database SIDs in the 4th column

Start all Oracle agents 

$ITM_HOME/smitools/scripts/StartAgent.sh start or


or
...using ITM standard command to restart one agent 

/usr/bin/su - itmora -c "/opt/IBM/ITM/bin/itmcmd agent  -o EMREP  stop or"

Perform cleanup as above 

/usr/bin/su - itmora -c "/opt/IBM/ITM/bin/itmcmd agent  -o EMREP  start or"

Check the agent is running

ps -ef | grep [k]or

Reconfigure agent

export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/bin/itmcmd config -A or

Troubleshooting

cinfo -r reports processes not running (as well as running)

eg: 

coupprod  or    25690298                    DMMPRD  ...process not running
coupprod  or    5832894                     DMMPRD  ...process not running
coupprod  ux    39452858  root    Apr23     None    ...running            
coupprod  or    43319374  itmora  Apr24     DMMPRD  ...running

need to remove the first 2 lines... 

cd /opt/IBM/ITM/config/.ConfigData
vi RunInfo

and remove the unnecessary lines

Find unique / 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 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

Write to a trace log from PL/SQL

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

Write to the alert log from PL/SQL

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

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'

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 get something from the operating system from inside SQL

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!

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 find ORACLE_HOME for a SID if there is no oratab

(it happens if you don't use dbca, and some people like it that way!)
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 (the accepted way is to ls the proc directory but this doesn't work for me? depends on AIX version? 

$ 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 /proc/13893878/cwd

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 '\';

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"

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.

How many redo log switches per hour?

select to_char(first_time,'yyyymmdd hh24')
,      count(1)
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 999.99 
col max_minutes format 999.99 
col avg_minutes format 999.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'
}

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 rewrap 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'

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;'\""

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';

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#
;

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 web server in current directory

Need to quickly share/copy a file? 

python -m SimpleHTTPServer 8000

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

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

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=1896"