1、单表创建
直接创建
Create table table_name
(
（增加权限）
（收回权限）
字段 1类型 1，
字段 2类型 2，
、、、、、
字段 n类型 n
) tablespace_name;
创建类似表
Create table table_name as select * from table_name1;
创建表结构：
Create table table_name as select * from table_name字段 1=字段 2；
表查询：
Select * from table_name;
Select * from table_name where条件
Select字段 1别名 1，字段 2
别名 2，字段 n别名 n from table_name
字段中可以进行代数运算，包括：＋－×÷，countsunavgetrunkround等函数
表插入：
Insert字段 1，字段 2、、字段 n into table_name value (字段值 1，字段值 2、、字段值 n )；
循环插入：
begin
for i in 1..10 loop
insert into table_name values (…);
end loop;
end;
表更新：
Update table_name set字段 1 where条件
表删除：
drop table table_name
表记录删除：
delete table_name where条件；
truncate table table_name清空表记录，保留表结构
表名修改：
rename table_name1 to table_name2
注意：表名修改后，索引还在
表迁移，（从一个表空间迁移到另外一个表空间）
alter table table_name move tablespace tablespace_name nologging parallele 4 ;
注意：将表从一个表空间迁移到另外一个表空间，必须进行索引重建，存储过程、触发器、其
他程序包都需要编译，以免执行报错。例如：
表压缩：
不同表空间： alter table table_name move tablespace tablespace_name compress;
相同表空间：alter table table_name move compress;
表统计信息收集
exec dbms_stats.gather_table_stats(ownname => ‘username’,tabname
=>’table_name’,degree =>10,cascade =>true,estimate_percent =>25);
例如：
exec dbms_stats.gather_table_stats(ownname => ‘test’,tabname
=>’data_table1_name1′,degree =>4,cascade =>true,estimate_percent =>30);
select a.row2_id, sum(nvl(b.item_value, 0))
from zk.name _201001 a, zk.table_name _201001 b
where a.so_row1 = b.so_row1 and a.row_a in (1, 4, 5) and a.row2_id = XXXXXXX and
book_row3_id in (NNNNNXXX1, NNNNNXXX2, NNNNNXXX3, NNNNNXXX4,
NNNNNXXX5, NNNNNXXX6) group by a.row _id;
表语句执行很慢，检查执行计划，zk.table_name_201001 b不引用索引，做表分析、重建索引都无
效，执行计划中均没有引用索引，最后强制使用索引效果明显，执行计划改变，索引引用。效果明
显
select /*+ index(b pk_table_name_201001)*/
a.serv_id, sum(nvl(b.item_value, 0))
from zk.name_201001 a, zk. table_name _201001 b
where a. so_row1 = b.so_row1 and a.busi_code in (1, 4, 5) and a.row2_id = XXXXXXX and
book_row3_id in (NNNNNXXX1, NNNNNXXX2, NNNNNXXX3, NNNNNXXX4,
NNNNNXXX5, NNNNNXXX6)
group by a.row2_id;
2、分区表创建
为了使大量的数据在读写操作和查询中速度更快，Oracle提供了对表和索引进行分区的技术，
以改善大型应用系统的性能。使用分区的优点：
A、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
B、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
C、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；
D、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度
Oracle数据库提供对表或索引的分区方法有三种：
1、范围分区
2、Hash分区（散列分区）
3、列表分区
4、范围—散列分区
5、范围—列表分区
2.1、范围分区表
分区表表创建：一般都是按照时间来创建
create table table_name
(
row1 char1 not null,
row2 char2 not null,
、、、、、、
rown number not null
)
partition by range (paration_row)
(
partition partition_name1 values less than (to_date(‘value_name’, ‘yyyy-mm-dd’,
‘nls_calendar=gregorian’)) tablespace tablespace_name1,
partition partition_name2 values less than (to_date(‘value_name’, ‘yyyy-mm-dd’,
‘nls_calendar=gregorian’)) tablespace tablespace_name2,
、、、、、、、、、、
partition partition_namen values less than (to_date(‘value_name’, ‘yyyy-mm-dd’,
‘nls_calendar=gregorian’)) tablespace tablespace_namen,
partition partmax values less than (maxvalue)
tablespace ora_data
pctfree 10
initrans 16
maxtrans 255
storage
(
initial 64k
minextents 1
maxextents unlimited ))
表插入：
Insert into table_name1 as select * from table_name2
表分区删除：
alter table表名 truncate partition分区名称 drop storage;
注意：分区删除以后，需要重新创建索引。
分区分割：
alter table table_name split partition partmax at(to_date(‘values_name’, ‘yyyy-mm-dd’)) into
(partition prtition_namexx tablespace tablespace_name , partition partmax);
分区查询：
select * from table_name partition (partition_name);
2.2、hash分区表（散列分区）
Hash分区表创建：hash分区表的分区数量一般是 2的 n次幂，这样记录分布在各个分区上就
比较均匀，可以进行 I/O的均衡。
create table table_name
(
row1 char1 not null,
row2 char2 not null,
、、、、、、
rown NUMBER not null
)
partition by hash (paration_row)
(
partition partition_name1 tablespace tablespace_name1,
partition partition_name2 tablespace tablespace_name2,
partition partition_name3 tablespace tablespace_name3
、、、、、、、、、、
partition partition_nameX tablespace tablespace_nameX
)
表插入：
insert into table_name1 as select * from table_name2
分区查询：
Select * From table_name Partition (partition_name); hash分区表一般由于数据分布均衡性，查
询不是
通过对分区的操作进行的。update、insert操作同普通表。
2.3、列表分区表
散列分区表创建：
create table table_name
(
row1 char1 not null,
row2 char2 not null,
、、、、、、
rown number not null
)
partition by list (paration_row)
(
partition partition_name1
values
(row_value1,row_value3,row_value8)
tablespace
tablespace_name1,
partition partition_name2
values
(row_value4,row_value6,row_value7)
tablespace
tablespace_name2,
partition
partition_name3
values
(row_value9,row_value10,row_value11,row_value12,row_value15)
tablespace tablespace_name3
、、、、、、、、、、
partition partition_nameX values (default) tablespace tablespace_nameX
)
表的插入、更新、删除和普通表相同，在 hash分区和 list分区中，比较困难的操作是：从一张 5
千万以上的表中，要删除一千万条记录比较困，可以通过以下方法进行清理：
第一种方法：
a创建中间表和表备份，b进行数据插入，c进行锁表，d进行表名修改，e进行索引创建
和存储过程编译，f进行表 truncate操作
第二种方法：直接创建 job进行 delete删除：这样长期做，会降低表的执行效果
a进行备份数据，b进行表记录删除，每次删除 1000——2000条记录，不然会发生锁表，c重
建索引，d进行表分析。
declare
v_lognum number; —数据库中拥有的日志文件数
v_needarc number; —需要归档的日志文件数
begin
select count(1) into v_lognum from v$log;
loop
loop
select count(1) into v_needarc from v$archive;
if v_needarc < v_lognum – 1 then
exit;
end if;
end loop;
delete from
对象.table_name where条件 1 and条件 2 and rownum<1000;
if sql%rowcount = 0 then
exit;
end if;
commit;
end loop;
end;
––––––————— — – – – – – – – – – – – – – – – – – – – – – –
create or replace procedure procedure_name(exp_date1 varchar2 ,exp_date2 varchar2)
as
del_sql varchar2(1024);
v_c integer;
begin
del_sql:=’delete from对象.表名 a where exists (select 1 from对象.表名 b where
条件 1 and
条件 2 and、、、、
条件 n）and rownum<1000′;
for i in 1..10000 loop
execute immediate del_sql using exp_date1,exp_date2;
if sql%rowcount = 0 then
exit;
end if;
commit;
end loop;
end procedure_name;
3、分区操作
添加分区
alter table table_partition_name add partition partition_name values less than
(to_date(‘row_values’,’yyyy-mm-dd’));
注意：以上添加的分区界限应该高于最后一个分区界限。
alter table table_partition_name
modify partition partition_name
add
subpartition
subpartition_name
values(‘row_values’);
删除分区
以下代码删除表分区：
alter table table_partition_name drop partition partition_name storage;
注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除
表
截断分区
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表
中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：
alter table table_partition_name truncate partition partition_name;
合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能
将分区合并到界限较低的分区。以下代码实现了 partition_name1与 partition_name分区的合并：
alter table table_partition_name merge partitions partition_name1,partition_name2 into partition
partition_name2;
拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对 HASH类型的分
区进行拆分。
alter table table_partition_name sblit partition partition_name1
at(to_date(‘row_value’,’yyyy-mm-dd’))
into (partition partition_name1,partition partition_name2);
接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加
散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合
分区：
alter table table_hash_partition_name coalesca partition;
重命名表分区
以下代码将 partition_name1更改为 partition_name2
alter table table_partition_name rename partition partition_name1 to partition_name2;
相关查询
跨分区查询
select sum( *) from
(select count(*) cn from table_partition_name partition (partition_name1)
union all
select count(*) cn from table_partition_name partition (partition_name2));
查询表上有多少分区
select * from user_tab_partitions where table_name =’table_partition_name’
查询索引信息
select object_name,object_type,tablespace_name,sum(value)
from v$segment_statistics
where statistic_name in (‘physical reads’,’physical write’,’logical reads’)and object_type=’index’
group by object_name,object_type,tablespace_name order by 4 desc
—显示数据库所有分区表的信息：
select * from dba_part_tables
—显示当前用户可访问的所有分区表信息:
select * from all_part_tables
—显示当前用户所有分区表的信息：
select * from USER_PART_TABLES
—显示表分区信息显示数据库所有分区表的详细分区信息：
select * from dba_tab_partitions
—显示当前用户可访问的所有分区表的详细分区信息：
select * from all_tab_partitions
—显示当前用户所有分区表的详细分区信息：
select * from user_tab_partitions
—显示子分区信息显示数据库所有组合分区表的子分区信息：
select * from dba_tab_subpartitions
—显示当前用户可访问的所有组合分区表的子分区信息：
select * from all_tab_subpartitions
—显示当前用户所有组合分区表的子分区信息：
select * from user_tab_subpartitions
—显示分区列显示数据库所有分区表的分区列信息：
select * from dba_part_key_columns
—显示当前用户可访问的所有分区表的分区列信息：
select * from all_part_key_columns
—显示当前用户所有分区表的分区列信息：
select * from user_part_key_columns
—显示子分区列显示数据库所有分区表的子分区列信息：
select * from dba_subpart_key_columns
—显示当前用户可访问的所有分区表的子分区列信息：
select * from all_subpart_key_columns
—显示当前用户所有分区表的子分区列信息：
select * from user_subpart_key_columns
—怎样查询出 oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned=’yes’
—删除一个表的数据是
truncate table table_name;
—删除分区表一个分区的数据是
alter table table_partition_name truncate partition partition_nameN drop storage;
4、表状态查询
Select * From Dba_Tab_Partitions Where Table_Name=’%表名%’;
Select owner,index_name,status,degree,table_name from dba_indexes where
table_name=’table_name ’;
select owner,bytes/1024/1024,segment_nam,segment_type,tablespace_name from dba_segments
where
segment_name=’table_name’ and segment_type=’TABLE’ ;
5、DDL语句操作
创建表
基本语法：
create [global temporary] table table_name(
column_name type [constraint constraint_def default default_exp]
[,column_name type [constraint constraint_def default default_exp] …])
[on commit {delete | preserve} rows]
tablespace tab_space;
其中：
1)global temporary说明改表的行都是临时的，这种表就称为临时表。
行的有效期由 on commit字句指定。临时表对于所有的会话都是可见的，但是这些行则是特定于
某个会话的。
2)table_name指定了要分配给该表的名称。
3)column_name指定了要分配给某个列的名称。
4)type指定了对某个列的类型。
5)constraint_def指定了对某个列的约束的定义。
6)default_def指定了一个表达式，用来为某个列赋予默认值。
7)on commit控制临时表中行的有效期。delete说明这些行在事务的末尾要被删除。
preserve说明这些行在会话的末尾要被删除。若对临时表没有指定 on commit选项，那末默认值是
delete。
8)tab_space为该表指定表空间。若没有指定表空间，该表就被存储在该用户的默认表空间中。
获得有关表的信息可以通过如下操作获取有关表的信息：
对表执行 describe命令。
desc order_status_temp
—注意 desc是 SQL*PLUS命令，在 SQL中不能执行。
查询 user_tables，它是数据字典的一部分。另：查询用户可访问的表的相关信息，可以查询
all_tables。
select table_name, tablespace_name, temporary
from user_tables
where table_name in (‘table_name’, ‘ORDER_STATUS_TEMP’);
获得表中列的信息
从 user_tab_columns中可以获得有关表中各列的信息,另：通过访问 all_tab_columns,可以获得有关
可以访问的表中所有列的信息。
select column_name, data_type, data_length, data_precision, data_scale
from user_tab_columns
where table_name = ‘table_name’;
修改表
alter table语句可以用于对表进行修改。
alter table语句可以执行以下任务：
1)添加、修改、删除列；
2)添加或删除约束；
3)启用或禁用约束。
添加列
alter table table_name add modified_by integer;
alter table table_name add rowname类型 [date default sysdate not null];
修改列
1)修改列的长度，条件是该列的类型的长度可以修改，比如 char或 varchar2；
2)修改数字列的精度；
3)修改列的数据类型；
4)修改列的默认值。
修改列的长度
alter table table_name modify rowname类型(长度);
只有在表中还没有任何行或所有列都为空值的情况下才可以减小列的长度。
修改数字列的精度
alter table table_name modify column类型;
只有在表中还没有任何行或列为空值时才可以减小数字列的精度。
修改列的数据类型
alter table table_name modify column类型;
若一个表中还没有任何行或列为空值，就可以将列修改为任何一种数据类型。否则，就只能将列的
数据类型修改为一种兼容的数据类型。
例如，可以将 varchar2类型修改为 char，条件是没有缩短列的长度；但是不能将 date修改为
number。
修改列的默认值
alter table table_name modify column column_name;
默认值只适用于新插入表中的行。
删除列
alter table table_name drop column column_name;
重命名表
rename语句可以用于对表进行重命名。
rename table_name1 table_name2;
删除表数据，保留表结构
truncate table table_name
