表、约束、索引、表空间、分区、序列、统计信息
一、表及其操作
1、创建表
create table 表名 (
字段名1 字段类型 默认值 是否为空 ，
字段名2 字段类型 默认值 是否为空，
字段名3 字段类型 默认值 是否为空，
......
字段名n 字段类型 默认值 是否为空
);
2、删除表
delete from 表名;
delete删除数据是一条一条的删除数据，后面可以添加where条件，不删除表结构。注意：如果表中有identity产生的自增id列，delete from后仍然从上次的数开始增加。
truncate table 表名;
truncate是一次性删掉所有数据，不删除表结构。注意：如果表中有identity产生的自增id列，truncate后，会恢复初始值。
drop table 表名;
drop删除所有数据，会删除表结构。
3、修改表
(1)添加新字段
alter table 表名 add(字段名 字段类型 默认值 是否为空);
(2)修改字段
alter table 表名 modify (字段名 字段类型 默认值 是否为空);
(3)删除字段
alter table 表名 drop column 字段名;
4、重命名
重命名表： rename 旧表名 to 新表名;
重命名列： alter table 表名 rename column 旧列名 to 新列名;
5、查看表及表结构
select * from user_tables;
select * from all_tables;
select * from dba_tables;
select * from user_tab_columns;
select * from all_tab_columns;
select * from dba_tab_columns;
二、约束及其操作
约束类型有5种：非空(not null)、唯一(unique)、主键(primary key)、外键(foreign key)、检查(check)。
1、添加约束
alter table 表名 add constraint 约束名称 约束类型(字段)[选项]
添加主键约束: alter table 表名 add constraint 约束名称 primary key (字段);
添加外键约束: alter table 表名 add constraint 约束名称 foreign key (字段) references 表名(字段);
添加唯一约束: alter table 表名 add constraint 约束名称 unique(字段);
添加检查约束: alter table 表名 add constraint 约束名称 check(表达式);
添加非空约束: 以上语法不适用于非空约束，添加非空约束只有一种方法，那就是修改表结构.
alter table 表名 modify (字段名 字段类型 默认值 not null);
2、删除约束
alter table 表名 drop constraint 约束名;
3、查看约束
select * from user_constraints;
select * from all_constraints;
select * from dba_constraints;
三、索引及其操作
1、创建索引
create [unique] index 索引名 on 表名(列名1 ASC|DESC,列名2 ASC|DESC,...,列名n ASC|DESC) [tablespace 表空间名];
注：基表中的列名，一个索引最多有16列，long列、long raw列不能建索引列.DESC、ASC 缺省为ASC即升序排序.
2、修改索引
alter [unique] index 索引名 ...
重建索引: alter [unique] index 索引名 rebuild
合并无用的索引空间: alter [unique] index 索引名 coalesce;
3、删除索引
drop index 索引名;
4、查看索引
select * from user_indexes;
select * from all_indexes;
select * from dba_indexes;
select * from user_ind_columns;
select * from all_ind_columns;
select * from dba_ind_columns;
四、序列及其操作
1、创建序列
create sequence 序列名
[increment by n]
[start with n]
[{maxvalue/ minvalue n| nomaxvalue}]
[{cycle|nocycle}]
[{cache n| nocache}];
注：
(1)MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增Oracle序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。
(2)MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是10的26次方;对于递增序列，最小值是1。
2、使用序列
select 序列名.nextval from dual;
select 序列名.currentval from dual;
3、修改序列
alter sequence 序列名 ...
修改序列步长: alter sequence 序列名 increment by n; -- n为修改后的步长
4、删除序列
drop sequence 序列名;
5、查询序列
select * from user_sequences;
select * from all_sequences;
select * from dba_sequences;
五、表空间及其操作
1、创建表空间
create tablespace 表空间名
datafile '数据文件路径' size 500M --存储地址 初始大小500M
autoextend on next 10M maxsize 200M --每次扩展10M，无限制扩展 unlimited
extent management local autoallocate
segment space management auto;
--创建临时表空间
create temporary tablespace 表空间名
tempfile '数据文件路径' size 50m
autoextend on next 50m maxsize 20480m
extent management local;
--建立UNDO表空间
create undo tablespace 表空间名
datafile '数据文件路径' SIZE 50M
注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间:
ALTER SYSTEM SET undo_tablespace=表空间名;
2、扩展表空间
(1)首先查看表空间的名字和所属文件
select tablespace_name, file_id, file_name, round(bytes/(10241024),0) total_space from dba_data_files order by tablespace_name;
(2)增加数据文件
alter tablespace 表空间名 add datafile '数据文件路径' size 1000M;
(3)手动增加数据文件尺寸
alter database datafile '数据文件路径' resize 4000M;
(4)设定数据文件自动扩展
alter database datafile '数据文件路径' autoextend on next 100M maxsize 10000M;
(5)设定后查看表空间信息
select a.tablespace_name,a.bytes total,b.bytes used, c.bytes free, (b.bytes100)/a.bytes "% used",(c.bytes*100)/a.bytes "% free"
from sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name;
3、删除表空间
drop tablespace 表空间名 including comments and datafiles;
4、改变表空间状态
(1)使表空间脱机
alter tablespace 表空间名 offline;
如果是意外删除了数据文件，则必须带有recover选项
alter tablespace 表空间名 offline for recover;
(2)使表空间联机
alter tablespace 表空间名 online;
(3)使数据文件脱机
alter database datafile '数据文件路径' offline;
(4)使数据文件联机
alter database datafile '数据文件路径' online;
(5)使表空间只读
alter tablespace 表空间名 read only;
(6)使表空间可读写
alter tablespace 表空间名 read write;
5、查询表空间
select * from dba_data_files;
六、分区及其操作
[1]oracle表分区详解 https://www.cnblogs.com/andy6/p/6238512.html
[2]oracle表分区增加分区 https://www.cnblogs.com/jadic/archive/2012/10/16/2725454.html
[3]Oracle删除表分区 https://www.cnblogs.com/lj821022/p/4958262.html
[4]Oracle 分区表的新增、修改、删除、合并。普通表转分区表方法 https://www.cnblogs.com/flowerszhong/p/4535206.html
[5]Oracle分区表及分区索引的创建 https://blog.csdn.net/linux__xu/article/details/80957783
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表.
分区种类：Range分区、Hash分区、List分区、组合分区（Range-hash、Range-list、RANGE-RANGE、LIST-RANGE、LIST-HASH、LIST-LIST）.
下面以Range分区为例:
1、创建分区表
CREATE TABLE part_test
(
andy_ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR2(30) NOT NULL,
LAST_NAME VARCHAR2(30) NOT NULL,
PHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(80),
STATUS CHAR(1)
)
PARTITION BY RANGE (andy_ID)
(
PARTITION PART1 VALUES LESS THAN (10000),
PARTITION PART2 VALUES LESS THAN (20000),
PARTITION PART2 VALUES LESS THAN (maxvalue)
);
2、增加分区
alter table 表名 add partition 分区名 ... [tablespace 表空间名];
如：
alter table part_test add partition PART3 values less than (3000);
3、删除分区
alter table 表名 drop partition 分区名 UPDATE GLOBAL INDEXES;
ALTER INDEX 索引名 REBUILD（如果含有全局索引）;
如：
alter table part_test drop partition PART3 UPDATE GLOBAL INDEXES;
注：truncate分区
①truncate一个分区
alter table 表名 truncate partition 分区名;
alter index 索引名 rebuild;
②如果存在约束的情况，先disable约束
alter table 表名 disable constraint 约束名;
alter table 表名 truncate partittion 分区名;
alter table 表名 enable constraint 约束名;
4、修改分区
重命名一个分区： alter table 表名 rename partition 旧分区名 to 新分区名
移动一个分区： alter table 表名 move partition 分区名 tablespace 表空间名 nologging;
合并分区： alter table 表名 merge partitions 分区名1, 分区名2 into partition 分区名3;
一个分区拆分为两个分区: alter table 表名 split partition 分区名1 at (分区临界值)
into (partition 分区名2 tablespace 表空间名1,
partition 分区名3 tablespace 表空间名2);
重建一个local索引： alter index 索引名 rebuild partition 分区名;
5、查询分区
select * from user_part_tables;
select * from all_part_tables;
select * from dba_part_tables;
七、统计信息
[1]收集oracle统计信息 https://www.cnblogs.com/rootq/archive/2010/02/04/1663622.html
[2]DBMS_STATS包方法详解 https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_stats.htm#CIHGBFIC
[3]DBMS_STATS常用方法（收集oracle信息） https://www.cnblogs.com/rusking/p/5867712.html
[4]使用DBMS_STATS来收集统计信息 https://www.cnblogs.com/perfei/archive/2004/01/13/5143171.html
[5]DBMS_STATS分析表 https://www.cnblogs.com/rootq/archive/2008/12/01/1345049.html
统计信息可以通过ANALYZE命令或者DBMS_STATS包来收集。在COB模式下，DBMS_STATS包是DBA管理统计信息的有力工具。DBMS_STATS包允许管理员以调用过程的方式创建，编辑，查看和删除统计信息。它可以收集表、索引、列、分区和模式的统计信息，但是它不可以生成cluster的统计信息。
1、收集统计信息
dbms_stats.gather_database_stats 收集数据库统计信息
dbms_stats.gather_schema_stats 收集schema下所有对象的统计信息
dbms_stats.gather_table_stats 收集表、列和索引的统计信息
dbms_stats.gather_index_stats 收集索引的统计信息
dbms_stats.gather_system_stats 收集系统统计信息
2、删除统计信息
dbms_stats.delete_database_stats 删除数据库统计信息
dbms_stats.delete_schema_stats 删除schema下所有对象的统计信息
dbms_stats.delete_table_stats 删除表的统计信息
dbms_stats.delete_column_stats 删除列的统计信息
dbms_stats.delete_index_stats 删除索引的统计信息
3、手动设置统计信息
dbms_stats.set_table_stats 设置表的统计信息
dbms_stats.set_column_stats 设置列的统计信息
dbms_stats.set_index_stats 设置索引的统计信息
4、查看统计信息
(1)表级的统计信息
select table_name,num_rows,blocks,empty_blocks,avg_space from user_tables where table_name = 表名;
(2)表上列的统计信息
select table_name,column_name,num_distinct,density from user_tab_columns where table_name = 表名;
(3)表上列的直方图信息
select table_name,column_name,endpoint_number,endpoint_value from user_tab_histograms
where table_name = 表名 and column_name = 列名;
(4)分区的统计信息
select partition_name,num_rows,blocks,empty_blocks,avg_space from user_tab_partitions where table_name = 表名;
(5)分区上列的统计信息
select column_name,num_distinct,density,num_nulls from user_part_col_statistics
where table_name = 表名 and partition_name = 分区名;
(6)分区上列的直方图信息
select column_name,bucket_number,endpoint_value from user_part_histograms
where table_name = 表名 and partition_name = 分区名 and column_name = 列名;
(7)子分区的统计信息
select subpartition_name,num_rows,blocks,empty_blocks from user_tab_subpartitions
where table_name = 表名 and partition_name = 子分区名;
(8)子分区上的列的统计信息
select column_name,num_distinct,density from user_subpart_col_statistics
where table_name = 表名 and subpartition_name = 子分区名;
(9)子分区上的列的直方图信息
select column_name,bucket_number,endpoint_value from user_subpart_histograms
where table_name = 表名 and subpartition_name = 子分区名 and column_name = 列名;
5、统计信息定时任务
从10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。
这个自动任务默认情况下在工作日晚上10：00-6：00和周末全天开启。
调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。
可以通过以下查询这个JOB的运行情况：
SELECT * FROM Dba_Scheduler_Jobs WHERE Job_Name = 'GATHER_STATS_JOB';
然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。
而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。
所以建议最好关闭这个自动统计信息收集功能：
关闭及开启自动搜集功能，有两种方法，分别如下：
方法一：
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
方法二：
alter system set "_optimizer_autostats_job"=false scope=spfile;
alter system set "_optimizer_autostats_job"=true scope=spfile;