Setting up a local Oracle XE database and importing DMP file

The experience of setting up a local Oracle Express Edition database is not a straight-forward as it should be. The following is supposed to outline what could go wrong and how to go about it the right way. It also includes importing a DMP file (a dump) from another system.

• First of all, download the installer from Oracle website. You will need to sign-in to download - the account creation is free. Be sure to choose the correct bit as per your computer (x64 or x86).
• Extract the download and install XE by running DISK1\setup.exe (and feel nostalgic of the floppy disk era).
• During installation, you will need to choose a new password. There will also be some details displayed after you enter the new password, such as folders and ports.
• Be sure to save both password and details in a text file for future reference.
• I saved it as C:\oraclexe\details.txt.
• Save password only if it's generic and you are likely to forget.
• The installation will take a while, but usually, no restart is necessary.
• Once installation is complete, you may want to import your dump.
• You need to move the import file to a specific location with a specific filename if you are going to use the impdp command.
• It is C:\oraclexe\app\oracle\admin\XE\dpdump\expdat.dmp for the default installation. Make sure the file name is expdat.dmp.
• At this point you may want to open SQL Plus or Toad and create necessary tablespaces and users/roles. See below.
• Open a new command prompt window.
• Enter the impdp command in the command prompt.
• There are two commands, imp and impdp (not impdb! the DP stands for Data Pump). You will need to use the command matching the one that was used to export the database: exp or expdp.
• I was trying to import using imp command at first, and it just threw some random error about charset and what not. Don't waste time troubleshooting this error. Just use impdp instead.
• You can add the TABLE_EXISTS_ACTION=REPLACE parameter to force replace all tables that already exist (perhaps from previous import attempts). There is also TABLE_EXISTS_ACTION=TRUNCATE if that suits better.
• Alternatively you can drop all objects under a user using DROP USER username CASCADE; to do a clean import.
• The utility asks for the username here. Enter SYS AS SYSDBA as the username, which includes the role.
• If you try to login as SYS only, you will get an error ORA-28009: connection as SYS should be as SYSDBA or SYSOPER. This is why you include AS SYSDBA in the username.
• Enter the password. There won't be any characters shown on screen. Just key in and press Enter.
• The import should run now. There are few things to note though:
• Oracle XE doesn't have partitioning. It is very limited - see comparison. For partitioning, you need Oracle Enterprise Edition (EE).
• You will need to create tablespaces in advance before starting the import. To do this, use SQL Plus which you can access as stated below, or you can use Toad for Oracle (which provides a nice GUI). Be sure to use auto-extend.
• You will also need to create other users or roles required before starting the import.
• If you are not sure of the tablespaces and users/roles to create, just run the import and look at the errors thrown. Create the required entities then run the import again.
• The log of import is also written out to C:\oraclexe\app\oracle\admin\XE\dpdump\import.log in addition to being displayed in the command window. This file is overwritten for each import, so be sure to back up if you need it later.
• If you want to connect from Toad, you can use the command lsnrctl status (on Windows command prompt) to check Service Names accepted. They will be listed as Service "NAME" and status READY. I used XEXDB.

In the Start Menu (All Programs), a new folder "Oracle Database 11g Express Edition" is added.

• Get Started - This is supposed to launch the browser console, but may show an error because of the environment variable not being set. If it does, manually navigate to this URL, which takes you to the same console:
• http://127.0.0.1:8080/apex/f?p=4950
• Run SQL Command Line - This launches the SQL Plus utility that you can use to administer the database.
• You need to connect to the database using CONNECT SYS AS SYSDBA or any other user.
• Tablespaces can be created using CREATE TABLESPACE command.
• You can also use Toad for Oracle or any other program to connect to the database and administer it.