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

Can't exit Toddler Lock on Android phone

Toddler Lock will lock your Android phone completely so you can give it to a baby or toddler to play with. The only danger from the kid is that it may throw your phone and break it. But other than that, the kid will not be able to damage the phone by unlocking it and messing with what's inside. Here's the problem. The security of Toddler Lock is so tight that it would seem the only way to exit out of the app is to tap four corners of screen in a clockwise direction. (Note that you have to tap the four corners one after the other. Tap any corner, remove your finger, tag next corner in CW direction, remove your finger, and so on. Some people seem to think you need to hold all corners simultaneously which is wrong.) You may think a restart may also unlock it but even pulling out the battery for minutes (or perhaps hours) will not help. You will be stuck in Toddler Lock no matter what. This is fine until my screen started having problems. Two of the four corners I had to touch...
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