Chapter 1 Introduction to Oracle

This chapter introduces Oracle to download and install the Oracle user-type EnterpriseManager management tool SQL * Plus management tool. oracle company profile a) Oracle (Oracle) company I. In June 1977, LarryEllison, BobMinor, and EdOates

This chapter introduces Oracle to download and install the Oracle user Type Enterprise Manager management tool SQL * Plus management tool. oracle company profile a) Oracle (Oracle) company I. in June 1977, Larry Ellison, Bob Minor, and Ed Oates were founded in partnership (Soft

Objectives of this Chapter

Introduction to Oracle

Download and install Oracle

Oracle user type

Enterprise Manager Management Tools

SQL * Plus management tool

Introduction to Oracle

1. Oracle Company Profile

A) Oracle (Oracle)

I. In June 1977, Larry Ellison, Bob Minor, and Ed Oates were founded in partnership (Software Development (SDL)

Ii. changed its name to Relational Software Inc. and RSI in 1979.

Iii. In 1983, RSI was renamed Oracle to highlight its core products

Iv. Release of Oracle version 8 on December 1, June 1997

V. In April 26, 2002, Oracle was launched as a Chinese registered trademark.

Vi. On June 19, September 2003, Ellison announced the next-generation database product "Oracle 10 Gb". "g" represents "grid, grid"

Vii. Oracle 11g was officially released in November 2007, with enhanced functions

2. Oracle 11g database products

A) Enterprise Edition)

This version is designed for enterprise-level applications.

B) Standard Edition)

This version is designed for department-level or workgroup-level applications. It is also applicable to small and medium-sized enterprises

C) Standard Edition 1 (Standard Edition One)

This version is designed for departments, working groups, or Web applications.

D) simplified version (Express)

This version currently supports single-user development and deployment environments that are fully compatible with the Enterprise and standard editions.

Download and install Oracle

1. Install Oracle 11g

A)

Http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html

B) download the installation file. The following files are suitable for Windows 32-bit operating systems.

I. Oracle 11g server files

1. win32_11gR2_database_1of2.zip

2. win32_11gr2_database204 f2.zip

Ii. Oracle 11g client files

1. win32_11gR2_client.zip

2. Oracle Installation

Oracle user type

1. The Oracle Database contains multiple user types. Only database administrators (DBAs) can manage the database.

2. Oracle Database User Type

A) Database Administrator

B) Security Officer

C) Network Administrator

D) application developers

E) Application Administrator

F) database users

3. Database Administrator

A) each database must have at least one database administrator. For large databases, a group of database administrators are required. The main task of the database administrator is

I. Evaluate the hardware configuration of the Database Server

Ii. Install Oracle database software and patches

Iii. Plan the logical storage structure, database structure, and backup policy of the database

Iv. Create and enable or disable databases on a daily basis

V. Create and maintain the primary database storage structure and database objects

Vi. Manage Users and maintain system security

Vii. control and monitor user access to the database

Viii. Monitor and optimize database Behavior

Ix. Back up and restore the database

4. Other database user types

A) security officer

It mainly manages users, controls and monitors users' access to databases, and maintains database security.

B) Network Administrator

Manage Oracle network products

C) application developers

Design and develop database applications

D) Application Administrator

Manage specified applications

E) database users

Database users interact with databases through applications, such as adding, modifying, and deleting data.

5. Default database administrator user

A) when installing the Oracle database, the username SYS and SYSTEM are automatically created, the user password is set during installation, and the database administrator (DBA) is automatically assigned.

B) SYS user

This account has all database management functions. The basic tables and views in all database data dictionaries are stored in the user's SYS Schema, these basic tables and views are important for Oracle database operations.

C) SYSTEM user

This account can perform all other daily management tasks except for database backup and recovery and Database Upgrade.

Enterprise Manager Management Tools

1. Enterprise Manager Management Tools

A) Oracle Enterprise Manager Database Control is a main tool for Database management based on the Web interface.

I. it can be used for daily management and maintenance tasks, including creating solutions (tables, views, and indexes), managing user and system security, managing the memory and storage of databases, backing up and restoring databases, data Import and Export to monitor database performance.

B) Start Enterprise Manager

C) in Microsoft Windows, go to control panel and select administrative tools ". Open the "service" window and confirm OracleDBConsole Start?

D) enter the following URL in the browser:

Https :// : /Em

For example: https: // localhost: 1158/em

2. Main Functions of Enterprise Manager

A) You can use SYS or SYSTEM or a user account with DBA permissions to log on.

B) The home page mainly contains the status and environment information of the current database.

I. Status and running time of the Current Database example

Ii. host CPU utilization

Iii. Number of active sessions

Iv. SQL Response Time

V. Diagnostic Summary

Vi. Summary of space usage

Vii. High Availability

SQL * Plus management tool

1. SQL * Plus management tool

A) the SQL * Plus tool can execute the entered SQL statements, files containing SQL statements, and PL/SQL statements. It allows you to directly manage databases, such as queries, inserts, updates, or deletes.

B) differences between SQL, PL/SQL, and SQL * Plus

I. SQL: structured query language. It is an industrial standard computer language used to query and update databases. It can implement various database management operations.

Ii. PL/SQL: it is a programming language formed by Oracle's procedural extension in the standard SQL language. During database management and maintenance, the PL/SQL language is often used to design and compile stored procedures, functions, and triggers.

