dbapool: Oracle Articles

dbapool: Authors | Submissions | Contact Us

Forgot password? | Sign up

Home

Discussion Forum

Articles

Interview Questions

FAQs

Scripts

Rewards

Analyzer

White Papers

Blog

Certification

Downloads

Tools

Articles


Select the category from below.


Oracle Installation (7)

Oracle Database Performance and Tuning (19)

Oracle Database Backup & Recovery including RMAN a (10)

SQL Tuning (3)

General Database Administration (52)

Others (10)

PL/SQL Programming (6)

Oracle and PHP (1)

Career (3)

System Administration (1)




Write articles and earn rewards. Check here for details.

Oracle Installation
	Oracle Database 10g Release 2 (10.2.0.1.0) Installation on Fedora Core 6 (FC6) by Vigyan Kaushik - Feb 07, 2007 This article will take you step by step for Oracle Database 10g Release 2 (10.2.0.1.0) installation on Fedora Core 6.
	Oracle Database 10g Release 2 (10.2.0.1.0) Installation on Fedora Core 5 (FC5) by Vigyan Kaushik - Feb 06, 2007 This article will take you step by step for Oracle Database 10g Release 2 (10.2.0.1.0) installation on Fedora Core 5.
	Oracle Database 10g Release 2 (10.2.0.1.0) Installation on Fedora Core 4 (FC4) by Vigyan Kaushik - Sep 11, 2005 This article will take you step by step for Oracle Database 10g Release 2 (10.2.0.1.0) installation on Fedora Core 4.
	Oracle Database 10g (10.1.0.2) Installation on Fedora Core 4 (FC4) by Vigyan Kaushik - Aug 22, 2005 This article will take you step by step for Oracle Database 10g (10.1.0.2) installation on Fedora Core 4.
	Oracle Database 10g (10.1.0.2) Installation on Fedora Core 3 (FC3) by Vigyan Kaushik - Aug 21, 2005 This article will take you step by step for Oracle Database 10g (10.1.0.2) installation on Fedora Core 3.
	Oracle Database 10g (10.1.0.2) Installation on Fedora Core 2 (FC2) by Vigyan Kaushik - Aug 20, 2005 This article will take you step by step for Oracle Database 10g (10.1.0.2) installation on Fedora Core 2.
	Oracle Database 10g (10.1.0.2) Installation on Fedora Core 1 (FC1) by Vigyan Kaushik - Aug 17, 2005 This article will take you step by step for Oracle Database 10g (10.1.0.2) installation on Fedora Core 1.
