数据库学习记录01

1、创建数据库

create pluggable database lspdb admin user ls identified by 123
file_name_convert=('/home/oracle/app/oracle/oradata/orcl/pdbseed/','
/home/oracle/ app/oracle/oradata/orcl/lspdb');\|

2、给管理员赋予权限:

>   alter session set container=lspdb;
>   alter database open;
>   grant dba to ls;

3、在christpdb数据库中创建了表空间USER01，表空间USER02,表空间USER03,表空间USER04

>   create tablespace
>   space1
>   datafile '/home/oracle/app/oracle/oradata/orcl/lspdb/space1.dbf'
>   size 150M
>   autoextend on next 50m
>   maxsize unlimited;
>   create tablespace
>   space2
>   datafile '/home/oracle/app/oracle/oradata/orcl/lspdb/space2.dbf'
>   size 150M
>   autoextend on next 50m
>   maxsize unlimited;
>   create tablespace
>   space3
>   datafile '/home/oracle/app/oracle/oradata/orcl/lspdb/space3.dbf'
>   size 150M
>   autoextend on next 50m
>   maxsize unlimited;
>   create tablespace
>   space4
>   datafile '/home/oracle/app/oracle/oradata/orcl/lspdb/space4.dbf'
>   size 150M
>   autoextend on next 50m
>   maxsize unlimited;

4、查看数据库的使用情况，即查看表空间的数据库文件，以及每个文件的磁盘占用情况；

SELECT tablespace\_name,FILE\_NAME,BYTES/1024/1024 MB,
MAXBYTES/1024/1024 MAX\_MB,autoextensible FROM dba_data_files 
WHERE tablespace_name='USERS';
SELECT a.tablespace_name "表空间名",Total/1024/1024 "大小MB",
free/1024/1024 "剩余MB",( total - free )/1024/1024 "使用MB",
Round(( total - free )/ total,4)\* 100 "使用率%"
from (SELECT tablespace\_name,Sum(bytes)free
FROM dba_free_space group BY tablespace_name)a,
(SELECT tablespace\_name,Sum(bytes)total FROM dba_data_files 
group BYtablespace_name)b
where a.tablespace_name = b.tablespace_name;

5、创建表：

create table classbase (
	classid varchar2(20) not null primary key,
	classname varchar2(20) not null,
	credit varchar2(50) not null
)TABLESPACE space1;

create table objectbase (
	objectid varchar2(20) not null primary key,
	objectname varchar2(20) not null**
)TABLESPACE space1;

create table userlogin(
	userid varchar2(20) not null primary key,
	password varchar2(20) not null,
	usertype varchar(10) not null,
)TABLESPACE space1;

create table userbase(
	userid varchar2(20) not null,
	studentid varchar2(20) not null primary key,
	name varchar2(20) not null,
	subject varchar2(20) not null,
	year\_DATE DATE NOT NULL
	classid varchar2(20) not null,
	CONSTRAINT userbase\_ classbase \_fk1 FOREIGN KEY(classid) REFERENCESclassbase (classid)
	)partition by range (year\_DATE)
	(
	partition p1 values LESS THAN (TO\_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) tablespace space1,
	partition p2 values LESS THAN (TO\_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace space2,
	partition p3 values LESS THAN (TO\_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace space3,
	partition p4 values LESS THAN (MAXVALUE) tablespace space4
);

create table objectrelative (
	objectid varchar2(20) not null,
	studentid varchar2(20) not null,
	result varchar2(20) not null,
	score varchar2(5) not null,
	nopass varchar2(5) not null,
CONSTRAINT objectrelative \_ objectbase \_fk1 FOREIGN KEY (objectid)
REFERENCES objectbase (objectid),
CONSTRAINT objectrelative \_ objectbase \_fk1 FOREIGN KEY (studentid)
REFERENCES userbase (studentid)
)tablespace space1;

分别创建两个用户user1和user2,并授权和分配表空间space1；

create user user1 IDENTIFIED by 123;
create user user2 IDENTIFIED by 123;
alter user user1 quota unlimited on space1;
alter user user2 quota unlimited on space1;

创建两个角色cb_role1，cb_role2，并分别赋予cb_role1，cb_role2读任何表和读、修改任何表的权利；

>   create role role1;
>   create role role2;
>   grant select any table to role1;
>   grant select any table to role2;
>   grant update any table to role2;

4、将cb_role1，cb_role2分别赋予给用户cb_user1，cb_user2：

>   grant role1 to user1;
>   grant role2 to user2;

**5、**向表中插入数据：

declare
	dt date;
	dan_no char(5);
	zhan_no char(5);
	dan_class char(15);
	exit\_ char(20);
	price float;
begin

	insert into classbase (classid ,classname ,credit) values
	('1001','软工1班','计算机');
	insert into classbase (classid ,classname ,credit) values
	('1002','软工2班','计算机');
	insert into classbase (classid ,classname ,credit) values
	('1003','软工3班','计算机');
	insert into classbase (classid ,classname ,credit) values
	('1005','学前3班','教育');
	insert into classbase (classid ,classname ,credit) values
	('1006','学前2班','教育');
	insert into classbase (classid ,classname ,credit) values
	('1004','学前1班','教育');
	insert into objectbase (objectid,objectname) values ('0005','马克思');
	insert into objectbase (objectid,objectname) values ('0006','毛概');
	insert into objectbase (objectid,objectname) values ('0007','历史');
	insert into objectbase (objectid,objectname) values ('0011','离散');
	insert into objectbase (objectid,objectname) values ('0012','概率');

for i in 1..50000
loop
	if i mod 3 =0 then
		dt:=to_date('2018-01-01','yyyy-mm-dd')+(i mod 60);
	--PARTITION_2018
	elsif i mod 6 =1 then
		dt:=to_date('2019-01-01','yyyy-mm-dd')+(i mod 60);
	--PARTITION_2019
	elsif i mod 6 =2 then
	dt:=to_date('2020-01-01','yyyy-mm-dd')+(i mod 60);
--PARTITION_2020
end if;
	dan\_no := i;
	zhan\_no := case i mod 6 when 0 then '1001' when 1 then '1002' when 2 then
'1003' when 4 then '1004' else '1005'end;
	dan_class := CASE i MOD 6 WHEN 0 THEN '马克思' WHEN 1 THEN '毛概' WHEN 2
THEN'历史'WHEN 3 THEN '离散' ELSE '概率' END;
exit\_ := CASE i MOD 2 WHEN 0 THEN '是' ELSE '否' end;
price := dbms_random.value(5,30);
insert into userbase (dan_no,zhan_no,dan_class,exit_,price,dan_date) 
values (dan\_no,zhan\_no,dan_class,exit_,price,dt);
end loop;
end;

6、连接验证：让user1连接数据库lspdb,让其查看ls的userbase表

grant connect to user1;
grant connect to user2;

SELECT * FROM CLASSBASE;
SELECT * FROM USERBASE;

userbase表中的year_DATE字段根据时间段的不同，将其放在了不同的表空间里面,验证表空间使用情况如下：

7、 包的建立

建立一个程序包，里面有一个函数和一个存储过程，其功能分别为：统计一段时间内的挂号总收入；统计一段时间内，各个用户表的挂号数。具体过程如下：

create or replace PACKAGE function1 IS
FUNCTION Get_TOTAL(dt1 char,dt2 char) RETURN NUMBER;
PROCEDURE get_people(dt1 char,dt2 char);
END function1;

create or replace PACKAGE BODY function1 IS
FUNCTION Get_TOTAL(dt1 char,dt2 char) RETURN NUMBER
AS
N NUMBER;
BEGIN

select sum(price) into N from WEIXIUDAN where DAN_DATE >=
to_date(dt1,‘yyyy-mm-dd hh24:mi:ss’)

and DAN_DATE <= to_date(dt2,‘yyyy-mm-dd hh24:mi:ss’);
RETURN N;
END;
PROCEDURE get_people(dt1 char,dt2 char)
AS
a1 NUMBER;
b1 NUMBER;
c1 NUMBER;
d1 NUMBER;
e1 NUMBER;
f1 NUMBER;
cursor cur is

select * from WEIXIUDAN where DAN_DATE >= to.date(dt1,‘yyyy-mm-dd
hh24:mi:ss’)

and DAN_DATE <= to_date(dt2,‘yyyy-mm-dd hh24:mi:ss’);
begin
a1 := 0;
b1 := 0;
c1 := 0;
d1 := 0;
e1 := 0;
f1 := 0;
–使用游标

for v in cur
LOOP
if v.dan_class = '马克思’
then a1 := a1 + 1;
elsif v.dan_class = '毛概’
then b1 := b1 + 1;
elsif v.dan_class = '历史’
then c1 := c1 + 1;
elsif v.dan_class = '离散’
then d1 := d1 + 1;
elsif v.dan_class = '概率’
then e1 := e1 + 1;
end if;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘马克思为：’ || a1);
DBMS_OUTPUT.PUT_LINE(‘毛概为：’ || b1);
DBMS_OUTPUT.PUT_LINE(‘历史为：’ || c1);
DBMS_OUTPUT.PUT_LINE(‘离散为：’ || d1);
DBMS_OUTPUT.PUT_LINE(‘概率为：’ || e1);
end;

END function1;

8、备份

8.1. 开始全备份

步骤1：cat rman_level0.sh

步骤2：./rman_level0.sh

8.2 查看备份文件

• *.log是日志文件
• dblv0*.bak是数据库的备份文件
• arclv0*.bak是归档日期的备份文件
• c-1392946895-20191120-01是控制文件和参数的备份

8.3 查看备份文件的内容

rman target /

RMAN> list backup;

8.4 备份后修改数据

sqlplus study/123@pdborcl；
create table t1 (id number,name varchar2(50));
insert into t1 values(1,‘zhang’);
commit;

–查看修改情况：select * from t1;

8.5删除数据库文件，模拟数据库文件损坏；

rm /home/oracle/app/oracle/oradata/orcl/pdborcl/SAMPLE_SCHEMA_users01.dbf;

8.6删除数据库文件后修改数据：删除数据文件后，仍然可以增加一条数据。这是因为增加的数据并没有写入数据文件，而是写到了日志文件中。如果增加的数据较多的时候，就会出问题了；

sqlplus study/123@pdborcl；
insert into t1 values(2,‘wang’);
commit;

8.7重启损坏的数据库到mount状态

通过shutdown immediate无法正常关闭数据库，只能通过shutdown
abort强制关闭。然后将数据库启动到mount状态。

8.8 开始恢复数据库

rman target /;
[RMAN>] : restore database ;
recover database;
alter database open;

8.9 查询数据是否恢复