Introduction to Oracle Database Environment Lesson 1
For the purposes of this course, you have access to Oracle version 8.05 available via telnet to gauss.math.luc.edu. If you have a PC version of Oracle installed in your home computer or have access to an Oracle server then there is no need for you to access our server. How to Connect to the Oracle server in the CS department at Loyola: You run telnet gauss.math.luc.edu from your PC (or Unix machine). Gauss is a Unix machine, actually, a Sun Solaris machine. Type your userid and password to log in to Gauss. These two pieces of information were sent to you by email a couple of weeks ago. After you log in, do the following: 1. cp /home/chandra/.cshrc . This will copy my C shell login script to your local directory. Make sure that you are in the toplevel home directory and not within a subdirectory when you type this copy command. Please do not ignore the dot (.) at the end of the copy command. 2. Then type /usr/local/bin/tcsh. This will change your shell environment from whatever you had when logged in to a new shell called TC shell. Don’t worry; you don’t have to know anything about TC shell beyond what I tell you to do. 3. Type sqlplus. This will ask you for an Oracle userid and password. These are not the same as your operating system userid and password. I sent these to you also by email. Type them in and see if you are connected to Oracle 8. You should see a screen that says something like “Connected to Oracle 8 with Partitioning and Objects Options” or something close to that. 4. That’s it. Now you can create data objects, run SQL queries, save the results of the query to a file, and so on within the SQL environment. 5. Each time you telnet to gauss.math.luc.edu, please remember to change your shell environment to TC shell by typing /usr/local/bin/tcsh. 6. You can quit the SQL*Plus environment by typing quit at the SQL*Plus prompt 7. If you have a PC Oracle GUI client and want to connect to the Loyola server via your local Oracle client, drop a note to me. ORACLE SERVER Aside from the notes that given here, you should also read Chapter 10 that pertains to Oracle. I will now introduce the basic components of an Oracle server. An Oracle server has two basic components: (a) memory structures (b) disk file structures. The Oracle memory structures are called an Oracle Instance and the disk file structures are called the Oracle database. By an Oracle database, we mean the disk file structures used by the Oracle server to carry out its data management tasks. An application (say for a library database) that runs on an Oracle platform is ambiguously called an Oracle database as well. But please note the distinction carefully. Your application is possibly one of several applications residing in an Oracle database (disk file structures). An Oracle instance being memory resident is temporary whereas an Oracle database is
permanent. In other words, an Oracle instance can go away if there is a power failure on the host machine that the Oracle server is running on whereas the Oracle database would remain intact. In the Oracle world, an instance can only connect to one database but one database can be connected to by multiple instances (for performance reasons). The latter case goes by the name Oracle Parallel Server (multiple instances connecting to a single Oracle database). The instance has an identifier and goes by the environment variable ORACLE_SID. The ORACLE_SID is WG805 for the instance you will be connecting. Oracle Instance: An Oracle instance consists of memory areas for (i) System Global Area (SGA), (ii) Program Global Area (PGA), and (iii) Oracle Processes. The SGA, in turn, comprises the Database Buffer Cache: This keeps the most recently fetched data blocks from the database. Any updates, insertions, and deletes are made here first. Redo Log Buffers: These buffers contain the “after-images” of changed data. Shared Pool: This area contains cursors, SQL statements, parse trees of SQL statements, and shared data structures. The program global area stores user-specific information and is controlled by a server process. Oracle processes consist of server processes and background processes. Background processes include (i) Database Writer (dbwr), (ii) Log writer (lgwr), (iii) Checkpoint (ckpt), (iv) smon, and (v) pmon. Database writer is responsible for writing entries of the database buffer cache into the data files, Log writer for writing entries from Redo log buffers into Redo log files, Checkpoint for keeping synchronization information, Smon for recovery, and managing storage, and pmon for cleaning up memory areas when a user process exits or fails. The file structures mentioned above are part of the Oracle database as explained below. Oracle Database: An Oracle database consists of (i) Data files, (ii) Control Files, and (iii) Redo Log Files. Data Files contain the application data, data dictionary information, system catalogs, and other application related information. Control Files contain information such as location of data files, and synchronization information. Redo Log Files contain the logs of changes made to data files. User Schemas: User created data objects such as tables, indexes, procedures etc are stored in data files and are “owned” by the user. A user may access his/her objects by logging into the server. However, objects owned by one user may not be accessed by another user unless the owner grants access privileges explicitly or if the objects had been created as “public”. In the event of access privileges being granted, one user may access another user’s objects by including the user schema as prefix to the data objects. For instance, the data objects owned by the user scott can be accessed by scott.objectname.
Specifically, if scott owned a table called DEPARTMENT, then you could run a query such as select * from scott.DEPARTMENT. SQL *Plus: Oracle’s SQL environment provides a simple, yet powerful way to interact with the server. Like we discussed earlier, SQL *Plus can be accessed via telnet to gauss.math.luc.edu and typing sqlplus on the command prompt. Some of the SQL *Plus commands are the following: 1. spool command: spool filename will store everything done in the SQL *Plus session from that point on into the file “filename” until either session is terminated or the spool off command. 2. @ command: Sql scripts (we will some examples of the script soon) can be executed by typing @fullpathname/script.sql. For instance, if you had a script by name create_emp.sql in /home/jparker directory. Then as the user jparker you could run the script by typing @/home/jparker/create_emp.sql within SQL*Plus. 3. / command: The last SQL statement will be re-executed by typing /. 4. All SQL statements within SQL *Plus session need to be terminated by a semicolon “;”. This is very important. 5. Data object names, such as tables, and attribute names are case insensitive. However, evidently, the table values are not.
Oracle Data Types
The following data types for defining attributes of a table are important for the purposes of this course.
CHAR: Fixed-length character. This can be any length between 1 and 255 bytes. The default is 1. DATE--Stores Century, Year, Month, Day, Hour, Minute, and Second values. The standard default date format is DD-MON-YY, as in ’13-Nov-99’. Date data is stored internally using seven bytes. LONG--Variable-length character data up to 2GB in size. NUMBER--Stores zero, or positive, or negative fixed or floating-point numbers. Its definition includes the precision and scale. For example, to store 5 digit integers you declare the column attribute as NUMBER(5). To store floating point numbers that have up to 2 decimal places, and up to 7 digits precision you declare the attribute as NUMBER(7,2). VARCHAR2: A variable-length character data type. When you create a table with a VARCHAR2 column, the maximum column length can be specified as between 1 and 2000 bytes.