Girish's Technical Blog

Query to find unix process id for the session

2010-01-12T01:57:00.000-08:00

The query is below. To find the process id from any normal user, below grant's are required.
conn / as sysdba
grant select on v_$session to scott;
grant select on v_$process to scott;
grant select on v_$mystat to scott;

conn scott/tiger
SELECT p.spid
FROM v$session s, v$process p, (select sid from v$mystat where rownum=1) m
WHERE s.paddr = p.addr
and s.sid = m.sid;

The first "computer bug" !

2009-09-03T22:22:00.000-07:00

The First "Computer Bug" Moth found trapped between points at Relay # 70, Panel F, of the Mark II Aiken Relay Calculator while it was being tested at Harvard University, 9 September 1945.

http://en.wikipedia.org/wiki/File:H96566k.jpg

What does "search" mean to google ?

2009-08-24T01:40:00.000-07:00

"Bing".. yes.. look at below image..

interesting...

Updating partition key column in oracle

2009-06-25T22:17:00.000-07:00

When you try to update the partitioned key column, in a partitioned table, oracle throws error 14402.

Ex:
Table "test"is partitioned by date_id.

update test set date_id = sysdate;

ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

Solution:

alter table test enable row movement;
update test set date_id = sysdate;
alter table test disable movement;

After the above sql executed, the triggers associated with table "test" will be in invalid state. You must recompile them.

Check the invalid objects using..
select object_name, object_type, status from user_objects where status='INVALID';
Below sql gets the trigger source..
set long 9999
select trigger_name, status, trigger_body from user_triggers where table_name='TEST';

Choosing desktop for vnc in linux

2009-03-03T03:18:00.000-08:00

twm is the default window manager. This can be changed by modifying the $HOME/.vnc/xstartup file.
You will see below line in xstartup
twm &

For KDE, change above line to
startkde &

For Gnome, change to
exec gnome-session &

For choosing fvwm,
/opt/fvwm/bin/fvwm2 &

Then kill and start the vncserver/s again.

PHP code to insert unicode data in excel to mysql database

2008-07-20T08:03:00.000-07:00

I struggled to get this working.. So sharing the working version of code.

I had a excel file Book1.xls with unicode (samskrit words) data in two columns. My requirement was to insert the data into a mysql table created using below SQL
CREATE TABLE utable
(
word1 varchar(64),
word2 varchar(64)
) CHARACTER SET utf8
COLLATE utf8_general_ci ;

Steps:

1. Get Book1.txt -> Save excel file as "Unicode Text". Saving as csv, did not save unicode columns correctly.
2. Enable/install mbstring extension in php. See if you have php_mbstring.dll in php/ext directory. It was there by default for me. To enable, just search for "extension=php_mbstring.dll" in php.ini and uncomment the line.
3. Here is the php code which worked for me :)

<!DOCTYPE html PUBLIC "-//W3C//DTD Xhtml 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head></head>
<body>
<h2>Hello</h2>
<?php
header ('Content-type: text/html; charset=utf-8');
$db = mysql_connect( "localhost", "root", "c1test" );
mysql_select_db( "sblib", $db);
mysql_query( "SET NAMES 'UTF8'", $db );
mysql_query( "SET CHARACTER SET 'utf8'", $db );
mysql_query( "SET collation_connection = 'utf8_general_ci'", $db );

$data = file_get_contents( "Book1.txt");
$data = mb_convert_encoding($data, "UTF-8", "UTF-16LE");
$lines = mb_split("\n", $data);

foreach ($lines as $ud)
{
if ( $ud )
{
$arr = mb_split(" ", $ud);
//echo "$arr[0]==$arr[1]";
$result = mysql_query("INSERT INTO utable(word1, word2) values ('$arr[0]', '$arr[1]')");
echo "<br>";
}
}

$result = mysql_query("SELECT word1, word2 FROM utable");
while($row = mysql_fetch_row($result))
{
echo "Name : $row[0]; $row[1]<br>";
}
?>

