Archive

Archive for the ‘database’ Category

Flush the buffer cache in Oracle

June 26th, 2007
No comments

Flush the buffer cache in [tag]Oracle[/tag]. This invalidates also the read cache, so the query performance results provides from the physical reads time. 

alter system flush buffer_cache;

This command is not available prior to 10g. It flushes the buffer cache in the SGA.
9i had an undocumented command to flush the buffer cache: 

alter session set events = 'immediate trace name flush_cache';

pjdc database

Dump database schema to a file

January 30th, 2007
No comments

export all oracle database to a dump file:

  1. $ORACLE_HOME/bin/exp / GRANTS=y ROWS=y FILE="exp.dmp exp_1.dmp" FILESIZE=1GB LOG=export.log OWNER="()"

pjdc database

Implement pagination with SQL Server 2005

January 9th, 2007
4 comments

With the new row_number() feature of the MS SQL Server 2005, now it’s very easy to implement pagination: get data rows page by page to avoid the retrieve too much data at same time. The new feature it’s very similar to the Oracle ROWUM.
The prepare statement could be like the follow:

  1. SELECT * FROM ( SELECT row_number() OVER (ORDER BY id) AS resultNum, id FROM table_name) AS numberResultsWHERE resultNum  BETWEEN ? AND ?

More information here.

Technorati Tags: ,

pjdc database

Retrieving identity column values from MS SQL Server by JDBC

January 3rd, 2007
3 comments

Retrieving identity column values in JDBC applications:

You define an identity column in a CREATE TABLE by specifying the IDENTITY clause when you define a column.
This feature is defined in the JDBC, and implmented in the MS Sql Server 2005, not in the older version. For Oracle database this is not implemented, but the common solution of the rownum can be used instead.

1.Set the prepared statement to get the identity column from DB:

  1. Connection.prepareStatement(sql-statement,
  2. Statement.RETURN_GENERATED_KEYS);

or

  1. Statement.executeUpdate(sql-statement, Statement.RETURN_GENERATED_KEYS);

2. Get the generated values from the statement

  1. rs = stmt.getGeneratedKeys();
  2. while (rs.next()) {
  3. rs.getBigDecimal(1);
  4. }

Technorati Tags: , ,

pjdc database, java ,