Alright – we have Oracle set up and all configured, we blasted a container and a pluggable database on to it – it’s time to connect in and see what we have! Now there are a number of clients available, but it seems like SQL Developer tend to take the cake as it pertains to native Oracle clients. That said before we can connect to our database, more specifically our Pluggable Database we will need to create a service for it. In order to do this, we will dive into an application called SQLPlus as it’s very important you get to know it as well!
SQLPlus
SQLPlus comes builtin with the Oracle instance install and is a way to connect and manipulate our Oracle databases via the CLI (mind you there are GUI versions as well). Either way, the first thing you need to do in order to connect to a database with SQLPlus is to, well, connect – simple as that really. Below we log into our default database, the container database using the sysdba credentials
|
1 |
sql> sqlplus / as sysdba |
After connecting, running a special command called “SHOW con_name;” will show us exactly what database we are connected to
Since we did not specify any name of a pluggable database when we connected, by default we are connected to the root container database. There isn’t a really a whole lot we can do here – even running a query of “Select table_name from all_tables;” will return a crazy amount of tables – mostly system tables used to drive Oracle itself. So how do we get to our pluggable database – can you remember its name – if not no fear, simply run the following command to list out all pluggable databases on the system. We can see that we have a couple of pluggable databases – ignore the PDB$SEED, this is simply a template that is used for creating other PDBs. That said, CDB1PDB is the name of our PDB which we installed in the previous part of this series.
|
1 |
SQL> SELECT name, con_id FROM v$pdbs; |
Make note of the name of your PDB because in order for us to connect to it we will need to create our service which will enable us to connect to our PDB. To do this, exit SQLPlus (exit) and navigate to the $ORACLE_HOME/network/admin/ directory. Here we can create a new service by modifying the tnsnames.ora file. Edit the file by appending the following information, in our case using CDB1PDB as the database name.
|
1 2 3 4 5 6 7 8 |
CDB1P1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mwp-oracle.rubrik.us)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb1pdb) ) ) |
Save the file and head back into SQLPlus. This time we will just launch it with the /nolog flag so we can specify our connection as shown below… Don’t forget to use your password 🙂
There we are now able to connect to CDB1PDB from SQLPlus – meaning the service is all set up. But what about SQL Developer – oh yeah, let’s try that…
SQL Developer
SQL Developer is an SSMS like client which we can use to build out tables and manipulate our databases with queries, etc. It’s completely free, so go download it from here. After installing its launched by simply clicking the executable. The first thing we will need to do is create a new connection by clicking the green plus sign under the connections window. Fill in the proper details here – Connection Name can really be anything – for a username we will use ‘sys as sysoper’ so we don’t have to deal with all of those system tables 🙂 – Fill in your hostname and port, and select Service Name, entering the service we just configured in the previous step. Click ‘Test’ to test authentication and then click ‘Connect’
Once connected we can expand our connection on the left-hand navigational menu to view things like tables, views, indexes, etc – all of that database goodness we are used to. Right-clicking gives us the ability to create tables and views and whatnot – now things are starting to look familiar to me 🙂
So there we go – we have successfully installed Oracle Linux, completed the prerequisites, installed Oracle Database on top of that, created a database and connected to it! We could call it here and begin building out our database by creating new tables and views, but who’s got the time for that? Instead, let’s take this one step further and install the HR schema into our CDB1PDB database – this will give us some example tables and data to play with! That said, we will leave that for Part 6.
