Oracle Database Foundation

Introduction to 1.Oracle databases

What is the difference between a database system and a database management system?

parsing: Database system = Database management system +oper+ operator + hardware

Oracle Database management system

Core products of Oracle Corporation

Currently the most popular database

Major Version oracle8i/9i (Internet),oracle10g/11g (GRID)

based on C/S system structure

2. Oracle main components

PGA:

SGA: system Global Area systems globals

DBWR(Read and write data files)

LGWR (log read and write)

Pmon (monitoring data running state) after an abnormally interrupted cleanup, processing, and freeing of resources

Smon (Cleanup temp table space)

CKPT Checkpoint(checkpoint guarantees database consistency, which means writing dirty data to the hard disk to ensure that the memory and data on the hard disk are the same)

Database

A collection of data stored on disk

Physical representation of data files, log files, control files, etc.

Logically present in tablespace form

You must first create a database before you can use Oracle

DB instance

Each database that is launched corresponds to a database instance that is accessed and controlled by the database

in order to run the database, The composition of all processes and allocated memory structures that are run by the Oracle system

Data files

the extension is . DBF, the file used to store database data

There is no one-to-one correspondence between database tables and data files

Control files

the extension is . CTL, which is required to start and run the database

The default contains 3 control files, the contents of each control file are the same

Log file

the extension is . Log, which records all changes to the data

Looping between multiple log file groups

2. relationship of instance and database

parsing:

Instance: After the database service starts, the cells in memory

database: Files on a hard disk

4.Oracle Installation attention is a matter

The path does not have Chinese  Special Characters  Space (any path)

5. location of the listening file

6. start Oracle Services

opening sequence: Start the listening service first, then turn on Oracle Services

Tnslistener Monitoring Service

ORACLESERVICEORCL Oracle Services

connecting to a database :

Common Ways to connect:

6. Database User

SYS: Super admin manages normal admin and normal user

System: General Administrator

Scott: Regular users

7. Data Type

6. Roles and Permissions

Note: In Oracle, the table is isolated based on the user .

Each user sees only the tables that they create

Create users and authorizations

 1  --  2  create  user  Liutao identified by  123  3  --   authorization  5  grant  connect to   Liutao  6  to  Liutao 

Oracle includes two classes of permissions (privileges), one for system permissions and the other for object permissions .

CONNECTrole:--are the typical rights granted to end users, the most basic
CREATE SESSION--Establish a session 
RESOURCErole:--is granted to developers 
CREATE CLUSTER--Build a cluster 
CREATE PROCEDURE--Build Process 
CREATE SEQUENCE--set up a sequence 
CREATE TABLE--Build Table 
CREATE TRIGGER--Creating triggers 
CREATE TYPE--Build Type
CREATE OPERATOR--Create operator
CREATE Indextype--Create an index type

DBA: Has full privileges, is the highest system privilege, and only the DBA can create the database structure.

RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure.

Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure.

for normal users: Grant Connect, resource permissions.
for DBA Administration User: Grant Connect,resource, dba authority.

7. How to view all the tables that the user manages

1 -- How to view all the tables that the user manages 2 Select *  from where owner='liutao'

6. Pseudo-Column

What is a pseudo-column?

a pseudo-column in Oracle is like a column in a table, but it is not stored in a table, and a pseudo-column can be queried in a table, but cannot be added, deleted, and modified.

Common pseudo-columns have ROWID and ROWNUM

1. ROWID

2.ROWNUM

ROWNUM is the ordinal of the row in the result set returned by the query, which you can use to limit the number of rows returned by the query

Question one:

Data Sheet

--Query the record of natural sequential query

1 Select *  from 2 (3    Select STUDENT. *  from STUDENT 4 )temp5where rn=5

--4-5 records on the second page of the paging query

1 Select *  from2 (3    Select Temp.*, ROWNUM RN from 4    (5     Select *  fromSTUDENT6)Temp7 whereROWNUM<=68 )9 whereRn>=4

--between and notation

1 SELECT *  from 2 (3 SELECTA.*, ROWNUM RN4  from(SELECT *  fromSTUDENT) A5 )6 WHERERnbetween 4  and 6

Oracle Database Foundation