Oracle - Extract user DDL for migration

Question

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?

Answer 1

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

expdp ... EXCLUDE=SCHEMA:"IN ('OUTLN','SYSTEM','SYSMAN','FLOWS_FILES','APEX_030200','APEX_PUBLIC_USER','ANONYMOUS')"

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

Reference:

https://oracle-base.com/articles/10g/oracle-data-pump-10g#IncludeExclude

Answer 2

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.