How to Pause and Resume Oracle Materialized View Refresh on Commit

A client wants to temporarily stop terabytes Oracle materialized views refresh for application issue diagnosing, and then resume the refresh without invalidating the materialized views.

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

“ORA-06533: Subscript beyond count” from DBMS_STATS

DBMS_STATS threw the following errors:

SQL> EXEC DBMS_STATS.gather_table_stats('USERNAME','TABLENAME');
BEGIN DBMS_STATS.gather_table_stats('USERNAME','TABLENAME'); END;

*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1

WORKAROUND

Use a numeric value for estimate_percent instead of default dbms_stats.auto_sample_size:

SQL> exec dbms_stats.gather_table_stats( ownname=>'USERNAME',tabname=>'EVENT',estimate_percent=>100);

PL/SQL procedure successfully completed.

OEM Agent Home Page Shows ” Communication between the Oracle Management Service to the Agent is unavailable. Any features or displayed information requiring this communication will be unavailable”

After OMS upgraded to 13.4, the agent home pages are showing the following error:

Communication between the Oracle Management Service to the Agent is unavailable. Any features or displayed information requiring this communication will be unavailable

WORKAROUND

This is known issue because of SSLCipherSuites compatibility issue between OMS and Agents after OMS upgrading.

Run the following commands on EM agent server to resolve this issue:

$ emctl status agent

$ emctl pingOMS

$ emctl getproperty agent -name SSLCipherSuites
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
SSLCipherSuites is unset; default value is SSL_RSA_WITH_RC4_128_MD5:SSL_RSA_WITH_RC4_128_SHA:SSL_RSA_WITH_3DES_EDE_CBC_SHA

$ emctl setproperty agent -name SSLCipherSuites -value TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
EMD setproperty succeeded

$ emctl getproperty agent -name SSLCipherSuites
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
SSLCipherSuites=TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256

$ emctl stop agent

$ emctl start agent 

ORA-01450: maximum key length (3215) exceeded

A client is trying to rebuild an index online with the following errors:

SQL> alter index indx_cdr rebuild online;
alter index IDX_CDR rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded



SQL> select max(length( call_message)) from  cdr;

MAX(LENGTH(CALL_MESSAGE))
-------------------------
               3586

WORKAROUND

Build the index without ONLINE option:

SQL> alter index indx_cdr rebuild;

Index altered.

PGAdmin GUI Server Group Connections List Repository

A client opens PGAdmin GUI with the following messages:

{"success":0,"errormsg":"(sqlite3.OperationalError) no such table: user\n[SQL: SELECT anon_1.user_id AS anon_1_user_id, anon_1.user_email AS anon_1_user_email, anon_1.user_username AS anon_1_user_username, anon_1.user_password AS anon_1_user_password, anon_1.user_active AS anon_1_user_active, anon_1.user_confirmed_at AS anon_1_user_confirmed_at, anon_1.user_masterpass_check AS anon_1_user_masterpass_check, anon_1.user_auth_source AS anon_1_user_auth_source, role_1.id AS role_1_id, role_1.name AS role_1_name, role_1.description AS role_1_description \nFROM (SELECT user.id AS user_id, user.email AS user_email, user.username AS user_username, user.password AS user_password, user.active AS user_active, user.confirmed_at AS user_confirmed_at, user.masterpass_check AS user_masterpass_check, user.auth_source AS user_auth_source \nFROM user \nWHERE user.id = ?\n LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN (roles_users AS roles_users_1 JOIN role AS role_1 ON role_1.id = roles_users_1.role_id) ON anon_1.user_id = roles_users_1.user_id]\n[parameters: ('1', 1, 0)]\n(Background on this error at: http://sqlalche.me/e/13/e3q8)","info":"","result":null,"data":null}

This message means the repository of PostgreSQL database connections details including credentials of Server Group is unavailable for PGAdmin.

The repository of PostgreSQL database connections details including credentials of Server Group is stored in SQLLite 3 format under “%UserProfile%\AppData\Roaming\pgAdmin\pgadmin4.db”.

It is strongly suggested to backup this file frequently if you have a long list of Server Group connections.