Getting Started with Oracle (Personal reference Note)

Getting Started with Oracle

database server, database, and table relationships

The so-called installation database server, just installed on the machine a database management program, the hypervisor can manage multiple databases, the general developer will create a database for each application.

To save data for an entity in your app, you typically create multiple tables in the database to save data for entities in your program.

Database server, database, and table relationships:

Introduction to Oracle Management Tools (1)

Sql*plus is Oracle's own tool software, primarily used to execute SQL statements, Pl\sql blocks.

How to use:

1) at Start, program->oracle oradb_home10g->application development->sql*plus

2) in the Run bar, enter: SQLPLUSW

Introduction to Oracle Management Tools (2)

Overview:

Sqlplus is a tool for operating Oracle under DOS with similar functionality and Sql*plus.

1) Enter Sqlplus in the run bar

2) Locate the executable file Sqlplus.exe, in the Oracle home directory \ora10g\bin\sqlplus.exe, the mouse double-click can

Introduction to Oracle Management Tools (3)

Enterprise Manager console (corporate manager)

Oracle is the general default port for Web management, which is 5500 and 1158.

Access the URL (make sure the Oracle service starts):

http://ip:1158 (also possibly 5500)/em

http://Machine name Port/em

Introduction to Oracle Management Tools (4)

Overview:

PL/SQL Developer belongs to third-party software, primarily for development, testing, and optimization of Oracle PL/SQL stored procedures such as: triggers, which Oracle does not have and needs to be installed separately.

PS: This software is also the most commonly used Oracle database development software, it is recommended to use this software for practice.

sql*plus Common CommandsConnection Command

(1) Conn[ect]

USAGE: Conn User name/password @ NETWORK SERVICE name [as Sysdba/sysoper]

When connecting with a privileged user, you must bring as SYSDBA or as Sysoper

(2) Disc[onnect]

Description: This command is used to disconnect from the current database

(3) Passw[ord]

Description: The command is used to modify the user's password. If you want to change the password of other users, you need to login with Sys/system.

Usage: password-This is a change of your password

Password Scott--This is going to Sys/system permissions.

PS: Change Password also has a command alter user ... This will be described in detail later in the user management.

(4) Show user

Description: Displays the current user name

(5) Exit

Description: The command disconnects from the database and exits the Sql*plus

Interactive Commands

(1) &

Note: You can override a variable, which needs to be entered by the user when it executes.

Sql>select * from emp where job= ' &job '

(2) @ or start

Description: You can execute the specified SQL script

sql>@ D:\a.sql

Sql>start D:\a.sql

(3) Edit

Description: The command can edit the specified SQL script

Case: Sql>edit D:\a.sql

(4) Spool

Note: This command can output the contents of the Sql*plus screen to the specified file.

Case: Sql>spool d:\b.sql and input sql>spool off

Displaying and setting environment variables

(1) linesize

Description: Sets the width of the display line, which is 80 characters by default

Sql>show linesize

Sql>set Linesize 90

(2) pagesize

Note: Set the number of rows displayed per page, default is 14, and use the same as linesize. The use of other environmental parameters is similar

Oracle Login authentication MethodOracle Login authentication Mode-windows

① Operating System Certification

If the current user belongs to the ORA_DBA group of the local operating system (for the Windows operating system), it can be certified by the operating system.

②oracle Database Validation (password file validation)

For normal users, Oracle uses database validation by default.

For privileged users (such as SYS users), Oracle uses operating system authentication by default, if validation does not pass, and then to database validation (password file validation). By configuring the Sqlnet.ora file, you can modify the Oracle Login authentication method

Sqlnet. Authentication_services= (NTS) is based on operating system authentication: Sqlnet. Authentication_services= (NONE) is based on Oracle authentication; sqlnet.authentication_services= (none,nts) is the coexistence of the two.

Oracle Login authentication Mode-linux

By default, the Oracle database Sqlnet.ora file under Linux does not have the sqlnet.authentication_services parameter, which is based on the coexistence of operating system authentication and Oracle password authentication. When the sqlnet.authentication_services parameter is added, either sqlnet.authentication_services is set to None or NTS is based on the Oracle password authentication.

Lost Administrator password handling method

Recovery method: Delete the original password file, generate a new password file.

The recovery steps are as follows:

① Search named PWD db instance name. ora File

② Delete the file, in case of precaution, recommend that you back up

③ generate a new password file, enter the command under the DOS console:

Orapwd file= the full path \ password file name of the original password files. Ora password= new password entries=10;//entries: Allow several privileged users

The password file name must be the same as the original password file name.

the difference between the Oracle generated SYS user and the system User:

(1), sys user is superuser, has the highest privileges, has the SYSDBA role, has the rights to create database, the user's default password is Change_on_install

(2), the system user is the management operator, the authority is also very large. With the Sysoper role, without the CREATE DATABASE permission, the default password is the manager

(3), generally speaking, the database maintenance, using the system user login on the line.

(4), SYS and system two users the biggest difference is that there is no permissions to create database.

PS: There is a common user for learning Oracle is Scott, after Oracle 11g, the user by default is locked, can be unlocked when the database is established, or with ALTER user Scott account unlock unlock

Getting Started with Oracle (Personal reference Note)