DataPump with CheckSum in 21c and above

 Oracle introduced a CheckSum parameter in 21c.   Here is a demo in 23.6.  

First I run the datapump export without the CheckSum:

[oracle@localhost ~]$ expdp hemant/hemant@freepdb1 schemas=HEMANT dumpfile=HEMANT

Export: Release 23.0.0.0.0 - Limited Availability on Sat Dec 28 12:31:26 2024
Version 23.6.0.24.10

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

Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Starting "HEMANT"."SYS_EXPORT_SCHEMA_01":  hemant/********@freepdb1 schemas=HEMANT dumpfile=HEMANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA/LOGREP
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "HEMANT"."MY_LOBS"                            7.9 MB   75929 rows
Master table "HEMANT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HEMANT.SYS_EXPORT_SCHEMA_01 is:
  /opt/oracle/admin/FREE/dpdump/2917EBB8705B3129E0630100007F3D2B/HEMANT.dmp
Job "HEMANT"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 28 12:32:06 2024 elapsed 0 00:00:38

[oracle@localhost ~]$

Now this is an export WITH the CheckSum (after verifying that COMPATIBLE is 20.0 or higher) by specifying CHECKSUM=YES :

[oracle@localhost ~]$ sqlplus hemant/hemant@freepdb1

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Sat Dec 28 12:35:20 2024
Version 23.6.0.24.10

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Sat Dec 28 2024 12:31:26 +00:00

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.6.0.24.10

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      23.6.0
noncdb_compatible                    boolean     FALSE
SQL> quit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.6.0.24.10
[oracle@localhost ~]$ expdp hemant/hemant@freepdb1 schemas=HEMANT dumpfile=HEMANT_With_CheckSum checksum=YES

Export: Release 23.0.0.0.0 - Limited Availability on Sat Dec 28 12:35:55 2024
Version 23.6.0.24.10

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

Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Starting "HEMANT"."SYS_EXPORT_SCHEMA_01":  hemant/********@freepdb1 schemas=HEMANT dumpfile=HEMANT_With_CheckSum checksum=YES
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA/LOGREP
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "HEMANT"."MY_LOBS"                            7.9 MB   75929 rows
Master table "HEMANT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Generating checksums for dump file set
******************************************************************************
Dump file set for HEMANT.SYS_EXPORT_SCHEMA_01 is:
  /opt/oracle/admin/FREE/dpdump/2917EBB8705B3129E0630100007F3D2B/HEMANT_With_CheckSum.dmp
Job "HEMANT"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 28 12:36:33 2024 elapsed 0 00:00:36

[oracle@localhost ~]$

After I transfer the dumpfile to another server, I verify the CheckSum with VERIFY_ONLY=YES:

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Sat Dec 28 12:40:54 2024
Version 23.6.0.24.10

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.6.0.24.10

SQL> create directory impdp_check as '/tmp';

Directory created.

SQL> quit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.6.0.24.10
[oracle@localhost ~]$ impdp directory=impdp_check dumpfile=HEMANT_With_CheckSum.dmp verify_only=YES

Import: Release 23.0.0.0.0 - Limited Availability on Sat Dec 28 12:42:28 2024
Version 23.6.0.24.10

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

Username: / as sysdba

Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Verifying dump file checksums
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
dump file set is complete
verified checksum for dump file "/tmp/HEMANT_With_CheckSum.dmp"
dump file set is consistent
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sat Dec 28 12:42:39 2024 elapsed 0 00:00:05

[oracle@localhost ~]$

[oracle@localhost ~]$ cd /tmp
[oracle@localhost tmp]$ ls -l import.log
-rw-r--r--. 1 oracle oinstall 600 Dec 28 12:42 import.log
[oracle@localhost tmp]$ cat import.log
;;;
Import: Release 23.0.0.0.0 - Limited Availability on Sat Dec 28 12:42:28 2024
Version 23.6.0.24.10

Copyright (c) 1982, 2024, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Verifying dump file checksums
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
dump file set is complete
verified checksum for dump file "/tmp/HEMANT_With_CheckSum.dmp"
dump file set is consistent
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sat Dec 28 12:42:39 2024 elapsed 0 00:00:05
[oracle@localhost tmp]$

The VERIFY_ONLY parameter verifies the file without actually importing the dump file.

The default CheckSum algorithm is SHA256.

You can override this by specifying CHECKSUM_ALGORITHM set to either of CRC32, SHA256, SHA384 or SHA512.

The GROUP BY column_position enhancement in 23ai

 Oracle 23ai allows specifying a Column Position (or Alias) in the GROUP BY clause.
For backward compatibility, the "group_by_position_enabled" parameter is a new feature that defaults to FALSE.

SQL> show parameter group_by_position_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
group_by_position_enabled            boolean     FALSE
SQL> alter session set group_by_position_enabled=TRUE;

Session altered.

SQL> 
SQL> select p.prod_name, t.day_name, sum(s.amount_sold)
  2  from sh.products p, sh.times t, sh.sales s
  3  where p.prod_id=s.prod_id
  4  and t.time_id=s.time_id
  5  and t.calendar_month_name = 'January'
  6  group by 1, 2
  7  order by 1, 2
  8  /

PROD_NAME                                          DAY_NAME  SUM(S.AMOUNT_SOLD)
-------------------------------------------------- --------- ------------------
11" Youth Field Master Glove                       Friday               4635.73
11" Youth Field Master Glove                       Monday               2903.62
11" Youth Field Master Glove                       Saturday             3636.85
11" Youth Field Master Glove                       Sunday               6602.18
11" Youth Field Master Glove                       Thursday             5696.37
11" Youth Field Master Glove                       Tuesday              2843.81
11" Youth Field Master Glove                       Wednesday            6072.04
11.5" Youth Triple Stripe Series Glove             Friday               6695.84
11.5" Youth Triple Stripe Series Glove             Monday               5436.28
11.5" Youth Triple Stripe Series Glove             Saturday              5653.8
11.5" Youth Triple Stripe Series Glove             Sunday              10909.86
...
...
multiple rows returned 
...
...
PROD_NAME                                          DAY_NAME  SUM(S.AMOUNT_SOLD)
-------------------------------------------------- --------- ------------------
Wicket Keeper Gloves                               Thursday             1550.94
Wicket Keeper Gloves                               Tuesday              3049.62
Wicket Keeper Gloves                               Wednesday            2583.16
Wide Brim Hat                                      Friday                189.28
Wide Brim Hat                                      Monday               1656.35
Wide Brim Hat                                      Saturday             1689.48
Wide Brim Hat                                      Sunday                 560.7
Wide Brim Hat                                      Thursday             1088.44
Wide Brim Hat                                      Tuesday              2855.67
Wide Brim Hat                                      Wednesday             250.19

461 rows selected.

SQL>

This helps developers who already use the Column Position in the ORDER BY clause and can be consistent when writing the GROUP BY clause.

The HAVING clause also supports Column Aliases.