oracle备份恢复

基于Oracle的书籍交易系统数据库设计

1.创建表空间

• space_qhl001

Create Tablespace space_qhl001
datafile
'/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest_qhl001_1.dbf'
  SIZE 100M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED,
'/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest_qhl001_2.dbf'
  SIZE 100M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

• space_qhl002

Create Tablespace space_qhl002
datafile
'/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest_qhl002_1.dbf'
  SIZE 100M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED,
'/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest_qhl002_2.dbf'
  SIZE 100M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

2. 创建角色及用户

创建第一个角色和用户

• 创建角色qhl1将connect,resource,create view授权给qhl1
• 创建用户qhl_1
• 分配60M空间给qhl_1并将角色qhl1授权给用户qhl_1

CREATE ROLE qhl1;

GRANT connect,resource,CREATE VIEW TO qhl1;

CREATE USER qhl_1 IDENTIFIED BY 123 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

ALTER USER qhl_1 QUOTA 60M ON users;

GRANT qhl1 TO qhl_1;

创建第二个角色和用户

• 创建角色qhl2，将connect,resource权限给qhl2
• 创建用户qhl_2
• 分配60M空间给qhl_2并将角色qhl2授权给用户qhl_2

CREATE ROLE qhl2;

GRANT connect,resource TO qhl2;

CREATE USER qhl_2 IDENTIFIED BY 123 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

ALTER USER qhl_2 QUOTA 60M ON users;

GRANT qhl2 TO qhl_2;

2. 在用户qhl1下创建表

创建管理员表

• id为主键

CREATE TABLE ADMINISTRATOR 
(
  ID NUMBER(*, 0) NOT NULL 
, PASSWORD VARCHAR2(20 BYTE) NOT NULL 
, ADMIN VARCHAR2(20 BYTE) NOT NULL 
, CONSTRAINT ADMINISTRATOR_PK PRIMARY KEY 
  (
    ID 
  )
  USING INDEX 
  (
      CREATE UNIQUE INDEX ADMINISTRATOR_PK ON ADMINISTRATOR (ID ASC) 
      LOGGING 
      TABLESPACE USERS 
      PCTFREE 10 
      INITRANS 2 
      STORAGE 
      ( 
        BUFFER_POOL DEFAULT 
      ) 
      NOPARALLEL 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE USERS 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  BUFFER_POOL DEFAULT 
) 
NOCOMPRESS 
NO INMEMORY 
NOPARALLEL;

创建用户表

• id为主键
• 根据注册日期按范围分区
• 分为2018和2019年两个分区，每年按季度划4个子分区

CREATE TABLE BOOKUSER 
(
  ID INT NOT NULL 
, PASSWORD VARCHAR2(20) NOT NULL 
, USERNAME VARCHAR2(50) NOT NULL 
, PHONE VARCHAR2(20) NOT NULL 
, ADDRESS VARCHAR2(30) NOT NULL 
, REGISTRATIONDATE DATE NOT NULL 
, CONSTRAINT U_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
) 
PARTITION BY RANGE (REGISTRATIONDATE) 
SUBPARTITION BY RANGE (REGISTRATIONDATE) 
(
  PARTITION DATE2018 VALUES LESS THAN (TO_DATE(' 2018-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
 'NLS_CALENDAR=GREGORIAN')) 
  (
    SUBPARTITION DATE2018_3 VALUES LESS THAN (TO_DATE(' 2018-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
 'NLS_CALENDAR=GREGORIAN'))  
  , SUBPARTITION DATE2018_6 VALUES LESS THAN (TO_DATE(' 2018-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
 'NLS_CALENDAR=GREGORIAN'))  
  , SUBPARTITION DATE2018_9 VALUES LESS THAN (TO_DATE(' 2018-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
 'NLS_CALENDAR=GREGORIAN'))  
  , SUBPARTITION DATE2018_12 VALUES LESS THAN (TO_DATE(' 2018-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
 'NLS_CALENDAR=GREGORIAN'))  
  )  
, PARTITION DATE2019 VALUES LESS THAN (TO_DATE(' 2019-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
 'NLS_CALENDAR=GREGORIAN')) 
  (
    SUBPARTITION DATE2019_3 VALUES LESS THAN (TO_DATE(' 2019-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
 'NLS_CALENDAR=GREGORIAN'))  
  , SUBPARTITION DATE2019_6 VALUES LESS THAN (TO_DATE(' 2019-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
 'NLS_CALENDAR=GREGORIAN'))  
  , SUBPARTITION DATE2019_9 VALUES LESS THAN (TO_DATE(' 2019-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
 'NLS_CALENDAR=GREGORIAN'))  
  , SUBPARTITION DATE2019_12 VALUES LESS THAN (TO_DATE(' 2019-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
 'NLS_CALENDAR=GREGORIAN'))  
  )  
);

创建商品表

CREATE TABLE COMMODITY 
(
  ID INT NOT NULL 
, PID INT NOT NULL 
, BOOKSNAME VARCHAR2(20) NOT NULL 
, PRICE NUMBER NOT NULL 
, DESCRIBE VARCHAR2(50) NOT NULL 
, NUM INT NOT NULL 
, ADMIN_ID INT NOT NULL 
, CONSTRAINT COMMODITY_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

创建购物车表

• 用户表字段BOOKUSER_ID为购物车表的外键
• 购物车采用引用分区

CREATE TABLE CART 
(
  ID INT NOT NULL 
, AMOUNT INT NOT NULL 
, PID INT NOT NULL 
, BOOKUSER_ID INT NOT NULL 
, CONSTRAINT CART_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
, CONSTRAINT CART_BOOKUSER FOREIGN KEY
  (
  BOOKUSER_ID
  )
  REFERENCES BOOKUSER
  (
  CART_ID 
  )
  ENABLE 
) 
PARTITION BY REFERENCE (CART_BOOKUSER);

论坛表

CREATE TABLE TABLE1 
(
  ID INT NOT NULL 
, CONTENT NVARCHAR2(50) NOT NULL 
, CONSTRAINT TABLE1_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

创建完成后表结构如下