Basic concepts of Oracle Database

Author: threedayman

Basic concepts

database

oracle database is the physical storage of data. Including data file ORA or DBF, control file, online file, log file and parameter file. oracle has only one database, which is managed by users. The process of creating a database in mysql database is to create a database → create a table, while the process of creating a database in oracle is to create a table space → create a user → create a table by the user.

example

An oracle instance consists of a series of background processes and memory structures. A database can have multiple instances. Usually we only use one example.

data file

The data file is the physical storage unit of the database. The data of oracle database is stored in table space, but it is really stored in one or more data files. A tablespace can be composed of one or more data files, and a data file can only belong to one tablespace

Tablespace

Table space is the logical mapping of oracle to related data files on the physical database. A database is logically divided into one or several table spaces, and each table space contains a set of logically related structures.

user

The creating user must specify a table space for it. If no default table space is explicitly specified, it is specified as the users table space; After creating a user, you can create tables, stored procedures and other database objects on the user;

Practice creating tables and inserting data

After logging in to the linux server, switch to the oracle user through su oracle and execute sqlplus / as SYSDBA

bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 18 17:05:41 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

Execute select name from v$datafile; Statement query table space data file storage path

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/system01.dbf
/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/users01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf

11 rows selected.

Create tablespace

CREATE TABLESPACE baktest datafile '/opt/oracle/oradata/ORCLCDB/baktest.dbf' size 600M autoextend on next 50m maxsize unlimited;

Create local user

View pdb container

SQL> select NAME,OPEN_MODE from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
ORCLPDB1
READ WRITE

Switch the container to the pdb (you also need to be in the container to create the tablespace)

SQL> alter session set container=ORCLPDB1;

Session altered.

Create user

SQL> CREATE USER testUser IDENTIFIED BY 123456 DEFAULT TABLESPACE baktest;

User created.

User authorization

SQL> grant connect,resource,dba to testUser;

Grant succeeded.

Posted by AP81 on Mon, 29 Nov 2021 19:03:07 -0800

Programmer Group