Let me first wish you a Happy New Year, this is my first post in 2016 so it would be rude from me not to mention this :) Let the 2016 be prosperous for each and every one of you out there.

Now, back to business :) We have migrated to the XE version of the Oracle database, as our needs do not request the full version anymore. Although the migration spared us some bucks as we are not paying the full licence anymore, it came with an additional cost – time needed to recreate everything. Since the XE version does not allow us multiple db instances, we have to create a new user/schema for each database we want to have. Additionally, i choose that we create separate tablespaces for every schema, just to keep it clean.

So first thing we need to do is login to the sqlplus with our system account (with the sysdba role), in order to add new tablespaces and create schema:

connect system/PASSWORD@XE as sysdba

Now, we can create new tablespaces with autoextend functionality. Please adjust the tablespace name and location to suit your needs:

CREATE TABLESPACE kuba2016_DATA DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\kuba2016_data.dbf' SIZE 300M REUSE
DEFAULT STORAGE ( INITIAL 160K NEXT 160K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 1);
ALTER DATABASE DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\kuba2016_data.dbf' AUTOEXTEND ON;

CREATE TABLESPACE kuba2016_INDX DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\kuba2016_indx.dbf' SIZE 250M REUSE
DEFAULT STORAGE ( INITIAL 160K NEXT 160K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 1);
ALTER DATABASE DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\kuba2016_indx.dbf' AUTOEXTEND ON;

One the tablespaces are created, we are ready to create new user/schema with needed privileges:

create user KUBA2016 identified by KUBA2016 default tablespace kuba2016_DATA;
GRANT create session to KUBA2016;
GRANT CREATE VIEW TO KUBA2016;
GRANT CREATE TABLE TO KUBA2016;
GRANT CREATE SESSION TO KUBA2016;
GRANT CREATE INDEXTYPE TO KUBA2016;
GRANT CREATE TRIGGER TO KUBA2016;
Grant CREATE DATABASE LINK to KUBA2016;
GRANT CREATE PROCEDURE TO KUBA2016;
GRANT EXECUTE ANY PROCEDURE TO KUBA2016;
GRANT CREATE ANY DIRECTORY TO KUBA2016;
COMMIT;
ALTER USER KUBA2016 quota unlimited on kuba2016_DATA;
ALTER USER KUBA2016 quota unlimited on kuba2016_INDX;

That is it, the new user/schema has been created and all we need to do now is import the oracle datapump. This can prove to be very tricky as we do not always know the name of the schema and tablespaces needed in order to import the datapump. If this is the case, I suggest that we extract DDL from the backup file using impdp:

impdp '/ as sysdba' dumpfile= logfile=import_log.txt sqlfile=ddl_dump.txt

If you examine the output file, you will realize if the schema needs to be remapped and/or additional tablespaces need to be created or remapped. I have to remap my schema and two tablespaces so my impdp command is probably more complicated than your. If you do not need to do this, just remove the last three parameters.
All you need to do now is import the datapump using impdp command:

impdp system/PASSWORD@XE directory=DATA_PUMP_DIR dumpfile=DPUMP.dmp logfile=log.log remap_schema=KUBA5_NOPIC:KUBA2016 remap_tablespace=KUBA_DATA:KUBA2016_DATA remap_tablespace=KUBA_INDX:KUBA2016_INDX

And that is it! Hope it helps

Leave a Reply

Your email address will not be published. Required fields are marked *