Category: Data Pump

How to Fix ORA-14519 Conflicting Tablespace Blocksizes During Oracle Data Pump Import

Recently, our team has received multiple questions about an error occurring during an Oracle Data Pump import:

ORA-14519: Conflicting tablespace blocksizes for table : Tablespace <string> block size <number< [partition specification] conflicts with previously specified/implied tablespace <string> block size <number< [object-level default]

Data Pump can’t import a partitioned table if you don’t store the table itself and all partitions in tablespaces with the same block size.

What is the Problem?

In almost all cases, you must store a partitioned table and all its partitions in tablespaces with the same block size. The documentation states:

Use caution when creating partitioned objects in a database with tablespaces of different block sizes. The storage of partitioned objects in such tablespaces is subject to some restrictions. Specifically, all partitions of the following entities must reside in tablespaces of the same block size:

  • Conventional tables
  • Indexes
  • …

You find the same restriction in Common Questions and Errors on Partition Operations Involving Tablespaces of Different Block Sizes (Doc ID 1401064.1):

Q: Can I store different partitions of the same table in tablespaces of different block sizes? A: No. For each conventional table (except index-organized tables), all partitions of that table must be stored in tablespaces with the same block size.

In the cases we looked at, Data Pump import recreates the partitioned table as it was defined in the source database. But the definition of the partitioned table is invalid – it spans tablespaces of different block size. The target database rejects the CREATE TABLE statement.

Solution

Fix in Source Database

In My Oracle Support note IMPORT OF PARTITIONED TABLE IN NON DEFAULT BLOCKSIZE TABLESPACE FAILS WITH ORA-14519(Doc ID 272229.1), the specified solution is to fix the invalid table in the source database. Often, users don’t want to change the source database. Such a change might have to go through a change management process, or the user might not be willing to take the risk of changing things in the source database.

The solution works, but not ideal.

Fix in Target Database

My colleague, Klaus Gronau, came up with a better solution. Fix it during import in the target database. The example below uses a schema-based Data Pump job.

  1. Extract the defintion of the offending table and schema:
    impdp ... include="table:""in('MYPARTTAB1')""" \
          include="user:""in('APPUSER')""" \
          include=tablespace_quota \			 
          sqlfile=myparttab1.sql
    • The SQL file also contains objects depending on the table like constraints and indexes.
  2. Edit the SQL file:
    vi myparttab1.sql
    • Ensure that the tablespace of the table itself matches the tablespaces used by the partitions. Check the tablespace clauses.
    • Optionally, you can move the constraint and index creation out of the file. After the next Data Pump import, you can add the constraints and indexes. This might speed up the process. You might even change the parallel degree on the index creation to create it faster.
  3. Create the user, quotas, partitioned table and dependent objects using the SQL file.
    @myparttab1.sql
  4. Perform the Data Pump import:
    impdp ... table_exists_action=truncate \
          data_options=trust_existing_table_partitions
    • The table_exists_action tells Data Pump to truncate any existing tables. The partitioned table is already empty since we just created it.
    • The data_options parameter instructs Data Pump to trust that the definition of the partitioned table matches that stored in the dump file. Data Pump does not perform any validation of the table, which is fine because we just created the table using the definition from the dump file.

Word of advice:

  • This method uses the exclude and include clauses to filter out the offending table. If the dump file contains a table of the same name, but in a different schema, then the filter applies to both those tables. The filter does not take the schema into account. It will match all tables, in any schema, with the name myparttab1.

Transportable Tablespace

If you move data with transportable tablespaces, there is no way to change the table definition on import. You must fix the problem in the source database.

Appendix

Thanks

A big shoutout to my colleague, Klaus Gronau, who provided the test case and solution for this issue.

How Can It Happen?

  • Observe the following test case:

    alter system set db_32k_cache_size=30M scope=both;
create tablespace users_8 datafile size 50m blocksize 8k;
create tablespace users_32 datafile size 50m blocksize 32k;

create user appuser identified by "appuser" default tablespace users_8;
grant connect, create table to appuser;
alter user appuser quota unlimited on users_8;
alter user appuser quota unlimited on users_32;

