Category: Oracle

PL/SQL example to create a list of tables and execute a dynamic SQL on each of the tables

This PL/SQL code identifies a list of tables that exist in a schema. For each table, it issues a count(*) dynamically.

set serveroutput on;
spool get_all_tables
 
declare
    dyn_sql varchar(300);
    count_x number (10);
    temp_tab_name char(40);
begin
for t in(select owner, substr(table_name,01,40) as table_name from dba_tables where owner = '' order by table_name)
  loop
     temp_tab_name := t.table_name;
     dyn_sql := 'select count(*) from ' || t.owner || '.' || t.table_name;
     execute immediate dyn_sql into count_x;
     dbms_output.put_line(t.owner || ' ' || temp_tab_name || ' = ' || count_x || ' rows');
  end loop;
end;
/
spool off

Bad filename convert on a physical standby

If a data file is added on a primary with a physical standby and there is no appropriate file name convert, the file gets created in the Oracle home. On a RAC this may also report as a certain node unable to lock a file as the file got created in the Oracle home (non ASM) of a different node.

Log on to the standby

Stop mrp

Set standby_file_management = ‘MANUAL’

Find the name of the “bad file”. In my case it was /oracle/product/11.2.0.3/db_1/dbs/UNNAMED00034

You can find the bad file with:

select     name 
from       v$datafile 
where not name like '+%'
;

Find the correct name of the file from the primary.

The file will not actually exist in /opt/oracle. Then

alter database create datafile '/oracle/product/11.2.0.3/db_1/dbs/UNNAMED00034' as '+ASM mount point/usually DB name/datafile/correct name of file from primary';

The goal is to create the file in the correct location as it would have been created if the correct file name convert was in place.

Check v$datafile again

Set standby_file_management = ‘AUTO’

Start MRP

GoldenGate abending – Unable to open file

The GoldenGate data pump was abending on the source:

GGSCI (uiiompd17) 1> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     RUNNING                                           
EXTRACT     ABENDED     DPUMPxx     00:00:00      04:00:29    
EXTRACT     RUNNING     EXAxxxx     00:00:00      00:00:02

The GoldenGate error log contained the below message:

2016-12-17 07:22:13  ERROR   OGG-01031  Oracle GoldenGate Capture for Oracle, dpumprp.prm:  There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Unable to open file "/ggs//dirdat/rp013263" (error 11, Resource temporarily unavailable)).

The file directory that was being reported was for the target of the GoldenGate process. On the target side, the file did exist and there were no issues with the file system, mount point etc. I moved the file as follows:

mv /ggs//dirdat/rp013263 /ggs//dirdat/rp013263.old

I then restarted the abended data pump and normal processing was resumed.