Homework #3 – CSc 134, Summer 2002, Prof



SQLPLUS204_s21.doc – Oracle usage, CSC204, January 27, 2021 - Prof. Mitchell

Section #0. Accessing ECS servers in spring 2021

IF you have been or will be given a ECS Linux “class account” in more than one class, you can login to the OS on BOTH athena AND cscoracle with any such class account. This is because these servers share the same file system and authentication (means login using a username and password).

Although athena and cscoracle have the same file system from a student viewpoint, they do not have the same software systems. Specifically, Oracle runs on cscoracle and NOT on athena.

This section outlines how to authenticate (means login via username & password) for access to the servers needed for this course.

There are two different procedures for DB login, depending on –

whether you are using the CSUS vpn to connect to ECS servers

OR

whether you are not using the CSUS vpn to connect to ECS servers

CSUS vpn

If you have installed the CSUS vpn (by GlobalProtect), you can then directly log into the OS on any ECS Linux server, including cscoracle.

Using the vpn avoids athena login and ssh login to cscoracle, as described elsewhere in this document.

How To Get the GlobalProtect vpn installed

In your web browser, type ecs.csus.edu. From the top menu, choose TECH TOOLS, and then choose Helpdesk; in the Tutorials section of that page (down and to the left) choose ‘Connect To The CSUS VPN via Global Protect …’ (it should be the top entry in the Tutorials area)

The connect tutorial is an Installation and Use guide for the CSUS vpn.

Although not being enforced for athena access this spring, using the vpn for CSC204 work enables you to directly log into cscoracle (bypassing/eliminating the need to log into athena)

Connecting to an Oracle DB session, requires two (2) or three (3) authentications, depending on whether the vpn is or is not used.

3 authentications to login to Oracle

If you are NOT using the CSUS vpn and you are off campus:

1. Login to athena in one of two ways:

A ) If you are a NEW STUDENT to CSUS this semester, log into athena using the CSC204 class specific account emailed to you by Prof. Mitchell

B ) If you are a CONTINUING student in ECS college, and already have an ECS Linux account from previous semesters, you can use that account to login to athena, OR use the spring 2021 CSC204 class specific account emailed to you by Prof. Mitchell

Caution: if you forget the password to any ECS account, you must contact ECS Helpdesk to get your password reset. Only ECS Helpdesk, not the instructor, can reset an ECS Linux account password.

2. Login to cscoracle (= the vm running Oracle) as follows:

a command-line OS session on cscoracle can be initiated when logged in to athena via Secure Shell protocol using the ssh command, with:

%OS_Prompt ssh –l Your_OS_UserName cscoracle (the firstt ssh argument is “el”, not “one”) where “%OS_Prompt” is OS shell prompt

The cscoracle server has the same OS username + password as your ECS Linux account

3. Log into Oracle on cscoracle as described in the boxes below.

Caution: The course instructor is the ONLY person who can reset an Oracle password.

DO NOT ASK the ECS Helpdesk concerning forgotten Oracle passwords. Opposite to ECS server passwords, only the instructor, not ECS, can reset Oracle passwords.

2 authentications to login to Oracle

If you ARE using the CSUS vpn:

1. You can bypass athena login, and log in directly to the Linux OS on cscoracle by using

step 1A ) or 1B ) above (returning students have an option of using their existing ECS account; new students must use a spring 2021 class account)

2. Log into Oracle on cscoracle as described in the boxes below.

Section #1. Configuring and using your Oracle Account on cscoracle

Class home work must be completed using the course virtual server named cscoracle.

cscoracle is an ECS server that also supports other CSC database courses.

It is very common that large software systems such as Oracle require customized installation, but also need a small amount of individual DB user settings/configuration (such a configuration adds some Oracle-specific symbols/variables to the user’s OS shell on cscoracle)

Complete the configuration of your Oracle account when you are logged in to the OS on cscoracle for the first time. Carry out the steps in the boxes below:

#1 Do the following file edit ONLY ONE TIME for the course:

{Hint for appending the configure_204_xxx.txt file for c or bash shell Linux described below:

Use Unix command cat f2 >> f1 to append contents of file f2 to end of file f1 }

A full file path and filename will be needed for the f2 argument of the cat command

(cut-and-paste instead of using cat might put invisible characters in .login & corrupt it; same warning for bash shell users)

IF using Linux c shell: append, to the end of your existing .login file, the contents of the file: /gaia/home/faculty/mitchell/html/csc204/homework/configure_204_cshell.txt

