Installing Oracle 11gR2 - Linux x86 - owb/external/oc4j_applications/applications/WFMLRSVCApp.ear not found

Installing Oracle 11gR2 on Linux x86.  It is deemed a simple task as long as we unlearn the way we install previous versions of Oracle and pick this up.

Both Disk1 and Disk2 should be unzipped in to same location .. what I mean is.. unzipping both will result in single directory named database. Launch installer from this location.

Otherwise, resolving kernel parameters and package dependencies are rather simple.

MySQL has the real syntax sugar

My first experience with MySQL is so wonderful. I never imagined that profiling queries can be this easy. Oracle profiling has a learning curve. SQL server is also somewhat simple and straightforward. Looking forward to see how it is implemented in PostgreSQL.

A little odd thing I see is the choice of storage engines. Otherwise, MySQL journey has been very smooth. One thing is sure, all relational databases architecture is kind of similar. Learn internals of one rdbms.. and believe me.. it is really  fun and adapt to work on others.

Calculating size of a table in Oracle

Before calculating the size of a table, one should understand how a table is stored.

A table is physically stored in a datafile on disk. Oracle reserves a defined space before writing the data. The data file is organized in to chunks called segments, which further contains chunks named extents and each extent is organized in to most atomic units called the blocks. The size of the block is determined by the Oracle init parameter named "db_block_size".


The data dictionary stores the details of filled and free blocks or extents. (You should do a step called "analyze" to gather the table statistics)
something like analyze table MYTAB compute statistics;
This might take long time depending on your tablesize.

And now you are ready to query the data dictionary tables to find the size occupied by the table.

select sum(blocks) from user_extents
where segment_name = 'TABLE_NAME'
and segment_type = 'TABLE';

or 

select blocks,EMPTY_BLOCKS,avg_space,avg_row_len,NUM_FREELIST_BLOCKS  from user_tables
where table_name='TABLE_NAME';

My personal choice is the first query.

Final steps ...
1. Know the block size by running "show parameter db_block_size" in sqlplus.
2. Multiply the block size by sum(blocks), and you get the table size.

SQL Server 2008 for the Oracle DBA

For those who have lived long enough working Oracle, and if you wish to learn SQL server 2008 by mapping feature set to Oracle database, then this is the video for you.

http://technet.microsoft.com/en-us/sqlserver/dd548020.aspx

Quick Summary from the link:
This 15 modules, level 300 course provides students with the knowledge and skills to capitalize on their skills and experience as an Oracle DBA to manage a Microsoft SQL Server 2008 system. This workshop provides a quick start for the Oracle DBA to map, compare, and contrast the realm of Oracle database management to SQL Server database management.

Feynman Algorithm - Interesting to read

Source: http://www.c2.com/cgi/wiki?FeynmanAlgorithm

The Feynman Algorithm:

1. Write down the problem.
2. Think real hard.
3. Write down the solution.

The Feynman algorithm was facetiously suggested by Murray Gell-Mann, a colleague of Feynman, in a New York Times interview.

Working with DML in parallel (Oracle 11gR2 feature in PLSQL)

http://www.oracle.com/technology/oramag/oracle/10-may/o30plsql.html

This link has comprehensive coverage about this new feature introduced in Oracle 11g R2.

New learning in PLSQL - NO_DATA_NEEDED exception

I have many times come across the NO_DATA_FOUND exception. Thanks for Asktom site for pointing me to NO_DATA_NEEDED exception.  There are always enough things to learn as long as mind is keen to observe around.

H2 Database Engine

After a few days of working with databases, I came across an in-memory DB named H2 database.

CRUISE is an wonderful continuous integration build enabler and this product uses H2 database. Hope to dig this tool a bit.

More details about this can be found at http://www.h2database.com/html/main.html

Reading data from SQL Server from MS Access

Wow ! I have the way to pull data from SQL server .. sitting in the MS Access application. Thanks to the ODBC linking views available.

Hierarchical queries (or recursive queries) in SQL Server 2005/2008

The question is ... how do you run hierarchical queries in SQL server ? I was trying to find something similar to CONNECT BY..PRIOR clause in Oracle. We stepped in to CTE (Common Table Expression). Awesome feature !

* Table has two columns... emp_id, mgr_id (LEVEL in the query is a computed column)

WITH DirectReports (MGR_ID, EMP_ID, Level)
AS
(
-- Anchor member definition
    SELECT mgr_id, emp_id, 0 AS Level
FROM test_emp_mgr    
    WHERE mgr_id is null


    UNION ALL
-- Recursive member definition
    SELECT e.mgr_id, e.emp_id,Level + 1
    FROM test_emp_mgr AS e
    
    INNER JOIN DirectReports AS d
        ON e.mgr_id = d.emp_id
)
-- Statement that executes the CTE
SELECT *
FROM DirectReports;

How to know the sessions active in your Oracle database ?

The following query should give you the summary of users and the number of sessions held by them.

SQL> select schemaname, session_count from 
(select distinct schemaname, count(sid) as session_count from v$session group by schemaname) 
order by session_count desc;


-- To know the total number of sessions in progress...
SQL> select count(*) from v$session;

97 Things every programmer should know

http://programmer.97things.oreilly.com/wiki/index.php/97_Things_Every_Programmer_Should_Know

Making a better place to dwell.. Help software survive

http://programmer.97things.oreilly.com/wiki/index.php/The_Boy_Scout_Rule

CouchDB - More Updates ...

I was lucky to attend a session by my colleague who invested a lot of time in getting a prototype of couchDB to work. Some of my observations...

• Dont map it to features of relational db
• Doesnt enforce user security/isolation levels
• It is document-based database
• Stores JSON format documents
• For better read speeds, we need VIEWS to be pre-created
• Event consistent model
• Follows Brewer's CAP theorem
• Equivalent of WHERE clause is Map-Reduce way of selecting & aggregating contents.
• Definitely not an enterprise class database
• Excellent for applications that can model and consume document databases (eg. LDAP structure, Orkut like sites, user profiles, patient profiles, employee details database etc)

I love to write code in Ruby :-)