Thursday, November 12, 2009

Switch the default system temporary tablespace

create temporary tablespace temp_new tempfile
'd:\oracle\oradata\test\temp1.dbf' size 500m autoextend on next 10m maxsize unlimited
extent management local;

Switch to new temporary tablespace. You may need to switch the temporary tablespace to new one to reduce the size of the current tempfile and remove it.

alter database default temporary tablespace temp_new;

You may not switch until the tablespace is in use.

Drop the old one .

drop tablespace temp_od including contents and datafiles;

Switch undo to new undo tablespace Oracle 9i onwards

Create New Undo Tablespace

create undo tablespace undo_new datafile
'd:\oracle\oradata\test\undodata1.dbf' size 1024m autoextend off maxsize unlimited online;

Issue these command one or more times to force the commited data write in datafiles.

alter system checkpoint;

alter system switch logfile;

alter database archive log current;

Modify the SIDinit.ora file parameter

undo_tablespace=undo_new

Shutdown the database

shutdown immediate;

Check the parameter value

show parameter undo_tablespace

Drop the old undo tablespace and physical datafiles

drop tablespace undo_old including contents and datafiles;

Sunday, October 25, 2009

Find IP Address and Host Name of Oracle Server from Client Environment

Invoke the following section to get the Oracle Database Server IP Address from Client Environment.

set serveroutput on
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME); -- get local host name
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS); -- get local IP addr
END;
/


Get the IP Address of the client

select SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr from dual;

Wednesday, September 30, 2009

Move or Rename Oracle Datafiles

Move or rename the datafiles from current location to another one. Then startup the database in mount mode as:

startup mount

Then rename the datafile as below:

alter database rename file '/abc/mydatafile.dbf' to '/xyz/mydatafile.dbf';

This will update the location of datafile in control file. Then open the database as

alter database open;

You can now view the location of datafile from the v$datafile view.

Identify Oracle Patches Applied in AIX Server

List of installed patches

$ORACLE_HOME/OPatch/opatch lsinventory

Detail List of installed patches

$ORACLE_HOME/OPatch/opatch lsinventory -details

Enviornment variable $ORACLE_HOME must be set.

Sunday, August 9, 2009

AJAX Frameworks with Server-Side Java Support All Open Source

DWR - DWR (Direct Web Remoting) is a way of calling Java code on the server directly from JavaScript in the browser. DWR consists of two main parts: JavaScript running in the user's browser to communicate with the server and dynamically update the web page, and a Java Servlet running on the server that processes requests and sends responses back to the browser. DWR takes a novel approach to AJAX by dynamically generating JavaScript code based Java classes. Thus the web developer can use Java code from JavaScript as if it were local to the web-browser; whereas in reality the Java code runs in the web-server and has full access to web-server resources.

JSON-RPC-Java - JSON-RPC-Java is a dynamic JSON-RPC implementation in Java. It allows you to transparently call server-side Java code from JavaScript with an included lightweight JSON-RPC JavaScript client. It is designed to run in a Servlet container such as Tomcat and can be used with JBoss and other J2EE Application servers to allow calling of plain Java or EJB methods from within a JavaScript DHTML web application.

AjaxTags - The AJAX Tag Library is a set of JSP tags that simplify the use of Asynchronous JavaScript and XML (AJAX) technology in JavaServer Pages. This tag library fills that need by not forcing J2EE developers to write the necessary JavaScript to implement an AJAX-capable web form. The tag library provides support for live form updates for the following use cases: autocomplete based on character input to an input field, select box population based on selections made from another field, callout or balloon popups for highlighting content, refreshing form fields, and toggling images and form field states on/off.

Echo 2 - Echo2 is the next-generation of the Echo Web Framework, a platform for developing web-based applications that approach the capabilities of rich clients. The 2.0 version holds true to the core concepts of Echo while providing dramatic performance, capability, and user-experience enhancements made possible by its new Ajax-based rendering engine.

