Oracle创建数据库、表空间、用户和新建表操作_oracle新建数据库实例和新建用户

一、创建数据库移步之前的文章：

Oracle 19c 创建数据库（详细明了）

二、创建表空间等：

1、创建表空间stu_data,100m,自动扩展表空间

create tablespace stu_data datafile 'D:\oracle\oradata\STUDENT\stu_data.dbf' size 100m
AUTOEXTEND on next 2m logging
1
2

2、查看表空间

select tablespace_name,contents from dba_tablespaces
1

3、创建用户studba,口令：student2020，指定默认表空间stu_data,临时表空间temp，具有创建表，创建视图，创建序列，创建对话，对表空间大小不加限制权限，和授予用户最典型、开发人员和系统管理角色

create user studba IDENTIFIED BY studba2020 
default TABLESPACE stu_data
TEMPORARY TABLESPACE temp; 

GRANT create table,create view,create sequence,create session,
unlimited tablespace TO studba;
GRANT connect TO studba;
GRANT resource TO studba;
GRANT dba TO studba;
1
2
3
4
5
6
7
8
9

4、使用studba用户创建用户表（包括主键、外键），存放在stu_data表空间

drop table usertab purge;
create table usertab 
(utid number(6) generated by default as identity(start with 1 increment by 1),
uname varchar2(20) not null,
upwd varchar2(50) not null,
uright char(1) default '2',
constraint pk_ytid primary key(utid))TABLESPACE stu_data
storage(initial 256k)  --数据区初始大小为256K
PCTFREE 5 --空闲空间的比例为5%
pctused 60 --表示该数据块中有60%的空间可用于插入新数据
initrans 10;  --设置当前表使用用户的并发数量
1
2
3
4
5
6
7
8
9
10
11

查看表名

select table_name,tablespace_name from user_tables
1

5、使用studba用户创建其他数据表（包括主键，外键），存放在stu_data表空间；

dorp table student_tab purge;
create table student_tab
(xh varchar2(11) not null primary key,
xm char(8) not null,
xb char(4) default'男',
cssj date not null,
zy char(12),
zxf number(2) default 0,
bz varchar2(200))
tablespace stu_data
storage(initial 256k)
pctfree 5
pctused 60
initrans 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

6、插入数据，每张表10条数据以上

insert into usertab(Uname,Upwd.Uright) values('a','122343','2');
insert into usertab(Uname,Upwd,Uright) values('b','12ed44','2');
insert into usertab(Uname,Upwd,Uright) values('c','3278js','2');
insert into usertab(Uname,Upwd,Uright) values('d','123456','1');
insert into usertab(Uname,Upwd,Uright) values('e','shwujwe','2');
insert into usertab(Uname,Upwd,Uright) values('f','3isjss','1');
insert into usertab(Uname,Upwd,Uright) values('g','123456','2');
insert into usertab(Uname,Upwd,Uright) values('h','hwu291i','1');
insert into usertab(Uname,Upwd,Uright) values('i','123456','2');
insert into usertab(Uname,Upwd,Uright) values('j','773628','2');
insert into usertab(Uname,Upwd,Uright) values('k','123456','2');
insert into usertab(Uname,Upwd,Uright) values('l','123456','1');
1
2
3
4
5
6
7
8
9
10
11
12

查看usertab表信息，命令如下:

select * from usertab
1

对student_tab表插入12条数据，命令如下:

insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120107','hhq','女',TO_DATE('2002-05-18','yyyy-mm-dd'),'信息20-1',50,'无');
insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120104','hzt','女',TO_DATE('2001-12-27','yyyy-mm-dd'),'信息20-1',50,'无');
insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120101','zlw','女',TO_DATE('2000-10-24','yyyy-mm-dd'),'信息20-1',50,'无');
insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120102','zdh','女',TO_DATE('2002-07-18','yyyy-mm-dd'),'信息20-1',50,'无');
insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120103','lqq','女',TO_DATE('2002-05-21','yyyy-mm-dd'),'信息20-1',50,'无');
insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120105','ccf','女',TO_DATE('2003-01-01','yyyy-mm-dd'),'信息20-1',50,'无');
insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120106','lst','女',TO_DATE('2002-02-17','yyyy-mm-dd'),'信息20-1',50,'无');
insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120108','zs','男',TO_DATE('2002-10-18','yyyy-mm-dd'),'信息20-1',50,'无');
insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120207','ls','男',TO_DATE('2001-10-10','yyyy-mm-dd'),'信息20-2',50,'无');
insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120209','ww','男',TO_DATE('2002-12-18','yyyy-mm-dd'),'信息20-1',50,'无');
insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120210','jaa','女',TO_DATE('2000-02-18','yyyy-mm-dd'),'信息20-2',50,'无');
1
2
3
4
5
6
7
8
9
10
11

觉得不错的话点个赞叭~