Writing own messages into the alert log

If you want to write your own message into alert.log then you can use undocumented procedure kdswrt in dbms_system package.

begin
sys.dbms_system.ksdwrt(2, ‘ORA-23930: My Own error’ );
end;
/

Convert Non-OMF to OMF datafiles

OMF datafile become these days and we had many requirements that we need to convert existing non-omf databases omf databases to support various reasons. This article explains the detail of converting non-omf to omf database.

In this article, I am using a database called FXRPT whose databases are under /oradata/FXRPT. But for conversion purpose, I changed all database files’ patch to /oradata/fxrpt directory which requires to recreate controlfile.

1. Set the control_files, db_create_file_dest and log_dests parameter

*.control_files=’/oradata/FXRPT/control01.ctl’,'/oradata/FXRPT/control02.ctl’,'/oradata/FXRPT/control03.ctl’ *.db_create_file_dest=’/oradata’ *.db_create_online_log_dest_1=’/oradata’ *.db_create_online_log_dest_2=’/oradata’

2. Restore control files to the destination by using RMAN and mount it

$ sqlplus “/ as sysdba” SQL> startup nomount ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size 2084296 bytes Variable Size 486539832 bytes Database Buffers 1107296256 bytes Redo Buffers 14692352 bytes $ rman target / RMAN> restore controlfile from ‘/oradata/fxrpt/control01.ctl’; Starting restore at 19-FEB-10 using channel ORA_DISK_1 channel ORA_DISK_1: copied control file copy output filename=/oradata/FXRPT/control01.ctl output filename=/oradata/FXRPT/control02.ctl output filename=/oradata/FXRPT/control03.ctl Finished restore at 19-FEB-10 RMAN> alter database mount; database mounted released channel: ORA_DISK_1

3. Backup datafiles using RMAN copy command from old directory to the new directory

RMAN> backup as copy database format ‘/oradata/FXPRT/%U’;

4. Switch database to copy

RMAN> switch database to copy;

5. Open the database and add tempfile

RMAN> alter database open; $ sqlplus “/ as sysdba” SQL> alter tablespace temp add tempfile size 500m autoextend on next 100m maxsize unlimited;

Multi-table insert statement

A multi-table insert statement allows you to insert the results of a single select statement into multiple tables. Parallel insert statements can take advantage of parallelism and Oracle’s direct-load mechanisms.

Unconditional insert Statements
The unconditional INSERT inserts the rows into all of the tables without any conditions. All inserts statements will execute simultaneously.

INSERT ALL
INTO product_totals VALUES(product_id, today, total)
INTO product_history VALUES(product_id, today, quantity)
SELECT product_id, trunc(sysdate) today,
sum(unit_price*quantity) total, sum(quantity) quantity
FROM orders, order_items
WHERE orders.order_id = order_items.order_id
AND trunc(order_date) = trunc(sysdate)
GROUP BY product_id;

Pivoting insert statements
The pivoting INSERT inserts into the same table several times pivoting from a non-normalized form to a normalized form.

INSERT ALL
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date, sales_sun)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
INTO sales (product_id, customer_id, week, amount)
VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_summary;

Conditional insert statements
The conditional ALL INSERT evaluates each of the WHEN clauses to determine if the row is inserted into each table. All WHEN clauses are evaluated, so some source rows may be inserted into more than one target while others may not be inserted at all. An optional ELSE clause can also be used.

INSERT ALL
WHEN order_mode=’online’
THEN INTO web_orders
VALUES (order_id, order_date, order_total)
WHEN coupon is not null
THEN INTO coupon_sales
VALUES (order_id, order_date, order_total)
SELECT order_id, order_date, order_total, coupon, order_mode
FROM orders;

Insert first
The conditional FIRST INSERT evaluates each WHEN clause in the order in which it appears in the statement and only executes the INTO clause for the first one which is TRUE. An optional ELSE clause can also be used.

INSERT FIRST
WHEN order_total > 10000 THEN
INTO priority_handling VALUES(order_id)
WHEN order_total > 5000 THEN
INTO special_handling VALUES(order_id)
WHEN order_total > 3000 THEN
INTO privilege_handling VALUES(order_id)
ELSE
INTO regular_handling VALUES(order_id)
SELECT order_id, order_total
FROM orders;

External Table

External table feature was introduced in Oracle9i version. I had tested long time before but didn’t have chance to summarize it.
I have chance to review this feature thesedays and now I decided to put some trace here so that I may be able to revisit this note later.

I am not write the details of the feature in this page since this page is just a note for my memory refresh and nothing to do with any theoretical documentation.

Objective
I will create two external tables called PASSWD and GROUP. These tables will be mapped to /tmp/passwd and /tmp/group file which I copied from /etc directory.

1. Create Directory
SQL> create directory ETC as ‘/tmp’;
SQL> grant read,write on directory ETC to scott; — If the directory is created by other user.

2. Create Tables
SQL> create table passwd_ext — Creating PASSWD_EXT table.
( userid varchar2(30),
password varchar2(10),
usernum number,
groupnum number,
description varchar2(50),
home varchar2(100),
shell varchar2(100))
organization external
( type oracle_loader
default directory etc
access parameters
( records delimited by newline
fields terminated by ‘:’
missing field values are null )
location ( ‘passwd’ ))
/

SQL> create table group_ext — Create GROUP_EXT table
( groupid varchar2(30),
password varchar2(10),
groupnum number,
member varchar2(100))
organization external
( type oracle_loader
default directory etc
access parameters
( records delimited by newline
fields terminated by ‘:’
missing field values are null )
location ( ‘group’ ))
/

3. Query/Join tables
Here I am joining two tables and produces output which show groupname,username and description order by groupname.
SQL> select g.groupid,p.userid,p.description from passwd_ext p, group_ext g
where p.groupnum = g.groupnum
order by groupid
/
GROUPID USERID DESCRIPTION
—————————— —————————— ————————————————–
adm adm adm
bin bin bin
daemon daemon daemon
dba racle Oracle10g
dbus dbus System message bus
ftp ftp FTP User
gopher gopher gopher
haldaemon haldaemon HAL daemon
hpsmh hpsmh
lp lp lp
mail mail mail
…

Conclusion
The external table is very powerful feature that enables you to map your external files to the database and even allow you to manipulate the data by joining either with external or internal table. This will make your job much flexible especially when you deal with ETL type jobs.