How to Backup and Restore Oracle PL/SQL User Objects Using Export/Import Tools

Backup

1.1 Export User Objects

In PL/SQL Developer select Tools → Export User Objects and choose the objects to back up, such as TABLE, SEQUENCE, VIEW, PACKAGE, TYPE, FUNCTION, PROCEDURE, PACKAGE BODY, and TRIGGER.

1.2 Export Tables with Data

Choose Tools → Export Table and enable the option to include all table data.

After exporting, you obtain an SQL file (typically a few megabytes) and a PDE file (potentially hundreds of megabytes) containing the schema and data.

Restore

2.1 Drop Existing User

If the target database already contains the user, drop it and terminate any active sessions.

drop user lqpvplmuser cascade;
select sid,serial# from v$session where username='username';
alter system kill session '150,9019';

2.2 Create User and Grant Privileges

CREATE USER username PROFILE "DEFAULT" IDENTIFIED BY "tyinteplm" ACCOUNT UNLOCK;
GRANT "CONNECT" TO username;
GRANT "RESOURCE" TO username;
grant create cluster to username;
grant create database link to username;
grant create procedure to username;
grant create sequence to username;
grant create table to username;
grant create trigger to username;
grant create type to username;
grant create view to username;
grant debug any procedure to username;
grant debug connect session to username;
grant select any dictionary to username;

2.3 Execute Exported SQL

Open a command window, paste the exported SQL script, and run it. This recreates all user objects—including tables, procedures, functions, and sequences—but the tables are initially empty.

2.4 Import Table Data

In PL/SQL Developer select Tools → Import Table . The import process disables all triggers and foreign‑key constraints, deletes existing table rows, inserts the exported data, then re‑enables the constraints and triggers.

Importing user objects usually completes within minutes, while loading table data can take from tens of minutes to several hours depending on data volume and hardware performance.