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
Hey when I test connection I get message test failed ORA28000 the account i s locked what should I do??? please help
Ask a DBA or someone else with the privs to unlock the account for you.
ALTER USER myuser ACCOUNT UNLOCK;
Or use the DBA panel to right click on the user and do the same thing.
Still ridiculous in 2014 to connect from SQL Developer tool to Oracle 11g XE. Maybe that’s why I’m a Senior MS SQL Server DBA and UNIX guy and not an Oracle DBA.
Saying Oracle is a sophisticated piece of software is an euphemism for confusing and clumsy tools … get real – this the XXI century.
Did you have a question?
The question was “I have Oracle SQL Developer Installed, Now What?” which coincidentally is the subject of this article of yours.
However my post was more an outpouring because I do not understand at all (and I suspect I’m not alone here…) that Oracle does a big effort to NOT to simplify things for the ones who just want to try the technology. This is considered to be on purpose.
Thanks for your reply / kind attention.
I sure hope not! Our team spends a lot of time, and it’s one of our core missions, to lower the cost of entry to the database – both monetarily and technology/effort-wise.
I think the best thing for a new person to try is our VirtualBox appliance. It’s basically, download and go.
Otherwise, installing the database software, creating a database, and configuring connectivity is indeed something that requires a bit of experience to be able to do w/o effort or lots of reading the docs.
I installed oracle database 11g express edition, and also sql developer, and now I can’t make a connection in sql, even though I tried a lot of things I saq on the internet. The test just won’t success. Can you tell me how to make that possible? Thank You
check this out
read it, tell me how you’re defining your connection, and then what error messages you are getting back
also what image are we talking about here…….???
Hi, i’m a student and want to practice sql….. I have downloaded a file from oracle site ‘sql developer’ of nearly 220 mb. but given the options on this very page cannot help me to have a new connection. I would appreciate it if you give me some more options or in very detail explain how to establish a new connection. i cannot find ‘TNSNAMES.ORA’ file in the folder and don’t know where else to find it. Please do help. Thanks in advance.
You don’t need a TNSNAMES.ORA to connect. Just use the direct connect.
If you want a TNSNAMES.ORA file, you’ll need to create one using the Network Configuration Assistant – which is part of the full Oracle Client.
But what would be the user name and password…..
connection : dev
username: hr
password: oracle
connection: basic as opposed to tns
I get following error while testing with above credentials:
IO Error: The network adapter could not make establish the connection….
Any help here…. Please…….:)
Did you read the very last parts of the post? There are instructions on how to setup port forwarding and how to connect.
I am new to Oracle and keep having error INS-20802…. Oracle Net Configuration Assistant failed.
I am using Windows 8.1 , Oracle 12c
Does anyone know what is causing this?
Thanks
What are you trying to do exactly?
Hi,
I’m just trying to add a new connection using “HR” as username and “oracle” as password but I’m getting an error:
Status: Failure -Test failed: ORA-01017: invalid username/password; logon denied
Could you help me please?
Make sure you’re logging into the pdb, not the cdb.
Thank you for answering my question.
Where can I choose that? Should I type PDB1 as service name?
I’m assuming you’re running the latest developer day image? If so, yes, you’ll need to connect to the PDB1 service, not the ORCL one. The connection properties are where you want to go. And read this
That’s great! Thank you very much!
Hi – great post 🙂 It might be worth noting though for more recent OTN VMs it appears OEL (at least 6.5) has a firewall inbuilt & blocking the DB port. I was pulling what little hair I have left out trying to get my host SQLDEV to connect to the VM DB. I finally found /system/administration/firewall > Disable that sucker. Cheers
thank you very much for ur time.
i wish u a good day!
i installed oracle linux 11g on virtual box. i also installed SQL developer, but when i try to create a new connection with credential:
usrnm:hr
pass:oracle
it says: io exception:the network adapter could not establish the connection.
could you please help me with this?
thank you in advance
username and passwords don’t matter if we can’t find the listener – what did you put for hostname, port, and sid/service?
hostname:localhost
port:1521
sid:xe
thnx
this will ONLY work IF
Does this describe your local environment?
i have installed: Oracle Database 11g Release 2 (11.2.0.1) Software (64-bit) – For unsupported installation.
how to check the listener on port 1521?
sorry , i’m new at oracle….: (
after you install oracle, you have to create a database. you can use ‘dbca’ – database creation assistant to walk you through this.
However, if you’re new to Oracle, I’d advise you to just use our developer day virtualbox appliance – everything is already setup and you can just concentrate on learning the database basics instead of starting with installation/creation/configuration – which is normally left to seasoned DBAs
This is on Amazon, an RDS database instance, so I don’t have access to the file system. I can’t use the ‘hr’ user, nor the ‘SYS’ user. I think I’m pretty much limited to the one master account user.
It’s a really easy way to bring up an Oracle instance and works fine connecting via Squirrel or SQL Developer. It’s just limited in some ways, one being that there don’t seem to be any sample tables.
As a followup to my previous post, I was able to work through the SQL developer tutorial without the HR database, but it would still be nice to have some sample data. The HR dataset would be good. I’ll also try loading the other data you’ve suggested.
thanks
There’s a sample schemas .sql script there in $ORACLE_HOME somewhere…check the DOCs on ‘sample schema’ and ‘HR’
I’m learning PL/SQL using SQL Developer and the Oracle tutorial. It assumes you have the HR database but I’m using an Oracle 11g instance on AWS so I don’t have the HR database. Where can I download it?
thanks
Jeff,
I had to download the virtual box. But, still, when I try to connect I get an error: “status: failure – test failed: ORA -28000: The account is locked” with SID: XE, that is the error I get, and tried it with orcl and got a different error, but still can not connect the developer. I have an pc running windows 8.1, connecting through WIFI.
What next?
Thanks,
Mike
Login as system/oracle then unlock the account you’re trying to use.
I think I might have not set up the virtual box correctly.
Is there directions anywhere on the Oracle site where it walks you through the process step-by-step?
Thanks.
I went into great detail on that here actually…try this
A bit of a long shot but maybe you can help with your knowledge of Virtualbox and tns. When I create two virtual machines in on my Macbook pro I can’t tnsping between them it just keeps coming up with ORA-12154: TNS: could not resolve… no matter which adapter I use. I can ssh, scp and ping between them but no tnsping. I know it is not my tnsnames.ora or listener.ora as I can copy them and place them into two VM’s created in WMWare Fusion and load the databases from a data pump export and it works fine in Fusion. The reason however that I want to use Virtualbox is that for RAC labs Virtualbox makes it so much easier with the ability to create shared drives. Anyway just wondered if you had any ideas regarding the internal etc interfaces. Mac laptops do have an issue with bridged adapters on Mac laptops also and it’s been found you need to use NAT.
Great article by the way!
>>I can ssh, scp and ping
Ok, well that tells me you seem to have gotten over the largest hurdle. If the two VM’s can ‘see each other’, then I’m thinking there’s an issue with the listener or sqlnet setups? Are you using IP addresses or machine names?
The real RAC Attack ninjas out there are Yury and Bjorn. I’d ask one of them for insight.
Hi Jeff, well I thought it could be the listener.ora or tnsnames.ora but I’m 100% sure it isn’t and I’ve added them at the bottom for you to see. As a confirmation as it was driving me crazy I took a data pump export of both databases and literally copied the listener.ora and tnsnames.ora files and pasted them into a vmware fusion setup I had configured and lsnrctl status was totally happy and I could tnsping no problem. Normally I configure the /etc/hosts file or if I am setting up a RAC lab I use dnsmasq, a great little dns server as the scan addresses don’t seem to work with hosts file entries. I am an Oracle fanatic and love the way virtualbox makes shared storage so easy for OCR and the voting disk. On a Macpower book etc using wireless card however there seems to be a bit of an adaptor issue. Firstly a well blogged issue is having to use NAT instead of Bridged as it wont accept bridged. So as it doesn’t like that I used an internal adaptor that it loves for everything, ping, ssh, scp and grid infrastructure for scan, private and priv. The only thing it won’t play with is tns. As I mentioned to I even verified my settings were right by doing a data pump export of both DB’s and copied my tnsnames.ora and listener.ora files and it worked perfectly in VMWare Fusion. So in summary:
Virutalbox: Love to use and is miles ahead of VMWare Fusion for shared disk allocation for RAC. Can’t use for Bridged connections or for internal adaptors (tns only).
WMWare Fusion: All adaptors work great for all protocols but heck is it a nightmare for creating shared disks and even when you do you get an annoying message stating Fusion doesn’t support clustering every time you restart VM.
Any help really appreciated as I love Virtualbox and would love to be able to practice RAC labs more using internal redundancy etc for my grid infrastructure configurations etc.
db1 host listener
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = fully1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = fully1)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
db1 tnsnames
FULLY2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fully2)
)
)
FULLY1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fully1)
)
)
db2 host listener
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = fully2)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = fully2)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
db2 tnsnames
FULLY2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fully2)
)
)
FULLY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fully1)
)
)
Did you check the firewalls on your two machines? Is port 1521 open? See my post from earlier this week.
Hi Jeff, yes I did it’s one of those things with linux I always go straight into /etc/selinux/config and set it to disabled then use chkconfig just encase to keep it off. Also I set up labs a lot and I installed from scratch three times trying it with Virtualbox and the same each time. Repetition is the key I find to learning and I’m on my fifth data guard configuration from scratch on VMWare Fusion and I haven’t had a problem once. Also tried it a couple of times on Solaris in Virtualbox and the same thing happened. It’s a real puzzler and I’d love to get it fixed so I can practice RAC with Virtualbox’s easy to setup external sharable virtual drives. It’s a real puzzler.
David,
can you please try some things?
– I assume your SAN DNS-name is “SCAN” (if it is not, please tell us)
* on all nodes of interest
1) nslookup db1
2) nslookup db2
3) nslookup SCAN
4) telnet db1 1521
5) telnet db2 1521
6) telnet SCAN 1521
– by these tests you sould know if you can resolve the names (either DNS or /etc/hosts) [test 1-3] and connect (on a network layer) to those [test 4-6].
With those results we can hunt the problem better.
Martin
Hi Jeff, Martin, thanks for all your effort. After many many tests I have found that like myself others have experienced the same problem using Virtualbox on a mac. No matter what even with completely disabling the firewall all protocols work, scp, ssh, telnet, ping, tcp, dns etc but tnsping simply wont connect. At the end of the day if you get it to work everytime by scp-ing over tnsnames.ora and listener.ora to another setup and everything else is enabled then it severely limits what it can do. Anyway bit the bullet and bought a great little N54L HP server and now do all my RAC, Data Guard etc labs on that using vsphere to manage it all.
Thanks again.
Dave
Hi Jeff, I found your site after I had a good friend at Oracle download the Mac OS X version and send it to me – why ? Because I couldn’t successfully register on the site to download it myself 🙁
I really want to use SQL Developer, but I’m on a MacBook Pro (running Mavericks) and designing databases in MS SQL Server (but we don’t have direct access to the server).
I’m hoping I can create the data model offline, generate the DDL then ship it off to be executed on the server – is this possible ?
Anyway, awesome tool from what I’ve seen so far.
Cheers 🙂
Where did you run into problems getting an Oracle SSO?
You can definitely do your designs and have the DDL generated (for SQL Server 2008, 2005, and 2000.)
If you get stuck, let us know!
The main problem is that I’ve never received an email from Oracle. I tried registering, I tried “forgot password”, both say “an email will be sent” but nothing arrives.
Is it possible it’s getting trapped in a corporate spam system like Postini? If not, let me know if you used the same email address as I see here for your blog comments so I can see what is happening on our side.
Thanks a lot Jeff! Now I can use the VM to do my first steps into oracle, regards.
Hey Jeff !
I installed the VN on my computer and succesfully create my first Database connection by following the steps you explain in this post (Thanks for that !). The Database “HR at 11gR2” already contain some tables like “Countries”, “Departments” and so on. I could train a little bit and create my first tables, but yet i want to create a new Database. What did i have to do ? Create a new Database connection ? I tried it already, but i don t know anything about what connection name, username, password i have to use and which hostname, port and SID i have to enter… I’m really new and a little bit lost in this stuff…
Creating a new database – the easiest way would be to run DBCA – database creation assistant – from the VM. It will ask you some questions, and when it’s done, you’ll have your 2nd database on the image.
If you’re just getting started, I would recommend waiting a bit to go and create your own database. Use the one on the image provided to do all of your experimentation 🙂
Hi Jeff,
I have been trying to connect my SQLdevelper from HOST OS (win 8) connect my Oracle Linux VM (in VirtualBox) where Oracle 11g is installed, but everything fails :'( . I can connect to the oracle db in the guest OS.
My VM has two adapters, one configured as NAT and the other as HOST-Only.
Can you please guide me how can I connect my Oracle DB in the VM with SQLDeveloper?
If your host can see the IP address of the vm, then you’re golden. Getting this to work reliably on Virtual Box used to be a big PITA for me – until I learned a trick: port forwarding. I’ve setup port 1521 on my host to auto-forward to port 1521 on the image via Virtual Box.
So my connections are to localhost:1521 and Virtual Box auto-forwards those to the listener port on the REL image running.
Thanks for your reply,
I just opened the port 1521 inside my VM and it got connected… 😀
Help, please. I want to install the latest version of Oracle XE under some version of Linux running in VirtualBox on a Mac (OS X 10.8.5). I’ve spent the last several days trying to do this with Ubuntu, and I’ve been tearing my hair out. I don’t know much about Linux, and I don’t want to know much about it. I just want to get the latest Oracle XE running on my Mac with the ability to interact with it using the Mac version of SQLDeveloper. (I do want to have the Linux desktop occupy the entire screen of my iMac, and I want Oracle to start automatically when I start the Linux OS.) Which available Linux VirtualBox VM do you recommend? If you don’t recommend any, which version of Linux should I install in a VM which will result in the easiest installation of Oracle XE? I will really, really appreciate any advice you can provide.
Never mind. All is working under Ubuntu now. 🙂 Thank you for the great information on this site!
I have the virtual machine running. When I start SQLDeveloper and try to connect with the parameters shown above, I get a message saying that the TNS listener doesn’t know of the SID “ord”. Help, please.
Jim, you’re so close!
It’s o-r-c-l not o-r-d
Jeff
Hey..I installed Oracle VM Virtual box manager and opened the Oracle developer days image. The image started running and asked me username and password for Enterprise Linux 5. I don’t know this combination of username and password. I tried using my windows credentials. It didn’t work. May I know what should I use?
Thanks 🙂
All passwords on the image are ‘oracle’ – you can login as user oracle or as user root. I recommend staying away from root user 🙂
I was about to be really happy, you answered my main question with relative ease. Then I realized that Oracle Express does not work on a 64bit version of windows 7. 🙁
You can go the Virtual Machine route, otherwise, yes, no can do. The next version will probably be based off 12c and will be x64 only. But I can’t tell you how far away that is from happening.
i owe a macbook pro and i have sql developer installed on it.
you know i have no database to connect to ..i read some of your articles but i guess i’m failed i getting any connection to any database nor i can connect.!
one more thing can we connect to a database which is on the other system in my case (its desktop) and a macbook . both are in same network . is this somehow possible?
You can connect to any system for which you have database/network access to.
If you want to build your own, I suggest using our Developer Days Hands On Lab. It’s a VirtualBox image you can run on your MBP.
When running Oracle VM VirtualBox Manager and Oracle Developer Days my machine is almost frozen. Every other second hitting 100%. I have lots of diskspace and am running on a Intel Celeron C900
Is there any point trying to open SQL Developer 3.2 with no performance?
John, how much physical memory do you have on your machine? I have 8GB of RAM on my machine and I can comfortably run that VM and 3-4 instances of SQL Developer.
Are you running SQL Developer inside the VM, or from your host OS?