Using Case When

Considering the following table, suppose I want to count how many values associated with the same key are greater than 10, what can I do?

SQL> select * from t;

       KEY        VAL
---------- ----------
         1          1
         1          2
         1         10
         1         11
         2          1
         2          2
         2          3
         3         13
         3         33
         3          3

10 rows selected.

If I use the following statement, the problem is it does not show key=2, in which case there are no values greater than 10:

SQL> select key, count(*) from t where val > 10 group by key;

       KEY   COUNT(*)
---------- ----------
         1          1
         3          2

Case when probably is what I want:

SQL> select key, sum(case when val > 10 then 1 else 0 end) from t group by key;

       KEY SUM(CASEWHENVAL>10THEN1ELSE0END)
---------- --------------------------------
         1                                1
         2                                0
         3                                2

Table mode import - same table name scenario

I have tested the folloiwng scenario in a 9i database:
- we have two tables with same name under different schemas
- we want to imp these two tables.


exp.par looks like:
-----
file=test.dmp
tables=
system.t,
denis.t
-----

imp.par looks like:
-----
file=test.dmp
fromuser=system,denis
touser=system,denis
tables=
t
-------
Note: I only specified one table name 't' in the par file

The following is the screen output during imp:

$ imp denis/oracle parfile=imp.par
 
Import: Release 9.2.0.8.0 - Production on Fri Feb 27 09:22:39 2009
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
 
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. . importing table                            "T"          1 rows imported
. importing DENIS's objects into DENIS
. . importing table                            "T"          1 rows imported
Import terminated successfully without warnings.
 

I verified that different table T created under desired schemas after imp.

Passing in a shell variable to AWK program used as a match pattern

I have a need to get the backlog message of a shareplex replicaton queue based on the queue name, I am not aware that there is a simple command in SharePlex to do it. So I developed a script. I have learned how to pass in a shell variable to the AWK program. This variable will thus be used as a match pattern in the AWK program.

The outoput of the SharePlex command I know to show the backlog messages looks like:

$ sp_ctrl qstatus on myhostname

Queues Statistics for myhostname
  Name:  QueueD (o.prddb-o.rptdb) (MTPost queue)
    Number of messages:      11825 (Age         7 min; Size      21547 mb)
    Backlog (messages):        126 (Age         0 min)

  Name:  QueueA (o.prddb-o.rptdb) (MTPost queue)
    Number of messages:     809345 (Age        48 min; Size     414840 mb)
    Backlog (messages):     806421 (Age        44 min)

  Name:  QueueB (o.prddb-o.rptdb) (MTPost queue)
    Number of messages:      49512 (Age       158 min; Size      71059 mb)
    Backlog (messages):      14425 (Age         4 min)

  Name:  QueueC (o.prddb-o.rptdb) (MTPost queue)
    Number of messages:       9678 (Age         8 min; Size     129632 mb)
    Backlog (messages):        155 (Age         0 min)

  Name:  QueueE (o.prddb-o.rptdb) (MTPost queue)
    Number of messages:          2 (Age         0 min; Size      28042 mb)
    Backlog (messages):          0 (Age         0 min)

My script:

----- script backlog_q.sh -------
#!/bin/ksh
# script bakclog_q.sh
# display backlog of a queuue

qname="QueueB"
sp_ctrl qstatus on myhostname  /usr/xpg4/bin/awk -v pat="$qname" '
begin
{
   if ( $0 ~ pat ) {
     getline
     getline
     print $3
   }
}'  read backlog

echo $backlog

---- end of the script --------

Set up read-only materialized view replication

I was assigned a task to evaluate the feasibility of seting up materialized view replication for another team. Currently this team obtains the data from one of our reporting databases through export and import. So I started to understand materialized view replication. As the first step, I build a testing materialized view replication environment on my pc. Below are some notes as the result of this effort.

 

 =========================================
 == Set up  read-only MVIEW replication ==
 =========================================

Environment:
   Master site (DBT920)             : 9.2.0.4
   Materialized view site  (DBT10G) : 10.2.0.2
 
Reference:
1. Metalink Note 256235.1 Scripts to create Trusted / Untrusted ReadOnly MVIEW Replication Sites
2. http://www.hpfuchs.com/2008/02/06/materialized-view-replication/

