Oracle Database Tips And Tricks: 2012

Infosys TechnicalInterview Questions 2012

1. which language is used to develop the oracle ?

2. how to retrieve daily sal from emp table in oracle 10g ?

3. structural difference between bitmap and btree index ?

4. what is difference between foreign key and reference key ?

5. What are Clusters ?

6. How to create DSN..Explain?

7. What's the advantage of using System.Text.StringBuilder over System.String?

8. What are Sticky Sessions?

9. what is code access security,role based security explain with example ?

10. What is role manager work in web.config? how to restrict perticular pages from the users using the role manager?

11. Explain current thinking around IClonable.

12. What is the difference between Debug.Write and Trace.Write? When should each be used?

13. What is the difference between an EXE and a DLL?

14. Difference between Htttppost and Httpget Methods?

15. what is strong name?

16. what are partial postbacks?

17. What is boxing and unboxing ?

18. You are given two candles of equal size, which can burn 1 hour each. You have to measure 90 minutes with these candles. (There is no scale or clock). Also u r given a lighter.

Ans: 1. First light up the two ends of the 1st candle. When it will burn out light up one end of the second candle. (30+60=90)

19. Try the similar problem to measure 45 minutes.

Ans: First light-up the two ends of the 1st candle and one end of the 2nd candle.

When the 1st candle will burn out ,then light up the both ends of the 2nd candle
(15+30=45)

20. You r given a thermometer. What can u do by this without measuring the m temperature?

Ans: if u put thermometer into a tree it won?t grow anymore, will just die off

21. How it is possible to place four points that are equidistance from each other?

OR
U r a landscape designer and your boss asked u to design a landscape such
that you should place 4 trees equidistance from each other.
(Distance from each tree to the other must be same)

Ans: Only 3 points can be equidistant from each other. But if u place points in the shape of a pyramid then its possible

22. You are given a cake; one of its corner is broken. How will u cut the rest into Two equal parts?

Ans: Slice the cake

23. How will you recognize the magnet & magnetic material & non-magnetic material?

Ans: Drag one piece of material over another. There is no attractive force in the middle portion of the magnet.

OR
Get a piece of thread and tie up with the one bar and check for poles. If it iron bar then it moves freely and if it is magnetic bar then it fix in one direction according to poles.

24. If one tyre of a car suddenly gets stolen.... and after sometime u find the tyre without the screws how will u make ur journey complete?

Ans: Open 3 screws, 1 from each tyre and fix the tyre.

25. How can u measure a room height using a thermometer?

Ans: temp varies with height. but its dependent on various other factors like humidity, wind etc.

26. What is the height of room if after entering the room with a watch ur head strikes a hanging bulb?

Ans: Oscillate the hanging bulb. Calculate the time period for one complete oscillation by Simple Harmonic Motion (SHM) of the handing bulb. Put it in the formula T=2 * 3.14 * (L/G)^1/2 L will be the length of the hanging thread. Add the L with ur height to get the height of the room.

OR
Ans: Drop it from the room and find the time at which it strikes the floor. Using physics formula s = (at^2)/2 (IM NOT SURE ABOUT THIS ONE)

27. Color of bear.... if it falls from 1m height in 1s.

Ans: We get 'g' perfect 10 which is only in poles...hence polar bear...color White

28. How will you measure height of building when you are at the top of the building? And if you have stone with you.

Ans: Drop the stone and find the time taken for the stone to reach the ground. find height using the formula s = a + gt ( s = height, a= initial velocity=0, g=9.8m/s, t = time taken)

29. How wud u catch and receive a ball in same direction? (Dropping is from north And receiving from bottom not accepted, as it is 2 directions)

30. 25 statements given. Some tell truth, some false and some alternators. Find out the true statements.

RMAN Hot Backup Script

The following unix shell script will do a full RMAN hot backup to your database and will copy the backup files compressed to the directory you will specify. This is a hot-backup, and the database must to be in ARCHIVELOG mode for this to work.

A backup retention policy with a recovery window of 2 days is defined in this script. With this retention policy RMAN will keep archive logs and backup files necessary to recover to any point of time within those 2 days in the recovery window. Older backups and archivelogs not needed to satisfy this retention policy will be automatically deleted by this script. No RECOVERY CATALOG is being used with this script, instead database controlfiles are used to record the RMAN repository information.

Make sure you set the rman CONFIGURE CONTROLFILE AUTOBACKUP parameter to ON in in RMAN in order to take extra backups of the controlfile and spfile (RMAN will not backup init.ora files) as extra protection.

This script will delete obsolete backups and not needed archive logs from the disks only after a successful backup. This means you don’t need to set up cronjobs or manually delete not needed backups. This is the beauty of using RMAN. It does this automatically with the commands: “…DELETE NOPROMPT OBSOLETE;…” and “…DELETE NOPROMPT EXPIRED BACKUP…” See last lines in the script.

The RMAN hot backup script rman_backup.sh

# Declare your ORACLE environment variables
export ORACLE_SID= (put your SID here)
export ORACLE_BASE= (put your ORACLE BASE here)
export ORACLE_HOME= (put your ORACLE_HOME here)
export PATH=$PATH:${ORACLE_HOME}/bin

# Start the rman commands
rman target=/ << EOF
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/(put backup directory here)/autobackup_control_file%F’;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
run {
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
CROSSCHECK BACKUP;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT ‘/(put backup directory here)/databasefiles_%d_%u_%s_%T’;
sql ‘ALTER SYSTEM ARCHIVE LOG CURRENT’;
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT ‘/(put backup directory here)/archivelogs_%d_%u_%s_%T’ DELETE INPUT;
BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE FORMAT ‘/(put backup directory here)/controlfile_%d_%u_%s_%T’;
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
RELEASE CHANNEL RMAN_BACK_CH01;
}
EXIT;
EOF
#

Note: If you run this script on a database which was being backed up using OS methods and user managed backups, initially it will fail as it will look to satisfy the ‘… ARCHIVELOG ALL…” clause. That is it will try to backup archivelogs since day 1. Well, unless you have lots of money to allocate for storage, we know that is not practical to keep all archivelogs and with OS backups usually we delete them manually according to the chosen backup retention pollicy. The script once run initially will fail like this:

