详解Oracle存储结构掌握基本操作管理
2018.10.14那天我写了Oracle12C 的安装并初步了解了一下Oracle体系结构中数据库和实例。从中我们知道:
数据库是磁盘上数据的集合,位于收集和维护相关信息的数据库服务器上的一个或多个文件中。数据库由各种物理和逻辑结构组成,而表则是数据库中最重要的逻辑结构。表由包含数据的相关行和列组成。
创新互联建站是专业的南县网站建设公司,南县接单;提供成都网站制作、成都做网站、外贸营销网站建设,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行南县网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!
- 组成数据库的文件主要分为两类:数据库文件和非数据库文件。两者之间的区别在于存储何种数据。数据库文件包含数据和元数据,非数据库文件则包含初始参数和日志记录信息等。数据库文件对于每时每刻正在进行的数据库操作来说至关重要。
- 典型的企业服务器的主要组成部分是一个或多个CPU(有多个核)、磁盘空间和内存。Oracle数据库存储在服务器的磁盘上,而Oracle实例则存在于服务器的内存中。Oracle实例由一个大型内存块和大量后台进程组成;该内存块分配在系统全局区域中,后台进程在SGA和磁盘上的数据库文件之间交互。
- 在OracleRAC中,多个实例将使用同一个数据库。虽然共享数据库的实例可能在同一服务器上,但最可能的是这些实例位于不同服务器上,这些服务器通过高速互连进行连接,并且访问驻留在专门的,支持RAID磁盘子系统上的数据库。Oracle Exadata数据库一体机是一个将数据库服务器、I/O服务器和磁盘存储组合到一个或多个机柜,并针对RAC环境优化的示例(包括以每接口40Gbps的速度连接所有这些设备的双InfiniBand接口)。
本篇开头我将Oracle体系结构中的存储结构先进行一个归纳:
- Oracle的存储结构从两方面看:
1.Oracle逻辑存储结构(表空间、块、盘区、段);
2.Oracle物理存储结构(数据文件、重做日志文件、控制文件、归档的日志文件、初始化参数文件、警报和跟踪日志文件、备份文件、Oracle管理文件、密码文件);- 在逻辑存储结构下Oracle的逻辑数据库结构:表、约束、索引、视图、用户和模式、配置文件、序列、同义词、PL/SQL、外部文件访问、数据库链接和远程数据库;
- 另外Oracle 12C与之前版本最大的亮点是;支持数据库热插拔,可以和其他类型的数据库对接
通过结构图就能一目了然:
Oracle逻辑存储结构图:
- Oracle逻辑数据库结构图:
- Oracle物理存储结构图:
详解Oracle逻辑存储结构:Oracle数据库中的数据文件被分组到一个或多个表空间中。在每个表空间中,逻辑数据库结构(如表和索引)都是片段,被进一步细分为“盘区”(extent)和“块”(block)。这种存储的逻辑细分允许Oracle更有效的控制磁盘空间的利用率。
- 表空间:
Oracle表空间由一个或多个数据文件组成,一个数据文件是且只能是一个表空间的一部分。对于Oracle12c的安装,最少会创建两个表空间:SYSTEM表空间和SYSAUX表空间。Oracle12c的默认安装创建6个表空间; - 段:
数据库中的下一个逻辑分组级别是段。段是一组盘区,这组盘区组成了被Oracle视为一个单位的数据库对象,如表或索引。因此,段一般是数据库终端用户要处理的最小存储单位。Oracle数据库中可看到4种类型的段:数据段(非分区表和分区表的每个分区)、索引段、临时段和回滚段。 - 盘区:
它由一个或多个数据库块组成。当扩大数据库对象时,为该对象添加的空间将分配为一个盘区。 - 块:
数据库块是Oracle数据库最小的存储单位。块的大小是数据库内给定表空间中特定数量的存储字节。默认块大小是8KB;
- 表空间:
Oracle逻辑数据库结构组成:
Oracle逻辑数据库结构内容非常繁杂,这里先简单概括一下都有哪些内容组成:
1.表:是Oracle数据库中的基本存储单位,如果没有表,数据库对于企业来说就没有任何价值;
包括: 关系表、临时表、索引组织表、对象表、外部表、群集表、散列群集、排序的散列群集、分区表、分区索引;
2.约束:Oracle约束是一条或多条规则,它在表的一列或多列上定义,用于帮助实施业务规则;
包括:空值约束、唯一列值、主键值、引用完整性值、复合内联完整性、基于触发器的完整性;
3.索引:当检索表少量的行时,使用Oracle索引能更快访问表中的这些行;
包括:唯一索引、非唯一索引、反向键索引、基于函数的索引、位图索引;
4.视图:视图允许用户查看单独表或多个连接表中数据的自定义表示。视图也称为“存储查询”,用户无法看到视图底层隐藏的查询细节;
包括:普通视图、物化视图、对象视图;
5.用户和模式:有权访问数据库的数据库账户称为“用户”。用户可存在与数据库中,而不拥有任何对象。如果用户在数据库中创建并拥有对象,这些对象就是与数据库用户同名的模式(schema)的一部分。模式可拥有数据库中任何类型的对象:表、索引、序列和视图等。模式拥有者或者DBA可授权其他数据库用户访问这些对象。用户总是拥有完整的权限,而且可以控制用户模式中的对象;
6.配置文件:数据库资源不是无限的,因此DBA必须为所有数据库用户管理和分配资源。数据库资源的一些示例是CPU时间、并发会话、逻辑读和连接时间。
数据库配置文件是可以赋给用户的限定资源的命令集。安装Oracle后,DEFAULT配置文件已经存在,并且系统将其赋给任何还没有显式分配配置文件的用户。DBA可添加新的配置文件或改变DEFAULT配置文件,从而符合企业的需求。DEFAULT配置文件的初始值允许无限使用所有的数据库资源。
7.序列:Oracle序列用于分配有序数,并且保证其唯一性。
8.同义词:Oracle同义词(synonym)只是数据库对象的别名,用于简化对数据库对象的引用,并且隐藏数据库对象源的细节。同义词可以赋给表、视图、物化视图、序列、过程、函数和程序包。与视图类似,除了数据字典中的定义外,同义词不会再数据中分配任何空间。
同义词可以是公有或者私有。私有同义词在用户模式下定义,并且只有该用户可用。公有同义词通常由DBA创建,并且所有的数据库用户都可以自动使用。
9.PL/SQL:PL/SQL Developer是一个集成开发环境,专门开发面向Oracle数据库的应用。PL/SQL也是一种程序语言,叫做过程化SQL语言(Procedural Language/SQL)。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点,所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL 只有 Oracle 数据库有。
Oracle物理存储结构:
物理存储构的组成及其功能:
数据文件(.dbf):用于存储数据库中的所有数据,包含系统数据、数据字典数据、临时数据、索引数据、应用数据等
控制文件(.ctl):用于记录和描述数据库的物理存储结构信息
重做日志文件(.log):用于记录外部程序(用户)对数据库的修改操作
初始化参数文件:用于设置数据库启动时的参数初始值
跟踪文件:用户记录用户进程、数据库后台进程的运行情况
归档文件:用于保存已经写满的重做日志文件
口令文件:用于保存具有SYSDBA,SYSOPER权限的用户名和SYS用户口令。
数据文件及其管理:
在安装Oracle数据库时系统会自动为数据库创建几个数据文件,用户对数据库的操作本质是对数据文件的操作。一个表空间可以对应多个数据文件,一个数据文件只能从属于一个表空间。在逻辑上,数据对象都存放在表空间中,实质上是存放在空间对应的数据文件中。
Oracle基本操作管理:
1.数据库开启、关闭
* 进入数据库:
* su - oracle
* sqlplus / as sysdba #以最高管理员sysdba登录
* SQL> help index #查看命令列表
* SQL> show user #查看当前用户
* 开启、关闭数据库
* SQL>startup #开启:实例--数据库装载--数据库打开
* SQL>shutdown immediate #关闭:数据库关闭--卸载数据库--实例
* 启用、关闭监听(作用:客户端访问oracle数据库实例,安装好数据库如果重启主机,则需要开启监听服务,客户端才可以登录oracle数据库实例)
* lnsrctl start
* lnsrctl stop
2.创建数据库:
* dbca (在桌面界面执行)
#创建数据库(如果不弹窗口执行: export DISPLAY=:0.0)
* 执行成功会跳出创建数据库页面,按以下步骤操作:
3.表空间操作:
创建表空间:
create tablespace school //创建表空间school
2 datafile '/oracle/app/oracle/oradata/school01.dbf' //指定表空间文件存放位置
3 size 10m autoextend on; //指定大小,空间可以扩容
调整表空间大小:
调整大小:
SQL> alter database datafile
2 '/oracle/app/oracle/oradata/school01.dbf'
3 resize 80m;
数据库已更改。
添加文件:
SQL> alter tablespace school
2 add datafile
3 '/oracle/app/oracle/oradata/school02.dbf'
4 size 20m autoextend on;
表空间已更改。
表空间权限:
SQL> alter tablespace school read only; //修改为只读权限
表空间已更改。
SQL> alter tablespace school read write; //权限修改为读写(默认)
表空间已更改。
删除表空间:
SQL> drop tablespace school including contents;
表空间已删除。
4.插拔数据库(CDB与PDB转换):
SQL> show con_name //查看当前所在的容器
SQL> show pdbs; //查询数据库的所有容器
将当前数据库CDB转换为PDB:
CDB:默认的数据库
PDB:容器型数据库
SQL> alter pluggable database orclpdb open; //修改可插拔库orclpdb打开状态
插接式数据库已变更。
SQL> alter session set container=orclpdb; //CDB下切换会话到PDB中
会话已更改。
SQL> shutdown immediate //在PBD中关闭插拔数据库
插接式数据库已关闭。
SQL> startup //在PBD中打开插拔数据库
插接式数据库已打开。
将PDB切换为CDB:
SQL> alter session set container=cdb$root;
//切换会话到CDB
5.创建用户:
* 创建用户必须指定一个默认的表空间,在12C版本上CDB中创建用户,用户名格式为:c##用户名;PDB中创建用户,用户名格式为:用户名;
* SQL> create user c##jack
2 identified by abc123 //指定密码
3 default tablespace users //指定默认表空间
4 temporary tablespace temp //指定临时表空间
5 quota unlimited on users; //不进行限额
更改用户密码
SQL> alter user c##jack identified by jack123;
删除用户
SQL> drop user c##jack cascade;
6.创建数据表(插入数据,事务)
SQL> create table info
2 (
3 id number(4),
4 name varchar2(10),
5 score number(5,2),
6 riqi date
7 );
7.事务:
在oracle中默认开启事务不进行提交。因此在进行数据操作时,一定要记得提交,或者设定为自动提交。否则数据不能写入硬盘中。
SQL> insert into info values (1,'lisi',80,to_date('2018-08-26','yyyy-mm-dd'));
SQL> set autocommit on; //设定自动提交,回滚无效
8.索引:
(SQL>)
B树索引
create index 索引名称 on 表名(列名)
唯一索引/非唯一索引
create unique index 索引名称 on 表名(列名)
反向索引
create index 索引名称 on 表名(列名)reverse
位图索引
create bitmap index 索引名称 on 表名(列名)
其他索引
create index 索引名称 on 表名(upper(列名)) //大写函数索引
查看索引
select index_name,index_type,table_name,tablespace_name from user_indexes;
查看索引相关信息
select index_name,table_name,column_name from user_ind_columns where index_name like 'EMP%';
重建索引
alter index 索引名称 rebuild;
alter index 索引名称 rebuild tablespace 表空间
合并索引碎片
alter index 索引名称 coalesce;
删除索引
drop index 索引名称
9.视图:
创建视图要注意权限问题
SQL> create view vinfo as select from info; //创建视图
SQL> select from vinfo; //查询视图
SQL> drop view vinfo; //删除视图
10.物化视图:
首先要通过管理员帐号为c##jack用户赋予权限
SQL> grant create materialized view to c##jack; //创建物化视图权限
SQL> grant query rewrite to c##jack; //查询,重写权限
SQL> grant create any table to c##jack; //创建所有表权限
SQL> grant select any table to c##jack; //查询所有表权限
SQL>alter table info add primary key (id); #添加主键
SQL>create materialized view log on info; #创建物化视图之前需要创建物化视图日志
创建物化视图:
SQL> create materialized view mtview
2 build immediate //创建完成立马生成新数据
3 refresh fast //刷新数据
4 on commit //提交
5 enable query rewrite //开启查询重写功能
6 as
7 select * from info;
SQL> drop materialized view mtview; //删除物化视图
11.序列:
oracle中自增列需要单独进行创建,然后插入数据时进行调用。
创建序列
SQL> create sequence id_seq
2 start with 10 //初始值
3 increment by 1 //增量
4 maxvalue 1000 //最大值
5 nocycle //肺循环
6 cache 50; //缓存
插入数据时调用序列
SQL> insert into info values (id_seq.nextval,'tom',80,to_date('2018-04-10','yyyy-mm-dd'));
查询序列当前值
SQL> select id_seq.currval from dual;
更改序列
SQL> alter sequence id_seq cache 30;
查看序列信息
SQL> select sequence_name,increment_by,cache_size from user_sequences;
删除序列
SQL>drop sequence id_seq;
12.同义词:
俗称别名,外号,可以提高数据安全可靠性。
私有同义词:只在当前用户可以使用
公有同义词:所有数据库用户都可以使用
SQL> create synonym pr_info for info; //创建私有同义词
SQL> create public synonym pub_info for info; //创建共有同义词
删除同义词
SQL> drop synonym pr_info;
SQL> drop public synonym pub_info;
13.补充:
常用oracle数据库查询命令:
(1)Select * from tables(表名); //查询表tables的内容
(2)Truncate table tables(表名);//清除表内数据;但保留格式
(3)Select ID(列名)from tables groupby ID(列名)、ID2(列名)havingcount(*)>1; //查询表中重复的数据
(4)Select * from tables(表名) a where a.id(列名)=’07552223’(重复的数据);//将在列ID中所有与07552223相同的数据排列出来
(5)delete from tables where rowid in (select min(rowid)fromtables where id=’15252525’) group by username); //删除table表中ID列数据为‘15252525’的重复项
(6)delete from table where rowid in(select min(rowid)fromtable group by id..id2); //删除表table中所有关于列(ID)的重复信息
(7)select * from tables where regex_like(id,’^[^[:digit:]]+$’); //查询table在id列中所有是数字的数据
(8)select* from tables where regex_like(id,’^([a-z]|[A-Z]+[0-9]+)$’); //查询table在id列中所有带有字符和数字的数据
(9)select count(*)fromtables; //查询table中所有数据的行数
(10)select table_name from xxcj_tj_zdcj a group by a.table_namecount(*) >1; //查询数据库xxcj_tj_zdcj中是否有重复的表
(11)select * from xxcj_tj_zdcj awhere a.table_name in (‘05255’) order by a.table_name for update; //修改数据库xxcj_tj_zdcj中的值(即括号内的内容)
(12)select * from xxcj_tj_zdcj awhere a.id=’’ for update;//修改数据库xxcj_tj_zdcj中id列的内容
(13)truncate table数据库; //数据比对
(14)select t.* ,t.rowid from 表名 t; //编辑表
(15)alter table 表名 add 列名 字符类型;//新建列
(16)alter table 表名 modify 列名 字符类型;//修改列类型
(17)alter table 表名 drop column 列名;//删除列(最后一列)
(18)select * from 表名 where asciistr(列名)not like ‘%\%’;//查询数据表中xx列中的为中文字符的数据
(19)select substr(f_get_sfzhm18(zjhm) ,7,8)as csrq from 表名where regexp_like(substr(f_get_sfzhm18(zjhm) ,7,1) ,’[^1[2]’);
名称栏目:详解Oracle存储结构掌握基本操作管理
URL网址:http://scyanting.com/article/jdiohp.html