Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the structure and basic operation of oracle database". In the daily operation, I believe that many people have doubts about the structure and basic operation of oracle database. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "the structure and basic operation of oracle database". Next, please follow the editor to study!

Introduction to the system

ORACLE database system is a group of software products with distributed database as the core provided by ORACLE (Oracle). It is one of the most popular client / server (CLIENT/SERVER) or Bhand S architecture databases.

As shown in the figure:

SGA--- provides services for databases

PGA--- provides services for users

The whole system is mainly divided into two parts:

(1) instance-management end

Memory structure-SGA: shared pool data buffer log buffer

Background process-maintain interaction with the database

(2) Database

Data file control file log file

Characteristics

Complete data management function

Products with complete relationships

Distributed processing function

The operation of data warehouse can be easily realized with ORACLE.

Logical structure

Logical structure consists of logical storage structure (table space, segment, range, block) and logical data structure (table, view, sequence, stored procedure, synonym, index, cluster and database chain, etc.). The schema objects (logical data structures) and relationships form the relational design of the database.

Segment: a logical storage structure of a specified type in a table space that consists of one or more ranges, and the segment will occupy and grow the storage space.

Extent: the logical unit of database storage space allocation. A range consists of many contiguous data blocks, and the range is allocated by segments in turn.

Data block (Block): the smallest unit in which a database performs IO operations. An oracle database is a unit of multiple Oracle database blocks.

Tablespace

System: system tablespace, which stores management information about tablespace name, control file, data file and so on. It belongs to sys and system mode. Cannot delete or rename

Sysaux: auxiliary system tablespaces to reduce system tablespace load and improve efficiency

Temp: temporary tablespace for temporary tables and temporary data for sorting

Users: user tablespace, which stores permanent user objects and private information, and also becomes database tablespace. Each database should have a user tablespace that is assigned to the user when the user is created

Undo: redo tablespaces to help roll back uncommitted transaction data

File structure

The physical storage structure of the database is composed of a variety of physical files, mainly including:

Control file: a binary file that stores information such as instances, data files, and log files.

Data file: stores data with a .dbf suffix. In a word: a tablespace for multiple data files, a data file for only one tablespace.

Redo log file: used for instance recovery of the database.

Log file: record database modification information.

Parameter file: record the basic parameters.

Password file: allows sysdba, sysoper, and sysasm to remotely connect to the instance and perform administrative tasks.

Archive log files: use these files to make a good database backup and recover lost data files.

Warning file: show parameter background_dump_dest--- connects using a shared server

Trace files: show parameter user_dump_dest--- connects using a dedicated server

Basic operation 1. Database opening and closing

/ / enter the database

Su-oraclesqlplus / as sysdba # Log in to SQL as the highest administrator sysdba > help index # View Command list SQL > show user # View current user

/ / enable and close the database (three phases)

SQL > startup # on: instance-database mount-database open SQL > shutdown immediate # close: database shutdown-uninstall database-instance

/ / enable and disable monitoring

Lnsrctl start

Lnsrctl stop

Second, create a database

Dbca

# create a database (execute export DISPLAY=:0.0 if the window is not popped)

Successful execution will jump out of the create database page, follow these steps

Then wait for the installation progress to be completed!

Table space operation

/ / create a tablespace

SQL > create tablespace tbs_work

Datafile'/ orc/app/oracle/oradata/aaa01.dbf'

Size 10m autoextend on; # supports automatic extension

/ / resize the tablespace

Method 1: modify the space size

SQL > alter database datafile

'/ orc/app/oracle/oradata/aaa01.dbf'

Resize 80M

Method 2: add data files

SQL > alter tablespace tbs_work

Add datafile

'/ orc/app/oracle/oradata/aaa02.dbf'

Size 40M autoextend on

/ / tablespace permissions

Alter tablespace tbs_work read only

Alter tablespace tbs_work write read

/ / Delete tablespace

Drop tablespaces tbs_work including contents

4. CDB and PDB operation

/ / query the current container

SQL > startup

SQL > show con_name

/ / query all containers in the database

SQL > show pdbs

/ / switch from CDB to PDB

SQL > alter pluggable database orclpdb open; # modify pluggable library orclpdb open status SQL > alter session set container=orclpdb; # CDB switch session to PDB

/ / switch from PDB to CDB

SQL > shutdown immediate # close plugging database SQL in PBD > startup # Open pluggable database SQL > alter session set container=cdb$root; # switch session to CDB in PBD

5. User management

/ / create a tablespace

SQL > create tablespace school 2 datafile'/ orc/app/oracle/oradata/school01.dbf' 3 size 100m

/ / create a user (created without permission, so you can't log in)

SQL > create user c##jack 2 identified by 123123 3 default tablespace school # default tablespace 4 temporary tablespace temp # define temporary tablespace 5 quota unlimited on school # No quota 6 password expire; # set password, modify next login

/ / change the user's password

SQL > alter user c##jack identified by abc123

/ / Delete a user

SQL > drop user c##jack cascade

/ / user authorization

SQL > grant connect, resource to caterpillar jerrys; # Grant permissions to connect and manage databases

SQL > revoke connect, resource from c##jerry

/ / user login

Sqlplus-c##jerry (user name)-123123 (password)-new password-login succeeded

/ / user handoff

Coon sys/abc123 as sysdba # switch to sysdba

Conn---c##jerry---123123 # switch to c##jerry

VI. Operation of the table

Create table info

2 (

3 id number (4) constraint PK_id primary key, # constraint PK_id: constraint-constraint name

4 name varchar2 (10)

5 score number (5par 2)

6 born date

7 address varchar2 (50)

8)

/ / View the table structure

Desc info

/ / insert data

Insert into info values ('2018-10-9 century recordable YYYYMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMUDD') ('2018-10-9))

/ / View the form

Select * from info

VII. Affairs

Transaction: either the sql statement is executed successfully or none of it is executed without violating the constraint.

/ / transaction commit

Insert into info values (# insert data: # insert data: # insert data

Commit; # submit

/ / transaction rollback

Insert into info values (3 recordwangwuzhongjia 77 recordnullpendium null); # insert data

Rollback; # rollback

/ / set automatic submission

Set autocommit on; # auto commit, rollback invalid

Set autocommit off; # turn off autocommit

At this point, the study of "the structure and basic operation of oracle database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.