Overview
~~~~~~~~~~
1. create users at master site
2. create users at mview site
3. create database link at mview site
4. create mview logs at master site
5. create mview at mview site
6. create mview groups
7. Some basic operations


Detailed Steps
~~~~~~~~~~~~~~~

1. create users at master site

   -- run as system

   create user mviewproxy identified by mviewproxy;

   grant create session to mviewproxy;
   grant create any table to mviewproxy;
   grant comment any table to mviewproxy;
   grant select any table to mviewproxy;

   BEGIN
     dbms_repcat_admin.register_user_repgroup(
        username =>  'mviewproxy',
        privilege_type => 'proxy_mviewadmin',
        list_of_gnames =>  NULL);
   END;
   /

   Note: Not sure if this register_user_repgroup is necessary


2. create users at mview site

   -- run as system

   create user mvowner identified by oracle
   default tablespace users temporary tablespace temp;
   grant connect, resource to mvowner;
   grant create materialized view to mvowner;
   grant create database link to mvowner;

   -- run as system
   
   CREATE USER mviewadmin IDENTIFIED BY mviewadmin;
   ALTER USER mviewadmin DEFAULT TABLESPACE users;
   ALTER USER mviewadmin TEMPORARY TABLESPACE temp;

   EXECUTE dbms_repcat_admin.grant_admin_any_schema('mviewadmin');
   GRANT comment any table TO mviewadmin;
   GRANT lock any table TO mviewadmin;

   GRANT create any materialized view TO mviewadmin;
   GRANT alter any materialized view TO mviewadmin;


3. create db link at mview site

   -- run as system
   CREATE PUBLIC DATABASE LINK DBT92.US.ORACLE.COM USING 'DBT92';

   Note: not sure why this is necessary.

   -- run as mvowner
     create database link DBT92.US.ORACLE.COM connect to mviewproxy identified by mviewproxy
     using 'DBT92';

4. create mview logs at master site

-- run as mviewproxy
create materialized view log on mstowner.big_table tablespace mviewlog;


Note:  1. mview logs residing in their own tablespace, i.e.
    create tablespace mviewlog datafile '/u03/oracle/oradata/DBT92/mviewlog01.dbf' size 50M;
       2. mviewproxy does not have the privs to alter/drop materialized view log

5. create mview  at mview site
  login as mvowner

-- run as mvowner
CREATE MATERIALIZED VIEW mvowner.t_mv REFRESH FAST AS SELECT * FROM mstowner.t@DBT92.US.ORACLE.COM;
CREATE MATERIALIZED VIEW mvowner.big_table_mv REFRESH FAST AS SELECT * FROM mstowner.big_table@DBT92.US.ORACLE.COM;

Note:
 Oracle will create three objects when executing the following command:
         CREATE MATERIALIZED VIEW mvowner.big_table_mv REFRESH FAST AS SELECT * FROM mstowner.big_table@DBT92.US.ORACLE.COM;

   OBJECT_NAME                    OBJECT_TYPE
   ------------------------------ -------------------
   BIG_TABLE_MV                   TABLE
   BIG_TABLE_PK                   INDEX
   BIG_TABLE_MV                   MATERIALIZED VIEW
    


6. create mview groups at mview site

-- create the refresh group for the mview to ensure transactional
-- consistency when refreshing nore than one mview in the group.

-- run as mviewadmin on the mview side
 
     begin
              dbms_refresh.make(
          name => 'RG_BIG_TABLE',
          list => 'mvowner.big_table_mv',
          next_date => sysdate,
          interval => 'sysdate + 1/24',
          implicit_destroy => true,
          lax => true);
     end;
     /                          


7. Some basic operations

  (1) Refresh the complete group
          execute dbms_refresh.refresh('RG_BIG_TABLE');
  (2) Refresh a single snapshot
          execute dbms_snapshot.refresh('mvowner.big_table_mv');


  (3) Check materialized veiw refresh status


set linesize 120
set pagesize 100
col owner format a20
col table_name format a20
col name format a20
col master_owner format a20
col master_link format a20
col next format a20

select owner
       ,name
--     ,table_name
--     ,master_owner
--     ,master
--     ,master_link
       ,to_char(last_refresh, 'YYYY-MON-DD HH24:MI:SS') lst_rfrsh
       ,next
       ,status
  from dba_snapshots
