Getting all users and roles that can access objects in a schema

To list all users and roles who can access objects in a schema, we can use following query. This will be useful when there is a need of migrating a schema from one database to another.

with
users_roles as(
        (select grantee, granted_role from dba_role_privs) union
        (select username, username from dba_users) union
        (select role, role from dba_roles)
),
direct_grantees as (
        select distinct grantee 
        from dba_tab_privs 
        where owner in ('SCHEMA_USER')
),
all_grantees as (
        select distinct grantee
        from users_roles
        start with 
                granted_role in (select * from direct_grantees)
        connect by nocycle prior grantee = granted_role
)
select grantee 
from all_grantees 
-- Filter as needed
-- For example, get only users not roles
where grantee in (select username from dba_users);

Automated spool file name generation

There was requirement recently, where spool file of SQLPlus has to be generated with an automatic name that consists of database name with timestamp (for example, db001_2014-12-26_04-54-13.log). Here is the script that generates spool file name dynamically.

set termout off
set feedback off
undefine spoolfile
column spoolfile new_value spoolfile noprint
select sys_context('userenv', 'db_name') || '_' || to_char(sysdate, 'YYYY-MM-DD_HH24-MI-SS') || '.log' as spoolfile from dual;
set termout on
set feedback on

set echo on
spool &spoolfile

select sysdate from dual;

spool off

Scripts to start and stop Oracle Business Intelligence (OBIEE) on Linux

Tested for OBIEE version: 11.1.1.7, and Linux version: OEL 6

Environment variables:

export MW_HOME=/u01/bi/mw_home
export DOMAIN_HOME=$MW_HOME/user_projects/domains/bifoundation_domain
export WL_HOME=$MW_HOME/wlserver_10.3
export ORACLE_INSTANCE=$MW_HOME/instances/instance1
export PATH=$WL_HOME/server/bin:$DOMAIN_HOME/bin:$ORACLE_INSTANCE/bin:$PATH
export ORACLE_HOME=$MW_HOME/Oracle_BI1

The scripts asks username and password for weblogic while starting/stopping. We can store them in below files so they will not be asked.
$DOMAIN_HOME/servers/AdminServer/security/boot.properties
$DOMAIN_HOME/servers/bi_server1/security/boot.properties

Contents to add in the above mentioned files:

username=weblogic
password=xxxxx

Though we are storing passwords in clear text, they will be encrypted automatically after weblogic started.

Start script:

 
export MW_HOME=/u01/bi/mw_home
export DOMAIN_HOME=$MW_HOME/user_projects/domains/bifoundation_domain
export WL_HOME=$MW_HOME/wlserver_10.3
export ORACLE_INSTANCE=$MW_HOME/instances/instance1
export PATH=$WL_HOME/server/bin:$DOMAIN_HOME/bin:$ORACLE_INSTANCE/bin:$PATH
export BI_LOG_DIR=/u01/bi/logdir
export ORACLE_HOME=$MW_HOME/Oracle_BI1

#################################################################
date

echo "Starting Weblogic"

nohup sh $DOMAIN_HOME/bin/startWebLogic.sh >> $BI_LOG_DIR/wls_start.log &
echo "Log: $BI_LOG_DIR/wls_start.log"

c=0
msg="<Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode>"

while [ $c -eq 0 ]
do
 sleep 1
 c=`tail -1 $BI_LOG_DIR/wls_start.log | grep "$msg"  | wc -l`
done

echo "Weblogic started"

#################################################################
date 

echo "Starting Node manager"

nohup sh  $WL_HOME/server/bin/startNodeManager.sh >> $BI_LOG_DIR/startNodeManager.log &
echo "Log: $BI_LOG_DIR/startNodeManager.log"

c=0
msg="INFO: Secure socket listener started on port"

while [ $c -eq 0 ]
do
 sleep 1
 c=`tail -1 $BI_LOG_DIR/startNodeManager.log | grep "$msg"  | wc -l`
done

echo "Node manager started"

#################################################################
date

echo "Starting Managed server"

nohup sh $DOMAIN_HOME/bin/startManagedWebLogic.sh bi_server1 >> $BI_LOG_DIR/start_bi_server1.log &
echo "Log: $BI_LOG_DIR/start_bi_server1.log"

