How does Oracle Data Pump Import handles referential integrety constraint violations?

How does Oracle Data Pump Import handles referential integrety constraint violations?

To find out, let's use the two tables SHIPS and PORTS, which are connected by a referential constraint from SHIPS to PORTS, called FK_SHIPS_PORTS.

CREATE TABLE SHIPS
(
  SHIP_ID       NUMBER,
  SHIP_NAME     VARCHAR2(20 BYTE),
  CAPACITY      NUMBER,
  LENGTH        NUMBER,
  HOME_PORT_ID  NUMBER,
  LIFEBOATS     NUMBER(3),
  CONSTRAINT PK_SHIPS PRIMARY KEY (SHIP_ID),
  CONSTRAINT FK_SHIPS_PORTS FOREIGN KEY (HOME_PORT_ID) 
  REFERENCES PORTS (PORT_ID)
);

CREATE TABLE PORTS
(
  PORT_ID    NUMBER,
  PORT_NAME  VARCHAR2(20 BYTE),
  COUNTRY    VARCHAR2(40 BYTE),
  CAPACITY   NUMBER,
  CONSTRAINT PK_PORTS PRIMARY KEY (PORT_ID)
);

In other words, you cannot add a ship without a valid PORT_ID that already exist in table PORTS.

PORT_ID	PORT_NAME	COUNTRY	CAPACITY
1	Baltimore	USA
2	Charleston	USA
3	Tampa	USA
4	Miami	USA

SHIP_ID	SHIP_NAME	CAPACITY	LENGTH	HOME_PORT_ID	LIFEBOATS
1	Codd Crystal	2052	855	1	80
15	Codd Champion	2000	650		30
2	Codd Elegance	2974	952	2	95
16	Codd Victorious	2055	876	2
17	Codd Grandeur	2030	840	4
18	Codd Prince	1500	550	2	32
20	Codd Norway	1500	900	3	80

I now remove one row from the PORTS table, so that new rows being imported will have a missing parent key. In order to do that I need to also remove any child record from SHIPS, in my case, only one:

DELETE TESTUSER.SHIPS WHERE HOME_PORT_ID = 1;
DELETE TESTUSER.PORTS WHERE PORT_ID = 1;
COMMIT;

1 row deleted.
1 row deleted.
Commit complete.

SCENARIO 1: "What happens if we use TABLE_EXISTS_ACTION=REPLACE when the table being replaced has a referential constraint to another table?"

Result:

• The operation will report an error because of a constraint violation issue.
• All rows are imported into SHIPS, including the offending ones
• The referential constraint is dropped.

Error message:
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."SHIPS" 7.289 KB 7 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (TESTUSER.FK_SHIPS_PORTS) - parent keys not found
Failing sql is:
ALTER TABLE "TESTUSER"."SHIPS" ADD CONSTRAINT "FK_SHIPS_PORTS" FOREIGN KEY ("HOME_PORT_ID") REFERENCES "TESTUSER"."PORTS" ("PORT_ID") DEFERRABLE ENABLE


SCENARIO 2: "What happens if we use TABLE_EXISTS_ACTION=TRUNCATE when the table being truncated has a referential constraint to another table?"

Result:

• The operation will report an error because of a constraint violation issue.
• The SHIPS table is left truncated, no rows are loaded.
• The referential constraint stays.

Error message:
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "TESTUSER"."SHIPS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TESTUSER"."SHIPS" failed to load/unload and is being skipped due to error:
ORA-02291: integrity constraint (TESTUSER.FK_SHIPS_PORTS) violated - parent key not found


Both cases requires manual intervention afterwards to cleanup offending rows, and then to recreate the constraint.
One way to do so would be to allow for orphan rows through temporarily disabling the index, loading the rows, then reenabling the index using the NOVALIDATE option.

First disable the constraint:
SQL> ALTER TABLE SHIPS DISABLE CONSTRAINT FK_SHIPS_PORTS;

Table altered.