Starting backup at 24-MAR-08
current log archived
using channel ORA_DISK_1
archived log /u00/arch/1_2_650282994.arc not found or out of sync with catalog
trying alternate file for archivelog thread 1, sequence 2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/24/2008 10:43:29
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u01/arch/1_2_650282994.arc
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

As you can see the script assumes that you have all archivelogs, if that is not the case (most likely), and you have been periodically deleting them ie. crontab, or manually, you can always sync the existing RMAN catalog (or controlfile) by crosschecking what is there on the disk before you run it with the following command from RMAN:
For before Oracle 9i

RMAN> change archivelog all crosscheck;

or in Oracle 9i,10g

RMAN> crosscheck archivelog all;

This will crosscheck existing archivelogs and will show you the ones which are EXPIRED and need to be deleted from the RMAN catalog, that is, they are not on the disks anymore, cause you have deleted them not using RMAN. RMAN doesn’t know this, it expects them! To delete these misleading entries from your control file you will have to run the command:

RMAN> delete expired archivelog all;

After you delete the expired archivelog records from the control file you can run the script and take your backups.

To see how a recovery is done with a backup taken with this script on a New Host with the same directory structure search this blog for RMAN Recovery.

The RMAN hot backup script rman_backup.sh

RMAN Hot backup—unix script
*******************************************

The RMAN hot backup script rman_backup.sh:
# !/bin/bash

# Declare your environment variables
export ORACLE_SID= (put your SID here)
export ORACLE_BASE= (put your ORACLE BASE here)
export ORACLE_HOME= (put your ORACLE_HOME here)
export PATH=$PATH:${ORACLE_HOME}/bin

# Start the rman commands
rman target=/ << EOF
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/(put backup directory here)/autobackup_control_file%F’;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
run {
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
CROSSCHECK BACKUP;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT ‘/(put backup directory here)/databasefiles_%d_%u_%s_%T’;
sql ‘ALTER SYSTEM ARCHIVE LOG CURRENT’;
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT ‘/(put backup directory here)/archivelogs_%d_%u_%s_%T’ DELETE INPUT;
BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE FORMAT ‘/(put backup directory here)/controlfile_%d_%u_%s_%T’;
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
RELEASE CHANNEL RMAN_BACK_CH01;
}
EXIT;
EOF
#

RMAN Backup and Recovery Example

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

I have been using this to backup and recover database. I thought as usual, I should share it.
This is a simple script and should be used with care. This assumes a full backup and recover. It does not take other scenarios into consideration.

RMAN Backup Script

I assume you are running Unix.

This script will help you. Save it as shell script (e.g rman_backup.sh) and make required changes.

# Change and to your own (e.g /u01/oracle/backup)

#!/usr/bin/sh

export ORACLE_SID=TEST
export ORACLE_HOME=$ORACLE_HOME

# Add date to be used in logfile
export TDAY=`date +%a`
export backup_dir = /u01/oracle/backup
export LOGFILE=$backup_dir/$SID_clone.log

echo "Backup Started at `date` \n" >$LOGFILE

$ORACLE_HOME/bin/rman <<'!' 1>> $LOGFILE 2>&1

# Connect to the database. Change this to Sys logon if not using /

connect target /

# Allocate Disk channels. Allocate more if you have enough max process to use

run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;

#backup the whole source database.
# Use tags for easy separation from other backups during restore
backup
tag whole_database_open
format '$backup_dir/df_%u'
database;

# switch out of the current logfile
sql 'alter system archive log current';

#backup the archived logs
backup
archivelog all
format '$backup_dir/al_%u';

# backup a copy of the controlfile that contains records for the backups just made
backup
current controlfile
tag = cf1
format '$backup_dir/cf_%u';

}
exit

echo "Backup Finished at `date` \n" >>$LOGFILE

RMAN Recovery Script

This one of the routines I use to clone my database from one Server to another. so it may help you, but you have to use it with caution .
hope you are a DBA and can make all the required changes to the scripts. Whereever you see $, it means run from OS. RMAN> means run from RMAN

--I assume the RMAN Backup has been restored to the DISK as well
--I assume your system is Unix
--I assume you will run the commands manually (e.g. copy and paste). If you can script them, thats ok.

Esnure you Rebuild all your configuration as before
Set all your ORACLE_HOME etc.
Prepare all your init ora file as before (restore a previous copy is possible)

Create all the starting mount point as it was for your datafiles.

Logon to RMAN
$ORACLE_HOME/bin/rman

Run the following command. This part can also be scripted if required.

RMAN> connect target /

Startup the Instance with nomount

RMAN> startup nomount;

# add the init parameter file to the above if not on default location

#If you have or know DBID, set the DBID

RMAN> set dbid

Identify and Restore the control file.

RMAN> RESTORE CONTROLFILE FROM 'mount_point//';

Create a password file

$create password file orapwd file=$ORACLE_HOME/dbs/orapw password=

Modify the script below to use to restore the database

export ORACLE_SID=
export ORACLE_HOME==$ORACLE_HOME

$ORACLE_HOME/bin/rman

connect target /

# Mount the database

alter database mount;

# Allocate Disk channels.

RMAN> run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;

restore database;

# If your rman used a tag,add "from tag " after database above

}

# Check the LOGFILE for errors

# Recover the Database

RMAN> run {
SET UNTIL logseq = thread = 1;
RECOVER DATABASE;
}

# Add Temp files because your backup will not have them
# Example

ALTER TABLESPACE "TEMP"
ADD TEMPFILE '//<_tempfile_name>' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 4000M;

# Run reset logs

RMAN> alter database open resetlogs;

SQL Tutorial, explaining the theory behind user creation using the CREATE USER command. It also introduces the concept of a tablespace....presented by www.oraclecoach.com

1. Which types of backups you can take in Oracle?

2. A database is running in NOARCHIVELOG mode then which type of backups you can take?

3. Can you take partial backups if the Database is running in NOARCHIVELOG mode?

4. Can you take Online Backups if the database is running in NOARCHIVELOG mode?

5. How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?

6. You cannot shutdown the database for even some minutes, then in which mode you should run the database?

7. Where should you place Archive log files, in the same disk where DB is or another disk?

8. Can you take online backup of a Control file if yes, how?

9. What is a Logical Backup?

10. Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?

11. Why do you take tablespaces in Backup mode?

12. What is the advantage of RMAN utility?

13. How RMAN improves backup time?

14. Can you take Offline backups using RMAN?

