Friday, November 16, 2012

Analyse log files in the pseudo-standard NCSA format

Quick awk script to analyse log files in the pseudo-standard NCSA format to show the number of times each page is hit and what HTTP Status Code is returned for each of the pages
awk '{
splitter=index($7,"=")-1;
if (splitter==-1)
  splitter=length($7);
url=substr($7,1,splitter) "\t" $9;
urls[url]++;
}
END {
for (url in urls)
  print url,"\t",urls[url];
}'

Monday, January 9, 2012

Creating a self-signed certificate using Oracle orapki

In our development environment, when configuring HTTPS for the Oracle XML DB Protocol Server I had to generate a self-signed certificate. All the examples I had seen relied on the owm utility which is only useful in Windows or a X11 environment. To generate a self-signed certificate from the command line execute the following commands:

# Create a new wallet in $ORACLE_BASE/oradata/DEV/wallets/
orapki wallet create -wallet $ORACLE_BASE/oradata/DEV/wallets/ -pwd oracle01 -auto_login

# Add the self-signed certificate to the new wallet
orapki wallet add -wallet $ORACLE_BASE/oradata/DEV/wallets/ -dn "CN=,OU=,O=My Organsisation,L=London,ST=Greater London,C=GB" \
-keysize 1024 -self_signed -validity -pwd oracle01

Friday, October 14, 2011

Adding Software Oracle Linux 5

I downloaded the one of the Oracle Developer Days VM and was trying to install subversion via the Add/Remove Software dialog (which is basically a GUI for yum). It turns out the VM you download points to Oracles internal yum servers. To fix this follow these steps (as root, the default password is oracle):
# cd /etc/yum.repos.d
# mv ULN-Base.repo to ULN-Base.repo.old
# wget http://public-yum.oracle.com/public-yum-el5.repo

Open the public-yum-el5.repo file in a text-editor and change the enabled flag for the first repository to enabled=1

Thursday, August 4, 2011

Foreign Keys with missing Indexes

The following script will show any Foreign Keys that do not have a corresponding Index:

select fk.owner, fk.table_name, fk.constraint_name, fkc.column_name
from all_constraints fk
, all_cons_columns fkc
where fk.owner = :owner
and fk.constraint_type = 'R'
and not exists ( select null
from all_indexes ind
where ind.index_name = fk.constraint_name
and ind.owner = fk.owner )
and fkc.owner = fk.owner
and fkc.table_name = fk.table_name
and fkc.constraint_name = fk.constraint_name
and not exists ( select null
from all_constraints pk
, all_cons_columns pkc
where pk.constraint_type = 'P'
and pk.owner = fk.owner
and pk.table_name = fk.table_name
and pkc.owner = pk.owner
and pkc.table_name = pk.table_name
and pkc.constraint_name = pk.constraint_name
and fkc.column_name = pkc.column_name )
order by owner, table_name, constraint_name;

Thursday, June 16, 2011

CTAS (Create Table As Select)and NOT NULL Constraints

Stumbled across YAOF (Yet Another Oracle Feature) today; When performing a CTAS (Create Table As Select) NOT NULL Constraints are usually created, for example:
CREATE TABLE ctas_test
( id NUMBER NOT NULL
, name VARCHAR2(30) NULL );

CREATE TABLE ctas_test_copy AS
SELECT * FROM ctas_test;

SELECT table_name, column_name, nullable
FROM all_tab_columns
WHERE table_name LIKE 'CTAS_TEST%'
ORDER BY table_name, column_name;

As you can see the Not Null Constraints have been successfully replicated to the CTAS_TEST_COPY table. However if I now execute:

CREATE TABLE ctas_test_minus AS
SELECT * FROM ctas_test
MINUS
SELECT * FROM ctas_test;

SELECT table_name, column_name, nullable
FROM all_tab_columns
WHERE table_name LIKE 'CTAS_TEST%'
ORDER BY table_name, column_name;

The Not Null Constraints are missing from the CTAS_TEST_MINUS table.

Thursday, September 30, 2010

Bulk Data Loading with Hibernate

The most efficient way of loading large amounts of data into Hibernate is using a StatelessSession (provided of course you don't want to keep the objects being loaded in the Persistent Context). The StatelessSession essentially works like a plain JDBC connection with the added bonus of being able to use your mapped persistent classes. You should be able to copy/paste the following into your code:

public void bulkCreate(Collection<?> entities) {
// Open a Stateless Session (this doesn't have a persistence context cache and
// doesn't interact with any other second-level or query cache). Everything
// executed with a Stateless session results in an immediate SQL operation.
StatelessSession session = HibernateUtil.getSessionFactory().openStatelessSession();
session.beginTransaction();
Iterator<?> it = entities.iterator();
while (it.hasNext()) {
session.insert(it.next());
}
session.getTransaction().commit();
session.close();
}

Wednesday, September 29, 2010

Confguring Java mail with JBoss AS 5 and gmail

I've been reading the excellent book: JBoss AS 5 Development and the Example in Chapter 4 creates a Mailer EJB, however the book gives no indication on how to configure Java Mail in JBoss, so I thought I'd share.

  1. Navigate to:

  2. $JBOSS_HOME/server/default/deploy

  3. Open the file: mail-service.xml in your favorite text editor
  4. Edit it as follows:


  5. <server>

    <mbean code="org.jboss.mail.MailService" name="jboss:service=Mail">
    <attribute name="JNDIName">java:/Mail</attribute>
    <attribute name="User">${username}@gmail.com</attribute>
    <attribute name="Password">${password}</attribute>
    <attribute name="Configuration">
    <!-- A test configuration -->
    <configuration>
    <!-- Change to your mail server prototocol -->
    <property name="mail.store.protocol" value="pop3" />
    <property name="mail.transport.protocol" value="smtp" />

    <!-- Change to the user who will receive mail -->
    <property name="mail.user" value="${username}@gmail.com" />

    <!-- Change to the mail server -->
    <property name="mail.pop3.host" value="pop.gmail.com" />

    <!-- Change to the SMTP gateway server -->
    <property name="mail.smtp.host" value="smtp.gmail.com" />
    <property name="mail.smtp.auth" value="true" />
    <property name="mail.smtp.user" value="${username}@gmail.com" />
    <property name="mail.smtp.password" value="${password}" />
    <property name="mail.smtp.ssl.enable" value="true" />
    <property name="mail.smtp.starttls.enable" value="true" />
    <property name="mail.smtp.socketFactory.class"
    value="javax.net.ssl.SSLSocketFactory" />

    <!-- The mail server port -->
    <property name="mail.smtp.port" value="465" />

    <!-- Change the default address mail will be from -->
    <property name="mail.from" value="${username}@gmail.com" />

    <!-- Enable debugging output from the javamail classes -->
    <property name="mail.debug" value="false" />
    </configuration>
    </attribute>
    <depends>jboss:service=Naming</depends>
    </mbean>
    </server>

NOTE: If you are using Google Apps, simply change the gmail.com extension to your-domain.com.