表的有关操作

一、oracle中的表分为：对象表和关系表

  关系表分为：堆组织表、临时表、索引组织表、分区表、集群表。

二、创建表之前估算表的大小

  1、使用OEM

  2、使用DBMS_SPACE

    例如:

      declare

      l_used_bytes number;

      l_allocate_bytes number;

      begin

      dbms_space.create_table_cost(

      tablespace_name =>'USERS',

      avg_row_size =>100000,

      row_count =>100000,

      pct_free =>20,

      used_bytes =>l_used_bytes,

      alloc_bytes =>l_allocate_bytes);

      dbms_output.put_line('使用空间为:'||l_used_bytes||','||'需要分配的空间为：'||l_allocate_bytes);

      end;

    3、释放段的空间

    如：alter table table_name deallocate unused;

三、创建表

    需要create table权限，为其他模式创建表，需要create any table权限。

    一般语法：create table table_name(column_name data_type [,column_name data_type.....])

                   tablespace tablespace_name

                  pctfree n

                  --使用本地管理的表空间，不需要指定存储参数

                 --在创建表时可以指定约束，可以在表级，也可以在列级，在多个列上创建约束则必须在表级；在创建约束时可以指定约束名，也可以不指定，由系统自动生成。

四、更改表

  1、添加列

    alter table table_name add (column_name data_type [,column_name data_type .....])

    如果只增加一列也可以这样写：alter table table_name column_nmae data_type;

    --可以在增加列的同时创建表级或者列级约束，就如同创建表时。

  2、删除列

    alter table table_name drop column column_name;  --该方法只能删除一列

    alter table table_name drop (column_name1 [,column_nmae2 .....]);  --该方法可以删除多列或者一列。

    如果该列被其他列引用，则在删除时需加cascade constraints选项。

    如果列含有较大数据，可以在先把列置为无效，然后在数据库使用低峰时候再删除，如：

    alter table table_name set unused column column_name;  --该方法只能针对一列。

    alter table table_name set unused (column_name1 [,column_nmae2 ....]);  --该方法可以针对多列或者一列。

    alter table table_name drop unused columns [checkpoint n]

    如果在删除时由于故障中断，可以在数据库恢复正常时继续删除：

    alter table table_name drop columns continue [checkpoint n];

  3、修改列

    修改数据类型：alter table table_name modify column_name new_data_type;

    增加非空约束：alter table table_name modify column_name not null;

    删除非空约束：alter table table_name modify column_name null;

  4、重命名列

    alter table table_name rename column column_name to column_new_name;

五、重命名表

  alter table table_name rename to new_table_name; 或者rename table_name to new_table_name;

  --表被重命名之后，有关表的索引、约束、权限将被过继到新表中，但依赖该表的子程序被置为无效。

六、删除

  1、删除表中数据

    delete from table_name [where 条件];  --delete时DML语句，oracle会写撤销数据，删除速度慢，但能通过rollback或者在commit之后使用flashback table恢复数据。

    truncate table table_name [{drop | reuse} storage]; --truncate时DDL语句，oracle不会写撤销数据，删除速度快，但不能恢复。

  2、删除表

  drop table table_name [purge] [cascade constraints]; 

  --如果不加purge，则表在删除之后被重命名进入回收站，可以使用flashback drop恢复

  --查询用户回收站中的数据：show purge；

  --清理回收站中的某个表：purge tabletable_name；

  --清理用户回收站中的所有数据：purge recyclebin；

  --闪回被删除的表：flashback table table_name to before drop；

七、使用CTAS创建表

  1、一般用法：create table table_name1 as select column_name1 [,column_name2 ...] from table_name2 [where 条件];  --新表和源表的列名和数据类型完全相同。

  2、新表使用和源表不同的列名：create table table_name1(column_name11 [,column_name12 ....]) --l列名之后不能指定数据类型，且列于select子句中的列一一对应。

                                                 as select column_name21 [,column_name22 ....] from table_name2

                                                  [where 条件]

                  

                                                  create table table_name1

                                                  as select column_name1 别名1 [,column_name2 别名2 ......] from table_name2   --使用别名作为新表的列名。

                                                  [where 条件]

  3、指定新表存储的表空间：create table table_name1 tablespace tablespace_name as select column_name1 [,column_name2 ...] from table_name2 [where 条件];

  4、使用CTAS创建表，符合条件的数据同时被写入新表中。如果数据量太大，可以使用parallel或者nologging加快创建的速度：

    crea te table table_name1

    parallel  n  --n为并行度的数值。

    nologging

    as

    select column_name1 [,column_name2 ...] from table_name2 [where 条件];

八、转移表到其他表空间

  alter table table_name move tablespace tablespace_name;

  --使用该方法可以整理表段中的碎片，但转移之后行数据的rowid发生变化，索引无效，需要重建索引。