Homework #3 – CSc 134, Summer 2002, Prof
SQLPLUS204_f20.doc – Oracle usage, CSC204, September 2020 - Prof. Mitchell
Section #0. Accessing ECS servers in fall 2020
There is an ongoing change to the ECS college server network in fall 2020. Previously, ECS managed all servers housed in the RVR (Riverside Hall) building. However, the university is taking over management of all ECS systems. This ongoing process has involved some confusion and problems.
An ECS student accounts note –
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 athena (or any other ECS Linux server that responds to the internet) with any such class account. This is because all such servers share the same file system and authentication.
In future semesters, having a student account for each CSC class will be replaced by the university with one authentication to ECS servers (in spring 2021?).
This section outlines how to authenticate (means login via username & password) for access to the DB server 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.
HowToGet GlobalProtect 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 …’
The connect tutorial is an Installation and Use guide for the CSUS vpn.
Update, Sept 9
(strongly) recommended server access policy to be followed by both returning and new ECS students for fall 2020.
Given the ECS server access info above and below this paragraph, I have just confirmed with ECS staff that ALL students should use the CSUS vpn when accessing any ECS server(s).
Although not being enforced for athena access this fall, this policy WILL BE enforced by spring 2021.
AND, using the vpn enables you to directly log into cscoracle (bypassing athena)
Connecting to an Oracle DB session, requires two (2) or three (3) authentications, depending on the connection method used.
3 authentications to login to Oracle
If you are NOT using the CSUS vpn:
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 fall 2020 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 a password.
2. Login to cscoracle (= the vm running Oracle) as follows:
a command-line OS session on cscoracle can be initiated 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
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.
3. Log into Oracle on cscoracle as described in the boxes below.
2 authentications to login to Oracle
If you ARE using the CSUS vpn:
1. Bypass athena login, and log in directly to the OS on cscoracle by using
step 1A ) or 1B ) above (returning students have an option of using their existing ECS account or the fall 2020 class account; new students must use their fall 2020 class account)
2. Log into Oracle on cscoracle as described in the boxes below.
Section #1. Configuring and using your Oracle Account on cscoracle
Oracle homework in the class must be completed using the course virtual server named cscoracle; note that athena (as most other ECS servers) does not run Oracle.
On the other hand, cscoracle is an ECS server that is dedicated to supporting some of the CSC database courses. Fortunately, cscoracle also runs the same CentOS Red Hat OS dialect as athena, and so the OS interface to our Oracle use will be familiar.
It is very common that large software systems such as Oracle require customized installation, but also need a small amount of user settings/configuration (that add some Oracle-specific symbols/variables to the user’s OS shell)
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)
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
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 dynamic & shared (for large 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: cs2041xx = > 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. In a SQL statement, you represent the name of an object within a schema with a quoted identifier or a non-quoted 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.
FYI: non-quoted names are used by most Oracle users.
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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- first grade summer homework packet
- printable homework for 3 year olds
- 134 miles inches
- department of business and prof regulation fl
- prof michel chossudovsky
- when is sas 134 effective
- il department of financial prof regulation
- grade 3 homework sheets
- 4th grade summer homework packet
- 5th grade summer homework packet
- 2002 ford ranger 3 0 motor
- connect chapter 3 homework answers