Oracle Database Performance and Tuning
	STATSPACK Statistics Level by Vinod - Feb 07, 2008 DBAs can change the amount of information or detail of statistics Statspack gathers by specifying a snapshot level.
	A Roadmap To Query Tuning by Vinod - Jan 29, 2008 For each SQL statement, there are different approaches that could be used to retrieve the required data. Optimization is the process of choosing the most efficient
	Table Partition Performance analysis by Vinod - Jan 07, 2008 The cost-based approach relies on statistics and if the cost-based Approach is used , then statistics should be generated for all tables, clusters, and all types of indexes accessed by your SQL statements.
	Understanding the SCN by Sandeep - Oct 29, 2007 In order to understand how Oracle performs recovery, it’s first necessary to understand Oracle’s SCN in terms of the various places where it can be stored and how it’s used for instance and media recovery.
	Slow Running SQL results in Oracle performance degradation by ChunPei Feng & R. Wang - Aug 03, 2007 n the morning, routine daily database checking shows that the database has an unusual heavy load. As DBA, definitely, the first checking step
	Table's Fragmentation by Mohammad - Jul 29, 2007 When rows are not stored contiguously, or if rows are split onto more than one page, performance decreases because these rows require additional page accesses.
	TK Prof in Oracle by Vikas Gupta - Apr 20, 2007 TK Prof is an Oracle tool used to display the statistics generated during a trace. When an Oracle session is traced (by SQL*Trace, Oracle Trace, or Database Trace), a trace file is generated.
	SQL Statement Parsing in Oracle by Vigyan Kaushik - Jul 17, 2006 Parsing of a SQL statement involves several steps. Blow is a flow diagram that will show you how this works. It also explains difference between soft and hard parse.
	SQL Advisor in Oracle 10g by Vigyan Kaushik - Jun 22, 2006 Another great feature of Oracle 10G that allow you to tune SQL. Now you don't need to tune SQL statement manually. This new feature does it for you.
	Automatic Workload Repository (AWR) by Vigyan Kaushik - Jun 19, 2006 One of the nice features of Oracle 10g that I really like is the Automatic Workload Repository (AWR). This new tool is kind of replacement of STATSPACK. AWR takes snapshots of the system every 60 minutes.
	Outer join query in Oracle using the (+) sign by Budi Raharjo - Jan 04, 2006 This article will show you how to create outer join query in Oracle using the (+) sign rather than LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN
	Reading Statspack by Arul Selvaraj - Dec 18, 2005 This article a quick guide to read the statspack report.
	How Deadlock situation occurs? by Vigyan Kaushik - Oct 26, 2004 Deadlock is a situation that occurs when two or more users are waiting for data locked by each other.
	How to create 9i database with Automatic Undo Management feature ? by Vigyan Kaushik - Aug 25, 2004 In Oracle 9i, you can create database with AUM (Automatic Undo Management) feature. AUM is one of the nice features of Oracle 9i.
	When you want to re-tune your database? by Vigyan Kaushik - May 18, 2004 It is one of the most important responsibilities of a DBA to tune the database after certain changes have been made by the application/users.
	When to Rebuild an Index? by Vigyan Kaushik - Feb 01, 2004 It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt. Here is a sample procedure on how to identify the such indexes.
	Top 10 init.ora parameters by Vigyan Kaushik - Dec 06, 2001 Some of the init.ora parameters are really critical in the performance of a database.Here we are discussing about the top 10 init.ora parameters.
	Optimizing Data transfer by SDU ? by Vigyan Kaushik - Jan 06, 2002 You can tune your application by reducing the number of round trips across the network. The best way to do it is improve your network performance. Net8/SQL*Net allows you to set role-specific data block sizes using the Session Data Unit (SDU) parameter.
	How to find Oracle Hidden Parameters ? by Vigyan Kaushik - May 09, 2003 Oracle has many hidden parameters. You will not find them in V$PARAMETER or see them with SHOW PARAMETERS command as these are hidden.
Oracle Database Backup & Recovery including RMAN a
	Fast-Start Failover An Overview In High Availability Environments by Vinod - Jan 09, 2008 In Faststart failover dataguard configuration if the primary database becomes unavailable, the observer confirms with the target standby database that the primary production database is unavailable
	STANDBY DATABASE MONITORING & PROTECION MODES (9iR2) by Vinod - Dec 01, 2007 This document is written for understanding and monitoring standby database configured with diffrent protection modes .
	RMAN Backup Strategy by Ahmed - Jul 11, 2007 This article show you different RMAN backup types.
	Creating a Recovery Catalog – RMAN in Oracle 10g by Sabdar - Apr 11, 2007 As we all know, RMAN maintains metadata about the target database and its backup and recovery operations in the RMAN repository.
	CREATION OF 9i STANDBY DATABASE through RMAN by Arjun Raja - May 30, 2006 This article explain how to create Oracle 9i standby database through rman
	What is RMAN (Recovery Manager)? by John Niveth - Nov 22, 2005 This artcle explain Recovery Manager (RMAN)concepts.
	Step by step document to create Standby Database by Jyoti Salunkhe - Apr 30, 2005 Standby database are very critical for disaster recovery. This article takes you step by step to setup Oracle Standby Database.
	Enhanced features of RMAN in Oracle 9i by Vigyan Kaushik - Oct 07, 2001 RMAN is an Oracle tool for taking the backup and recovering the databases. You can copy, restore, and recover datafiles, control files and archived redo logs. It has command line utility as well as GUI-based Enterprise Manager Backup.
	How to take Backup using RMAN ? by Vigyan Kaushik - Sep 06, 2001 RMAN is an Oracle tool for taking the backup and recovering the databases. You can copy, restore, and recover datafiles, control files and archived redo logs. It has command line utility as well as GUI-based Enterprise Manager Backup.
	Standby Database on the same Server by Jasmeet Batra - Feb 06, 2002 You may face a problem when you are setting up a Standby database environment in the same machine as the primary database.
