I need to export and import data from a Oracle 9i to Oracle 12.2 database.

This old app doesn't log in as one privileged user - it logs in for each user. I have hundreds of users with nothing but synonyms.

I need to pre-create the users and their privileges so I can import.

There are scripts all over the internet:
* Ask Tom suggests import and an indexfile (which I find kludgy).
* DBMS_METADATA.GET_DDL scripts, as well.

However, being in a rush, I haven't found one script to cycle through all the users and generate their DDL and grants, etc.

I want to run one 9i script to grab everything and one 12.2 script to create users, but can't find anything useful.

Can you suggest how I should solve this? Or am I making this too complicated?

2 Answers 2


A really concise way would be to use the EXCLUDE parameter to expdp, something like:


You'll want to list all the system users and your actual data-containing schema(s).



  • there is no datapump in Oracle 9i
    – miracle173
    Aug 29, 2018 at 21:09

I resolved my problem using the SQL Developer tool.

I followed the steps in this article to generate the DDL for anything in the database. I had an 11g DB that I needed to migrate in addition to a 9i and this process was easy for 11g.

However, for 9i I used the script in this post and ran it in SQLDeveloper to pre-create my users. Just save the output of the script to a file and then run in SQLDeveloper for ease.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.