Data source problem

Hi,

I have problems creating a data source in #5321 (against a Oracle 9.2.0.6
database).

When I test the connection i got "ORA-01000: Maximum open cursos exceeded"
after about 2-3 minutes.

It seems that Idea scans all tables and columns. Our DBA says that Idea
opens a new cursor for each table?
Our database contains 692 tables, and thus we got the above error?

This is the SQL that is executed for each table:
SELECT NULL AS pktable_cat, p.owner as pktable_schem,
p.table_name as pktable_name, pc.column_name as pkcol
umn_name, NULL as fktable_cat, f.owner as fktable_
schem, f.table_name as fktable_name, fc.column_nam
e as fkcolumn_name, fc.position as key_seq, NULL a
s update_rule, decode (f.delete_rule, 'CASCADE', 0, 'SET
NULL', 2, 1) as delete_rule, f.constraint_name as fk_name
, p.constraint_name as pk_name, decode(f.deferrabl
e, 'DEFERRABLE',5 ,'NOT DEFERRABLE',7 , 'DEFERRE
D', 6 ) deferrability FROM all_cons_columns pc, all_
constraints p, all_cons_columns fc, all_constraints f WHER
E 1 = 1 AND f.table_name = :1 AND f.owner = :2 AND f.const
raint_type = 'R' AND p.owner = f.r_owner AND p.constraint_na
me = f.r_constraint_name AND p.constraint_type = 'P' AND pc.
owner = p.owner AND pc.constraint_name = p.constraint_name A
ND pc.table_name = p.table_name AND fc.owner = f.owner AND f
c.constraint_name = f.constraint_name AND fc.table_name = f.ta
ble_name AND fc.position = pc.position ORDER BY pktable_schem,
pktable_name, key_seq


So - any suggestions how I can create a data source to this datbase? Is it
possible to only get the table names
when open/testing the data source and then getting the detail information
later (when needed)?

kind regards,
Lennart


3 comments
Comment actions Permalink

Hello Lennart,

The cursors issue is fixed.

In the next EAP DataSource properties dialog will have 2 buttons:
"Test Connection" (and nothing more)
"Load Contents" (load tables/columns)
Ok button will do "Test connection" only.

I'd be glad to see your comments on the matter.

Gregory


Lennart Häggkvist wrote:

Hi,

I have problems creating a data source in #5321 (against a Oracle 9.2.0.6
database).

When I test the connection i got "ORA-01000: Maximum open cursos exceeded"
after about 2-3 minutes.

It seems that Idea scans all tables and columns. Our DBA says that Idea
opens a new cursor for each table?
Our database contains 692 tables, and thus we got the above error?

This is the SQL that is executed for each table:
SELECT NULL AS pktable_cat, p.owner as pktable_schem,
p.table_name as pktable_name, pc.column_name as pkcol
umn_name, NULL as fktable_cat, f.owner as fktable_
schem, f.table_name as fktable_name, fc.column_nam
e as fkcolumn_name, fc.position as key_seq, NULL a
s update_rule, decode (f.delete_rule, 'CASCADE', 0, 'SET
NULL', 2, 1) as delete_rule, f.constraint_name as fk_name
, p.constraint_name as pk_name, decode(f.deferrabl
e, 'DEFERRABLE',5 ,'NOT DEFERRABLE',7 , 'DEFERRE
D', 6 ) deferrability FROM all_cons_columns pc, all_
constraints p, all_cons_columns fc, all_constraints f WHER
E 1 = 1 AND f.table_name = :1 AND f.owner = :2 AND f.const
raint_type = 'R' AND p.owner = f.r_owner AND p.constraint_na
me = f.r_constraint_name AND p.constraint_type = 'P' AND pc.
owner = p.owner AND pc.constraint_name = p.constraint_name A
ND pc.table_name = p.table_name AND fc.owner = f.owner AND f
c.constraint_name = f.constraint_name AND fc.table_name = f.ta
ble_name AND fc.position = pc.position ORDER BY pktable_schem,
pktable_name, key_seq


So - any suggestions how I can create a data source to this datbase? Is it
possible to only get the table names
when open/testing the data source and then getting the detail information
later (when needed)?

kind regards,
Lennart

0
Comment actions Permalink

Hi,

Thanks.

Will the 'Load Contents' work for us as well? ie. will the problem with the
large number of open cursors be fixed?

regards,
Lennart


"Gregory Shrago (JetBrains)" <gregory.shrago@jetbrains.com> wrote in message
news:e69se1$d7f$1@is.intellij.net...

Hello Lennart,

>

The cursors issue is fixed.

>

In the next EAP DataSource properties dialog will have 2 buttons:
"Test Connection" (and nothing more)
"Load Contents" (load tables/columns)
Ok button will do "Test connection" only.

>

I'd be glad to see your comments on the matter.

>

Gregory

>
>

Lennart Häggkvist wrote:

>> Hi,
>>
>> I have problems creating a data source in #5321 (against a Oracle 9.2.0.6
>> database).
>>
>> When I test the connection i got "ORA-01000: Maximum open cursos
>> exceeded" after about 2-3 minutes.
>>
>> It seems that Idea scans all tables and columns. Our DBA says that Idea
>> opens a new cursor for each table?
>> Our database contains 692 tables, and thus we got the above error?
>>
>> This is the SQL that is executed for each table:
>> SELECT NULL AS pktable_cat, p.owner as pktable_schem,
>> p.table_name as pktable_name, pc.column_name as pkcol
>> umn_name, NULL as fktable_cat, f.owner as fktable_
>> schem, f.table_name as fktable_name, fc.column_nam
>> e as fkcolumn_name, fc.position as key_seq, NULL a
>> s update_rule, decode (f.delete_rule, 'CASCADE', 0, 'SET
>> NULL', 2, 1) as delete_rule, f.constraint_name as fk_name
>> , p.constraint_name as pk_name, decode(f.deferrabl
>> e, 'DEFERRABLE',5 ,'NOT DEFERRABLE',7 , 'DEFERRE
>> D', 6 ) deferrability FROM all_cons_columns pc, all_
>> constraints p, all_cons_columns fc, all_constraints f WHER
>> E 1 = 1 AND f.table_name = :1 AND f.owner = :2 AND f.const
>> raint_type = 'R' AND p.owner = f.r_owner AND p.constraint_na
>> me = f.r_constraint_name AND p.constraint_type = 'P' AND pc.
>> owner = p.owner AND pc.constraint_name = p.constraint_name A
>> ND pc.table_name = p.table_name AND fc.owner = f.owner AND f
>> c.constraint_name = f.constraint_name AND fc.table_name = f.ta
>> ble_name AND fc.position = pc.position ORDER BY pktable_schem,
>> pktable_name, key_seq
>>
>>
>> So - any suggestions how I can create a data source to this datbase? Is
>> it possible to only get the table names
>> when open/testing the data source and then getting the detail information
>> later (when needed)?
>>
>> kind regards,
>> Lennart


0
Comment actions Permalink

It definitely should


Lennart Häggkvist wrote:

Hi,

Thanks.

Will the 'Load Contents' work for us as well? ie. will the problem with the
large number of open cursors be fixed?

regards,
Lennart


"Gregory Shrago (JetBrains)" <gregory.shrago@jetbrains.com> wrote in message
news:e69se1$d7f$1@is.intellij.net...

>> Hello Lennart,
>>
>> The cursors issue is fixed.
>>
>> In the next EAP DataSource properties dialog will have 2 buttons:
>> "Test Connection" (and nothing more)
>> "Load Contents" (load tables/columns)
>> Ok button will do "Test connection" only.
>>
>> I'd be glad to see your comments on the matter.
>>
>> Gregory
>>
>>
>> Lennart Häggkvist wrote:
>>> Hi,
>>>
>>> I have problems creating a data source in #5321 (against a Oracle 9.2.0.6
>>> database).
>>>
>>> When I test the connection i got "ORA-01000: Maximum open cursos
>>> exceeded" after about 2-3 minutes.
>>>
>>> It seems that Idea scans all tables and columns. Our DBA says that Idea
>>> opens a new cursor for each table?
>>> Our database contains 692 tables, and thus we got the above error?
>>>
>>> This is the SQL that is executed for each table:
>>> SELECT NULL AS pktable_cat, p.owner as pktable_schem,
>>> p.table_name as pktable_name, pc.column_name as pkcol
>>> umn_name, NULL as fktable_cat, f.owner as fktable_
>>> schem, f.table_name as fktable_name, fc.column_nam
>>> e as fkcolumn_name, fc.position as key_seq, NULL a
>>> s update_rule, decode (f.delete_rule, 'CASCADE', 0, 'SET
>>> NULL', 2, 1) as delete_rule, f.constraint_name as fk_name
>>> , p.constraint_name as pk_name, decode(f.deferrabl
>>> e, 'DEFERRABLE',5 ,'NOT DEFERRABLE',7 , 'DEFERRE
>>> D', 6 ) deferrability FROM all_cons_columns pc, all_
>>> constraints p, all_cons_columns fc, all_constraints f WHER
>>> E 1 = 1 AND f.table_name = :1 AND f.owner = :2 AND f.const
>>> raint_type = 'R' AND p.owner = f.r_owner AND p.constraint_na
>>> me = f.r_constraint_name AND p.constraint_type = 'P' AND pc.
>>> owner = p.owner AND pc.constraint_name = p.constraint_name A
>>> ND pc.table_name = p.table_name AND fc.owner = f.owner AND f
>>> c.constraint_name = f.constraint_name AND fc.table_name = f.ta
>>> ble_name AND fc.position = pc.position ORDER BY pktable_schem,
>>> pktable_name, key_seq
>>>
>>>
>>> So - any suggestions how I can create a data source to this datbase? Is
>>> it possible to only get the table names
>>> when open/testing the data source and then getting the detail information
>>> later (when needed)?
>>>
>>> kind regards,
>>> Lennart


0

Please sign in to leave a comment.