SQL Tuning
	Avoid Sorting done by 'ORDER BY' clause by Hasan - Apr 27, 2008 Sorting is mostly expensive, if you have an index, you can use it to avoid sorting ! Is NOT NULL constraint required? The following TEST CASES will answer your question.
	Introduction to EXPLAIN PLAN by Gajendra - Jun 05, 2007 An EXPLAIN PLAN is a tool that you can use to have Oracle explain to you how it plans on executing your query. This is useful in tuning queries to the database to get them to perform better.
	Querying Hierarchies in Oracle by Vikas Gupta - Feb 16, 2007 Hierarchies , when the number of levels is not fixed, because the only possible implementation then is a recursive table relationship . If you're not using Oracle, "painful" might be a better word choice.
General Database Administration
	ORACLE SECURITY THROUGH THE LISTENER by Desmond - Apr 09, 2008 The Oracle TNS listener is a process based on the server that provides network connection to clients, application servers and other databases to an oracle database.
	External Tables in Oracle 10g by Vigyan Kaushik - Mar 07, 2008 External table in Oracle are a representation of external data in a file. These tables are not stored in the database.
	Instance Shared Memory & Semaphore Segments by Vinod - Feb 28, 2008 Oracle has three different possible models for the SGA - one-segment, contiguous multi-segment, and non-contiguous multi-segment.When attempting to allocate and attach shared memory for the SGA
	Basic Structure of PL/SQL by Vinod - Feb 08, 2008 PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL.
	Remote Diagnostic Agent (RDA) by Vinod - Feb 06, 2008 On Monday morning I received a mail from my client regarding installation of oracle 10g on RHEL ES 4 platform.
	Redo Byte Address (RBA) by Vinod - Feb 07, 2008 Recent entries in the redo thread of an Oracle instance are addressed using a 3-part redo byte address, or RBA.
	Advanced Replication in Oracle by Babu - Oct 25, 2007 This document only creating "Materialized View Replication ( You need one Master Site and more than on Materialized view site)" not for "Multimaster Replication"
	How tnsnames.ora and sqlnet.ora file work together? by Vinod - Oct 19, 2007 I would like to bring an article which explains how useful sqlnet and tnsnames files are and how they work together.
	Locally managed tablespaces in Oracle by Manikandan - Oct 16, 2007 Traditionally, up to Oracle8 Release 8.0.5 the management of free and used extents of a tablespace was done in the data dictionary
	TNS less connection in Oracle by Vigyan Kaushik - Sep 15, 2007 Few dbapool users have asked me about TNS less connection in Oracle. I have put together this article for them and everybody else who are seeking for this information.
	Partition in Oracle by Vikas Gupta - Apr 23, 2007 Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a "partition".
	An effective simple method for exchanging data between Oracle databases by Vinod Udapudi - Apr 13, 2007 When offering data from one Oracle database to other a number of issues need to be considered "Shall the sink database have direct access to the source database?"
	Flash Back Table by Fahad - Feb 09, 2007 This article will help you recover a table upto a certain time. This Procedure was tested on Oracle 10g Release 2
	Sequence in Oracle by Gayathri Sathiamoorthy - Jan 30, 2007 There are many instances where we want a continuous seqnence of numbers to be generated. For example, lets say that we develop a POS (Point of Sale) system (an application used to bill the items in a store).
	How to use MERGE in Oracle? by Baba Kiri - Jan 10, 2007 MERGE is used to select rows from one or more sources, and update or insert into a table or view. You cannot update the same row of the target table multiple times in the same MERGE statement.
	THE RECYCLE BIN in Oracle 10g by Vigyan Kaushik - Nov 15, 2006 Oracle has introduced "Recycle Bin" Oracle 10g where all dropped objects are stored. If you drop a table in Oracle 10g then any associated objects to this table such as indexes, constraints and other dependant objects are simply renamed with a prefix of BIN$$.
	Difference between Dictionary managed tablespace (DMT) and Locally managed tablespace (LMT): by Vishal Patil - Sep 13, 2006 The CREATE TABLESPACE command has a new clause introduced in Oracle8i, the "extent management clause", that specifies how the extents of the tablespace are managed. This clause uses one of the following parameters:
	Virtual Index in Oracle by Felix Thomas - Jun 12, 2006 Virtual indexes is to simulate the existence of an index - without actually building a full index. Using Oracle Hidden Parameter.
	How Is Undo Space Managed by Oracle9i? by Dhruvang Mehta - Mar 20, 2006 Is it true that as an Oracle DBA I don't have to worry about managing undo space anymore, because Oracle9i takes care of it for me?
	CONNECT BY to querying hierarchical data in Oracle by Vigyan Kaushik - Feb 22, 2006 Getting data in hierarchical order may be a big task by writing pl/sql or other programing code. Oracle made is easy with connect by clause. This feature is not often found in other databases.
	Creating An Autonumber Field in Oracle by Budi Raharjo - Mar 08, 2006 Not like in some of RDBMS's (e.g. Microsoft SQLServer), there are no feature in Oracle to create an autonumber field automatically. What is an autonumber field?
	The Basics of Oracle Architecture by Budi Raharjo - Feb 13, 2006 As an Oracle DBA, you must be understand the concepts of Oracle architecture clearly. It is a basic step or main point that you need before you go to manage your database.
	SQL LOADER by John Niveth - Jan 26, 2006 SQL*Loader is a high-speed data loading utility that loads data from external files into tables in an Oracle database.
	The Basic Steps to Connect Oracle and Java Program by Budi Raharjo - Jan 17, 2006 Here, I will show you what the steps to make a connection between Oracle database and your Java program
	ORACLE DATA PUMP in Oracle 10g by Arjun Raja - Dec 31, 2005 Oracle Data Pump is a feature of Oracle Database 10g that enables very fast bulk data and metadata movement between Oracle databases.
	RESUMABLE SPACE ALLOCATION in Oracle 10g by Arjun Raja - Dec 27, 2005 This article describes a useful feature in 10G which allows the user to resume a session from where it hung for reasons of space or other constraints.
	FLASHBACK in Oracle 10g database (FLASHBACK QUERY) by Arjun Raja - Dec 24, 2005 This article explain FLASHBACK QUERY capabilities of the oracle 10g database.
	FLASHBACK in Oracle 10g database (FLASHBACK TABLE) by Arjun Raja - Dec 24, 2005 This article explain FLASHBACK TABLE capabilities of the oracle 10g database.
	FLASHBACK in Oracle 10g database (FLASHBACK DATABASE) by Arjun Raja - Dec 24, 2005 This article explain FLASHBACK DATABASE capabilities of the oracle 10g database.
	Manually Creating an Oracle Database on Windows by John Niveth - Nov 16, 2005 Step to step guide to create database on Windows
	SSH1 and SSH2 authentication without password by Vigyan Kaushik - May 23, 2005 This articles show how to setup SSH authentication without password.
	LOGON trigger by Vigyan Kaushik - Apr 05, 2005 LOGON trigger specify LOGON to fire the trigger whenever a client application logs onto the database.
	How to Start and Stop Intelligent Agent on Unix? by Vigyan Kaushik - Jan 13, 2005 This article shows how agentctl utility is used to start, stop and verify the Agent on Unix system.
	Testing against a fixed date in Oracle? by Gajendra Nagapurkar - Jan 04, 2005 Testing against a fixed date is easy in Oracle. Here is one example demonstrating use of FIXED_DATE parameter.
	File Transfer Protocol (FTP) by Nidhi Kaushik - Jul 20, 2001 File Transfer Protocol (FTP) is the standard method of copying files from one machine onto another across the Internet. FTP is in common use for downloading data and programs from servers, and for uploading Web sites
	SSH (Secure Shell) by Puneet Sharma - Jun 15, 2001 Ssh (Secure Shell) is a program to log into another computer over a network, to execute commands in a remote machine, and to move files from one machine to another.
	How to prevent Fragmentation? by Susan Methew - Nov 06, 2001 Tablespace organize its storage into segment and extents. When an extent is allocated to a segment then its called used extents otherwise itâ€™s called free. Overtime allocation and de-allocation of extents, cause the free space in tablespace to become fragmented.
	How to protect System Tablespace ? by Karen Shirker - Aug 07, 2001 The system tablespace is the most critical tablespace in the database. It contains the data dictionary information that needs to maintain the oracle database.
	How to kill Oracle Process on Windows NT/2000 ? by James Miller - Jun 07, 2001 You may experience problem with the performance of the database and may need to kill the process run by a user.
	How to add and drop online redo log members and groups? by Vigyan Kaushik - Dec 08, 2002 The Oracle server maintains online redo log files to minimize the loss of data in the database. The redo log files record all changes made to data in the database buffer cache with some exceptions.
	How to detect and eliminate Chained and Migrated rows? by Herong Zu - Nov 07, 2002 There could be a situation when data for a row in a table is too large to fit into a single data block. This can be a cause of row chaining or row migration.
	How To Determine Which Instance Owns Which Shared memory and Semaphore Segments? by Anil Singh - Oct 09, 2002 If you are running several oracle instances and suppose one instance crashes and leaves "sgadef.dbf" file, shared memory and semaphore segments running. You want to kill the associated memory segments but not sure which memory segments are associated with that instance since there are many instances are running. You can do it by unix command ipcs.
	How to calculate the approximate size of the SGA (System Global Area) ? by Vigyan Kaushik - Sep 08, 2002 It is very difficult and time-consuming process to find the exactly SGA size based on values of init.ora parameters. It is difficult because of different port specific sizes of data structures that are allocated in the SGA.
	How to check whether auto extend is enabled on a datafile? by Vasu Reddy - Aug 08, 2002 If you have space problem then it is useful to know if datafiles have autoextended their original sizes. Starting from Oracle release 7.2,datafiles were given the ability to extend automatically if needed.
	How to check your oracle is 32 bit or 64 bit? by Vigyan Kaushik - Jun 09, 2002 There are few ways to check this.
	Working with Multiple Block Sizes in Oracle 9i by Vigyan Kaushik - Apr 09, 2002 One of the most important decision for a DBA is the selection of an appropriate block size. This decisions affect the performance of the system. Choosing the proper block size requires a very good understanding of the application environment.
	How to kill the dialog sessions of a user in SAP ? by Mark Ben - Mar 08, 2002 Usually a user may lose the active session on his computer due to various reasons .In that case, SAP retains the session at server level for usually a certain period of time before the session dies off.
	How to handle ORA-01000 ? by Kelly Royan - Jan 09, 2003 This a very common error that could happen in association with application or at the database level with just regular inserts, updates, deletes, etc. in PL/SQL or in SQL*Plus, etc.
	How to handle ORA-01919: role 'PLUSTRACE' does not exist ? by Roy W. - Feb 09, 2003 You might get this error message when trying to use the AUTOTRACE feature of SQL*Plus.
	How to partition a non-partitioned table? by Vigyan Kaushik - Mar 10, 2003 You can partition a non-partitioned table three different ways: A. Export/Import method B. Partition exchange method C. Insert with a sub-query method
	*How to specify a large rollback segment for a SQLLoader session?** by Vigyan Kaushik - Apr 08, 2003 If you are loading huge data by SQL* Loader then you might get rollback segment errors. You can avoid these errors by specifying a large rollback segment for the SQL*Loader session. Here are few steps to do it.
	What is locally managed tablespace? by Vigyan Kaushik - Jul 08, 2003 A Locally Managed Tablespace is a tablespace that manages its own extents maintaining a bitmap in each data file to keep track of the free or used status of blocks in that data file.