AjaxAnywhere - AjaxAnywhere is designed to turn any set of existing JSP components into AJAX-aware components without complex JavaScript coding.

ActiveMQ Ajax Support - Ajax support in ActiveMQ builds on top of the REST connector for ActiveMQ which allows any web capable device to send or receive messages over JMS. All the Ajax examples are currently using OpenRico.

Tacos - The Tacos library project provides components and ajax behaviour for the Tapestry java web application framework. Most of the library relies almost exclusively on Dojo.

WebWork 2.2 Awesome AJAX support built on top of DWR and Dojo. Form validation, tabbed panels, remotable forms, and remote divs. More AJAX components will be coming in subsequent releases.

Simple Web Framework - The Simple Web Framework (SWF) is an event based framework targeting Struts developers who want to build rich Web applications but do not want to migrate to JSF. The SWF is built upon the same Jakarta commons basis as Struts, but uses a different request processor (front controller.) The SWF event model supports XmlHttpRequest (as well as form/submit) based event posting, similar to VB.NET or JSF, with In place Page Updating (IPU) rather than page reloading.

Wicket 1.1 rc2 - Dojo, Scriptaculous and Wicket integration. The release consists of an Ajax handler, allowing you to create your own custom Wicket Ajax components based on the Dojo toolkit.

Taconite - The heart of Taconite is a parser that converts normal (X)HTML code into a series of JavaScript commands that dynamically create the content on the browser. This parser allows you, the developer, to write content in way that is natural -- as (X)HTML. You no longer have to crowd your pages with a slew of document.createElement and document.appendChild commands to dynamically create new content. The Taconite custom parser is implemented as a set of JSP custom tags, which can be used in any Java servlet container, or as a client-side JavaScript library, meaning it can be used in conjunction with virtually any server-side technology.

SWATO - Server side Java library can be deployed in Servlet 2.3+ compatible containers. Client side JavaScript library is base on Prototype. JSON based marshalling. Spring integration. Includes several reusable components like auto-suggest Textbox, Javascript templates and logging.

Zimbra - Zimbra is an open source server and client technology for AJAX based messaging and collaboration. The collaboration server is built using Java based technologies. The server features file based message storate, a SQL metadata storage, Lucene based search, clustering, replication, archiving and LDAP support.

Orbeon Forms - An XForms engine much improved over OPS 2.8's, significant improvements in the Page Flow Controller, and more. The XForms engine is based on Ajax technologies. This makes the XForms engine much more responsive to user interaction than with OPS 2.8.

JsOrb - JsOrb includes code generators that build on demand JavaScript classes for your POJOs and as proxies to your business logic interfaces. The JavaScript classes have the same methods as your POJOs and business logic interfaces, so your JavaScript code ends up looking surprisingly similar to Java.

ZK - ZK is an event-driven, XUL-based, AJAX-embedded, all Java framework to enable rich user interfaces for Web applications. With ZK, you represent and manipulate RIA in XUL/HTML components all at the server similar to how you developed desktop apps. ZK includes an AJAX-based event-driven engine to automate interactivity, and a rich set of XUL-based components.

Rialto - Rialto is a cross browser javascript widgets library. It supports pure javascript development and JSP/taglib development. A JSF integration is planned.

JSP Controls Tag Library - JSP Controls Tag Library (the "Library") provides the lifecycle for portlet-like JSP components. The Library does not require a portal engine or other central controller. The components built with the Library can be used in any JSP-based application. The Library supports two request processing modes: Non-Ajax and Ajax. Pages composed with JSP Controls Tag Library look and behave the same way whether they run in Ajax mode or not.

OpenLaszlo - OpenLaszlo programs are written in XML and JavaScript and transparently compiled to Flash and DHTML. The OpenLaszlo APIs provide animation, layout, data binding, server communication, and declarative UI.

