Learning Objectives – after this chapter you will be able to:
- Describe Oracle as a database server used as the back-end in client/server computing.
- Explain the difference between an Oracle instance and an Oracle database.
- Differentiate Personal Oracle from Oracle Database Server.
- Log on with SQL*Plus using username, password and host string.
- Follow the rules for entering SQL and SQL*Plus commands and recognise common logon errors.
The PDF was written for Oracle8i / Oracle9i on Windows NT/2000. The concepts (instance, database, SQL*Plus, net service names) still apply, but menu names, service names and pre-created accounts differ in newer Oracle releases – follow the documentation of the version you have installed.
What is Oracle?
- Oracle is the name of the database management system from Oracle Corporation.
- According to the notes, Oracle9i is a platform – a collection of Oracle9i Application Server (9iAS), Oracle9i Database Server (9iDB) and Oracle9i Developer Suite (9iDS) – whereas Oracle8i was only a database management system.
- This course is about the Oracle Database Server – the RDBMS used to store and access data.
- Oracle Corporation also offers ERP software (Oracle Applications such as Oracle Financials).
Oracle Database Server
- Widely used as the back-end in client/server computing: front-end programs (written in tools such as Visual Basic) submit SQL commands to Oracle for execution.
- From Oracle8i onwards Oracle added features needed by an internet database and for data stored by web sites.
- Supports both OLTP (online transaction processing) and data-warehouse applications that hold a very large database (VLDB).
- Runs on around 100 platforms and is scalable – it scales up and down as requirements change.
- Includes a Java Virtual Machine; from Oracle8i, programs can be written in PL/SQL or Java.
Oracle Instance vs Oracle Database
Oracle InstanceMemory structures + processes in primary memory (RAM) that manage the database.
Oracle DatabaseThe data stored on the hard disk.
- An Oracle instance is a collection of memory structures and processes used to manage an Oracle database.
- Each database is accessed by one or more instances. Two or more instances accessing the same database is called parallel server architecture.
- To use a database we must first start the instance; the instance then opens the database and makes it available to users.
- Every Oracle installation contains at least one instance and one database.
Personal Oracle
| Personal Oracle | Oracle Database Server | |
|---|---|---|
| Purpose | A learning / development tool on desktop PCs; not for production systems. | Production systems where real data is stored. |
| Where things run | Instance, database and client application on the same machine. | Instance and database on the server; client applications on clients. |
| Starting | Instance must be started explicitly (Start Database option). | Starts automatically on Windows NT/2000 (a Windows service). |
| Host string | Never needed. | Needed when the server is on a remote machine. |
Applications developed on Personal Oracle can later be deployed in a client/server environment.
Starting and shutting down the database is the job of the Database Administrator (DBA). On Windows the server runs as a service (named after the instance, e.g. OracleServiceOracle8i) that can be started from the Services program.
Logging on with SQL*Plus
SQL*Plus is a command-line tool supplied with Oracle to enter SQL and SQL*Plus commands. It asks for three values in the Log On dialog:
- Username & password – every user needs an account created by the DBA. A user can access only his/her own data unless privileges are granted by the owner.
- Host string – actually a net service name stored in the client’s
TNSNAMES.ORAfile. It supplies the host name / IP address of the server machine, the instance name and the port number of the listener (the program that receives client requests).
The DBA creates users, manages security, takes backups, recovers the database after failures and tunes it for performance. Logging on as SYSTEM or SYS gives DBA privileges, while accounts such as SCOTT and DEMO were sample accounts in those versions (the PDF lists their default passwords).
SQL vs SQL*Plus Commands
| Rule | SQL commands | SQL*Plus commands |
|---|---|---|
| What they are | ANSI/ISO SQL plus Oracle’s extensions | Available only in SQL*Plus – used to edit SQL commands and format query results |
| Lines | May span multiple lines (a word cannot be broken across lines) | Must be entered on a single line |
| Terminator | Must end with a semicolon (;) | No semicolon needed |
| Abbreviation | Not allowed | Allowed – e.g. CLEAR SCREEN → CL SCR |
| Case | Neither SQL nor SQL*Plus commands are case sensitive. | |
A multi-line SQL command and an abbreviated SQL*Plus command
SQL> select ccode,name 2 from courses 3 where fee > 5000; SQL> clear screen SQL> cl scr
Common Logon Errors
- ORA-01017: invalid username/password; login denied – the username does not exist or the password is mistyped. SQL*Plus gives three chances and then exits.
- ORA-01034: ORACLE not available – the Oracle instance is not up and running. Start the instance (Personal Oracle) or notify the administrator (Oracle Server).
Key Takeaways
- Instance = memory structures + processes; database = data on disk.
- The host string (net service name in TNSNAMES.ORA) locates a remote server: host, instance and listener port.
- SQL commands end with “;” and may span lines; SQL*Plus commands are single-line and may be abbreviated.
Chapter 2 Notes (PDF)
ch2.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 2 notes (PDF). Click a question to see the answer.
Answer : Oracle is the database management system from Oracle Corporation. Oracle8i is only a database management system, whereas Oracle9i is a platform – a collection of Oracle9i Application Server (Oracle9iAS), Oracle9i Database Server (Oracle9iDB) and Oracle9i Developer Suite (Oracle9iDS).
Answer : It works as the back-end. Front-end programs written using application development tools such as Visual Basic access Oracle and submit SQL commands to it for execution.
Answer : Oracle supports both OLTP (online transaction processing) applications and data-warehouse applications. Data-warehouse applications contain a very large database, called a VLDB.
Answer : Oracle is available on around 100 different platforms, and it is scalable – it can scale up and down easily as requirements change. It also provides a Java Virtual Machine, so from Oracle8i it can run programs written in PL/SQL or Java.
Answer : An Oracle instance is a collection of memory structures and processes used to manage an Oracle database. The instance must be started first; it then opens the database and makes it available to users.
Answer : Each Oracle database is accessed by one or more Oracle instances. When two or more instances access the same database, it is called parallel server architecture.
Answer : Personal Oracle is a flavour of Oracle used mainly as a learning tool on desktop PCs, not for production systems. In Personal Oracle the instance, the database and the client application all run on the same machine, whereas in Oracle Database Server only the instance and database reside on the server and client applications run on clients.
Answer : Starting up the database means starting the Oracle instance and associating it with an Oracle database so that the instance can access the database. On Windows NT/2000 the server usually starts automatically as a service; in Personal Oracle the instance must be started explicitly.
Answer : SQL*Plus is a tool that comes with Oracle. It provides a command-line interface through which SQL and SQL*Plus commands are entered; it displays the SQL> prompt after a successful logon.
Answer : The DBA creates users and manages overall security, makes sure the database is functioning smoothly, takes backups, recovers the database in the event of failure and fine-tunes the database for best performance. Starting and shutting down the database is also the DBA’s job.
Answer : A host string is actually a net service name stored in the TNSNAMES.ORA file on the client. It is needed only to connect to an Oracle server on a remote machine and provides the host name or IP address of the server, the name of the Oracle instance and the port number of the listener.
Answer : Because in Personal Oracle the client and Oracle always run on the same machine; a host string is required only when the Oracle server runs on a different machine from the client.
Answer : An SQL statement may be entered in multiple lines, a word cannot be broken across lines, and the statement must be terminated by a semicolon (;). SQL commands are not case sensitive.
Answer : SQL*Plus commands are available only in SQL*Plus and are not part of standard SQL; they are mainly used to edit SQL commands and format query results. The entire command must be on a single line, it need not be terminated with a semicolon, and it can be abbreviated (e.g. CLEAR SCREEN as CL SCR).
Answer : ORA-01017 (invalid username/password; login denied) occurs when the username does not exist or the password is typed wrongly; SQL*Plus allows three attempts. ORA-01034 (ORACLE not available) occurs when the Oracle instance is not up and running; the instance must be started (or the administrator notified).
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database Concepts, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/
- Oracle Corporation. (n.d.). Oracle Database Administrator’s Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/
- Oracle Corporation. (n.d.). SQL*Plus User’s Guide and Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/
- Oracle Corporation. (n.d.). Oracle Database Net Services Administrator’s Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/
- Oracle Corporation. (n.d.). Oracle Database Error Messages, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/errmg/