Load the data once more:
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "TESTUSER"."SHIPS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."SHIPS" 7.289 KB 7 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Finally, enable the constraint again. However, tell oracle to disregard existing rows, only apply the constraint to new rows:
SQL> ALTER TABLE SHIPS ENABLE NOVALIDATE CONSTRAINT FK_SHIPS_PORTS;

Table altered.

SQL>

Example is based on Steve O'Heam's "SQL Certified SQL Expert Exam Guide"

How to drop a table partition without invalidating global indexes

My interval range partitioned table looks as follows:

CREATE TABLE SEGMENT_SIZES(
  SEGMENT_SIZES_ID NUMBER,
  STIMESTAMP      DATE,
  OWNER           VARCHAR2(30 BYTE),
  SEGMENT_NAME    VARCHAR2(30 BYTE),
  PARTITION_NAME  VARCHAR2(30 BYTE),
  SEGMENT_TYPE    VARCHAR2(20 BYTE),
  BYTES           NUMBER,
  CONSTRAINT SEGMENT_SIZES_PK PRIMARY KEY (SEGMENT_SIZES_ID)
)
PARTITION BY RANGE (STIMESTAMP )
-- Use 11gR1 Interval Partitioning
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION P062013 VALUES LESS THAN (TO_DATE('01.07.2013','DD.MM.YYYY'))
)
TABLESPACE TOOLS
ENABLE ROW MOVEMENT
COMPRESS FOR ALL OPERATIONS;

It was populated with data, which created the needed partititions automatically.
The primary key defined on the table will of course create a global index spanning all partitions.
Consequently, during partition maintenance operations, you will end up with an UNUSUABLE primary key index if you drop a partition, as follows:

ALTER TABLE SEGMENT_SIZES DROP PARTITION SYS_P42;

However, if you you add the "UPDATE INDEXES" clause, oracle will update the global index; the index will remain USABLE:

ALTER TABLE SEGMENT_SIZES DROP PARTITION SYS_P42 UPDATE INDEXES;

Keep in mind that you cannot drop the highest range partition of an interval-partitioned table!

Source: Oracle Documentation

why am I getting ORA-14006: invalid partition name when attempting to drop a partition in TOAD?

In TOAD you are trying to drop a partition, as follows:

alter table segment_sizes drop partition SYS_P41;

But you get ORA-14006: invalid partition name as a result.

Solution: remove the ";" at the end of the statement, and try again.
Alternatively, execute the statement through SQL*plus.

Source: Derya Oktay's Oracle Weblog

How to use the sqlplus "autotrace" facility

SET AUTOT ON
Executes the SQL and returns the execution plan and statistics (shorthand for "set autotrace on")

SET AUTOT TRACE
Executes the SQL and returns the execution plan and statistics (shorthand for "set autotrace traceonly")

Note: Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.

SET AUTOT TRACE EXP
Explains the SQL, omits statistics and does not execute the SQL(shorthand for "set autotrace traceonly explain")
The SQL statement is never execute for real, only explained.

SET AUTOT TRACE EXP STAT
Executes the SQL, displays the execution plan, displays statistics. Executes the SQL, but supresses the output (shorthand for "set autotrace traceonly explain statistics")

SET AUTOT OFF
Disables autotraceing (shorthand for "set autotrace off")

If you have trouble getting the autotrace feature to work, make sure you have created the PLUSTRACE role.

Source: Oracle 19c Documentation

sdtperfmeter - a graphical monitoring of cpu, paging and I/O on Solaris

Create a file called monitor.sh:

#!/usr/bin/bash

/usr/dt/bin/sdtperfmeter -t page &
/usr/dt/bin/sdtperfmeter -t cpu &
/usr/dt/bin/sdtperfmeter -t disk &

exit

Start your X server, set display to your client, then execute in background:

./monitor.sh &

Works on Solaris 10 (and several previous versions)

How to generate a script to rebuild unusable index partitions

