DataPump Export (EXPDP) Creates Excessive Traces Even If It Runs Successfully (Doc ID 1154963.1)

DataPump Export (EXPDP) Creates Excessive Traces Even If It Runs Successfully (Doc ID 1154963.1) To BottomTo Bottom


APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS
A simple 11.2 DataPump export (EXPDP) at SCHEMA level (e.g. on SCOTT schema) can be shown to generate a number of DWxx (and also seen from P00x and ORAxx processes) traces in /diag/trace directory. The traces are of the following nature: *** MODULE NAME:(Data Pump Worker) *** ACTION NAME:(SYS_EXPORT_FULL_02) KUPC: Setting remote flag for this process to FALSE prvtaqis - Enter *** 2010-07-15 23:00:23.133 prvtaqis subtab_name upd prvtaqis sys table upd *** 2010-07-15 23:00:23.831 kwqberlst !retval block kwqberlst rqan->lagno_kwqiia 5 kwqberlst rqan->lascn_kwqiia > 0 block


Traces are typically < 100K in length. If the EXPDP run was successful the traces contain no error messages, if EXPDP raises an internal 600/7445 or other errors the traces might contain some equivalent information.


SOLUTION
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The first messages come from package body DBMS_PRVTAQIS which is called during EXPDP/IMPDP. ORACLE Support can confirm that in 11.2 source code the call to DBMS_PRVTAQIS is not considered to be debugging code so there is no option (event/parameter) to disable these traces. The traces will just need to be periodically deleted. It should be noted that EXPDP/IMPDP are not the only products to call DBMS_PRVTAQIS, any product that does can also create these traces and the MODULE NAME in the traces will clarify this. FIXED RELEASE ---- 11.2.0.4 PATCH FIX ---- Patch:12817425 is not available for 11.2.0.1. WORKAROUND ---- None. To purge trace files manually you could execute: adrci> purge -age {minutes} -type TRACE

RANGE - Interval Partitioning In Oracle

Range Interval Partitioning In Oracle

Interval Partitioning has been introduced in oracle 11g.
With this method, we can automate the creation of range partition .
While creating the partitioned table, we just need to define one partition.
New partitions will be created automatically based on interval criteria when the data is inserted to the table. We don’t need to create the future partitions.

1. Lets create a monthly partitioned table ( with interval).

CREATE TABLE DBATEST.PART_TEST_TABLE ( CORRELATION_ID VARCHAR2(100 BYTE), ATTRIBUTE_KEY VARCHAR2(50 BYTE), ATTRIBUTE_VALUE VARCHAR2(100 BYTE), EVENT_TIMESTAMP TIMESTAMP(6) ) TABLESPACE STCDBA PARTITION BY RANGE (EVENT_TIMESTAMP) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) ( PARTITION PART_TEST_TABLE_MIN VALUES LESS THAN (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) );

CREATE INDEX DBATEST.PART_TEST_TABLE_COR_ID_IDX ON DBATEST.PART_TEST_TABLE (CORRELATION_ID) LOCAL; Index created.

Here the date 2016-05-01 is known as TRANSIT POINT . Any data inserted beyond this transit point will led to creation of a new partition automatically.

INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point



2. Now check dba_tab_partitions

SELECT table_name,partition_name,high_value from dba_tab_partitions where table_name='PART_TEST_TABLE'; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------------- ---------------------------------- --------------------------------------------------------------------- PART_TEST_TABLE PART_TEST_TABLE_MIN TIMESTAMP' 2016-05-01 00:00:00'


3. Lets insert some data and check the dba_tab_partitions:

insert into dbatest.PART_TEST_TABLE values ('387fade4-f0f4-4bc3-9ba3-41458fad7a4c','ApplicationName','ULA','01-JUN-2016') commit; SELECT table_name,partition_name,high_value,interval  from dba_tab_partitions where table_name='PART_TEST_TABLE'; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------------- ---------------------------------- --------------------------------------------------------------------- PART_TEST_TABLE PART_TEST_TABLE_MIN TIMESTAMP' 2016-05-01 00:00:00'


We can see a new monthly partition has been created automatically .

Similarly

for weekly partition use the parameter – INTERVAL (numtodsinterval(7,’day’))
for yearly partition use the parameter – INTERVAL (NUMTOYMINTERVAL(1,’YEAR’))



Convert Existing Range partitioned table to interval partition:
ALTER TABLE dbatest.range_part SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));


Restriction of Interval Partitioning:

1. Cannot be created for Index organized table(IOT)

2. The partitioning column can be only one and it must be of type NUMBER or DATE

3. Cannot use the MAXVALUE clause

Convert NON-Partitioned table into Partitioned table in oracle

Convert NON-Partitioned table into Partitioned table in oracle

This is one of the new feature of oracle 12.2 release .

Non-partitioned tables can be converted to partitioned table online without any downtime to the application , i.e no impact to the DML activities.

Till now for this activity, we were using dbms_redef methods. But in Oracle 12.2 release this has been simplified much.


See the below example:

1. Identify the non partitioned table.

desc BSSTDBA.PART_TEST_TABLE Name Type ---------------------------------------- -------- ----------------------------  ROW_ID                                    NOT NULL VARCHAR2(15 CHAR)  CREATED                                   NOT NULL DATE  CREATED_BY                                NOT NULL VARCHAR2(15 CHAR)  LAST_UPD                                  NOT NULL DATE  MODIFICATION_NUM                          NOT NULL NUMBER(10)  CONFLICT_ID                               NOT NULL VARCHAR2(15 CHAR)  ALW_PART_SHIP_FLG                         NOT NULL CHAR(1 CHAR)

