title: "Oracle创建表空间和表"
date: 2021-02-01T20:17:20+08:00
draft: true
tags: ['oracle']
author: "dadigang"
author_cn: "大地缸"
personal: "http://www.real007.cn"
关于作者
Oracle创建表空间和表
创建表空间和表ORACLE物理上是由磁盘上的以下几种文件:数据文件和控制文件和LOGFILE构成的oracle中的表就是一张存储数据的表。表空间是逻辑上的划分。方便管理的。数据表空间 (Tablespace) 存放数据总是需要空间， Oracle把一个数据库按功能划分若干空间来保存数据。当然数据存放在磁盘最终是以文件形式，所以一盘一个数据表空间包含一个以上的物理文件数据表 在仓库，我们可能有多间房子，每个房子又有多个货架，每架又有多层。 我们在数据库中存放数据，最终是数据表的单元来存储与管理的。数据文件 以上几个概念都是逻辑上的， 而数据文件则是物理上的。就是说，数据文件是真正“看得着的东西”，它在磁盘上以一个真实的文件体现
1、创建表空间：格式: create tablespace 表间名 datafile '数据文件名' size 表空间大小 create tablespace data_test datafile 'e:\oracle\oradata\test\data_1.dbf' size 2000M; create tablespace idx_test datafile 'e:\oracle\oradata\test\idx_1.dbf' size 2000M; (*数据文件名 包含全路径, 表空间大小 2000M 表是 2000兆) 2、建好tablespace, 就可以建用户了 格式: create user 用户名 identified by 密码 default tablespace 表空间表; create user study identified by study default tablespace data_test; (*我们创建一个用户名为 study,密码为 study, 缺少表空间为 data_test -这是在第二步建好的.) (*缺省表空间表示 用户study今后的数据如果没有专门指出，其数据就保存在 data_test中, 也就是保存在对应的物理文件 e:\oracle\oradata\test\data_1.dbf中)创建用户并指定表空间CREATE USER cici IDENTIFIED BY cici PROFILE DEFAULT DEFAULT TABLESPACE CICI ACCOUNT UNLOCK;create user jykl identified by jykl default tablespace jykl_data temporary tablespace jykl_temp;授权给新用户GRANT connect, resource TO cici;grant create session to cici;4. 授权给新用户 grant connect,resource to study; --表示把 connect,resource权限授予study用户 grant dba to study; --表示把 dba权限授予给 study5. 创建数据表 在上面，我们已建好了用户 study 我们现在进入该用户 sqlplusw study/study@test 然后就可以在用户study中创建数据表了 格式: create table 数据表名 oracle命令建立主键外键 1、创建一张学生表create table t_stu( stuid number(10) primary key, stuname varchar2(20) not null, stusex varchar2(2) default '男' check(stusex in('男','女'))); 2、创建一张课程表create table t_couse( couseid number(10) primary key, cousename varchar2(20) not null, cousetype varchar2(4)); 3、创建一张学生课程成绩表（包括主外键）create table t_score( scoreid number(10) primary key, stuid number(10) references t_stu(stuid), couseid number(10), constraint fk_couseid foreign key(couseid) references t_couse(couseid) on delete cascade); CREATE TABLE log(log_id int(10) unsigned NOT NULL auto_increment,log_time datetime NOT NULL,log_user varchar(30) NOT NULL,log_title varchar(30) default NULL,log_content text default NULL,PRIMARY KEY(log_id));
orale表管理：
Oracle创建表同SQL Server一样，使用CREATE TABLE命令来完成。创建约束则使用如下命令：
语法格式：alter table 命令
alter table 表名 add constraint 约束名 约束内容。
不论创建表还是约束，与SQL Server基本相同，注：在Oracle中default是一个值，而SQL Server中default是一个约束，因此Oracle的default设置可以在建表的时候创建。
案例1： 创建一个学生信息（INFOS）表和约束
Oracle创建表和约束
create table INFOS(
STUID varchar2(7) not null, --学号 学号=‘S’+班号+2位序号
STUNAME varchar2(10) not null, --姓名
GENDER varchar2(2) not null, --性别
AGE number(2) not null, --年龄
SEAT number(2) not null, --座号
ENROLLDATE date, --入学时间
STUADDRESS varchar2(50) default '地址不详', --住址
CLASSNO varchar2(4) not null, --班号 班号=学期序号+班级序号
);
/ ①
alter table INFOS add constraint pk_INFOS primary key(STUID) ②
/
alter table INFOS add constraint ck_INFOS_gender check(GENDER = '男' or GENDER = '女') ③
/
alter table INFOS add constraint ck_INFOS_SEAT check(SEAT >=0 and SEAT <=50) ④
/
alter table INFOS add constraint ck_INFOS_AGE check(AGE >=0 and AGE<=100) ⑤
/
alter table INFOS add constraint ck_INFOS_CLASSNO check((CLASSNO >='1001' and CLASSNO<='1999') or
(CLASSNO >='2001' and CLASSNO<='2999')) ⑥
/
alter table INFOS add constraint un_STUNAME unique(STUNAME) ⑦
/
代码解析：
① 在Oracle代码中，“/”执行缓存区中的语句，由于缓冲区中只存储一条刚刚保存过语句，由于每条语句没有用分号结尾，只是保存在缓冲区，因此每条语句后面都有单独一行“/”。
② 创建一个主键约束。
③ 与 ④ ⑤ ⑥ ⑦一起创建各种check约束。其中⑦是唯一约束，表示该列值是唯一的，列中的值不能重复。
Oracle中创建外键约束与SQL Server相同。比如：现有成绩表定义如下：
案例2： 创建一个成绩表（SCORES）表和约束
Oracle创建表和约束
create table scores(
ID number, --ID ①
TERM varchar2(2), --学期 S1或S2
STUID varchar2(7) not null, --学号
EXAMNO varchar2(7) not null, --考号 E+班号+序号
WRITTENSCORE number(4,1) not null, --笔试成绩
LABSCORE number(4,1) not null, --机试成绩
);
ALTER TABLE SCORES ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2')
/
ALTER TABLE SCORES ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID) ②
/
代码解析：
① SQL Server中可以使用identify创建自动增长列，但是Oracle中的自动增长需要借助序列（Sequence）完成，在后面章节中讲解。
② Oracle中的外键约束定义。
注意：表名，字段名要大写！
oracle建表、建主键、外键基本语法
-创建表格语法:
create table 表名(
字段名1 字段类型(长度) 是否为空,
字段名2 字段类型 是否为空 );
-增加主键
alter table 表名 add constraint 主键名 primary key (字段名1);
-增加外键:
alter table 表名 add constraint 外键名 foreign key(字段名1) references 关联表 (字段名2);
在建立表格时就指定主键和外键
create table T_STU(
STU_ID char(5) not null,
STU_NAME VARCHAR2(8) not null,
constraint PK_T_STU primary key (STU_ID));
主键和外键一起建立：
create table T_SCORE(
EXAM_SCORE number(5,2),
EXAM_DATE date,
AUTOID number(10) not null,
STU_ID char(5),
SUB_ID char(3),
constraint PK_T_SCORE primary key (AUTOID),
constraint FK_T_SCORE_REFE foreign key (STU_ID) references T_STU (STU_ID));
orale数据类型：
类型
含义
CHAR(length)
存储固定长度的字符串。参数length指定了长度，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节。
VARCHAR2(length)
存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。
NUMBER(p，s)
既可以存储浮点数，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分和小数部分和小数点，p默认是38为），s是指小数位数。可存负数
DATE
存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。
TIMESTAMP
不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区。
CLOB
存储大的文本，比如存储非结构化的XML文档
BLOB
存储二进制对象，如图形、视频、声音等。
2. 创建表时给字段加默认值 和约束条件
创建表时可以给字段加上默认值 例如 : 日期字段 DEFAULT SYSDATE 这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间
例如：IS_SEND NUMBER(1) default 1 --是否已发
创建表时可以给字段加上约束条件 例如: 非空 NOT NULL ，不允许重复 UNIQUE ，关键字 PRIMARY KEY ，按条件检查 CHECK (条件)， 外键 REFERENCES 表名(字段名)
3. 创建表的例子
create table DEPT(
DNAME varchar2(14),
LOC varchar2(6),
EPTNO number(2) constraint PK_DEPT primary KEY,
);
create table region(
ID number(2) not null primary KEY,
postcode number(6) default '0' not null,
areaname varchar2(30) default '' not null,
);
4. 创建表时的命名规则和注意事项
1)表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#
2)大小写不区分
3)不用SQL里的保留字, 一定要用时可用双引号把字符串括起来
4)用和实体或属性相关的英文符号长度有一定的限制
5）约束名的命名规则和语法 约束名的命名规则约束名如果在建表的时候没有指明，系统命名规则是SYS_Cn(n是数字) 约束名字符串的命名规则同于表和字段名的命名规则
6）使用约束时的注意事项 约束里不能用系统函数,如SYSDATE和别的表的字段比较 可以用本表内字段的比较
注意事项:
1)建表时可以用中文的字段名, 但最好还是用英文的字段名
2)创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面
3)建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引
4)一个表的最多字段个数也是有限制的，254个.
想在事务处理后, 做约束的
检查 SQL> alter session set constraints deferred.
7. 由实体关系图到创建表的例子 s_dept 前提条件:已有region表且含唯一关键字的字段id SQL> CREATE TABLE s_dept (id NUMBER(7) CONSTRAINT s_dept_id_pk PRIMARY KEY, name VARCHAR2(25) CONSTRAINT s_dept_name_nn NOT NULL, region_id NUMBER(7) CONSTRAINT s_dept_region_id_fk REFERENCES region (id), CONSTRAINT s_dept_name_region_id_uk UNIQUE(name, region_id));
8. 较复杂的创建表例子 SQL> CREATE TABLE s_emp (id NUMBER(7) CONSTRAINT s_emp_id_pk PRIMARY KEY, last_name VARCHAR2(25) CONSTRAINT s_emp_last_name_nn NOT NULL, first_name VARCHAR2(25), userid VARCHAR2(8) CONSTRAINT s_emp_userid_nn NOT NULL CONSTRAINT s_emp_userid_uk UNIQUE, start_date DATE DEFAULT SYSDATE, comments VARCHAR2(25), manager_id NUMBER(7), title VARCHAR2(25), dept_id NUMBER(7) CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id), salary NUMBER(11,2), commission_pct NUMBER(4,2) CONSTRAINT s_emp_commission_pct_ck CHECK (commission_pct IN(10,12.5,15,17.5,20)));
8. 通过子查询建表 通过子查询建表的例子 SQL>CREATE TABLE emp_41 AS SELECT id, last_name, userid, start_date FROM s_emp WHERE dept_id = 41;
SQL> CREATE TABLE A as select * from B where 1=2; 只要表的结构.
10. 用子查询建表的注意事项 1)可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。 2)用子查询方式建立的表，只有非空NOT NULL的约束条件能继承过来, 其它的约束条件和默认值都没有继承过来. 3)根据需要，可以用alter table add constraint ……再建立其它的约束条件，如primary key等.
11. Foreign Key的可选参数ON DELETE CASCADE 在创建Foreign Key时可以加可选参数: ON DELETE CASCADE它的含义是如果删除外键主表里的内容，子表里相关的内容将一起被删除. 如果没有ON DELETE CASCADE参数，子表里有内容，父表里的主关键字记录不能被删除掉.
12. 如果 数据库 表里有不满足的记录存在，建立约束条件将不会成功.
13. 给表创建和删除同义词的例子 SQL> CREATE SYNONYM d_sum 2 FOR dept_sum_vu;
SQL> CREATE PUBLIC SYNONYM s_dept 2 FOR alice.s_dept;
SQL> DROP SYNONYM s_dept;
ORACLE之新建 表
创建一个名为INSURES的表
create table INSURES
(
INSURE_NO CHAR(18) not null, --医保号
GETSURE_UNIT_NO CHAR(9) not null, --经办机构号
INSURE_NAME VARCHAR2(10) not null, --姓名
INSURE_SEX CHAR(1) not null, --性别
ID_CARD_NO CHAR(18) not null, --身份证号
);
创建/修改主键，唯一性约束和外键 这里INSURE_NO, GETSURE_UNIT_NO唯一性约束
alter table INSURES
add constraint UNQ_INSURES unique (INSURE_NO, GETSURE_UNIT_NO)
alter table TWN_SEED
add constraint UNQ_INSURES primary key (。。。。)
创建索引
create index IDX_INSURES on INSURES (GETSURE_UNIT_NO, SONSURE_UNIT_NO, UNIT_NO, FAMILY_NO, HOSPS_NO)
创建一个PK的时候，是自动创建一个与之对应的唯一索引的。 如果不特别指定，那么这个索引的表空间和表格的空间是一样的，但是我们不建议放在一起。
create table testone(
name varchar2(10 char))
TABLESPACE1;
ALTER TABLE TESTONE ADD CONSTRAINT PK_TESTONE1 PRIMARY KEY(NAME) USING INDEX TABLESPACE TABLESPACE2;
作为一种好习惯，不要把索引和表格的数据存在在同一个表空间中
Oracle创建表语法 - create
--创建Oracle表（使用create关键字）
-- \\\\\\
（1）创建新表 use 数据库（在那个数据库中建表） create table 表名
( 字段名1（列名） 数据类型 列的特征,
字段名2（列名） 数据类型 列的特征(NOT NULL),
...... )
（2）创建带有主键约束的表语法 create table 表名 (
字段名1（列名） 数据类型 列的特征,
字段名2 数据类型 列的特征(NOT NULL),
...... primary key(主键列字段))
（3）利用现有的表创建表 -- 注意：仅复制Oracle数据表结构：采用的是子查询方式 create table 新表 as select * from 旧的表 where 1=2
（4）利用现有的表的结构创建新表 -- 注意：仅复制Oracle数据表结构：采用的是子查询方式 create table 新表 select 字段1,字段2... from 旧的表 where 条件（旧的表字段满足的条件）
（5）利用现有的表的结构创建新表 -- 注意：复制Oracle数据表数据 create table 新表 as select * from 旧的表 where 1=1
（6）利用现有的表的结构创建新表 -- 注意：复制Oracle数据表数据 create table 新表 as select 字段1,字段2... from 旧的表 where 条件（旧的表字段满足的条件）（7）将查询结果插入另一张表 insert into 另一张表 select * from 要查询的表 where 条件（要查询的表的列符合什么条件）
- //建测试表
- create table dept(
deptno number(3) primary key,dname varchar2(10),loc varchar2(13));- create table employee_info(
empno number(3),deptno number(3),ename varchar2(10),sex char(1),phone number(11),address varchar2(50),introduce varchar2(100));- --
- 重命名
- 重命名表:rename dept to dt;
rename dt to dept;- 重命名列:alter table dept rename column loc to location;
- alter table dept rename column location to loc;
- 添加约束
- primary key
- alter table employee_info add constraint pk_emp_info primary key(empno);
- foreign key
- alter table employee_info add constraint fk_emp_info foreign key(deptno)
- references dept(deptno);
- check
- alter table employee_info add constraint ck_emp_info check
(sex in ('F','M'));- not null
- alter table employee_info modify phone constraint not_null_emp_info not null;
- unique
- alter table employee_info add constraint uq_emp_info unique(phone);
- default
- alter table employee_info modify sex char(2) default 'M';
- 添加列
- alter table employee_info add id varchar2(18);
- alter table employee_info add hiredate date default sysdate not null;
- 删除列
- alter table employee_info drop column introduce;
- 修改列
- 1.修改列的长度
- alter table dept modify loc varchar2(50);
- 修改列的精度
- alter table employee_info modify empno number(2);
- 修改列的数据类型
- alter table employee_info modify sex char(2);
- 修改默认值
- alter table employee_info modify hiredate default sysdate+1;
- 5.禁用约束
- alter table employee_info disable constraint uq_emp_info;
- 6.启用约束
- alter table employee_info enable constraint uq_emp_info;
- 7.延迟约束
- alter table employee_info drop constraint fk_emp_info;
- alter table employee_info add constraint fk_emp_info foreign key(deptno)
- references dept(deptno)
- deferrable initially deferred;
- 8.向表中添加注释
- comment on table employee_info is 'information of employees';
- 9.向列添加注释
- comment on column employee_info.ename is 'the name of employees';
- comment on column dept.dname is 'the name of department';
- 10.清除表中所有数据
- truncate table employee_info; (DELETE FROM table_name或DELETE * FROM table_name)
- 11.删除表
- drop table employee_info;
- --
- //下面来看看刚刚才我们对表dept和表employee_info所做的更改
- //user_constraints视图里面包含了刚刚才我们创建的所有约束，以及其他信息，
- //你可以用desc user_constraints命令查看其详细说明
- select constraint_name,constraint_type,status,deferrable,deferred
- from user_constraints
- where table_name='EMPLOYEE_INFO';
- --
- CONSTRAINT_NAME CONSTRAINT_TYPE STATUS DEFERRABLE DEFERRED
- ------------------------------ --------------- -------- -------------- ---------
- PK_EMP_INFO P ENABLED NOT DEFERRABLE IMMEDIATE
- FK_EMP_INFO R ENABLED DEFERRABLE DEFERRED
- NOT_NULL_EMP_INFO C ENABLED NOT DEFERRABLE IMMEDIATE
- SYS_C005373 C ENABLED NOT DEFERRABLE IMMEDIATE
- UQ_EMP_INFO U ENABLED NOT DEFERRABLE IMMEDIATE
- CK_EMP_INFO C ENABLED NOT DEFERRABLE IMMEDIATE
- //我们可以通过user_cons_columns视图查看有关列的约束信息；
- select owner,constraint_name,table_name,column_name
- from user_cons_columns
- where table_name='EMPLOYEE_INFO';
- --
- OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
- ------------------------------ ------------------------------ ------------------------------ ---------------
- YEEXUN PK_EMP_INFO EMPLOYEE_INFO EMPNO
- YEEXUN CK_EMP_INFO EMPLOYEE_INFO SEX
- YEEXUN NOT_NULL_EMP_INFO EMPLOYEE_INFO PHONE
- YEEXUN SYS_C005373 EMPLOYEE_INFO HIREDATE
- YEEXUN UQ_EMP_INFO EMPLOYEE_INFO PHONE
- YEEXUN FK_EMP_INFO EMPLOYEE_INFO DEPTNO
- //我们将user_constraints视图与user_cons_columns视图连接起来
- //查看约束都指向哪些列
- column column_name format a15;
- select ucc.column_name,ucc.constraint_name,uc.constraint_type,uc.status
- from user_constraints uc,user_cons_columns ucc
- where uc.table_name=ucc.table_name and
uc.constraint\_name=ucc.constraint\_name anducc.table\_name='EMPLOYEE\_INFO';- --
- COLUMN_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
- --------------- ------------------------------ --------------- --------
- EMPNO PK_EMP_INFO P ENABLED
- DEPTNO FK_EMP_INFO R ENABLED
- PHONE NOT_NULL_EMP_INFO C ENABLED
- HIREDATE SYS_C005373 C ENABLED
- PHONE UQ_EMP_INFO U ENABLED
- SEX CK_EMP_INFO C ENABLED
- --
- //这里有个constraint_type，他具体指下面几种类型:
- //C:check,not null
- //P:primary key
- //R:foreign key
- //U:unique
- //V:check option
- //O:read only
- --
- //我们可以通过user_tab_comments视图获得对表的注释
- select * from user_tab_comments
- where table_name='EMPLOYEE_INFO';
- TABLE_NAME TABLE_TYPE COMMENTS
- ------------------------------ ----------- --------------------------
- EMPLOYEE_INFO TABLE information of employees
- --
- //我们还可以通过user_col_comments视图获得对表列的注释：
- select * from user_col_comments
- where table_name='EMPLOYEE_INFO';
- --
- TABLE_NAME COLUMN_NAME COMMENTS
- ------------------------------ ------------------------------ ---------------------------
- EMPLOYEE_INFO EMPNO
- EMPLOYEE_INFO DEPTNO
- EMPLOYEE_INFO ENAME the name of employees
- EMPLOYEE_INFO SEX
- EMPLOYEE_INFO PHONE
- EMPLOYEE_INFO ADDRESS
- EMPLOYEE_INFO ID
- EMPLOYEE_INFO HIREDATE
- --
- select * from user_col_comments
- where table_name='EMPLOYEE_INFO' and
comments is not null;- --
- TABLE_NAME COLUMN_NAME COMMENTS
- ------------------------------ ------------------------------ ------------------------
- EMPLOYEE_INFO ENAME the name of employees
- --
- //最后我们来查看一下修改后的表:
- desc employee_info;
- Name Type Nullable Default Comments
- EMPNO NUMBER(2)
- DEPTNO NUMBER(3) Y
- ENAME VARCHAR2(10) Y the name of employees
- SEX CHAR(2) Y 'M'
- PHONE NUMBER(11)
- ADDRESS VARCHAR2(50) Y
- ID VARCHAR2(18) Y
- HIREDATE DATE sysdate+1
- --
- desc dept;
- Name Type Nullable Default Comments
- DEPTNO NUMBER(3)
- DNAME VARCHAR2(10) Y the name of department
- LOC VARCHAR2(50) Y
create table test_user ( no number(5) not null , --pk username varchar2(30) not null , --用户名 passpord varchar2(30) not null , --密码 constraint pk_connectdb primary key(no) )storage (initial 10k next 10k pctincrease 0);
\*下面讲解上面命令的各方面的含义 create table test\_user --创建数据表 no number(5) not null , --pk (列名或字段名) 数据类型(数据长度) 该数据列不能为空 ,是列之间的分隔符 --后的内容是注释 constraint pk\_connectdb primary key(no) (约束) 约束名 (主键) (列名) 意思就是 在数据表 test\_user中所有行数据 no的值不能相同(这就是主键的含义)
storage (initial 10k next 10k pctincrease 0); 这个说起来比较复杂, 反正如果某个数据表要存放大量数据，就把initial和next后的值设置大一点, 否则设置小一点.
既然上面在创建数据表中没有特别指定 表空间，当然该表就存放在study缺省表空间data\_test了.
create tablespace data_phonepos datefile 'd:\install\OracleXpdb\datafilephonepos.dbf' size 8000M; create user phonepos identified by phonepos default tablespace data_phonepos;
grant connect, resource to phonepos; grant dba to phonepos;
权限的查询
5.1 查询某个用户授予其他用户在当前用户模式下的对象权限 select * from user_tab_privs_made --假如当前用户为WENZI，那么查询结果就是由WENZI授权，在WENZI模式下的权限记录
5.2 查询某个用户授予其他用户在该用户模式对象及其他模式对象上的对象权限 select * from all_tab_privs_made -- 假如当前登录用户为WENZI，那么查询结果就是所有由WENZI授予的权限的记录
修改用户
ALTER USER avyrros IDENTIFIED EXTERNALLY DEFAULT TABLESPACE data_ts TEMPORARY TABLESPACE temp_ts QUOTA 100M ON data_ts QUOTA 0 ON test_ts PROFILE clerk;
删除用户 DROP USER username [CASCADE] --CASECADE 选项会删除该用户模式下的所有对象，建议在删除前，先确认是否有其他的依赖关系存在。
查询属于用户的对象 select owner,object_name,object_type,status from dba_objects where owner='WENZI'
5.3 查询为某个用户授予的，在其他模式对象上的权限 select * from user_tab_privs_recd --假如当前登录用户为WENZI，那么查询结果就是WENZI在其他模式对象上的权限
5.4 查询为某个用户授予的，在该用户模式对象与其他模式对象上的权限 select * from all_tab_privs_recd --假如当前用户为wenzi，则查询结果为wenzi在整个数据库中拥有权限的对象
角色管理
创建口令文件 orapwd file='..........\pwd{SID}.ora' password='***(sys的密码)' tntries=10(口令文件最大的用户数量)
要使某个用户可以使用口令文件，必须为其授予SYSDBA权限，系统会自动将其加入到口令文件中。 grant sysdba to wenzi 当收回SYSDBA权限时，系统将对应的用户从口令文件中删除。 revoke sysdba from wenzi
查看口令文件管理的用户 select * from v$pwfile_users
创建步骤： SYS用户在CMD下以DBA身份登陆：[user@root ~]$ sqlplus /nolog
SQL> conn sys/h1w2D3B4 as sysdba
SQL> startup
[user@root ~]$ lsnrctl start
// 分为四步
//第1步：创建临时表空间
create temporary tablespace user_temp
tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
//第2步：创建数据表空间
create tablespace user_da ta
logging
datafile 'D:\oracle\oradata\Oracle9i\user_da ta.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
//第3步：创建用户并指定表空间
create user username identified by password
default tablespace user_da ta
temporary tablespace user_temp;
//第4步：给用户授予权限
grant connect,resource to username;
---------------------------------------------------------------------------------
//以后以该用户登录，创建的任何数据库对象都属于user_temp 和user_data表空间，
这就不用在每创建一个对象给其指定表空间了
撤权：
revoke 权限... from 用户名;
删除用户命令
drop user user_name cascade;
建立表空间
CREATE TABLESPACE data01
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k
删除表空间
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
一、建立表空间
CREATE TABLESPACE data01
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k
二、建立UNDO表空间
CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M
注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间:
ALTER SYSTEM SET undo_tablespace=UNDOTBS02;
三、建立临时表空间
CREATE TEMPORARY TABLESPACE temp_data
TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M
四、改变表空间状态
1.使表空间脱机
ALTER TABLESPACE game OFFLINE;
如果是意外删除了数据文件，则必须带有RECOVER选项
ALTER TABLESPACE game OFFLINE FOR RECOVER;
2.使表空间联机
ALTER TABLESPACE game ONLINE;
3.使数据文件脱机
ALTER DATABASE DATAFILE 3 OFFLINE;
4.使数据文件联机
ALTER DATABASE DATAFILE 3 ONLINE;
5.使表空间只读
ALTER TABLESPACE game READ ONLY;
6.使表空间可读写
ALTER TABLESPACE game READ WRITE;
五、删除表空间
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
六、扩展表空间
首先查看表空间的名字和所属文件
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
1.增加数据文件
ALTER TABLESPACE game
ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;
2.手动增加数据文件尺寸
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'
RESIZE 4000M;
3.设定数据文件自动扩展
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf
AUTOEXTEND ON NEXT 100M
MAXSIZE 10000M;
设定后查看表空间信息
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SMTS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE
创建数据表：
--建一个表 create table HH2(
tid number primary key ,--主键设定
tname varchar2(20)
);
--删除表 drop table HH;
--表空间（相当于一个数据库）(DBA权限) create tablespace test datafile 'D:test.dbf' size 10M autoextend on next 10M maxsize 100M
--指定表在那个表空间里面(默认在USERS表空间里) create table HH(tid number primary key) tablespace test; select * from tabs;
--删除 表空间 drop tablespace test including contents and datafiles --连带物理文件和表空间中的数据也一起删除
--建表建约束 create table student1(
sid number primary key,
sname varchar2(20) not null,
sage number,
ssex char(2),
saddress varchar2(100),
cid number references tclass(cid)--建立外键关系 );
create table tclass (
cid number primary key,
cname varchar2(20) );
--唯一unique 检查 check 默认值 modify 添加外键关系 添加列
alter table student1 add constraint UQ_student1_sname unique(sname);
alter table student1 add constraint CK_student1_agae check(sage between 19 and 70);
alter table student1 modify ssex default '男';
alter table student1 add constraint FK_student1_cid foreign key(cid) references tclass(cid);
alter table student1 add dt date;
--删除约束 alter table student1 drop constraint UQ_student1_sname ;
1.创建oracle数据表
创建oracle数据表的语法如下：
[图片上传失败...(image-6a04e6-1612973157705)]
create table命令用于创建一个oracle数据表；括号内列出了数据表应当包含的列及列的数据类型；tablespace则指定该表的表空间。
创建数据表students。
[图片上传失败...(image-b52597-1612973157705)]
在该创建语句中，依次定义了student_id、student_name、student_age、status及version等列；tablespace users表示将表创建于表空间users中。
通过视图user_tables可以获得当前用户所拥有的表信息，利用如下SQL语句可以查看表student的表空间信息。
[图片上传失败...(image-1db165-1612973157706)]
在oracle的SQL命令行下，可以利用describe命令来查看已有数据表的表结构，如下所示。
[图片上传失败...(image-33c6bd-1612973157706)]
2.数据表的相关操作
数据表创建之后，由于某些原因，例如，设计时的考虑不足，往往需要对其进行结构上的调整。常见的调整包括，增加新列、修改已有列、删除、重命名已有列。另外，还可以转移数据表的表空间。
修改数据表结构应当使用alter table命令。例如，在表student中，增加新列class_id(班级ID)的SQL语句如下图所示。
[图片上传失败...(image-b14a92-1612973157706)]
alter table student 用于修改表student的结构；add用于增加列，注意此处没有collumn关键字；小括号内是列及列的数据类型；用户可以一次性为表增加多个列，各列之间使用逗号进行分隔。
在修改成功之后，表student的结构如下所示。
[图片上传失败...(image-304b42-1612973157706)]
同样，可以利用alter命令修改和删除已有列。
利用alter命令将class_id的数据类型修改为varchar2(20)。
[图片上传失败...(image-d22742-1612973157706)]
modify(class_id varchar2(20))用于修改表student中的已有列class_id，实际相当于重新定义。该列新的数据类型为varchar2(20)。此时表student的结构如下：
[图片上传失败...(image-b4d757-1612973157706)]
通过modify选项可以将列的类型重新定义，而通过drop选项则删除已有列，如下所示。
[图片上传失败...(image-5664ff-1612973157707)]
drop column class_id用于删除已有列class_id；需要注意的是，此处必须添加column选项，才能表示删除的目标是一个列。此时，表student的结构已经修改如下：
[图片上传失败...(image-2a57c8-1612973157707)]
对于数据表的列，除了增、删、改操作之外，还可以进行重命名操作。重命名一个列，应该使用rename选项。例如，为了与其他数据表进行统一，需要将表student的列student_id重命名为id，则可以利用如下SQL语句。
[图片上传失败...(image-653941-1612973157707)]
rename column student_id to id用于将列student_id重命名为id。此时，表student的结构如下所示。
[图片上传失败...(image-f6dc86-1612973157707)]
对于调整数据表结构来说，要特别注意严谨性。列的数据类型的修改，有可能会影响应用程序对数据库进行存取；而列的删除和重命名更需要检查应用程序是否会出现关联性错误。
如果数据表创建时，选择了错误的表空间，那么可以利用alter table命令，结合move tablespace选项转移表空间，如下图所示。
将表student转移至表空间users中。
[图片上传失败...(image-186c77-1612973157707)]
alter table student用于修改表student的属性；move tablespace users用于将表student从当前表空间转移至表空间users。可以通过查询视图user_tables获得表student转移之后的表空间信息，如下所示。
[图片上传失败...(image-913cd6-1612973157707)]
分析查询结果可知，利用move tablespace选项已经成功实现了表空间的转移。
删除数据表
利用drop table命令删除数据表
[图片上传失败...(image-273827-1612973157707)]
有时，由于某些约束的存在，例如，当前表的主键被其他表用作外键，会导致无法成功删除。利用cascade constraints选项可以将约束同时删除，从而保证drop table命令一定能够成功执行。
[图片上传失败...(image-642462-1612973157707)]
特殊的数据表dual
dual表实际属于系统用户sys，具有了数据库基本权限的用户，均可查询该表的内容，如下所示：
[图片上传失败...(image-ff0524-1612973157707)]
或者
[图片上传失败...(image-3393fa-1612973157707)]
分析查询结果可知，dual表仅含有一行一列。该表并非为了存储数据而创建的，其存在的意义在于提供强制的数据源。
在oracle中，所有查询语句必须满足select column_name from table_name的格式。但是，在某些场景下，数据源table_name并不明确。例如，函数sysdate()用于返回当前日期，那么在SQL命令行下调用该函数时，很难有明确的数据源，此时即可使用dual表。
利用dual表提供数据源，以获得当前日期。
[图片上传失败...(image-755923-1612973157707)]
同样，对于各种数学运算，同样可以利用dual表作为数据源，来打印和查看输出结果。
[图片上传失败...(image-f58e5b-1612973157707)]
dual表提供了一行一列的数据格式，从而使各种表达式、函数运算在以其为数据源时，能够输出单行单列的形式。
由于dual表的所有者为系统用户sys，因此，只有用户利用该身份登录数据库才可以修改该表。但是，修改该表的内容或者结构都应该被禁止。
oracle create tablespace
- create tablespace ZGECM
- logging datafile 'G:\oracle\product\10.2.0\ZGECM.DBF'
- size 500M autoextend on
- next 10M maxsize 500M extent management local
- create user sms2 identified by sms2 default tablespace ECM2
- temporary tablespace temp profile default;
- --6 授权给新建用户
- grant connect,resource to sms2;
- grant dba to sms2;
- exp xuner_ecm/xuner_ecm@192.168.1.139/SMS FILE=E:/xunerecm0910_server.DMP OWNeR=xuner_ecm
- imp scjt/scjt@SMS fromuser=ecm touser=sms2 file='G:\sms.dmp'
- imp SMS/SMS@LOCAL_SMS fromuser=SMS touser=SMS file='G:\sms.dmp'
- select userenv('language') from dual; //查询oracle服务器端的字符
- select nls_charset_name(to_number('0354','0354'))
- from dual;
- CREATE DATABASE LINK sms CONNECT TO sms IDENTIFIED BY sms
- USING 'SMS';
- insert into XSJBXXB select * from sms.xsjbxxb@zhang2
- insert into XSJBXXB select * from sms.xsjbxxb@to_test
- --创建dblink
- create database link to_test
- connect to sms identified by sms
- using 'ZHANG';
- '(DESCRIPTION =(
- ADDRESS_LIST =(
- ADDRESS =(
- PROTOCOL = TCP)(
- HOST = 192.168.0.100)(PORT = 1521)))
- (CONNECT_DATA =(SERVICE_NAME = AAA)))'
- --查询约束
- select owner,constraint_name,table_name from user_constraints
- where constraint_name='SYS_C00119759'
- /*分为四步 */
- /*第1步：创建临时表空间 */
- create temporary tablespace user_temp
- tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'
- size 50m
- autoextend on
- next 50m maxsize 20480m
- extent management local;
- /*第2步：创建数据表空间 */
- create tablespace user_data
- logging
- datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'
- size 50m
- autoextend on
- next 50m maxsize 20480m
- extent management local;
- /*第3步：创建用户并指定表空间 */
- create user username identified by password
- default tablespace user_data
- temporary tablespace user_temp;
- /*第4步：给用户授予权限 */
- grant connect,resource,dba to username;
- --Error dropping MEM_GENINF:
- --ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源
- --Record is locked by another user
- --1.查看锁
- select t2.username,t2.sid,t2.serial#,t2.logon_time
- from v
session t2 where t1.session_id=t2.sid ;
- --2、Kill
- alter system kill session 'sid,serial#';
- alter system kill session '151,14678';
网友评论