connect appuser/appuser

  • The following create table statement fails because of the different tablespace block sizes:

    create table myparttab1 (
   col1 number
) tablespace users_8 
partition by range (col1) (
   partition myparttab1_1 values less than (100001) tablespace users_32 ,
   partition myparttab1_2 values less than (maxvalue) tablespace users_32
);

ERROR at line 5:
ORA-14519: Conflicting tablespace blocksizes for table : Tablespace USERS_32
block size 32768 [partition specification] conflicts with previously
specified/implied tablespace USERS_8 block size 8192 [object-level default]

  • But if you remove the table tablespace specification, it works. See how I removed tablespace users_8. However, the user default tablespace is users_8. The table segment of the partitioned table inherits the user default. The table definition is now unsupported:

    create table myparttab1 (
   col1 number
) 
partition by range (col1) (
   partition myparttab1_1 values less than (100001) tablespace users_32 ,
   partition myparttab1_2 values less than (maxvalue) tablespace users_32
);

Table created.

  • You can check the table and partition defintion in the data dictionary:

    select 'TABLE' as type, 
       table_name as segment_name, 
       nvl(tablespace_name, (select default_tablespace from user_users))  as tablespace 
from user_tables 
where table_name='MYPARTTAB1'
union all
select 'PARTITION', 
       partition_name, 
       tablespace_name 
from user_tab_partitions 
where table_name='MYPARTTAB1';

TYPE	  SEGMENT_NAME	  TABLESPACE
--------- --------------- ---------------
TABLE	  MYPARTTAB1	  USERS_8
PARTITION MYPARTTAB1_1	  USERS_32
PARTITION MYPARTTAB1_2	  USERS_32

  • We filed a bug to tighten the syntax validation in the create table statement. For now, store the table segment in the same tablespace as the partitions using the tablespace clause on the create table statement.

How to Trace Oracle Data Pump

Please read the updated post. This is an old post.

Data Pump is hanging!

If you ever think the above, the answer is probably:

No, it’s not, it’s working…

It might be working in a suboptimal way, but nevertheless, it’s working. Here’s how you can tell what’s going on.

My Ultimate Tracing Guide

I use this approach every time I work with Oracle Data Pump and need to see what happens underneath the hood.

Before Data Pump

  • I always ensure dictionary and fixed objects statistics are current before starting Data Pump:

    begin
   dbms_stats.gather_schema_stats('SYS');
   dbms_stats.gather_schema_stats('SYSTEM');
   dbms_stats.gather_fixed_objects_stats;
end;
/
    • I usually don’t use gather_dictionary_stats. I prefer gathering schema stats instead. I’ve seen a few edge cases with gather_dictionary_stats over the years, but mostly it works fine. But you know, old dog and new tricks…
    • You should always follow this advice even if you are not tracing a specific problem.

  • I change the AWR snapshot interval to 15 minutes to get better granularity and manually create an AWR snapshot:

    begin
   dbms_workload_repository.modify_snapshot_settings(null, 15);
   dbms_workload_repository.create_snapshot;
end;
/

  • Optionally, if I need to diagnose a performance problem, I enable SQL trace for Data Pump processes:

    alter system set events 'sql_trace {process: pname = dw | process: pname = dm} level=8';
    • Or a specific SQL ID:
    alter system set events 'sql_trace[SQL: <sql_id> ]';

Starting Data Pump

  • I add diagnostic information (metrics and logtime) to the log file and turn on tracing:
    expdp ... metrics=yes logtime=all trace=1FF0300
impdp ... metrics=yes logtime=all trace=1FF0300
    • 1FF0300 turns on tracing for more or less everything in Data Pump, but there are other trace levels.

After Data Pump

  • I create a new AWR snapshot:
    begin
   dbms_workload_repository.create_snapshot;
end;
/
    • You might also want to reset the AWR snap interval to the previous setting.
  • Plus, an AWR report spanning the entire period.
    @?/rdbms/admin/awrrpt
  • Data Pump writes the tracing into the process trace files. You can find them in the database trace directory:
    • Control process trace files named *dm*.trc.
    • Worker process trace files named *dw*.trc.

Happy Debugging

Normally, the above information is enough to figure out the underlying problem. If you supply the above to Oracle Support when you open the next case, they’ll be super excited.

