Hong Wang

I am an Oracle DBA, working in Oracle database since version 7.3. Worked in both application development and production support. Many experiences in real world complicate problems and database projects. This blog serves as a collections of notes I write on my database studies as well as issues I encountered/solved. Your comments are well welcome.
Hong Wang has written 16 posts for Hong Wang's Oracle Blog

Tuning Query without Changing Code by Using SQL Plan Baseline

As a DBA most of us have had such experiences, when tuning a SQL statement we found the only way to improve the performance of the SQL is to change the code. The change can be adding/removing hints, changing the order of joins, removing unnecessary function calls, etc. However in reality we all know the … Continue reading

Object Privileges for Function Based Indexes

This is in database. A function based index stores values of a function or expression applied to one or more columns of a table. When you create a  function based index using a user-defined function that is owned by another schema, besides meeting all prerequisites of creating conventional indexes you will also need to … Continue reading

Tuning RMAN Backup/Restore with Memory Settings

Introduction When RMAN does database backup, restore or recovery, RMAN client (rman command process) itself does not perform the operations. Instead it directs database server sessions to do the tasks. One database session corresponds to one RMAN channel that represents one stream of data to a device, disk or tape. The channel reads data into … Continue reading

Space Usage of LOBs, General Behavior and in OTM Database

What is LOB LOB (large object) is a group of Oracle database build-in data types. Using LOB data types in Oracle database allows user to store large blocks of unstructured data, such as text, graphic images, video clips and sound waveforms, in character or binary format. Since they were introduced in Oracle 8, LOB data … Continue reading

Automatic Workload Repository Snapshots

Oracle Automatic Workload Repository (AWR) collects, processes and maintains performance statistics for problem detection and self-tuning purposes. The gathered data can be viewed in both reports and views. AWR collects cumulative and differential statistics data into AWR snapshots. The differential values captured by the snapshot represent the changes for each statistic over the time period … Continue reading

On Database Deadlock, Foreign Key, and Indexing

This is in 11g database. A database deadlock is a situation in which two user sessions are waiting for data locked by each other. Oracle database automatically detects deadlocks and resolves them by rolling back one statement involved in the deadlock, release one set of the conflicting row locks. The statement rolled back belongs to … Continue reading

Solving ORA-04031 Error Examples

Oracle error code 4031 states: unable to allocate %s bytes of shared memory (\”%s\”,\”%s\”,\”%s\”,\”%s\”). Generally this error happens when more shared memory is needed than was allocated in the shared pool or Streams pool. When a database has Automatic Shared Memory Management (ASMM) turned on, the DBA should look at the current SGA dynamic components … Continue reading

Database I/O Calibration

There are several new features in 11g Oracle Resource Manager, I/O calibration is one of them. This feature enables user to assess the performance of the storage subsystem, and determine whether I/O performance problems are caused by the database or the storage subsystem. Unlike other external I/O calibration tools that issue I/Os sequentially, the I/O calibration feature … Continue reading

Grid Control — Adding Target: a Standby Database

In Grid Control adding a database target is pretty straightforward. You go to the home page of the host, on which your database is running, and click on “Targets” tab. Then by clicking on the agent link you come to the agent home page. In “Monitored Targets” section you can add a new database target, … Continue reading

Data Guard 11g’s Automatic Gap Resolution and ORA-16401 Error

A log file gap occurs whenever a primary database continues to commit transactions while the LNS process has ceased transmitting redo to the standby database. This can happen when the network or the standby database is down and your Data Guard protection mode is not Maximum Protection. The primary database’s LGWR process continues writing to … Continue reading