oracle 表(下)
需要说明一点的是,此处讲表,更多的是一些概念与管理,至于创建的参数细节更多与业务有关。
创建表
创建一个表,create table 命令说简单将非常简单,说复杂巨复杂,在实际的生产中,并不像我们前面创建一个表那简单的指定两个个字段就OK了。而且我们前面介绍的那么多种表的类型也是这一条命令稿定的。
创建自动管理与手动管理两个表空间:
创建个一个自动管理的表空间: SQL create tablespace assm datafile /ora10/product/oradata/ora10/assm_1.dbf size 100M extent management local uniform size 128k segment space management auto ; 创建一个手动管理的表空间: SQL create tablespace mssm datafile /ora10/product/oradata/ora10/mssm_1.dbf size 100M extent management local uniform size 128k segment space management manual ; 查看创建的表空间: SQL !ls -l /ora10/product/oradata/ora10 总计 1189996 -rw-r----- 1 ora10 dba 104865792 09-24 21:54 assm_1.dbf -rw-r----- 1 ora10 dba 104865792 09-24 22:01 mssm_1.dbf .............................
创建两个用户:
用户名密码都为:as1 SQL create user as1 identified by as1 default tablespace assm; User created. 用户名密码都为:ms1 SQL create user ms1 identified by ms1 default tablespace mssm; User created. 为两个用户赋予权限: SQL grant connect,resource to as1; Grant succeeded. SQL grant connect,resource to ms1; Grant succeeded.
用创建的用户登录:
登录: SQL conn as1/as1 Connected. 查看当前用户: SQL show user USER is "AS1" create table tt(id int,name char(10)) storage(initial 128k next 128k pctincrease 0 minextents 1 maxextents 5) tablespace assm;
创建一个表:
创建一个表,加一些参数限定: SQL create table tt(id int,name char(10)) storage(initial 128k next 128k pctincrease 0 minextents 1 maxextents 5) tablespace assm; SQL select segment_name,segment_type from user_segments; SEGMENT_NAME SEGMENT_TYPE -------------------------------------------------------------------------------- TT TABLE
添加这些参数的指定值会影响数据库的性能。
Initial 当前的extents 大小为128KB
Next 随着表数据的增加会申请新extents ,申请的下一个extents 的大小。
Pctincrease 增长的百分比,这个参数一般默认情况下为0 。
Minextents 表示这个表最少只能有1个extents
Maxextents 表示最多可以有5个extents
tablespace assm 指定当前表所属的表空间为 assm ,其实默认不指定也是assm表空间,因为当前的用户权限范围已经指定为assm表空间。
其实,对于assm(自动管理)表空间来说,修改上面的参数是无效的,。但对于mssm(手动管理)的表空间就会起作用,在非常了解数据库的情况下,调整参数会提高数据库的性能。
创建表的原则
* 把表放在不同的表空间里
* 减少磁盘空间碎片(定期整理碎片,不同对象整理方式也不同)
* 尽量少使用几种标准的extents 盘区,用于减少磁盘空间。
* 使用临时表
临时表
什么是临时表,用户做一个操作查询出几百几千条数据,我们可以把数据放在内存中。当有很多用户都这样做,内存空间不足,这个时候就需要把数据保存在磁盘上。对于oracle就提供了一种临时表用于存放这些数据。
也可以把上面两句合并 SQL create global temporary table hr.employees_temp as select * from hr.employees_temp ;
一个session 可以包含多个事务。那么临时表的生命周期只作用于一个事务或一个session (会话)。 事务临时表与会话临时表级别不同,也会有一些差异。
对于临时表不用加DML锁,相对速度会比普通表快。
临时表你可以创建索引、视图和触发器。
这里我们需要理解:当我们创建一个普通的表时,系统会给我们分配存储空间,我们创建临时表的时候不会分配空间,更像是创建的一种规则,当用户使用时会生成临时表,他们放不同的临时表空间中 ,所以每个用户的临时表只有自己可看到。
临时表也会产生undo 和redo信息,但它产生的redo信息要远远小于普通表。
下面演示创建一个事务级别与会话级别的两个临时表,(这个演示有点啰嗦,但完全可以根据下面的步骤,操作验证。)
SQL conn as1/as1 登录用户 Connected. SQL desc tt; 查看tt表结构 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(38) NAME CHAR(10) 创建sessione级别的临时表 SQL create global temporary table tmp_session on commit preserve rows as select * from tt where 1=0; Table created. on commit preserve rows 表示创建的表是session 级别,只要用户不退出,临时表一直有效。 select * from tt where 1=0; 将tt表的结构拷贝过来使用
创建事务级别的临时表 SQL create global temporary table tmp_transaction on commit delete rows as select * from tt where 1=0; Table created. on commit delete rows 表示创建的表是事务级别,这个临时表的作用范围只在一个事务内有效。
验证两种临时表的作用范围:
SQL select * from tt; no rows selected //查看tt表为空 向表中插入两条数据: SQL insert into tt values(0,chongshi); row created. SQL insert into tt values(1,chongshi); row created. SQL commit; //提交事务 Commit complete. SQL select * from tt; //查看表中插入的数据 ID NAME ---------- -------------------- chongshi chongshi ---------------------------------------------------------------------------------------------------------------------------------------- //把tt表信息放到session级别临时表中 SQL insert into tmp_session select * from tt; rows created. //把tt表信息放到事务级别临时表中 SQL insert into tmp_transaction select * from tt; rows created. //查看两张表信息: SQL select count(*) from tmp_session; COUNT(*) ---------- SQL select count(*) from tmp_transaction; COUNT(*) ---------- //我们可以看到session临时表与事务临时表都是有数据的。
SQL quit 退出 Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options [ora10@localhost /]$ sqlplus as1/as1 重新登录 SQL*Plus: Release 10.2.0.1.0 - Production on Sat Oct 6 12:20:08 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL select count(*) from tmp_session; COUNT(*) ---------- 再来查看session 级别的临时表也变为空了。
行迁移与链接
Row Migration
在前面的学习中有介绍到数据块的概念,块中存放着一条一条的数据,每个数据块都会预留的有剩余的空间用一条数据的扩展。
当剩余的空间不够一条数据的扩展时,那么这条数据就需要迁移,迁移到可存放这条数据的数据块中。那么怎样找到新数据的位置呢,就需要在原来存放数据的位置放一个指针,用于指向新的数据。迁移数据带来的后果就是oracle的性能下降。
Row Chaining
如果一条记录的某列内容非常长了,任何一个块都放不下。Oracle会把他们分成不同的部分,每一部分被称为行片(row pieces)。每一个块中都会有指针帮助oracle组装成一条完整的记录。造成row chaining的根本原因是因为数据块设置的太小了。Row chaining同样会造成oracle的性能下降。
如果减少row chaining一种方法是可以甚至增加块的大小,加一个种方法是将表拆分成多个小表。
表操作
SQL alter table hr.employees Ppctfree 30 pctused 50 Storage(next 500k minextents 2 maxextents 100);
需要注意的是此设置不会对已经存在的数据造成影响,只会规定到新创建的数据。
手动的为一个表分配盘区
SQL alter table hr.employees Allocate extent(size 500k datafile /disk3/data01.dbf);
extent(size 500k datafile /disk3/data01.dbf) 用于指这在哪个数据文件中指定盘区以及大小。
表的改编
一张表可能由于我们初始设置不当,或者由于后期业务发生改变,表的机构需要调整与重新组织。
重新组织命令:
SQL alter table hr.employees move tablespace data1;
演示:此命令只对普通的表有用,或分区表中的一个表有用。
SQL show user; USER is "AS1" //查看当前用户下的表 SQL select table_name from user_tables; TABLE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------------- TT ASSM TMP_SESSION TMP_TRANSACTION
TABLE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------------- TMP_SESSION TMP_TRANSACTION TT MSSM
那么对表的改编有很多方法,一种就是导出、导入。一种创建一个新表,将数据一列一列的移动到新表中。
清空表
Truncating a table 相当把表中的内容全部清空,但表及其结构依然存在,像刚创建的一个新表。
SQL truncate table hr.employees;
清空表不会产生undo信息,也就是执行些操作无法进行回滚,只能通过其它办法恢复数据。
表的索引也会被一并清掉。
如果一个表被外界引用,那么它就不能被truncate。
删除表
Dropping a table
SQL drop table hr.employees cascade constraints;
如果这个表被删除掉,那么它占用的空间也会被释放。
如果要删除的表与其它表有关联,则需要加上cascade constraints 命令
删除一列数据
Dropping a column 的操作使用不是很频繁,但也需要知道如何操作。
SQL alter table hr.employees drop column comments cascade constraints checkpoint 1000;
删除列操作会产undo信息,如果数据量过大可能会把undo表空间撑满,checkpoint 1000 会每执行1000条数据发起一个检查点,发起检查点就是会把操作的数据写到磁盘的数据文件中。
Oracle Ascii& Asciistr()函数使用介绍以及常用字符ASCII码对应表 Asciistr ASCII chr(9) tab空格 chr(10) 换行 chr(13) 回车 Chr(13) amp;chr(10) 回车换行 chr(32) 空格符 chr(34) 双引号 chr(39) 单引号 chr(33) ! chr(34) chr(35) # chr(36) $ ...
相关文章
- ORA-31661: there are no metadata transform values of type VARCHAR2 ORACLE 报错 故障修复 远程处理
- ORA-16131: An error occurred during a Terminal Recovery of the standby. ORACLE 报错 故障修复 远程处理
- 在Oracle数据库中同时更新两张表的简单方法
- Oracle 视图 DBA_ADVISOR_ACTIONS 官方解释,作用,如何使用详细说明
- 使用Oracle子查询优化查询结果(子查询oracle)
- 表数据Oracle表数据截断:清理整理数据库(oracle截断)
- Oracle如何增加表空间(oracle增加表空间)
- 灵活运用Oracle触发器类型达到更高效率(oracle触发器类型)
- 备份Oracle增量数据备份:保障数据安全(oracle增量数据)
- Oracle数据库格式化无忧(oracle格式化)
- 如何提高Oracle数据库的性能和安全性?(Oracle数据库)
- Unlocking the Power of Your Data with Oracle Dump: A Comprehensive Guide(oracledump)
- 利用Oracle数据泵快速导入数据(oracle导入数据泵)
- 实现Oracle数据库中存储过程的调用(调用oracle存储过程)
- Oracle事务实现原子操作的4大要素(oracle事务包含什么)
- Oracle数据库中实现大小写转换的方法(oracle中大小写转换)
- 新世界Oracle 11c开启崭新的世界大门(oracle11c打开)