Further Reading

Data Pump and Parallel Transportable Jobs

In migrations, you often use transportable tablespaces or Full Transportable Export/Import (FTEX). Downtime is always of concern when you migrate, so having Data Pump perform the transportable job in parallel is a huge benefit.

How much benefit? Let’s find out with a little benchmark.

The Results

Let’s start with the interesting part. How much time can you save using parallel transportable jobs in Data Pump.

The following table lists four different scenarios. Each scenario consists of an export and an import. The total is the time Data Pump needs to finish the migration – the sum of export and import. In a real migration, many other things are in play, but here, we are looking solely at Data Pump performance.

Export Time Import Time Total
19c, no parallel 2h 2m 19c, no parallel 6h 44m 8h 46m
23ai, parallel 4 1h 48m 23ai, parallel 4 2h 33m 4h 21m
19c, no parallel 2h 2m 23ai, parallel 16 1h 23m 3h 25m
23ai, parallel 16 1h 8m 23ai, parallel 16 1h 23m 2h 31m

The first row is what you can do in Oracle Database 19c, almost 9 hours. Compare that to the last row you can do with parallel 16 in Oracle Database 23ai, almost a 3.5x reduction.

If you migrate from Oracle Database 19c to Oracle Database 23ai (3rd row), you can still benefit from parallel import and gain a significant benefit.

The Benchmark

I used the following Oracle homes:

My test database:

  • E-Business Suite database
  • 300 GB physical size
  • 630.000 database objects, including
    • 89.000 tables
    • 66.000 indexes
    • 60.000 packages
  • CPU_COUNT = 16
  • SGA_TARGET = 64G

My test machine:

  • OCI shape VM.Standard.E4.Flex
  • 8 CPUs
  • 128 GB mem
  • Fast disks (max. IOPS 128.000)

How to

It’s very easy to enable parallel transportable jobs. If you want to use 16 parallel workers, on export:

expdp ... parallel=16 dumpfile=ftex%L.dmp

On import:

impdp ... parallel=16

What Happens

A Data Pump job consists of several object paths that Data Pump must process. An object path could be tables, indexes, or package bodies.

Parallel Export

Each worker takes an object path and starts to process it. The worker works alone on this object path. You get parallelism by multiple workers processing multiple object paths simultaneously.

Parallel Import

During import, Data Pump must process each object path in a certain order. Data Pump can only import constraints once it has imported tables, for example.

Data Pump processes each object path in the designated order, then splits the work in one object path to many workers. You get parallelism by multiple workers processing one object path in parallel.

The Fine Print

  • Parallel transportable jobs work in Oracle Database 21c and later. In Oracle Database 19c, a transportable job has no parallel capabilities.

  • Data Pump can use parallel only on transportable jobs via a dump file. Network mode is not an option for parallel transportable jobs.

  • If you export in Oracle Database 19c (which does not support parallel transportable jobs), you can still perform a parallel import into Oracle Database 23ai.

  • The export parallel degree and import parallel degree do not have to match. You can export with parallel degree 1 and import with parallel degree 16.

  • When you enable parallel jobs, Data Pump starts more workers. How much extra resources do they use?

    • I didn’t notice any significant difference in undo or temp tablespace use.
    • I didn’t notice any extra pressure on the streams pool, either. I had the streams pool set to 256M, and the database didn’t perform any SGA resize operation during my benchmark.

Conclusion

For migrations, parallel transportable jobs in Data Pump are a huge benefit. Every minute of downtime often counts, and this has a massive impact.

How to Use Oracle Data Pump When You Have Common Objects

One of the benefits of the multitenant architecture in Oracle Database is the concept of common objects. If you want to move data around with Oracle Data Pump, is there anything you must know?

What Are Common Objects

The documentation describes common objects as:

A common phenomenon defined in a root is the same in all containers plugged in to this root. … For example, if you create a common user account while connected to CDB$ROOT, then this user account is common to all PDBs and application roots in the CDB.

A simpler explanation:

The stuff you create as C##.... in the root container (CDB$ROOT).

The principles of commonality are:

  • A common phenomenon is the same in every existing and future container. …
  • Only a common user can alter the existence of common phenomena. More precisely, only a common user logged in to either the CDB root or an application root can create, destroy, or modify attributes of a user, role, or object that is common to the current container.

