我的oracle笔记第二部分
************************************************************************************************
2005-06-09 14：45
本地表空间管理的优点：
1、本地管理避免了循环空间管理操作，而这种操作在数据字典管理表空间中可能发生（当释放或消耗某个区的空间，导致另一个消耗或释放回退段或数据字典表内空间的操作时间）
2、因为本地管理的表空间不记录数据字典表中的空闲时间，从而减少了对这些表的争用。
3、区的本地管理自动跟踪临近的空闲时间，因而无须合并空闲区
4、本地管理的区大小可以由熊自动决定。
5、转换为区的位图不会生成回退信息，因为它们不更新数据字典中的表。
************************************************************************************************
2005-06-09 14：54
创建数据字典管理表空间
SQL> create tablespace userdata1
2 datafile 'f:\oracle\oradata\ora9i\userdata1.dbf' size 50m
3 extent management dictionary
4 default storage(initial 1M NEXT 1M);
create tablespace userdata1
*
ERROR 位于第 1 行:
ORA-12913: 无法创建字典管理的表空间
%%注意：在9.2版本中只能创建本地管理表空间。
************************************************************************************************
2005-06-09 14：58
改变表空间的存储参数
alter tablespace userdata
minimum extent 1m;
只能用于数据字典管理的表空间
ALTER TABLESPACE userdata
DEFAULT STORAGE (
INITIAL 2M
NEXT 2M
MAXEXTENTS 999 );
************************************************************************************************
2005-06-09 15：06
回滚段表空间：
1、用于存储回滚段
2、不能够存储其它对象
3、扩展是本地管理
4、只能用datafile 和extent mangement ，在create tablespace中
SQL> create undo tablespace undo
2 datafile 'f:\oracle\oradata\ora9i\undo01.dbf' size 10m;
表空间已创建。
************************************************************************************************
2005-06-09 15：12
临时表空间
1、用于排序
2、不能存储永久的对象
3、建议本地管理段
SQL> create temporary tablespace temp1
2 tempfile 'f:\oracle\oradata\ora9i\temp1.dbf' size 10m
3 extent management local uniform size 250k;
表空间已创建。
创建的临时表空间，默认并不起作用，而是使用系统默认的表空间，需要指定用户使用表空间。
alter user us temporary tablespace temp1；
alter database default temporary tablespace temp1；
在9i中每个用户只能使用一个临时表空间，在10G中可以使用多个。
************************************************************************************************
2005-06-09 15：22
1、临时文件总是设置为nologging模式
2、不能设置一个临时文件为只读
3、不能创建一个临时文件通过alter database 命令
4、tempfiles are required for read-only databases
5、介质恢复不需要恢复临时文件
6、backup controlfile bushengcheng 任何tempfile 信息
7、create controlfile不指定任何关于tempfiles的信息
若要优化临时表空间的排序性能可以将UNIFORM SIZE 参数设置为SORT-AREA-SIZE 参数的整数倍。
************************************************************************************************
2005-06-09 15：30
缺省临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
************************************************************************************************
2005-06-09 15：33
缺省临时表空间的限制：
1、当一个新的缺省表空间有效前，不能被删除。
2、不能够 offline
3、你不能够改变一个缺省临时表空间为永久表空间。
************************************************************************************************
2005-06-09 15：37
表空间的offline 状态
1、offline tablespace对数据访问来说是无效的
2、一些表空间必须online ：system ，tablespaces with active undo segments，缺省临时表空间
3、通过alter tablespace userdata offline 命令使表空间离线
5、alter tablespace userdata online；
************************************************************************************************
2005-06-09 15：42
ALTER TABLESPACE userdata READ ONLY;
************************************************************************************************
2005-06-09 15：43
删除表空间
DROP TABLESPACE userdata
INCLUDING CONTENTS AND DATAFILES;
参数including contents and datafiles
contents：从数据字典删除相关信息
datafiles，数据文件将直接删除。
CASCADE CONSTRAINTS：从表空间外的表中丢弃引用完整性约束，如果该表引用了已丢弃表空间内的表的主键和唯一键
删除表空间userdata：
SQL> drop tablespace userdata including contents and datafiles;
表空间已丢弃。
************************************************************************************************
2005-06-09 15：54
调整表空间大小
1、打开表空间自动增长
ALTER DATABASE DATAFILE
'/u01/oradata/userdata02.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
2、调整数据文件的size
ALTER DATABASE
DATAFILE '/u03/oradata/userdata02.dbf'
RESIZE 200M;
3、增加数据文件
ALTER TABLESPACE app_data
ADD DATAFILE '/u01/oradata/userdata03.dbf'
SIZE 200M;
************************************************************************************************
2005-06-09 15：57
迁移数据文件
基于表空间方式：
1、表空间离线
2、复制这个表空间下的数据文件
3、更新控制文件
ALTER TABLESPACE userdata
RENAME
DATAFILE '/u01/oradata/userdata01.dbf'
TO '/u01/oradata/userdata01.dbf';
4、表空间online
5、删除原来的数据文件。
查询某个表空间对应的数据文件用这条语句：
SQL> select tablespace_name,file_name from dba_data_files;
基于数据库的方式
1、关闭数据库
2、mount数据库
3、复制数据库文件
4、更新控制文件
ALTER DATABASE RENAME
FILE '/u01/oradata/system01.dbf'
TO '/u03/oradata/system01.dbf';
5、alter database open
6、删除原来的数据文件
************************************************************************************************
2005-06-09 16：28
维护表空间信息：
1、tablespace information
dba_tablespaces
v$tablespace
2\datafile information
dba_data_files
v$datafile
3\ tempfile information
dba_temp_files
v$tempfile
************************************************************************************************
2005-06-09 21:28
段的分配：
创建
扩展
改变
段的回收：
已丢弃
已改变
已截断
************************************************************************************************
2005-06-09 21:31
db_block_size是缺省block size
************************************************************************************************
2005-06-09 22：25
查询DBA_SEGMENTS试图以获得分配给某个段的区和块的数目
SQL> select segment_name,tablespace_name,blocks,extents from dba_segments where owner='SCOTT'
2 /
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BLOCKS EXTENTS
------------------------------ ---------- ----------
DEPT
SYSTEM 8 1
EMP
SYSTEM 8 1
BONUS
SYSTEM 8 1
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BLOCKS EXTENTS
------------------------------ ---------- ----------
SALGRADE
SYSTEM 8 1
PK_DEPT
SYSTEM 8 1
PK_EMP
SYSTEM 8 1
已选择6行。
使用DBA_EXTENTS试图以检查给定段的区
SQL> select extent_id,file_id,block_id,blocks
2 from dba_extents
3 where owner='SCOTT';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 1 50449 8
0 1 50465 8
0 1 50481 8
0 1 50489 8
0 1 50457 8
0 1 50473 8
已选择6行。
使用 DBA_FREE_SPACE 视图以检查给定段的区
SQL> SELECT tablespace_name, count(*),
2 max(blocks),sum(blocks)
3 FROM dba_free_space
4 GROUP BY tablespace_name;
TABLESPACE_NAME COUNT(*) MAX(BLOCKS) SUM(BLOCKS)
------------------------------ ---------- ----------- -----------
CODES_TABLES 1 15328 15328
CWMLITE 2 1328 1360
DATA 2 12792 19176
DRSYS 1 1320 1320
EXAMPLE 1 64 64
INDX 1 3192 3192
ODM 1 1360 1360
SYSTEM 2 248 328
TOOLS 1 504 504
UNDO 1 1112 1112
UNDOTBS1 14 23880 24712
TABLESPACE_NAME COUNT(*) MAX(BLOCKS) SUM(BLOCKS)
------------------------------ ---------- ----------- -----------
USERS 1 3192 3192
XDB 1 24 24
************************************************************************************************
2005-06-10 9:09
undo segment 保存着旧的数据值，它存储修改之前的数据位置及数据。undo segment的标题包含一个事务处理表，该表中存储使用该undo segment的当前事务处理的消息。事务处理可以只使用一个回退段存储所有回退记录，许多并发事务处理可以写入一个回退段。
undo segment的主要作用是：
1、事务回滚
2、事务恢复
3、读一致性
sql语句读一致性
set transaction read only
dml一致性
set transaction isolation level serializable；
************************************************************************************************
2005-06-10 9:42
undo segment type
1、system undo segment：用在系统表空间中
2、non-system segment：用在对象表空间中
自动模式
手工模式：
私有的：只用在一个实例中
共有的：多个实例
3、延时：用在表空间offline，immediate，temporary 或者recovery
当使表空间脱机时可能创建延迟回退段它们用于在表空间恢复联机时回退事
务处理当不再需要它们时自动将它们丢弃
************************************************************************************************
2005-06-10 10：01
自动回滚段配置：
如果在数据库中只有一个回滚表空间存在，而且UNDO_MANAGEMENT 设置为AUTO,then undo_tablespace参数是可选的.oracle server将自动选择undo_tablespace.
初始化参数:
undo_management:指定系统是不是用auto或者manual模式,必须设置在初始化参数中.数据库启动后不能够动态地改变.缺省是手工模式.
undo_tablespace:指定一个特定的回滚段应用.,设置在初始化参数中,但可以动态设定,alter system set undo_tablespace=undotbs;
************************************************************************************************
2005-06-10 10：11
创建undo tablespace
CREATE DATABASE db01
. . .
UNDO TABLESPACE undo1 DATAFILE
SIZE 20M AUTOEXTEND ON
或者
CREATE UNDO TABLESPACE undo1
DATAFILE 'undo1db01.dbf' SIZE 20M;
************************************************************************************************
2005-06-10 10：15
在回滚表空间添加一个文件
SQL> run
1 alter tablespace undo1
2 add datafile 'f:\oracle\oradata\ora9i\undo2.dbf' size 10m
3* autoextend on
当alter一个undo tablespace时,可以用下列参数
ADD DATAFILE
RENAME
DATAFILE [ONLINE|OFFLINE]
BEGIN BACKUP
END BACKUP
************************************************************************************************
2005-06-10 10：18
在一个instance中可以有不仅仅一个undo tablespace ,但是只能有一个为active
用alter system 命令切换undo tablespace
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;
************************************************************************************************
2005-06-10 10：21
drop undo tablespace
SQL> drop tablespace undo1;
表空间已丢弃,但是数据文件需要手工删掉.
处在active状态的不能drop掉.如果想drop当前处在active状态的,需要切换表空间
************************************************************************************************
2005-06-10 10：24
查询是否有活动的事务存在
SQL> SELECT a.name,b.status
2> FROM v$rollname a, v$rollstat b
3> WHERE a.name IN ( SELECT segment_name
4> FROM dba_segments
5> WHERE tablespace_name = 'UNDOTBS'
6> )
************************************************************************************************
2005-06-10 10：41
自动undo management的其它参数
undo_suppress_errors:设置为true时,当企图执行由手工模式转为auto模式时,抑制错误.
undo_retention:控制为了读一致性保留的undo data 数量
ALTER SYSTEM SET UNDO_RETENTION=900;
************************************************************************************************
2005-06-10 10：47\
Undo Data Statistics
SQL> c /v$undostats/v$undostat;
1* select end_time,begin_time,undoblks from v$undostat
SQL> /
END_TIME BEGIN_TIME UNDOBLKS
---------- ---------- ----------
10-6月 -05 10-6月 -05 2
10-6月 -05 10-6月 -05 17
10-6月 -05 10-6月 -05 16
10-6月 -05 10-6月 -05 16
10-6月 -05 10-6月 -05 20
10-6月 -05 10-6月 -05 17
10-6月 -05 10-6月 -05 19
10-6月 -05 10-6月 -05 15
10-6月 -05 10-6月 -05 19
10-6月 -05 10-6月 -05 18
10-6月 -05 10-6月 -05 16
END_TIME BEGIN_TIME UNDOBLKS
---------- ---------- ----------
10-6月 -05 10-6月 -05 22
已选择12行。
************************************************************************************************
2005-06-10 10：49
Undo Quota
Using resource plans, users can be grouped and limits placed on the amount of resources that
can be used by the group. The amount of undo data generated by a group can be limited by
setting a value for UNDO_POOL: the default value is unlimited. When a group exceeds its
limit an error is received and no new transactions can be performed for the group until
current transactions complete or abort.
************************************************************************************************
2005-06-10 11:00
Data Dictionary Views
– DBA_ROLLBACK_SEGS
? Dynamic Performance Views
– V$ROLLNAME
– V$ROLLSTAT
– V$UNDOSTAT
– V$SESSION
– V$TRANSACTION
1、收集undo segments信息
SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ------ ------------------------------ ---------------
SYSTEM SYS SYSTEM ONLINE
_SYSSMU1$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU2$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU3$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU4$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU5$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU6$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU7$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU8$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU9$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU10$ PUBLIC UNDOTBS1 ONLINE
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ------ ------------------------------ ---------------
_SYSSMU11$ PUBLIC UNDO OFFLINE
_SYSSMU12$ PUBLIC UNDO OFFLINE
_SYSSMU13$ PUBLIC UNDO OFFLINE
_SYSSMU14$ PUBLIC UNDO OFFLINE
_SYSSMU15$ PUBLIC UNDO OFFLINE
_SYSSMU16$ PUBLIC UNDO OFFLINE
_SYSSMU17$ PUBLIC UNDO OFFLINE
_SYSSMU18$ PUBLIC UNDO OFFLINE
_SYSSMU19$ PUBLIC UNDO OFFLINE
_SYSSMU20$ PUBLIC UNDO OFFLINE
已选择21行。
2、实例当前回滚段使用统计
SQL> select n.name,s.extents,s.rssize,s.hwmsize,s.xacts,s.status from v$rollname n,v$rollstat s wher
e n.usn = s.usn;
NAME EXTENTS RSSIZE HWMSIZE XACTS STATUS
------------------------------ ---------- ---------- ---------- ---------- ---------------
SYSTEM 6 385024 385024 0 ONLINE
_SYSSMU1$ 7 450560 450560 0 ONLINE
_SYSSMU2$ 6 385024 385024 0 ONLINE
_SYSSMU3$ 7 450560 450560 0 ONLINE
_SYSSMU4$ 4 2220032 2220032 0 ONLINE
_SYSSMU5$ 7 450560 450560 0 ONLINE
_SYSSMU6$ 8 516096 516096 0 ONLINE
_SYSSMU7$ 8 516096 516096 0 ONLINE
_SYSSMU8$ 7 450560 450560 0 ONLINE
_SYSSMU9$ 5 1302528 1302528 0 ONLINE
_SYSSMU10$ 6 385024 385024 0 ONLINE
已选择11行。
3、To check the use of a undo segment by currently active transactions, join the
V$TRANSACTION and V$SESSION views:
Example
SQL> SELECT s.username, t.xidusn, t.ubafil,
2 t.ubablk, t.used_ublk
3 FROM v$session s, v$transaction t
4 WHERE s.saddr = t.ses_addr;
USERNAME XIDUSN UBAFIL UBABLK USED_UBLK
-------- ---------- ---------- ---------- ----------
HR 2 2 5005 1
************************************************************************************************
2005-06-10 11:09
临时表：
行由会话专用
CREATE GLOBAL TEMPORARY TABLE employee_temp
AS SELECT * FROM employee;
表仅在事务处理或会话期间保留数据
ON COMMIT PRESERVE ROWS;
数据不要求DML锁定
DML不生成重做日志
************************************************************************************************
2005-06-10 11:34
行移植
如果 PCTFREE 值设置得低则在一个块中可能没有足够的空间来容纳更新后
增长的行出现这种情况时Oracle 服务器会把整个行移动到一个新块并创建
一个从原块指向新位置的指针此进程称为行移植在移植行时与该行相关
联的 I/O 性能会降低因为 Oracle 服务器检索该行时必须扫描两个数据块
行链接
当一个行因太大而任何一个块都容纳不下时就会发生行链接当行包含的列
太长时就可能发生这种情况在这种情况下Oracle 服务器将该行分成称为行
片的更小的程序块每个行片存储在一个块中带有检索和装配整行所需要的
指针如果可能通过选择较大的块大小或将表分成含有较少列的多个表可
以最大限度地减少行链接
更改存储参数：
ALTER TABLE summit.employee
PCTFREE 30
PCTUSED 50
STORAGE(NEXT 500K
MINEXTENTS 2
MAXEXTENTS 100);
************************************************************************************************
2005-06-10 11:55
创建临时表：
SQL> create global temporary table t1_temp
2 as select * from t1;
************************************************************************************************
2005-06-10 13：53
更改表的存储参数
ALTER TABLE hr.employees
PCTFREE 30
PCTUSED 50
STORAGE(NEXT 500K
MINEXTENTS 2
MAXEXTENTS 100);
手工分配extents
ALTER TABLE hr.employees
ALLOCATE EXTENT(SIZE 500K
DATAFILE ‘/DISK3/DATA01.DBF’);
************************************************************************************************
2005-06-10 14：00
ALTER TABLE hr.employees
MOVE TABLESPACE data1;
移动一个表从一个表空间到另一个表空间
重组织表以消除行迁移。
************************************************************************************************
2005-06-10 14：06
truncate一个表
TRUNCATE TABLE hr.employees;
1、删除所有的行，并且释放空间
2、相应的索引也被删除。
3、没有回滚数据产生和命令隐含提交
4、不能truncate某个外键正在饮用的表
5、使用此命令时不会触发删除触发器。
***********************************************************************************************
2005-06-10 14：12
drop 一个表
DROP TABLE hr.department
CASCADE CONSTRAINTS;
如果某个表不再需要或者要对它进行重新组织就可以将它删除
删除表时将释放该表所用的区如果这些区相邻则在以后某个阶段可以自
动或手动将它们合并
***********************************************************************************************
2005-06-10 14：17
ALTER TABLE hr.employees
DROP COLUMN comments
CASCADE CONSTRAINTS CHECKPOINT 1000;
Mark a column as unused
ALTER TABLE hr.employees
SET UNUSED COLUMN comments CASCADE CONSTRAINTS;
Drop unused columns
ALTER TABLE hr.employees
DROP UNUSED COLUMNS CHECKPOINT 1000;
Continue to drop column operation
ALTER TABLE hr.employees
DROP COLUMNS CONTINUE CHECKPOINT 1000;
Information about tables can be obtained by querying
the data dictionary.
DBA_TABLES
DBA_OBJECTS
***********************************************************************************************
2005-06-10 14：20
查找表对象数目、位置
SQL> select table_name from dba_tables where owner='HR';
TABLE_NAME
------------------------------
COUNTRIES
DEPARTMENTS
EMPLOYEES
EMPLOYEES_TEMP
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
已选择8行。
SQL> select object_name,created
2 from dba_objects
3 where object_name like 'EMPLOYEES' AND OWNER='HR';
OBJECT_NAME
----------------------------------------------------------------------------
EMPLOYEES
***********************************************************************************************
2005-06-10 14：25
