Study of oracle lesson one, oraclelesson

Study of oracle lesson one, oraclelesson

Oracle learning Part 1

1. oracle Introduction

1.1 common database categories

Small databases: SQL server, access (Microsoft)

Medium-sized databases: mysql (from Sweden) and sybase (sybase from the United States)

Large Databases: informix (IBM), oracle (oracle), db2 (IBM)

1.2 oracle Database Introduction

Oracle Database is a product developed by oracle in the United States. Since 1970, the company has developed databases. Currently, the database has grown to oracle 11G. oracle 9i and oracle 10g are popular, the meaning of oracle words is a metaphor for God, the speaker of God, 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, registration fee is very expensive, about $2000, not necessarily available in mainland China)

2. oracle Database Installation

2. Default User

After the oracle database is installed, three users are created by default.

Dba User: a database administrator with the highest database permissions. the user can create database permissions, also known as a Super User and sysdba roles. The user passwords are as follows:

Sys/change_on_install

Sysoper User: database User administrator. The permissions are also high, but the create database permission is not available. The user passwords are as follows:

System/manager

Scott User: a common user. oracle generates some data objects by default. The user passwords are as follows:

Scott/tiger

Note: Generally, you can use system to maintain and operate databases.

3. oracle Data startup

A. Start the database instance

B. Start database listening

4. Connect to the database

A. sqlplus can be connected

B. PL/SQL can be connected

...

5. Common commands

A. Common system commands:

(1) display the current user: show user;

(2) connect to the database: conn (connect) User Name/password;

(3) Change password: passw (password) User Name;

(4) view the tables of the current user:

Select table_name from user_tables;

(5) set Environment Variables

Set row width: set pagesize = 120;

Set page size: set pagesize = 8; // eight data entries are displayed on one page, including the last blank row.

B. File interaction commands

(1) Interactive commands :&

This variable can be used to replace variables. During execution, you must enter

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

(2) execute the script command: start or @

@ D: \ my. SQL;

Start d: \ my. SQL;

(3) modify the SQL script command: edit

Edit D: \ my. SQL; // open the script file and you can modify it.

(4) redirection: spool, which redirects the command line output to a file and requires two steps

Spool filename; // create a new file filename

Spool off; // output to the file

C. Operate user statements

(1) oracle user creation: Only sys or system users have permission to create users

Create user liudh identified by m123; // create a user liudh with the password m123. Note: The password in the oracle database must start with a letter.

(2) Change the password

Password username;

(3) Delete a user, usually cascade Deletion

Drop user Username cascade;

D. grant permissions only to the database administrator. The default tablespace of the new user is system space.

(1) grant connect to liudh; // grant the connect role permission to the user liudh. connect is a role that has seven permissions, such as database connection.

(2) view the table structure

Desc tablename;

(3) grant table access permissions to other users

Grant select on emp to liudh; // The emp table is owned by scott. After this operation is performed, the liudh user can query (select) The table emp under scott.

Grant update on emp to liudh;

You can use all to grant multiple object permissions, including: select, update, delete, and create.

Grant all on emp to liudhu;

(4) to operate tables of other users, add the user name

Liudh users want to query scott's emp table. The following statement can be used to grant permissions:

Select * from scott. emp;

(5) Permission Resource Recycling: invoke

Revoke select on emp from liudh; // The scott user cancels the select object permission of the liudhy user on the emp table.

Note: Permission revocation is cascade.

Scott passes the select Table emp permission to liudh, and liudh grants zhangxy the select object permission to scott table emp. When scott revokes this permission, the zhangxy permission is also revoked.

That is, permission revocation is cascade.

(6) Transfer permissions. To maintain permissions, use with grant option and with admin option.

Scott grants liudh the select object permission for his table emp, and also wants liudh to grant the select object permission for the emp table to others, such as zhangxy. The statement is as follows:

Object permission transfer:

Grant select on emp to liudh with grant option;

Transfer of system permissions:

Grant connect to liudh with admin option; // the database administrator grants the connect system permission to liudh, and then liudh can grant the connect system permission to other common users.

6. oracle user management

By default, oracle Installation generates three users and one database instance. Other users can be added to the instance, and then the user can create data objects under the instance, is a shared tablespace,

Each user has a table that he or she can operate on under this instance. Different users have the same table data object name. each user has his or her own permissions, the permissions include system permissions and object permissions (for Data

Object operation permissions)

Data Objects include tables, stored procedures, views, functions, sequences, triggers, roles, tablespaces, jobs, packages, etc.

7. oracle user password management

By creating a profile file, profile is a set of commands for password restrictions and resource restrictions.

(1) account locking

Create profile rule aaa: the user enters the wrong password three times and the account is locked for two days

Create profile aaa limit faild_login_attempts 3 password_lock_time 2;

Send the aaa lock rule to the user liudh.

Alter user liudh profile aaa; // note: the profile here is a keyword

(2) account unlock: (only dba has permission)

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 10 password_grace_time 2;

Grant the rule bbb to the user liudh

Alter user liudh profile bbb;

(4) password history:

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

Create profile passwordtime limit password_life_time 10 password_grace_time 2 password_reuse_time 10;

Delete A profile rule, which is generally cascading deletion.

Drop profile passwordtime cascade; // cascading Deletion