IF using Linux bash shell: add the contents of the file: /gaia/home/faculty/mitchell/html/CSC204/homework/configure_204_bashshell.txt

to the end of your existing .bash_profile file (if .bash_profile does not exist, create an empty one)

Then, any future oracle login automatically executes your .login or .bash_profile

Note: logout and then login again for your shell updates to take effect (or do Unix shortcuts you know to avoid logging out that force the file changes to take effect without logging out)

#3. cscoracle runs the Linux CentOS 6 OS on an ECS virtualized machine; the OS is an open source dialect of Redhat Linux; the Oracle system on cscoracle is Enterprise version 11g release 2.0.1.0 This is a quite old version of Oracle , but it is more than adequate for CSC204.

After login to cscoracle, you can connect to an Oracle SQL*Plus command session by:

% OS prompt sqlplus ( Recall:Unix/Linux OS commands are case sensitive

( Note: “[yourOS_username]cscoracle” is the cscoracle server’s OS prompt )

You are then prompted for the standard Oracle USERNAME and PASSWORD prompts.

Initially, your Oracle password is the same as your Oracle username

After successful first-time connection to Oracle (you will see the SQL> prompt), IMMEDIATELY change your Oracle password using Oracle SQL DDL command:

SQL> alter user cs2041xx identified by NewOraclePassword; (semicolon required)

(See footnote 3 about “;” “xx” syntax, and legal Oracle password characters)

A SQL*Plus connection is persistent until session logout.

((Normal) logout from a SQL*Plus session and return to the OS using: SQL> exit)

You are now properly configured for CSC204 course work.

Oracle schemas and the DD

Each cs2041xx account maps to Oracle DB space (consisting of logical and physical space –

see Fig 1). Disk space is nfs filesystem filespace ( = SAME file system on cscoracle as on athena ).

By default, each object (such as a table/view) you create is private to your Oracle account.

All CSC204 students share 2 Oracle logical tablespace objects, named STUD_CS2041 and DATABASE_TEMP. Table rows, are mapped by STUD_CS2041 into physical disk space.

Per-user space quota is enforced for STUD_CS2041, and (temporary) quota for DATABASE_TEMP is shared (for large SQL sorts, transactions, etc.). Tablespaces have parameters defining storage allocation/placement/etc. per object associated with that tablespace. A given user’s objects can be associated with different tablespaces (for flexible physical storage)

After each Oracle login, you see the standard Oracle SQL*Plus[1] command prompt “SQL >”. At the “SQL>” prompt, you are interacting with the command-line language (named SQL*Plus) interface to oracle SQL. Used frequently this semester, it is “closest to” the SQL engine.

During a SQL session, you can suspend the Oracle session and spawn a Linux command shell to execute an OS-level command via: SQL> host Any_OS_command (or SQL> ! Any_OS_command)

For example, try this NOW: SQL> host date

When the command finishes, the OS shell process spawned by the OS command terminates,

and the Oracle session automatically resumes with the “SQL>” prompt.

Terminate an Oracle session and return to the OS command shell by: SQL> exit

Section #2. Oracle SQL and SQL*Plus introduction

Section 2.1 Sample user queries and data dictionary (DD) queries

You can practice queries on schema SH’s (a public readable schema) tables[2]. Almost all SH tables can be queried (via SELECT). You access tables such as SALES with qualified name SH.SALES (syntax is: SchemaName.TableName) SQL> select count(*) from sh.sales;

[ Unlike some non-Oracle DBMSs, the above syntax does not need to specify a database name, nor initialize database access – all student accounts use one implicitly named database. ]

Note: each student schema name is of the form: cs2041xy = > thus, sales is not in your schema

Every existing object has an ‘owner’ schema. If you own an object, you do not need to prefix its name with your schema name. You can display schema structures of Oracle tables/views you own via: SQL> describe TableName (or ViewName)

(Try this NOW for the two TableNames SALES and then SH.SALES and compare the result)

You should see that: the SQL*Plus describe command queries Oracle DD content and displays the schema for TableName (if it exists and user has access rights to it, otherwise, error).

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

You often need to capture results from many kinds of processing into a simple text file (as the OS sees it). The SQL*Plus spool command creates such files and is required in homeworks.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Spooling – how to capture output from a SQL*Plus script to an OS text file

Example scripts and homework questions in this course often require oracle “spooling”.

Use the following steps whenever you need to capture a (plain text) file copy of a sequence of SQL*Plus (or other oracle) commands and their results:

set echo on (Displays each executed command as well as command results)

show user (Display the oracle username of this session;

only one spool file can be open at any moment in a SQL*Plus session)

spool spoolFileName (the file is an OS text file; suggested naming convention xxx.out)

. . .

command(s) (Any number of SQL*Plus commands, except quit/exit ;

. . . can also call various kinds of other modules)

set echo off

spool off (spool file remains open until this command closes the spool file or session terminates/aborts)

Section 2.2 Review of some simple (standard) SQL; additional Oracle SQL*Plus syntax

1. Each Oracle SQL statement (SELECT, UPDATE, etc.) is terminated by an “;”[3]

2. SQL*Plus session commands affecting the user interface of display (such as: describe, show, column, etc.) do not require a “;” for command termination

3. SQL statement clauses are free-format, but it is easier to read code with select, from, where, etc. clauses on different source lines

4. SQL statement elements do not distinguish case (Example: SELECT same as SElecT)

5. LIKE is a string comparison operator, and string values are delimited by single quotes. Upper and lower case are significant in strings Ex: ‘this is a string constant in SQL’

6. A “*” in a SELECT clause represents (i.e., is an alias for) all columns of the table(s)

7. Schema object naming (is DBMS-vendor specific) – see Section #4 for the basics.

Oracle translates schema names (table, view, etc.) into upper case before storing the name in the DD. That is why uppercase ‘SH’ is the string value in a query such as:

select table_name from all_tables where owner = ‘SH’; (coding = ‘sh’ will return 0 rows)

When working on complicated queries (for which several attempts at correct code might be needed), it is best to initially code the query in a text file in your cscoracle OS filespace, and then have Oracle load and execute the file. xxx.sql is the Oracle naming form for SQL source code files.

Recommended SQL development/testing cycle with SQL*Plus in this course:

1) Code queries in file, assuming a name such as MyQuery.sql (use any OS-installed editor)