15. How do you see information about backups in RMAN?

16. What is a Recovery Catalog?

17. Should you place Recovery Catalog in the Same DB?

18. Can you use RMAN without Recovery catalog?

19. Can you take Image Backups using RMAN?

20. Can you use backupsets created by RMAN with any other utility?

21. Where RMAN keeps information of backups if you are using RMAN without Catalog?

22. You have taken a manual backup of a datafile using o/s. How RMAN will know about it?

23. You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?

24. Which is more efficient Incremental Backups using RMAN or Incremental Export?

25. Can you start and shutdown DB using RMAN?

26. How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?

27. You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?

28. You lost some datafiles and you don't have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?

29. How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?

30. You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file?

31. You loss controlfile how do you recover from this?

32. The current logfile gets damaged. What you can do now?

33. What is a Complete Recovery?

34. What is Cancel Based, Time based and Change Based Recovery?

35. Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?

36. Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?

37. A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don't have any backups?

38. How do you recover from the loss of a controlfile if you have backup of controlfile?

39. Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?

40. Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?

41. Have you faced any emergency situation? Tell us how you resolved it?

42. At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the parameters set to previous values.

dba interview questions

1. What is an Oracle Instance?

An oracle instance is a means to access an oracle database . Consists of the SGA memory structures and the background processes that are used to manage a database .

2. What information is stored in Control File?

A control file contains :

=>Database name and identifier

=>Timestamp of database creation

=>Tablespace names

=>Names and locations of datafiles and online redolog files

=>Current online redolog file sequence number

=>Checkpoint information

=>Begin and end of undo segments

=>Redolog archive information

=>Backup information

3. When you start an Oracle DB which file is accessed first?

Parameter File

4. What is the Job of SMON, PMON processes?

SMON

1.Instance Recovery(Rolls forward changes in online redolog files, opens the database for user access and rolls back uncommitted transactions)

2.Coalesce free space

3.Deallocates Temporary segments.

PMON(Cleans up after failed processes by:)

1.Rolling back the transaction

2.Releasing locks

3.Releasing other resources

4.Restarting dead dispatchers.

5. What is Instance Recovery?

When an Oracle instance fails,Oracle performs an instance recovery when the associated database is re-started.

Instance recovery occurs in two steps:

Cache recovery: Changes being made to a database are recorded in

the database buffer cache. These changes are also recorded in online redo log

files simultaneously. When there are enough data in the database buffer cache,

they are written to data files. If an Oracle instance fails before the data in

the database buffer cache are written to data files, Oracle uses the data

recorded in the online redo log files to recover the lost data when the

associated database is re-started. This process is called cache recovery.

Transaction recovery: When a transaction modifies data in a

database, the before image of the modified data is stored in an undo segment.

The data stored in the undo segment is used to restore the original values in

case a transaction is rolled back. At the time of an instance failure, the

database may have uncommitted transactions. It is possible that changes made by

these uncommitted transactions have gotten saved in data files. To maintain

read consistency, Oracle rolls back all uncommitted transactions when the

associated database is re-started. Oracle uses the undo data stored in undo

segments to accomplish this. This process is called transaction recovery.

6. What is written in Redo Log Files?

Online redolog files provides a means to redo transactions in the event of a database failure . Every transaction is synchronously written to the redolog buffer ,then gets flushed to the online redolog files in order to provide a recovery mechanism in case of media failure. This includes transactions that have not yet been committed ,undo segment information , schema and object management statements. Online redolog files are used in situation such as instance failure to recover committed data that has not yet been written to data files .The online redolog files are used only for recovery .

7. How do you control number of Datafiles one can have in an Oracle database?

8. How many Maximum Datafiles can there be in an Oracle Database?

9. What is a Tablespace?

A tablespace is a logical storage unit in Oracle Database which collectively stores all of the database’s data. Each tablespace in an oracle database consists of one or more files called datafiles .A tablespace can belong to only one database at a time .A tablespace is further divided into logical units of storage.

10. What is the purpose of Redo Log files?

11. Which default Database roles are created when you create a Database?

Sysdba and sysoper

12. What is a Checkpoint?

A checkpoint is an operation that Oracle performs to ensure data file consistency. When a checkpoint occurs, Oracle ensures all modified buffers are written from the data buffer to disk files. Frequent checkpoints decrease the time necessary for recovery should the database crash, but may decrease overall database performance.

A checkpoint performs the following three operations:

1.) Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk. It's the DBWR that writes all modified databaseblocks back to the datafiles.

2.) The latest SCN is written (updated) into the datafile header.

3.) The latest SCN is also written to the controlfiles.

The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Checkpoints occur in a variety of situations. For example, Oracle Database uses the following types of checkpoints:

1.) Thread checkpoints : The database writes to disk all buffers modified by redo in a specific thread before a certain target. The set of thread checkpoints on all instances in a database is a database checkpoint. Thread checkpoints occur in the following situations:

Consistent database shutdown .

ALTER SYSTEM CHECKPOINT statement .

Online redo log switch .

ALTER DATABASE BEGIN BACKUP statement

2.) Tablespace and data file checkpoints : The database writes to disk all buffers modified by redo before a specific target. A tablespace checkpoint is a set of data file checkpoints, one for each data file in the tablespace. These checkpoints occur in a variety of situations, including making a tablespace read-only or taking it offline normal, shrinking a data file, or executing ALTER TABLESPACE BEGIN BACKUP.

3.) Incremental checkpoints : An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every three seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers.

Other types of checkpoints include instance and media recovery checkpoints and checkpoints when schema objects are dropped or truncated.

Importance of Checkpoints for Instance Recovery :

Instance recovery uses checkpoints to determine which changes must be applied to the data files. The checkpoint position guarantees that every committed change with an SCN lower than the checkpoint SCN is saved to the data files.

Checkpoint Position in Online Redo Log File

During instance recovery, the database must apply the changes that occur between the checkpoint position and the end of the redo thread. As shown in Figure, some changes may already have been written to the data files. However, only changes with SCNs lower than the checkpoint position are guaranteed to be on disk.

Time and SCN of last checkpoint :

The date and time of the last checkpoint can be retrieved through checkpoint_time in v$datafile_header view

The SCN of the last checkpoint can be found in v$database.

13. Which Process reads data from Datafiles?

Server process

14. Which Process writes data in Datafiles?

Database writer

