what is cursor sharing and adaptive cursor sharing in oracle , do we need to define using commands or any parameters

 In Oracle, a cursor is a database object that allows you to fetch data from a database table. Cursor sharing is a feature in Oracle that determines how Oracle will handle similar SQL statements that have different literal values. When two SQL statements are identical except for their literal values, they are called similar statements.

For example, consider the following SQL statements:

SELECT * FROM employees WHERE employee_id = 100;

SELECT * FROM employees WHERE employee_id = 200;

Although these two SQL statements are similar, they have different literal values (100 and 200). In Oracle, cursor sharing determines whether these two SQL statements will share the same cursor or not.

There are two types of cursor sharing in Oracle:

Implicit Cursor Sharing: This type of cursor sharing is automatic and requires no explicit configuration. Oracle automatically determines whether to share cursors based on the hash value of the SQL statement.

Adaptive Cursor Sharing: This type of cursor sharing is also automatic, but it allows Oracle to adapt to changing conditions. Adaptive cursor sharing uses statistics about the execution of SQL statements to determine whether to share cursors or not. For example, if a SQL statement is executed frequently with different literal values, adaptive cursor sharing may decide to share the cursor.

You do not need to define cursor sharing or adaptive cursor sharing using commands or parameters. By default, Oracle uses implicit cursor sharing. However, you can enable or disable adaptive cursor sharing using the initialization parameter "CURSOR_SHARING" (set it to either "SIMILAR" for implicit cursor sharing or "FORCE" for adaptive cursor sharing).