CTAS高效重建表进行碎片整理，字段新增的操作文档

举例：

select /*+ parallel(8) */ min(rptdate),max(rptdate),count(1) 

from TRPTCLSPARAM

where rptdate <= '2015-11-30 23:59:59' 

重建表：

create table TRPTCLSPARAM_NEW parallel 8 nologging as  select *

from TRPTCLSPARAM

where rptdate >= '2015-11-30 23:59:59'  

修改并行度：

alter table TRPTCLSPARAM_NEW PARALLEL 1;

查询默认列：

select * from user_tab_cols   where  table_name='TRPTCLSPARAM'

生成列值的表：

create table wdf_tab_col  parallel 2 nologging as

select TABLE_NAME,COLUMN_NAME,to_lob(data_default) def from user_tab_cols

select * from wdf_tab_col

生成修改默认值的语句：

select 'alter table '||table_name||'_NEW modify '||column_name||' default '||to_char(def)||';' from wdf_tab_col where table_name='TRPTCLSPARAM' and to_char(def) is not null

修改默认值：

alter table TRPTCLSPARAM_NEW modify PARAMID default 0 ;

alter table TRPTCLSPARAM_NEW modify CLSID default 0 ;

alter table TRPTCLSPARAM_NEW modify CUSTCOUT default 0 ;

alter table TRPTCLSPARAM_NEW modify CUSTPRICE default 0 ;

alter table TRPTCLSPARAM_NEW modify PLUCOUT default 0 ;

alter table TRPTCLSPARAM_NEW modify JYCOUNT default 0 ;

alter table TRPTCLSPARAM_NEW modify TOTAL default 0 ;  

生成索引重建、触发器重建语句：

SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME) FROM user_indexes U where U.table_name='TRPTCLSPARAM'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('TRIGGER',U.trigger_NAME) FROM user_triggers U where U.table_name='TRPTCLSPARAM'  

删除原表的约束、触发器、索引：

alter table TRPTCLSPARAM_OLD  drop constraint PK_TRPTCLSPARAM      删除约束（主键约束删除后，还需要删除唯一索引）

drop trigger TRG_TRPTCLSPARAM_IDT_BI    删除触发器

drop index PK_TRPTCLSPARAM    删除触发器

将原表改名或删除掉：

ALTER table TRPTCLSPARAM rename to TRPTCLSPARAM_OLD

或

truncate table  TRPTCLSPARAM;   

drop table  TRPTCLSPARAM ;

 

修改新表为原表名：

ALTER table TRPTCLSPARAM_NEW rename to TRPTCLSPARAM

重新执行触发器、约束、索引创建语句：

alter table TRPTCLSPARAM

  add constraint PK_TRPTCLSPARAM primary key (PARAMID)

  using index 

  tablespace HSCMP_TBL

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 49M

    next 1M

    minextents 1

    maxextents unlimited

  );

  

  CREATE OR REPLACE TRIGGER "HSCMP"."TRG_TRPTCLSPARAM_IDT_BI" before insert

on tRptClsParam for each row

declare

    integrity_error  exception;

    errno            integer;

    errmsg           char(200);

begin

    --  Column "ParamID" uses sequence qTimeStamp

    select qId_tRptClsParam_ParamID.NEXTVAL INTO :new.ParamID from dual;

    exception

      when integrity_error then

        raise_application_error(errno, errmsg);

end;

ALTER TRIGGER "HSCMP"."TRG_TRPTCLSPARAM_IDT_BI" ENABLE;  

CREATE   INDEX  "HSCMP"."IDX_TRPTCLSPARAM"  ON  "HSCMP"."TRPTCLSPARAM" ("ORGCODE", "RPTDATE", "CLSCODE") 

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 

  STORAGE(INITIAL 187695104 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "HSCMP_IDX" 

 

如果开了并行，修改表或索引的并行度为1，设置logging

  

alter table TRPTCLSPARAM parallel 1;

 alter table TRPTCLSPARAM logging;  

然后修复无效对象。

OK，收工。

建表语句：

 create table t(id int, name varchar2(10));

 create table hr.t(id int, name varchar2(10));

建表时设置默认值：

 create table t(id int,
                    name varchar2(10) default 'Smith'); 

从其他表拷贝：

create table t as select * from emp; 

建表指定表空间：

 create table t(id int) tablespace users; 

修改列：

 alter table t modify id double;   修改字段数据类型

 alter table t modify name varchar2(10);    修改字段值

 -- 加字段
alter table t add(b number(5));

删除字段（删除列时会将整张表锁定）

 alter table t drop column c;  

修改字段名：

ALTER TABLE test RENAME COLUMN id TO idd;

  --改表名

ALTER TABLE test RENAME TO test2; 

-- 将表改成只读/读写

ALTER TABLE employees READ ONLY;

ALTER TABLE employees READ WRITE;

创建表的时候能直接指定是否只读 

当我们需要删除某一个字段时，会锁表，这时可能会造成大量并发

这时可以将这列标记为没有使用，也就是没有这列了，这时再删除列，这样就不会锁表了。

 ALTER TABLE t SET UNUSED COLUMN b;

这个东西不能乱执行，只能删除列时才用。标记为没有使用后，不能恢复正正常状态的

DESC查看简单的表信息

查看复杂的表信息：

 DBMS_METADATA.GET_DDL  获取元信息

如：

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')

-----------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"

   (    "ID" NUMBER(*,0)

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS" 

查表或列的注释:

 select * from user_tab_comments where table_name='T' AND comments is not null;

 select * from user_col_comments where table_name='T' AND column_name='ENAME' AND  comments is not null;

 

添加表或列的注释：

comment on table 表 is '表注释';

comment on column 表.列 is '列注释';

约束：

 约束分类:

1. NOT NULL
2.UNIQUE
3.PRIMARY KEY
4.FOREIGN KEY
5. CHECK 

非空约束：实际就是通过CHECK约束来实现的

 建表的时候加约束

 create table t_null(

id number(10) not null,

name varchar2(10)) 

如何建表时加约束，并给约束取名？

 create table t_null(
     id number(10)  constraint t_nk not null,
name varchar2(10)); 

先建表，再加约束 

 alter table t_null modify id not null;

加约束并给约束命名

 alter table t_null modify id constraint t_nk not null; 

不给约束命名系统就会自己命名成这种：SYS_C-----

 SCOTT> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE 
  2  from USER_CONSTRAINTS where TABLE_NAME='T_NULL';

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
T_NULL                         SYS_C0010889                   C

约束类型：

 CONSTRAINT_TYPE
    C (check constraint on a table)
    P (primary key)
    U (unique key)
    R (referential integrity)
    V (with check option, on a view)

    O (with read only, on a view) 

约束还分

表级约束

行级约束

唯一约束（就是行级约束）

创建方法：

 create table t_unique(
  id number(10) constraint t_uk unique,
name varchar2(10));

注意：唯一约束可以为空，而且可以插入无法个空值，因为空值并不等于空值

 -- 表级约束

create table t_unique(
  id number(10),
name varchar2(10),
unique(id)
);

也相当于是：

 -- 表级组合约束（组合唯一索引）
create table t_unique(
  id number(10),
name varchar2(10),
unique(id,name)
);

 

表级约束一般表示几个字段组合起来的值才是唯一的。表级约束管几行。