Oracle对象管理

一、用户管理

1、创建表空间

create tablespace school        #指定表空间名称datafile '/orc/app/oracle/oradata/school01.dbf' #指定数据文件路径size 200M   #指定表空间大小autoextend on       #设置表空间自动扩展

2、创建用户

create user c##tom      #创建用户"Tom"identified by abc123    #设置用户密码"abc123"default tablespace school   #指定默认表空间"school"temporary tablespace temp   #指定默认临时表空间"temp"quota unlimited on school  #针对"school"表空间不做磁盘配额限制password expire;    #设置用户每次登录，强行修改密码

更改用户密码
alter user c##tom identified by 123123; #将用户"tom"密码更改为"123123"
删除用户
drop user c##tom cascade;
3、用户授权

grant connect,resource to c##tom;   #connect为连接权限;resource为管理数据库权限revoke connect,resource from c##tom;        #撤销用户授权（sysdba用户下）

以tom身份登录
方法一：
sqlplus #输入用户名与口令！
方法二：
SQL> conn
注意：tom与school表空间绑定，所以写入数据都将保存在school文件中！
4、提交事务（默认开启事务）

commit;    #注意，oracle默认是手动提交事务，对数据编辑完之后，必须使用commit进行提交rollback;   #注意，事务回滚使用此命令set autocommit on;      #设置自动提交

二、索引

1、说明
1) 索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
2) 索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率
3) 索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；
4) 索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响
5) 索引对用户是透明的，无论表上是否有索引，sql语句的用法不变
6）oracle创建主键时会自动在该列上创建索引
2、索引原理
1) 若没有索引，搜索某个记录时（例如查找name='wish'）需要搜索所有的记录，因为不能保证只有一个wish，必须全部搜索一遍
2) 若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值哪找升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方
3) 创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引
3、索引简历原则
1)如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
2)至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
3)小表不要简历索引
4)对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引
5)列中有很多空值，但经常查询该列上非空记录时应该建立索引
6)经常进行连接查询的列应该创建索引
7)使用create index时要将最常查询的列放在最前面
8)LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引
9)限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）
4、索引使用（创建、修改、删除、查看）
创建索引
Create index index_name on table_name(column)
修改索引
1)重命名索引
alter index index_sno rename to bitmap_index;
2)合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）
alter index index_sno coalesce;
3)重建索引
方式一：删除与原来的索引，重新建立索引
方式二：alter index index_sno rebuild;
删除索引
drop index index_sno;
查看索引
select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename'; #查看索引
例子：

create index index_sno on student('name');select * from all_indexes where table_name='student';

5、索引的分类
A、B数索引（默认索引，保存排序过的索引列和对应的rowid值）
说明：
1)oracle中最常用的索引；B树索引就是一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值
2)所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同
3)能够适应精确查询、模糊查询和比较查询
分类：
UNIQUE,NON-UNIQUE(默认),REVERSE KEY（数据列中的数据是反向存储的）
创建例子

create index index_info on info(score) ;create unique index uni_index_info on info(id); #唯一索引create index rev_index_info on info(createtime) reverse;    #反向索引

使用场景
列基数（列不重复值的个数）大时适合使用B数索引
B、位图索引
说明：
创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索引中的映射函数完成位到行的ROWID的转换
创建例子：
create bitmap index bt_index_info on info(address);
使用场景：
对于基数小的列适合简历位图索引（例如性别等）
C、单列索引和复合索引（基于多个列创建）
注意：
即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列
D、函数索引
说明：
1)当经常要访问一些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度
2)函数索引既可以使用B数索引，也可以使用位图索引；当函数结果不确定时采用B树索引，结果是固定的某几个值时使用位图索引
3)函数索引中可以水泥用len、trim、substr、upper（每行返回独立结果），不能使用如sum、max、min、avg等
创建列子：

create index up_index_info on info(upper(name));  #大写函数索引select * from student where upper(name) ='WISH';    #验证查看

三、视图