c=0
msg="<Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode>"

while [ $c -eq 0 ]
do
 sleep 1
 c=`tail -1 $BI_LOG_DIR/start_bi_server1.log | grep "$msg"  | wc -l`
done

echo "Managed server started"

#################################################################
date

echo "Starting OPMN"

$ORACLE_INSTANCE/bin/opmnctl startall
opmnctl status

echo "OPMN started"

#################################################################

Stop script:

export MW_HOME=/u01/bi/mw_home
export DOMAIN_HOME=$MW_HOME/user_projects/domains/bifoundation_domain
export WL_HOME=$MW_HOME/wlserver_10.3
export ORACLE_INSTANCE=$MW_HOME/instances/instance1
export PATH=$WL_HOME/server/bin:$DOMAIN_HOME/bin:$ORACLE_INSTANCE/bin:$PATH
export BI_LOG_DIR=/u01/bi/logdir
export ORACLE_HOME=/u01/bi/mw_home/Oracle_BI1

$ORACLE_INSTANCE/bin/opmnctl stopall
$DOMAIN_HOME/bin/stopManagedWebLogic.sh bi_server1 
$DOMAIN_HOME/bin/stopWebLogic.sh 

ps -ef | grep weblogic.NodeManager | grep -v grep

p=`ps -ef | grep weblogic.NodeManager | grep -v grep | tr -s ' ' |  cut -f 2 -d ' '`


if [ -n "$p" ] 
then
 echo "Killing pid $p"
 kill -9 $p
fi

Writing INTERVAL expression of DBMS_JOB for exactly timed schedules

On heavily loaded database with lot of jobs scheduled, sometimes we need to time jobs to run at exactly specific intervals in a day. Examples
1. Running a job at every 30 minutes, and timing exactly at 00:10, 00:40, 1:10, 1:40...
2. Running a job at every 6 hours, and timing exactly at 3:00, 9:00, 15:00 ...

Even if a specific instance of job runs at an random time, the next run should fall into timed schedule. It means, for a job that runs at every 6 hours starting with 3:00 (3:00, 9:00, 15:00..), if it's run at 4:30, next run should be at scheduled time 9:00 (not at 4:30+6 i.e 10:30).

Here is how we can derive interval property of a job to get next scheduled date and time.

 
date_time_of_next_scheduled_run = today_date + time_of_next_scheduled_run

time_of_next_scheduled_run = time_of_last_scheduled_run + interval_between_runs

time_of_last_scheduled_run = time_of_first_scheduled_run + (number_of_times_run_completed_today * interval_between_runs) 

number_of_times_run_completed_today = floor((present_time_of_day - time_of_first_scheduled_run)/interval_between_runs)

By substituting all the above, we can get an expression for interval

 
date_time_of_next_scheduled_run =   today_date 
                                  + time_of_first_scheduled_run 
                                  + floor(
                                          (present_time_of_day - time_of_first_scheduled_run)
                                          /interval_between_runs
                                         ) 
                                       * interval_between_runs 
                                   + interval_between_runs

For example, a schedule for every 30 minutes, and timing at 00:10, 00:40, 1:10, 1:40...,

 
today_date = trunc(sysdate)
time_of_first_scheduled_run (in seconds) = 00:10 = 10*60
interval_between_runs (in seconds) = 30*60
present_time_of_day (in seconds) = to_char(d,'SSSSS')

date_time_of_next_scheduled_run = 
trunc(sysdate) +                             
  (   10*60                                  
    + floor(                                 
            (to_char(d,'SSSSS')-(10*60))     
            /(30*60)                         
            )                                
         *(30*60)                            
     + (30*60)                               
   )/(24*60*60)                              
   

We can test if this working by sample PL/SQL program which prints next scheduled time for every minute.

 
declare 
  d date; 
begin 
  for i in 0 .. 150
  loop 
    d := to_date('2014-05-15 00:00', 'YYYY-MM-DD HH24:MI') + i/(24*60); 
    dbms_output.put(to_char(d, 'YYYY-MM-DD HH24:MI') || ' - '); 
    
    dbms_output.put_line( to_char(
          trunc(d) + ( 10*60 + floor( (to_char(d,'SSSSS')-(10*60)) /(30*60) ) *(30*60) + (30*60) )/(24*60*60)  
     , 'YYYY-MM-DD HH24:MI')); 
    
  end loop; 
