欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle表的创建及相关参数

程序员文章站 2023-09-09 08:47:35
1、 创建表完整语法 create table [schema.]table (column datatype [, column datatype] … ) [table...
1、	创建表完整语法
create  table  [schema.]table
(column  datatype [, column  datatype] … )
[tablespace  tablespace]
[pctfree  integer]
[pctused  integer]
[initrans  integer]
[maxtrans  integer]
[storage  storage-clause]
[logging | nologging]
[cache | nocache] ];
 说明:
?	schema:表所在的方案名(所属用户名)
?	table:表名
?	column:字段名
?	datatype:字段的数据类型
?	tablespace:表所在的表空间名
控制数据空间使用的参数:
?	pctfree:为了行长度增长而在每个块中保留的空间的量(以占整个空间减去块头部后所剩余空间的百分比形式表示),当剩余空间不足pctfree时,不再向该块中增加新行。
?	pctused:在块剩余空间不足pctfree后,块已使用空间百分比必须小于pctused后,才能向该块中增加新行。
控制并发性参数:
?	initrans:在块中预先分配的事务项数,initrans对数据段的缺省值为1,对索引段的缺省值为2,以保证最低程度的并发。当事务访问表中的一个数据块时,该事务会在oracle块的头部中记录一个值,用于标记该事务正在使用这个oracle块。该事务结束时,会删除对应的条目。例如,如果initrans设为3,则保证至少3个事务可以同时对块进行更改。如果需要,也可以从块空闲空间内分配其它事务位置,以允许更多的事务并发修改块内的行。
?	maxtrans:限定可以分配给每个块的最大事务项数,缺省值为255。设置后,该值限制事务位置对空间的使用,从而保证块内有足够的空间供行或者索引数据使用。
?	storage:标识决定如何将区分配给表的存储子句
i.	initial:初始区的大小
ii.	next:下一个区的大小
iii.	pctincrease:以后每个区空间增长的百分比
iv.	minextents:段中初始区的数量
v.	maxextents:最大能扩展的区数
?	logging:指定表的创建将记录到重做日志文件中。它还指定所有针对该表的后续操作都将被记录下来。这是缺省设置。
?	nologging:指定表的创建将不被记录到重做日志文件中。
?	cache:指定即使在执行全表扫描时,为该表检索的块也将放置在缓冲区高速缓存的lru列表最近使用的一端。
?	nocache:指定在执行全表扫描时,为该表检索的块将放置在缓冲区高速缓存的lru列表最近未使用的一端。
?	案例1
?	通过设置表的nologging来产生更少的redo
oracle数据库会对产生改变的操作记录redo,比如ddl语句、dml语句,这些操作首先会放在redo buffer中,然后由lger进程根据触发条件写到联机日志文件,如果数据库开启归档的话,还要在日志切换的时候归档。在这样一个完整的链条上的每一个环节,都可能会成为性能的瓶颈,所以需要引起dba和数据库应用人员的注意。
下面案例中,当把一个表设置成nologging模式的时候,通过一定的插入操作,可以让oracle产生较少的redo。
sql> conn / as sysdba
sql> archive log list  --此时为归档模式
sql> create table tj as select * from dba_objects where 1=2;
sql> select count(*) from tj;
sql> select table_name,logging from user_tables where table_name='tj'; 
--观察logging属性值
sql> set autotrace on stat
sql> insert into tj select * from dba_objects;             --观察redo size的统计值
sql> rollback;
sql> insert /*+append*/ into  tj select * from dba_objects;  --观察redo size的统计值
sql> rollback;
sql> alter table tj nologging;
sql> select table_name,logging from user_tables where table_name='tj'; 
--观察logging属性值
sql> insert into tj select * from dba_objects;              --观察redo size的统计值
sql> rollback;
sql> insert /*+append*/ into tj select * from dba_objects;  --观察redo size的统计值

