Monthly Archives: August 2019


Average size of set of rows from a table

Sometimes users have strange requests that I avoid asking the reason.

Today a user asked me how he could find the average size of a set of rows inside a table. Not only for one column and not for the whole table.

This reminded me from university times, when doing SQL exercises.

With Oracle 11g and listagg function, it is quite easy to fulfill the request:

set serveroutput on;

declare
  col_list varchar(2500);
  result number;
begin
select 'select round('||listagg('nvl(avg(length('||column_name||')),0)','+') within group (order by column_id)||') 
  from MYAPP.mytable
  where creationdate > to_date(''01.01.2018'',''dd.mm.yyyy'') 
    and creationdate < to_date(''31.12.2018'',''dd.mm.yyyy'')' into col_list 
from all_tab_columns where owner='MYAPP' and table_name='MYTABLE' ;
execute immediate (col_list) into result;
dbms_output.put_line(result);
end;
/

DGMGRL error: Object “a1021p_site1” was not found

While testing some scripts, I was getting an error on DGMGRL, while querying some databases:

DGMGRL> show database A1021P_SITE1
Object "a1021p_site1" was not found

But it would work if I put single quotes around:

DGMGRL> show database 'A1021P_SITE1'

Database - A1021P_SITE1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    A1021P

Database Status:
SUCCESS

The problem is on the way the database was added to the configuration, and it is quite simple to fix it…

  1. after disabling configuration
  2. and connecting with password to the database
  3. then just rename the database to a name without using quotes.
DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
Error: ORA-16602: database must be disabled to perform this operation
Failed.

DGMGRL> disable fast_start failover;
Disabled.

DGMGRL> disable configuration
Disabled.

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
ORA-16541: database is not enabled
Configuration details cannot be determined by DGMGRL

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
ORA-16541: database is not enabled
Configuration details cannot be determined by DGMGRL

DGMGRL> connect sys/XXXX@a1021p_site1
Connected.

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
Succeeded.

DGMGRL> show database A1021P_SITE1

Database - a1021p_site1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    A1021P

Database Status:
DISABLED

DGMGRL> enable configuration;
Enabled.

DGMGRL> enable fast_start failover
Enabled.

DGMGRL> show configuration

Configuration - A1021P

  Protection Mode: MaxAvailability
  Databases:
    a1021p_site1 - Primary database
    a1021p_site2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS