Tech-n-freak

Finding Schema Size in a Database

Often we need to find out the size occu­pied by each and every schema on a data­base, majorly for clean­ing up pur­pose and disk size lim­i­ta­tions. Run the fol­low­ing script on your db and find out the space occu­pied by each user

SELECT owner, Sum(bytes)/1024/1024 AS total_size_mb

FROM dba_segments

GROUP BY owner

ORDER BY Sum(bytes)/1024/1024 desc;

Configuring VNCServer

Set­ting up the X set­tings for VNC

By default on con­nect­ing to a vnc­server instance, a user can not see a ses­sion of X. He would see a plain grey desk­top as by default a new ses­sion of X is not started. To fix this issue, we need to edit the xstartup script in the .vnc folder of user’s home directory.

Open the script in vi and uncom­ment the two lines for X ses­sion initialization.

[ansriv@mymachine ~/.vnc]$ vi xstartup

#!/bin/sh

# Uncom­ment the fol­low­ing two lines for nor­mal desktop:

unset SESSION_MANAGER

exec /etc/X11/xinit/xinitrc

[ –x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup

[ –r $HOME/.Xresources ] && xrdb $HOME/.Xresources

xset­root –solid grey

vnc­config –iconic &

xterm –geom­e­try 80x24+10+10 –ls –title “$VNCDESKTOP Desk­top” &twm &

#!/bin/sh
# Uncom­ment the fol­low­ing two lines for nor­mal desktop: unset SESSION_MANAGER exec /etc/X11/xinit/xinitrc
[ –x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup[ –r $HOME/.Xresources ] && xrdb $HOME/.Xresourcesxsetroot –solid greyvnc­config –iconic &xterm –geom­e­try 80x24+10+10 –ls –title “$VNCDESKTOP Desk­top” &twm &

Start­ing a VNC Server

You can start the vnc­server now. To add your res­o­lu­tion set­tings, you can pass an argu­ment –geometry.

[ansriv@mymachine ~/.vnc]$ vnc­server –geom­e­try 1680x1050 –depth 16

The client machine for me has a res­o­lu­tion of 1680x1050. The argu­ment for pro­vid­ing depth as 16 makes the con­nec­tion a bit faster since lesser the infor­ma­tion that needs to be sent the more respon­sive the ses­sion will be.

Chang­ing the passwd

To add secu­rity we need to pro­vide a pass­word at the time of con­nec­tion ini­tial­iza­tion. Open the ter­mi­nal and type

$vnc­passwd

Pass­word:

Ver­ify:

$vnc­server –kill :1

Increasing traffic to your blog

Webreference.com has given some really good tips to increase the traf­fic to your blog here. Basi­cally, you have to sub­mit your blogs at

1. Ping­ing Ser­vices like Yahoo’s blogs, Google Blog Search

2. Blog search engines like Tech­no­rati.

3. Ser­vices Pro­vid­ing web feed man­age­ment ser­vices like Feed­Burner

4. Book­mark your blog at Book­marks man­age­ment sites like del.icio.us

5. Com­ment on other blogs.

6. Use keywords.

These are some sim­ple steps that any­one with basic knowl­edge of brows­ing can do. For advanced SEO tips, start here.

Any­way, while claim­ing your blog on Tech­no­rati, you will be asked to pub­lish your Ver­i­fi­ca­tion code. My ver­i­fi­ca­tion code is ZD682VKGJZDN.

Removing ^M Character in Linux

The end of the line char­ac­ter is treated in a dif­fer­ent way in Linux as com­pared to Win­dows. The new line char­ac­ter in Linux is rep­re­sented by ‘\n’. Whereas in win­dows end of line is a car­riage return fol­lowed by a line feed/newline(\r\n).

When you open a file in Linux that was cre­ated in win­dows, a CTRL-M char­ac­ter is vis­i­ble at the end of line as ^M in vi.

There are two ways of replac­ing this ^M character -

1. Use VI.

Open the file in vi edi­tor and use the fol­low­ing command:

:%s/^V^M//g

^V rep­re­sents CTRL-V and ^M rep­re­sents CTRL-M. When you type this, it will look like

:%s/^M//g

In, Linux you can escape a con­trol char­ac­ter by pre­ced­ing it with a CTRL-V. ‘%s’ is a basic search and replace com­mand in vi. It replaces the reg­u­lar expres­sion between the first and sec­ond occur­ance of ‘/’ with the text pro­vided between the sec­ond and third occur­ance of ‘/’. The com­mand ‘g’ tells vi to search and replace all occur­ance of the reg­u­lar expres­sion in the file.

2. The other way of doing the same is using ‘sed’.

To con­vert a win­dows file to Linux file, exe­cute the fol­low­ing command

$ sed ‘s/^M//’ IN_FILE > OUT_FILE

For typ­ing ^M, press CTRL-V and then CTRL-M.

NOTE : You can do the same using dos2unix com­mand, if it is present on your machine.

Creating an Oracle JDBC Connection and issuing a SQL statement

JDBC (Java Data­base con­nec­tiv­ity) API defines classes and inter­faces for inter­ac­tions with a rela­tional data­base and pro­vides capa­bil­ity of writ­ing data­base based appli­ca­tions in Java. Using the api, you can issue SQL state­ments to almost all rela­tional data­bases. It pro­vides access by allow­ing you to embed SQL state­ments in Java code. You will get to learn how to load a data­base dri­ver, cre­ate a con­nec­tion and issue a SQL statement.

The JDBC Architecture

Java Appli­ca­tion –> JDBC –> Dri­ver –> Rela­tional Database

Java Appli­ca­tion calls JDBC api. JDBC loads a dri­ver that talks to the rela­tional data­base. This allows us to change data­base engines with­out chang­ing the code as such by chang­ing the inter­act­ing dri­ver information.

Basics

Before you start writ­ing the class def­i­n­i­tion, import java.sql package.

import java.sql.*; (* denotes that all classes of java.sql pack­age will get imported).

1. Load­ing the driver

In this step, you will load the dri­ver class by call­ing Class.forName() with the dri­ver name as an argu­ment passed. The dri­ver class cre­ates an instance of itself and a client can con­nect to the data­base server through a JDBC dri­ver. We will use a ODBC dri­ver (JDBC-ODBC bridge) for con­nect­ing to a data­base server. Class.forName returns a class.

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

This will throw an excep­tion that can be caught by using a try/catch state­ment or by adding

throws Class­Not­FoundEx­cep­tion

2. Cre­at­ing a JDBC connection

The JDBC Dri­ver­Man­ager class cre­ates objects that can con­nect a Java appli­ca­tion to a JDBC Dri­ver. This class pro­vides the back­bone of the JDBC archi­tec­ture by man­ag­ing var­i­ous dri­vers installed on the oper­at­ing sys­tem. The get.connection() method is used to obtain a con­nec­tion to the data­base server. The argu­ments passed are user­name, pass­word and a JDBC URL to obtain a con­nec­tion to the data­base. The method returns a Con­nec­tion object. This object rep­re­sents a ses­sion with the data­base. SQL state­ments can be exe­cuted only when the con­nec­tion is obtained. An appli­ca­tion can obtain one or more con­nec­tions to a sin­gle data­base, or it can obtain mul­ti­ple con­nec­tions to many databases.

JDBC URL — Each dri­ver has a dif­fer­ent syn­tax for source. For a ODBC dri­ver the syn­tax is

jdbc:odbc:systemDSNName

A sys­tem DSN can be cre­ated by going to Con­trol Panel -> Admin­is­tra­tive Tools -> Data Sources (ODBC). Pro­vide infor­ma­tion to your remote data­base server and test the con­nec­tion after creation.

The syn­tax for con­nec­tion cre­ation is

String jdbcUrl = “jdbc:odbc:systemDSNName”;

String user­Name = “scott”;

String pass­word = “tiger”;

Con­nec­tion dbConnection=DriverManager.getConnection(jdbcUrl,userName,password);

Now that we have obtained a con­nec­tion, we can issue SQL state­ments to the data­base and obtain/manipulate the results.

Sum­mary till now

The fol­low­ing class returns a ODBC con­nec­tion to a database.

pub­lic class DBConnection {

pub­lic sta­tic Con­nec­tion getOracleJDBCConnection(String dsn, String user­Name, String pass­word ) throws Class­Not­FoundEx­cep­tion, SQLException{

dsn = “jdbc:odbc:”+dsn;

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

return DriverManager.getConnection(dsn, user­Name, password);

}

}

An object of the above class(inside our main func­tion) is cre­ated as

Con­nec­tion con = DBConnection.getOracleJDBCConnection(“systemDSN”,“scott”,“tiger”);

ORA-12638 Credential Retrieval Failed

While try­ing to cre­ate a sys­tem DSN point­ing to an Ora­cle data­base I came across an error

“ORA-12638 cre­den­tial retrieval failed”

From what I could fig­ure out is, the NTS option makes the Ora­cle client use your Win­dows domain cre­den­tials to authen­ti­cate you with the remote Ora­cle Server. Now, this could fail for the fol­low­ing reasons -

• Ora­cle server is not con­fig­ured for Win­dows cre­den­tial authentication.
• The cre­den­tials used to login to your local Win­dows is not enough to login to your server.

In my case, it was the sec­ond option. Even after telling the ODBC DSN man­ager to use a dif­fer­ent user­name and pass­word, it was using my domain cre­den­tial infor­ma­tion for authentication.

Any­way this could be solved by replac­ing the fol­low­ing line in sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)

by

SQLNET.AUTHENTICATION_SERVICES= (NONE)

This will remove the option of using Win­dows domain cre­den­tials for authen­ti­ca­tion to Ora­cle Server.

Recover MySql password

You can recover MySQL data­base server pass­word with fol­low­ing five easy steps.

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.

Step # 3: Con­nect to mysql server as the root user.

Step # 4: Setup new root password.

Step # 5: Exit and restart MySQL server.

Here are com­mands you need to type for each step (login as the root user):
Step # 1 : Stop mysql ser­vice
# /etc/init.d/mysql stop
Out­put:
Stop­ping MySQL data­base server: mysqld.
Step # 2: Start to MySQL server w/o pass­word:
# mysqld_safe --skip-grant-tables &
Out­put:
[1] 5988

Start­ing mysqld dae­mon with data­bases from /var/lib/mysql mysqld_safe[6025]: started
Step # 3: Con­nect to mysql server using mysql client:
# mysql -u root
Out­put:
Wel­come to the MySQL mon­i­tor. Com­mands end with ; or \g.

Your MySQL con­nec­tion id is 1 to server ver­sion: 4.1.15-Debian_1-logType ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>
Step # 4: Setup new MySQL root user pass­word.
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
Step # 5: Stop MySQL Server:
# /etc/init.d/mysql stop
Out­put:
Stop­ping MySQL data­base server: mysqld

STOPPING server from pid file /var/run/mysqld/mysqld.pid

mysqld_safe[6186]: ended[1]+ Done mysqld_safe –skip-grant-tables

Step # 6: Start MySQL server and test it
# /etc/init.d/mysql start
# mysql -u root -p

Linux Hard Disk Format Command

There are total 4 steps involved for hard disk upgrade and instal­la­tion pro­ce­dure:

Step #1 : Par­ti­tion the new disk using fdisk command

Fol­low­ing com­mand will list all detected hard disks:
# fdisk -l | grep '^Disk'

Out­put:
Disk /dev/sda: 251.0 GB, 251000193024 bytes
Disk /dev/sdb: 251.0 GB, 251000193024 bytes
A device name refers to the entire hard disk.

To par­ti­tion the disk – /dev/sdb, enter:
# fdisk /dev/sdb
The basic fdisk com­mands you need are:

• m – print help
• p – print the par­ti­tion table
• n – cre­ate a new partition
• d – delete a partition
• q – quit with­out sav­ing changes
• w – write the new par­ti­tion table and exit

Step#2 : For­mat the new disk using mkfs.ext3 command

To for­mat Linux par­ti­tions using ext2fs on the new disk:
# mkfs.ext3 /dev/sdb1
Step#3 : Mount the new disk using mount command

First cre­ate a mount point /disk1 and use mount com­mand to mount /dev/sdb1, enter:
# mkdir /disk1
# mount /dev/sdb1 /disk1
# df -H
Step#4 : Update /etc/fstab file

Open /etc/fstab file, enter:
# vi /etc/fstab

Append as fol­lows:
/dev/sdb1 /disk1 ext3 defaults 1 2
Save and close the file.

Task: Label the partition

You can label the par­ti­tion using e2label. For exam­ple, if you want to label the new par­ti­tion /backup, enter
# e2label /dev/sdb1 /backup

You can use label name insted of par­ti­tion name to mount disk using /etc/fstab:
LABEL=/backup /disk1 ext3 defaults 1 2

Searching Shell Command History

Almost all mod­ern shell allows you to search com­mand his­tory if enabled by user. Use his­tory com­mand to dis­play the his­tory list with line num­bers. Lines listed with with a * have been mod­i­fied by user.
Shell his­tory search command

Type his­tory at a shell prompt:
$ history
Out­put:
Sam­ple out­put:
6 du –c
7 du –ch
8 ls [01–15]*-2008
9 ls –ld [01–15]*-2008
….
41 g++ prime1.cpp
42 ./a.out
43 ssh ankit@delta
44 scp ~/Desktop/passport.jpg ankit@delta:
45 man expr
46 iptab
47 history

….
…
996 ping intrarouter.delta.nitt.edu
997 ssh ankit@intrarouter.delta.nitt.edu
998 alias
999 ~/scripts/clean.rss –fetch
1000 vnstat
1001 ~/scripts/clean.rss –update

To search par­tic­u­lar com­mand, enter:

$ history | grep command-name
$ history | egrep -i 'scp|ssh|ftp'

Emacs Line-Edit Mode Com­mand His­tory Searching

To get pre­vi­ous com­mand con­tain­ing string, hit [CTRL]+[r] fol­lowed by search string:
(reverse-i-search):

To get pre­vi­ous com­mand, hit [CTRL]+[p]. You can also use up arrow key.
CTRL-p

To get next com­mand, hit [CTRL]+[n]. You can also use down arrow key.
CTRL-n

fc com­mand

fc stands for either “find com­mand” or “fix com­mand. For exam­ple list last 10 com­mand, enter:

$ fc -l 10

To list com­mands 130 through 150, enter:
$ fc -l 130 150

To list all com­mands since the last com­mand begin­ning with ssh, enter:
$ fc -l ssh

You can edit com­mands 1 through 5 using vi text edi­tor, enter:
$ fc -e vi 1 5

Delete com­mand history

The –c option causes the his­tory list to be cleared by delet­ing all of the entries:
$ history -c

Dimensions and Introduction to Star Schema

Dimen­sions

Dimen­sions, in data ware­hous­ing are used for cat­e­go­riza­tion of items in a data set into non-intersecting regions. Dimen­sions helps in pro­vid­ing struc­tured labeled infor­ma­tion from oth­er­wise unstruc­tured numer­i­cal data. Dimen­sions are basi­cally per­spec­tives of ana­lyz­ing data in your data ware­house, exam­ple Geo­graph­i­cal loca­tion, Peri­odic infor­ma­tion, etc. Com­mon dimen­sion tables in a data ware­house are Geog­ra­phy, Time, Prod­uct, etc. Lets take an exam­ple of where a Sales team man­ager wants to orga­nize and view Sales data and the var­i­ous dimen­sions that can be attached to it. Before I go into that let me intro­duce you to the con­cept of Star schema.

Star Schema

Star schema is the most basic one of the data ware­house schemas. In a star schema, there are a few fact tables (one in most cases) and a lot of dimen­sions point­ing to it. In the fol­low­ing top­ics we will dis­cus a prac­ti­cal imple­men­ta­tion of a star schema for bet­ter under­stand­ing of this con­cept. The fact tables hold the main data and the dimen­sion tables help in cat­e­go­riz­ing this data into dif­fer­ent clus­ters. Dimen­sion tables have pri­mary keys and the fact tables have for­eign keys point­ing to var­i­ous pri­mary keys in the dimen­sion tables. One main rea­son of using a star schema is that the queries are never com­plex because the joins involve a fact table and a sin­gle level of dimen­sion tables. Another rea­son is that it lets imple­men­ta­tion of the con­cept of multi dimen­sional data­bases using rela­tional data­bases.

A sim­ple Star Schema

Image Src — Wikipedia.org

Dimen­sion Hierarchies

Each Dimen­sion may have a hier­ar­chy attached to it. Hier­ar­chies have lev­els for drilling up or drilling down on data in the OLAP. By drilling down I mean chang­ing the view of data to a greater level of Detail, exam­ple Year -> Month -> Week. Drilling up refers to chang­ing the view of data to a higher level of aggre­ga­tion, exam­ple — State -> Coun­try -> Region. By drilling up and down, an ana­lyst can move up and down the hier­ar­chies to see how the infor­ma­tion at the var­i­ous lev­els is related.

Case Study — Sales Schema

Say I am a busi­ness man and I own a retail chain with branches all over the globe. So the most impor­tant things that i will be inter­ested in would be Net Profit by region/year/product/sales per­son. Now a model schema that can be imple­mented here could be :

Fact table — Sales fact con­tain­ing Net Profit

Dimen­sional tables, basi­cally the cat­e­go­riza­tions used to spread out an aggre­gate mea­sure i.e. fact (net profit) to reveal its con­stituent parts.

TIME DIMENSION

Con­tains met­ric infor­ma­tion about net profit by Year, month and weeks. The day in which max­i­mum sales occurred. The most prof­itable month of the year.

Hier­ar­chy asso­ci­ated — Year -> Months -> Weeks -> Day.

GEOGRAPHY DIMENSION

Con­tains met­ric infor­ma­tion about net profit by region, coun­try or area. The most prof­itable region of the world. The coun­try with the least amount of profits.

Hier­ar­chy asso­ci­ated — Region -> Coun­try -> State -> City -> Area.

PRODUCT DIMENSION

Defines the dif­fer­ent prod­ucts and prod­uct cat­e­gories and cat­e­go­rizes the net profit on that.

Hier­ar­chy Asso­ci­ated — Prod­uct lines and Prod­uct Categories.

SALES PERSONS

Con­tains met­ric infor­ma­tion about net profit by indi­vid­ual sales per­sons. Per­son with the max­i­mum profit, min­i­mum Profit, etc.

Thats it. The a very sim­ple Sales Data ware­house is ready to use. We can imple­ment inter depen­den­cies between the dimen­sions like Prod­uct Hier­ar­chy with Geo­graph­i­cal details, etc and such a model can be imple­mented using a snowflake schema.