Oracle 19c Export/Import Guide: Users, Roles & Tablespaces

Oracle 19c Export Import is an essential process for database migration, but it involves much more than just moving data. A successful migration requires exporting and recreating critical components such as users, roles, tablespaces, and datafiles before running Oracle Data Pump. In this guide, I’ll walk you through extracting DDL scripts for these objects and combining them into a reusable workflow. Whether you’re a DBA or just learning Oracle 19c, this tutorial will help you achieve a smooth and efficient migration.

---

Why You Need DDL Before Import

When migrating schemas between databases, simply running an impdp may fail if:

• The user doesn’t exist in the target DB.
• The roles and privileges are missing.
• The tablespace and datafiles don’t exist or are too small.

That’s why you should always generate DDL ahead of time. By preparing these scripts, you avoid errors like ORA-01918: user does not exist or ORA-00959: tablespace does not exist.

---

Extracting User DDL in Oracle 19c

To get existing users with their configuration, use DBMS_METADATA:

SET LONG 10000
SET PAGESIZE 500
SET LINESIZE 200

SELECT DBMS_METADATA.GET_DDL('USER', username)
FROM dba_users
WHERE account_status='OPEN';

Example output:

CREATE USER "HR" IDENTIFIED BY VALUES 'S:XYZHASHEDPASSWORD123'
DEFAULT TABLESPACE "HR_TBS"
TEMPORARY TABLESPACE "TEMP"
QUOTA UNLIMITED ON "HR_TBS";

.Notice the IDENTIFIED BY VALUES. Oracle doesn’t reveal plain-text passwords, but it exports hashed values. This lets you import users without forcing a password reset

Extracting Role DDL

Roles bundle privileges so you don’t have to grant them one by one. To extract all custom roles:

SELECT DBMS_METADATA.GET_DDL('ROLE', role)
FROM dba_roles
WHERE role NOT IN ('CONNECT','RESOURCE','DBA');

select 'CREATE ROLE ' || role || ' NOT IDENTIFIED;' from dba_roles;

Example:

CREATE ROLE "APP_READONLY";

Then extract the privileges assigned to each role:

-- System privileges granted to roles
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', role) FROM dba_roles;

-- Object privileges granted to roles
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', role) FROM dba_roles;

-- Roles granted to other roles
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', role) FROM dba_roles;

Sample output:

GRANT CREATE SESSION TO "APP_READONLY";
GRANT SELECT ANY TABLE TO "APP_READONLY";

Extracting Tablespace DDL

Tablespaces are critical: if they don’t exist during import, your schema creation will fail. To generate tablespace DDL:

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', tablespace_name)
FROM dba_tablespaces;

Example:

CREATE TABLESPACE "HR_TBS"
DATAFILE '/u02/oradata/ORCL/hr_tbs01.dbf' SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING ONLINE PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

Checking Tablespace Sizes

SELECT tablespace_name,
ROUND(SUM(bytes)/1024/1024, 2) AS total_size_mb
FROM dba_data_files
GROUP BY tablespace_name;

Sample output:

TABLESPACE_NAME   TOTAL_SIZE_MB
----------------  --------------
SYSTEM            900
SYSAUX            600
USERS             500
HR_TBS            200
UNDO              400

---

Putting It All Together: Migration Workflow

Here’s a humanized DBA workflow that combines everything:

Step 1 — Extract Users, Roles, and Tablespaces

Use the above queries with SPOOL in SQL*Plus:

SPOOL pre_migration_ddl.sql
-- User DDL
SELECT DBMS_METADATA.GET_DDL('USER', username) FROM dba_users;
-- Role DDL
SELECT DBMS_METADATA.GET_DDL('ROLE', role) FROM dba_roles;
-- Tablespace DDL
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', tablespace_name) FROM dba_tablespaces;
SPOOL OFF

Step 2 — Run Pre-DDL on Target

• Execute pre_migration_ddl.sql on the target system.
• This recreates users, roles, and tablespaces.

Step 3 — Export Data with Data Pump

Export schemas:

expdp system/password@ORCL schemas=HR directory=dpump_dir dumpfile=hr_exp.dmp logfile=hr_exp.log

Step 4 — Import into Target

Run import after ensuring pre-DDL has executed:

impdp system/password@ORCL schemas=HR directory=dpump_dir dumpfile=hr_exp.dmp logfile=hr_imp.log

Step 5 — Verify and Compile

Recompile invalids:

EXEC UTL_RECOMP.recomp_serial();

Verify users, roles, and tablespaces:

SELECT username, account_status FROM dba_users;
SELECT role FROM dba_roles;
SELECT tablespace_name FROM dba_tablespaces;

---

Final Thoughts

Oracle 19c migrations succeed when you think beyond just tables and data. Capturing users, roles, and tablespaces with DDL is the foundation of a smooth import. With DBMS_METADATA, you can automate the extraction of all critical definitions, spool them into scripts, and guarantee your target database matches the source.

By following this guide, you’ll avoid the classic “user does not exist” or “tablespace missing” errors, and you’ll build confidence in your export/import process.