Run Some SQL On All Databases On Server

for ORACLE_SID in $( ps -ef|grep [p]mon|awk -F'_' '{ print $3 }' )
do
export ORACLE_SID
ORAENV_ASK=NO
. oraenv
echo "$ORACLE_SID"
sqlplus -s / as sysdba  <<EOF
<your SQL here>
EOF
done

e.g. Recompile invalid objects in all databases
for ORACLE_SID in $( ps -ef|grep [p]mon|awk -F'_' '{ print $3 }' )
do
export ORACLE_SID
ORAENV_ASK=NO
. oraenv
echo "$ORACLE_SID"
sqlplus -s / as sysdba  <<EOF
@?/rdbms/admin/utlrp parallel 4;
EOF
done

Database Link Syntax

It is tempting to create database links by refencing the aliases from a tnsnames.ora file. e.g.

CREATE DATABASE LINK SCOTTS_DB_LINK
CONNECT TO SCOTT IDENTIFIED BY tiger
USING 'PROD_SERV';

But the "one ring to rule them all" tnsnames.ora approach can come back and bite you if the tnsnames.ora file is ever accidentally lost or - worse - replaced with one that is pointing, say, to Production, rather than Development databases!

Rather than using tnsnames.ora aliases, hard-code instead. e.g.

CREATE DATABASE LINK SCOTTS_DB_LINK
CONNECT TO SCOTT IDENTIFIED BY tiger
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PROD01.my.domain)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PROD_SERVICE))))';

Now, no matter what happens to your tnsnames.ora file, you know that your database link is pointing to the correct database.

Query Results in Comma-Separated String

Another hidden gem.

Ever wanted to get the results of a query in a single string, with values separated by commas? e.g. you want all tables for a user to be exported using Data Pump.

Here's an easy way to get that list of tables in Data Pump-friendly format:

select wm_concat('SCOTT.'||table_name)

from dba_tables 

where owner = 'SCOTT';

Results:
SCOTT.TABLE_01,SCOTT.TABLE_02,SCOTT.TABLE_03, ...

wm_concat is an Oracle-supplied function and may change at some point in the future, etc., etc.

Object Dependencies

I am currently reacquainting myself with the Oracle documentation, starting with the Concepts Manual, now on Database Administration, and on to a load more. It's surprising what one can forget, or think one knows, but doesn't.

Anyway, I was surprised to find that there is a nice little tool - written 11 years ago - called DEPTREE. 

The doco is here.

NOTE: the objects created by utldtree.sql are created in the schema you are current logged in to.