ORA-01000: maximum open cursors exceeded

 

The OPEN_CURSORS parameter is set to the maximum number of cursors that each session can open at a time.

Example, if the value of OPEN_CURSORS is set to 2000, then each session can have up to 2000 cursors open at one time.

If the number of cursors in the database exceeds the maximum limit then you will get the following error:

ORA-01000: maximum open cursors exceeded.

Cause : The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action: Modify the program to use fewer cursors.

compare the parent and child table rows, if both table rows are not matched then you may get this error

further you can check the following steps:

Login to the schema with DBA privilege of the database.

To find out the session that is causing the error using the below SQL statement:

 SQL>SELECT A.VALUE, S.USERNAME, S.SID, S.SERIAL# FROM V$SESSTAT A, V$STATNAME B, V$SESSION S WHERE A.STATISTIC# = B.STATISTIC#  AND S.SID=A.SID AND B.NAME = 'OPENED CURSORS CURRENT' AND S.USERNAME IS NOT NULL;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

open_cursors                         integer     2048

check maximum number of open cursors in oracle?

select sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor

group by sql_text, user_name order by count(*) desc;

you will get the output as details of all sessions. You can see the maximum out session IDs.

Connect to database as sysdba user, get current setting of open cursors limit

show parameter open_cursors

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

open_cursors                         integer     1000

list the top 10 sessions which are currently opening most cursors

select *

 from (

 select ss.value, sn.name, ss.sid

 from v$sesstat ss,

 v$statname sn

 where ss.statistic# = sn.statistic#

 and sn.name like '%opened cursors current%'

 order by value desc)

 where rownum < 11

Check what makes session= 93 open so many cursors?

SQL> select sid, status, event, seconds_in_wait, state, blocking_session, prev_sql_id  from v$session where sid=93;

 

 SID STATUS   EVENT                          SECONDS_IN_WAIT STATE               BLOCKING_SESSION PREV_SQL_ID

----- -------- ------------------------------ --------------- ------------------- ---------------- -------------

 93 INACTIVE SQL*Net more data from client            19607 WAITING                              uuyzf60cphjs2a

 

&nbsp;

 

SQL> select sql_text from v$sqlarea where sql_id ='uuyzf60cphjs2a'

 

SQL_TEXT

--------------------------------------------------------------------------------

begin ro_tst.ctrlrorfder(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11); end;

 

SQL> select * from

(select SQL_TEXT, sql_id, user_name, count(*)

from v$open_cursor where sid=93

group by SQL_TEXT, sql_id, user_name

order by count(*) desc)

where rownum < 11;

 

SQL_TEXT                                                                         SQL_ID        USER_NAME  COUNT(*)

-------------------------------------------------------------------------------- ------------- ---------- --------

check the recent executed package. we find the piece of code that caused remaining open cursors:

Report the  problem to apps  team and kill these sessions INACTIVE exceeding limit of opening cursors.

or 

ALTER SYSTEM SET OPEN_CURSORS = 3500 SCOPE=BOTH;

Check if open cursors reach the limit set by OPEN_CURSORS

SELECT MAX(a.value) as highest_open_cur, 

       p.value as max_open_cur

  FROM V$SESSTAT a, V$STATNAME b, V$PARAMETER p

 WHERE a.statistic# = b.statistic#

   AND b.name = 'opened cursors current'

   AND p.name= 'open_cursors'

GROUP BY p.value;

HIGHEST_OPEN_CUR MAX_OPEN_CUR

---------------- -------------

             300 400

Transportable Tablespaces Features in Oracle

 Transportable Tablespaces Features in Oracle

The transportable tablespaces feature to copy/move subset of data (set of user tablespaces) from an Oracle database and plug it in to another Oracle database. 

The tablespaces being transported can be either dictionary managed or locally managed.

Example:

SQL> CREATE TABLESPACE fusion

DATAFILE '+DATA01'

SIZE 1M AUTOEXTEND ON NEXT 1M;  2    3

Tablespace created.

CREATE USER fusion IDENTIFIED BY ****

DEFAULT TABLESPACE fusion

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON fusion;

GRANT CREATE SESSION, CREATE TABLE TO fusion;

SQL> show user;

USER is "FUSION"

SQL> CREATE TABLE test_tab (

  id          NUMBER,

  description VARCHAR2(50),

  CONSTRAINT test_tab_pk PRIMARY KEY (id)

);

  2    3    4    5

Table created.

insert some values in the table.

insert 1000 rows

commit;

issue 

select * from v$transportable_platform order by platform_id;

select tp.PLATFORM_NAME, tp.ENDIAN_FORMAT from v$database d, v$transportable_platform tp where d.platform_name=tp.platform_name;

PLATFORM_NAME

--------------------------------------------------------------------------------

ENDIAN_FORMAT

--------------

Linux x86 64-bit

Little

TTS requires all the tablespaces, which we are moving, must be self contained. This means that the segments within the migration tablespace set cannot have dependency to a segment in a tablespace out of the transportable tablespace set. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.

SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('FUSION', TRUE);

PL/SQL procedure successfully completed.

OR

EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'FUSION', incl_constraints => TRUE);

TO check whether any voilations

SQL> SELECT * FROM transport_set_violations;

no rows selected

ALTER TABLESPACE fusion READ ONLY;

SQL> CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';

GRANT READ, WRITE ON DIRECTORY temp_dir TO system;

Directory created.

SQL>

Grant succeeded.

take the export

expdp userid='system/****@connect string' transport_tablespace=y tablespaces=FUSION file=FUSION_EXP.dmp log=FUSION_exp.log

once the export completes alter the tablespace from readonly to read write.

sql>alter tablespace fusion read write;

Target server:

+++++++++++

SQL> CREATE TABLESPACE fusion

DATAFILE '/u02/app/oracle/oradata/soorya/fusion01.dbf'

SIZE 1M AUTOEXTEND ON NEXT 1M;

  2    3

Tablespace created.

SQL>

SQL>

SQL> CREATE USER fusion IDENTIFIED BY ***

DEFAULT TABLESPACE fusion

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON fusion;  2    3    4

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO fusion;

Grant succeeded.

CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';

GRANT READ, WRITE ON DIRECTORY temp_dir TO system;

impdp userid='system/***@*****' directory=temp_dir transport_tablespace=y datafiles='/u02/app/oracle/oradata/soorya/fusion01.dbf' tablespaces=fusion file=FUSION_EXP.dmp log=FUSION_EXP.log

source 

alter tablespace fusion read write;

check the table which was in target and source.

oem command

OEM command in oracle 

+++++++++++++++++

Go to oem path from the oem user or oracle user

bin ]./emctl status agent

bin ]./emctl start agent

ps -ef |grep emagent // to check the status of running agent

Agent on oms server

emctl start | getversion oms    Oracle Management Server

you need to go the oms path on server

To start the oms

./emctl start oms

./emctl start oms -all

To stop the oms

./emctl stop oms [-all] [-force] 

./emctl stop oms

./emctl stop oms -all

./emctl stop oms -all -force

To check the status

emctl status oms

emctl status oms -detail