Oracle
1. Oracle architecture

2. Storage structure of Oracle
It is divided into physical storage structure and logical storage structure, as shown in the following figure:

Physical storage:

Control file (. ctl), log file (. log), data file (. dbf/.ora)

Data file:

• Data file is the physical storage unit of the database.
  The data of the database is stored in the table space. It is actually in one or more data files. Once the data file is added to a table space, the file cannot be deleted. If you want to delete a data file, you can only delete the table space to which the period belongs

• Table data is put into a table space by the user, and this table space will randomly put these data into one or more data files.

• oracle database is managed and stored by users and table spaces. Tables are not queried by tablespaces, but by users.

Logical structure:

Segments / extents / datablocks, tablespaces

From a storage perspective, there are only segments:

• Segments exist in the tablespace. A segment is a collection of segments.
• An area is a collection of data blocks.
• Data blocks are mapped to disk blocks.

Tablespaces:

• Table space is oracle's logical mapping of related data on the physical database,
• A database can be logically divided into one or several table spaces, and each table space contains a logically related set of structures.
• **Each database instance has at least one table space (system table space), and each table space is composed of one or more files on the same disk, * * these files are called data files, and a data file can only belong to one table space

Storage structure of database:

example:
An Oracle instance consists of a series of background processes and memory structures. An Oracle database can have n instances

User:
Users are created under instances. Different instances can create users with the same name

3. Oracle 12c create sample database

Before 12c, if the sample database is specified to be installed during oracle installation, the system will create a user scott/tiger by default

Create user:

Log in as super administrator
Mode 1:
Create a new command window and use the command to create

-- establish scott user
 create user scott identified by tiger;
 --by scoot Grant authority
  Grant connect,RESOURCE,UNLIMITED TABLESPACE TO scott;
  --Set the tablespace used by the user
  ALTER USER scott default tablespace users;
  --Set user's temporary tablespace
  Alter user scott Temporary tablespace  temp;

Mode 2:
Create a new user in user

Mode 3:

4. Table related operations

1 . Create table

To create a table, you must specify a size indicating the listed data type
The precondition is that the user must have the permission to create table

Create table  dept(
	deptno  Number(2),
	dname   Varchar2(20),
	loc  varchar2(100)
)

oracle data type

data type	describe
varchar2(size)	Variable length string
char(size)	Fixed length string
number(p,n)	Variable length value type
date	Date type
long	Variable length character data up to 2G
clob	Up to 4G character data
Bob	Binary data up to 4G

2 . alter table:

-- Add a new column. The additional new column must be at the end
alter table dept add(dnum number(2)  default 10); 
--Modify existing column data type size defaults
alter table dept modify (dnum number(3) default 100);
--Delete column 
alter table dept drop COLUMN dnum;
--Heavy life list
alter table dept rename COLUMN loc to location;

3 . Delete table

--Delete table
drop table department;

4 . Clear data in table

--Empty table
delete from dept;
truncate table dept;

be careful:

• delete deletes the data in the table. The deleted data can be rolled back before being submitted without releasing the occupied storage space and resources

• truncate clears the data and deletes all the data in the table. After deletion, it cannot be rolled back to free the occupied storage space and resources

5 . rename table

--rename table 
rename dept to department;

6 . Create sample database and initialization table

-- Create table
-- Department table
create table dept(
       deptno number(2) constraint pk_dept primary key,--Department number
       dname varchar2(14),--Department name
       loc varchar2(13)--Location of Department
);
-- Initialization table
-- Insert test data—— dept
INSERT INTO dept VALUES    (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES    (30,'SALES','CHICAGO');
INSERT INTO dept VALUES    (40,'OPERATIONS','BOSTON');