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
I am getting a crash any time i try to open up Oracle SQL developer on my Mac Mid-2010. Can you help me out at all? Or point me in the right direction? Here is a piece of the crash thread:
Crashed Thread: 0 AppKit Thread Dispatch queue: com.apple.main-thread
Exception Type: EXC_BREAKPOINT (SIGTRAP)
Exception Codes: 0x0000000000000002, 0x0000000000000000
Dyld Error Message:
Symbol not found: _CCRandomGenerateBytes
Referenced from: /Library/Java/JavaVirtualMachines/jdk1.8.0_241.jdk/Contents/Home/jre/lib/libjfxwebkit.dylib
Expected in: /usr/lib/libSystem.B.dylib
Thanks…!
Hello Jeff,
Currently, we are using Toad but Quest starting being pain and charging us a lots of money so plan is dropping Toad . meanwhile, I am learning Oracle SQL developer. I am dba and was able to connect to one of my current databases in sql developer but when I try to connect to another one I do not see that database in the drop down Network Alias. Where is tnsnames location in sql developer so I can set it up. My tnsnames.ora file is under current path located in $ORACLE_HOME/network/admin. I have almost 30 databases there. The Network Alias only recognizes couple. Can you kindly advise.
I am interested learning about migration of Oracle objects from one schema to another, let say from DEVL to PROD and mostly Oracle securities in sql developer. Any tutorial on that?
Best
Sam
sql dev looks in the same places that sqlplus will – it should def see it in your $ORACLE_HOME/network/admin directory. But, if it’s not, there’s a preference in SQLDev where you can manually specify the directory to look in.
Thanks, Jeff. Virtua Box installed (and upgraded). The .ova file downloaded (finally!) and imported. Port 1521 forwarded! Works like a champ!
A tip: Log into oracle.com and find the developer days download from there. If you try to download it from the blog post, the first step will be to log into oracle.com and your download will freeze on the log in page.
I installed sql developer and oracle 11g http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html and ran the setup file. the prompt asked for a password setup and I set it . but now how do i connect?
Start SQLDev. Create a connection. Enter the connection properties. You’ll need to know where you put the database, what it’s called (XE), the listener port (1521), and your username and password.
You should prob connect with user SYSTEM which will have the pwd you assigned in the setup.
sir iam not getting connected with sql developer am using oracle 11g
ok?
i dont know ,,what is connection name , and user name and password,
and its giving error as network adaptor could not connect. please help me
did you read this post?
do you have a database?
Hi Jeff,
Thanks for writing this article, will try it soon 🙂
sir it is neccesary to download oracle db before downloading sql developer ..
Read this post. It’s only necessary if you don’t already have a database.
Thank you for this tutorial!
It helped me a lot, setting up my Oracle DB.
Hi Smith,
I have successfully imported an xml file into sqldeveloper which contains various databases. Now if i click to open any database it throws back me an error. i.e Vendor error (017176).
I have not installed any Oracle versions.
Kindly suggest way to proceed.
what sort of database do you have in your ‘xml’ file?
How do I solve this problem with my Oracle VM an SQL developer: Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
?
You’re trying to connect to a database that the server doesn’t know about.
Make sure your SID or service in the connection properties is correct.
Jeff,
I followed the instructions as stated as was still unable to connect to the Developer Days Database. I downloaded SQL Developer to get me experience with Oracle as well as learning SQL.
I have been trying for several days but have not been able to successfully connect to a database to this point. I have a Mac, I am unsure if this would cause me any trouble. Part of the difficulty has come from finding resources that will instruct me on how to access the sample databases, whether they need to be downloaded or I need to enter a password for them.
Could you please provide me with any additional information that you feel may be more descriptive in downloading and connecting to a sample database?
Thank you.
What happens when you try to connect?
The image has the sample schemas already installed.
I use this image on a Mac, runs the same as on Windows.
Jeff
I’ve used SQL Developer before sometime ago.
Now, I’ve a Windows 10 client and I’m trying to connect after installing and I get this error below:
An error was encountered performing the requested operation:
IO Error: The Network Adapter could not establish the connection
Vendor code 17002
Apparently, it looks like there is a connection issue with my laptop.
I looked up the Windows 10 services, to see if I need to bring up the services by going to Control Panel > Admin Tools > Services.
Here I dont see any services related to Oracle such as Oracle.
Can you let me knwo what I need to do to be able to connect?
Did you see the part in this story where I said that SQL Developer doesn’t include a database, and that you’ll need to provide your own? I also included a few options for making that happen with varying degrees of effort…
Hi Jeff,
I downloaded sql developer on my mac. I downloaded virtual box as well and installed windows server 2012. I downloaded the databases from oracle website and installed them on the virtual box on windows server 2012. When i open sql developer and put in all the required information, i use the ip of the virtual box but i am still receiving the error “the network adapter cannot establish a connection” I NEED HELP PLEASE!! ive been at it for about 2 hours, went through so many videos still no luck.
Why did you build the windows vm? Our Linux vm already has the db and SQLDev configured to work out of the box. Just run that.
So what you’re saying is to download the linux vm from the oracle website?
So what you’re saying is to download the linux vm from the oracle website?
Oh, and i was on the notion i needed the windows server vm to run the oracle db since i’m on mac
I’m suggesting you read the post you commented on, in particular this bit:
It’s a VM that already has linux and Oracle 12c going on it. You just download it, import it to VirtualBox, and hit ‘start’ – then you’re up and running.
Here’s the link to go get it.
You’re a life saver. Will try it out and give you feedback. I appreciate your time.
No worries..getting started can be intimidating. We got your back here.
Hi
I have OralceSqlDeveloper 4.0.3 version, and I am not able to create a connection due to I/O adapter network error. and I am not able to find out the tnsname.ora file
please help me out
You don’t have a tnsnames.ora file unless you make one or unless someone gave you one? Are either of these true? If so, point to the directory where that file is in your preferences, on the database page.
Otherwise I/O adapter network errors generally indicate we can’t reach the server/port you have defined in your db connection.
Hi
I get the following error:
VT-x/AMD-V hardware acceleration is not available on your system. Your 64-bit guest will fail to detect a 64-bit CPU and will not be able to boot.
What have I done wrong?
Thanks
Tash
nothing, but whoever imaged your machine didn’t enable that in your BIOS, you can fix it
Thanks Jeff.
That did it. I managed to install and run virtual box and I’ve now connected to the database and run some queries from sql developer.
Hi,
I installed SQL developer recently. when i click on create new connection, it shows the gallery and doesnt give me the actual new connection popup.Whats the problem?
I tried connecting my Oracle 11.2.0.1.0 database instance using sys as sysdba role selected but failed on sqldeveloper with ORA-01017 However i was able to connect to sys user using sqlplus terminal using same password.
Using command client console using sys user, I tried to create another user with granted CONNECT role on the same database and this user is able to connect successfully using sqldeveloper.
Earlier, I had been using Oracle XE instance on SQLDeveloper and was successful to connect to XE instance using sys user
I would like to know what could be a reason for such a behavior on SQLDeveloper.
i had install the sql developer but when i try to click on button’test’to create connexion,a msg appears:Error I/O:the network adapter could not establish the connection.I try to start oracle listener on windows tasks,but i didn’t find any service refers to oracle.
There’s no database with SQL Developer. You have to have your own database. If you didn’t install the server component and then create a database, you don’t have a database. Hence this post.
Jeff, I’ve installed Oracle Database in a virtual box Windows Server 2012.
I’ve installed the SqlDeveloper there too.
I can connect with SqlDeveloper using localhost as hostname. But I wanted to put the name or IP of the windows server. How could i do that?
I’ve changed the tnsnames.ora and listener.ora. I’ve changed the localhost to the IP of the Win server. But still can use this on SqlDev.
Any idea?
I installed an SQL developer, but I don’t have a database to connect to. I really only want to practice my skills. Is there any databases out there I can connect to in order to just practice query writing?
So if you don’t want to grab the virtual machine we make available, you do have an alternative.
Oracle Live SQL – you won’t be using SQL Developer, but it’s great for learning and playing with SQL.
I downloaded the SQL developer for my MAC, and i followed through with the instructions for the connections etc but when i tested it out it did not work. Help!!
What did you try? What errors did you encounter?
Status : Failure – Test failed: IO Error: The Network Adapter could not establish the connection
Thanks Jeff
So that’s your error message. Now I need to know your connection properties and I need to know about your database. Is this a db you installed, is it from a free vm, is it a work thing?
After downloading SQL developer and running it on my MAC, i clicked for new connection
Connection name: hr
Username: hr
Password: oracle
SID: orcl
clicked on test connection and i got the error i told you about.
And your server? Sqldev doesn’t include a database. You can’t use the tool to do database stuff without a database.
I just downloader the Oracle DB developer VM to try on my VirtualBox. Hope that works