Oracle之表空间、索引、管理权限及角色
|
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,'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,'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),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上创建索引 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
