How to extract all mountpoints for oracle data files in a database

To extract all unique mount points used for datafiles in a database, used the query below:

SELECT distinct SUBSTR(FILE_NAME, 1,
               INSTR(FILE_NAME, '/', -1, 1) --> Search for position of the first occurrence of the char '/', start at end of string
              -1) "PATH" --> starting from the rightmost side of the string, search from position found in INSTR above, and work your way to position 1
FROM DBA_DATA_FILES
;

Example output:

PATH
---------------------------------
/sales_db_02/oradata/SALES
/salesdb/oradata/SALES
/sales_db_01/oradata/SALES

If the database is in mount-mode (for example, a physical standby database), exchange dba_data_files with v$datafile:

 SELECT distinct SUBSTR(NAME, 1,
                   INSTR(NAME, '/', -1, 1)  
                  -1) "PATH" 
FROM v$datafile;

For logfiles, exchange dba_data_files with v$logfile and file_name with member:

  SELECT distinct SUBSTR(member, 1,
               INSTR(member, '/', -1, 1) 
              -1) "PATH" 
FROM v$logfile
;

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