oracel_table表

1、创建表（规划）

1.1设计类型

确定表的类型（堆表、临时表、索引表）、确定表每个字段的数据类型(CHAR/VARCHAR2/NUMBER/DATE)、确定表字段的完整性约束（PRIMARY KEY /NOT NULL）

1.2确定位置

确定表的存储位置（需要将表放到表空间中TABLESPACE中管理）

1.3使用NOLOGGING语句

避免产生过多的重做记录，节省重做日志文件的存储空间，加快表的创建速度

1.4预计和规划表

对索引、回退段和日志文件大小进行估计，从而预计所需的磁盘空间大小。

2、SQL语句创建表

2.1基本创建

语法：

-- Create table
CREATE TABLE [schema.] table_name (
column_name data_type [DEFAULT expression] [constraint]
[,column_name data_type [DEFAULT expression] [constraint] ]
[,column_name data_type [DEFAULT expression] [constraint] ]
)
TABLESPACE tablespace_name
pctfree 10
initrans 1
maxtrans 255
storage  ( 
initial 1088M 
next 1M 
minextents 1
)；
-- Add comments to the table 
comment on table TABLE_NAME
  is '某某表';
-- Add comments to the columns 
comment on column TABLE_NAME.COLUMN_NAME
  is '某某列';
comment on column TABLE_NAME.COLUMN_NAME1
  is '某某列';
-- Create/Recreate primary, unique and foreign key constraints 
alter table TABLE_NAME
  add constraint PK_TABLE_NAME primary key (xx1, xx2, xx3)
  using index 
  tablespace TABLESPACE_NAME
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
  
schema.：指定表所属的用户名，或者所属的用户模式名称。
table_name：所要创建的表的名称。
column_name：列的名称。列名在一个表中必须具有唯一性。
data_type：列的数据类型。
DEFAULT expression：列的默认值。
constraint：为列添加的约束，表示该列的值必须满足的规则。

create table mybook (
bookid number(10) not null,
bookname varchar2 (40),
bookprice number(4,2),
presstime date,
constraint book1_pk primary key(bookid)
);

查看创建表信息：

desc 表名称、select table_name,tablespace_name,temporary from user_tables where table_name=‘表名称’

查看创建表的列：

select * from user_tab_cols where table_name=‘表名称’

2.2创建时指定表空间

语法：TABLESPACE tablespace_name

查看默认表空间：select default_tablespace from user_users;

查看用户表对应的表空间：select table_name,tablespace_name from user_tables;

2.3指定存储参数

语法：

STORAGE (INITIAL n k | M NEXT n k | M PCTINCREASE N )

storage ( initial 1088M next 1M minextents 1 )

INITIAL：用来指定表中的数据分配的第一个盘区大小，以KB或者MB为单位，默认值是5个Oracle数据块的大小。

NEXT：用来指定表中的数据分配的第二个盘区大小。该参数只有在字典管理的表空间中起作用；在本地化管理表空间中，该盘区大小将由Oracle自动决定。

MINEXTENTS：用来指定允许为表中的数据所分配的最小盘区数量。同样，在本地化管理表空间的方式中，该参数不再起作用。

2.4设置数据块管理参数

PCTFREE和 PCTUSED这两个参数用于控制数据块中空闲空间的使用。

（手动管理需要设置、自动管理不需要设置，默认为10，）

PCTFREE 参数用于指定数据块中必须保留的最小空闲空间比例。

PCTUSED参数用于设置数据块是否可用的界限。

INITRANS和MAXTRANS用于控制能够并发访问数据块的事务数量。这两个参数的值，将会影响数据块头部的空间使用情况。

INITRANS参数用来设置数据块头部可以存放的事务数量；

MAXTRANS参数用来设置并发访问数据块所允许的事务总数。

2.5指定重做日志LOGGING

在创建表时，如果没有使用LOGGING或者NOLOGGING子句，则Oracle会默认使用LOGGING子句。使用重做日志，可以防止数据丢失，提高数据的可靠性。