15. Can you make a Datafile auto extendible. If yes, how?

16. What is a Shared Pool?

17. What is kept in the Database Buffer Cache?

Database Buffer cache is one of the most important components of System Global Area (SGA). Database Buffer Cache is the place where data blocks are copied from datafiles to perform SQL operations. Buffer Cache is shared memory structure and it is concurrently accessed by all server processes.

18. How many maximum Redo Logfiles one can have in a Database?

19. What is difference between PFile and SPFile?

20. What is PGA_AGGREGRATE_TARGET parameter?

You can automatically and globally manage the size of SQL work areas. The database administrator simply needs to specify the total size dedicated to PGA memory for the Oracle instance by setting the initialization parameter PGA_AGGREGATE_TARGET. The specified number (for example, 2G) is a global target for the Oracle instance, and Oracle tries to ensure that the total amount of PGA memory allocated across all database server processes never exceeds this target. With PGA_AGGREGATE_TARGET, sizing of work areas for all dedicated sessions is automatic. At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated by other components of the system (for example, PGA memory allocated by sessions). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirement. There are fixed views and columns that provide PGA memory use statistics. Most of these statistics are enabled when PGA_AGGREGATE_TARGET is set.

• Statistics on allocation and use of work area memory can be viewed in the following dynamic performance views:

V$SYSSTAT

V$SESSTAT

V$PGASTAT

V$SQL_WORKAREA

V$SQL_WORKAREA_ACTIVE

• The following three columns in the V$PROCESS view report the PGA memory allocated and used by an Oracle process:

PGA_USED_MEM

PGA_ALLOCATED_MEM

PGA_MAX_MEM

21. Large Pool is used for what?

The database administrator can configure an optional memory area called the large pool to provide large memory allocations for:

• Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database)

• I/O server processes

• Oracle backup and restore operations

By allocating session memory from the large pool for shared server, Oracle XA, or parallel query buffers, Oracle can use the shared pool primarily for caching shared SQL and avoid the performance overhead caused by shrinking the shared SQL cache.

In addition, the memory for Oracle backup and restore operations, for I/O server processes, and for parallel buffers is allocated in buffers of a few hundred kilobytes. The large pool is better able to satisfy such large memory requests than the shared pool.

The large pool does not have an LRU list. It is different from reserved space in the shared pool, which uses the same LRU list as other memory allocated from the shared pool.

22. What is PCT Increase setting?

Specify the percent by which the third and subsequent extents grow over the preceding extent. The default value is 50, meaning that each subsequent extent is 50% larger than the preceding extent. The minimum value is 0, meaning all extents after the first are the same size. The maximum value depends on your operating system. Oracle rounds the calculated size of each new extent to the nearest multiple of the data block size. If you change the value of the PCTINCREASE parameter (that is, if you specify it in an ALTER statement), then Oracle calculates the size of the next extent using this new value and the size of the most recently allocated extent. If you want to keep all extents the same size, you can prevent the SMON background process from coalescing extents by setting the value of PCTINCREASE to 0. In general, Oracle recommends a setting of 0 as a way to minimize fragmentation and avoid the possibility of very large temporary segments during processing. You cannot specify PCTINCREASE for rollback segments. Rollback segments always have a PCTINCREASE value of 0.

23. What is PCTFREE and PCTUSED Setting?

The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block.

The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block.

24. What is Row Migration and Row Chaining?

25. What is 01555 - Snapshot Too Old error and how do you avoid it?

You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

26. What is a Locally Managed Tablespace?

27. Can you audit SELECT statements?

28. What does DBMS_FGA package do?

The DBMS_FGA package provides fine-grained security functions. Execute privilege on DBMS_FGA is needed for administering audit policies. Because the audit function can potentially capture all user environment and application context values, policy administration should be executable by privileged users only. This package is available for only cost-based optimization. The rule-based optimizer may generate unnecessary audit records since audit monitoring can occur before row filtering. For both the rule-based optimizer and the cost-based optimizer, you can refer to DBA_FGA_AUDIT_TRAIL to analyze the SQL text and corresponding bind variables that are issued.

29. What is Cost Based Optimization?

30. How often you should collect statistics for a table?

31. How do you collect statistics for a table, schema and Database?

32. Can you make collection of Statistics for tables automatic?

33. On which columns you should create Indexes?

34. What type of Indexes are available in Oracle?

35. What is B-Tree Index?

36. A table is having few rows, should you create indexes on this table?

37. A Column is having many repeated values which type of index you should create on this column, if you have to?

38. When should you rebuilt indexes?

39. Can you built indexes online?

40. Can you see Execution Plan of a statement.

41. A table is created with the following setting

storage (initial 200k

next 200k

minextents 2

maxextents 100

pctincrease 40)

What will be size of 4th extent?

42. What is DB Buffer Cache Advisor?

43. What is STATSPACK tool?

44. Can you change SHARED_POOL_SIZE online?

45. Can you Redefine a table Online?

46. Can you assign Priority to users?

47. You want users to change their passwords every 2 months. How do you enforce this?

48. How do you delete duplicate rows in a table?

49. What is Automatic Management of Segment Space setting?

50. What is the difference between DELETE and TRUNCATE statements?

51. What is COMPRESS and CONSISTENT setting in EXPORT utility?

52. What is the difference between Direct Path and Convention Path loading?

53. Can you disable and enable Primary key?

54. What is an Index Organized Table?

55. What is a Global Index and Local Index?

56. What is the difference between Range Partitioning and Hash Partitioning?

57. What is difference between Multithreaded/Shared Server and Dedicated Server?

58. Can you import objects from Oracle ver. 7.3 to 9i?

59. How do you move tables from one tablespace to another tablespace?

60. How do see how much space is used and free in a tablespace?

dba_constraints/ user_constraints/ all_constraints queries

Simply list of constranits along with the constraint_type you can achieve querying from dba_constraints/ user_constraints/ all_constraints.

SQL> select owner, constraint_name, constraint_type from dba_constraintswhere owner='&Owner_name' and table_name='&Tab_name';
Output:

In the column constraint_type there may have values C,P,U,R,V and O which means,
1) C :check constraint on a table
2) P :primary key
3) U :unique key
4) R :referential integrity
5) V :with check option, on a view
6) O :with read only, on a view

