Import only sequences from an Oracle exp dump

Question

Is there a way to import all sequences of a schema from a dump of a schema (generated with exp full=y)? I don't want to import procedures or tables, but only all the sequences.

I want to use the imp command.

You could do this with datapump `expdp`/`impdp`, using the `include` parameter on [export](http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007837) or [import](http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#autoId29), but not the old `exp`/`imp`. Is there a reason you're stuck using the old utilities? — Alex Poole, Apr 22 '13 at 08:18

David Aldridge · Answer 1 · 2013-04-22T08:21:08.610

3

If you use the original Import utility, you can use four different modes: Table Mode, User Mode, Full Database Mode, and Tablespace Mode.

Each of them will import different types of object, but they are not as controllable as the new Data Pump Import, and you cannot choose exactly which types to import.

See the documentation here.

Sequences are imported in User and Full Database modes, but that also imports many other object types, including procedural objects (procedures, functions and packages).

I think you will have to open the dmp file with a text editior and copy-paste the Create Sequence statements to a new .sql file. You may be able to use Perl, Ruby, or a shell script to make that easier, but you cannot do it with imp only.

edited Apr 22 '13 at 08:21

answered Apr 22 '13 at 08:04

David Aldridge

51,479
8
68
96

but with imp command i import also the sequence ? – esoni Apr 22 '13 at 08:07
Yes, but also many other object types. – David Aldridge Apr 22 '13 at 08:11
the important thing i dont want to import procedure/function and package. import also this type of object ? – esoni Apr 22 '13 at 08:12
i want to import sequence number but not procedure or function – esoni Apr 22 '13 at 08:14
What David is saying is *you can't import just sequences*. Not with IMP. This sort of inflexibility is why IMP & EXP have been deprecated, and why you should be using Data Pump instead. – APC Apr 22 '13 at 08:51

score 3 · Accepted Answer · answered Apr 22 '13 at 09:32

exp has it's limitations. expdp is the better option if you want to use an exp/imp like solutiuon.

If the goal is to just copy the sequences, why not use dbms_metadata to get the ddl:

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', u.sequence_name, decode(u.sequence_owner,'SYS','',sequence_owner)) ddl
     FROM all_sequences u where sequence_owner = 'SOE' order by sequence_owner, sequence_name;

Import only sequences from an Oracle exp dump

2 Answers2