2.6指定缓存

创建表时，默认使用NOCACHE子句。对于数据量小而且又经常查询的表，可以指定CACHE关键字，以便利用系统缓存提高对该表的查询效率。

3、修改表

修改列：
增加列：ALTER TABLE ... ADD...
ALTER TABLE mybook ADD presstime date;

删除列：
单一列：ALTER TABLE ...DROP COLUMM ...
多个列：ALTER TABLE ...DROP (xxx,xxx) ...
ALTER TABLE mybook DROP COLUMN PRESS;
alter table mybook drop (date,press);

标记不使用的列：
ALTER TABLE ... SET UNUSED...
alter table mybook set unused(presstime);
查看被标记的列：
select * from user_unused_col_tabs
select * from all_unused_col_tabs
select * from dba_unused_col_tabs

删除被标记的列：
alter table jcxlw_syslog drop unused column;

更新列：
修改列名：ALTER TABLE table_name RENAME COLUMN oldcolumn_name to newcolumn_name;
alter table mybook rename column czgw to cgzw；

修改数据类型、精度：ALTER TABLE table_name MODIFY column_name new_datatype;
alter table mybook modify cgzw VARCHAR2(20);

修改默认值：ALTER TABLE table_name MODIFY (column_name DEFAULT default_value);
alter table mybook modify (cgzw default 20);

重命名表：（谨慎）ALTER TABLE ... RENAME TO...

移动表所属表空间：alter table ... move...
1.查询：select table_name,tablespace_name from user_tables
2.移动：alter table table_name move tablespace new_tablespace_name
3.检查：select table_name,tablespace_name from user_tables where table_name='xx'

修改表存储参数：alter table TABLE_NAME pctfree 30 pctused 50;(修改后表中的所有数据块都将受到影响)

删除表定义:DROP TABLE table_name
参数：
1.CASCADE CONSTRAINTS
DROP TABLE操作不仅删除该表，而且删除所有引用这个表的视图、约束、索引和触发器等。
2.PURGE 
删除表定义后，立即释放该表所占用的资源空间。

完整性约束：保证数据的正确性和相容性
根据约束的作用域，可以将约束分为以下两类。
口表级约束：定义在一个表中，可以用于表中的多个列。
口列级约束：对表中的一列进行约束，只能够应用于一个列。

根据约束的用途，可以将约束分为
口PRIMARYKEY主键约束
口FOREIGNKEY外键约束
口UNIQUE唯一性约束
口NOT NULL非空约束
口CHECK检查约束

非空约束：NOT NULL 
口NOT NULL约束只能在列级别上定义。
口在一个表中可以定义多个NOT NULL约束。
口为列定义NOT NULL约束后，该列中不能包含有NULL值。
1.创建表时：（sutdent number（10） not null，）；
2.创建修改：（alter table student modify studentname not null；）；

主键约束：PRIMARY KEY
口在一个表中，只能定义一个PRIMARY KEY约束。
口定义为PRIMARY KEY的列或者列组合中，不能包含任何重复值和NULL值。
口Oracle数据库会自动为具有PRIMARY KEY约束的列建立一个唯一索引，以及一个NOT NULL约束。
在定义PRIMARY KEY约束时，可以在列级别和表级别上分别进行定义。
口如果主键约束是由一个列组成，那么该主键约束被称为列级别上的约束。
口如果主键约束定义在两个或者两个以上的列上，则该主键约束被称为表级别约束。
1.创建表时：（constraint yourbook1_pk primary key(bookid,bookname);）（sutdent number（10） not null primary key，）;
2.添加主键约束：（alter table bookes add constraint yourbook1_pk primary key(bookid);）;
3.删除主键：（alter table bookes drop constraint yourbook1_pk;）

