Oracle Express Installation Guide

Oracle Express Installation Guide Introduction These Oracle installation instructions are used by students enrolled in the Master of Science in Computer Information Systems and other Computer Science Department programs in both on‐campus and online programs. The document begins with a discussion of Oracle, connecting to the Oracle web site, creating an Oracle Technology Network (OTN) account, then downloading and installing Oracle, then setting up a client to connect to your database. The examples in the main document are for the Microsoft Windows family, including Windows Windows 7, Windows 8, and Windows 10. If you would like to install Oracle on a Mac (OS X), please first follow the instructions in Appendix D, then continue with the rest of this installation guide. If you are installing on an operating system other than the Windows family or OS X, such as Linux or Solaris, then follow the instructions in this installation guide to join OTN, and download the version of Oracle for your platform rather than the Windows version. The Quick Installation Guide and the full Installation Guide for your operating system can be found in the Install directory created when you unzip the file you will download as part of these instructions. These setup instructions are for Oracle 11g Express. Although Express has limitations with regard to the amount of memory, size, and number of tables allowed, it is quite usable for most of our classes, and it introduces a much lighter load on your computer. Oracle has maintained upward compatibility for SQL between Oracle versions for many years, so if you cannot install Oracle 11g Express on your own machine, and you have ready access to installation for an older version of Oracle, you may be able to use it. Oracle is continually updating Oracle.com, so the screens that you see on Oracle.com will probably be a little different than what is shown in this document. If you can’t determine how to proceed because what you see is too different, or if something goes wrong ask your facilitator or instructor for help. Good luck, and have fun! Oracle Overview About Oracle Oracle has most of the advanced features in any relational or object‐relational database management system. Because of this, many of the new ANSI/ISO SQL standards are based on Oracle, so when you learn Oracle you are mainly close to the portable standards. Oracle runs on all common platforms from huge symmetric multiprocessors to ordinary PCs. Oracle is scalable, and supports both clustering and grid computing to surpass the performance and reliability of any single platform. Oracle is broadly considered as the standard by which other database management systems are measured, and when you learn to use Oracle your skills will transition well to other database management systems. Oracle runs on ordinary machines. Oracle runs on the largest multiprocessors, but Oracle also runs very well on ordinary PCs and recent Macintoshes. PCs are a common Oracle hardware platform, and there are Oracle versions for many PC operating systems, including the Windows family and Linux. The following instructions are for the default configuration, the easiest of the Oracle installations. Oracle is very flexible and can be configured to use less or more in the way of machine resources. Hardware Requirements Both Windows and Linux installations require approximately 1.5GB of disk space. At least 512MB of memory is recommended. Platform Independence Oracle runs on most common platforms, including the Windows family, and several Linux and Unix variants. Oracle supports the features that we use in our courses on all platforms, and it doesn’t matter what platform your Oracle runs on. Oracle behaves mostly the same regardless of the platform except for database size limitations and performance. Your exercises will behave the same way across all Oracle platforms. Preparations and Setup You can obtain a copy of Oracle by downloading Oracle database software directly from Oracle. If you experience downloading difficulties, please contact your facilitator, lead facilitator, or course instructor. Most people with broadband should be able to complete the download and install the program with minimal difficulty. Oracle 12c This guide covers installation of the Express edition of Oracle. If your class requires the full version, or if you would rather install the full edition for your own learning, follow the instructions in the Oracle Installation Guide instead of the instructions in this document. The Oracle Installation Guide includes instructions for setting up Oracle 12c, the full version of the product which does not have limitations on memory and size. Downloading and Installing Oracle Step 1: Creating an OTN Account Introduction Log On to OTN This section guides you through the sub‐steps to create your own Oracle Technology Network (OTN) account. Before you can download Oracle you must create and login to an Oracle Technology Network account. Go to http://www.oracle.com to log onto the Oracle Technology Network. Oracle is regularly updates their web site, so what you see may be different than the following. Click on the “Oracle Technology Network” tab at the top or just enter http://www.oracle.com/technology into your browser and you will see something like this: Click on “Sign In/Register for Account” in the top bar. You should see something like this: If you already have an Oracle account, enter your new username and password, and then click the "Go" button to continue. Sign Up For your Oracle OTN Account If you don't already have an Oracle account, click on “Create your Oracle account now” at the bottom of the right box. You should see something like the following:  Type in your email address and a password for your new OTN account.  Click Confirm. Note that Oracle uses the portion of your email address to the left of the "@" sign as your OTN user name. If the site complains that the user name is already taken, you will need to use another email address, or create a new one. It is easy to create a new email account at sites like yahoo.com. I used such a Yahoo email address below. You will then be presented with a long form asking for your name, address, etc. Complete the form and click "continue." You will find yourself logged into the OTN. Click on the “Downloads” tab to get to the Oracle database downloads. If you look in the upper left hand corner you should see your first name and “Sign Out”, which indicates that you are logged onto OTN. Step 2: Downloading Oracle Introduction Downloading the Setup File The following instructions guide you through the sub‐steps to download the Oracle zip file appropriate for your operating system. Click on the “Downloads/Oracle Database 11g Express Edition” link to get to the Oracle Express download page. After doing see, you will see a screen like this: You will need to accept the license agreement, then click on the “Oracle Database Express Edition 11g Release 2 for Windows x64” link to download the setup file. If you are running a 32‐bit version of Windows, you will need to click on the “Oracle Database Express Edition 11g Release 2 for Windows x32” link. Most people run 64‐bit windows today, but if you do not know for sure which version you have, in Windows click on “Start>>Accessories>>System Tools>>System Information”. Under the entry for “System Type”, it will indicate what type of hardware you have. It will look something like this: If you do not have 64‐bit hardware, the entry for “System type” might be something like “X86‐based PC”. If you do not see an entry like this, you have 64‐bit hardware, which is what most people will have today. Save the Zipped Installation File Create a directory for the downloaded file, such as C:\Oracle, and download the file into that directory. When the download is complete, you will see something similar to the following: Unzipping the File Use the zipping program of your choice to unzip the file. There are many such tools, but if you do not have one already, you can download 7‐zip at http://www.7‐ zip.org/download.html. Once the file is unzipped, you will see a directory named DISK1. It will look something like this: Step 3: Installing Oracle Launch Setup Launch the Oracle Installer by double‐clicking on the DISK1 directory, then clicking on setup.exe. You will then see the first screen of the Install Wizard: Complete the Wizard Click next, then accept the license agreement and click next again: Running Oracle Express After you have installed Oracle Express, the database is up and running. Because the database is a server, you do not need to run anything specifically to start your database. It is always running in the background. You will however need a client to access your database. Follow the instructions on subsequent pages in this document for installing the Oracle SQL Developer client. Installing the Oracle SQL Developer Client Step 1: Downloading Oracle SQL Developer Introduction With the database installed, you now need to download and use a client that is capable of connecting to the database. Modern clients are graphical and have many features to enhance productivity. If you already have a working copy of a SQL client you prefer, you do not need to complete this section. Simply setup a connection to your installed database and continue with the next section. Otherwise, it is recommended that you install the free Oracle SQL Developer client. This section contains step‐by‐step instructions for downloading and using Oracle SQL Developer. Download Oracle SQL Developer On Oracle.com, click on the Downloads/SQL Developer link: This will take you to a page of Oracle SQL Developer downloads for different platforms and different Java configurations. Before downloading, you must first accept the license agreement. Then click on “Download” link for the “Windows 64‐bit with JDK 8 included” option. This option includes Java and allows you to run the client by simply downloading and unzipping it, without a more complex installation process. However, if you have Windows 32‐bit, you will need to use the alternative “Windows 32‐bit/64‐bit” option, and ensure that you also have Java 8 installed. Since you have already logged into the OTN to download Oracle, clicking on the link here should immediately begin the file download. The file name will start with “sqldeveloper”, and then be followed by the version that is being downloaded. Save the file into the C:\Oracle directory you used to download the database, or into another directory of your choice that is easy for you to remember. Unzip SQL Developer Use the zipping program of your choice to unzip the file. There are many such tools, but if you do not have one already, you can download 7‐zip at http://www.7‐ zip.org/download.html. Once the file is unzipped, you will see a directory named “sqldeveloper”. It will look something like this: Step 2: Launching Oracle SQL Developer Launch Oracle SQL Developer To launch Oracle SQL Developer, simply navigate into the newly created “sqldeveloper” directory, and execute “sqldeveloper”. This standalone client does not require an installation before being used. Simply double‐click “sqldeveloper” to launch the program. You can return to this directory each time you wish to launch Oracle SQL Developer, or you can create a shortcut and place it on your desktop. Step 3: Connecting to Your Database Introduction In any SQL client, we must first establish a connection to the database before typing our SQL commands. A connection opens a communication pathway between the client and the database. In Oracle SQL Developer, the term “connection” is overloaded to indicate both inactive and active communication pathways, as well as all of the configuration information necessary to connect again and again to the same database. Connecting as the System User We first need to connect to the database as the “system” user, so that we can create our own user to complete the assignments. To start, right click on the Connections category, then select “New Connection…” from the context menu. Oracle SQL Developer will launch the New Connection dialog, which requests configuration information about this new connection. You will need to modify the following fields – Connection Name, Username, and Password. The Connection Name is just an identifier that you are giving your connection, so that you may identify it in the future. You can type anything you want that is memorable. We will use the name “system” because this connection is for the system user. The username is “system”. The password is whatever you chose your password to be during the Oracle installation process. button to ensure that you are able to connect. If you are able to Click the connect, it will state “Status: Success” in the lower left‐hand part of the dialog. If there is an issue, it will display the issue in the place of the success status. You will need to fix any issues issue before proceeding to connect. Now simply click the button to establish the connection. When you do so, Oracle SQL Developer will open up a blank SQL worksheet. In this SQL worksheet, you can type commands, execute them, and see the results. Congratulations! You have now successfully launched Oracle SQL Developer and established a connection to your database. Creating a Database User to Complete Your Assignments Step 1: Creating Your Own User Create the User The SYSTEM account that we are now logged in under is the initial DBA account, and it has so many privileges that it is risky for routine use. We will next create an ordinary user of the sort that we will use for this class. I will call this user “student”, but you can substitute any name. Choose a password that you can remember. Just type in the following: CREATE USER student IDENTIFIED BY studentpassword DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; This SQL command creates user “student” with password “studentpassword” and sets up two important defaults for student’s account. Note the semicolon at the end. Upper case is conventional for SQL keywords in the Oracle community. When you have typed this command, it will look like this: We now need to execute this command for it to take effect in the database. Click the icon to do so. When executed, the results of the command – the fact that the user is created – should display in a pane below the command. Give the User Basic Privileges Next we will give user “student” the privilege to connect to the database and the privilege to create database resources. Type and execute GRANT CONNECT, RESOURCE TO student; ALTER USER student QUOTA UNLIMITED ON USERS; Again, notice the semicolon at the end. Your screen should now look about like this: The new user is now created! Step 2: Connecting with Your New User Disconnect the System User We can use this new user to complete our assignments without concerning ourselves with accidentally damaging the database. So that we are sure not to use the “system” any longer, let us close the connection to “system” by right clicking on the “system” connection, and choosing “Disconnect” from the context menu. This will terminate the system connection, but will not close the SQL command window. You can close the existing SQL command window by clicking the icon. Connect as Your New User Next we will setup a new connection to your new user by following similar steps to setting up a connection to the system user. Right click the “Connections” category, choose “New Connection…” from the context menu, and fill in the appropriate information for that connection. Remember to put in the new username and password instead of the system username and pasword. After inputting the information, click the worksheet for the new user will open up. to connect. A SQL You are now connected to Oracle as an ordinary user, and you can complete your classwork, or even just experiment. If you want to create another user, just connect as “system”, and do what we did to create user “student”. Have fun! Congratulations! You are ready to work in Oracle!