Migrating a tablespace within the same Oracle DB instance can be useful for backup testing, development scenarios, or cloning environments. This step-by-step guide covers creating a dedicated tablespace, user setup, object creation, export\/import operations, and verification.<\/p>\n\n\n\n
SELECT tablespace_name, file_name FROM dba_data_files;<\/pre>\n\n\n\n\u2795 Create a New Tablespace<\/h3>\n\n\n\n
CREATE TABLESPACE galaxy
DATAFILE '\/u02\/oradata\/ORCL\/galaxy01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;<\/pre>\n\n\n\n\u2705 Verify the Tablespace<\/h3>\n\n\n\n
SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name = 'GALAXY';
SELECT tablespace_name, SUM(bytes)\/1024\/1024 AS size_mb
FROM dba_data_files
WHERE tablespace_name = 'GALAXY'
GROUP BY tablespace_name;
SELECT file_name
FROM dba_data_files
WHERE tablespace_name = 'GALAXY';<\/pre>\n\n\n\n\ud83d\udc64 Create User with Default Tablespace<\/h3>\n\n\n\n
CREATE USER dev_user IDENTIFIED BY \"P@ssw0rd\"
DEFAULT TABLESPACE galaxy
QUOTA UNLIMITED ON galaxy;<\/pre>\n\n\n\n\ud83d\udd10 Grant Necessary Privileges<\/h3>\n\n\n\n
GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE TABLE,CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER,CREATE SEQUENCE, CREATE SYNONYM TO dev_user;<\/pre>\n\n\n\n\ud83d\udccb Check User Details<\/h3>\n\n\n\n
SELECT username, default_tablespace
FROM dba_users
WHERE username = 'DEV_USER';<\/pre>\n\n\n\n\ud83d\udce6 Check User Tablespace Usage<\/h3>\n\n\n\n
SELECT owner, SUM(bytes)\/1024\/1024 AS size_mb
FROM dba_segments
WHERE owner = 'DEV_USER'
GROUP BY owner;<\/pre>\n\n\n\n\ud83d\udd27 PART 2: Login as
DEV_USER<\/code> and Create Schema Objects<\/strong><\/h2>\n\n\n\n\ud83d\udd11 Connect<\/h3>\n\n\n\n
CONNECT dev_user\/\"P@ssw0rd\";<\/pre>\n\n\n\n\ud83d\udcc2 Create and Populate Table<\/h3>\n\n\n\n
CREATE TABLE project_data ( id NUMBER, name VARCHAR2(100) );
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO project_data VALUES (i, 'Item_' || i);
END LOOP;
COMMIT;
END;
\/<\/pre>\n\n\n\n\u2699\ufe0f Create Schema Objects<\/h3>\n\n\n\n
CREATE INDEX idx_proj_id ON project_data(id);
CREATE VIEW view_proj AS SELECT * FROM project_data WHERE id <= 5;
CREATE SEQUENCE seq_proj START WITH 11 INCREMENT BY 1;
CREATE SYNONYM syn_proj FOR project_data;
CREATE OR REPLACE PROCEDURE add_data IS
BEGIN
INSERT INTO project_data VALUES (seq_proj.NEXTVAL, 'Auto_Item');
COMMIT;
END;
\/
CREATE OR REPLACE FUNCTION fetch_name(p_id NUMBER) RETURN VARCHAR2 IS
v_name VARCHAR2(100);
BEGIN
SELECT name INTO v_name FROM project_data WHERE id = p_id;
RETURN v_name;
END;
\/
CREATE OR REPLACE TRIGGER trg_upper_name
BEFORE INSERT ON project_data
FOR EACH ROW
BEGIN
:NEW.name := UPPER(:NEW.name);
END;
\/<\/pre>\n\n\n\n\ud83d\uddc2\ufe0f PART 3: Setup OS and Logical Directories<\/strong><\/h2>\n\n\n\n
\ud83e\uddf1 Create OS Directories<\/h3>\n\n\n\n
mkdir -p \/u02\/dump\/export
mkdir -p \/u02\/dump\/import
chmod 777 \/u02\/dump\/export
chmod 777 \/u02\/dump\/import<\/pre>\n\n\n\n\ud83d\udcc1 Create Oracle Logical Directories<\/h3>\n\n\n\n
CREATE OR REPLACE DIRECTORY exp_dir AS '\/u02\/dump\/export';
GRANT READ, WRITE ON DIRECTORY exp_dir TO dev_user;
CREATE OR REPLACE DIRECTORY imp_dir AS '\/u02\/dump\/import';
GRANT READ, WRITE ON DIRECTORY imp_dir TO dev_user;<\/pre>\n\n\n\n\ud83d\udcbe PART 4: Perform Tablespace Export<\/strong><\/h2>\n\n\n\n
expdp dev_user\/\"P@ssw0rd\" DIRECTORY=exp_dir DUMPFILE=galaxy_exp.dmp LOGFILE=galaxy_exp.log TABLESPACES=galaxy<\/pre>\n\n\n\n\ud83d\udcc4 Export Log Sample<\/h3>\n\n\n\n
Export: Release 19.0.0.0.0 - Production on Mon May 20 10:15:30 2025
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Starting \"DEV_USER\".\"SYS_EXPORT_TABLESPACE_01\":
Processing object type TABLE_EXPORT\/TABLE\/TABLE_DATA
. . exported \"DEV_USER\".\"PROJECT_DATA\" 6.015 KB 10 rows
Master table \"DEV_USER\".\"SYS_EXPORT_TABLESPACE_01\" successfully loaded\/unloaded
******************************************************************************
Dump file set for DEV_USER.SYS_EXPORT_TABLESPACE_01 is:
\/u02\/dump\/export\/galaxy_exp.dmp
Job \"DEV_USER\".\"SYS_EXPORT_TABLESPACE_01\" successfully completed<\/pre>\n\n\n\n\ud83e\uddf9 PART 5: Optional Cleanup Before Import<\/strong><\/h2>\n\n\n\n
\u274c Drop User (If Re-importing Cleanly)<\/h3>\n\n\n\n
DROP USER dev_user CASCADE;<\/pre>\n\n\n\n\ud83d\udd01 Recreate User and Objects<\/h3>\n\n\n\n
Repeat PART 1<\/strong> and PART 2<\/strong> if necessary.<\/p>\n\n\n\n
\ud83d\udce4 PART 6: Copy Dump File & Run Import<\/strong><\/h2>\n\n\n\n
\ud83d\uddc2\ufe0f Copy Dump to Import Directory<\/h3>\n\n\n\n
cp \/u02\/dump\/export\/galaxy_exp.dmp \/u02\/dump\/import\/<\/pre>\n\n\n\n$ cp \/u02\/dump\/export\/galaxy_exp.dmp \/u02\/dump\/import\/
# No error output means file copied successfully<\/pre>\n\n\n\n\ud83d\udce5 Run Import Command<\/h3>\n\n\n\n
impdp dev_user\/\"P@ssw0rd\" DIRECTORY=imp_dir DUMPFILE=galaxy_exp.dmp LOGFILE=galaxy_imp.log TABLESPACES=galaxy TABLE_EXISTS_ACTION=REPLACE<\/pre>\n\n\n\n\ud83d\udcc4 Import Log Sample<\/h2>\n\n\n\n
Import: Release 19.0.0.0.0 - Production on Mon May 20 10:20:42 2025
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Master table \"DEV_USER\".\"SYS_IMPORT_TABLESPACE_01\" successfully loaded\/unloaded
Starting \"DEV_USER\".\"SYS_IMPORT_TABLESPACE_01\":
Processing object type TABLE_EXPORT\/TABLE\/TABLE
. . imported \"DEV_USER\".\"PROJECT_DATA\" 6.015 KB 10 rows
Processing object type TABLE_EXPORT\/TABLE\/INDEX\/INDEX
. . imported \"DEV_USER\".\"IDX_PROJ_ID\"
Processing object type TABLE_EXPORT\/TABLE\/CONSTRAINT\/CONSTRAINT
Processing object type TABLE_EXPORT\/TABLE\/TRIGGER
. . imported \"DEV_USER\".\"TRG_UPPER_NAME\"
Processing object type TABLE_EXPORT\/TABLE\/PROCEDURE\/PROCEDURE
. . imported \"DEV_USER\".\"ADD_DATA\"
Processing object type TABLE_EXPORT\/TABLE\/FUNCTION\/FUNCTION
. . imported \"DEV_USER\".\"FETCH_NAME\"
Processing object type TABLE_EXPORT\/TABLE\/VIEW
. . imported \"DEV_USER\".\"VIEW_PROJ\"
Processing object type TABLE_EXPORT\/TABLE\/SEQUENCE\/SEQUENCE
. . imported \"DEV_USER\".\"SEQ_PROJ\"
Processing object type TABLE_EXPORT\/TABLE\/SYNONYM
. . imported \"DEV_USER\".\"SYN_PROJ\"
Job \"DEV_USER\".\"SYS_IMPORT_TABLESPACE_01\" successfully completed at Mon May 20 10:20:56 2025<\/pre>\n\n\n\n\u2705 PART 7: Post-Import Validation<\/strong><\/h2>\n\n\n\n
\ud83d\udce6 Check Imported Objects<\/h3>\n\n\n\n
SELECT object_name, object_type FROM user_objects;<\/pre>\n\n\n\n\ud83d\udd0d Verify Data<\/h3>\n\n\n\n
SELECT * FROM project_data;<\/pre>\n\n\n\n\u2699\ufe0f Test Procedure and Function<\/h3>\n\n\n\n
EXEC add_data;
SELECT fetch_name(1) FROM dual;<\/pre>\n\n\n\n\ud83d\udd01 Verify Trigger Functionality<\/h3>\n\n\n\n
INSERT INTO project_data (id, name) VALUES (100, 'test');
SELECT * FROM project_data WHERE id = 100;<\/pre>\n\n\n\n\ud83c\udfc1 Conclusion<\/strong><\/h2>\n\n\n\n
This guide walks you through a full tablespace-level export\/import within the same Oracle DB instance using
Data Pump<\/code>. The process is ideal for internal migrations, backups, or test environment setups.<\/p>\n\n\n\nPro Tip<\/strong>: Always validate objects, permissions, and data integrity after import to avoid unexpected runtime issues.<\/p>\n","protected":false},"excerpt":{"rendered":"
Migrating a tablespace within the same Oracle DB instance can be useful for backup testing, development scenarios, or cloning environments. This step-by-step guide covers creating a dedicated tablespace, user setup, object creation, export\/import operations, and verification. \ud83e\uddf1 PART 1: Setup \u2013 Tablespace and User Creation \ud83d\udd0d Check Existing Tablespaces and Datafiles SELECT tablespace_name, file_name FROM […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-3460","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3460","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=3460"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3460\/revisions"}],"predecessor-version":[{"id":3463,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3460\/revisions\/3463"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3460"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3460"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=3460"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}