set trimspool on
set lines     200
set pages     0
set heading   off
set verify    off
set feedback  off
set echo      off
spool rebuild_stmts.sql
--Rebuild the index partition to the same tablespace:
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ';'
FROM   dba_indexes idx, dba_ind_partitions idxpart
WHERE  idx.table_owner = 'SCOTT'
AND    idx.index_name = idxpart.index_name
AND    idxpart.status <> 'USABLE'
ORDER BY idx.table_owner, idx.index_name
/
--For a specific INDEX, reallocating the index partitions to a new tablespace:
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE MYTABLESPACE;'
FROM    dba_indexes idx, dba_ind_partitions idxpart
WHERE   idx.table_owner = 'SCOTT'
AND     idx.index_name = idxpart.index_name
AND     idx.index_name = 'IDX_01'
AND     idxpart.status <> 'USABLE'
ORDER BY idx.table_owner, idx.index_name
/
--Use sqlplus variables, also reallocating the index partitions to a new tablespace:
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE MYTABLESPACE;'
FROM    dba_indexes idx, dba_ind_partitions idxpart
WHERE   idx.table_owner = 'SCOTT'
AND     idx.index_name = idxpart.index_name
AND     idx.index_name = '&&index_name'
AND     idxpart.status <> 'USABLE'
ORDER BY idx.table_owner, idx.index_name
/
-- Rebuild index subpartitions. Also add a parallel statement and the ONLINE keyword:
SELECT 'alter index ' || IDX.TABLE_OWNER || '.' ||  IDX.INDEX_NAME || ' rebuild subpartition ' || IDXSUBPART.SUBPARTITION_NAME || ' parallel 4 tablespace ' || IDXSUBPART.TABLESPACE_NAME ||' online;'
FROM    DBA_INDEXES IDX, DBA_IND_SUBPARTITIONS IDXSUBPART
WHERE   IDX.TABLE_OWNER IN ('SCOTT','JAMES')
AND     IDX.INDEX_NAME = IDXSUBPART.INDEX_NAME
AND     IDX.INDEX_NAME IN (SELECT UNIQUE INDEX_NAME
                                        FROM DBA_IND_SUBPARTITIONS
                                        WHERE STATUS = 'UNUSABLE')
AND INDEX_OWNER NOT IN ('SYS','SYSTEM')
AND IDXSUBPART.STATUS <> 'USABLE'
ORDER BY IDX.OWNER, IDX.INDEX_NAME
/

Upon completion, the file "rebuild_stmts.sql" should contain your "ALTER INDEX REBUILD PARTITION..." directives.

How to display session information, followed by a kill-statement

Often, my customers ask me to kill a runaway session for them, typically this will be in the form "Can you please kill session with session id 170 for us?. It's urgent!"

Before I do so, I'd like to be one houndred percent certain that I kill the right session.
To help me in such situations, I execute the script below. It will take one parameter, the sessions SID, and echo back some basic information about what the session is doing. Finally it prints a "kill session" command, which I can choose to execute if it was indeed the right session.

set verify           off
set linesize 200
col "os process"     format a30
col "os process id"  format a10
col "osuser"         format a15
col "schemaname"     format a20
col "client program" format a20
col "client name"    format a20
col "session type"   format a20
col status           format a10
col "session type"   format a15
cle scr
prompt ===================================================
prompt This script will print basic information about a
prompt session.
prompt
accept SID prompt 'Pls enter SID of session: '
prompt ===================================================
prompt  Information about database session &&SID
prompt ===================================================
column host_name new_value v_hostname;

set termout off
select host_name
from   v$instance;
set termout on

select
        p.program "os process",
        p.spid "os process id",
        p.username "osuser",
        s.sid,s.serial# "serial num",
        lower(s.schemaname) "schemaname",
        lower(s.osuser) "client name",
        s.program "client program",
        lower(s.status) "status",
        lower(s.type) "session type"
from v$process p, v$session s
where p.program not like 'oracle@v_hostname (%'
and p.addr = s.paddr
and p.addr = (select paddr from v$session where sid=&&SID)
order by s.username asc
/
set heading off
prompt ===================================================
prompt  Kill statement for session &&SID:
prompt ===================================================
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
from   v$process p, v$session s
where  p.program not like 'oracle@v_hostname (%'
and    p.addr = s.paddr
and    p.addr = (select paddr from v$session where sid=&&SID)
order by s.username asc
/

exit