budsus's notes: August 2007

Wednesday, August 22, 2007

Oracle for Beginner: A Simple Audit

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> alter system set audit_trail=db scope=spfile;
SQL> shutdown immediate;
SQL> startup
SQL> conn / as sysdba
SQL> select name, value from v$parameter where name like 'audit%';
SQL> @$ORACLE_HOME/rdbms/admin/cataudit.sql
SQL> audit connect by budsus whenever not successful;
SQL> conn budsus/sdasdasd
SQL> conn budsus/xhsjd
SQL> conn / as sysdba
SQL> desc aud$;
SQL> select * from aud$;

SQL> grant select, insert, update on hr.departments to budsus;
SQL> Audit Update, Delete, Insert On hr.departments By Access Whenever Successful;
SQL> conn budsus/xxx
SQL> insert into hr.departments values (... ... );
SQL> conn / as sysdba
SQL> col comment$text format a20
SQL> select statement, timestamp#, userid, comment$text from aud$ where userid='BUDSUS'
SQL> select * from select * from dba_common_audit_trail;
SQL> noaudit update, delete, insert on hr.departments;

Saturday, August 18, 2007

Oracle for Beginner: Knowing your Linux Kernel Parameter for Oracle

If you want to know about how to install Oracle 10g on your Linux, there are some interesting articles that may be can help you to inform step by step installation. Here are some of those:

Oracle-Base (this site is a famous one)
Dizwell Informatics (installing Oracle10g on Ubuntu)
OTN Oracle-on-Linux.

From those guides, before installation is started, there are some kernel parameters that have to be setup in /etc/sysctl.conf files. Here are from Oracle-Base:

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536

kernel.shm* are parameters that is used to configure shared memory in Linux. Shared memory is a system resource that is used for interprocess communication. With this technique, two or more processes share a single chunk of memory to communicate between them. Shared memory is allocated in segments.

shmall : define the maximum amount of shared memory that may be in use at any time on the system.
shmmax : define the maximum size of each shared memory segment (max. value is 4GB).
shmmni : define the maximum number of shared memory segments on the system.

Semaphore is an important mechanism for controlling access to resources. Oracle instance use semaphore to control access to shared memory. Here is the kernel parameter which is used to configure the semaphore:

# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128

semmsl: maximum number of semaphores per set.
semmns : total number of semaphores in the system.
semopm : maximum number of operations per semop call.
semmni : maximum number of semaphore sets.

fs.file-max is a file handle parameter that determines the maximum number of files that each process can open. Default value is 8192 and the maximum value is 65536.

For more information, please refer to puschtz and dba-oracle.com .

Monday, August 06, 2007

System 7 Simulation

If you want to look and try an old Mac Operating System, i.e. System 7, you can visit WebSE. This simulation is very good. They create it using Flash. Very Interactive. You have to try it.

My New Book

This month, my newest book has been released (in Bahasa Indonesia). In this book, I wrote about Oracle10g XE Database basic administration and talk about how to build an application to access Oracle10g XE Database in four computer languages, i.e. Java, PHP, Ruby on Rails and Oracle Application Express. In this book, I explained about how to build a program that access a BLOB data type in OracleXE database. For more detail, please visit beranda budsus.

Sunday, August 05, 2007

Oracle for Beginner: Create External Table

In Oracle 10g, there is a data pump module that handle almost moving data from database Oracle to another database or external storage. Except data pump, there is a oracle loader that can be used to create and access an external data file (csv) as a regular table. In this article we will try to create an external table with data pump and oracle loader. This is a very simple thing that you can do easily in Oracle 10g.

First, we will try to create external table with Oracle data pump module :

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> create directory mylab as 'your/data/path';

SQL> create table dept(department_name)
2  organization external
3  (
4    type ORACLE_DATAPUMP
5    DEFAULT DIRECTORY mylab
6    LOCATION ('ware.exp')
7  )
8  AS select distinct department_name from hr.departments;

SQL> select * from dept;

Next, in same manner, we can access an external csv as a regular table in Oracle database using Oracle Loader:

Create a csv file. For example we will create kota.dat with the following values:

Jakarta,DKI
Surabaya,Jawa Timur
Semarang,Jawa Tengah
Yogyakarta,DIY
Malang,Jawa Timur
Bandung,Jawa Barat

login to your database as sysdba, create a directory object as a map for your data directory, and create an external table:

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> create directory mydata as 'your/data/path';

SQL> create table kota(kota varchar(50), propinsi varchar(50))
 2  organization external
 3  (type oracle_loader default directory mydata
 4   access parameters
 5  ( records delimited by newline
 6    badfile mydata:'kota%a_%p.bad'
 7    logfile mydata:'kota%a_%p.log'
 8    fields terminated by ','
 9    missing field values are null
10    (kota, propinsi)
11  )
12  location ('kota.dat') );

SQL> select * from kota;

I hope you can find a new experience with Oracle Database.

Fixing vmware-config on FC7

I just want to share about vmware-config (vmware player) error fixing when I tried to run it on my FC7 with kernel 2.6.22.1-41. This discussion is very helpful for me. I just want to rewrite again in my blog, so if I forget about this, I can just visit this my note.