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