References:
http://www.joelonsoftware.com/articles/Unicode.html -- Must read.
http://www.nicknettleton.com/zine/php/php-utf-8-cheatsheet
http://us.php.net/manual/en/ref.mbstring.php#50298

Bill Gates logs out of Microsoft and logs into philanthropy

2008-06-29T22:41:00.000-07:00

On 27th of Jun 2008, Bill gates bids adieu to the company he built for last 33 years, Microsoft. A big loss to technology sector, but a bigger gain to philanthropy.
Bill Gates owns worlds biggest, 100 billion dollar philanthropic organization, Bill and Melinda Gates Foundation, where he will work full time from now..

Google docs is offline now!

2008-06-20T00:01:00.000-07:00

Now we can view and edit documents offline, without an active internet connection. This is really cool. You need to have google gears installed for this and should not use it on shared computers.

http://docs.google.com

SQL functions in oracle

2008-06-18T04:29:00.000-07:00

Below is list of all functions built into oracle database in release 11.1

Numeric Functions:
ABS, ACOS, ASIN, ATAN, ATAN2, BITAND, CEIL, COS, COSH, EXP, FLOOR, LN, LOG, MOD, NANVL, POWER, REMAINDER, ROUND (number), SIGN, SIN, SINH, SQRT, TAN, TANH, TRUNC (number), WIDTH_BUCKET

Character Functions:
ASCII, CHR, CONCAT, INITCAP, INSTR, LENGTH, LOWER, LPAD, LTRIM, NLS_INITCAP, NLS_LOWER, NLSSORT, NLS_UPPER, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR, REPLACE, RPAD, RTRIM, SOUNDEX, SUBSTR, TRANSLATE, TREAT, TRIM, UPPER

NLS Character Functions:
NLS_CHARSET_DECL_LEN, NLS_CHARSET_ID, NLS_CHARSET_NAME

Datetime Functions
ADD_MONTHS, CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, EXTRACT (datetime), FROM_TZ, LAST_DAY, LOCALTIMESTAMP, MONTHS_BETWEEN, NEW_TIME, NEXT_DAY, NUMTODSINTERVAL, NUMTOYMINTERVAL, ROUND (date), SESSIONTIMEZONE, SYS_EXTRACT_UTC, SYSDATE, SYSTIMESTAMP, TO_CHAR (datetime), TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_DSINTERVAL, TO_YMINTERVAL, TRUNC (date), TZ_OFFSET

General Comparison Functions
GREATEST, LEAST

Conversion Functions
ASCIISTR, BIN_TO_NUM, CAST, CHARTOROWID, COMPOSE, CONVERT, DECOMPOSE, HEXTORAW, NUMTODSINTERVAL, NUMTOYMINTERVAL, RAWTOHEX, RAWTONHEX, ROWIDTOCHAR, ROWIDTONCHAR, SCN_TO_TIMESTAMP, TIMESTAMP_TO_SCN, TO_BINARY_DOUBLE, TO_BINARY_FLOAT, TO_CHAR (character), TO_CHAR (datetime), TO_CHAR (number), TO_CLOB, TO_DATE, TO_DSINTERVAL, TO_LOB, TO_MULTI_BYTE, TO_NCHAR (character), TO_NCHAR (datetime), TO_NCHAR (number), TO_NCLOB, TO_NUMBER, TO_DSINTERVAL, TO_SINGLE_BYTE, TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_YMINTERVAL, TO_YMINTERVAL, TRANSLATE ... USING, UNISTR

LOB Functions
BFILENAME, EMPTY_BLOB, EMPTY_CLOB

Collection Functions
CARDINALITY, COLLECT, POWERMULTISET, POWERMULTISET_BY_CARDINALITY, SET

Hierarchical Function
SYS_CONNECT_BY_PATH

