3、oracle表空间及索引操作
3.1、创建表空间和用户授权：
1、创建表空间：
CREATE TABLESPACE <表空间名> LOGGING DATAFILE '<存放路径>' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 31768M EXTENT MANAGEMENT LOCAL;
#windows存放路径：D:\app\Administrator\oradata\orcl\lc_data.dbf
#linux存放路径：/application/oracle/oradata/orcl/lc_data.dbf
2、创建用户并指定表空间：
CREATE USER <用户名> IDENTIFIED BY <密码> DEFAULT TABLESPACE <表空间名>;
#一个用户只有一个表空间，而表空间可以有多个用户；
3、给用户授予权限：
grant connect,resource,dba to <用户名>;
3.2、删除表空间：
1、删除用户：
drop user <用户名> cascade;
2、删除表空间：
drop tablespace <表空间名> including contents and datafiles cascade constraints;
#including contents：删除表空间中的内容，如果删除表空间之前表空间中有内容，而未加此参数，表空间无法删除；
#including datafiles：删除表空间中的数据文件；
#cascade constraints：删除表空间中表的外键参照；
3.3、表空间查询操作：
1、查询所有表空间及对应的路径：
select tablespace_name,file_name from dba_data_files;
2、查询所有表空间的状态信息；
select tablespace_name,status from dba_tablespaces;
3、增加表空间大小：
ALTER TABLESPACE <表空间名> ADD DATAFILE '<存放路径>' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
4、查看所有用户以及对应的表空间：
select username,default_tablespace from dba_users;
5、查看当前用户的缺省表空间：
select username,default_tablespace from user_users;
6、表空间之间的链接：
（1）创建表空间之间的链接：
create database link TO_<表空间名>_LINK connect to <用户名> identified by <密码> using '<数据库实例名>';
（2）查询表空间之间的链接：
select * from <当前表空间名>@TO_<表空间名>_LINK;
（3）删除表空间之间的链接：
drop database link TO_<表空间名>_LINK;
7、查询所有表空间的用量：
select tablespace_name,count(*) AS extends,round(sum(bytes)/1024/1024,2) AS MB,sum(blocks) AS blocks from dba_free_space group BY tablespace_name;
8、查看序列号，last_number是当前值：
select * from user_sequences;
9、修改表空间属性(离线)：
alter tablespace <表空间名> offline;
10、修改表空间属性(在线)：
alter tablespace <表空间名> online;
11、修改表空间属性(只读)：
alter tablespace <表空间名> read only;
12、修改表空间属性(读写)
alter tablespace <表空间名> read write;
13、修改session的时间格式：
alter session set nls_date_format='yyyy-mm-dd';
3.4、索引操作：
1、创建单一索引：
create index <索引名> on <表名>(<列名1>);
2、创建组合索引：
create index <索引名> on <表名>(<列名1>,<列名2>);
select * from <表名> where <列名1>='<字符>'
#走索引
select * from <表名> where <列名2>='<字符>'
#不走索引
select * from <表名> where <列名1> like '%<字符>%'
#不走索引
select * from <表名> where <列名1>='<字符>' and <列名2>='<字符2>'
#走索引
select * from <表名> where <列名1>='<字符>' or <列名2>='<字符2>'
#不走索引
drop index <索引名称>;
#删除索引
3、查看索引的方法：
（1）在当前用户中查找表名：
select * from user_tables where table_name like '<表名>%';
（2）查询该表的所有索引：
select * from user_indexes where table_name='<表名>';
（3）查询该表的所有索引列：
select * from user_ind_columns where table_name='<表名>';
（4）查询当前用户所有表的索引和索引类别：
select table_name,index_name,index_type from user_indexes order by index_name;
（5）查看当前用户下指定索引的信息：
select * from user_indexes where index_name=upper('&index_name');
（6）查看当前用户下指定的索引的索引列：
select * from user_ind_columns where index_name=upper('&index_name');
（7）查看当前用户下指定索引的大小：
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');
（8）补充：
user_indexes #存放着当前用户所有表的索引信息；
user_segments #存放着当前用户所有表的索引大小；
user_ind_columns #存放着当前用户所有表的索引列信息；
4、索引补充：
（1）哪些列适合建索引：
1）经常出现在where子句的列；
2）经常用于表连接的列，在匹配表上进行建索引；
3）该列是高基数数据列，高基数数据列是指有很多不同的值；
4）索引里面不计null值；
5）表很大，查询结果集小；
6）在pk、uk、fk键上建立索引；
7）经常需要排序"order by"和分组"group by"的列；
（2）索引用不了的写法：
1）函数导致索引用不了 where upper（colname）= 'char'；
2）可以对函数建索引：
create index <索引名> on <表名>(round(<列名1>));
3）表达式导致索引用不了 where colname*12=1200；
4）索引不是万能的；
（3）索引结构：
1）分析索引结构有效性：
analyze index <索引> validate structure;
一般来讲默认的方式是offline；
当以offline的模式analyze索引时，会对table加一个表级共享锁，对目前table的一些实时DMl操作会产生一定的影响；
而以online模式分析时候，则不会加任何lock，但在index_stats中是看不到任何信息的；
2）查看索引结构：
select NAME,HEIGHT,BLOCKS,BR_BLKS,BR_ROWS,LF_BLKS,LF_ROWS from index_stats;
3）合并索引叶级块碎片:
alter index <索引名> coalesce;
4）重建索引：
alter index <索引名> rebuild;
