How to Export/Import A Single Queue Using Data Pump

In a recent project, a customer used Advanced Queueing (AQ), and I had to move a single queue from one database to another.

In Oracle AI Database, you can use Data Pump to move queues. According to the documentation, exporting and importing a queue is straightforward, but there are some caveats to be aware of.

How to Export a Queue

I want to export the ORDER_Q queue in the APPUSER schema.

Under the hood, a queue consists of a queue table. Often, the queue uses an object type for its payload. I start by finding the queue table name and payload type:

select q.queue_table, qt.object_type
from   dba_queues q, dba_queue_tables qt 
where  q.owner='APPUSER' and q.name='ORDER_Q'
and    q.owner=qt.owner and q.queue_table =qt.queue_table;

   QUEUE_TABLE        OBJECT_TYPE
______________ __________________
ORDER_QT       APPUSER.ORDER_T

I prepare a Data Pump parfile named queue_table_export.par. It exports the queue table:
```
directory=dpdir
dumpfile=order_qt.dmp
logfile=order_q_export.log
tables=APPUSER.ORDER_QT
exclude=statistics
```
- The tables parameter is the queue table for the queue I want to export. I find it using the query above.

Then, I export the queue table:

expdp ... parfile=queue_table_export.par

Next, I create a new parfile named payload_type_export.par. It exports the object_type or payload_type:
```
directory=dpdir
dumpfile=queue_types.dmp
logfile=queue_types_export.log
schemas=appuser
include=type:"IN ('ORDER_T')"
```
- To export types, I must perform a schema export.
- I use the include parameter to include only types and further limit the export to the ORDER_T type.

Then, I export the type:

expdp ... parfile=payload_type_export.par

How to Import

I start by importing the type:
```
impdp ... dumpfile=queue_types.dmp
```
Then, I import the queue table:
```
impdp ... dumpfile=order_qt.dmp
```
- Data Pump imports the queue table and all messages.
- It also calls the AQ API to create the queue infrastructure.
I connect to the database and start the queue:
```
BEGIN
DBMS_AQADM.START_QUEUE(
	queue_name => 'APPUSER.ORDER_Q'
);
END;
/
```
- Note that I start the queue by its name ORDER_Q. It was created automatically during the import of the queue table.
That’s it. I’ve moved the queue and can start using it.

What Happens During Import

Let’s see how Data Pump uses the AQ API when it imports the queue table.

I start Data Pump to extract the DDL from the dump file:
```
impdp ... dumpfile=order_qt.dmp sqlfile=order_qt.sql
```
- Data Pump reads the dump file and extracts the statements needed to perform this import.
- It doesn’t perform the import.
- The statements are written to the text file specified by sqlfile.

I examine the text file. This is what Data Pump would do during an import.

Data Pump starts by creating the queue table:

CREATE TABLE "APPUSER"."ORDER_QT"
   (    "Q_NAME" VARCHAR2(128 BYTE),
        "MSGID" RAW(16),
        "CORRID" VARCHAR2(128 BYTE),
        "PRIORITY" NUMBER,
        ...

Next come a few indexes:

CREATE INDEX "APPUSER"."AQ$_ORDER_QT_I"
   ON "APPUSER"."ORDER_QT" ("Q_NAME", "STATE", "ENQ_TIME", "STEP_NO", "CHAIN_NO", "LOCAL_ORDER_NO")
   ...

Finally, the call to the AQ API to create the queue:

BEGIN
SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE_TABLE('ORDER_QT',1,16801800,2,0,0,'', SYS.DBMS_AQ_IMP_INTERNAL.DBVER_10i, '00:00');
COMMIT;
END;
/

BEGIN
SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE(HEXTORAW('598D4E9609226B60E0635301000A0A08'),'ORDER_QT','AQ$_ORDER_QT_E',1,0,0,0,0,'exception queue');
COMMIT;
END;
/

BEGIN
SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE(HEXTORAW('598D4E9609236B60E0635301000A0A08'),'ORDER_QT','ORDER_Q',0,5,0,0,0,'');
COMMIT;
END;
/

You can’t see the rows being loaded into the queue, but that happens too. All messages in the queue are also exported.

The DBMS_AQ_IMP_INTERNAL is an internal API. Don’t call it yourself.

What About a Full or Schema Export

If you perform a full or schema mode export, it’s simple. Data Pump exports all the required objects, including the types.

The only thing you must do is to start the queues after the import.

What If

The payload type must exist when you import the queue table. If you try to import the queue table without the type, Data Pump throws an error:

Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "APPUSER"."ORDER_QT" ("Q_NAME" VARCHAR2(128 BYTE), "MSGID" RAW(16), "CORRID" VARCHAR2(128 BYTE), "PRIORITY" NUMBER, "STATE" NUMBER, "DELAY" TIMESTAMP (6), "EXPIRATION" NUMBER, "TIME_MANAGER_INFO" TIMESTAMP (6), "LOCAL_ORDER_NO" NUMBER, "CHAIN_NO" NUMBER, "CSCN" NUMBER, "DSCN" NUMBER, "ENQ_TIME" TIMESTAMP (6), "ENQ_UID" VARCHAR2(128 BYTE), "ENQ_TID" VARCHAR2(30 BYTE), "DEQ_TIME" TIMESTAMP (6), "DEQ_UID" VARCHAR2(128 BYTE), "DEQ_TID" VARCHAR2(30 BYTE), "RETRY_COUNT" NUMBER, "EXCEPTION_QSCHEMA" VARCHAR2(128 BYTE), "EXCEPTION_QUEUE" VARCHAR2(128 BYTE), "STEP_NO" NUMBER, "RECIPIENT_KEY" NUMBER, "DEQUEUE_MSGID" RAW(16), "SENDER_NAME" VARCHAR2(128 BYTE), "SENDER_ADDRESS" VARCHAR2(1024 BYTE), "SENDER_PROTOCOL" NUMBER, "USER_DATA" "APPUSER"."ORDER_T" , "USER_PROP" "SYS"."ANYDATA" ) USAGE QUEUE SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"  OPAQUE TYPE ("USER_PROP") STORE AS SECUREFILE LOB (ENABLE STORAGE IN ROW CHUNK 8192 CACHE  NOCOMPRESS  KEEP_DUPLICATES  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

That’s It

You can use Data Pump to move queues around. For table-mode exports, remember to export the payload type separately.

A few interesting links:

Happy queueing!

Data Pump Creates Your Indexes Even Faster

In Oracle Database 23ai, Oracle has enhanced Data Pump to create indexes more efficiently. This can significantly reduce the time it takes to create indexes during a Data Pump import.

Oracle also backported the enhancement. You find the new features in:

23.8 with Data Pump Bundle Patch
19.26 with Data Pump Bundle Patch

In any case, the new feature is on by default. No configuration is needed; just enjoy faster imports.

Benchmark

I made a benchmark using a schema with:

100 small tables (125 MB)
50 medium tables (1,5 GB)
10 big tables (25 GB)
1 huge table (100 GB)
Each table had three indexes – 483 indexes in total

Using the new index method, the import went from almost 18 minutes to 11 minutes.

Here are extracts from the import log file:

# The old method
10-MAY-25 16:36:46.902: W-30 Completed 483 INDEX objects in 1071 seconds

# The new method
10-MAY-25 15:59:17.006: W-3 Completed 483 INDEX objects in 686 seconds

Details

So far, I haven’t seen a case where the new method is slower than the former method. However, should you want to revert to the old way of creating indexes, you can do that with the Data Pump parameter ONESTEP_INDEX=TRUE.

What Happens

To understand what happens, let’s go back in time to Oracle Database 11g. Imagine an import with PARALLEL=16. Data Pump would use one worker process to create indexes one at a time using CREATE INDEX ... PARALLEL 16. This is efficient for large indexes.

In Oracle Database 12c, the algorithm changed to better fit schemas with more indexes and especially many smaller indexes. Now, Data Pump would use all 16 workers, and each would create indexes using CREATE INDEX ... PARALLEL 1. However, this turned out to be a performance-killer for large indexes.

In Oracle Database 23ai (and 19c), you get the best of both worlds. Data Pump uses the size of the table to determine an optimal parallel degree. It creates smaller indexes in large batches with PARALLEL 1, and larger indexes using an optimal parallel degree up to PARALLEL 15.

Happy importing!

Faster Data Pump Import of LOBs Over Database Link

A colleague was helping a customer optimize an import using Data Pump via a database link that involved SecureFile LOBs.

Do you see a way to parallelize the direct import to improve performance and thus shorten the time it takes to import? Or is it not possible for LOB data?

Network mode imports are a flexible way of importing your data when you have limited access to the source system. However, it comes with the price of restrictions. One of them being:

Network mode import does not use parallel query (PQ) child processes.

In Data Pump, one worker will process a table data object which is either a:

Table
Table partition
Table subpartition

So, for a regular table, this means just one worker is processing the table and it doesn’t use parallel query. That’s bound to be slow for larger data sets, but can you do something?

Starting Point

To illustrate my point, I’ll use a sample data set consisting of:

One schema (BLOBLOAD)
With one table (TAB1)
Containing two columns
- Number (ID)
- BLOB (BLOB_DATA)
The table has around 16.000 rows
Size is 50 GB

Doing a regular Data Pump import over a database link is slow because there’s only one worker and no parallel query:

impdp ... \
   network_link=srclnk \
   schemas=blobload \
   parallel=4

...

21-OCT-24 05:30:36.813: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Oct 21 05:30:36 2024 elapsed 0 00:11:50

Almost 12 minutes!

Partitioning

Since we know that multiple workers can process different partitions of the same table, let’s try to partition the source table. I’ll use hash partitioning and ensure my partitions are equally distributed:

alter table tab1 
modify partition by hash (id) 
partitions 32 online;

Repeat the import:

impdp ... \
   network_link=srclnk \
   schemas=blobload \
   parallel=4

...

21-OCT-24 09:08:00.897: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Oct 21 09:08:00 2024 elapsed 0 00:04:26

Just 4m 26s – that’s a huge improvement!

In the log, file you’ll see that multiple workers are processing partitions individually. So, even without parallel query, I get parallelism because of multiple workers on the same table – each on different partitions.

But partitioning is a separately licensed option.

Using QUERY Parameter and Multiple Data Pump Imports

I’ve previously blocked about do-it-yourself parallelism for Data Pump exports of BasicFile LOBs. Can I use the same approach here?

The idea is to start multiple Data Pump jobs importing the same table, but each working on a subset of the data.

First, import just the metadata

impdp ... \
   network_link=srclnk \
   schemas=blobload \
   content=metadata_only

Next, start 4 concurrent imports importing just the rows. Each import works on a subset of the data using thery query parameter:

impdp ... \
   network_link=srclnk \
   schemas=blobload \
   content=data_only \
   query="where mod(id, 4)=0"

impdp ... \
   network_link=srclnk \
   schemas=blobload \
   content=data_only \
   query="where mod(id, 4)=1"

impdp ... \
   network_link=srclnk \
   schemas=blobload \
   content=data_only \
   query="where mod(id, 4)=2"

impdp ... \
   network_link=srclnk \
   schemas=blobload \
   content=data_only \
   query="where mod(id, 4)=3"

No – that’s not possible. During imports, Data Pump acquires a lock on the table being imported using the APPEND hint. This is from a trace of the imports:

INSERT /*+  APPEND  NESTED_TABLE_SET_REFS   PARALLEL(KUT$,1)   */ INTO "BLOBLOAD"."TAB1"  KUT$ ("ID", "BLOB_DATA")
SELECT /*+ NESTED_TABLE_GET_REFS  PARALLEL(KU$,1)  */ "ID", "BLOB_DATA" FROM "BLOBLOAD"."TAB1"@srclnk KU$ WHERE mod(id, 4)=1

If you try to start multiple imports into the same table, you get an error:

ORA-02049: timeout: distributed transaction waiting for lock

So, let’s prevent that by adding data_options=disable_append_hint to each Data Pump import jobs.

Now, multiple Data Pump jobs may work on the same table, but it doesn’t scale lineary.

One concurrent job: Around 12 minutes
Four concurrent jobs: Around 8 minutes
Eight concurrent jobs: Around 7 minutes

It gives a performance benefit, but probably not as much as you’d like.

Two-Step Import

If I can’t import into the same table, how about starting four simultaneous Data Pump jobs using the do-it-yourself approach above, but importing into separate tables and then combining all the tables afterward?

I’ll start by loading 1/4 of the rows (notice the QUERY parameter):

impdp ... \
   network_link=srclnk \
   schemas=blobload \
   query=\(blobload.tab1:\"WHERE mod\(id, 4\)=0\"\)

While that runs, I’ll start three separate Data Pump jobs that each work on a different 1/4 of the data. I’m remapping the table into a new table to avoid the locking issue:

impdp ... \
   network_link=srclnk \
   schemas=blobload \
   include=table \
   remap_table=tab1:tab1_2 \
   query=\(blobload.tab1:\"WHERE mod\(id, 4\)=1\"\)

In the remaining two jobs, I’ll slightly modify the QUERY and REMAP_TABLE parameters:

impdp ... \
   network_link=srclnk \
   schemas=blobload \
   include=table \
   remap_table=tab1:tab1_3 \
   query=\(blobload.tab1:\"WHERE mod\(id, 4\)=2\"\)

impdp ... \
   network_link=srclnk \
   schemas=blobload \
   include=table \
   remap_table=tab1:tab1_4 \
   query=\(blobload.tab1:\"WHERE mod\(id, 4\)=3\"\)

Now, I can load the rows from the three staging tables into the real one:

ALTER SESSION FORCE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(a) */ INTO "BLOBLOAD"."TAB1" a 
SELECT /*+ PARALLEL(b)  */ * FROM "BLOBLOAD"."TAB1_2" b;
commit;

INSERT /*+ APPEND PARALLEL(a) */ INTO "BLOBLOAD"."TAB1" a 
SELECT /*+ PARALLEL(b)  */ * FROM "BLOBLOAD"."TAB1_3" b;
commit;

INSERT /*+ APPEND PARALLEL(a) */ INTO "BLOBLOAD"."TAB1"  a
SELECT /*+ PARALLEL(b)  */ * FROM "BLOBLOAD"."TAB1_4" b;
commit;

This approach took around 7 minutes (3m 30s for the Data Pump jobs, and 3m 30s to load the rows into the real table). Slower than partitioning but still faster than the starting point.

This approach is complicated; the more data you have, the more you need to consider things like transaction size and index maintenance.

Conclusion

Network mode imports have many restrictions, which also affect performance. Partitioning is the easiest and fastest improvement, but it requires the appropriate license option. The final resort is to perform some complicated data juggling.

Alternatively, abandon network mode imports and use dump files. In dump file mode, one worker can use parallel query during export and import, which is also fast.

Thanks

I used an example from oracle-base.com to generate the test data.