Let me first start by saying, I am not an Oracle expert.  I learn what I need to in order to get things done.  I don’t go digging into learning Oracle proactively, frankly because I am not a huge fan of it, I still prefer DB2, but enough of that 🙂

This blog simply talks about the quick items you need to know to about creating a database, connecting to and testing the datababase using SQLPlus, user IDs/password, roles etc etc.

So first, to create the database, I use the Database Configuration Assistant to create the database.  There are some very important things you need to remember, the SID and the password.  For the most part, all other settings you see in the UI can be defaulted, although you may want to set the character set to UTF-8 as you go thru the screens.  So write down the SID (choice of your name) and the password.

Let’s explain some things a bit here with Oracle.  When you create a database, you typically will create a user and assign your tables (or schema) to that user.  It takes a bit of getting used to this approach if you are a DB2 or mySQL person.  The best way to create a user and assign that user to the database is to use SQL Developer, a free download UI program from Oracle, strongly recommend this.

Once that is done, you’ll want to make sure you can connect to the SID (or the database).  So here’s your intro into SQLPlus.

  1. Run SQLPlus from a cmd prompt using this command: sqlplus /nolog
  2. Run this: connect <userid/schemaname>/<password>@dbname-aka-sid (optionally include as sysdba on the end of the command if you need system access).  Alternatively, you can run this command connect sys/<password>@<dbname> as sysdba to connect as the root user of the database.

Now if this works, you’ll see a message indicating the connection was successful (ie connected message shows).

A note about roles.

Oracle ships some default roles, and the ones you will probably be most interested in is the sys and sysdba user.  I tend to think of sys as the root user of the database and sysdba as the role of the database.