Is there an easy to follow guide for installing and successfully connecting to a local oracle database with SQL Developer for Windows 10? I also cannot connect to my local Oracle database after reboot and don't know how to create another user for my local database.
In this guide, I will show you how to install a working local Oracle database with SQL Developer on your Windows machine. We need three applications: Java SE Development Kit (with Oracle JDK 11), Oracle Database Express Edition (XE) 18c, and SQL Developer.
First, download and install the Java SE Development Kit with Oracle JDK 11 from this link: https://www.oracle.com/java/technologies/javase-jdk11-downloads.html
Scroll down and select "Windows x64 Installer" and download the .exe file. You will need an Oracle account to begin this download, so sign up for an account, verify it with your email, and then login.
Install by running the .exe file and do not change the installation path directory.
Now we need to set the JAVA_HOME and PATH environment variables on Windows to reflect the installation location.This step is critical before installing Oracle Database XE and SQL Developer, do not skip it.
Press the Windows start button and enter "environment" and click "Edit the system environment variables."
In the "System Properties" screen, click "Environment Variables". Make sure to edit the "System variables" on the lower part of the window and not "User variables" at the top.
Click "New..." and under "Variable name:" enter JAVA_HOME
For "Variable value:" enter the path of the main Java JDK installation, which will look like the following (the version number may differ):
C:\Program Files\Java\jdk-11.0.11
Press OK to save the JAVA_HOME system environment variable, now scroll down in the system variables and find
the "Path" variable. Click on it then select "Edit...".
Click "New" and enter the following text (with different version number if applicable):
C:\Program Files\Java\jdk-11.0.11\bin
Press enter then use "Move Up" to move the variable to the top of the list. Click "OK" and you are done with this step.
Second, download and install Oracle Database Express Edition (XE) 18c from this link: https://www.oracle.com/database/technologies/xe-downloads.html
Click on the link for the Windows x64 to download the OracleXE184_Win64.zip file. Once you can begin downloading, it may take some time as the file is about 2 gigabytes in size.
Extract the completed zip archive into a folder in your Downloads folder. Open the unzipped directory then scroll down and run "setup.exe".
Do not change the installation path in the installer, it will only make things more complicated.
During installation, enter a password for the system account and write it down somewhere you can remember it. Do not include any special characters and do not start the password with a number (as per Oracle guidelines). Also make sure you allow network access to the Java binary on private networks during installation.
This installer may take up to 15 to 20 minutes to complete on some systems so be prepared to wait a bit. Once the process is complete, we will add the ORACLE_HOME environment variable to the system.
Under System variables in the Windows Environment Variable window, Click "New..." and under "Variable name:" enter ORACLE_HOME
For "Variable value:" enter the path of the main Oracle database installation, which will be a variant of the following string:
C:\app\nicho\product\18.0.0\dbhomeXE
Obviously your username will be different after the \app directory, although you may not know what the abbreviated form will be. Make sure to verify this path by navigating to the dbhomeXE folder on your C:\ drive.
Press "OK" to save the ORACLE_HOME system environment variable, now scroll down in the system variables and find the "Path" variable.
Click on it then select "Edit...". Check to see if the following path (your username will differ from mine) is already in the Path variable:
C:\app\nicho\product\18.0.0\dbhomeXE\bin
If not, click "New" and enter the preceding text path with your username substituted.
Press enter if you had to add the path, then use "Move Up" and "Move Down" to move the variable to the entry near the top, right before the JDK path, which looks like the following:
C:\Program Files\Java\jdk-11.0.11\bin
Click "OK" in the window and "OK" again in the parent window and you are done with this step.
Third, download SQL Developer 20 from this link: https://www.oracle.com/tools/downloads/sqldev-downloads.html
DO NOT download the version that comes with JDK 8, we already have JDK 11 installed. Select "Windows 32-bit/64-bit" which is a 432 MB .zip file and wait for the download to finish.
Extract the folder and move (cut then paste) the inner folder titled
"sqldeveloper" to your programs folder at C:\Program Files
Now we want to add a shortcut to the executable to the Windows start menu for easy access to the program.
Right click sqldeveloper.exe and select "Create shortcut", then rename the shortcut to "SQL Developer".
Then move (cut and paste) the shortcut to the following directory:
%appdata%\Microsoft\Windows\Start Menu\Programs
For the next few steps, it is necessary to first restart your computer.
Now we want to ensure the Oracle local listener is functioning and fix the listener location so that we can connect to the local database through SQLDeveloper.
Open the Windows Task Manager and click the tab on the far right named "Services" Ensure that the service "OracleServiceXE" is running (if so, it will have a PID).
It should start automatically, but if not, right click on it and select "Start".
Then start the service "OracleOraDB19Home1TNSListener" manually by right clicking it and selecting "Start".
ATTENTION: Keep in mind that EVERY time you restart your computer, you must manually start the listener service!
Now to fix the listener (This is a one-time operation, you don't have to do this every restart.) open a Windows command prompt as Administrator (type cmd in the start menu and click "Run as administrator")
Now enter the following commands exactly:
sqlplus /nolog
conn system
alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope = both;
alter system register;
exit
lsnrctl status and you should see the following lines in the output:Service "XE" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
sqlplus sys as sysdba
alter session set "_ORACLE_SCRIPT"=true;
create user usernamehere identified by passwordhere;
grant all privileges to usernamehere;
exit;
Hostname (localhost), Port (1521), and SID (xe) the default values to connect to your local database.