Changing or rename Oracle user schema

Posted: Apr 2, 2020 | Reading time: 4 min
⚠️ Warning: This post is over a year old, the information may be out of date.
📢 I’ve moved to a new website. Please visit me at https://journal.robbi.my !

Renaming or changing a schema is not an easy task in Oracle operation but if you really want to rename the schema go for the traditional way of exporting the existing schema and import into a new schema.

Step shown in this tutorial is using Oracle 11g, it maybe won’t work for newer version.

> select * from v$version;
|================================================================================|
|BANNER                                                                          |
|================================================================================|
|Oracle Database 11g Release 11.2.0.4.0 - 64bit Production                       |
|PL/SQL Release 11.2.0.4.0 - Production                                          |
|CORE 11.2.0.4.0 Production                                                      |
|TNS for Linux: Version 11.2.0.4.0 - Production                                  |
|NLSRTL Version 11.2.0.4.0 - Production                                          |

Data Pump Mapping to the imp Utility

Please take note, Data Pump import often doesn’t have a one-to-one mapping of the legacy utility parameter. Data Pump import automatically provides many features of the old imp utility.

For example, COMMIT=Y isn’t required because Data Pump import automatically commits after each table is imported. Table below describes how legacy import parameters map to Data Pump import.

Original imp Parameter	Similar Data Pump impdp Parameter
FROMUSER	REMAP_SCHEMA
TOUSER	REMAP_SCHEMA

How I gonna rename my ORACLE schema

Let say, I accidently create user schema HOST_USER as suppose to be host1

-- Create user schema (mistaken username here..shit)
create user HOST_USER IDENTIFIED BY password4sk default TABLESPACE host_sk;
-- creating table space
create TABLESPACE host_sk datafile 'host_sk.dbf' size 1G autoextend on maxsize 8G;
-- create database role
create role HOST_SK_ROLE;
-- granting some privilage to role we created
grant  
   CREATE SESSION, ALTER SESSION, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, 
   CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, 
   CREATE VIEW, DEBUG CONNECT SESSION
to HOST_SK_ROLE;
-- grant that role to user (that I mistaken created previously) 
-- and give tablespace quota to them
GRANT HOST_SK_ROLE TO HOST_USER;
ALTER USER HOST_USER QUOTA unlimited ON host_sk;
-- create table
CREATE TABLE "HOST_USER"."STOCK_BALANCE_WS" 
   ("TRANSFERID" NUMBER(9,0), "ARTICLE_ID" VARCHAR2(14), 
  "QUANTITY" NUMBER(6,0)) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "HOST_SK" ;
-- insert some data inside
INSERT INTO HOST_USER.ARTICLE_SW 
       (TRANSFERID, ARTICLE_ID, ARTICLE_NAME, DESCRIPTION, WEIGHT) 
VALUES (1, '1003', 'CONDOM DUREX', 'Super studs', 10);

As you see, I already do lot of thing with my database, then I just realize schema should be HOST1 instead of HOST_USER! I want to rename the schema. Unfortunately, oracle don’t allow to change schema name easily.

There is a trick by importing and map to HOST1 schema (if you follow my step, please don’t just copy paste. create the target user it if you don’t have and as long that user have same privilage and tablespace it will be fine)

Export with oracle data pump

$ expdp HOST_USER/password4sk directory=tmp schemas=HOST_USER dumpfile=old_schema_to_remap.dmp  LOGFILE=exp_schema_to_remap.log
Export: Release 11.2.0.4.0 - Production on Thu Apr 2 04:17:08 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "HOST_USER"."SYS_EXPORT_SCHEMA_01":  HOST_USER/******** directory=tmp schemas=HOST_USER dumpfile=old_schema_to_remap.dmp LOGFILE=exp_schema_to_remap.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "HOST_USER"."ARTICLE_SW"                     0 KB       0 rows
Master table "HOST_USER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HOST_USER.SYS_EXPORT_SCHEMA_01 is:
  /tmp/old_schema_to_remap.dmp
Job "HOST_USER"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Apr 2 04:17:21 2020 elapsed 0 00:00:13

Import to other target user via remap_schema parameter

$ impdp userid=host1/password4sk directory=tmp dumpfile=old_schema_to_remap remap_schema=HOST_USER:host1 LOGFILE=imp_schema_to_remap.log
Import: Release 11.2.0.4.0 - Production on Thu Apr 2 04:19:07 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Master table "HOST1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HOST1"."SYS_IMPORT_FULL_01":  userid=host1/******** directory=tmp dumpfile=old_schema_to_remap remap_schema=HOST_USER:host1 LOGFILE=imp_schema_to_remap.log
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HOST1"."ARTICLE_SW"                            0 KB       0 rows
Job "HOST1"."SYS_IMPORT_FULL_01" successfully completed at Thu Apr 2 04:19:09 2020 elapsed 0 00:00:01

after import via data pump, the next step is just to drop the old schema.

This step is much easier than redo everything IMHO. Anyway, please careful. I am novice oracle dba, my steps maybe not suitable for your use case.

Edit

Have some thoughts, discussion or feedback on this post?

💬 Send me an email