Feeds:
Posts
Comments

Archive for the ‘9i’ Category

A note to self post for queries to use to check cursor counts presently in use and to troubleshoot ORA-1000: maximum open cursors exceeded:

select sum(a.value), b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = ‘opened cursors current’
group by b.name;

This can be altered to focus in on a single user:

select sum(a.value), c.sql_text
from v$sesstat a, v$statname b, v$open_cursor c
where a.statistic# = b.statistic#
and b.name = ‘opened cursors current’
and c.sid=a.sid
and c.user_name=’MYUSERNAME’
group by c.sql_Text

Note that using a query that doesn’t specify ‘opened cursors current’ such as one that pulls data from v$open_cursor will provide cached cursors as well thus giving a misleading answer.

More info:
http://www.orafaq.com/node/758
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1089433252941
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:553222846752

Read Full Post »

Trying to run:

ALTER TABLE my_table DROP CONSTRAINT my_constraint_pk;

ALTER TABLE my_table ADD CONSTRAINT my_constraint_pk PRIMARY KEY (my_col_1, my_col_2);

On 9i, this works fine. If memory serves, I believe it works fine on 10g as well. But on 11gR2 (not sure about R1), this statement results in:

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at line 51

ORA-00955: name is already used by an existing object

Though on both versions, an index is created by default, it ends up that on the newer version, it no longer drops the associated index when dropping the constraint.

So, in order to run this statement successfully, one must drop the index as well:

ALTER TABLE my_table DROP CONSTRAINT my_constraint_pk;

DROP INDEX my_constraint_pk;

ALTER TABLE my_table ADD CONSTRAINT my_constraint_pk PRIMARY KEY (my_col_1, my_col_2);

Read Full Post »

Not so easy to find anymore…

http://download-west.oracle.com/docs/html/B13670_06/toc.htm – Linux x86 release notes

303859.1 – kernel parameters and requirements for 9iR2 on RHEL 4

http://www.oracle-base.com/articles/9i/Oracle9iInstallationOnRedHatAS4AndCentos4.php has the installation setup as well.

http://download.oracle.com/docs/html/A96167_01/pre.htm#CHDHDABJ – kind of worthless…the best I could find that still exists on otn 😦

Read Full Post »