Workaround for ORA-39358 during import

When running import, you may sometimes run into the problem below:

impdp parfile=myparfile.par

Import: Release 12.2.0.1.0 - Production on Tue Jun 8 14:54:34 2021

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-39002: invalid operation
ORA-39358: Export dump file version 18.0.0 not compatible with target version 12.2.0

Solution:

 oerr ora 39358
39358, 00000, "Export dump file version %s not compatible with target version %s"
// *Cause:    The Oracle Data Pump export job version was newer than the target
//            compatibility version.
// *Action:   Upgrade the target database to a compatibility level of at least
//            the export job version, or rerun the export job with a job
//            version that is lower than or equal to the compatibility version
//            of the target database.

The simplest way out for me was to downgrade the export. On the source database server, add the following parameter to your export file:

VERSION=12.2

So that my complete parameter file looked as follows:

userid=system/passwd
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=HR.dmp
LOGFILE=exp_HR.log
JOB_NAME=HR
EXCLUDE=STATISTICS
schemas=HR
VERSION=12.2

Rerun the export:

expdp parfile=myparfile.par

Transfer the newly generated dumpfile to your destination server, and rerun the import. This time around, you shouldn't see any errors relating to incompatible versions.

How to fix error message from data guard broker "Property 'DbFileNameConvert' has inconsistent values"

After a rebuild of a physical standby database, I was tailing the broker log file drcSALES.log, and noticed the following warning:

Property 'DbFileNameConvert' has inconsistent values:
METADATA='/data1/oradata/SALES, /data1_tstb/oradata/SALES, /data2/oradata/SALES, /data2_tstb/oradata/SALES', 
SPFILE=  '/data1/oradata/SALES, /data1_tstb/oradata/SALES, /data2/oradata/SALES, /data2_tstb/oradata/SALES', 
DATABASE='/data1/oradata/SALES/, /data1_tstb/oradata/SALES/, /data2/oradata/SALES/, /data2_tstb/oradata/SALES/'

Note that the metadata and spfile is missing the trailing slash character /.

To fix this incorrect setup:

dgmgrl / as sysdba
show configuration;
Configuration - SALES

  Protection Mode: MaxPerformance
  Members:
  SALES      - Primary database
    SALES_STB  - Physical standby database
      SALES_TSTB - Physical standby database (receiving current redo)
    SALES_RO   - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

Check the current setting:

show database 'SALES_TSTB' DbFileNameConvert
DbFileNameConvert = '/data1/oradata/SALES, /data1_tstb/oradata/SALES, /data2/oradata/SALES, /data2_tstb/oradata/SALES'

Update the property:

DGMGRL> edit database 'SALES_STB' set property DbFileNameConvert='/data1/oradata/SALES/,/data1_stb/oradata/SALES/, /data2/oradata/SALES/, /data2_stb/oradata/SALES/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "dbfilenameconvert" updated

Verify the new setting:

show database 'SALES_TSTB' DbFileNameConvert
DbFileNameConvert = '/data1/oradata/SALES/, /data1_tstb/oradata/SALES/, /data2/oradata/SALES/, /data2_tstb/oradata/SALES/'

Restart your physical database, and the issue is fixed.

Is default partition supported for partitioned tables in PostgreSQL?

Yes it is!

From version 11, it is possible to create a default partition for partitioned tables in PostgreSQL.

See the documentation for further details.

In version 12, several improvements to partitioning was introduced. See this blog post for details.

How to trim output from a psql file - equivalent to SET commands in sqlplus

I have the following query, which will generate DML:

SELECT 'drop table ' || table_schema || '.' || table_name ||';'
FROM information_schema.tables
WHERE table_schema = 'myschema'
AND table_name like '%2020%'
AND table_type = 'BASE TABLE';

I put it in a file called gen_drop.sql

When executed like this:

 psql  testdb01 -f gen_drop_tab.sql -L gen_drop_tab.log

The file output will show header and row count, like this:

                   ?column?
-----------------------------------------------
 drop table myschema.tabA_2020_09;
 drop table myschema.tabB_2020_09;
 drop table myschema.tabC_2021_05;