XAP - The basic intent of XAP is to leverage existing Ajax projects such as Apache Kabuki and Dojo, as well as other community efforts such as Eclipse openAjax. It aims to be pluggable with various Ajax toolkits, reduce the need of scripting and solve the development challenge as well as application maintenance challenges associated with Ajax programming.

AJAX4JSF - Ajax4jsf fully leverages the benefits of the JavaServer Faces framework including lifecycle, validation, and conversion facilities, along with management of static and dynamic resources. Ajax4jsf enables page-wide AJAX support instead of the traditional component-wide support. This means you can define the event in the page that invokes an AJAX request and then the areas of the page that should be synchronized with the JSF Component Tree after the AJAX request changes the data on the server.

ThinkCAP JX - ThinkCAP's core is an advanced MVC framework that manages the runtime layout and presentation of GUI components, state management, data binding, validation, and data persistence. The framework is driven by Java classes and XML metadata generated by the Workbench. The current 6.0 version is very Struts-like.

jMaki - jMaki is all about enabling Java developers to use JavaScript in their Java based applications as either a JSP tag library or a JSF component.jMaki uses the best parts of Java and the best parts of JavaScript to deliver a rich AJAX style widgets. jMaki currently provides bootstrap widgets for many components from Dojo, Scriptaculus, and Yahoo UI Widgets. Also included in this project are a set of AJAX widgets with a focus on Web 2.0. Included are a RSS widget, a del.icio.us Bookmark widget, a Chat widget, and many more to come.

GWT - Google Web Toolkit (GWT) is a Java software development framework that makes writing AJAX applications like Google Maps and Gmail easy for developers who aren't experience with javascript. GWT lets you avoid tedious and error-prone javascript programming while offering your users the same dynamic, standards-compliant experience. You write your front end in the Java programming language, and the GWT compiler converts your Java classes to browser-compliant JavaScript and HTML.

Helmi - Helmi's Open Source RIA Platform permits client- and server-side engineers to work independently while communicating effectively through an object-oriented environment. This solution is composed of a 100% browser-based Client Framework, a Virtual Browser that enables cross-browser application operations, an Integrated Development Environment (IDE), and a J2EE-based AJAX Server Connect that simplifies transmitting data from server to client.

Open jACOB - Open-jACOB is a Java rapid application development tool (RAD) based on Eclipse that enables the development of Rich Internet Applications (RIA) that are based entirely on Java. The primary goal of the jACOB Framework is to handle web applications with CRUD (create, retrieve, update, delete) with little or no Java programming. Instead of telling the application how to retrieve and how to display the data. The developer creates the Web-UI with a point&click eclipse UI-Designer.

ThinWire - ThinWire is an open source development framework that allows you to easily build RIA applications. Notable features include a familiar event-driven GUI programming model, state maintenace via variables and not via session, exclusively server-side language and supports all major browsers.

ICEfaces - ICEfaces is a RIA framework that delivers unique Ajax Push capabilities. Discover the power of Ajax Push and create collaborative and dynamic enterprise applications. ICEfaces leverages the entire standards-based Java EE ecosystem of tools and execution environments. Rich enterprise application features are developed in pure Java, and in a pure thin-client model. ICEfaces is based on JavaServer Faces (JSF).

Thursday, May 21, 2009

Managing Online Transaction Processing Oracle Database (OLTP)

In an OLTP system, several factors may effect the performance of the oracle database. The administrator should configure database parameters taking into account the numbers of synchronous users logging into the system at time and the type of processes they carry out. The administrator should also have knowledge about the way in which the application manages and create session during logging into the system so that the desired level of parameters can be set. The most important parameters to be taken into account in OLTP system are as below:

processes : maximum operating system user processes that can connect to database.
sessions : This parameter is derived as (1.1 *processes)+5. If the process logged into the database create session out
of it then the derived session using above formula may not be enough for OLTP system. If this is the case, one should increase this parameter as needed.
transactions: This parameter is derived as (1.1 *sessions). It is the maximum number of current transaction that can occur. The greater is this parameter more SGA is allocated and more undo data segment needed.
open_cursors :specifies the maximum number of DML statements a session can have.
parallel_max_servers : specifies the maximum no of parallel execution processes and parallel recovery processes for an instance.

