Oracle表空间
果洛州网站建设公司创新互联公司,果洛州网站设计制作,有大型网站制作公司丰富经验。已为果洛州上千余家提供企业网站建设服务。企业网站搭建\外贸网站建设要多少钱,请找那个售后服务好的果洛州做网站的公司定做!
表空间是数据库的逻辑组成部分,从物理上讲,数据库数据存放在数据文件中
从逻辑上讲,数据库则是存放在表空间中,表空间由一个或多个数据文件组成
数据库的逻辑结构
oracle中逻辑结构包括表空间、段、区和块
说明一下数据库由表空间构成,而表空间又是由段构成,而段又是由区构成,而
区又是由oracle块构成的这样的一种结构,可以提高数据的效率
表空间用于从逻辑上组织数据库的数据。数据库逻辑上是由一个或是多个表空间组成的
通过表空间可以达到以下作用:
1、控制数据库占用的磁盘空间
2、dba可以将不同数据类型部署到不同的位置,这样有利于提高I/O性能,同时
利于备份和恢复等管理操作
建立表空间
建立表空间是使用create tablespace命令完成的,需要注意的是,一般情况下,建立表空间
是特权用户或是dba来执行的,如果用其他用户来创建表空间,则用户需要具有create tablespace
的系统权限
建立数据表空间
在建立数据库后,为便于管理表,最好建立自己的表空间
SQL> create tablespace data01 datafile '/oracle/datafile/data01.dbf' size 10m uniform 128k;
create tablespace data01 datafile '/oracle/datafile/data01.dbf' size 10m uniform 128k
*
ERROR at line 1:
ORA-02180: invalid option for CREATE TABLESPACE
报错区域在uniform 128k,区大小设置错误
SQL> create tablespace data01 datafile '/oracle/datafile/data01.dbf' size 10m;
Tablespace created.
查看表空间信息
[oracle@aliyun_test oracle]$ ll datafile/data01.dbf
-rw-r----- 1 oracle oinstall 10493952 Sep 27 09:54 datafile/data01.dbf
使用数据表空间(如果不带表空间,默认会放到system表空间)
创建一张表,并将表空间data01给创建的这张表
SQL> create table mydept(deptno number(2),dname varchar2(14),loc varchar2(13)) tablespace data01;
Table created.
创建好的表空间如何查询呢?由表的信息dab_tablespaces来进行查询,先查看表结构以及字段说明
SQL> desc dba_tablespaces;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
PREDICATE_EVALUATION VARCHAR2(7)
ENCRYPTED VARCHAR2(3)
COMPRESS_FOR VARCHAR2(12)
查看表空data01:
SQL> select * from dba_tablespaces where tablespace_name='DATA01';
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FORCE_LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGED_IN SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION BIGFILE PREDICATE_EVALUATION ENCRYPTED COMPRESS_FOR
------------------------------ ---------- -------------- ----------- ----------- ----------- ---------- ------------ ---------- --------- --------- --------- ------------- ----------------- --------------- ---------- ------------------------ ------------------- ----------- ------- -------------------- --------- ------------
DATA01 8192 65536 1 2147483645 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO HOST NO
可以详细清楚的查询该表空间的详细信息
修改表空间的状态
当建立表空间时,表空间处于online状态,此时该表空间时可以访问的,并且该表空间时可以读写的
既可以查询该表空间的数据,而且还可以在表空间执行各种语句。但是在进行系统维护或是数据
维护时,可能需要改变表空间的状态。一般情况下由特权用户或是dba来操作
1、使表空间脱机
alter tablespace data01 offline; data01就是表空间名称
2、使表空间联机
alter tablespace data01 online;
3、将表空间设为只读状态
当建立表空间时,表空间可以读写,如果不希望在该表空间上执行update,delete,insert
等操作,那么可以将表空间修改为只读表空间
alter tablespace data01 read only;
事例如下:
上述已经使用了表空间在表mydept上面,于是向里面插入数据
SQL> insert into mydept values (1,'xiaohuang','wuhan');
1 row created.
然后将表空间设置为只读状态
SQL> alter tablespace data01 read only;
Tablespace altered.
然后再向表mydept插入数据,看下是否成功
SQL> insert into mydept values (2,'xiaobai','shanghai');
insert into mydept values (2,'xiaobai','shanghai')
*
ERROR at line 1:
ORA-00372: file 5 cannot be modified at this time
ORA-01110: data file 5: '/oracle/datafile/data01.dbf'
提示出错,表示不能修改表空间的数据文件
如何恢复呢,只需再将表空间设置为可读可写就行,见如下操作
SQL> alter tablespace data01 read write;
Tablespace altered.
然后再向里面插入数据即可
SQL> insert into mydept values (2,'xiaobai','shanghai');
1 row created.
插入数据成功
删除表空间
一般情况下,由特权用户或是dba操作,如果是其他用户操作,需要具有drop tablespace系统权限
drop tablespace '表空间名' including contents and datafiles;
说明:including contents表示删除表空间的同时删除该表空间的所有数据库对象,而datafiles
表示将数据库文件也删除
SQL> drop tablespace data01 including contents and datafiles;
Tablespace dropped.
表示删除表空间操作完成,下面查看是否已经删除
SQL> select * from dba_tablespaces where tablespace_name='DATA01';
no rows selected 由dba_tablespaces表查询并没有查询到,即已经删除
[oracle@aliyun_test ~]$ ll /oracle/datafile/
total 0 可以看出数据文件也已经删除掉了
1、知道表空间名,显示该表空间包括的所有表
再次创建表空间data01来使用
SQL> create tablespace data01 datafile '/oracle/datafile/data01.dbf' size 10m;
Tablespace created.
创建一张表使用该表空间
SQL> create table mydept(deptno number(2),dname varchar2(14),loc varchar2(13)) tablespace data01;
Table created.
然后通过表空间名data01如何来查询到什么表使用了它呢?
使用到的表为all_tables
SQL> desc all_tables;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
可以查询到有一个tablespace_name的字段,于是如下操作
SQL> select tablespace_name,table_name from all_tables where tablespace_name='DATA01';
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
DATA01 MYDEPT
查询到使用的表以及对应的表空间
如上,知道了表名称也可以知道表空间
SQL> conn scott/redhat@test;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott@test
SQL> select tablespace_name,table_name from user_tables where table_name='MYDEPT';
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
DATA01 MYDEPT
下面使用system用户查询的
SQL> select tablespace_name,table_name from all_tables where table_name='MYDEPT';
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
DATA01 MYDEPT
通过2,可以了解到scott.emp表是在system这个表空间上,如果将表空间system改为只读
但是不会成功,因为system是系统表空间,如果是普通表空间,就可以修改为只读
扩展表空间
表空间是由数据文件组成的,表空间的大小实际就是数据文件相加后的大小,假定表emp存放在
data01表空间上,初始大小为2m,当数据满2m后,如果再向表emp插入数据,这样就会显示空间不足的错误
所以就需要扩展表空间?
1、增加数据文件
alter tablespace data01 add datafile '/oracle/datafile/data02.dbf' size 20m;
SQL> alter tablespace data01 add datafile '/oracle/datafile/data02.dbf' size 20m;
Tablespace altered.
[oracle@aliyun_test ~]$ ll /oracle/datafile/
total 30736
-rw-r----- 1 oracle oinstall 10493952 Sep 27 14:05 data01.dbf
-rw-r----- 1 oracle oinstall 20979712 Sep 27 14:06 data02.dbf
2、增加数据文件的大小
alter tablespace data01 '/oracle/datafile/data01.dbf' resize 20m;
3、设置文件的自动增长
alter tablespace data01 '/oracle/datafile/data01.dbf' autoextend on next 10m maxsize 500m;
如何迁移表空间的数据文件?
1、确定数据文件所在的表空间
select tablespace_name from dba_data_files where file_name='/oracle/datafile/data01.dbf';
TABLESPACE_NAME
------------------------------
DATA01
知道了表空间后,然后使表空间状态脱机
2、使表空间脱机
确保数据文件的一致性,将表空间转变为offline状态
alter tablespace data01 offline;
3、使用命令移动数据文件到指定的目标位置
sql>host move /oracle/datafile/data01.dbf /test/oracle/datafile/data01.dbf;
4、执行alter tablespace命令,数据文件重命名
在物理上移动了数据后,还必须执行alter tablespace命令对数据库文件进行逻辑修改
alter tablespace data01 rename datafile '/oracle/datafile/data01.dbf' to '/test/oracle/datafile/data01.dbf';
5、使表空间联机状态
在移动了数据文件后,为了使用户可以访问该表空间,必须将其转变为online状态
alter tablespace data01 online;
至此迁移表空间的数据文件完成
显示表空间信息
查询数据字典视图dba_tablespaces,显示表空间的信息
select tablespace_name from dba_tablespaces;
显示表空间所包含的数据文件
查询数据字典视图dba_data_files,可显示表空间所包含的数据文件
select file_name,bytes from dba_data_files where tablespace_name='表空间名';
其他表空间
除了常用的数据表空间外,还有其他类型的表空间
1、索引表空间
2、undo表空间
3、临时表空间
4、非标准块表空间
数据的完整性
数据的完整性用于确保数据库遵从一定的商业和逻辑规则,在oracle中,数据完整性可以使用约束
触发器、应用程序(过程、函数)三种方法来实现,在这三种方法中,因为约束易于维护并且具有
最好的性能,所以作为维护数据完整性的首选
约束
约束用于确保数据库数据满足特定的商业规则,在oracle中,约束包括:not null、unique、primary key、foreign key和check五种。
not null:非空,如果在列上定义了not null,那么插入数据时,必须为列提供数据
unique:唯一键,当定义了唯一的约束,该列的值是不能重复的,但是可以为null
primary key:主键,用于唯一的标识表行的数据,当定义主键约束后,该列不但不能重复而且不能为null一张表最多只能有一个主键,但是可以有多个唯一键
foreign key:外键,用于定义主表和从表之间的关系,外键约束要定义在从表上,主表则必须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为null
check:用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000~2000之间,如果不在1000~2000之间就会提示错误
SQL> create table goods(goodid char(8) primary key,--主键
2 goodname varchar2(30),
3 unitprice number(10,2) check (unitprice > 0),--单价必须大于0
4 category varchar2(8),
5 provider varchar2(30))
6 ;
SQL> create table customer(customerid char(8) primary key, --主键
2 name varchar2(20) not null,--不为空
3 address varchar2(20),
4 email varchar2(20) unique,--不允许重复,唯一键
5 sex char(4) default '男' check(sex in ('男','女')),---默认为男,不是男就是女
6 cardid char(18))
7 ;
SQL> create table purchase(customerid char(8) references customer(customerid),---由于这是外键,必须关联到主表的字段
2 goodid char(8) references goods(goodid),--同上,数据类型必须和主表一致
3 nums number(5) check(nums between 1 and 30)) --数量必须在1~30之间
4 ;
如果在建表时忘记建立必须的约束,则可以在建表后使用alter table命令为
表增加相应的约束。但是注意:增加not null约束是修改null约束,即使用关键字
modify选项,而增加其他的约束则使用add关键字选项
alter table goods modify gooname not null;
alter table customer add constraint cardunique(约束名) unique(cardid(char);
alter table customer add constraint addresscheck check(address in ('东城','西城'));
删除约束
当不需要某个约束时,可以进行删除
alter table 表名 drop constraint 约束名称;
当删除主键约束的时候,可能会报错
alter table 表名 drop primary key;
这是因为如果在两张表存在主从关系,那么删除主表的主键约束时,必须带上cascade选项,例
alter table 表名 drop primary key cascade; 相当于破坏了主外键关系
显示约束信息
1、显示约束信息
通过查询数据字典视图user_constraint,可以显示当前用户的所有的约束信息
select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名';
2、显示约束列
通过查询数据字典视图user_cons_columns,可以显示约束所对用的表列信息
select column_name,position from user_cons_columns where constraint_name='约束名';
3、当然也有容易的方法,使用pl/sql工具查看
定义约束有如下方式:
列级定义
列级定义是在定义列的同时定义约束
如在department表定义主键约束
create table department(dept_id number(2) constraint pk_department primary key,name varchar(12),loc varchar2(12));
关键字:constraint 约束名称:pk_department 约束类型:primary key
表级定义
表级定义是指在定义了所有列后,在定义约束,not null约束只能在列级上定义
create emp2(emp_id number(4),name varchar2(15),dept_id number(2),constraint pk_emp primary key(emp_id),constraint
fk_department foreign key(dept_id) references department(dept_id));
索引是用于加速数据存取的数据对象,合理的使用索引可以大大降低I/O次数,从而提高数据访问性能
创建索引
单列索引是基于单个列所建立的索引
create index 索引名 on 表名(列名)
以name字段查询:select * from customer where name='sp';如果经常这样查就可以将name字段创建索引,提高查询速度
create index name_index on customer(name);---给name字段建立index
复合索引
复合索引是基于两列或是多列的索引,在同一张表上可以有多个索引,但是要求列的组合必须不同
create index emp_index1 on emp(ename,job);select * from customer where ename='sp' and job='MANAGER';创建联合索引
create index emp_index1 on emp(job,ename);
使用原则
1、在大表上建立索引才有意义
2、在where字句或是连接条件上经常引用 的列上创建索引
3、索引的层次不要超过4层(多级索引不要超过4层)
索引的缺点
1、建立索引,系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引
2、更新数据的时候,系统必须要有额外的实际来同时对索引进行更新,以维持数据和索引的一致性
如下字段建立索引是不恰当的
1、很少或从不引用的字段
2、逻辑型的字段,如男或女等,提高查询速率是以消耗一定的系统资源为代价的,索引不能盲目的建立
显示表的所有索引
在同一张表上可以有多个索引,通过查询数据字典视图dba_indexes和user_indexes,可以显示索引信息其中dba_indexes
用于显示数据库所有的索引信息,而user_indexs用于显示当前用户的索引信息
select index_name,index_type fom user_indexes where table_name='表名';
显示索引列
通过查询数据字典视图user_ind_columns,可以显示索引对应的列的信息
select table_name,column_name fom user_ind_columns where index_name='索引名';
管理权限和角色
系统权限介绍
系统权限是指执行特定类型sql命令的权利,它用于控制用户可以执行的一个或是一组数据库操作。比如当用户
具有create table权限时,可以在其方案中建表,当用户具有100中系统权限
常用的有:
create session连接数据库 create table建表
create view建视图 create public sysnonym:键同意词
create procedure建立过程、函数、包 create trigger建触发器
create cluster建簇
显示系统权限
oracle提供了多条系统权限,而且oracle的版本越高,提供的系统权限就越多
select * from system_privilege_map order by name;
授予系统权限
一般情况下,授予系统权限是由dba完成的,如果其他用户来授予系统权限,则要求该用户必须具有grant any privilege
的系统权限,可以带有with admin option选项,被授予的用户或是角色还可以将该系统权限授予其他用户
1、创建两个用户ken,tom
create user ken identified by redhat;
2、给用户key授权
grant create session,Crete table to ken with admin option;
grant create view to ken;
3、给tom授权
grant create session,create table to tom;这是登录的系统权限
revoke create session from tom;
授予数据对象权限
角色不能带有with grant option(授予角色)
只修改某张表的某一列(字段)
grant update on emp(sal) to monkey;只能在表中的字段sal上修改
grant select on emp(ename,sal) to monkey;能在字段ename,sal上查询
grant index on emp to monkey;允许monkey能在表emp上创建索引
名称栏目:Oracle之表空间、索引、管理权限及角色
标题URL:/article2/jcgiic.html
成都网站建设公司_创新互联,为您提供网站收录、云服务器、企业建站、外贸建站、网站内链、做网站
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联