You can get constraints along with their columns and position by querying from DBA_CONS_COLUMNS/ USER_CONS_COLUMNS/ ALL_CONS_COLUMNS.

SQL> col owner for a10
col constraint_name for a27
col table_name for a25
col column_name for a23
set pages 100
SQL> select * from dba_cons_columns where table_name='&Tab_name' and owner='&Owner_name' order by constraint_name, position;
By joining both two views you can get a list of constraints , their type, column_name, column position and their reference constraint name, reference table name in the constraint by,

SQL> col r_owner for a10
SQL> Select c.constraint_name, cc.column_name, c.r_owner,c.r_constraint_name,c.constraint_type,cc.position, r.table_namefrom dba_constraints c JOIN dba_cons_columns cc ON(c.table_name=cc.table_name AND c.owner=cc.ownerAND c.constraint_name=cc.constraint_name) LEFT JOIN dba_constraints rON(c.r_constraint_name=r.constraint_name AND r.constraint_type in ('P','U') ) where c.table_name='&Tab_name' and c.owner='&Owner_name' order byconstraint_name, position;
Thanks.

Type of constraints in oracle

Constraint in oracle imposes rule that restrict the values in a oracle database. There are six types of constraint in oracle database and all these constraints except not null constraint can be declared in two ways.

A brief description of these six types of constraints are listed below.

1)Not Null Constraint: If I define a field not null then value must be inserted in to that column.

2)Unique Constraint: If I define a column or set of column to be unique then it prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

3)Primary Key Constraint: Primary key constraint is the combination of not null constraint and unique constraint. In a single declare it satisfy both constraint.

4)Foreign Key Constraint: Foreign key constraint requires values in one table to match values in another table.

5)Check Constraint: Check constraint imposes restriction of values based on specified condition.

6)REF Constraint: A REF constraint lets you further describe the relationship between the REF column and the object it references.

Understanding Execution Plan Statistics

Below is the output of an execution plan statistics.
SQL> set autot trace statistics
SQL> select * from tab;

107 rows selected.

Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
898 consistent gets
0 physical reads
0 redo size
3459 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed

You sometimes want to know what these fields indicates. Below is the details of these fields.

1)recursive calls: For processing of sql statements oracle database maintain internal tables. Sometimes for processing sql statements change is needed in the internal tables and sometimes not. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.

2)db block gets: Number of times a CURRENT block was requested.

3)consistent gets: Number of times a consistent read was requested for a block. This is called the logical reads indicates for processing of a query how many blocks needs to be accessed.

4)physical reads: Total number of data blocks read from disk. This number equals the value of "physical reads direct" + all reads into buffer cache.

5)redo size: For processing of a query total amount of redo generated in bytes.

6)bytes sent through SQL*Net to client: Total number of bytes sent to the client from the foreground processes.

7)bytes received through SQL*Net from client: Total number of bytes received from the client over Oracle Net.

8)SQL*Net round-trips to/from client: Total number of Oracle Net messages sent to and received from the client.
9)sorts (memory):Number of sort operations that were performed completely in memory and did not require any disk writes.

10)sorts (disk):Number of sort operations that required at least one disk write.

11)rows processed: Number of rows processed during the operation.

Thanks.

Top SQL query by cpu for MSSQL Server

Check the fantastic query below to find the 25 top most cpu intensive queries for a SQL Server Instance.
select top 25 * from (SELECT pa.value AS dbid,
COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value as int))+'*') AS DBNAME,
SUBSTRING(text,
-- starting value for substring
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
-- ending value for substring
CASE WHEN statement_end_offset = 0
OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END + 1
) AS TSQL, total_worker_time,execution_count,
total_worker_time/execution_count avg_cpu,total_physical_reads/execution_count avg_physical_read,
total_elapsed_time/execution_count avg_elapsed_time,total_logical_reads/execution_count avg_logical_read
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
WHERE attribute = 'dbid') a where a.dbid>4 order by avg_cpu desc;

Let me know any problem on execution.
Thanks.

All About V$ views

This can help a lot to an Oracle DBA to get info about different views:

Advisors: Information related to cache advisors

V$PGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE_HISTOGRAM
V$MTTR_TARGET_ADVICE
V$PX_BUFFER_ADVICE
V$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE
V$JAVA_POOL_ADVICE
V$STREAMS_POOL_ADVICE (10.2)
V$SGA_TARGET_ADVICE (10.2)
V$ADVISOR_PROGRESS (10.2)

ASM

V$ASM_ALIAS (10.1)
V$ASM_CLIENT(10.1)
V$ASM_DISK(10.1)
V$ASM_DISK_STAT(10.2)
V$ASM_DISKGROUP(10.1)
V$ASM_DISKGROUP_STAT(10.2)
V$ASM_FILE(10.1)
V$ASM_OPERATION(10.1)
V$ASM_TEMPLATE(10.1)

Backup/recovery Information related to database backups and recovery including last backup,archive logs,state of files for backup,and recovery