(27 rows)

To avoid this, add the -t flag to your command line. -t is "print tuples only"

psql  testdb01 -f gen_drop_tab.sql -o drop_tab.sql -t

To translate into the world of Oracle, I consider the above to be the equivalent of spooling a file using sqlplus and adding the directives:

set trimspool on
set pages 0
set heading off
set feedback off
set verify off
set echo off

Workaround for ORA-14634 during an attempt to split a partition containing subpartitions

Short background:
I had a LIST/RANGE partitioned tables that needed a new partition.

The table is partitioned BY LIST on column STREAM, and subpartitioned BY RANGE on column LASTUPDATED. 

Each subpartition is placed in a tablespace according to the year in the LASTUPDATED column. 

So for example, no matter which partition the subpartition belongs to, it will always go into tablespace DATA_2019 if the LASTUPDATED column has a value which falls within the boundaries of year 2019. 

This is called vertically striping of partitions and is often, but now always, set up using a subpartition template.

DDL for the table:

  CREATE TABLE CATEGORIES
(
  UID              VARCHAR2(255 BYTE),
  CREATED          TIMESTAMP(6)                 NOT NULL,
  LASTUPDATED      TIMESTAMP(6)                 NOT NULL,
  BODY             CLOB,
  STREAM           VARCHAR2(255 BYTE)
)
LOB (SBODY) STORE AS SECUREFILE BLOB (
  TABLESPACE  DATA1
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  COMPRESS    MEDIUM
)
COMPRESS FOR OLTP
TABLESPACE DATA1
PARTITION BY LIST (STREAM)
SUBPARTITION BY RANGE (LASTUPDATED)
(
 -- partition definition ..
 	-- subpartition definition..
);
  

I had the following SQL intended to split the partition OTHERS into a brand new partition called DIGITAL_MEDIA:

ALTER TABLE categories
 SPLIT PARTITION OTHERS VALUES
 ('audiobooks')
 INTO (
       PARTITION DIGITAL_MEDIA
       TABLESPACE DATA1
     (
     	 -- 2016
        SUBPARTITION SP_dm_older 
        VALUES LESS THAN ( TO_DATE('2016-01', 'YYYY-MM')) TABLESPACE DATA1,
        subpartition SP_dm_201601 
        VALUES LESS THAN (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2016,
        LOB (BLOB) STORE AS SECUREFILE  ( TABLESPACE DATA_2016 ),
        VALUES LESS THAN (TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2016,
        LOB (BLOB) STORE AS SECUREFILE  ( TABLESPACE DATA_2016 ),
        .
        .
        .
        subpartition SP_dm_202411 
        VALUES LESS THAN (TO_DATE(' 2024-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2024
        LOB (BLOB) STORE AS SECUREFILE  ( TABLESPACE ATOMHOPPER_2024 ),
        subpartition SP_dm_202412 
        VALUES LESS THAN (TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2024
        LOB (BLOB) STORE AS SECUREFILE  ( TABLESPACE DATA_2024 ),
        SUBPARTITION SP_DM_DEFAULT VALUES LESS THAN (MAXVALUE) TABLESPACE DATA1
        LOB (BLOB) STORE AS SECUREFILE  ( TABLESPACE DATA1)
        )
     ,
PARTITION OTHERS
)
ONLINE
UPDATE GLOBAL INDEXES;

There were a total of 110 subpartitioned listed in the above DDL. When trying to execute the script, I received:

ORA-14634: Subpartition descriptions cannot be specified during the SPLIT/MERGE of a partition of a Range-List partitioned table

Looking it up:

oerr ora 14634
// *Cause:  During a split or a merge of a partition of a range list
//          partitioned table you cannot specify any subpartitioning
//          information for the resulting partition (s)
// *Action: Remove all subpartitioning information from the DDL.

A rather clear message, which I followed, and thus changed my DDL to this:

ALTER TABLE categories
 SPLIT PARTITION OTHERS VALUES
 ('audiobooks')
 INTO (
       PARTITION DIGITAL_MEDIA
     , PARTITION OTHERS)
ONLINE
UPDATE GLOBAL INDEXES;

indeed a much simpler syntax.

 
It parsed without errors, and the result was a new partition, with system-generated names, placed in the correct tablespaces.

Let's verify the results:

SELECT table_name,partition_name,subpartition_name,tablespace_name
FROM DBA_TAB_SUBPARTITIONS 
WHERE TABLE_NAME='CATEGORIES' 
AND PARTITION_NAME='DIGITAL_MEDIA';
 

TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	TABLESPACE_NAME
CATEGORIES	DIGITAL_MEDIA	SYS_SUBP39427	DATA_2016
CATEGORIES	DIGITAL_MEDIA	SYS_SUBP39428	DATA_2016
CATEGORIES	DIGITAL_MEDIA	SYS_SUBP39429	DATA_2016
CATEGORIES	DIGITAL_MEDIA	SYS_SUBP39527	DATA_2024
CATEGORIES	DIGITAL_MEDIA	SYS_SUBP39528	DATA_2024
CATEGORIES	DIGITAL_MEDIA	SYS_SUBP39529	DATA_2024

The fact that we for this particular partition now must deal with system generated names is of no importance. The optimizer will still use partition pruning when appropriate.

How to find the opening and closing time for the maintenance windows in an Oracle database

To see the last 7 days of opening/closing time for the maintenance windows, use this query:

SELECT window_name,to_char(start_time,'dd.mm.yyyy hh24:mi:ss') "start time", duration 
FROM dba_autotask_schedule 
ORDER BY start_time desc fetch first 7 rows only;

Example output:

WINDOW_NAME	start time	DURATION
SATURDAY_WINDOW	19.06.2021 06:00:00	+00 20:00:00.000000
FRIDAY_WINDOW	18.06.2021 22:00:00	+00 04:00:00.000000
THURSDAY_WINDOW	17.06.2021 22:00:00	+00 04:00:00.000000
WEDNESDAY_WINDOW	16.06.2021 22:00:00	+00 04:00:00.000000
TUESDAY_WINDOW	15.06.2021 22:00:00	+00 04:00:00.000000
MONDAY_WINDOW	14.06.2021 22:00:00	+00 04:00:00.000000
SUNDAY_WINDOW	13.06.2021 06:00:00	+00 20:00:00.000000

Simple script for finding a leftover FK constraint to an interim table after a redefinisjon

After a redefinition of a table, you may be stuck with some FK constraints from other tables, to the old interim tables.

This simple script fixed my problem:

alter session set nls_language='american';
set lines 200
col table_name format a30
col constraint_name format a30
col r_constraint_name format a30
col status format a20

SELECT  TABLE_NAME, CONSTRAINT_NAME
FROM    DBA_CONSTRAINTS
WHERE   OWNER = '&&owner'
AND     CONSTRAINT_TYPE='R'
AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='&&interim_table');

prompt copy and paste output above as input to owner and table_name:
prompt
ALTER TABLE &&owner..&&table_name DROP CONSTRAINT &constraint_name;
DROP TABLE &&owner..&&interim_table PURGE;
exit

Result:

Enter value for owner: SCOTT
old   3: WHERE   OWNER = '&&owner'
new   3: WHERE   OWNER = 'SCOTT'
Enter value for interim_table: ORDERS_INTERIM
old   5: AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='&&interim_table')
new   5: AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='ORDERS_INTERIM')

TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------
ORDERROWS                      TMP$$_FK__ORD__ENT0

copy and paste output above as input to owner and table_name:
Enter value for table_name: ORDERROWS
Enter value for constraint_name: TMP$$_FK__ORD__ENT0
old   1: ALTER TABLE &&owner..&table_name DROP CONSTRAINT &constraint_name
new   1: ALTER TABLE SCOTT.ORDERROWS DROP CONSTRAINT TMP$$_FK__ORD__ENT0

Table altered.

old   1: DROP TABLE &&owner..ORDERS_INTERIM PURGE
new   1: DROP TABLE SCOTT.ORDERS_INTERIM PURGE

Table dropped.