/
 

 (4)  views:
     dba_snapshots
     dba_mviews

Relocate all data files by re-creating controlfile

Today I installed Oracle 9.2.0.4 software on CentOS4.7. During the installation, when starting the runInstaller, I encounter the following error:

Error occurred during initialization of VM
Unable to load native library: /tmp/OraInstall2003-10-25_03-14-57PM/jre/lib/i386/libjava.so:
symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference

By google, I found the following info:

"To resolve the __libc_wait symbol issue, download the p3006854_9204 patch p3006854_9204_LINUX.zip from http://metalink.oracle.com. See bug 3006854 for more information."

So I downloaded and applied that patch. The installation succeeded.

Alter creating a 9i database using DBCA, I found that all files are located at '/u03/oracle/oradata/DBT92/DBT92'

for example:

'/u03/oracle/oradata/DBT92/DBT92/system01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/undotbs01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/cwmlite01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/drsys01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/example01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/indx01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/odm01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/tools01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/users01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/xdb01.dbf'

I decided to relocate them to /u03/oracle/oradata/DBT92/.

The following steps were taken to achieve this goal:

1. create pfile from spfile
edit pfile, modify control file path by removing one 'DBT92'

2. Backup control file to trace, edit the trace file, name it as cr_ctrl.sql.
---- contents of cr_ctrl.sql -----

CREATE CONTROLFILE DATABASE "DBT92" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/u03/oracle/oradata/DBT92/redo01.log' SIZE 100M,
GROUP 2 '/u03/oracle/oradata/DBT92/redo02.log' SIZE 100M,
GROUP 3 '/u03/oracle/oradata/DBT92/redo03.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/u03/oracle/oradata/DBT92/system01.dbf',
'/u03/oracle/oradata/DBT92/undotbs01.dbf',
'/u03/oracle/oradata/DBT92/cwmlite01.dbf',
'/u03/oracle/oradata/DBT92/drsys01.dbf',
'/u03/oracle/oradata/DBT92/example01.dbf',
'/u03/oracle/oradata/DBT92/indx01.dbf',
'/u03/oracle/oradata/DBT92/odm01.dbf',
'/u03/oracle/oradata/DBT92/tools01.dbf',
'/u03/oracle/oradata/DBT92/users01.dbf',
'/u03/oracle/oradata/DBT92/xdb01.dbf'
CHARACTER SET WE8ISO8859P1
;
--- end of cr_ctrl.sql --------------

2. shutdown database
move all data files, redo log file to /u03/oracle/oradata/DBT92/

3. startup nomount with the pfile

4. Execute cr_ctrl.sql

5. Issue: alter database open resetlogs

6. Add tempfile
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oracle/oradata/DBT92/temp01.dbf'
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

7. create spfile from pfile
bounce the db to use spfile

SSH tunneling

Our team is taking over a new application. This application is hosted on the servers that adopt SSH tunneling for remote access. This feature I have never experienced before.

Here is a good arcticle to get started: Securing Oracle Network Traffic by Roger Schrag

My simple test succeeded:

#####  -- create the tunnel

ssh2 -l username -L 9902: remote_host:1521 remote_host


##### --  test remote connection

C:\Documents and Settings\Yu>sqlplus system@DBCONNSTRG

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 6 13:25:20 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
xxxx


####  My tnsname.ora entry:  I use 9902 because 1521 is for my local database lisenter

DBCONNSTRG =
   (DESCRIPTION =
      (ADDRESS_LIST =  (ADDRESS = (PROTOCOL= TCP)
                                  (HOST = 127.0.0.1)
                                   (PORT = 9902)
                        ) )
      (CONNECT_DATA = (SID = DBSIDNAME)(SERVER = DEDICATED)))

[Ed. Sep 17 2009: to make SSH tunneling work, on the server side we may want to do:
in /etc/ssh/sshd_config change AllowTcpForwarding from no to yes then recycle sshd ]

Email attachment from Unix server

I found that I need to do this. Googled, found this document: Creating email with a text message and an attachment from standard UNIX.

I tested on my server, if only need the attachment, I can do:

$ uuencode pfile_sac.txt pfile_sac.txt | mailx -s "pfile_sac" myname@mycompany.com