select on view causing ddl lock

[canadj]

I have a view and every time I run a select against it from a sql window the session performs a ddl lock on every table referenced in the view. once the select starts it has to finish bringing back 100% of the result set or else the OS Pid has to be killed... killing the session from the db does not work. I've tested running the same select statement from sql*plus and it did not perform any of the locks that pl/sql developer performs.

Also it appears that editing or viewing the view (right click and edit/view) also locks all tables referenced... this means that I cannot have 2 different session access the same view from pl/sql and again this is not the reaction I get from sql*plus. jcanada@brierley.com

 

[canadj]

argg... spent days on this issue and as soon as i post it i find the solution... but not the reason.

one by one i eliminated a table from the view and queired the revised view each time. I eventually found that a singe table (1200 records) was causing the issued. I droped and recreated the table and now it seems to work. very strange that this issue did not happen with other tools but i'm just happy that it works now.

 

[Marco Kalter]

I have no immediate explanation for this. PL/SQL Developer simply fires the SQL statement in the SQL Editor, and does notthing special with the view or the underlying table.

Note however that Oracle will sometimes lock objects for the duration of a cursor. If not all results have been fetched, the corresponding cursor remains open and objects can be locked. This should never prevent other users from accessing the same view or tables.

I'm glad it's "fixed" though.