Monitor v$resource_limit view and compare the current utilization, max utilization, initial allocation and limit value for the parameters and modify the parameters as needed.

Wednesday, May 20, 2009

Using Sqlloader load data from excel to oracle database

Sql Loader is the oracle utility to load data into oracle database from external source datafile specified in specific format. The different types of files comes into being during the data load.

Parameter file : specifies the parameters neccesary to invoke sql loader utility. This may contain the oracle user id and password needs to connect to database, placement of the control file need to parse data and placement of the log files
during the load. Different options may be specified in this file during load such as rows of data after which to commit, disable the archiving option , parallel load option, conventional and direct path load etc.

Control file : specifies the format of the data to be loaded and conditions necessary to load data during the load. The data can be replaced,appended, inserted, truncated in the table. Sql loader does not create table and load into the table. INFILE specifies the source data file whereas DISCARDFILE specifies logs of the rejected rows during the load.The method of field segregation is specified by keyword FIELD TERMINATED BY. Source data may be included in the control file itself using begindata option.

Data file : name of the source data file that contains the data to load as format specified in the control file.

Log file : name of log file to log the result of log during the load.

Discard file : name of file to log the rejected rows during the load.Data can be loaded into the database in two ways:

Direct : This option in parameter file load the data from memory directly into the oracle blocks without generation of the redologs. Direct path loads cannot be used in clustered tables.

Conventional : uses sql insert statement to load data. Generation of redo log, archive files, execution of triggers are the characteristic of conventional path load.

Below is the sample of sql load from the excel file saved in csv format:

Parameter file contents:

userid=username/password
control=c:\myData.ctl
errors=10
log=c:\myData.log
load=1000

Control file contents:

LOAD DATA
INFILE 'c:\myData.csv'
DISCARDFILE 'c:\myData.dsc'
append --REPLACE , APPEND, TRUNCATE
INTO TABLE myData
-- PARTITION (Partition_table_name)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(v_name, v_acct)

Sql loader can be invoked from command prompt as:

sqlldr parfile=c:\myData.par

Check the logfiles during the load

Wednesday, April 29, 2009

Database Verify Utilily (dbv) in Oracle Database

The Database verify utility is mechanism to check the block corruption of the oracle datafiles at Operating system level. It validate the blocks specified by the dbv utility parameters and generates output accordingly. dbv utility is generally useful
for verifying and validating online as well as offline datafiles. Generally this procedure is not used for online data files as errors may be induced in the online files during the execution of validating the files and successive checks may be needed. The Database utiliy can be executed as below

dbv file=datafile_name blocksize=datafile_blocksize

It is the duty of Database administrator to check the file corruption at frequent basis. Below is the script that generates the script for the checks required for the datafiles in unix enviornment and executes the same at the end of the generation

#!/bin/ksh
# dbv script in unix enviornement
#
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
wlogfile=dbv.${ORACLE_SID}
echo "connecting to oracle database and generating commands"
$SQLPLUS system/manager >>$wlogfile << EOF
set echo off feedback off verify off pages 0 termout off
set linesize 150
spool dbv.cmd
select 'dbv file=' || name || ' blocksize=' || block_size ||' feedback=' || round(blocks*.10,0) from v\$datafile;
spool off
set feedback on verify on pages24 echo on termout on
EOF
echo "command generated succesfully"
echo "verifying datafiles
ksh dbv.cmd
#End

The verification would end up as below

DBVERIFY - Verification complete

Total Pages Examined : 61440
Total Pages Processed (Data) : 995
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1
Total Pages Empty : 60444
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

Summary of the verification