V$ARCHIVE
V$ARCHIVED_LOG
V$ARCHIVE_DEST
V$ARCHIVE_DEST_STATUS
V$ARCHIVE_GAP
V$ARCHIVE_PROCESSES
V$BACKUP
V$BACKUP_ASYNC_IO
V$BACKUP_CORRUPTION
V$BACKUP_DATAFILE
V$BACKUP_DEVICE
V$BACKUP_PIECE
V$BACKUP_REDOLOG
V$BACKUP_SET
V$BACKUP_SYNC_IO
V$BLOCK_CHANGE_TRACKING
V$COPY_CORRUPTION
V$DATABASE_BLOCK_CORRUPTION
V$DATABASE_INCARNATION
V$DATAFILE_COPY
V$DELETED_OBJECT
V$FAST_START_SERVERS
V$FAST_START_TRANSACTIONS
V$INSTANCE_RECOVERY
V$MTTR_TARGET_ADVICE
V$PROXY_ARCHIVEDLOG
V$PROXY_DATAFILE
V$RMAN_CONFIGURATION
V$RECOVERY_FILE_STATUS
V$RECOVERY_LOG
V$RECOVERY_PROGRESS
V$RECOVERY_STATUS
V$RECOVER_FILE
V$BACKUP_ARCHIVELOG_DETAILS(10.2)
V$BACKUP_ARCHIVELOG_SUMMARY(10.2)
V$BACKUP_CONTROLFILE_DETAILS(10.2)
V$BACKUP_CONTROLFILE_SUMMARY(10.2)
V$BACKUP_COPY_DETAILS(10.2)
V$BACKUP_COPY_SUMMARY(10.2)
V$BACKUP_FILES(10.1)
V$BACKUP_PIECE_DETAILS(10.2)
V$BACKUP_SET_DETAILS(10.2)
V$BACKUP_SET_SUMMARY(10.2)
V$BACKUP_SPFILE
V$BACKUP_SPFILE_DETAILS(10.2)
V$BACKUP_SPFILE_SUMMARY(10.2)
V$DATAFILE_HEADER
V$FLASH_RECOVERY_AREA_USAGE(10.2)
V$FLASHBACK_DATABASE_LOG(10.1)
V$FLASHBACK_DATABASE_STAT(10.1)
V$OBSOLETE_BACKUP_FILES
V$OFFLINE_RANGE
V$PROXY_ARCHIVELOG_DETAILS(10.2)
V$PROXY_ARCHIVELOG_SUMMARY(10.2)
V$PROXY_COPY_DETAILS(10.2)
V$PROXY_COPY_SUMMARY(10.2)
V$RECOVERY_FILE_DEST(10.1)
V$RESTORE_POINT(10.2)
V$RMAN_BACKUP_JOB_DETAILS(10.2)
V$RMAN_BACKUP_SUBJOB_DETAILS(10.2)
V$RMAN_BACKUP_TYPE(10.2)
V$RMAN_ENCRYPTION_ALGORITHMS(10.2)
V$RMAN_OUTPUT(10.1)
V$RMAN_STATUS(10.1)
V$UNUSABLE_BACKUPFILE_DETAILS(10.2)

Caches Information related to the various caches, including objects, library, cursors, and the dictionary

V$ACCESS
V$BUFFER_POOL
V$BUFFER_POOL_STATISTICS
V$DB_CACHE_ADVICE
V$DB_OBJECT_CACHE
V$JAVA_POOL_ADVICE
V$LIBRARYCACHE
V$LIBRARY_CACHE_MEMORY
V$PGASTAT
V$PGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE_HISTOGRAM
V$ROWCACHE
V$ROWCACHE_PARENT
V$ROWCACHE_SUBORDINATE
V$SESSION_CURSOR_CACHE
V$SGA
V$SGASTAT
V$SGA_CURRENT_RESIZE_OPS
V$SGA_DYNAMIC_COMPONENTS
V$SGA_DYNAMIC_FREE_MEMORY
V$SGA_RESIZE_OPS
V$SGAINFO
V$SHARED_POOL_ADVICE
V$SHARED_POOL_RESERVED
V$SYSTEM_CURSOR_CACHE
V$SUBCACHE
V$JAVA_LIBRARY_CACHE_MEMORY(10.1)
V$PROCESS_MEMORY(10.2)
V$SGA_TARGET_ADVICE(10.2)

Cache Fusion/RAC

V$ACTIVE_INSTANCES
V$BH
V$CLUSTER_INTERCONNECTS(10.2)
V$CONFIGURED_INTERCONNECTS(10.2)
V$CR_BLOCK_SERVER
V$CURRENT_BLOCK_SERVER(10.1)
V$GC_ELEMENT
VGCSHVMASTER_INFO
V$GCSPFMASTER_INFO
V$GES_BLOCKING_ENQUEUE
V$GES_CONVERT_LOCAL
V$GES_CONVERT_REMOTE
V$GES_ENQUEUE
V$GES_LATCH
V$GES_RESOURCE
V$GES_STATISTICS
V$HVMASTER_INFO
V$INSTANCE_CACHE_TRANSFER
V$RESOURCE_LIMIT

Control files Information related to instance control files

V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION

Cursors/SQL statements Information related to cursors and SQL statements, including the open cursors, statistics, and actual SQL text

V$OPEN_CURSOR
V$SQL
V$SQLAREA
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES
V$SQL_BIND_DATA
V$SQL_BIND_METADATA
V$SQL_CURSOR
V$SQL_OPTIMIZER_ENV
V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL
V$SQL_REDIRECTION
V$SESSION_CURSOR_CACHE
V$SQL_SHARED_CURSOR
V$SQL_SHARED_MEMORY
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
V$SQL_WORKAREA_HISTOGRAM
V$SYSTEM_CURSOR_CACHE V$MUTEX_SLEEP(10.2)
V$MUTEX_SLEEP_HISTORY(10.2) (for those dubious shared latches)
V$SQL_BIND_CAPTURE(10.1)
V$SQL_JOIN_FILTER(10.2)
V$SQL_AREA_PLAN_HASH(10.2)
V$SQLSTATS(10.2)
V$SYS_OPTIMIZER_ENV(10.1)
V$VPD_POLICY

Database instances Information related to the actual database instance

V$ACTIVE_INSTANCES
V$BGPROCESS
V$DATABASE
V$INSTANCE
V$PROCESS
V$SGA
V$SGASTAT
V$BLOCKING_QUIESCE(10.2)
V$CLIENT_STATS(10.1)
RAC Views: V$BH
V$ACTIVE_INSTANCES

Direct Path Operations Information related to the SQL*Loader direct load option

V$LOADISTAT
V$LOADPSTAT

Distributed/Heterogeneous Services

V$DBLINK
V$GLOBAL_TRANSACTION
V$GLOBAL_BLOCKED_LOCKS
V$HS_AGENT
V$HS_PARAMETER
V$HS_SESSION

Fixed view Information related to the v$ tables themselves

V$FIXED_TABLE
V$FIXED_VIEW_DEFINITION
V$INDEXED_FIXED_COLUMN

General General information related to various system information

V$DBPIPES
V$CONTEXT
V$GLOBALCONTEXT
V$LICENSE
V$OPTION
V$RESERVED_WORDS
V$TIMER
V$TIMEZONE_NAMES
V$TYPE_SIZE
V$_SEQUENCES
V$VERSION
V$DB_TRANSPORTABLE_PLATFORM(10.2)
V$TRANSPORTABLE_PLATFORM(10.1)
V$SCHEDULER_RUNNING_JOBS(10.2)

I/O Information related to I/O, including files and statistics

