If you’re here because you downloaded a copy of Oracle SQL Developer and now you need help connecting to a database, then you’re in the right place.
I’ll show you what you need to get up and going so you can finish your homework, teach yourself Oracle database, or get ready for that job interview. You’ll need about 30 minutes to set everything up…and about 5 years to become proficient with Oracle 😉
Oracle Database come with SQL Developer but SQL Developer doesn’t include a database
If you install Oracle database, it includes a copy of SQL Developer. If you’re running that copy of SQL Developer, please take a second to upgrade now, as it is WAY out of date.
But I’m here to talk to the folks that have downloaded SQL Developer and want to know what to do next.
You’ve got it running. You see this ‘Connection’ dialog, and…
You NEED a database
Installing SQL Developer does not give you a database. So you’re going to need to install Oracle and create a database, or connect to a database that is already up and running somewhere. Basically you need to know the following: where is this database, what’s it called, and what port is the listener running on?
These default settings CAN work, but ONLY if you have installed Oracle Database Express Edition (XE).
Localhost is a network alias for 127.0.0.1 which is an IP address that maps to the ‘local’ machine, or the machine you are reading this blog post on.
The listener is a service that runs on the server and handles connections for the databases on that machine. You can run a database without a listener and you can run a listener without a database, but you can’t connect to a database on a different server unless both that database and listener are up and running. Each listener ‘listens’ on one or more ports, you need to know the port number for each connection. The default port is 1521, but 1522 is often pretty common.
I know all of this sounds very complicated
Oracle is a very sophisticated piece of software. It’s not analogous to downloading a mobile phone app and and using it 10 seconds later. It’s not like installing Office/Access either – it requires services, environment setup, kernel tweaks, etc.
However.
Normally an administrator will setup and install Oracle, create the database, and configure the listener for everyone else to use. They’ll often also setup the connection details for everyone via a ‘TNSNAMES.ORA’ file. This file contains a list of database connection details for folks to browse – kind of like an Oracle database phoneboook.
If someone has given you a TNSNAMES.ORA file, or setup your machine to have access to a TNSNAMES file, then you can just switch to the ‘TNS’ connection type, and use the dropdown to select the database you want to connect to. Then you don’t have to worry about the server names, database names, and the port numbers.
It’s just me, and I need help!
No administrator, no database, no nothing. What do you do?
You have a few options:
- Buy a copy of Oracle and download, install, and create a database
- Download and install XE (FREE!)
- Download, import, and run our Developer Days Hands-on-Lab (FREE!)
If you’re a student (or anyone else) with little to no experience with Oracle, then I recommend the third option.
Oracle Technology Network Developer Day: Hands-on Database Application Development Lab
The OTN lab runs on a A Virtual Box image which contains:
- 11gR2 Enterprise Edition copy of Oracle
- a database and listener running for you to connect to
- lots of demo data for you to play with
- SQL Developer installed and ready to connect
- Some browser based labs you can step through to learn Oracle
You download the image, you download and install Virtual Box (also FREE!), then you IMPORT the image you previously downloaded.
You then ‘Start’ the image. It will boot a copy of Oracle Linux, start your database, and all that jazz. You can then start up and run SQL Developer inside the image OR you can connect to the database running on the image using the copy of SQL Developer you installed on your host machine.
Setup Port Forwarding to Make It Easy to Connect From Your Host
When you start the image, it will be assigned an IP address. Depending on what network adapter you select in the image preferences, you may get something that can get out to the internet from your image, something your host machine can see and connect to, or something that kind of just lives out there in a vacuum. You want to avoid the ‘vacuum’ option – unless you’re OK with running SQL Developer inside the Linux image.
Open the Virtual Box image properties and go to the Networking options. We’re going to setup port forwarding. This will tell your machine that anything that happens on port 1521 (the default Oracle Listener port), should just go to the image’s port 1521. So I can connect to ‘localhost’ and it will magically get transferred to the image that is running.
Now You Just Need a Username and Password
The default passwords on this image are all ‘oracle’ – so you can connect as SYS, HR, or whatever – just use ‘oracle’ as the password. The Linux passowrds are all ‘oracle’ too, so you can login as ‘root’ or as ‘oracle’ in the Linux desktop.
Connect!
If you’re connecting to someone else’s database, you need to ask the person that manages that environment to create for you an account. Don’t try to ‘guess’ or ‘figure out’ what the username and password is. Introduce yourself, explain your situation, and ask kindly for access.
This is your first test – can you connect?
I know it’s hard to get started with Oracle. There are however many things we offer to make this easier. You’ll need to do a bit of RTM first though. Once you know what’s required, you will be much more likely to succeed. Of course, if you need help, you know where to find me 🙂
166 Comments
Hi jeff,
I have downloaded sql developer and oracle 12c edition, but to unlock HR schema i can’t find my TNSNAME.ORA file. In home directory i searched in Oracle folder, but it is not having much files.
Please help me to solve this issue, I’m finding it very difficult.
Thank you.
you don’t need a tnsnames.ora file to connect to oracle 12c with sql developer
just define a connection, and give the server address (probably localhost if it’s running on your machine), the port and the Service name for your database (probably ORCL)
Thank you, i connected using system as username,
i wanted to unlock HR schema from oracle 12c., so i needed TNSNAMES.ora file so that i can have some sample tables installed with it already.
And is it possible to install both oracle 12c and 11g in my laptop, so that i can try to get connection from 11g.
you don’t need a tnsnames to unlock a user
after you login as system, run this
ALTER USER HR ACCOUNT UNLOCK
How is the user id and password passed on from SQL Developer to Oracle Database. Does it use any encryption mechanism or is it sending in plain text?
via JDBC or OCI-JDBC (thick client) – just like any other Oracle application, unless you’re using SSL, then it’s plain text
I want to practice SQL queries but there is no dummy Connection..pLease help me…Please send solution to my mail to [email protected]
Read the article again. There’s no database supplied with SQL Developer.
However, you can practice your SQL here
Are there any plans to allow Oracle SQL Live access connection via SQL Developer?
Thanks
LiveSQL? Not today.
Hello Jeff,
I was able to follow your notes till the second paragraph where you are saying that the default connections work only when we have database installed on our system, and you have provided the link to download the Oracle Database express edition (XE). I couldn’t find the mac version of the oracle database express edition. Could you please help me from here to install a database software and set up connection.
Thank you,
Have a good night.
XE isn’t available for OS X. You’ll need to run a linux or windows VM on your Mac. That’s why I talk about our VirtualBox appliance, which is what i’m running right now on my mac as I type this…
your a blessing. God bless u
Thanks so much nebs, that put a smile on my face 🙂
Kadence Buchanan writes articles for – In addition,
Kadence also writes articles for and. When purchasing a baby parrot, take a look at the size of its parents to know how big the bird will grow
so that you can know what size the cage needs to be when the bird is completely grown. This can become aggravated to
result to brain tumor, kidney failure and liver ailment.
Hello Jeff, I know this post is year old.. but I had been trying to connect SQL Developer to oracle on VM (centOS7) for 3 days now.. Tried everything until I tried this link How to properly set up 2 network interfaces in CentOS (running in VirtualBox)?
After changing one thing here and there it finally worked .. but what so I turned off the NAT configured adapter (with port forwarding as suggested here) and SQL Developer was still able to connecting. However my connection would only work with the host name set to the ip address set for the VM (with the host-only ip set as default gateway)..
I was wondering if I missed anything as I was hoping localhost would work.
N.B. localhost is set in listener.ora and tnsnames.ora though
Thanks
Hey Mr. Jeff i’m back 🙂 please i realy need help, i’m using SQL developer on my mac with the virtual box image and everything works correctly, but now i want to create and manage my own database, does the otn oracle developer comes with a tool like DBCA, if not how can create our database in this case ?
Why not create a new pluggable db inside the 12c Multitenant database already running in your image?
If you mean you want to create your own database on your Mac, you cannot as we don’t have Oracle Database for Mac available today. You’ll have to create a Windows or *NIX virtual environment and proceed as normal.
jeff please i need just a clarification i’m a newbie, if i use the virtual box image, and SQL developer on my host machine ( using the forwarding of course ), i will be able to manipulate any database on the SQL DEV. that is on my host machine without any modification on the vm ? , and the SQL queries written on the SQL DEV. ( that is on my host ) will be interpreted by the oracle database of the virtual machine ?
correct
thank u jeff i really appreciate it :), i’ve installed SQL developer on my macbook pro (yosemite os) but it won’t launch, i use jdk 7, does upgrading the JDK will solve the problem ?
nevermind, i was right, when i returned to check the download page of sql developer i found that the version that i’ve downloaded is only compatible with java 8 and above, so upgrading the jdk has solved the problem and sql devloper is now starting correctly, thank u again jeff your article is a life savior.
I’m happy it was assistance to you Zakaria, and I hope you won’t hesitate to ask for help if you need it getting started.
Hi Jeff,
I installed virtualbox with SQL Developer installed. My system password was expired and instead of using oracle as password I tried everything else and it locked me out. Using terminal when I log in as sysdba, I get invalid username / password at logon display.
you login as sys, not as sysdba
sqlplus sys as sysdba
password:
type in ‘oracle’
Thank you for your swift response. I had to re-install everything using dimitrisli’s instructions. Here’s the link:
https://dimitrisli.wordpress.com/2012/08/08/how-to-install-oracle-database-on-mac-os-any-version/
when I finally logged in to my reinstalled Oracle Day Developer’s SQL Developer; I got a pop up stating that my system password is good for 7 days only. I tried changing it by going to properties and clicking on resetting option but it didn’t work.
I tried connecting via terminal and entering the following commands
* SQLPLUS /nolog
* SQL> conn as sysoper
* Enter user-name: sys
* Enter password: oracle
Connected.
SQL> alter user system identified by *******
2
System responded with 2 after alter statement. I tested my connection and found out that system password didn’t change at all and it is still oracle.
Kindly accept apologies for my lack of knowledge. Still learning 🙂
Really appreciate you.
Regards,
Shawn —
once you’re connected, just run this
alter user hr identified by oracle – that will reset the password to ‘oracle’ and restart your password lifetime countdown
not sure why you had to re-install ‘everything’ though – the point of the virtualbox image is that we have everything installed and configured for you
Jeff,
I will try running it this evening and will report back. I think first time when I installed; I was not paying too much attention to details and I guess the password must have expired. On my follow up attempts; I may have locked myself out. Anyhow, trial and error is the name of the game. I appreciate your swift responses and will post upon my progress.
Jeff,
I ran alter statement and got 2 on the next line. Not sure if that was expected. Is there a way I can verify the timeline of password for a specific table?
addendum: …this excellent site notwithstanding.
Umm, OK?
what oracle is, is a completely overwrought, overblown pain in the @ss and curse upon the modern world.
hi, i have been trying to connect my Sql developer but i keep getting error 12505, what am i suppose to use as my username and password? is it the same as the one i use for sqlplus?
Hi,
I have a machine provided by my workplace which already had a sql developer installed when i got it. I want to use it at home for practice but i am unable to create a local connection. Everytime i try to create it using the method you have explained here, i get error which says “Network editor could not establish the connection”. What should i do?
hi ,
i have some doubt like SID,service name .what are major differ of both . why to use service name only preference by oracle .why not SID . when there are going to stop if the support of SID.i want to know the work flow also..
thank you..
SID is the name of your database, SERVICE is the name of the service the listener is using to serve connections to that SID
in a 12c multitenant world, you have to use SERVICE to connect to a PDB
how to fire query in sql developer without server
get a database
Hi there, this is a helpful post but HOW in the world do I get Oracle Database Express 11g on a Mac (OSX, Yosemite)? I am trying to learn SQL and I literally have no idea what I’m doing. I just downloaded SQLDeveloper through Oracle and am trying to just understand the very basics, but I can’t since I can’t connect to a database! Help?
use our virtualbox appliance – it’s a free virtualized Linux machine that has Oracle Database 12c + plus all the client software and self-teaching labs you need
I just installed Oracle VM VirtualBox and loaded the image. But when i try to start the DB i am getting this error:
Failed to open a session for the virtual machine Oracle DB Developer VM.
VT-x is disabled in the BIOS. (VERR_VMX_MSR_VMXON_DISABLED).
Result Code: E_FAIL (0x80004005)
Component: Console
Interface: IConsole {8ab7c520-2442-4b66-8d74-4ff1e195d2b6}
how do i fix that?
You need to go into your physical machine’s BIOS and enable hardware virtualization. Some mean person in your company didn’t turn it on for you.
Hello,
I have previously installed XE 11g v2 and then I got SQL Developer as well. The problem is that when I try to create a connection my listener doesn’t recognize the SID for XE(I used all default settings: localhost on port 1521 with SID xe). I also tried many other combinations but it’s just not working. I googled my issue and found lots of posts that recquired me to modify some files.ora. My problem is that I don’t know how to make the mods so that it would work in the end.
Could you please help me out?
P.S.: Checked if my listener is running at it is.
Hi, i followed the above procedure and installed everything.
When i am running sql developer and starting the data base its shows warning telling to change password, the present one will expire in 6 days.
How do i change the password.
I am not able to create new data bases in sql.
Thank you.
Hi Jeff
I am using oracle 12c and I have downloaded virtual box and set up port forwarding whenever trying to connect, it hangs then error message ‘Connection Reset’
are you able to connect inside the vm?
Hi,
I have a question.
I need to connect my VBA code to Oracle SQL Developer. However I am getting the error as “Provider could not be found”. Can someone let me know how I can find the provider details from the SQL Developer. ?”
I used the Provider as “ORAOLEDB”. Still I face the same issue
TIA
Thanks for the awesome steps Jeff! It worked for me
but i had to use settings as hostname: 127.0.0.1 instead of localhost
and orcl as “Service Name” instead of SID
Then it worked perfectly 🙂
Price MSRP: $5,700 USDModel Year: 2013, 2014 Score: 9.5/ 10
Hi!
When I’m trying to connect with the given parameters, it says that the account is locked. What should I do about it?
Thanks in advance!:)
Ask your DBA to unlock it.
If you are the dba, login as SYSTEM, go to the view > Dba panel. Go to security, users, your user. Right click – unlock.