ORA-22992, ORA-64202 cannot use LOB locators selected from remote tables

If you want try to select CLOB column via database link then you get error ORA-22992

select * from <TABLE_NAME>@<DATABASE LINK>;

ORA-22992: cannot use LOB locators selected from remote tables
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables.

This article shows how to avoid this error

Continue reading

Oracle PGA monitoring using ASH and AWR

Article presents how to create simple report to monitor PGA in Oracle database using ASH or AWR as source.

PGA is dedicated memory structure in SGA stores stores following areas:

Cursors and private sql areas

  •  opened cursors, links to shared SQL areas in shared pool
  • bind variable for cursors
  •  query execution state information

SQL work areas

  • sort operations (order by, group-by, rollup, window function)
  • hash-join
  • bitmap merge
  • bitmap create

Continue reading