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.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Maria Anderson
Can I use /var/opt/oracle/oratab to specify listener information? Continue Reading
We would like to migrate our database from Oracle 8.1.7 to Oracle 10g. We would like to know the impact for our application developed using Delphi ... Continue Reading
I have been trying to install Oracle 8.1.7 on SUSE Linux 9.0 and got the error: "Error in invoking target install of makefile /opt/oracle/...../*.mk." Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.