2) In an Oracle session, do: SQL> @MyQuery.sql (loads and executes contents of MyQuery.sql ) Note: filename upper/lower case reverts to Linux filename syntax

Note: if MyQuery.sql is not in the same directory as the current directory during Oracle login, then fullPath/MyQuery.sql must be specified.

3) If there are problems/errors, re-edit the source using:

SQL> host nano MyQuery.sql (nano editor is assumed here, or your preferred editor)

exit your editor session in the usual way for that editor; and, resume at step 2

Practice the above 3-step cycle on a simple example of your own, now.

Section #3. SQL user session login file and cascaded login initializations

This information in this section will not be needed/used in the early part of this course, but is FYI, and will be used later in homeworks.

SQL*Plus provides session initialization capabilities. Each Oracle user can compose his/her own set of commands (in a file that must be named login.sql). login.sql is automatically executed during session startup, as SQL*Plus will search for login.sql in the path: SQLPATH/login.sql.

The shell symbol SQLPATH must be defined in your OS shell; define it (using a setenv in your .login file (for C-shell users), for example)

In addition, Oracle installs also configure a “global” SQL*Plus startup file for all Oracle users

by executing the following initialization before a user’s login.sql (if login.sql exists):

$ORACLE_HOME/sqlplus/admin/glogin.sql

One of the things glogin.sql does is pre-format the display of some columns in Oracle DD.

Section #4. Summary of Oracle schema object naming rules – Reference

“Database SQL Reference”, pg. 19 of 479, Oracle 10g release 2.0

Quoting the reference: “Schema objects are made up of parts that you can or must name, such as the columns in a table or view, index and table partitions and sub-partitions, integrity constraints on a table, and objects that are stored within a package, including procedures and stored functions.”

Every database object has a name. You represent the name of an object within a schema with a quoted identifier or a non-quoted identifier (aka regular identifier).

• A non-quoted identifier (the more common usage) is not surrounded by any punctuation.

You can use either quoted or non-quoted identifiers to name any database object.

Case mapping: select * from abC (or any mix of case) maps to select * from ABC

because the table object is stored in the Oracle DD as ABC (all upper case)

• A quoted identifier begins and ends with double quotation marks ("); it cannot contain a double quotation character. If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.

Note: non-quoted names are usually used in Oracle. Regular of names are defined in the SQL standard

Database names, global database names, and database link names are always case insensitive and are stored as uppercase. With quoted identifiers, the quotation marks are silently ignored. Refer to the Oracle CREATE USER for additional rules for naming users and passwords.

These rules apply to both quoted and non-quoted identifiers unless otherwise indicated:

Names must be from 1 to 30 bytes long with these exceptions:

o Names of databases are limited to 8 bytes.

o Names of database links can be ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download