UNIQUE唯一约束
口如果为列定义UNIQUE约束，那么该列中不能包含重复的值。
口在一个表中，可以为某一列定义UNIQUE约束，也可以为多个列定义UNIQUE约束。
口Oracle将会自动为UNIQUE约束的列建立一个唯一索引。
口可以在同一个列上建立NOTNULL约束和UNIQUE约束。
1.创建表时：（studentcode varchar2(10) constraint stu2_uk unique,）；
2.创建后添加：（alter table student2 add unique (studentname);）；
3.删除：（alter table student2 drop constraint stu2 uk;）

CHECK约束检查
CHECK约束是指检查性约束，使用CHECK约束时，将对输入的每一个数据进行检查，只有符合条件的记录才会保存到表中，从而保证数据的有效性和完整性。
CHECK约束具有以下4个特点。
口在CHECK约束的表达式中，必须引用表中的一个或者多个列；并且表达式的运算结果是一个布尔值。
口在一个列中，可以定义多个CHECK约束。
口对于同一列，可以同时定义CHECK约束和NOTNULL约束。口CHECK约束既可以定义在列级别中，也可以定义在表级别中。
1.创建表时：（studentage number(3)constraint stu3 ck check(studentage >0),）；
2.创建表后：（alter table student2 add constraint stu2_age_ck check(studentage>0);）
3.删除：（alter table student2 drop constraint stu2_age_ck;）

FOREIGN KEYY外键约束
在使用FOREIGN KEY约束时，被引用的列应该具有主键约束，或者具有唯一性约束。
口如果为某列定义FOREIGN KEY约束，则该列的取值只能为相关表中引用列的值或者NULL值。
口可以为一个字段定义FOREIGN KEY约束，也可以为多个字段的组合定义FOREIGN KEY约束。因此，FOREIGN KEY约束既可以在列级别定义，也可以在表级别定义。
口定义了FOREIGN KEY约束的外键列，与被引用的主键列可以存在于同一个表中，这种情况称为“自引用”。
口对于同一个字段可以同时定义FOREIGN KEY约束和NOT NULL约束。
1.创建表时关联：（classid number(4) references stuclass(classid)）；外键列和被引用列的列名可以不同，但是数据类型必须完全相同。
2.创建表后：（alter table student4 add constraint stu4_fk foreign key (classid) references stuclass(classid);）
3.删除：（alter table student4 drop constraint stu4_fk;）

引用类型
在定义外键约束时，还可以使用关键字ON指定引用行为的类型。当删除父表中的一条记录时，通过引用行为可以确定如何处理外键表中的外键列。引用类型可以分为如下3种。
口使用CASCADE关键字
如果在定义外键约束时使用CASCADE关键字，那么当父表中被引用列的数据被删除时，子表中对应的数据也将被删除。
口使用SET NULL关键字
如果在定义外键约束时使用SET NULL关键字，那么当父表中被引用列的数据被删除时，子表中对应的数据被设置为NULL。要使这个关键字起作用，子表中的对应列必须支持NULL值。
口使用NO ACTION关键字
如果在定义外键约束时使用NOACTION关键字，那么当父表中被引用列的数据被删除时，将违反外键约束，该操作也将被禁止执行，这也是外键约束的默认引用类型。

(alter table student4 add constraint stu4_fk foreign key(classid) references stuclass on delete cascade;)

禁止和激活约束（表和约束之间的关系）关键字DISABLE/ENABLE：对表进行插入和更新操作时是否验证操作符合约束规则
口禁止状态（DISABLE）当约束处于禁止状态时，即使对表的操作与约束规则相冲突，操作也会被执行。（外部数据导入）
口激活状态（ENABLE）当约束处于激活状态时，如果对表的操作与约束规则相冲突，则操作会被取消。（正常使用时）

验证约束：是否对表中已经存在的数据进行约束规则检查
口验证约束：如果约束处于验证状态，则在定义或者激活约束时，Oracle将对表中所有已有记录进行验证，检查是否满足约束限制。
口非验证约束：如果约束处于非验证状态，则在定义或者激活约束时，Oracle将对表中已有记录不执行验证操作。