end;
/​

2014-05-15 00:00 - 2014-05-15 00:10
2014-05-15 00:01 - 2014-05-15 00:10
...
2014-05-15 00:09 - 2014-05-15 00:10
2014-05-15 00:10 - 2014-05-15 00:40
2014-05-15 00:11 - 2014-05-15 00:40
...
2014-05-15 00:39 - 2014-05-15 00:40
2014-05-15 00:40 - 2014-05-15 01:10
2014-05-15 00:41 - 2014-05-15 01:10
...
2014-05-15 01:09 - 2014-05-15 01:10
2014-05-15 01:10 - 2014-05-15 01:40
2014-05-15 01:11 - 2014-05-15 01:40
...

Other examples of interval expression

Schedule for every 6 hours starting with 3:00 (3:00, 9:00, 15:00..)

trunc(sysdate) + ( 3*60*60 + floor( (to_char(sysdate,'SSSSS')-(3*60*60)) /(6*60*60) ) *(6*60*60) + (6*60*60) )/(24*60*60)

Schedule for every 1 hour starting with 0:00 (0:00, 1:00, 2:00, 3:00..)

trunc(sysdate) + (  floor( (to_char(sysdate,'SSSSS')) /(1*60*60) ) *(1*60*60) + (1*60*60) )/(24*60*60)

My style (CSS) for blogs

Here is sample HTML code with CSS inside, which I am planning to use as template for future blog posts. This is initial one, and may be enhanced as needed.

<html>

<head>
<style type="text/css">

pre {
 white-space: pre-wrap;
 font-family: Ubuntu Mono, Courier New, monospace;
 color: #000000;
 background-color: #dddddd;
 border-style:dashed;
 border-width:thin;
 margin-top:1px;margin-bottom:25px;margin-right:50px;margin-left:25px;
 }

body {
 font-family: Droid Sans, Verdana, Sans-Serif;
 color: #000000;
 background-color: #ffffff;
 line-height:125%
 }

</style>
</head>

<body>

<p> This is a sample text paragraph 1. </p>

<p> This is a sample text paragraph 2. </p>

<pre>
This is a sample code line 1
  This is a sample code line 2 with indentation
</pre>

<p> This is a sample text paragraph 3. </p>

</body>

</html>

Loading LOB data using SQL Loader

When there is LOB data at client side, SQL loader can be used to load into Oracle tables. Here are the steps to do it.

Create table with LOB column(s), if it is not already there.

create table tab1
( 
  id   number(5),
  text varchar2(10),
  dt   date,
  doc  clob
);

Here is the CSV file with data. Instead of LOB data, there will be respective file names that contain LOB data.

$ cat data.csv 
1,AAA,2014-05-01,doc1.txt
2,BBB,2014-05-02,doc2.txt

$ cat doc1.txt 
Text of doc1

$ cat doc2.txt 
Text of doc2

The control file of SQL Loader looks like below. There will be FILLER column for file names.

$ cat load.ctl 
LOAD DATA 
INFILE 'data.csv'
   APPEND INTO TABLE tab1
   FIELDS TERMINATED BY ','
   (
    id        CHAR(5),
    text      CHAR(10),
    dt        DATE "YYYY-MM-DD" ":dt",
    file_name FILLER CHAR(100),
    doc       LOBFILE(file_name) TERMINATED BY EOF
    )

The output on screen looks like below when sqlldr command is executed.

$ sqlldr userid=murty/xxxx@mm1 control=load.ctl 

SQL*Loader: Release 12.1.0.1.0 - Production on Tue May 13 10:13:26 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 2

Table TAB1:
  2 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.

We can verify if the data is loaded successfully

SQL> select * from tab1;

 ID TEXT       DT
---------- ---------- ---------
DOC
--------------------------------------------------------------------------------
  1 AAA       01-MAY-14
Text of doc1

  2 BBB       02-MAY-14
Text of doc2

The same procedure can be used for BLOB data also.