Study of Oracle Lesson One

The first part of Oracle Learning

1.oracle Introduction

1.1 Common database classification at present

Small database: SQL Server,access (Microsoft)

Medium database: MySQL (Swedish company), Sybase (US Sybase)

Large database: Informix (IBM), Oracle (Oracle), DB2 (IBM)

1.2 Oracle Database Introduction

Oracle database is by the United States Oracle Company's products, the company since 1970 began to develop databases, the current database has been developed to Oracle 11G, the more popular is Oracle 9i and Oracle 10g,oracle Words of the idea is divine, Dayshen Speaking Man, Oracle

1.3 Oracle Database Qualification certification

A. OCA Oracle low-level authentication

B. OCP Oracle Intermediate Certification

C. OCM Oracle Advanced Certification (difficult to test, the application fee is very expensive, about 2000 dollars, mainland China is not necessarily a couch)

2. Installation of Oracle Database

2. Default User

After the Oracle database is installed, there will be three users by default

DBA User: The database administrator, with the highest database permissions, which can be either the CREATE DATABASE permission or the superuser, the SYSDBA role, the user password is as follows:

Sys/change_on_install

Sysoper User: Database user Administrator, the permissions are also very large, but no permissions to create DATABASE, the user password is as follows:

System/manager

Scott users: Ordinary users, Oracle will give it default to produce some data objects, the user password is:

Scott/tiger

Note: In general, the maintenance and operation of the database, using the system user can be

3.oracle Data Start-up

A. Starting a DB instance

B. Start the database listener

4. Connect to the database

A.sqlplus can be connected

B.pl/sql can be connected

...

5. Common commands

A. Common system commands:

(1) Show current users: Show user;

(2) Connection database: Conn (connect) Username/password;

(3) Change Password: PASSW (password) user name;

(4) See what tables are available for the current user:

Select table_name from User_tables;

(5) Setting environment variables

Set line width: set pagesize = 120;

Set Page Size: Set pagesize = 8;//a page showing 8 data, including the last blank line

B. File interaction commands

(1) Interactive command:&

You can override a variable that, when executed, requires the user to enter

SELECT * from emp where job= ' &job ';

(2) Execute script command: Start or @

@ D:\my.sql;

Start D:\my.sql;

(3) Modify SQL script command: Edit

Edit D:\my.sql; Open the script file, and the user can then modify the

(4) Redirect: Spool, output redirected from command line to file, two-step operation required

Spool filename;//Creating a new file filename

Spool off;//output to File

C. Manipulating user statements

(1) Oracle user creation: only SYS or system user has permission to create user

Create user Liudh identified by m123; Create user Liudh, password is m123, note: the password in the Oracle database must start with a letter

(2) Change password

Password user name;

(3) Delete user, generally cascade delete

Drop user username cascade;

D. Authorization actions, permissions of the database administrator, new user default tablespace is System space

(1) Grant connect to liudh;//gives the user Liudh access to the Connect role, and connect is a role that has 7 permissions, such as connecting to a database

(2) View table structure

DESC TableName;

(3) Permission to grant access to other users ' tables

Grant SELECT on the EMP to Liudh;//emp table is a Scott user, and after doing this, the Liudh user can query the table EMP under the (SELECT) Scott User

Grant update on EMP to Liudh;

Use all to grant multiple object permissions, including object permissions: Select,update,delete,create

Grant all on the EMP to Liudhu;

(4) to operate the other user's table, to bring the user name

Liudh the user wants to query the EMP table of the Scott user, which can be implemented by the following statement, given the permissions:

SELECT * from Scott.emp;

(5) Permission Resource recovery: Invoke

Revoke select on EMP from Liudh;//scott user cancels Liudhy user's Select object permissions on their own table EMP

Note: The collection of permissions is cascading

Scott grants Liudh,liudh the right to select an EMP to grant ZHANGXY the Select Object permission for the Scott table EMP, and when Scott recycles Liudh that permission, Zhangxy's permissions are also retracted.

, that is, the recall of permissions is cascading

(6) Transfer of permissions, maintenance of permissions using with GRANT option and with admin option

Scott users Grant Liudh the Select object permissions on their own table EMP and also want the Liudh user to grant the Select object permissions on the EMP table to others, such as Zhangxy, as follows

Object permissions are passed:

Grant SELECT on EMP-Liudh with GRANT option;

System permissions are passed:

Grant connect to Liudh with Admin option;//database administrator grants Connect system permissions to Liudh, and Liudh can grant connect system privileges to other ordinary users

6.oracle User Management

The Oracle installation generates three users by default, and a DB instance can be added below, and then the user can build a data object under that instance, which is a shared table space.

Each user has its own table under this instance, and the user's own unique Table data object name can be the same, each user has their own permissions, where the permissions are divided into system permissions and object permissions (the data

Permissions for object manipulation)

Data objects include: tables, stored procedures, views, functions, sequences, triggers, roles, table spaces, jobs, packages, etc.

7.oracle User Password Management

Implemented by creating a profile file, which is a collection of commands that are password-constrained, resource-constrained

(1) Account lockout

Create Profile Rule AAA: User three password error, account locked for two days

Create profile AAA limit faild_login_attempts 3 Password_lock_time 2;

AAA The locking rule to the user Liudh

Alter user Liudh profile aaa;//Note: The profile here is a keyword

(2) Account unlock: (only DBA has authority)

Alter user Liudh account unlock;

(3) Termination password:

Force the user to change the password every 10 days for a period of 2 days

Create profile BBB limit password_life_time Password_grace_time 2;

Grant the rule BBB to the user Liudh

Alter user Liudh profile BBB;

(4) Password history:

If the user modifies the password, it cannot be changed to the same as the previous one, after 10 days.

Create profile Passwordtime limit password_life_time password_grace_time 2 password_reuse_time 10;

Delete a profile rule, which is usually a cascade delete

Drop profile passwordtime cascade;//cascade Delete

Study of Oracle Lesson One