Monthly Archives: July 2019

pupdel.sql and SQLPLUS_PRODUCT_PROFILE

This entry was posted in Oracle 12c Oracle 19c and tagged 19c dbca installation sqlplus on by

On Oracle 18c and 19c, the CreateDBCatalog.sql script has one more line calling:

/u00/app/oracle/product/19.3.0/sqlplus/admin/pupdel.sql

What is this pupdel.sql?

It creates a trigger after drop of user on the database, that will delete entries on SYSTEM.SQLPLUS_PRODUCT_PROFILE table belonging to this user.

But, what is SYSTEM.SQLPLUS_PRODUCT_PROFILE ?

This is the new name for PRODUCT_USER_PROFILE table, which allows to restrict usage of sqlplus based on keywords/attributes and users. PRODUCT_USER_PROFILE is now a synonym of SQLPLUS_PRODUCT_PROFILE and there is also the public view PRODUCT_PRIVS which shows the privileges of the current connected user.

From Oracle 19c all this is anyway desupported, as described at the documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/SQL-Plus-security.html#GUID-F94ACCFA-824F-4B58-B0D5-A73AB56DE3AA

WARNING: pga_aggregate_limit value is too high for the amount of physical memory on the system

This entry was posted in Oracle 12c on by

On Oracle 12.2 I have this fancy message after the database mounts. It is a datawarehouse and there are quite some memory.

The full message is:

2019-07-02T17:41:33.036963+02:00
 WARNING: pga_aggregate_limit value is too high for the
 amount of physical memory on the system
   PGA_AGGREGATE_LIMIT is 20480 MB
   PGA_AGGREGATE_TARGET is 10240 MB.
   physical memory size is 302063 MB
   limit based on physical memory and SGA usage is 512 MB
   SGA_TARGET is 276480 MB

There are 300GB of physical memory on the server.

SGA_TARGET is set to 270GB, PGA_AGGREGATE_LIMIT to 20GB. There should be 10GB available for the OS to run even when the PGA usage goes to the maximum it is defined.

So why does Oracle gives me this message?

The answer is in the documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/PGA_AGGREGATE_LIMIT.html#GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3

Even though PGA_AGGREGATE_LIMIT is set to a value different from 0, Oracle calculates so that it does not overpass 90% of the physical memory.

90% of 300GB is 270GB, minus 270GB from SGA size, means there is nothing left. Fortunately it is not in action, and I can see that by checking the maximum PGA used since instance start:

SQL> select round(value/1024/1024/1024) max_used from v$pgastat where name='maximum PGA allocated';
  MAX_USED
----------
        16