1、视图的定义
视图（view），也称虚表，不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候,只是重新执行SQL。
视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。
视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。
视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些Oracle视图可以修改对应的基表，有些则不能（仅仅能查询）。
还有一种视图：物化视图（MATERIALIZED VIEW ），也称实体化视图，快照 （8i 以前的说法） ，它是含有数据的，占用存储空间。
查询视图没有什么限制, 插入/更新/删除视图的操作会受到一定的限制; 所有针对视图的操作都会影响到视图的基表; 为了防止用户通过视图间接修改基表的数据, 可以将视图创建为只读视图(带上with read only选项)
2、视图的作用
1)提供各种数据表现形式, 可以使用各种不同的方式将基表的数据展现在用户面前, 以便符合用户的使用习惯(主要手段: 使用别名)；
2)隐藏数据的逻辑复杂性并简化查询语句, 多表查询语句一般是比较复杂的, 而且用户需要了解表之间的关系, 否则容易写错; 如果基于这样的查询语句创建一个视图, 用户就可以直接对这个视图进行"简单查询"而获得结果. 这样就隐藏了数据的复杂性并简化了查询语句.这也是oracle提供各种"数据字典视图"的原因之一,all_constraints就是一个含有2个子查询并连接了9个表的视图(在catalog.sql中定义)；
3)执行某些必须使用视图的查询. 某些查询必须借助视图的帮助才能完成. 比如, 有些查询需要连接一个分组统计后的表和另一表, 这时就可以先基于分组统计的结果创建一个视图, 然后在查询中连接这个视图和另一个表就可以了；
4)提供某些安全性保证. 视图提供了一种可以控制的方式, 即可以让不同的用户看见不同的列, 而不允许访问那些敏感的列, 这样就可以保证敏感数据不被用户看见；
5)简化用户权限的管理. 可以将视图的权限授予用户, 而不必将基表中某些列的权限授予用户, 这样就简化了用户权限的定义。
3、普通视图
1)创建普通视图

grant create view to c##tom;        #Sysdba身份登录为用户创建视图授权SQL> create view view_info as select * from info;

2)查看视图

SQL> select * from view_info;
3)删除视图
SQL> drop view view_info;
4、物化视图
1)切换dba身份
SQL> conn / as sysdba
2)授权

SQL> grant create materialized view to c##tom;     #授予创建物化视图权限SQL> grant query rewrite to c##tom;        #授予查询、重写权限SQL> grant create any table to c##tom;     #授予创建任何表权限            SQL> grant select any table to c##tom;   #授予查询任何表权限

3)创建物化视图日志

SQL> conn            #连接tom用户SQL> create materialized view log on info;

4)创建物化视图

SQL> create materialized view mtrlview_pro  2  build immediate     #创建物化视图是否立即生成数据，immediate代表true  3  refresh fast    #设置与基表进行同步更新，如果不添加此参数，相当于快照功能  4  on commit        #开启提交功能  5  enable query rewrite      #开启查询、重写功能  6  as select * from info;

5)删除视图

SQL> drop materialized view mtrlview_pro;

四、导入导出数据

drop index up_index_info;   #删除前面创建的索引，否则无法更改列字段的属性alter table info modify name varchar2(11);   #更改name字段的位宽begin  2  for i in 2..100  3  loop  4  insert into info values(i,'zhangsan'||i,88,to_date('2018-10-10','yyyy-mm-dd'),'nanjing');  5  end loop;  6  commit;  7  end;  8  /

select count(*) from info; //最终查看数据量
1、数据导入：

ho ls /home/oracle      #oracle软件可以兼容Linux 使用ho命令跟上liunx命令查看家目录文件用指定用户导入数据imp c##tom/abc123 full=y file=/home/oracle/test.dmpselect * from tab;  #查看相关表信息

2、导出数据

exp c##tom/abc123 file=/home/oracle/test.dmp #指定用户进行导出数据ls /home/oracle/        //查看验证

五、序列

1、序列定义
序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存。
其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。
序列是oracle用来生产一组等间隔的数值。序列是递增，而且连续的。oracle主键没有自增类型，所以一般使用序列产生的值作为某张表的主键,实现主键自增。序列的编号不是在插入记录的时候自动生成的，必须调用序列的方法来生成（一般调用nextval方法）。我们也可以编写表的insert触发器来进自动生成。
2、创建序列