For illustration purposes, imagine a common profile used by a local user. You would create such as:

alter session set container=CDB$ROOT;
create profile c##commonprofile1 ... ;
alter session set container=pdb1;
create user localuser1 ... profile c##commonprofile1;

With other object types, you can make it even more complex, but the principles remain the same.

What Happens In Oracle Data Pump

On Export

Data Pump

  • Connects to the PDB.
  • Extracts the relevant data and metadata.

Using the above example, Data Pump extracts the user DDL. The user DDL specifies the use of a common profile.

On Import

Data Pump

  • Connects to the PDB.
  • Assumes that you have already created the common objects.
  • Creates the local user localuser1 and specifies the use of the common profile c##commonprofile1.

If the common profile does not exist already, the creation of the user fails:

Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-39083: Object type USER:"LOCALUSER1" failed to create with error:
ORA-02380: profile C##COMMONPROFILE1 does not exist

Failing sql is:
 CREATE USER "LOCAL1" ... PROFILE "C##COMMONPROFILE1"

Why Don’t Data Pump Create The Common Objects?

  • First, Data Pump connects to the PDB itself to perform the import. Since you must create common objects in the root container, that’s obviously a problem. The importing user might not have access to the root container, so switching containers and creating common objects is impossible.

  • Also, it would break the contract in the multitenant architecture that each PDB is isolated. If one PDB were allowed to create common objects, those objects would also be available in other PDBs, and the isolation would no longer exist.

  • Finally, it would open for all sorts of security issues if a user connected to a PDB could create common objects.

What Can You Do?

A CDB Blueprint

Ideally, you should have some CDB blueprint; A definition of the common objects used in your databases. When you create a new CDB, you have an afterburner that creates those common objects according to your company guidelines.

Examine the Dump File

You can also examine the DDLs in the Data Pump file and create a list of common objects.

  1. Extract the DDL:
    impdp ... sqlfile=import_ddls.sql
  2. Search for C## and build a list of common objects needed:
    grep -n -i "C##" import_ddls.sql
    This is a crude search, and I imagine awk aficionados can improve it.
  3. Extract the DDL for the common objects from the source CDB:
    l_stmt := dbms_metadata.get_ddl(...);
dbms_output.put_line(l_stmt);
l_stmt := dbms_metadata.get_granted_ddl(...);
dbms_output.put_line(l_stmt);
    Use dbms_metadata.get_granted_ddl to extract object and role grants and system privileges.
  4. Create the common objects in the target CDB before import:
    alter session set container=cdb$root;
create profile c##commonprofile1 ... ;

Data Pump and Faster Export of SecureFile LOBs

SecureFile LOBs are the best LOB type, partly because it allows parallel access to and from the LOB. The parallel access allows Data Pump to unload and load data faster.

The following applies to SecureFile LOBs:

select owner, table_name, column_name 
from   dba_lobs 
where  securefile='YES';

There is another blog post for Data Pump and BasicFile LOBs.

How Data Pump Works

First of all, you must allow Data Pump to work in parallel. Only when so, Data Pump assigns one worker – and only one worker – per table data object. That is a table, partition or subpartition.

Suppose the table data object is big enough, that one worker uses parallel query (PQ) to unload the data. The PQ worker will hand over the data to the worker, who will write it to the dump file.

How much is big enough? By default, Data Pump examines the object statistics, and if the segment is larger than 250 MB, it will use parallel query. You can change this threshold using the parallel_threshold parameter.

To export a SecureFile LOB in parallel:

  • You must allow Data Pump to do parallel work using the parallel parameter.
  • The table data object must be of a certain size.

Data Pump determines the size by using either:

  • Optimizer statistics (default) – estimate=statistics
  • Calculation – estimate=blocks

Why doesn’t Data Pump use parallel query on all objects? Because it takes time to start a PQ worker and keep track of them. It is a waste on small table data objects and will often be slower. Parallel query makes sense only when a table data object reaches a certain size.

How the Database Stores LOBs

With LOBs you can potentially store a lot of data.

