What are the major difference between Oracle9i and Oracle10g?

Oracle 9i has the following new features: 

1. Direct XML database support.
2. Oracle Data Guard and enhancements.
3. New data sharing and replication features.
4. Compresses keys in tables when loading data.
5. Security Improvements.
6. Manage system table spaces locally.
7. Automated DBA.

Oracle 10g has following new features:

1. New drop database and alter database begin backup syntax.
2. Oracle 10g Data Guard Broker and RAC to support Redo Log Transport.
3. SQL apply feature and regular expression support.
4. Supports HTML database.
5. Recycle bin for storing objects and new purge command.
6. SYSAUX table space and rename table space command.
7. Automated Storage Management (ASM).
8. Automatic Workload Repository (AWR).
9. Automatic Database Diagnostic Monitor (ADDM)
10. PLS_INTEGER, a new data type was added which enhances performance. ANYDATA data type was introduced to hold a data of variant feature.

How to get list of Top Performing SQLs in Oracle?

The V$SQLAREA is used for identifying the top most resource-consuming SQL statements with a variety of criteria. It is widely useful to Database Administrators, Application Developers, Oracle Support Engineers and generally anyone involved in an Oracle Database Performance Tuning activity.

The thresholds used are the same as used by default in Statspack:
Buffer Gets : 10,000
Physical Reads : 1,000
Executions : 100
Parse Calls : 1,000
Sharable Memory : 1,048576
Version Count : 20
For Top Buffer Gets >>>

SELECT * FROM(
SELECT substr(sql_text,1,40) sql,
buffer_gets, executions, buffer_gets/executions "Gets/Exec",hash_value,address
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC
)WHERE rownum <= 10;

For Top Physical Reads >>>

SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
disk_reads, executions, disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10;

For Top Executions >>>

SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
executions, rows_processed, rows_processed/executions "Rows/Exec",
hash_value,address
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10;

For Top Parse Calls >>>

SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10;

What are the Collections or Composite Datatypes in PL/SQL?

A Composite Datatypes are internal components that can be manipulated individually in PL/SQL are also called as PL/SQL Collections.

A PL/SQL collection is an ordered group of elements of the same type. It is a general concept that encompasses lists, arrays, and other familiar data types. Each element has a unique subscript that determines its position in the collection.

Three types of PL/SQL collections are in Oracle.

Index-by tables :

It is also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. They are similar to hash tables in other programming languages.Index-by tables is a non -persistent collection and it can’t store in the database.

Nested tables:

It can hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

Varrays :

Variable-Size Arrays (Varrays) can hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Nested tables and Varrays are persistent collections and it is stored in database.

What are the process states in Unix?

As a process executes it changes state according to its circumstances. Unix processes have the following states:

Running : The process is either running or it is ready to run .

Waiting  : The process is waiting for an event or for a resource.

Stopped : The process has been stopped, usually by receiving a signal.

Zombie   : The process is dead but have not been removed from the process table.

When a program forks and the child finishes before the parent, the kernel still keeps some of its information about the child in case the parent might need it - for example, the parent may need to check the child's exit status.

To be able to get this information, the parent calls wait() In the interval between the child terminating and the parent calling wait() , the child is said to be a “zombie” state.
while using ps command, the child will have status  Z for Zombie.

Common Errors while performing DML Operations on a table.

Primary key  Constraints

Equivalent to a unique constraints, both sets of rules apply.

ORA-00001: unique constraint (SCOTT.PK_DEPT) violated


Foreign key Constraints

INSERT and UPDATE on the child table: the value must exist in the parent table.

ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found

UPDATE and DELETE on the parent table,
there must be no dependent rows in the child table.

ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated – child record found

Not null Constraints

INSERT and UPDATE: cannot insert without a value, or modify the value to null.

ORA-01400: cannot insert NULL into (“SCOTT”.“EMP”.“EMPNO”)

Unique Constraints

INSERT and UPDATE: cannot have any value that already exists in the table. 
Multiple nulls are permitted.

ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

What is ACID Property?

ACID Property - Atomicity Consistency Isolation Durability

A for Atomicity

The principle of atomicity states that all parts of a transaction must complete or not.

C for Consistency

The principle of consistency states that the results of a query must be consistent with the state of the database at the time the query started. It requires that the database ensure that changed values are  not seen by the query. Through the use of undo segments Oracle guarantees that if a query succeeds, the result will be consistent to all else data shown from undo segments.

I for Isolation

The principle of isolation states that an incomplete i.e. uncommitted transaction must be invisible to all others. While the transaction is in progress, only the one session that is executing the transaction is allowed to see the changes, all other sessions must see the unchanged data, not the new values.

