Oracle 19c import issues ORA-31693, ORA-02354, ORA-39002, ORA-39405

When performing import come across the following issue

ORA-31693: Table data object "ORCL"."ITEMCG" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39840: A data load operation has detected data stream format error .
ORA-39844: Bad stream format detected: [klaprs_62] [139751105749101] [139751105749012] [4] [2] [2065583] [] []

User may encounter this issue when new column added to a table with cokumn optimization enabled and the same column was modified.

Below are the workarounds

• Use access_method=EXTERNAL_TABLE during export

• Prior export add and drop a dummy column to the problematic table using

SQL> ALTER TABLE <table_name> ADD dummy number;
SQL> ALTER TABLE <table_name> DROP dummy number;

• Take export of failed table and import using CONTENT=DATA_ONLY as tableau structure already imported.

The fix for this bug was initially available on 19.13 and above see if it helps.

Also, I come across below issues when importing data

ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 33 into a target database with TSTZ version 32.

User may encounter this issue when export from higher time zone version and importing into lower time zone version.

• Patch the target database to higher or equal to source time zone patch or DST TZ version
• Create a database with same time zone and perform export/import

For latest DST patches refer,

• Oracle RDBMS and OJVM DST-related notes
• Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)

Hope this helps,

Thanks & Regards

https://oracleracexpert.com

Active Sessions, Proxy Sessions and Locked objects in Oracle

I have received few requests from Oracle User community to provide SQL queries related to active sessions, proxy sessions and locked objects in a session. Here are few SQL queries which you can use in day to day monitoring.

 
--------------------------------------------------------
--Script : ACTIVE SESSIONS
--Author : oracleracexpert.com
--------------------------------------------------------
SET PAGESIZE 1000
SET LINESIZE 600

COLUMN username FORMAT A10
COLUMN osuser FORMAT A10

COLUMN sid FORMAT 9999

COLUMN serial# FORMAT 999999999

COLUMN status FORMAT A10
COLUMN machine FORMAT A20
COLUMN program FORMAT A40
COLUMN module FORMAT A35
COLUMN action FORMAT A15
COLUMN logon_time FORMAT A20

SELECT s.username,

s.osuser,

s.sid,

s.serial#,

p.spid,

s.lockwait,

s.status,

s.machine,

s.program,

s.module,

s.action,

TO_CHAR(s.logon_Time,'MM-DD-YYYY HH24:MI:SS') AS logon_time,

s.blocking_session_status AS BlockStatus

FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;

------------------------------------

--Script : Active Session waits

--Author : oracleracexpert.com

------------------------------------

SET PAGESIZE 1000

SET LINESIZE 600

COLUMN username FORMAT A10

COLUMN osuser FORMAT A10

COLUMN sid FORMAT 9999

COLUMN serial# FORMAT 999999999

COLUMN spid FORMAT A10

COLUMN state FORMAT A10

COLUMN wait_class FORMAT A20

COLUMN seconds_in_wait FORMAT 999999999

COLUMN module FORMAT A35

COLUMN blocking_session FORMAT A20

COLUMN blocking_session_status FORMAT A20

SELECT s.username,

       s.osuser,

       s.sid,

       s.serial#,

   p.spid, 

       s.state,

   s.wait_class,

       s.seconds_in_wait,

       s.module,

       TO_CHAR(s.logon_Time,'MM-DD-YYYY HH24:MI:SS') AS logon_time,

   s.blocking_session,

       s.blocking_session_status AS BlockStatus

FROM   v$session s, v$process p

WHERE  s.paddr  = p.addr

AND    s.status = 'ACTIVE'

ORDER BY 1,2;

--------------------------------------------------------
--Script : PROXY SESSIONS
--Author : oracleracexpert.com
--------------------------------------------------------
SET PAGESIZE 1000
SET LINESIZE 600

COLUMN username FORMAT A10
COLUMN osuser FORMAT A10

COLUMN sid FORMAT 9999

COLUMN serial# FORMAT 999999999

COLUMN status FORMAT A10
COLUMN machine FORMAT A20
COLUMN program FORMAT A40
COLUMN module FORMAT A35
COLUMN action FORMAT A15
COLUMN logon_time FORMAT A20

SELECT s.username,

s.osuser,

s.sid,

s.serial#,

p.spid,

s.lockwait,

s.status,

s.machine,

s.program,

s.module,

s.action,

TO_CHAR(s.logon_Time,'MM-DD-YYYY HH24:MI:SS') AS logon_time,

s.blocking_session_status AS BlockStatus

FROM  v$session s, v$process p, v$session_connect_info sci
WHERE s.paddr = p.addr
AND s.sid = sci.sid
AND s.serial# = sci.serial#
AND sci.authentication_type = 'PROXY'
ORDER BY s.username, s.osuser;

--------------------------------------------------------
--Script : LOCKED OBJECT SESSIONS
--Author : oracleracexpert.com
--------------------------------------------------------
SET PAGESIZE 1000
SET LINESIZE 600

COLUMN sid FORMAT 9999

COLUMN serial# FORMAT 999999999

COLUMN status FORMAT A10
COLUMN owner FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A15
COLUMN oracle_username FORMAT A15
COLUMN locked_mode FORMAT A15
COLUMN os_user_name FORMAT A15

SELECT s.sid,

s.serial#,

s.status,

do.owner,

do.object_name,

do.object_type,

lo.oracle_username,

Decode(lo.locked_mode, 0, 'None',

1, 'Null (NULL)',

2, 'Row-S (SS)',

3, 'Row-X (SX)',

4, 'Share (S)',

5, 'S/Row-X (SSX)',

6, 'Exclusive (X)',

lo.locked_mode) locked_mode,

lo.os_user_name

FROM v$locked_object lo, dba_objects do, v$session s
WHERE lo.session_id = s.sid
AND do.object_id = lo.object_id
ORDER BY 1, 2, 3, 4;

Thanks & Regards,

https://oracleracexpert.com