If a LOB (in a single row) is less than 4000 bytes, it is stored in-row together with the rest of the data. Having small LOBs as part of the table is efficient. However, if a LOB is larger than 4000 bytes, it is inefficient to store it in-row. Instead, the database stores the entire LOB in a separate LOB segment. This is called out-of-row.

In Oracle Database 23ai, you can optionally store up to 8000 bytes in-row.

When Statistics Are Not Enough

Update, 6 May 2024: The below mentioned issue is solved in the 19.23.0 Data Pump Bundle Patch

If a LOB is stored in-row, the space it uses, is added to the total segment space of the table. However, if the LOB is stored out-of-row, the LOB space is added to the LOB segment space usage.

The table statistics (dba_tab_statistics) show only the blocks (i.e., space) used by the table. The size of the LOB segment does not affect the size of the table in the optimizer statistics. This is by design, because the purpose of statistics is to enable the optimizer to come up with good plans, and the size of the LOB segment is irrelevant in that situation.

In other words, you can have a table with 100 rows and 1 TB of LOB data. If all that LOB data is stored out-of-row, the table looks really small.

If Data Pump determines the size of the table based on the statistics, that table with 1 TB LOBs looks really small. As a result, Data Pump will not use parallel query on it.

This applies also to partitioned tables. In this case, Data Pump looks at the partition statistics instead of the table statistics.

Faster Exports

Bundle Patch

The patch to fix the above issue is included in the 19.23.0 Data Pump Bundle Patch.

Don’t Estimate By Statistics

You can change the way Data Pump determines the size of a table from statistics to a proper calculation:

expdp ... estimate=blocks
  • Estimating by blocks is slower than using statistics. You will see that the startup phase in Data Pump takes longer.
  • Due to a bug in Data Pump, this will not work unless your database is on 19.18.0 with the Data Pump bundle patch.

Fake Statistics

Since Data Pump only looks at the table data object statistics, you can fake the statistics. Trick Data Pump into believing the table itself is huge by setting statistics to high values:

begin
 dbms_stats.set_table_stats (
   ownname => 'APPUSER', 
   tabname => 'T1',
   numrows => 10000000, 
   numblks => 1000000);
 end;
 /
  • You must do this for all tables with big LOBs.
  • It requires testing to get the best result.
  • The fake statistics will influence the choices made by the optimizer, so only do this in your maintenance window. Also, note that setting statistics invalidates cursors in the library cache.
  • Ensure that the statistics gathering job doesn’t overwrite your fake stats.

Use Partitioning

If you partition a table, Data Pump uses one worker per partition or subpartition. Data Pump might also use parallel query on a single partition/subpartition depending on the number of partitions and subpartitions and available parallel processes. But only if the partition statistics reflect that it is significant in size. Otherwise, you run into the same problem as described above.

An advantage of having multiple Data Pump workers on the same partitioned table, is that more workers can write to dump files simultaneously. Not only can Data Pump read faster from the database, it can also store it faster into dump files.

Final Words

Our developers solved the issue in the 19.23.0 Data Pump Bundle Patch. The best solution is of course to stay current and apply the Data Pump Bundle Patch.

Finally, as a reminder, always convert your LOBs to SecureFile LOBs on import:

impdp ... transform=lob_storage:securefiles

Applying Parallelism to Data Pump Exports and Imports

When you use Oracle Data Pump, you often want it to happen fast. Applying parallelism is the obvious choice. Here are a few things you might not be aware of.

Parallel and Export

During export, when a worker needs to write to a file, it needs an exclusive lock on a dump file. If your dump file specification does not allow creating multiple files, then the workers will queue up waiting for a lock on that single dump file.

You should always allow Data Pump to create as many dump files as it wants:

expdp ... dumpfile=my_exp_%L.dmp

The wildcard operator, %L, allows the creation of many files by adding a number. Previously, you would use %U, but %L allows more files. Check the documentation for other substitution variables.

Import From a Single Dump File

Even if you made the export with no parallel or just have one dump file, you can still import in parallel.

During an import, Data Pump only needs to read from the dump file, and multiple workers can read from the same file without problems. The only issue would be how much I/O a single file can handle for a large database.

Does import parallelism has to match export parallelism, and the answer is no. The two are independent. This would work fine:

expdp ... parallel=4
impdp ... parallel=64

Import From a Previous Release

In Oracle Database 12.2, Data Pump starts to support parallel metadata import during a regular import. If you import from a dump file from a previous version (that does not support it), you can still import metadata in parallel in Oracle Database 12.2 and newer. There are no specific requirements for the dump file to perform parallel operations.

The same applies to transportable tablespace jobs, which you can do in parallel in Oracle Database 21c.

Want to Know More?

How to Export Database Scheduler Jobs Using Data Pump

Which options do you have to move scheduler jobs (DBMS_SCHEDULER) to a different Oracle Database?

Data Pump

In Data Pump, scheduler jobs are referred to as PROCOBJ. You can get just the scheduler jobs using:

expdp ... include=procobj
impdp ... include=procobj

If you need just a subset of the scheduler jobs (works for impdp as well):

expdp ... include=procobj:" IN ('JOB1', 'JOB2')"

Data Pump and SQLFILE

If you have a Data Pump dump file, you can extract the DDL of the scheduler jobs using the sqlfile option:

impdp ... include=procobj \
   sqlfile=my_scheduler_jobs.sql

The DDL is written to a text file specified by sqlfile. This allows you to review the job defintions and, optionally, change them. Then, you execute the commands.

DBMS_METADATA

If you need to export just a few jobs, it might be easier to simply get the defintions using the metadata API:

select
   dbms_metadata.get_ddl('PROCOBJ','<name>','<owner>')
from 
   dual;

SYS Jobs

In older releases of Oracle Database, you could not get the definition of a job owned by SYS. A simple workaround is to copy the job into another schema and then generate the DDL:

exec dbms_scheduler.copy_job('SYS.MY_JOB','APPUSER.MY_JOB');
select
   dbms_metadata.get_ddl('PROCOBJ','MY_JOB','APPUSER')
from 
   dual;

Appendix

Credits

Thanks to my colleague, Klaus Gronau. He has done more migrations than I’ll ever do and he maintains a remarkable set of notes. From his notes, he dug out many good pointers.

Data Pump and Faster Export of BasicFile LOBs

Exporting BasicFile LOBs can be quite a pain. If your maintenance window is too short, here is a way to make the export faster.

The following applies to BasicFile LOBs:

select owner, table_name, column_name 
from   dba_lobs 
where  securefile='NO';

Background

The old BasicFile LOBs do not offer any parallel access methods. The new, improved SecureFile LOBs offer superior functionality, including full parallel access to the LOB. This is a good reason to migrate any old BasicFile LOBs in your database.

When Data Pump starts to export a table with a BasicFile LOB, only one worker will be assigned (due to the limited parallel access methods). If you have a large table, it will take a lot of time for that one worker to process the table.

Solution

Instead of one Data Pump job with one worker processing the table with the LOB, the idea is to start multiple Data Pump exports. Each export has one worker working on a dedicated part of the table.

Imagine you have a table with 100 rows. You start four concurrent Data Pump sessions:

Job Rows
Data Pump job 1 1-25
Data Pump job 2 26-50
Data Pump job 3 51-75
Data Pump job 4 76-100

To do this, you need to use the Data Pump query parameter.

Generating the Predicates

Now, you need a way to split the table into chunks and generate a predicate for each of the concurrent Data Pump sessions. It is important that:

  • All rows are exported.
  • No rows are exported more than once.

Further, you should ensure that:

  • The rows are spread evenly across the Data Pump jobs.

ROWIDs

An elegant solution is to use modulus and the block number that holds the row. You can get the block number using rowid and dbms_rowid:

Job Predicate
Data Pump job 1 where mod(dbms_rowid.rowid_block_number(rowid), 4) = 0
Data Pump job 2 where mod(dbms_rowid.rowid_block_number(rowid), 4) = 1
Data Pump job 3 where mod(dbms_rowid.rowid_block_number(rowid), 4) = 2
Data Pump job 4 where mod(dbms_rowid.rowid_block_number(rowid), 4) = 3

This might not be the fastest option, but it is a generic one that works without any knowledge of the table structure you are exporting.

Keys

You can also split the table on a primary/unique key or any other column that offers an even distribution of values:

Job Predicate
Data Pump job 1 where mod(--primary_key_column--, 4) = 0
Data Pump job 2 where mod(--primary_key_column--, 4) = 1
Data Pump job 3 where mod(--primary_key_column--, 4) = 2
Data Pump job 4 where mod(--primary_key_column--, 4) = 3

More Workers

If you need more sessions working on the table, you can change the modulus operation and add more Data Pump jobs.

How to Export With ROWID

Here is the first of the Data Pump parameter files:

logfile=exp_lob_0.log
dumpfile=exp_lob_0.dmp
tables=appuser.table_with_lob
query="where mod(dbms_rowid.rowid_block_number(rowid), 4) = 0"

The second one. Notice how the serial number changes in the logfile and dumpfile parameters. Also, the modulus operation changes:

logfile=exp_lob_1.log
dumpfile=exp_lob_1.dmp
tables=appuser.table_with_lob
query="where mod(dbms_rowid.rowid_block_number(rowid), 4) = 1"

The third one:

logfile=exp_lob_2.log
dumpfile=exp_lob_2.dmp
tables=appuser.table_with_lob
query="where mod(dbms_rowid.rowid_block_number(rowid), 4) = 2"

The fourth one:

logfile=exp_lob_3.log
dumpfile=exp_lob_3.dmp
tables=appuser.table_with_lob
query="where mod(dbms_rowid.rowid_block_number(rowid), 4) = 3"

Now, you can start four concurrent Data Pump jobs. You must execute the commands in different terminals:

expdp ... parfile=exp_lob_0.par
expdp ... parfile=exp_lob_1.par
expdp ... parfile=exp_lob_2.par
expdp ... parfile=exp_lob_3.par

How to Import

When importing, you change the LOB to SecureFile LOB, which allows parallel access. Then, take one Data Pump job at a time.

  • The first job does a regular schema import of the first dump file:

    impdp ... \
   dumpfile=exp_lob_0.dmp \
   logfile=imp_lob_0.log
   transform=lob_storage:securefile \
   parallel=4
    • It also creates the table itself.
    • You should always convert the LOB to SecureFile LOB during import. Conversion happens on-the-fly and allows for parallel import of data. Even if the LOB originally was a BasicFile LOB.

  • Now, proceed with the other dump files in serial. Since you already converted the LOB to a SecureFile (previous step), you can now perform a Data Pump import using native parallelism:

    impdp ... \
   dumpfile=exp_lob_1.dmp \
   logfile=imp_lob_1.log
   parallel=4 \
   table_exists_action=append
impdp ... \
   dumpfile=exp_lob_2.dmp \
   logfile=imp_lob_2.log
   parallel=4 \
   table_exists_action=append
impdp ... \
   dumpfile=exp_lob_3.dmp \
   logfile=imp_lob_3.log
   parallel=4 \	  
   table_exists_action=append
    • Notice how the dumpfile and logfile changes for each job.
    • Run the jobs in serial. Each job will use Data Pump parallelism.

That’s it!

Few Words of Caution

  • You must figure out how you create indexes – if they are present on the table. Ideally, you want to postpone index creation until the last job has loaded its rows. Otherwise, you’ll have expensive index maintenance happening when you load data.

  • Data Pump uses Advanced Queueing, which relies on the streams pool in the SGA. Be sure that streams_pool_size is set high enough when you start multiple data pump sessions. In most situations, setting it to 2G should be more than enough:

    alter system set streams_pool_size=2G scope=memory;

  • Do this in a maintenance window only with no users connected.

Things to Consider When Importing Advanced Queues using Oracle Data Pump

Oracle Data Pump supports moving Advanced Queues (AQ) using export/import and full transportable export/import. But there are some things to be aware of.

Data Pump Does Not Start Queues

Data Pump creates the queues during import but does not start the queues. At the end of the import, you must manually start the queues:

exec dbms_aqadm.start_queue(queue_name => ... );

Be sure to start all queue:

select owner, name, queue_table, enqueue_enabled, dequeue_enabled 
from   dba_queues;

This behavior is intentional. During a migration, you typically don’t want to use the queues. Depending on your use case, it can have unintended side effects. After the migration, when you confirm a successful migration and are ready to go live, you can manually start the queues.

