Export Oracle Users from One Database to Another

Oracle Export Users from One Database to Another

Using PeopleTools Data Mover

For Oracle databases utilizing PeopleTools, use Data Mover scripts to transfer users. This ensures security information is accurately copied, particularly during upgrades or when synchronizing production with development environments.

DMS Script Process

Execute USEREXPORT.DMS to export user profiles from the source to a DAT file. Then, use USERIMPORT.DMS to import from the DAT file to the target database. This process involves tables like PSOPRDEFN and PSROLEUSER.

Using Oracle Data Pump (expdp)

Export users with their roles and privileges using the expdp command. This method is ideal for schema refreshes without data. Ensure user creation and privilege scripts are version-controlled.

Exporting Users to CSV

Oracle allows exporting users to a CSV file, capturing essential attributes. Navigate to Identity & Security, select users, and use the Export users function from the More actions menu. Save the exported CSV in UTF-8 format.

Frequently Asked Questions

What is the purpose of the USEREXPORT.DMS and USERIMPORT.DMS scripts?

The USEREXPORT.DMS script is used to export User Profiles from the source database into a DAT file, and the USERIMPORT.DMS script is used to import that data into the target database.

Where can I find the USEREXPORT.DMS and USERIMPORT.DMS scripts?

Both USEREXPORT.DMS and USERIMPORT.DMS scripts are located in the /scripts folder.

Can I move user profiles with all their object rights between Oracle databases?

Yes, you can use the dbms_metadata package to move a user with all object rights from one Oracle database to another.

What roles must be granted to a user performing import and export with Oracle Data Pump?

The user must be granted the DBA role, and both datapump_exp_full_database and datapump_imp_full_database roles.

What are the prerequisites for using Oracle Data Pump to export and import users?

You must create an OS level directory, create a corresponding directory object inside both the source and target databases, and grant the necessary permissions to the user performing the export or import.

Common Use Cases

• 
  Migrating user accounts during a database upgrade or consolidation
• 
  Synchronizing user accounts between development, testing, and production environments
• 
  Backing up user account data for disaster recovery planning
• 
  Transferring user accounts to a new database as part of an organizational restructuring
• 
  Copying user accounts to a secondary database for load balancing or distributed query processing

Sourcetable: A Streamlined Alternative to Oracle Exports

Opt for Sourcetable to centralize and manage data with ease, bypassing the traditional complexities of exporting users from Oracle databases. Its powerful connectivity transforms data handling.

Experience real-time data retrieval with Sourcetable's intuitive spreadsheet interface. This seamless integration negates the need for cumbersome Oracle exports, offering a direct, efficient alternative.

Sourcetable's dynamic interface allows for on-the-fly data manipulation, eliminating the need for intermediary steps typical in Oracle database user exports. Simplify your workflow today.