Oracle数据库日常管理

环境

Oracle 11.2.0.4

db_block_size = 8192

字符集：AMERICAN_AMERICA.ZHS16GBK

表空间的管理

        下图为Oracle数据库存储结构

 

        我们的数据是存储在数据文件中，数据文件属于表空间的，所以添加数据首先要有表空间存在。

1、查看数据库的表空间信息

 select * from dba_tablespaces;

创建为数据库后，默认会有5个表空间。

SYSTEM：系统表空间

SYSAUX：系统辅助表空间

UNDOTBS1：UNDO表空间

TEMP：临时表空间

USERS：默认表空间

2、查看数据文件及临时数据文件信息

select tablespace_name, file_name, file_id, bytes, maxbytes, autoextensible from dba_data_files
union all
select tablespace_name, file_name, file_id, bytes, maxbytes, autoextensible from dba_temp_files;

        可以看出每个表空间有一个数据文件。

        我们在项目中一般会创建专门的表空间用来存放相应的数据，避免数据全存放在系统默认的表空间中，导致性能问题。

3、创建表空间

create tablespace ys datafile '/oradata/ys01.dbf' size 1G autoextend on next 100M;
--检查
select * from dba_tablespaces;
select tablespace_name, file_name, file_id, bytes, maxbytes, autoextensible from dba_data_files
union all
select tablespace_name, file_name, file_id, bytes, maxbytes, autoextensible from dba_temp_files;

        这里我创建的为小文件表空间，且大小为1G，自动扩充，每次增长100M，直到32为止。还有一种大文件表空间。

                小文件表空间：一个小文件表空间可以有1024个数据文件。

                大文件表空间：一个大文件表空间只能有一个数据文件。

        因为此环境中db_block_size为8K，所以小文件表空间的数据文件一个最大为32G，大文件表空间的数据文件最大为32T。

        后面随着数据的增长，此表空间的数据文件也会随之被数据填满，如果再想添加数据则需要为该表空间添加数据文件了。

4、添加数据文件

alter tablespace ys add datafile '/oradata/ys02.dbf' size 1G autoextend on next 100M;
--检查
select tablespace_name, file_name, file_id, bytes, maxbytes, autoextensible from dba_data_files
union all
select tablespace_name, file_name, file_id, bytes, maxbytes, autoextensible from dba_temp_files;

        此时YS表空间就有了两个数据文件ys01.dbf和ys02.dbf了。

        在生产环境中，如果数据量较大，建议把表数据与索引数据分别存放不不同的表空间中。

        创建一个索引表空间。

create tablespace ys_index datafile '/oradata/ys_index01.dbf' size 1G autoextend on next 100M;
--检查
select * from dba_tablespaces;

        表空间准备好后，就可以创建表了。 但表是属于某个用户的，所以还需要提前创建专门的用户。

用户管理 1、用户的创建

create user ys identified by 123456 default tablespace ys;

        创建一个名为ys的用户，密码为123456，默认存放的表空间为ys。

        如果此时使用该用户去创建表，会报错，因为没有权限。

2、用户的授权

        首先需要赋予连接会话的权限，再赋予表创建权限等。

grant connect, resource to ys;
--检查
select * from dba_role_privs where grantee = 'YS';

        赋予了connect与resource角色的权限给ys用户。生产环境也推荐赋予此权限，如果不够再额外赋予，千万不要为了方便就赋予dba权限。

        然后我们就可以使用该用户来创建表了。

表的管理 1、表的创建

create table ys.t1(
id number,
name varchar2(10),
sex char(1));
--检查
select * from user_tables;
select * from ys.t1;

        在ys用户下创建了一张t1表，字段为id、name和sex。表数据存储在ys表空间中。

2、数据添加

insert into ys.t1 values(1,'a',1);
insert into ys.t1 values(2,'b',1);
insert into ys.t1 values(3,'c',2);
commit;
--检查
select * from ys.t1;

        往ys.t1表中添加了三行数据。

        日后随着数据的增加，查询会变满，我们可以在检索的字段上加上索引来提高查询速度。

索引管理 1、索引的创建

create index ind_ys_t1_id on ys.t1(id);
create bitmap index ind_ys_t1_sex on ys.t1(sex);
--检查
select * from user_indexes;
select * from user_ind_columns;

         此处创建了两个索引，一个为B树索引：IND_YS_T1_ID，另一个为位图索引：IND_YS_T1_SEX。都存放在ys_index表空间中，与表数据分开。

        索引并非创建的越多越好。在对表数据操作时，Oracle会自动维护索引数据，也是需要成本的。