Iii. SQL * Plus: it is a command line database management tool. It is the main operating environment for Oracle Database SQL statements and the main interface between users and servers. You can use SQL statements and PL/SQL programs to interactively access the database.

2. Start SQL * Plus to connect to the database

A) In Windows, you can start the SQL * Plus operation through command line or Windows menu.

B) start SQL * Plus in Command Line Mode

I. 1. Open the DOS window

Ii. 2. Configure operating system environment variables (sometimes omitted)

Iii. 3. Use the following command to start SQL * Plus

1. sqlplus {user name |/} [as sysdba]

2. For example:

3. sqlplus SYS AS SYSDBA

4. Enter the password: password

C) start SQL * Plus in Windows

I. Click Start in sequence? All programs? Oracle-OraDb11g_home1? Application Development? SQL Plus

Ii. After opening the logon window, enter the user name and password

3. SQL * Plus Editor

SQL * Plus allows you to easily edit and manage programming processes, including editing commands, saving commands, adding comments, running commands, editing interactive commands, binding variables, and tracking statements.

4. Edit commands

5. Save the command

A) in SQL * Plus, one or more SQL commands, PL/SQL blocks, and SQL * Plus commands can be stored in the command file, its Commands include the SAVE command, the INPUT command, and the EDIT command.

I. SAVE command

1. Format: SAVE filename

2. You can use the SAVE command to directly SAVE the SQL statement in the buffer zone to a file specified in the current path or specified path. The extension is. SQL.

Ii. INPUT command

You can use the INPUT and SAVE commands in combination. You can use the INPUT command to INPUT the SQL * Plus command into the buffer, and then use the SAVE command to SAVE it to the file.

Iii. EDIT command

You can use the EDIT command to create an object.

6. Add comments

A) adding annotations to the code can improve readability. adding annotations to SQL * Plus includes the REMARK command,/*... */, and --.

I. Use the REMARK command

1. Use the REMARK command to add comments to a line in a command file

2. For example, REMARK. This is the comment content.

Ii. Use the SQL comment separator/*... */to annotate one or more lines of a command file

Iii. Use --

1. Use ANSI/ISO style annotations to annotate a single row

2. For example:

3. -- clear the screen

4. CLEAR SCREEN

7. Run the command

A) There are three ways to run SQL commands and PL/SQL blocks: command line, SQL Buffer, and command file.

I. Command Line

The plus sign (;) after the command is used as the terminator to run the SQL command.

Ii. SQL Buffer Mode

1. SQL * Plus provides the RUN Command and the slash (/) command to execute SQL commands in the buffer mode.

2. RUN Command Format: R [un]. RUN Command to list and execute the commands currently stored in the buffer zone, return the query results, and make the last row in the buffer zone the current row.

3. the slash (/) command is similar to the RUN Command. It executes the SQL command or PL/SQL block stored in the buffer, but does not display the buffer content, it does not make the last row of the buffer zone the current row.

Iii. command file Method

1. Run an SQL command, SQL * Plus command, or PL/SQL block as a command file. There are two methods: START command and @ command.

2. START command

A) Syntax: START filename [. SQL] [arg1 arg2]

B) SQL * Plus searches for files with names and extensions specified in the START command in the current path. If not, it will be searched in the directory defined by the SQLPATH environment variable. Parameter section [arg1 arg2]

3. @ command

Similar to the functions of the START command, the only difference is that the @ command can be run both within the SQL * Plus session and at the command line level when SQL * Plus is started, the START command can only be executed within the SQL * Plus session command.

8. Use SQL * Plus to format the query results

A) When you execute a query statement in the SQL * Plus environment, you want to generate a readable output result. This requires some settings before executing the query statement.

B) SET command

I. SET command is used to SET the value of System Variables

Ii. Syntax: SET <系统变量名> <变量值>

Iii. Some variables are related to the output results

1. PAGESIZE: set the number of lines displayed on each page

2. LINESIZE: set the number of characters displayed on each line.

3. NEWPAGE: set the number of blank lines between the split page and the page.

4. HEADING: Indicates whether to display the column title of the query result. The default value is ON, indicating whether to display the title.

5. FEEDBACK: when at least n rows of records are selected in a query, the number of returned rows is displayed for the items in the result set. The default value is 6.

6. ECHO: when you use the START command to execute a script file, the ECHO command is used to control whether the SQL statements being executed in the script are displayed. The default value is OFF.

9. SHOW command

A) The SHOW command can be used to display system variables in the current SQL * Plus environment. The command format is

I. SHO [W] option

Where option is the system variable name

Ii. Example

SHOW LINESIZE

Returns the value of the current LINESIZE.

10. COLUMN command

A) The COLUMN command can change the COLUMN header and reformat the data of the COLUMN in the query. Here we introduce two types of syntax:

I. Modify the column header

1. COLUMN column_name HEADING column_heading

2. column_name: column of the title to be modified

3. column_heading: the title of the output column of column_name is specified.

Ii. format the NUMBER Column

1. COLUMN column_name FORMAT model

2. column_name: columns to be formatted and Output

3. model: format the output.

Summary

Introduction to Oracle

Download and install Oracle

Oracle user type

Enterprise Manager Management Tools

SQL * Plus management tool