补充说明:设置autotrace的命令
用法: set autot[race] {off | on | trace[only]} [exp[lain]] [stat[istics]]
--关闭跟踪执行计划和统计信息功能(默认关闭)。
sql> set autotrace off;
--执行计划和统计信息都显示
sql> set autotrace on ;
--只显示执行计划和统计信息,不显示sql执行结果。
sql> set autotrace traceonly;
--只显示执行计划
sql> set autotrace on explain;
--只显示统计信息
sql> set autotrace on statistics;
补充说明:归档模式与非归档模式间的转换命令
--1)关闭数据库  
sql>shutdown immediate  
--2)把数据库启动到mount的模式 
sql>startup mount  
--3)把数据库改为非归档模式 /归档模式
sql>alter database noarchivelog;  
或者
sql>alter database archivelog; 
--4)打开数据库 
sql>alter database open; 
--5)查看数据库归档模式的状态
sql> archive log list
备注:如果在关闭归档日志时出现ora-38774错误,请关闭flash闪回数据库模式。
sql> alter database flashback off 
?	案例2
?	创建一张基本表
create tablespace exampletb
  datafile 'e:\ examp01.dbf' reuse;
create table scott.student
  (id  number(5) constraint st_id_pk primary key, 
   name varchar2(10) constraint st_name not null,
   phone varchar2(11),
   school_time date default sysdate,
sex char(1),
constraint st_sex_ck check (sex in('f','m')),
constraint st_ph_uk unique (name))
initrans 1 maxtrans 255
pctfree  20  pctused  50
storage( initial  1024k  next  1024k  pctincrease  0  minextents  1  maxextents  5)
tablespace  exampletb
2、	修改表结构
alter table 表名  add (列名  类型);  --添加新列
alter table 表名 modify  (列名  类型);  --修改列定义
alter table 表名  drop column 列名;  --删除列
rename  表名 to 新表名   --改表名(表名前不能加方案名)
alter table 表名 rename column 当前列名 to 新列名;  --修改列名
?	修改表结构案例
sql> alter table scott.student add (qq  number(10)); 
--为student表增加列存放qq号
sql> alter table scott.student modify (qq  number(12)); 
--修改student表中名为qq的列
sql> alter table scott.student rename column qq to qq_num; 
--将student表中名为qq的列改名qq_num
sql> alter table scott.student drop column qq_num;  
--删除student表中名为qq_num的列
sql> insert into scott.student(id,name) values(1, 'lucy');
--向student表中插入一条记录
sql> alter table scott.student modify (sex char(1) default 'm');
--修改sex列的定义
sql> insert into scott.student(id,name) values(2, 'dell');
--向student表中插入一条记录
sql> alter table scott.student modify (sex char(1) default null);
--修改sex列的定义
sql> insert into scott.student(id,name) values(3, 'mary');
--向student表中插入一条记录
思考:oracle中列的默认值设置与修改。
3、	表的约束
alter table 表名 add constraint 约束 ;         --增加一个约束
alter table 表名 drop constraint 约束名;       --删除一个约束
alter table表名enable [validate/novalidate] constraint约束名;      
--启用一个约束,validate/novalidate代表启用约束时是否对表中原有数据作检查。
alter table表名disable constraint约束名;      --禁用一个约束
?	修改表约束案例
sql> alter table scott.student disable constraint st_sex_ck;
--禁用st_sex_ck约束
sql> insert into scott.student(id,name,sex) values(4, 'lily', 'n');
sql> alter table scott.student enable novalidate constraint st_sex_ck;
--启用st_sex_ck约束,但不检查已有数据。
sql> select * from scott.student;
sql> insert into scott.student(id,name,sex) values(5, 'mark', 'n');
sql>@$oracle_home/rdbms/admin/utlexpt1.sql    --建立异常数据保存表
     或者
@ g:\app\administrator\product\11.2.0\dbhome_1\rdbms\admin\utlexpt1.sql
--具体路径可以通过搜索utlexpt1.sql获取
sql>alter table scott.student enable validate constraint st_sex_ck exceptions into exceptions;                                     --  将异常数据装入异常表
sql> select * from scott.student where rowid in(select row_id from exceptions); 
--查看对应的原表中的异常数据
sql>alter table scott.student drop constraint st_sex_ck; --删除约束st_sex_ck