Problem solve Get help with specific problems with your technologies, process and projects.

Eliminating partition tables during export

We are upgrading our database from Oracle 8i to 10g. While taking the export, how can we eliminate the partition tables?

I am working as a DBA in Oracle. I read your articles and they are very clear and I can understand them well. I need your help for one of my tasks.

We are upgrading our database from Oracle 8i to 10g on a Windows platform. Our production database is in 8i and the upgrade method we are using is imp/exp. The production box contains a lot of partition tables. If we export we will get the "rollback segment too small" error. So we are going to 1) exp the partition tables separately and 2) exp the remaining tables in each schema.

Import will happen in the same manner. Our DB size is nearly half a TB. I hope my above plans will go smoothly. I have one question about the above plan. While taking the export, how can we eliminate the partition tables? Will parfile work with a where condition, or is there any other way to do this? If I mention tables=(,,,,,) it's too long for me.

I have one solution, i.e., start the export and when a partition table comes just press Ctrl-C to avoid export of that table.

I need your help and suggestions for this task.

My first recommendation would be for you to test your migration strategy in a non-production environment. This is the only way you will be able to estimate how long the export/import jobs will take to complete, and also whether your proposed plan will be successful.

The 'query' clause available with export allows you to select a subset of rows from tables, not to include or exclude tables to export (this function is available, however, with Oracle 10g Data Pump). Your only option is to explicitly indicate, with the 'tables' clause, which tables you would like exported in a parameter file. To make this task easier, instead of typing out table names, you could write SQL to list the tables you want, then copy and paste the output into your parameter file (after some minor editing). Here's an example of writing SQL to get the table names of partitioned tables:

select owner||'.'||table_name part_tables from dba_part_tables
   where owner not in ('SYS','SYSTEM');
This will give you a list of partitioned tables not owned by SYS or SYSTEM. You could then copy and paste this list into your parameter file.

To exclude partitioned tables, try this SQL:

select owner||'.'||table_name nonpart_tables from dba_tables
   where owner not in ('SYS','SYSTEM') and table_name not in
 (select table_name from dba_part_tables 
      where owner not in ('SYS','SYSTEM'));
I would not recommend using the 'Ctrl-C' method you've described. This is not a reliable, nor elegant, method of exporting data and may cause issues during your imports.
This was last published in February 2007

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.