Others
	Troubleshooting a Streams Environment by Vinod Udapudi - Feb 15, 2008 After configuring streams once it goes to production it is very much necessary to have a document which explains you basic methods and scripts to trouble shoot the streams environment.
	ODBC connectivity using UNIX ODBC and FreeTDS by Vigyan Kaushik - Jun 28, 2007 This article will show you how you can use establish connectivity in Oracle and MS SQL database. I did my test on Fedora Core 6 with Oracle 10g R2.
	USA 2007 Daylight Saving Time (DST) Impact on your Oracle database by dbapool Editor - Mar 05, 2007 As you may be hearing in the news, the Energy Policy Act of 2005 was signed into law in August 2005 to extend daylight saving time.
	Iostat (Input Output statistics) on Solaris by Vigyan Kaushik - Jan 31, 2006 The iostat command is used for monitoring system input/output device.
	Installing and configuring PHP and Oracle on IIS by Vigyan Kaushik - Nov 30, 2005 This article describes step by step installation and configuration of php with Oracle.
	How to add swap space in Solaris? by Vigyan Kaushik - Jul 19, 2005 This articles explain how you can add swap space on Solaris. If you are installing Oracle and running out of swap space, the steps below can help you in adding additional swap space.
	How to change your hostname in Solaris by Vigyan Kaushik - Jan 30, 2004 It is easy but involve certain things if you want to change host name in Solaris. Below are the files location where you should change the hostname.
	Sap Parameter Checks by Saji Nair - Jul 08, 2002 Here is same detail of sap parameters.
	Monitoring System performance using SAR by Tim Mayer - May 09, 2002 The sar (System Activity Reporter) utility reports on System activity. This is an excellent utility to monitor the system. By using sar data, you can find why a system was slow at a particular time.
	How to mount Oracle 9i CD-ROMs manually on Solaris? by Vigyan Kaushik - Jun 09, 2003 Steps to mount Oracle 9i CD-ROMs manually on Solaris.