Total Pages Examined : number of pages examined
Total Pages Processed (Data) : number of pages inspected that contained table data
Total Pages Failing (Data) : number of table blocks that have corruption
Total Pages Processed (Index): number of blocks inspected by dbv that contained index data
Total Pages Failing (Index): number of index blocks that are corrupted
Total Pages Processed (Other): number of blocks processed other than table and indexes that are corrupted
Total Pages Empty : number of unused blocks in the file
Total Pages Marked Corrupt : show the number of corrupt blocks during scan if it is non zero there is error in the datafile
Total Pages Influx : number of blocks rechecked due to the blocks in use

Thursday, April 9, 2009

Control Resource Utilization in Oracle

Resource utilization can be controlled either through profiles or Oracle Database Resource Manager. Profiles are generally used to control the password protection, cpu usage assigned to users under profile. While Oracle Database Manager makes use
of total resource available in the hardware by distribution of the resources among the groups when certain resource plans are inactive. Managing resources through Database resource manager is the best solution to manage the utilization of cpu
usage, active session pool, degree of parallelism limit, automatic group switching mechanism, execution time limit, undo pool and idle time limit whereas profiles can be only temporary solution to manage resource utilization.

Here is an example to demonstrate manage cpu usage through profile.

Change the resource_limit parameter either in pfile or through the command line by executing

alter system set resource_limit=true;

CREATE PROFILE PROF_QUERY LIMIT
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 1000;

CPU_PER_SESSION UNLIMITED provides cpu usage to the session for UNLIMITED TIME
CPU_PER_CALL 1000 provides cpu usage to the SQL PARSE,EXECUTE,FETCH for 10 secs for every SQL Statement.

Thus the users under the profile PROF_QUERY can use resource for unlimited time in a session and for 10 secs for each Sql Statement.

Check the default profile of the user

select * from dba_users WHERE USERNAME='TEST'

Change the profile of the user

ALTER USER TEST PROFILE PROF_QUERY;

Check the parameters of profile

select * from dba_profiles where profile='PROF_QUERY'

To implement and modify Oracle Database Manager feature for resource utilization, change compatible parameter to 8.1.0.0.0 or above, from pfile

compatible='8.1.0.0.0'

Sunday, February 22, 2009

Jsp Page Example

Shows the demonstration of page directive to import java classes,get the hostname through predifined HttpServletRequest variable,access count since last reboot of machine, use of jsp:useBean Directive, class named Man and MysqlConn must exist in package pack and loads the list from the mysql database.

<%@ page import = "java.util.*,pack.MysqlConn,java.sql.*" %>
Current Date <%= new java.util.Date() %>

Remote Host <%=request.getRemoteHost() %>
<%! private int remoteAccess=0; %>
Access Count :<%= ++remoteAccess %>

angle bracket jsp:useBean id="test" class="pack.Man" scope="page"/ angle bracket
angle bracketjsp:setProperty name="test" property="empName" value="Maheswor Prajapati" /angle bracket
angle bracketjsp:setProperty name="test" property="address" value="bkt"/angle bracket
angle bracketsp:setProperty name="test" property="gender" value="male"/angle bracket
Name: angle bracket jsp:getProperty name="test" property="empName" /angle bracket
Address :angle bracketjsp:getProperty name="test" property="address" /angle bracket>
Gender: angle bracketjsp:getProperty name="test" property="gender" /angle bracket

<%! ResultSet set=null; %>
<%
MysqlConn con=new MysqlConn();
set=con.getEmp();
%>

The output is:
begin select tag
<%

while (set.next()){
%>
begin option tag value='<%=set.getString(1) %>' ><%=set.getString(2)%> end option tag
<%
}

%>

end select tag

Program to Connect Mysql Database in Java

The program connects mysql database in java. It assumes that the database named test exist in the local computer and mysql.jar is loaded in the JRE system library. It retrives and displays the name of the employee from the database.