If you forget to start the queues, your application will start to receive the following error:

ORA-25207: enqueue failed, queue string.string is disabled from enqueueing
ORA-25226: dequeue failed, queue string.string is not enabled for dequeue

The Database Does Not Create All Queue Objects

Data Pump creates the queues using the Advanced Queuing administration API DBMS_AQADM:

exec dbms_aqadm.create_queue_table( ...

The database then creates the essential parts of the underlying queue infrastructure: tables, views, IOTs, etc. This should include:

  • <queue_table_name>
  • AQ$_<queue_table_name>_E
  • AQ$_<queue_table_name>_I
  • AQ$_<queue_table_name>_T
  • AQ$_<queue_table_name>_F

In the source database, depending on your configuration and use of Advanced Queueing, you might also see the following objects:

  • AQ$_<queue_table_name>_C
  • AQ$_<queue_table_name>_D
  • AQ$_<queue_table_name>_G
  • AQ$_<queue_table_name>_H
  • AQ$_<queue_table_name>_L
  • AQ$_<queue_table_name>_P
  • AQ$_<queue_table_name>_S
  • AQ$_<queue_table_name>_V

This is intentional. The database creates those objects when needed. You should never create them or otherwise touch the underlying queue objects.

Typically, as part of a migration, you compare the count of objects in the source and target database to ensure nothing is lost. This comparison must consider the above.

For queue tables, it is sufficient to compare using the following query:

SQL> select count(*) 
     from dba_objects
     where owner='<schema>' 
           and object_type = 'QUEUE';

Definition Of Queue Tables Might Change

As stated, you should not worry about the underlying queue objects. However, if you compare the underlying queue objects, you might see that the definition of the object changes. I describe this situation in a different blog post.

Migrate Old Queues

If the queues are REALLY old, the queue compatibility setting might also be old. If so, I strongly recommend migrating the old queues to the newest version. You can learn more about that in this blog post.

Appendix

Further Reading

Honey, I Shrunk the Database! Why Is My Database Smaller after Migration?

In a recent migration, a customer noticed that the size of the database decreased dramatically after the import. The size of the data files went from 500 GB to only 300 GB. Data Pump didn’t report any errors. Nevertheless, the customer was afraid that data was lost during migration.

The customer asked:

Am I missing data?

Why Would a Database Shrink During Import?

First, the following applies to Data Pump imports. If you migrate with transportable tablespaces or Data Guard, things are different.

Fragmentation

If you perform a lot of DML on a table, it will become fragmented over time. A fragmented table will use much more space, because the database will only re-use a data block with free space, once the free space reaches a certain limit (PCTUSED). Often, this leads to blocks that are never filled and waste of space.

When you import, the database neatly packs all blocks, and the table is completed defragmented.

Depending on the nature of your DML statements, you can see a dramatic impact on space usage for a defragmented table.

Indexes

After importing the data, Data Pump rebuilds the indexes. In many cases, an index has some level of fragmentation, but a rebuild removes all that, resulting in a neatly packed and efficient index. Most often, an index rebuild ends up using less space.

PCTFREE

If you change PCTFREE for an existing object, it applies to new blocks only. The existing blocks are left untouched. Any space saving from the lower setting applies to new data blocks only.

However, during an import (or index rebuild) the database builds the object from scratch and applies the setting to all the blocks. Now, all blocks have the new settings.

Connor’s Words

Connor McDonald was part of the original conversation with the customer. He phrased it like this:

Create a 100 GB table, and a 1 GB table. Your database is now (at least) 101 GB. Drop the 100 GB table and do an export/import. You now have a 1 GB database.

How Can You Know No Data Is Lost?

If you can’t use the database’s size to indicate whether data is lost, what do you do?

We covered this in one of our webinars, Migrating Very Large Databases.

Can the Database Become Bigger?

The headline of this blog post is a reference to Honey, I shrunk the kids. The sequel is called Honey, I Blew Up the Kid.

Then, can the database become bigger as part of an import? Yes, it may.

  • If you move from a singlebyte character set to Unicode, then characters stored in your database may take up more space. In particular, LOBs now take up much more space.
  • PCTFREE setting of your segments may also cause them to increase in size. Opposite of the case above.