Only import sequences?
I fully exported a db.
I only import 1 user. But I made a mistake: I dropped the tables, but forgot to drop the sequences.
Oracle documentation says:
If sequence numbers need to be reset to the value in an export file as part of an import, you should drop sequences. A sequence that is not dropped before the import is not set to the value captured in the export file, because Import does not drop and re-create a sequence that already exists. If the sequence already exists, the export file's CREATE SEQUENCE statement fails and the sequence is not imported.
Now, old sequences are used, with wrong values.
Can I drop the sequences and import them seperately, without importing something else?
Help is highly appreciated!
I'm pretty sure it's going to try and import the tables etc again.
Why not try dropping the sequences then doing an inport with:
This won't try to load the rows, and won't chuck out messages when objects already exist.
Another alternative is to capture the DDL for the sequences from the original database and load them manually. You can do this directly or do the import using the indexfile option to generate a script. You can then pick out the sequence DDL from this script.
Step 1: Drop the Seq.
Step 2: Find out the max value from the table.
Step 3: Recreate the seq again starting with max value obtained in step 2.
Or you can query from dba_sequences to get the last value(LAST_NUMBER column), and recreate the seq.
I drop the sequences before the import. As soon as the import starts and before Oracle starts to import tables, Oracle automaticly imports all sequences. Now I only have to import a dummy table to activate this.
The programmers had a bright idea: lets name the sequences completely random and if we don't document this, its even more brilliant!
So it's hard to determine which table uses which sequence...
(don't worry, I took all developers outside and had them shot - just kidding offcourse)
Relying on column "last_number" is tricky, because the sequence are cached, so the that column does not exactly know the last_number. I heared that's an isssue. In a test I added 1000 at each sequence to be sure and the complete unknowns I started at 1000000. So the startvalue is always higher than the highest field-value.
/***do the import using the indexfile option to generate a script. You can then pick out the sequence DDL from this script.**/
How i will get the sequence info from indexfile= option
I think we can get only index info.
You're quite right. It contains tables and indexes but no sequence definitions.
If you use the SHOW=Y option it will likely show you what the commands are it would have used to generate the SEQUENCES. The problem is that it is in a not very friendly format that involves some tinkering.
If you are running under UNIX it is relatively easy to apply a grep command to the dmp file and pull out all the CREATE SEQUENCE commands.