DB_NAME: A basic parameter which shows the name of database created into the system. List of 10 Must Know Oracle Database Parameters for ... - Rajat DBA'S Blog When the application re-executes a cursor, it can be found in the cursor cache and a reparse is avoided. Oracle Cursor Usage Test. Syntax of declaring a cursor parameter is pretty similar to that of the simple cursor except the addition of parameters enclosed in the parenthesis. Oracle Cursor FOR UPDATE By Practical Examples Or, pass in a collection: create or replace PROCEDURE get_Sample_request ( in_request_status IN SYS.ODCIVARCHAR2LIST, out_cursor OUT SYS_REFCURSOR ) AS sql_qry VARCHAR2 (150); emp_tot NUMBER (3); BEGIN OPEN out_cursor FOR SELECT * FROM user_requests WHERE request_status IN (SELECT column_value FROM TABLE (in_request_status)); END get_Sample . SQL> show parameter open_cursors. The cursor count is per session. To increase the size of AUTOEXTEND NEXT value, use the following procedure. alter system set open_cursors = 1000 scope=both; If you are using a pfile instead, you can change the setting for the running instance. SESSION_CACHED_CURSORS helps you in pinning (partial because it only pins HEAP 0 . My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts. An issue occurs when the open_cursors limit is less than 300 in the Oracle server. Increase the SHARED POOL SIZE .... - dbasupport.com of open_cursors or kill the inactive session which has open the large number of cursors. Oracle 12cR1 ORA-02020 too many database links in use This section describes some of the parameters that control allocation of shared memory. Add the entry open_cursors = 300 to the ORACLE_BASE\ADMIN\VUM\pfile\init.ora file. ORACLE-BASE - Oracle Enterprise Manager Cloud Control 12c Release 1 ... . For a million rows the speed-up was closer . To enable the test, go to the enable / disable tests page using the menu sequence : Agents -> Tests -> Enable/Disable, pick Oracle Database as the Component type, Performance as the Test type, choose this test from the disabled tests list, and click on the . Then in SQL*Plus start up the database using that pfile as follows. With this parameter Oracle maintained a local session cache which stores recently closed. How To Create Cursor Parameter In Oracle Database