package pack;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class MysqlConn {
static String user = "mahesh";
static String password = "prajap";
static String url = "jdbc:mysql://localhost/test";

public static Connection dbConnect() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
java.sql.Connection conn = DriverManager.getConnection(url, user,
password);
return conn;
} catch (Exception ms) {
ms.printStackTrace();
return null;
}
}

public static void main(String args[]) {
MysqlConn conn = new MysqlConn();
conn.getEmp();
}

public void getEmp() {

Connection conn = MysqlConn.dbConnect();
String queryStr = "select emp_id,emp_name from emp";

try {
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(queryStr);
while(rs.next()){
System.out.println("The employee is: "+rs.getString(2));
}

} catch (Exception ms) {
ms.printStackTrace();

}
}

}

Friday, February 13, 2009

To retrive source code of database objects in oracle

The user_source view provides the source code of the named objects in the user schema. One can get the source code for the object by issuing

SET LINES 1200
SET PAGES 1200
SELECT TEXT FROM USER_SOURCE WHERE NAME='object_name';

Multiplexing Control Files and Redo Log Files in Oracle Database

Placing the oracle server processing files such as redo log and control files in multiple destination is multiplexing. It is good practise to run database in muliplexing mode such that one can recover or run the database from the multiplexed files in case of failure.

Multiplexing Control Files

Create pfile from spfile;
Shutdown the database;
shutdown immediate;
Change
control_files='filepath1\CONTROL01.CTL',
filepath1\CONTROL02.CTL',
filepath1\CONTROL03.CTL'
To
control_files='filepath1\CONTROL01.CTL',filepath2\CONTROL01.CTL',
filepath1\CONTROL02.CTL',filepath2\CONTROL02.CTL',
filepath1\CONTROL03.CTL',filepath2\CONTROL03.CTL' in pfile.

Copy control files from the first destination to second destination
Start the database
statup;
confirm the muliplexing.
show parameter control_files

Multiplexing Redo Log files

List the status of the Redo Log files by firing
SELECT * FROM V$LOG;
Drop all the redolog that are in inactive and unused state by issuning
ALTER DATABASE DROP LOGFILE 'filepath\logfilename.log';

Then create the redolog files group in muliple destination by issuning
ALTER DATABASE ADD LOGFILE GROUP group_number ('filepath1\logname.log','filepath2\logname.log') SIZE 5M;
Repeat the process to add the group each time

To drop active and current log files Switch the logfile to next by issuing
ALTER SYSTEM SWITCH LOGFILE;
Then drop the redo log and create the new group as before.

Then issue
SELECT * FROM V$LOG;

Remember that for best performance redo log files must be of same size.
This completes the muliplexing of control files and redo log files.

Thursday, February 12, 2009

Moving User Indexes and User Tables to new tablespace

It is the need for good performance to maintain separate tablespace for user indexes. One can assign the tablespace name to the user index tablespace at the time index creation or can move the index to a new tablespace afterwards.

At the time of table creation
CREATE INDEX index_name ON table_name (col1, col2) TABLESPACE tablespace_name;
The index will be created in users default tablespace if the tablespace option is ommitted.

To move index to other index tablespace
ALTER INDEX index_name REBUILD TABLESPACE indx_tablespace_name;
This move the index to name tablespace and rebuild the index.

To move table to new tablespace
ALTER TABLE table_name MOVE TABLESPACE tablespace_name;
This will move table to new tablespace.

Monday, February 9, 2009

Program in Java to convert physical files in jpg format to Binary files in Oracle Database

Connection to Oracle database

import java.sql.*;
import java.sql.Connection;

public class Conn {
static String url = "jdbc:oracle:thin:@ipaddress:1521:dbname";

static String user = "username";

static String pass = "password";

public static Connection getConnection() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, user, pass);
return conn;

} catch (Exception ms) {
ms.printStackTrace();
return null;
}
}

public static void main(String ss[]) {
Connection conn = Conn.getConnection();
}

}