V$DBFILE
V$FILESTAT
V$WAITSTAT
V$TEMPSTAT
V$FILE_HISTOGRAM(10.1)
V$FILEMETRIC(10.1)
V$FILEMETRIC_HISTORY(10.1)
V$SYSAUX_OCCUPANTS(10.1)
V$TABLESPACE
V$TEMP_HISTOGRAM(10.1)
V$TEMP_SPACE_HEADER
V$TEMPFILE
V$TEMPSEG_USAGE

Latches/locks Information related to latches and locks

V$ENQUEUE_LOCK
V$ENQUEUE_STAT
V$EVENT_NAME
V$FILE_CACHE_TRANSFER
V$GLOBAL_BLOCKED_LOCKS
V$LATCH
V$LATCHHOLDER
V$LATCHNAME
V$LATCH_CHILDREN
V$LATCH_MISSES
V$LATCH_PARENT
V$LOCK
V$LOCKED_OBJECT
V$RESOURCE
V$RESOURCE_LIMIT
V$TRANSACTION_ENQUEUE
V$_LOCK
V$_LOCK1
V$ENQUEUE_STATISTICS

FOLLOWING ARE RAC VIEWS:

V$CR_BLOCK_SERVER
V$GCSHVMASTER_INFO
V$GCSPFMASTER_INFO
V$GC_ELEMENT
V$GES_BLOCKING_ENQUEUE
V$GES_ENQUEUE
V$HVMASTER_INFO
V$GES_LATCH
V$GES_RESOURCES

Log Miner Information related to Log Miner

V$LOGMNR_CALLBACK
V$LOGMNR_CONTENTS
V$LOGMNR_DICTIONARY
V$LOGMNR_LATCH
V$LOGMNR_LOGFILE
V$LOGMNR_LOGS
V$LOGMNR_PARAMETERS
V$LOGMNR_PROCESS
V$LOGMNR_REGION

V$LOGMNR_SESSION
V$LOGMNR_STATS
V$LOGMNR_TRANSACTION
V$LOGMNR_DICTIONARY_LOAD(10.2)

Metrics Information related to Metrics (ALL New in 10g!)

V$METRICNAME
V$SERVICEMETRIC
V$EVENTMETRIC
V$FILEMETRIC
V$FILEMETRIC_HISTORY
V$SERVICEMETRIC_HISTORY
V$SESSMETRIC
V$SYSMETRIC
V$SYSMETRIC_HISTORY
V$SYSMETRIC_SUMMARY
V$THRESHOLD_TYPES
V$WAITCLASSMETRIC
V$WAITCLASSMETRIC_HISTORY

Multithreaded/shared servers Information related to multithreaded and parallel servers, including connections, queues,dispatchers, and shared servers

V$CIRCUIT
V$DISPATCHER
V$DISPATCHER_RATE
V$QUEUE
V$QUEUEING_MTH
V$REQDIST
V$SHARED_SERVER
V$SHARED_SERVER_MONITOR
V$DISPATCHER_CONFIG(10.1)

Object Usage Information related to object use and dependencies

V$OBJECT_DEPENDENCY
V$OBJECT_USAGE

Overall system Information related to the overall system performance

V$GLOBAL_TRANSACTION
V$SHARED_POOL_RESERVED
V$RESUMABLE
V$SORT_SEGMENT
V$TEMPSEG_USAGE
V$STATNAME
V$SYS_OPTIMIZER_ENV
V$SYS_TIME_MODEL
V$SYSSTAT
V$SYSTEM_CURSOR_CACHE
V$SYSTEM_EVENT
V$TEMPFILE
V$TEMPORARY_LOBS
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_SPACE_HEADER
V$TRANSACTION
V$ALERT_TYPES(10.1)
V$EVENT_HISTOGRAM(10.1)
V$OSSTAT(10.1)
V$SYSTEM_WAIT_CLASS(10.1)
V$TEMP_HISTOGRAM(10.1)
V$XML_AUDIT_TRAIL

Parallel Query Information related to the Parallel Query option

V$EXECUTION
V$PARALLEL_DEGREE_LIMIT_MTH
V$PQ_SESSTAT
V$PQ_SLAVE
V$PQ_SYSSTAT
V$PQ_TQSTAT
V$PX_PROCESS
V$PX_PROCESS_SYSSTAT
V$PX_SESSION
V$PX_SESSTAT

Parameters Information related to various Oracle parameters, including initialization and NLS per session

V$NLS_PARAMETERS
V$NLS_VALID_VALUES
V$OBSOLETE_PARAMETER
V$PARAMETER
V$PARAMETER2
V$SPPARAMETER
V$SYSTEM_PARAMETER
V$SYSTEM_PARAMETER2
V$PARAMETER_VALID_VALUES(10.2)

Redo logs Information related to redo logs, including statistics and history

V$LOG
V$LOGFILE
V$LOGHIST
V$LOG_HISTORY
V$THREAD (RAC related)

Replication and materialized views Information related to replication and materialized views

V$MVREFRESH
V$REPLPROP
V$REPLQUEUE

Resource Manager Information related to resource management

V$ACTIVE_SESSION_POOL_MTH
V$ACTIVE_SESSION_POOL_HISTORY
V$RSRC_CONS_GROUP_HISTORY(10.2)
V$RSRC_CONSUMER_GROUP
V$RSRC_CONSUMER_GROUP_CPU_MTH
V$RSRC_PLAN
V$RSRC_PLAN_CPU_MTH
V$RSRC_PLAN_HISTORY(10.2)
V$RSRC_SESSION_INFO(10.2)

Rollback segments And Undo Information on rollback segments, including statistics and transactions

V$ROLLNAME
V$ROLLSTAT
V$TRANSACTION
V$UNDOSTAT

Security/privileges Information related to security

V$ENABLEDPRIVS
V$PWFILE_USERS
V$VPD_POLICY
V$WALLET(10.2)
V$XML_AUDIT_TRAIL(10.2)

Sessions (includes some replication information and heterogeneous services) Information related to a session, including object access, cursors, processes, and statistics

V$ACTIVE_SESSION_HISTORY
V$MYSTAT
V$PROCESS
V$SESS_TIME_MODEL
V$SESSION
V$SESSION_CONNECT_INFO
V$SESSION_CURSOR_CACHE
V$SESSION_EVENT
V$SESSION_LONGOPS
V$SESSION_OBJECT_CACHE
V$SESSION_WAIT
V$SESSION_WAIT_CLASS
V$SESSION_WAIT_HISTORY
V$SESSTAT
V$SESS_IO
V$SES_OPTIMIZER_ENV
V$SESSMETRIC
and V$CLIENT_STATS
and V$TSM_SESSIONS(10.2)