SQL> col owner for a13 SQL> col table_name for a14 SQL>  set lines 299 SQL> select owner,table_name,partitioned from dba_tables where table_name='PART_TEST_TABLE'; OWNER         TABLE_NAME     PAR ------------- -------------- --- BSSTDBA       PART_TEST_TABLE      NO <br /> <br /> <br /> SQL> select count(*) from BSSTDBA.PART_TEST_TABLE;   COUNT(*) ----------     954598 SQL>create index BSSTDBA.PART_TEST_TABLE_IND1 on BSSTDBA.PART_TEST_TABLE(row_id); Index created.

SQL> create index BSSTDBA.PART_TEST_TABLE_IND2 on BSSTDBA.PART_TEST_TABLE(created); Index created.
 2. Alter table modify to partition the table.( partition key is column CREATED ) alter table BSSTDBA.PART_TEST_TABLE modify PARTITION BY RANGE (CREATED) (partition created_2105_p8 VALUES LESS THAN (TO_DATE('01/09/2015', 'DD/MM/YYYY')), partition created_2105_p9 VALUES LESS THAN (TO_DATE('01/10/2015', 'DD/MM/YYYY')), partition created_2105_p10 VALUES LESS THAN (TO_DATE('01/11/2015', 'DD/MM/YYYY')), partition created_2105_p11 VALUES LESS THAN (TO_DATE('01/12/2015', 'DD/MM/YYYY')), partition created_2105_p12 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')), PARTITION Created_MX VALUES LESS THAN (MAXVALUE)) ONLINE;

This activity will take some time depending upon amount of data table has.

While this alter statement is running, I have started running DML activities on the same table, To check whether it is impacting the DML activities.



SESSION 2:
  insert into BSSTDBA.PART_TEST_TABLE select * from BSSTDBA.PART_TEST_TABLE; Lets check for blocking session:        SID USERNAME             MODULE                         STATUS   EVENT                          BLOCKING_SESSION ---------- -------------------- ------------------------------ -------- ------------------------------ ----------------        490 SYS                  sqlplus@bttstdev64 (TNS V1-V3) ACTIVE   enq: TX - row lock contention                 7 SID      > 490  SQL_TEXT > alter table BSSTDBA.PART_TEST_TABLE modify PARTITION BY RANGE (CREATED) (partition created_2105_p8 VALUES LESS THAN (TO_DATE('01/09/2015', 'DD/MM/YYYY')), partition created_2105_p9 VALUES LESS THAN  (TO_DATE('01/10/2015', 'DD/MM/YYYY')), partition created_2105_p10 VALUES LESS THAN (TO_DATE('01/11/2015', 'DD/MM/YYYY')), partition created_2105_p11 VALUES LESS THAN (TO_DATE('01/12/2015', 'DD/MM/YYYY')), partition created_2105_p12 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')), partition created_2016_p1 VALUE LESS THAN (MAXVALUE)) ONLINE SID    >  7 SQL_TEXT> insert into BSSTDBA.PART_TEST_TABLE select * from BSSTDBA.PART_TEST_TABLE;

We can see that the insert statement(SID 7), is blocking the alter table command(SID 490), not the other way around. It means during this partition conversion activity, if any DML requests are coming, then it will allow them to complete their request. This may slow down the partition conversion time, But it won’t impact the application. Once ALTER TABLE MODIFY is completed. Check the whether table was partitioned properly or not.

SQL> select partition_name,high_value from dba_tab_partitions where table_name='PART_TEST_TABLE'; PARTITION_NAME          HIGH_VALUE ----------------------- -------------------------------------------------------------------------------- CREATED_2105_P10        TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_2105_P11        TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_2105_P12        TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_2105_P8         TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_2105_P9         TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_MX              MAXVALUE 20 rows selected.

But what happened to the INDEXES:
select  index_name,PARTITIONED from dba_indexes where table_name='PART_TEST_TABLE'; INDEX_NAME          PARTITIONED    ------------------- ------------ PART_TEST_TABLE_IND1      NO      PART_TEST_TABLE_IND2      YES

We can see PART_TEST_TABLE_IND1 was NON partitioned, But PART_TEST_TABLE_IND2 was partitioned.



Oracle document Says:

If no index clause is mentioned in the alter table statement, then

nonprefixed indexes(i.e index column is not a partitioned key) will be become global non-partitioned Index.
prefixed indexes(i.e index column is a partitioned key) will become local partitioned Index.


PART_TEST_TABLE_IND1 - ------------------- INDEX_SQL - >  create index BSSTDBA.PART_TEST_TABLE_IND1 on BSSTDBA.PART_TEST_TABLE(row_id);

It is an nonprefixed Index i.e index column is not a partitioned key. So it became global non partitioned Index


PART_TEST_TABLE_IND2  -
--------------------

create index BSSTDBA.PART_TEST_TABLE_IND2 on BSSTDBA.PART_TEST_TABLE(created);

It is an prefixed Index.  i.e index column in a partitione key .
So this indexes became local partitioned Index.


SQL> select index_name,PARTITION_NAME,HIGH_VALUE from dba_ind_partitions where index_name='PART_TEST_TABLE_IND2'; INDEX_NAME          PARTITION_NAME          HIGH_VALUE ------------------- ----------------------- -------------------------------------------------------------------------------- PART_TEST_TABLE_IND2      CREATED_2016_P9         TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P10        TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P11        TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P12        TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P8         TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P9         TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_MX              MAXVALUE 20 rows selected. There are lot of 12.2 New features like moving a table online, Spliting a partitioned table online etc. which we will discuss very soon.