Friday, January 15, 2021

What privileges are needed for flashback queries?

If the table is in your own schema, no extra privileges are needed. If the table is in a schema different from your own, you need object privileges to flashback the table:
grant flashback on scott.emp to jim;
or you can grant user jim a system privilege to flashback any table:
grant flashback any table to jim;

Friday, January 8, 2021

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'.

Wednesday, December 16, 2020

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

Friday, December 4, 2020

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

Tuesday, November 10, 2020

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