How to disable a scheduler job in a another schema

Use the syntax

connect system
set serveroutput on
EXECUTE DBMS_SCHEDULER.DISABLE(name=>'SCOTT.UPDATE_COMMISIONS');

You cannot login as SYS to perform this operation, you need to be logged in as another user with the required preivileges (such as SYSTEM, in this case). Documented here

Comparison between default traditional auditing and default unified auditing

Applicable to Oracle Database versions 12.1 and onwards. By default, the databases are created with "Mixed-Mode" auditing enabled. This means that the unified auditing polices ORA_SECURECONFIG og ORA_LOGON_FAILURE are enabled. These policies actually covers a lot of what any DBA would typically want to audit, and overlaps the default (tradtitional) auditing implemented by running $ORACLE_HOME/rdbms/admin/secconf.sql.

Here is a table comparing what the two default settings under each auditing strategy:

My conclusion is that unless your customer specifically wants to continue with traditional auditing, take the opportunity to migrate to Unified Auditing policies implmemented in "Mixed-Mode" auditing. In such cases you should also disable the default traditional auditing by setting the parameter audit_trail to 'NONE'.

How to fix error SYS.DBMS_BACKUP_RESTORE version 18.11.00.00 in TARGET database is not current

Even if you're not using a recovery catalog, you may encounter this issue if you have recently patched your database. Simply by connecting to the target database as sysdba, you can verify that you need to update your internal packages:

oracle@myserver.mydomain.com:[proddb01]# rman target / nocatalog

Recovery Manager: Release 18.0.0.0.0 - Production on Wed Dec 16 13:51:43 2020
Version 18.12.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

PL/SQL package SYS.DBMS_BACKUP_RESTORE version 18.11.00.00 in TARGET database is not current
PL/SQL package SYS.DBMS_RCVMAN version 18.11.00.00 in TARGET database is not current
connected to target database: PRODDB01 (DBID=1234567890)
using target database control file instead of recovery catalog

Solution: Follow Doc ID 888818.1: "Rman 06190 Connecting to target database after upgrade" In short, connect to the database as sysdba, and run the following scripts:

SQL> @$ORACLE_HOME/rdbms/admin/dbmsrman.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtrmns.plb   
SQL> @$ORACLE_HOME/rdbms/admin/dbmsbkrs.sql 
SQL> @$ORACLE_HOME/rdbms/admin/prvtbkrs.plb

How to view the contens of a zip file without extracting it

Method 1:

zip -sf myzipfile.zip

Method 2:

zipinfo myzipfile.zip

How to mark an existing SQL Plan baseline as fixed

Thanks a lot to Tim Hall for providing this simple example:

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_7ff180a4583f257d',
    plan_name       => 'SQL_PLAN_7zwc0njc3y9bx2c993bf4',
    attribute_name  => 'fixed',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

The details for the plan baselines can be found like this:

SELECT sql_handle,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE plan_name ='SQL_PLAN_7zwc0njc3y9bx2c993bf4';

Result:

SQL_HANDLE	PLAN_NAME	ORIGIN	ENABLED	ACCEPTED	FIXED
SQL_7ff180a4583f257d	SQL_PLAN_7zwc0njc3y9bx2c993bf4	AUTO-CAPTURE	YES	YES	YES

After fixing the plan, all the plan alternatives for the SQL ID can be seen below:

SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active",
       SQL_ID,
       PLAN_HASH_VALUE,
       SQL_PLAN_BASELINE "BASELINE", 
       SQL_PROFILE,
       IS_RESOLVED_ADAPTIVE_PLAN,
       CHILD_NUMBER AS "Child Num",
       ELAPSED_TIME,
       EXECUTIONS,
       ROUND(ELAPSED_TIME/1000000) "duration (sec)"
       ,CASE WHEN EXECUTIONS > 0 THEN
        ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1)
        ELSE
            NULL
       END "sec per exe"
FROM V$SQL 
WHERE SQL_ID in('2mympbsn3r4rk')
ORDER BY sql_id,LAST_ACTIVE_TIME DESC;

Result:

last active	SQL_ID	PLAN_HASH_VALUE	BASELINE	SQL_PROFILE	IS_RESOLVED_ADAPTIVE_PLAN	Child Num	ELAPSED_TIME	EXECUTIONS	duration (sec)	sec per exe
04.12.2020 09:49	2mympbsn3r4rk	480132689	SQL_PLAN_7zwc0njc3y9bx3bf43977			1	612838711	116	613	5,3
04.12.2020 09:49	2mympbsn3r4rk	3102497174	SQL_PLAN_7zwc0njc3y9bx2c993bf4		Y	2	203961	191	0	0
04.12.2020 08:49	2mympbsn3r4rk	480132689	SQL_PLAN_7zwc0njc3y9bx3bf43977			0	2247452482	30	2247	74,9

Date formatting in PostgreSQL

select action_type, to_char(action_date,'DD Mon YYYY'), count(*) from actions group by action_type,

action_type     |   to_char   | count
-----------------+-------------+-------
 action1 | 27 Oct 2020 | 47831
 action1 | 22 Oct 2020 |   640
 action1 | 20 Oct 2020 |     1
 action1 | 22 Oct 2020 |  1654
 action1 | 26 Oct 2020 |   290
 action2 | 21 Oct 2020 |     8
 action2 | 27 Oct 2020 |   140
 action2 | 26 Oct 2020 |   900

Documentation here

How to change the control_files parameter used in the spfile

Mind your syntax when you are changing certain multi-value parameters directly in the spfile. Oracle will tell you that the syntax is accepted, but it will give you an error later. The following example is from an attempt to change the control_files parameter.

Won't work:

SQL> alter system set control_files='/oradata/proddb01/control01.ctl, /fra/proddb01/control02.ctl' scope=spfile;

System altered.

During mount stage, this will give you the following message in the database's alert log:

ORA-205 signalled during: ALTER DATABASE   MOUNT...
ORA-00202: control file: /oradata/proddb01/control01.ctl, /fra/proddb01/control02.ctl
ORA-27037: cannot obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

Works:

SQL> alter system set control_files='/oradata/proddb01/control01.ctl','/fra/proddb01/control02.ctl' scope=spfile;

System altered.

You should enclose both values within ' ' apostroph characters.