Data Mining Functions
CLUSTER_ID, CLUSTER_PROBABILITY, CLUSTER_SET, FEATURE_ID, FEATURE_SET, FEATURE_VALUE, PREDICTION, PREDICTION_BOUNDS, PREDICTION_COST, PREDICTION_DETAILS, PREDICTION_PROBABILITY, PREDICTION_SET

XML Functions
APPENDCHILDXML, DELETEXML, DEPTH, EXTRACT (XML), EXISTSNODE, EXTRACTVALUE, INSERTCHILDXML, INSERTXMLBEFORE, PATH, SYS_DBURIGEN, SYS_XMLAGG, SYS_XMLGEN, UPDATEXML, XMLAGG, XMLCAST, XMLCDATA, XMLCOLATTVAL, XMLCOMMENT, XMLCONCAT, XMLDIFF, XMLELEMENT, XMLEXISTS, XMLFOREST, XMLPARSE, XMLPATCH, XMLPI, XMLQUERY, XMLROOT, XMLSEQUENCE, XMLSERIALIZE, XMLTABLE, XMLTRANSFORM

Encoding and Decoding Functions
DECODE, DUMP, ORA_HASH, VSIZE

NULL-Related Functions
COALESCE, LNNVL, NULLIF, NVL, NVL2

Environment and Identifier Functions
SYS_CONTEXT, SYS_GUID, SYS_TYPEID, UID, USER, USERENV

Aggregate Functions
AVG, COLLECT, CORR, CORR_*, COUNT, COVAR_POP, COVAR_SAMP, CUME_DIST, DENSE_RANK, FIRST, GROUP_ID, GROUPING, GROUPING_ID, LAST, MAX, MEDIAN, MIN, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, RANK, REGR_ (Linear Regression) Functions, STATS_BINOMIAL_TEST, STATS_CROSSTAB, STATS_F_TEST, STATS_KS_TEST, STATS_MODE, STATS_MW_TEST, STATS_ONE_WAY_ANOVA, STATS_T_TEST_*, STATS_WSR_TEST, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE

Analytic Functions
AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, REGR_ (Linear Regression) Functions, ROW_NUMBER, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE

Object Reference Functions
DEREF, MAKE_REF, REF, REFTOHEX, VALUE

Model Functions
CV, ITERATION_NUMBER, PRESENTNNV, PRESENTV, PREVIOUS

References:
Oracle® Database SQL Language Reference 11g Release 1 (11.1)

Index organized tables (IOT) in oracle

2008-06-18T03:01:00.000-07:00

Introduction:
In ordinary oracle tables data is stored as an unordered collection (or its heap organized). An index-organized table has a storage organization that is a variant of a primary B-tree. The data is stored in a B-tree index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the non key column values as well.

In an IOT table, changes to the table data, such as adding new rows, updating rows, or deleting rows, result only in updating the index. Because data rows are stored in the index, index-organized tables provide faster key-based access to table data for queries that involve exact match or range search or both.

Create statement for an IOT

Just add "ORGANIZATION INDEX" clause to normal create table to create an IOT.

Ex:
CREATE TABLE sample_iot(
token CHAR(20),
doc_id NUMBER,
token_frequency NUMBER,
token_offsets VARCHAR2(2000),
CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id)
)
ORGANIZATION INDEX ;

When to use IOT:

Index organized tables are well suited for OLTP applications, where the queries and DML's are heavily primary key based.

When not to use IOT:

IOT tables are not effective when
- There is heavy insert activity.
- The table uses secondary indexes.

Table compression in oracle

2008-05-28T01:34:00.000-07:00

