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.

How to repeat a command for all the lines i a text file using vi

This is a handy command for all those using the vi editor to fix scripts with many lines. For example, I had to create statements like this:

catalog start with '/recovery_data/PRODDB01/archivelog/2021_05_06/o1_mf_1_391574_j98pt1n9_.arc';

and then execute it as a script in RMAN. I did the following

cd /recovery_data/PRODDB01/archivelog/2021_05_06
ls *.arc > register_06052021.cmd

open the file in vi:

vi register_06052021.cmd

check the number of lines:

:set number [enter]

press Shift+G [enter] - you're taken to the end of the file. In my case, the file had a total of 98 lines Go to the top of the file:

:0 [enter]

Now type to record your action:

q a I catalog start with '/recovery_data/PRODDB01/archivelog/2021_05_06/  ESC j q

Repeat it 97 times:

97 @ a

I also needed to add a closing '; at the end of each statement, to be able to pass it to RMAN. Turned out to be as easy as going to the top of the file again, and then execute:

q a A '; ESC j q

Repeat it 97 times:

97 @ a

How to clear a standby redo logfile and then dropping it

During set up of a physical standby database, one of the standby redo logfiles was marked as "ACTIVE" when querying the v$standby_log:

  THREAD#     GROUP#  SEQUENCE#      BYTES ARCHIVED  STATUS
---------- ---------- ---------- ---------- --------- ------------------------------
         0         12          0 2097152000 YES       UNASSIGNED
         0         13          0 2097152000 YES       UNASSIGNED
         0         14          0 2097152000 YES       UNASSIGNED
         0         15          0 2097152000 YES       UNASSIGNED
         0         16          0 2097152000 YES       UNASSIGNED
         0         17          0 2097152000 YES       UNASSIGNED
         0         18          0 2097152000 YES       UNASSIGNED
         1         11     392344 2097152000 YES       ACTIVE

The file is not even existing on my system, but the path was somehow copied from the primary database, which has a different file structure.
The query:

SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;

would return

  GROUP# TYPE                  MEMBER
---------- --------------------- ------------------------------------------------------------
        11 STANDBY               /backup/fast_recovery_area/PRODDB01/onlinelog/stb_redo01.log

The path /backup/fast_recovery_area/PRODDB01/onlinelog doesn't even exist on my server. This makes it impossible to drop and recreate it:

SQL> alter database drop standby logfile group 11;
alter database drop standby logfile group 11
*
ERROR at line 1:
ORA-00313: open failed for members of log group 11 of thread 1
ORA-00312: online log 11 thread 1:
'/fra/stdb/onlinelog/stb_redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Solution: Clear the logfile:

SQL> alter database clear logfile group 11;

Database altered.

Then drop and recreate the standby redo logfile:

SQL> alter database drop standby logfile group 11;

Database altered.

SQL> alter database add standby logfile group 11 ('/data1/oradata/PRODDB01/stb_redo01.log') size 2000M;

How to install the Oracle database sample schemas

1. go to https://github.com/oracle/db-sample-schemas/releases/latest and download the desired version of the sample schemas
2. Upload the zip file to your server
3. Unpack to any directory. $ORACLE_HOME/demo/schema is a good candidate
4. Prepare your file by running this perl command which replaces occurrences of the token `__SUB__CWD__` with your current directory:

perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat

4. Make sure you know the system and the sys password, and execute

sqlplus /nolog
@mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd EXAMPLE TEMP /u01/oracle/18c/demo/schema/log/ mydb

The list of arguments is pretty obvious: 

1. system password 

2. sys password 

3-6: passwords for the sample schemas you are about to install 

7. default tablespace for the samples schemas 

8. temporary tablespace for the sample schemas 

9. log locatation 

10. connect string. When using multitenant, you need to pass a pdb name as an argument. The cdb cannot be used

 
Read more here