Services (all new for 10.1)

V$ACTIVE_SERVICES
V$SERV_MOD_ACT_STATS
V$SERVICE_EVENT
V$SERVICE_STATS
V$SERVICE_WAIT_CLASS
V$SERVICES

Sorting Information related to sorting

V$SORT_SEGMENT
V$TEMPSEG_USAGE
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_HISTOGRAM(10.1)
V$TEMP_SPACE_HEADER
V$TEMPFILE
V$TEMPSTAT

Standby databases (Data Guard) Information related to standby databases

V$DATAGUARD_STATUS
V$LOGSTDBY
V$LOGSTDBY_STATS
V$MANAGED_STANDBY
V$STANDBY_LOG
V$DATAGUARD_CONFIG(10.1)
V$DATAGUARD_STATS(10.2)
V$LOGSTDBY_PROCESS(10.2)
V$LOGSTDBY_PROGRESS(10.2)
V$LOGSTDBY_STATE(10.2)
V$LOGSTDBY_TRANSACTION(10.2)

File mapping interface Information related to file mapping

V$MAP_COMP_LIST
V$MAP_ELEMENT
V$MAP_EXT_ELEMENT
V$MAP_FILE
V$MAP_FILE_EXTENT
V$MAP_FILE_IO_STACK
V$MAP_LIBRARY
V$MAP_SUBELEMENT

Streams Information related to streams

V$AQ
V$STREAMS_APPLY_COORDINATOR
V$STREAMS_APPLY_READER
V$STREAMS_APPLY_SERVER
V$STREAMS_CAPTURE
V$BUFFERED_PUBLISHERS(10.1)
V$BUFFERED_QUEUES(10.1)
V$BUFFERED_SUBSCRIBERS(10.1)
V$PROPAGATION_RECEIVER(10.1)
V$PROPAGATION_SENDER(10.1)
V$RULE(10.1)
V$RULE_SET(10.1)
V$RULE_SET_AGGREGATE_STATS(10.1)
V$STREAMS_TRANSACTION(10.2)

Statistics Information related to statistics in general

V$SEGMENT_STATISTICS
V$SEGSTAT
V$SEGSTAT_NAME
V$STATISTICS_LEVEL
V$STATNAME
V$WAITSTAT

Transactions Information related to transactions in general

V$GLOBAL_TRANSACTION
V$LOGSTDBY_TRANSACTION
V$RESUMABLE
V$STREAMS_TRANSACTION
V$TRANSACTION
V$TRANSACTION_ENQUEUE

Note: In Oracle 10.1 and above, the V$LOCK_TYPE dynamic performance view summarizes all implemented lock types

MTV ENJOY....

Controlling the Autotrace Report in sql*plus

In sql*plus with AUTOTRACE command you can see the execution plan of successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements which is useful for monitoring and tuning the performance of these statements.

You can control the report by setting the AUTOTRACE system variable.

Following is the available AUTOTRACE settings.

1)SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default. Result of a query will be displayed on the console as usual.

2)SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows the optimizer execution path along with query output and note but does not show statistics.

3)SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows the SQL statement execution statistics along with query output but does not display execution plan.

4)SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics along with query output.

5)SET AUTOTRACE TRACEONLY: Report displays both optimizer execution path as well as execution statistics but does not display query output. If STATISTICS is enabled, query data is still fetched, but not printed.

Thanks.

Select the TOP N rows from a table in Oracle

Below is the examples to find the top 5 employees based on their salary.

Option 1: Using RANK()

SELECT employee_name, salary
FROM ( SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) salary_rank
FROM employee )
WHERE salary_rank <= 5;

Option 2: Using Dense_Rank()

SELECT employee_name, salary
FROM ( SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) salary_dense_rank
FROM employee )
WHERE salary_dense_rank <= 5;

Option 3: Using inner query
This is an example of using an inner-query with an ORDER BY clause:

SELECT *
FROM (SELECT * FROM employee ORDER BY salary DESC)
WHERE ROWNUM < 5;

Option 4: Using count distinct combination

SELECT *
FROM employee e
WHERE 5 >= (SELECT COUNT(DISTINCT salary)
FROM employee b
WHERE b.salary >= e.salary)
ORDER BY salary DESC;

Thanks.

Select the LAST N rows from a table in Oracle

From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the LAST N or BOTTOM N rows. Examples:

Get the bottom 10 employees based on their salary

SELECT ename, sal
FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal) sal_rank
FROM emp )
WHERE sal_rank <= 10;

Select the employees getting the lowest 10 salaries

SELECT ename, sal
FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank
FROM emp )
WHERE sal_dense_rank <= 10;

For Oracle 8i and above, one can get the bottom N rows using an inner-query with an ORDER BY clause:

SELECT *
FROM (SELECT * FROM my_table ORDER BY col_name_1)
WHERE ROWNUM < 10;

Use this workaround for older (8.0 and prior) releases:

SELECT *
FROM my_table a
WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol <= a.maxcol)
ORDER BY maxcol;

Thanks.

DIFF utility: Find Differences Between Files

Working as a DBA , you may encounter situations like to compare two files to check whether the contents are same or not.
diff will surely make your life easy.Believe IT or ....

$ diff init.ora init1.ora

Check the output showing the differences in the files:
16,17c16,17
< sga_max_size=900M
< sga_target=700M
---
> sga_max_size=800M
> sga_target=600M
19a20
> # sort_area_size=0

here, < refer to file1 & > refer to file 2.
And , c stands for change
a stands for append (looks like file 2 has one more line & that is 20 which shows contents "# sort_area_size=0"

There is another tag like d (beside c & a) which stands for delete.

$ diff init.ora init1.ora > change.diff
To convert init.ora to init1.ora, use the "patch" command with the difference report output:
$ patch init.ora change.diff

We can even use "sdiff" utility too.
Ex: $ sdiff init.ora init1.ora

This will show the changes in more readable format.

Note: can also use "diff3" utility to compare differences between three files.

Find Differences Between Directories:

Possible guys!!!!

$ diff /oracle/scripts /u01/oracle/scripts

thanks... have a nice day.