CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloperplsqldeveloper
RMu
RMu

Reputation: 869

How to generate DDL for all Users and privileges in Oracle

I'm trying to migrate a database instance from RDS Oracle to On-premise Oracle. I'm still pretty new to database scripting. I found this script that does the work for me for a single user that I pass via SQLDeveloper. However I have 200+ users on my RDS Oracle instance.

https://oracle-base.com/dba/script?category=script_creation&file=user_ddl.sql

I have tried removing the variable here for the user name and the rownum but this generates a lot of duplicate entries. Can someone guide me what is the best way to achieve the result set of the above script for all users.

Much appreciated!

Upvotes: 0

Views: 2265

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

I'd suggest another approach (if you can use it): data pump.

You'd perform export

  • using user SYSTEM or SYS as they own the database and can export ...
  • ... FULL database
  • content would be METADATA_ONLY (so that you'd skip data; I understood you don't need that)

That would be something like

expdp system/pwd file=your_db.dmp full=y content=metadata_only

Import goes the same way, in the target database.

Upvotes: 2

Related Questions