Create binary files


import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

class MySignFilter implements FileFilter {
public boolean accept(File pathname) {
return (pathname.getName().toLowerCase().endsWith("jpg"));
}
}

public class Transfer {

Connection conn = Conn.getConnection();

public void ReadFile() {

try {
File chkerror = new File("C:/pic/errfile.txt");
chkerror.setWritable(true);
FileWriter writer = new FileWriter(chkerror);
PrintWriter printwriter = new PrintWriter(writer);
File file = new File("C:/pic");
MySignFilter mySign = new MySignFilter();
File list[] = file.listFiles(mySign);

for (int i = 0; i < list.length; i++) {

FileInputStream ins = new FileInputStream(list[i]);
byte b[] = new byte[ins.available()];
System.out.println(i+ ":"+ list[i].getName().trim().toLowerCase().replace(".jpg", ""));
if (b.length < 65536) {

PreparedStatement ps = conn
.prepareStatement("insert into binfiles "
+ " (fname,fvalue) "
+ " values (?,?)");
ps.setString(1, list[i].getName().trim().toLowerCase().replace(".jpg", ""));
ps.setBinaryStream(2, ins, (int) list[i].length());
ps.executeUpdate();
ps.close();
ins.close();
conn.commit();

} else {
printwriter.append(list[i].getName().trim() + "\n");
}

}
printwriter.flush();
printwriter.close();
writer.close();

} catch (Exception ms) {
ms.printStackTrace();
}
}

public static void main(String args[]) {
Transfer trn = new Transfer();
trn.ReadFile();
}

}

Program to create jpg files from the binary data stored in SqlServer

It is sometimes necessary to create the physical files from the binary data stored in the database. Given below is the code in Visual Basice that would create the files in jpg format accessing the binary data stored in SqlServer database. It is required to make ODBC Connection named "sqlserver" before the program can be executed.


Dim cAcc As New ADODB.Connection
Dim rAcc As New ADODB.Recordset

Private Sub Form_Load()
cAcc.Open "Provider=MSDASQL.1;Password=password;Persist Security Info=True;User ID=username;Data Source=sqlserver"
End Sub

Private Sub Command1_Click()
rAcc.Open "select mname,mblob from mFiles", cAcc '

Dim strStream As New ADODB.Stream
strStream.Type = adTypeBinary
strStream.Open
Dim acctno As String
Do While Not rAcc.EOF
strStream.Write rAcc.Fields("mblob").Value
picname = rAcc.Fields("mname").Value
strStream.SaveToFile "C:\pic\" + picname + ".jpg", adSaveCreateOverWrite
rAcc.MoveNext
Loop

End Sub

Thursday, February 5, 2009

Performance Tuning in Oracle Database

Tuning performance is a difficult task to perform. One should know the area on which the tuning is required before performing the real tuning. This column is added to know the database parameters to tune to meet the usage of the hardware requirement during the installation of oracle database.

Parameters affecting the real time transaction processing system in oracle database are as below:

For instance the server is of 1GB memory. The size of the parameters is specified in bytes.

sga_max_size=536870912
reset this parameter upto 50% of the total memrory (in bytes) -512m
shared_pool_size=107374182
reset this parameter upto 10% of the total memrory (in bytes) -102m
large_pool_size=53687091
reset this parameter upto 5% of the total memrory (in bytes) -51m
db_cache_size=107374182
reset this parameter upto 10% of the total memrory (in bytes) -102m
pga_aggregate_target=214748364
reset this parameter upto 20% of the total memrory (in bytes) -204m
sort_area_size=53687091
reset this parameter upto 5% of the total memrory (in bytes) -51m


Create the pfile from the current spfile and change or add the parameters neccessary parameters in the parameter file. Rename
the spfile to take the effect of the pfile in the next startup of the database or create spfile from the changed pfile.

These parameters helps in processing the DML statements during Online Transaction Processing System.