PL/SQL Programming
	Working with Timestamps by Vikas - Feb 15, 2007 We all love the DATE datatype, but let's face it. It wasn't everything we always wanted in a timestamp datatype. Namely, the DATE datatype.
	Handling mutating table trigger problem by Sachin - Feb 11, 2007 This article explain a scenario of mutating table error on trigger. Suppose u had written a "AFTER DELETE" trigger on "EMP" table as below:
	REF CURSORS in Oracle by Gayathri Sathiamoorthy - Oct 05, 2006 Lets say that we have the bank transaction tables like this, transaction_2005, transaction_2006 that stores the transactions of the respective years. (Both the tables have identical columns).
	Cursor for loop by Gayathri Sathiamoorthy - Sep 12, 2006 In most of the explicit cursors, the programmer tends to open a cursor, fetch it and loop through it and finally close the cursor. A cursor FOR loop could be used instead of these operations. A cursor FOR Loop implicitly declares its loop index as a record that represents a row fetched from the database.
	Cursor for update by Gayathri Sathiamoorthy - Sep 11, 2006 In most of the cases, the row fetched by the cursor query is updated inside the cursor loop. When an update occurs, the updated row is locked by Oracle. When the ‘FOR UPDATE’ clause is given in the cursor, oracle locks the rows that satisfy the where condition during opening of the cursor.
	Cursor in Oracle by Gayathri Sathiamoorthy - Sep 07, 2006 It is very important for a programmer/developer to understand what cursor is. The article below explain cursor with example.
Oracle and PHP
	Compile PHP with Oracle and Apache by Vigyan Kaushik - Dec 27, 2005 How to compile php with apache and oracle client?
Oracle Application Server
Oracle XML DB
Career
	Back to Basics: 8 things to remember when looking for your next job by Michael - May 25, 2007 Over my 20 year career, I have hired and managed over 50 DBAs. While the market for DBAs is certainly improving, getting a new or better job should not be taken lightly.
	Resume - 9 common mistakes by dbapool Editor - Feb 26, 2007 Most of us update a resume, apply for a job, and 1 out of 4 times end up getting a job that we do not like for various reasons.
	10 Tips for Effective Resume Writing by dbapool Editor - Feb 23, 2007 Here are 10 Tips for Effective Resume Writing.
System Administration
	Logging Out Inactive Users Automatically by Sachin - Mar 21, 2007 In a multiuser environment, many times users log in to the system and forget to log out(or involved with another work, keeping current login active).

Home Discussion Forum FAQs Articles Jobs Newsletters Directory Downloads

Our Premium Sponsor