Table compression in oracle database helps to reduce the amount of disk space used by database tables and improve query performance (except DML's). The feature is very useful when the data is mostly read-only.

The "COMPRESS" clause needs to be specified while creating tables to enable data compression.

Ex:
Below query creates a compressed table sale_data_history..

CREATE TABLE sales_data_history(
product_id NUMBER,
cust_id NUMBER,
sale_date DATE,
region VARCHAR2(20)
) COMPRESS;

We can also compress an existing table using ALTER TABLE..

ALTER TABLE sales_data_history MOVE COMPRESS;

The data compression can be specified at the partition level, if the table is partitioned.

CREATE TABLE sales_data_history
product_id NUMBER,
cust_id NUMBER,
sale_date DATE,
region VARCHAR2(20)
)COMPRESS
PARTITION BY LIST (region)
(
PARTITION northwest VALUES ('NORTHWEST'),
PARTITION southwest VALUES ('SOUTHWEST'),
PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS,
PARTITION southeast VALUES ('SOUTHEAST'),
PARTITION western VALUES ('WESTERN')
);

Alternatively, you can just compress only specific partitions using

ALTER TABLE sales_data_history
MOVE PARTITION northeast COMPRESS;

This option is quite useful when few partitions become read-only storing historical data.

Because data in compressed table is stored in fewer blocks, it results in less storage on disk and less memory usage in buffer cache. The queries on a compressed table will often complete more quickly, because they need to read fewer database blocks. But there is a overhead during data loading and executing DML's on a compressed table. So the usage of COMPRESS should be done after careful design.

Table partitioning in oracle

2008-05-20T03:40:00.000-07:00

Partitioning enables tables and indexes to be split into smaller, more manageable components and is a key requirement for any large database with high performance and high availability requirements.

Types

Range
Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition.

Ex:


CREATE TABLE salestable
(
 s_productid    NUMBER,
 s_saledate     DATE,
 s_custid       NUMBER,
 s_totalprice   NUMBER,
 s_region       VARCHAR2(20)
)
PARTITION BY RANGE(s_saledate)
(
PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY'))
);

Hash
Hash partitioning maps data to partitions based on a hashing algorithm that evenly distributes rows among partitions, giving partitions approximately the same size.

Ex:


CREATE TABLE salestable
(
 s_productid    NUMBER,
 s_saledate     DATE,
 s_custid       NUMBER,
 s_totalprice   NUMBER,
 s_region       VARCHAR2(20)
)
PARTITION BY HASH(s_region)
PARTITIONS 10;

List
List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values in the description for each partition.

Ex:


CREATE TABLE salestable
(
 s_productid    NUMBER,
 s_saledate     DATE,
 s_custid       NUMBER,
 s_totalprice   NUMBER,
 s_region       VARCHAR2(20)
)
PARTITION BY LIST(s_region)
(
PARTITION q1_northwest VALUES ('OR', 'WA'),
PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
PARTITION q1_southeast VALUES ('FL', 'GA'),
PARTITION q1_northcentral VALUES ('SD', 'WI'),
PARTITION q1_southcentral VALUES ('OK', 'TX')
);

Composite
Composite partitioning is a combination of two partitioning methods to further divide the data into sub-partitions. The table is initially partitioned by the first data distribution method and then each partition is sub-partitioned by the second data distribution method. The following composite partitions are available:
Range-Hash, Range-List, Range-Range, List-Range, List-List and List-Hash.

Interval
The "INTERVAL" clause of the "CREATE TABLE" statement establishes interval partitioning for the table. You must specify at least one range partition using the "PARTITION" clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition. This is a very useful 11g feature.

Ex:
The below query will automatically create a partition every month.