D for Durability

The principle of durability states that once a transaction completes with a COMMIT, it must be impossible for the database to lose it. During the time that the transaction is in progress, the principle of isolation requires that no one can see the changes it has made so far.

What are the File/Directory Permissions in Unix/Linux?

Every user on a Unix/Linux system has a unique username, and is a member of at least one group. This group information is held in the password file stored in /etc/passwd. A user can also be a member of one or more other groups, stored in /etc/group.  Only the administrator can create new groups or add/delete group members.

Every directory and file on the system has an owner, and also an associated group. It also has a set of permission flags which specify separate read, write and execute permissions for the owner, group and other.

ls -l command shows the permissions and group associated with files.

ls -g command shows the group information.

The permission flags are read as follows (left to right)

1	directory flag, 'd' if a directory, '-' if a normal file, something else occasionally may appear here for special devices.
2,3,4	read, write, execute permission for User (Owner) of file
5,6,7	read, write, execute permission for Group
8,9,10	read, write, execute permission for Other
-	in any position means that flag is not set
r	file is readable by owner, group or other
w	file is writeable. On a directory, write access means you can add or delete files
x	file is executable - only for programs and shell scripts. Execute permission on a directory means you can list the files in that directory

Please explain - rollforward & rollback.

• Rollforward refers to the process Oracle goes through to apply changes contained in the redo log files (both online and archive). The database SCN is moved forward within the blocks of the datafile that are changed within the redo log vectors. Roll forward occurs during database tablespace or datafile recovery and during crash recovery.
• Rollback is the process of undoing uncommitted database transactions. The blocks copied to the rollback segments during transactions as a copy of the block for other transaction to read.When the instance aborts the undo information in the redo log files must be applied to the database during the roll forward process of recovery. Therefore during recovery the database must roll forward and roll back.
•  Roll forward is applied on Redo log files as Rollback is applied on undo log files.

Explain the Perfomance Tuning.

• It is the Improved throughput in OLTP System or decrease response time in datawarehouse(DWH). It s like that how fast we get output of a query/transaction/page/form from one to another.

• Tuning can be applied at
  Query level /Object level / Database level /Application level / System level

• Tuning is mainly responsible for   
  Database designer, Database developer, Database administrator(DBA), Application developer, etc.

How to send email from 10g Oracle Database (UTL_MAIL)?

To enable mailing from database using below steps.

1. sqlplus ‘/ as sysdba’
2. @$ORACLE_HOME/rdbms/admin/utlmail.sql
3. @$ORACLE_HOME/rdbms/admin/prvtmail.plb
4. Set smtp_server information in init.ora or spfile.ora

alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;

Use default SMTP Port as 25.

If database instance had been started with spfile

alter system set smtp_out_server = ’10.10.10.10:25′ scope=both;

If database instance had been started with pfile


alter system set smtp_out_server = ’10.10.10.10:25′;


Also make below entry in your initSID.ora 

smtp_out_server = ’10.10.10.10:25′

To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.

grant execute on utl_mail to USERS;

Now,database configuration finished.

To send email, use below code in your procedure/package,

exec utl_mail.send((sender => ‘abc@abc.com’, recipients => ‘xyz@xyz.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘TESTMAIL USING  UTL_MAIL PACKAGE.’);
 

Check the inbox of the email id, to verify the email received or not.

How I can find a running Oracle database is started using PFILE or SPFILE?

You can check through following ways

Check in v$parameter view

Select * from v$parameter where  upper(name)  like ‘SPFILE’;                              

or

SHOW PARAMETER SPFILE. 

If you find any data in above query then it started using SPFILE else with PFILE.

What is Oracle Data Pump (impdp and expdp)?

• Oracle Data Pump is a new feature of Oracle Database 10 g that provides high speed, parallel, bulk data and metadata movement of Oracle database contents.
• DBMS_DATAPUMP package  provides a server-side infrastructure for fast data and metadata movement. expdp and impdp is used to import/export activites.
• It vastly improved performance and greatly enhanced functionality, such as restartability, flexible object selection, and better monitoring and control of export and import jobs.
• Data Pump is server-based, rather than client-based. Dump files, log files, and SQL files are accessed relative to server-based directory paths, so that appropriate file security can be enforced.
• Data Pump requires you to specify directory paths as directory objects. A directory object maps a name to a directory name on the file system.
• Before you can run Data Pump Export or Data Pump Import, a directory object must be created by a DBA or by any user with CREATE ANY DIRECTORY privilege.