Subqueries

A subquery  places one query inside another one. The second query resides somewhere within the WHERE clause of a SELECT statement. One or more values returned by the subquery are used by the main query to return the results to the user Subquery: A query that is embedded in a main, or parent, query and used [...]

Continue reading →

Equijoins in Oracle

Equijoins are also commonly known as simple joins, or inner joins. Given two or more tables, an equijoin will return the results of these tables where a common column between any given pair of tables has the same value (an equal value). Equijoins are typically joins between foreign keys in one table to a primary [...]

Continue reading →

Join Syntax in Oracle

So far, we have been dealing with only one table at a time in our SQL query examples. But typically the information needed to satisfy a user query requires more than one table. In new SQL 1999, you can not only  join two or more tables in a number of different ways, but can also [...]

Continue reading →

ROLLUP and CUBE Operators in Oracle

Sometimes, a simple GROUP BY clause just isn’t enough in a query. Once you generate a report of, let’s say, average salary by department or the standard deviation of sick days by job title, you often must run a second query that calculates the average salary or standard deviation across the entire set of employees. [...]

Continue reading →

OEM Event Manager

OEM automatically alert the DBA, through an e-mail message or page, to error conditions or conditions that may signal an impending error. Using OEM’s Event Manager, accessible as one of the nodes in the OEM Navigator pane, the DBA can monitor a variety of error conditions, such as an abnormal termination of the Oracle instance [...]

Continue reading →

The Alert Log File in Oracle

The alert log file is a grab bag of messages about the state of the database instance. It contains entries about significant database events, such as database startup and shutdown, nondefault initialization parameters, ALTER SYSTEM commands, and various errors. Alert Log File  :  A text file that contains entries about significant database events, such as [...]

Continue reading →

Recovery Manager in Oracle

The Recovery Manager (RMAN)  tool is an extensive and comprehensive set of tools that can streamline the backup and recovery of a database. It can be accessed via either a command line or a GUI interface through OEM by selecting Tools Database Tools Backup Management Backup. Using RMAN can reduce errors by automating many of [...]

Continue reading →

Log Miner in Oracle

  Oracle Log Miner is a  tool the DBA can use to view past activity in the database. The Log Miner tool can help the DBA find changed records in redo log files by using a set of PL/SQL procedures and functions. Log Miner extracts all DDL and DML activity from the redo log files for [...]

Continue reading →

Hot Backups in Oracle

A hot backup is similar to a cold backup, except that the backup is performed while the database is open and available to users. A hot backup is also known as an open backup .  Hot backups are performed on one tablespace at a time. They are better than cold backups in that the database [...]

Continue reading →

Cold Backups in Oracle

A database cold backup is most likely the simplest way to make a backup of a database. A cold backup consists of making copies of the datafiles, the control files, and the initialization parameter files, or SPFILEs, while the database is shut down. A cold backup is also known as a closed backup.  Cold backups [...]

Continue reading →

Export and Import for DBAs

 The DBA can use additional features of the Export (EXP) and Import (IMP) utilities that a user can use to save and restore database objects.for backing up all user objects in the database or to copy a tablespace to another database. The tablespace copy feature, introduced in Oracle9i, is known as transportable tablespaces. It is [...]

Continue reading →

SPFILE in Oracle

An SPFILE (Server Parameter File), new to Oracle9i, is a binary file. The file is not meant to be modified manually and must always reside on the server side. After the file is created it is maintained by the Oracle server. If modified manually, the SPFILE is rendered useless. The SPFILE provides the ability to [...]

Continue reading →

PFILE in Oracle

The PFILE (initSID.ora)  is a text file that can be maintained using a standard operating system editor. The PFILE is read only during instance startup. If the file is modified, the instance must be shut down and restarted in order to make the new parameter values effective.  By default, the PFILE is located in the [...]

Continue reading →