SQL> create sequence toy_seq  2  start with 1              #指定初始值   3  increment by 1            #指定增量  4  maxvalue 2000             #指定最大值  5  nocycle                   #指定工作模式为非循环  6  cache 30;                 #指定缓存区30个数值，空闲等待

3、添加数据

Delete from info;   #清空表SQL> insert into info values (toy_seq.nextval,'Sony',999999,to_date('2018-10-09','yyyy-mm-dd'),'ok');       #插入值多次执行该语句，发现ID对应的序列号在自增Select * from info;  #查看验证

4、查看序列
SQL> select toy_seq.currval from dual; #查看当前序列的值

SQL> select sequence_name,increment_by,cache_size from user_sequences; #查看序列的相关信息

5、更改序列
SQL> alter sequence toy_seq maxvalue 5000 cycle;
6、删除序列
SQL> drop sequence toy_seq;

六、同义词

1、定义
从字面上理解就是别名的意思，和视图功能类似。就是一种映射关系。主要分为私有（用户独有）和公有（系统用户共有）
2、私有同义词管理
1)创建私有同义词

SQL> grant create synonym to c##tom     #dba为用户创建同义词授权SQL> show user;   #查看当前用户SQL> create synonym pr_info for info;      #为表设置了一个别名"pr_info"

2)调用私有同义词

SQL> select * from pr_info; #注意，此时定义的为私有同义词，只是对当前用户有效，切换另一个用户无法识别
3、公有同义词管理
1)授予用户权限

SQL> conn / as sysdba    #管理员登录 SQL> grant create public synonym to c##tom; #为用户创建公有同义词授权

2)创建公有同义词

SQL> conn c##tom/abc123  #连接普通用户SQL> create public synonym public_sy_info for info;

3)查看公有同义词

SQL> conn / as sysdba    #管理员登录SQL> select * from public_sy_info;   #切换dba之后可以查看，dba中并没有info表，说明共有同义词生效

七、分区表

1、定义
以某张表的某个字段为依据，将数据分散存储在不同表空间中
2、建立若干表空间

SQL> show user;  #当前用户为系统管理员SQL> create tablespace tmp01   #注意建立4个表空间，依次为tmp01、tmp02、tmp03、tmp04  2  datafile '/orc/app/oracle/oradata/tmp01.dbf'  3  size 100M;SQL> edit       #编辑之前的操作将便空间名和dbf文件名修改SQL> /  #指定edit操作重复操作创建好四个表空间SQL> select tablespace_name from dba_tablespaces;       //查看表空间是否创建成功

3、创建数据表

SQL> create table sales       #创建表  2  (  3  sales_id number(4),  4  product_id varchar2(5),  5  sales_date date  6  )  7  partition by range (sales_date)   #指定按时间字段进行分区  8  (  9   partition p1 values less than (to_date('2018-04-03','yyyy-mm-dd')) tablespace tmp01,          #p1指定名称；less than小于指定的时间;tablespace指定表空间 10   partition p2 values less than (to_date('2018-05-03','yyyy-mm-dd')) tablespace tmp02, 11   partition p3 values less than (to_date('2018-06-03','yyyy-mm-dd')) tablespace tmp03, 12   partition p4 values less than (maxvalue) tablespace tmp04 13  );

4、插入测试数据

insert into sales values (1,'ttt1',to_date('2018-03-23','yyyy-mm-dd'));     #应该存放在P1insert into sales values (1,'ttt1',to_date('2018-04-23','yyyy-mm-dd'));     #应该存放在P2 insert into sales values (1,'ttt1',to_date('2018-05-23','yyyy-mm-dd'));    #应该存放在P3 insert into sales values (1,'ttt1',to_date('2018-06-23','yyyy-mm-dd'));    #应该存放在P4

5、查询分区验证
SQL> select * from sales partition(P1); #按照时间分散存储，已经存储到P1中

八、控制文件

1、定义
控制文件中记录了oracle数据库的物理结构，也就是记录了数据库数据文件和日志文件的位置，控制文件中还记录了多种SCN，用这些SCN来确定数据文件和日志文件是否是正确的。如果不正确那么数据库就需要恢复。
2、查看控制文件

3、备份控制文件
alter database backup controlfile to '/home/oracle/controlfile.bk';