CREATE TABLE interval_sales
(
 prod_id        NUMBER(6),
 cust_id        NUMBER,
 time_id        DATE,
 channel_id     CHAR(1),
 promo_id       NUMBER(6),
 quantity_sold  NUMBER(3),
 amount_sold    NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION p_first VALUES LESS THAN ('01-JAN-2006');

REF Partitioning or Reference Partitioning
Oracle Database 11g allows to partition a table by leveraging an existing parent-child relationship. The partitioning strategy of the parent table is inherited to its child table without the necessity to store the parent's partitioning key columns in the child table.

Ex:
Lets create a table orders which is range-partitioned on order_date. The reference-partitioned child table order_items is created with four partitions, Q1_2005, Q2_2005, Q3_2005, and Q4_2005, where each partition contains the order_items rows corresponding to orders in the respective parent partition.


CREATE TABLE orders
(
 order_id           NUMBER(12),
 order_date         TIMESTAMP WITH LOCAL TIME ZONE,
 order_mode         VARCHAR2(8),
 customer_id        NUMBER(6),
 order_status       NUMBER(2),
 order_total        NUMBER(8,2),
 sales_rep_id       NUMBER(6),
 promotion_id       NUMBER(6),
 CONSTRAINT orders_pk PRIMARY KEY(order_id)
)
PARTITION BY RANGE(order_date)
(
 PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
 PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
 PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
 PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
);


CREATE TABLE order_items
(
 order_id           NUMBER(12) NOT NULL,
 line_item_id       NUMBER(3)  NOT NULL,
 product_id         NUMBER(6)  NOT NULL,
 unit_price         NUMBER(8,2),
 quantity           NUMBER(8),
 CONSTRAINT order_items_fk
 FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);

Virtual column based partitioning
Virtual columns, a new functionalityin Oracle Database 11g, allows the partitioning key to be defined by an expression, using one or more existing columns of a table, and storing the expression as metadata only.

Ex:
Lets create sales table partitioned by range-range using a virtual column for the subpartitioning key. The virtual column calculates the total value of a sale by multiplying amount_sold and quantity_sold.


CREATE TABLE sales
(
prod_id       NUMBER(6) NOT NULL,
cust_id       NUMBER NOT NULL,
time_id       DATE NOT NULL,
channel_id    CHAR(1) NOT NULL,
promo_id      NUMBER(6) NOT NULL,
quantity_sold NUMBER(3) NOT NULL,
amount_sold   NUMBER(10,2) NOT NULL,
total_amount AS (quantity_sold * amount_sold)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE(total_amount)
SUBPARTITION TEMPLATE
(
SUBPARTITION p_small VALUES LESS THAN (1000),
SUBPARTITION p_medium VALUES LESS THAN (5000),
SUBPARTITION p_large VALUES LESS THAN (10000),
SUBPARTITION p_extreme VALUES LESS THAN (MAXVALUE)
)
(PARTITION sales_before_2007 VALUES LESS THAN
   (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
)
ENABLE ROW MOVEMENT
PARALLEL NOLOGGING;

References:

Partitioning in Oracle Database 11g: An Oracle White Paper
Oracle® Database VLDB and Partitioning Guide: 11g Release 1 (11.1)

A forum post on htmldb/apex url

2008-01-23T22:53:00.000-08:00

http://forums.oracle.com/forums/thread.jspa?messageID=1013059

First AJAX page

2007-11-13T01:50:00.001-08:00

Today i tried my first AJAX page. The best place to start with AJAX is
http://developer.mozilla.org/en/docs/AJAX:Getting_Started

Just copied the script to a html file in my apache htdocs directory, and loaded the page in a browser. Thats it..

Starting on Web 2.0

2007-11-07T01:41:00.000-08:00

I just started reading web2.0 concepts.
Starting from the http://www.programmableweb.com/reference

I'll write down whatever knowledge i acquire on web2.0 here.

Free space available for a user

2007-09-25T22:29:00.000-07:00

Below query returns the free space available for the user in each tablespace.

SELECT tablespace_name, sum(bytes)/1048576 Megs
FROM user_free_space
GROUP BY tablespace_name

Query to find space usage of db objects

2007-09-25T03:18:00.000-07:00

Below query displays the space consumed by every object user owns in MB.

SELECT segment_name, sum(bytes)/1048576 Megs
FROM user_extents
WHERE segment_name like '%'
GROUP BY segment_name
/

If the table/index is partitioned, the the space consumed by each partition can be queried using below query.

SELECT segment_name, partition_name, sum(bytes)/1048576 Megs
FROM user_extents
where segment_name='TABLE_NAME'
GROUP BY segment_name, partition_name
order by 3
/

More information can be found here.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/schema007.htm