Skip to main content

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:
  • 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.

Comments

Post a Comment

Comments are moderated, and are usually posted within 24 hours if approved. You must have a minimum of OpenID to post comments.

Popular posts from this blog

Disable auto save in JetBrains IDE software (IntelliJ IDEA, PyCharm, PhpStorm)

JetBrains provides the following IDE software: IntelliJ IDEA PhpStorm PyCharm RubyMine WebStorm AppCode CLion Google also provides Android Studio which is powered by the IntelliJ platform. If you come from a different IDE such as Eclipse, you will be unpleasantly surprised to find that JetBrains-branded IDEs automatically save everything the moment you look away. The proponents argue that as you work on your project, you should not have to worry about saving files. But to others, this auto-save behavior which is enabled by default is a curse that catches them by surprise, and a shocking departure from the workflow they are very much used to. You can change the behavior by altering some settings.
Post a Comment
Read more

Make Samsung DVD-C350 region-free

Update 2: An anonymous commentator has shown me a way to make Region 1 players (such as DVD-H1080R) region-free by first converting it to Region 3, then applying my region-free hack below. For details, click here or look for a comment by an Anonymous user dated 18 April 2011. Update: The instructions in the original post below did not make the DVD player region-free. Instead it only locked it to region 1. Many thanks to Anonymous who posted the first comment on this post, I now have alternate instructions. Note: If you have edited the numbers menu (see original post) , I suggest you return it to the original settings you had backed up. A modified numbers menu may prevent the instructions below from working properly.
62 comments
Read more

Group, Ungroup and Regroup disabled in Word

I was editing a Microsoft Word document which had a collection of shapes and text boxes grouped together. I wanted to modify some of the shapes, and therefore I had to ungroup them. But when I right-click the group and open the Group menu, all three options namely Group, Ungroup and Regroup are completely disabled or grayed out. I couldn’t figure out what’s wrong. This group of objects is perfectly ungroupable, and I can even select objects within the group. However, Microsoft Word 2007 is not letting me ungroup it. I searched the Internet for a solution, but did not find anything very useful. The closest I came across is this statement: “The type of Text Wrapping doesn't make any difference as long as it isn't In Line with Text.” ( Link here ) Anyway, I changed the text wrapping of the group of objects from ‘In line with Text’ to ‘Tight’ and viola! I could now ungroup it and edit it. The document got a bit messed up when I did so, but after I ungrouped, edited and regro...
10 comments
Read more