延迟约束：对添加和更新操作的数据不会立即执行约束检查，可以改变检查的时机
使用关键字DEFERRABLE可以创建延迟约束，延迟约束有以下两种初始状态。
口使用INIFIALLY DEFERRABLE关键字，表示约束的初始状态是延迟检查。
口使用INITIALLYIMMEDIATE关键字，表示约束的初始状态是立即检查。

4、查询约束信息

4.1.查询表中的约束信息
通过查询数据字典视图USER_CONSTRAINTS，可以了解当前用户模式中所有约束的基本信息。

4.2.查询定义约束的列
通过查询数据字典视图USER_CONS_COLUMNS，可以了解定义约束的列

5、分析表

口验证表的存储情况
可以使用ANALYZE VALIDATE STRUCTURE语句，验证表的存储结构，对存储结构的完整性进行分析，如果发现表中存在损坏的数据块，则需要用户重新创建该表。
在使用ANALYZE VALIDATE STRUCTURE 语句验证表的存储结构时，Oracle会将表中含有损坏数据块的记录的物理地址（即ROWID），添加到一个名为INVALID_ROWS的表中。
INVALID_ROWS表可以通过Oracle提供的脚本文件UTLVALID.SQL来创建。

口查看表的统计信息。
使用ANALYZE语句，可以收集关于表的物理存储结构和特性的统计信息，这些统计信息被存储到数据字典中，可以通过查询数据字典USER_TABLES、ALL TABLE和DBA_TABLE，查看表的统计结果。
在使用ANALYZE语句进行统计信息时，可以指定如下两个子句。
口 COMPUTE STATISICS在分析过程中对表进行全部扫描，获取表的精确统计信息。
口 ESTIMATE STATISTICS在分析过程中对表进行部分扫描，并获取扫描信息，以部分扫描获取的数据来代表整个表的统计信息。

口查找表中的链接记录和迁移记录
在Oracle数据库中，表中数据的基本组织单位是记录，这些记录都被存储在数据块中。如果一个数据块的大小足够容纳一条记录，那么Oracle将这条完整的记录存储到一个数据块中。
但是，如果一个数据块无法容纳一条完整的记录，那么Oracle会将这条记录分割成多个片段，并将这些片段存储在多个数据块中，这种被存储在多个数据块中的记录被称为“链接记录”。
如果一条记录原来存储在一个数据块中，但是由于进行更新操作，记录信息被扩展，从而导致数据块的存储空间不足。这时Oracle会将这条记录移动到另一个数据块中，这种情况下的记录被称为“迁移记录”。产生迁移记录的原因大多是由于记录中存在LONG或者LOB类型的数据。
如果需要查找链接记录和迁移记录，可以通过在ANALYZE语句中使用LIST CHAINED ROWS子句实现。表中的链接记录和迁移记录的ROWID都被保存到CHAINED_ROWS表中。可以通过Oracle提供的脚本文件UTLCHAIN.SQL创建 CHAINED_ROWS表。
如果表中存在链接记录和迁移记录，则可以通过某种方式将这些记录修复，使表中不再存在链接记录和迁移记录。例如，可以将所有链接记录和迁移记录保存到一个临时表中，然后将链接记录和迁移记录从原表中删除，最后再将临时表中的记录全部添加到原表中，并删除临时表，清空CHAINED_ROWS表中的内容。

产生迁移记录的原因大多是由于记录中存在LONG或者LOB类型的数据。
如果需要查找链接记录和迁移记录，可以通过在ANALYZE语句中使用LIST CHAINED ROWS子句实现。表中的链接记录和迁移记录的ROWID都被保存到CHAINED_ROWS表中。可以通过Oracle提供的脚本文件UTLCHAIN.SQL创建 CHAINED_ROWS表。
如果表中存在链接记录和迁移记录，则可以通过某种方式将这些记录修复，使表中不再存在链接记录和迁移记录。例如，可以将所有链接记录和迁移记录保存到一个临时表中，然后将链接记录和迁移记录从原表中删除，最后再将临时表中的记录全部添加到原表中，并删除临时表，清空CHAINED_ROWS表中的内容。