下面就是小编给大家带来的[]Oracle将system表空间的数据文件变小（共含5篇），希望大家喜欢，可以帮助到有需要的朋友!同时，但愿您也能像本文投稿人“芝士榴莲千层”一样，积极向本站投稿分享好文章。
![[]Oracle将system表空间的数据文件变小](/d/file/357/2022-12-17/fw339240.jpg)
问题描述：今天闲的 ，在自己破电脑上面跑了一下这个过程：
create or replace procedure pro_test_pga isbegin insert into t_test2 select t.* from t_test t,(select rownum from dual connect by rownum <= 10 ) order by t.object_name; commit;end pro_test_pga;当时t_test有2883968行数据，这个过程跑下来各种direct path read temp因为我的pga_aggregate_target只有可怜的194M，其实开始跑这个过程的目的是验证把pga_aggregate_target调小会不会让速度更慢，因为这个数字是我建库的时候用的默认值，结果跑了5455.62秒，心都碎了，我的机器上的数据库是开了归档的，反正硬盘只这么多，他一边产生我一边删，不然空间一没，就停在那了，本来还想来个把pga_aggregate_target设置为10M试试的，但后来想想还是算了，至少这周算了，以后有时间试试，挂一晚就是，
[精品]Oracle将system表空间的数据文件变小
，一．表空间的创建
创建表空间：
Create tablespace felix
Datafile'/u01/app/oracle/oradata/felix/felixtbs.dbf'
Size100m autoextendonnext10m maxsize1024m
Extentmanagementlocaluniformsize128k
Segmentspacemanagementauto;
这个很重要，如何查看创建的表空间属性，就用如下的语句：
selecttablespace_name,block_size,contents,extent_management,allocation_type,segment_space_management
from dba_tablespaces
where tablespace_name='FELIX';
如果需要查看表空间的扩展属性，需要通过dba_data_files进行查看：
selecttablespace_name,autoextensible,increment_by,maxbytes
fromdba_data_files
where tablespace_name='FELIX';
--为应用创建用户
createuser felix identifiedby felix
defaulttablespace felix
temporarytablespace temp;
#创建用户的相关信息则可以通过DBA_USERS 视图查询得到：
selectusername,user_id,password,default_tablespace,temporary_tablespace
fromdba_users
whereusername='FELIX'
#进行相应的权限的授予：
grantconnect,resourceto felix;
revokeunnlimited tablesapce from felix;
alteruser felix quotaunlimitedon felix;
二．表空间管理技术
（1）数据字典表空间管理技术（DMT）：所谓的数据字典管理表空间是指，当创建或者删除对象时，oracle的表空间分配或回收是通过数据库中的数据字典来记录和管理的，用于管理的两个数据字典分别是：UET$ （used extents，已使用的空间）和FET$ （free extents，空闲表空间），
SQL>DESC UET$;
Name Type Nullable Default Comments
--------- ------ -------- ------- --------
SEGFILE# NUMBER
SEGBLOCK# NUMBER
EXT# NUMBER
TS# NUMBER
FILE# NUMBER
BLOCK# NUMBER
LENGTH NUMBER
SQL>DESC FET$;
Name Type Nullable Default Comments
------ ------ -------- ------- --------
TS# NUMBER
FILE# NUMBER
BLOCK# NUMBER
LENGTH NUMBER
可以清晰的看到文件号（FILE#）,数据块号(BLOCK#)等信息来管理空间的分配和回收；
数据字典管理表空间的工作方式;当一个新的段或者段在表空间中请求新的空间时，oracle通过执行一系列的sql语句来完成这个工作，这些工作包括从FET$找到可用的自由空间移动或增减相应的行到UET$中，并在FET$中删除相应的记录；当删除一个段的时候，oracle则移动UET$中相应的行到FET$；这个过程是连续的、串行的，在繁忙的数据库中，这类操作极可能导致竞争和等待，产生数据字典的争用；另一方面，当数据字典的表的信息被修改时，系统同样要记录undo和redo信息，频繁的修改又不可避免的对整个数据库的性能产生影响；
然而，数据字典管理表空间面临的另外一个问题就是：空间碎片
（2）本地管理表空间技术（LMT）：oracle不再使用数据字典管理而是在每个表空间的数据文件头加了一个位图区域，在其中记录每个extent的使用情况，每当一个extent被使用，或者被释放以供重新使用时oracle都会跟新数据文件头的这个记录，反应这个变化；
创建语法如下：
CREATE TABLESPACEtablespace_name
DATAFILE ‘datafile_path_name’
[EXTENT MANASGEMENT { LOCAL| AUTOALLOCATION | UNIFORM. [SIZEINTER [K|M]]}];
由于区间（extent）是oracle创建对象时的最小分配单元，所以表空间的管理实际上就是针对区间的管理；
--通过DBA_TABLESPACES视图查询表空间的类型：
select tablespace_name,extent_management,allocation_type
from dba_tablespaces;
DBA_EXTENTS记录了每个对象分配的区间（EXTENT），哪些对象分配了多少空间以及区间具体位于的文件等信息：
SQL>SELECTEXTENT_ID,BLOCK_ID,BLOCKS FROM DBA_EXTENTS WHERE SEGMENT_NAME='TS_TEST';
EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 536 8
1 544 8
2 552 8
3 560 8
4 568 8
5 576 8
6 584 8
7 592 8
8 600 8
9 608 8
... …
11 624 8
12 632 8
13 640 8
14 1800 8
15 1808 8
16 768 128
17 896 128
18 1024 128
19 1152 128
20 1280 128
21 1408 128
56 7040 128
57 7168 128
58 rowsselected
转储数据块信息：
select object_id,
dbms_rowid.rowid_relative_fno(rowid) file#,
dbms_rowid.rowid_block_number(rowid) block#
from ts_test
where rownum<=100;
进行dump查看：
#alter systemdumpdatafile3blockmax 1blockmin 6;
selectvaluefrom v$diag_info;
[oracle@felix ~]$ vi/u01/app/oracle/diag/rdbms/felix/felix/trace/felix_ora_3219.trc
*** -03-25 00:53:38.498
Block 1 (file header) not dumped:use dump file header command
Block dump from cache:
Dump of buffer cache at level 4 for tsn=2rdba=12582914
BH (0x6dbf97a8) file#: 3 rdba: 0x00c00002(3/2) class: 13 ba: 0x6db88000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: -1 objn: -1 tsn: 2 afn: 3 hint: f
hash: [0x77fb7be0,0x77fb7be0] lru: [0x6dbf9e80,0x6dbf9760]
lru-flags: hot_buffer
obj-flags: object_ckpt_list
ckptq: [0x6a3ed9a8,0x6a3e4eb8] fileq: [0x6c7e74c8,0x6bfe75f8] objq:[0x6d3e5e08,0x6dbf9788] objaq: [0x6dbf9eb8,0x6dbf9798]
st:XCURRENT md: NULL tch: 13
flags: buffer_dirty block_written_once redo_since_read
LRBA: [0x14.4b5f.0] LSCN: [0x0.14de1c] HSCN: [0x0.14de27] HSUB: [1]
Block dump from disk:
buffer tsn: 2 rdba: 0x00c00002 (3/2)
scn: 0x0000.00145380 seq: 0x02 flg: 0x04tail: 0x53801d02
frmt: 0x02 chkval: 0x1352 type: 0x1d=KTFBBitmapped FileSpace Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FAD09B98200 to0x00007FAD09B9A200
7FAD09B98200 0000A21D 00C00002 001453800400 [.........S......]
7FAD09B98210 00001352 00000003 0000000800003700 [R............7..]
7FAD09B98220 00000009 00000280 003FFFFE0000007E [..........?.~...]
7FAD09B98230 000036FF 00000060 000004DA00145375 [.6..`.......uS..]
7FAD09B98240 00000000 00000000 0000000000000000 [................]
alter session set events 'immediate trace name file_hdrs level 10';
1 select file_id,extent_id,block_id,blocks
2 from dba_extents
3*where segment_name='TS_TEST'
FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
4 0 536 8
4 1 544 8
4 2 552 8
4 13 640 8
4 14 1800 8
4 15 1808 8
4 16 768 128
4 17 896 128
4 18 1024 128
4 19 1152 128
4 20 1280 128
4 21 1408 128
4 22 1536 128
4 23 1664 128
4 24 2944 128
4 25 3072 128
4 26 3200 128
4 56 7040 128
4 57 7168 128
4 58 7296 128
59 rows selected.
SQL>select block_size,tablespace_name,min_extents,max_extents fromdba_tablespaces;
BLOCK_SIZE TABLESPACE_NAME MIN_EXTENTS MAX_EXTENTS
---------- ------------------------------ ----------- -----------
8192 SYSTEM 1 2147483645
8192 SYSAUX 1 2147483645
8192 UNDOTBS1 1 2147483645
8192 TEMP 1
8192 USERS 1 2147483645
8192 EXAMPLE 1 2147483645
8192 STATSPACK 1 2147483645
8192 STATSPACKTEMP 1
8192 FELIX 1 2147483645
9 rows selected
段空间管理技术：
段内则是以block为单位进行空间使用和管理的；
主要段的类型有：
SQL>select distinct(segment_type) fromdba_segments;
SEGMENT_TYPE
------------------------------------
LOBINDEX
INDEX PARTITION
TABLE SUBPARTITION
TABLE PARTITION
NESTED TABLE
ROLLBACK
LOB PARTITION
LOBSEGMENT
INDEX
TABLE
CLUSTER
TYPE2 UNDO
12 rows selected.
（1） 手工段空间管理（manual Segment space management）：这种技术（9i之前的做法）是室通过段头分配的自由列表（pctlist）来管理block的使用，简单一点就是把自由列表想象成一个数据表，oracle依赖一系列的算法通过自由列表中加入或移出block来管理段空间；
（2） 自动断管理方式（autosegment space management）：通过位图实现管理
ASSM的巨大优势是位图组能够减轻缓冲区忙等待（Bufferbusy wait）的负担，在9i以前的版本里曾是一个严重的问题；并且显著提高了并发性，因为位图数组的不同部分可以被同时使用，这样就消除了寻找剩余空间的串行化，
ASSM的管理机制，首先前2个数据块为数据文件头，3~8个数据块为位图区，接下来的第9个块和第10个块就是ASSM位图块；
进行dump第9个块：
Alter database dump datafile 3 block 9;
SQL>alter systemdump datafile 3 block 9;
System altered.
SQL>select value from v$diag_info;
VALUE
------------------------------------------------------------------------------------
TRUE
/u01/app/oracle
/u01/app/oracle/diag/rdbms/felix/felix
/u01/app/oracle/diag/rdbms/felix/felix/trace
/u01/app/oracle/diag/rdbms/felix/felix/alert
/u01/app/oracle/diag/rdbms/felix/felix/incident
/u01/app/oracle/diag/rdbms/felix/felix/cdump
/u01/app/oracle/diag/rdbms/felix/felix/hm
/u01/app/oracle/diag/rdbms/felix/felix/trace/felix_ora_3050.trc
0
0
11 rows selected.
tail -300 /u01/app/oracle/diag/rdbms/felix/felix/trace/felix_ora_3050.trc
*** 2014-03-25 02:07:30.355
Block dump from cache:
Dump of buffer cache at level 4 for tsn=2rdba=12582921
Block dump from disk:
buffer tsn: 2 rdba: 0x00c00009 (3/9)
scn: 0x0000.000f2557 seq: 0x01 flg: 0x04tail: 0x25571e01
frmt: 0x02 chkval: 0xc075 type: 0x1e=KTFBBitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FAD0893C600 to0x00007FAD0893E600
7FAD0893C600 0000A21E 00C00009 000F255704010000 [........W%......]
7FAD0893C610 0000C075 00000003 002E808000000000 [u...............]
7FAD0893C620 00000000 0000F800 0000000000000000 [................]
7FAD0893C630 00000000 00000000 0000000000000000 [................]
Repeat 507 times
7FAD0893E5F0 00000000 00000000 0000000025571E01 [..............W%]
File Space Bitmap Block:
BitMap Control:
RelFno: 3, BeginBlock: 3047552, Flag: 0,First: 0, Free: 63488
0000000000000000 0000000000000000 00000000000000000000000000000000
0000000000000000 00000000000000000000000000000000 0000000000000000
0000000000000000 00000000000000000000000000000000 0000000000000000
查询segment header，可以使用dba_segment视图：
SQL>selectsegment_name,header_file,header_file,header_block from dba_segments
2 where segment_NAME='TS_TEST';
SEGMENT_NAME HEADER_FILE HEADER_FILE HEADER_BLOCK
------------------- ----------- -----------------------
TS_TEST 4 4 538
存储结构之数据文件和表空间
oracle存储结构，可分物理结构和逻辑结构，后者是为方便管理前者而生。
oracle把数据逻辑地存放在表空间里，物理地存放在数据文件里。
有两个视图，我们可能会常用到：
dba_data_files:描述数据文件的信息
dba_tablespaces:描述表空间的信息
这里先谈三个问题。
表空间的类型？
1）永久性表空间，如：system，sysaux，user等
sysaux用于存放非核心功能的数据，如OEM
查看存放了哪些非核心功能的数据：
select occupant_name,occupant_desc,schema_name
from v$sysaux_occupants;
2)临时表空间
用于排序，创建索引
oracle建议，为每个用户创建一个临时表空间；
10g引入临时表空间组
3）undo表空间
虽有多个undo表空间，但任一时点，只有一个undo表空间被激活。
不同类型的表空间会产生不同的写入方式和时机点
永久性表空间
DBWn写入有两种方式多个时机点
方式一：
LRU机制：
LRU list：保存最近被存取的数据块；
Dirty list：被修改但尚未写入数据文件的数据块；
时机点：
1）Dirty buffer达到阀值时
2）没有free buffer时（server process在LRU list里找不到足够多的free buffer）
3）每3秒，DBWn会去检查dirty list，如果dirty list未到域值，就去读LRU list，将dirty buffer移到dirty list；如果dirty list已满，则写入数据文件，
方式二：
检查点事件：
时机点：
1）log switch时，要求做检查点，即：把dirty buffer flush到数据文件。也即：DBWn将dirty buffer从LRU list中移到dirty list，然后把dirty list中的dirty block flush到数据文件。
2）表空间下线或热备时，
3）drop一个对象时，
4）关闭数据库时
表空间组成？
段：占用存储空间的数据库对象，如：emp表又叫emp段。可跨越数据文件，但不能跨越表空间。
区：连续分配的空间。不能跨越数据文件。注意：这里的连续可能会带来空间碎片
块：
1）一个数据库中允许不同块大小，主要用于可传输表空间
2）通常，数据库中5种不同块大小：默认和非默认。在特殊情况，还存有非标准（不是2的幂）。注意：system表空间总是使用默认块大小，一个表空间中所有块的大小都相同。
3）块组成：
块开销：块头，表目录，行目录（指针表：指向每条记录）
空闲空间
数据空间
4）块头：数据块地址，数据块类型，事务表（ITL）
ITL:行级锁和读一致性的实现基础，每条记录含：UBA(undo block address），事务号，SCN号
一致性读：oracle对每次用户查询都要记录查询开始的SCN号，用于和数据块中的SCN号比较，如果数据块中的SCN号大于查询SCN，oracle就会利用UBA信息构造CR块，然后再比较CR块中的SCN号和查询SCN，如果仍然大于查询SCN，则还需要继续构造，直到CR块中的SCN小于或等于查询SCN，若还是找不到，就会报ora-01555错误了。
作者 linwaterbin
谈谈Oracle undo表空间
Oracle比其他数据库牛逼的地方好几个,其中一个很重要的就是undo表空间的引入(当然,锁也是 的一个东西)
1.oracle段的类型:
SQL>select segment_type from dba_segments t group by t.segment_type;
SEGMENT_TYPE
------------------
LOBINDEX
INDEX PARTITION
TABLE PARTITION
NESTED TABLE
ROLLBACK
LOB PARTITION
LOBSEGMENT
INDEX
TABLE
CLUSTER
TYPE2 UNDO
在dba_tablespace中.表空间的类型分为:undo,temporary,permanent
2.查看undo表空间创建之后创建的段
select * from dba_segments where tablespace_name = 'UNDOTBS1';
每一个undo段至少要有2个extent
也可以查看
select * from dba_rollback_segs
在status一栏有显示从数据库启动用的有10个undo段,如果存在多个undo表空间,那么从status=online的可以查看当前在用的回滚段.
个人认为9i的最大贡献就是,开始undo可以自动管理
3.每个回滚段最多有几个事务数?
在9i以前通过参数transactions_per_rollback_segment(默认是5个),现在已经失效,从10g开始默认一个回滚段上只有一个事务,如果回滚段不够的话,那么就自己创建undo段,直到undo表空间用完.这个时候,回滚段上的事务才开始有多个.
4.最牛逼的一致性读
一致性读(Consistent Get)是Oracle一个非常优秀的特性.(当然它也是产生ora-1555错误的主要原因)
在标准SQL中,为了防止并发事务中产生脏读,就需要通过加锁来控制.这样就会带来死锁、阻塞的问题,即时是粒度最小的行级锁,也无法避免这些问题.
为了解决这一矛盾,Oracle充分利用的回归段,通过会滚段进行一致性读取,即避免了脏读,又大大减少了系统的阻塞、死锁问题.
Oracle是如何实现一致性读的：
当Oracle更新数据块(Data Block Oracle中最小的存储单位)时,会在两个地方记录下这一更新动作.一个是在Redo Segment,.一个是回滚段UNDO Segment.并在数据块头部标示出来是否有修改数据.一个语句在读取数据快时,如果发现这个数据块是在它读取的过程中被修改的(即开始执行读操作时并没有被修改),就不直接从数据块上读取数据,而是从相应的回滚段条目中读取数据.这就保证了最终结果应该是读操作开始时的那一时刻的快照(snapshot),而不会受到读期间其他事务的影响.这就是Oracle的一致性读,也可以叫做多版本(Multi-Versioning).
5.ORACLE的据库事务隔离级别
事务隔离级别：一个事务对数据库的修改与并行的另一个事务的隔离程度，
两个并发事务同时访问数据库表相同的行时，可能存在以下三个问题：
(1)幻想读：事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这条新纪录就是幻想。
(2)不可重复读取：事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录，然后T1再次查询，发现与第一次读取的记录不同，这称为不可重复读。
(3)脏读：事务T1更新了一行记录，还未提交所做的修改，这个T2读取了更新后的数据，然后T1执行回滚操作，取消刚才的修改，所以T2所读取的行就无效，也就是脏数据。
为了处理这些问题，SQL标准定义了以下几种事务隔离级别
Oracle数据库支持READ COMMITTED 和 SERIALIZABLE这两种事务隔离级别。Oracle不支持脏读。
SET TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
6.Oracle是怎样实现一致性读的,我们可以通过以下实验来查看
(1)建一个测试的表
SQL>create tablespace test datafile '/u01/app/oracle/oradata/pmisdb/test.dbf' size 20M;
Tablespace created.
SQL>create table tt (id int,name varchar2(10)) tablespace test;
Table created.
SQL>
SQL>insert into tt values(1,'a++');
1 row created.
SQL>insert into tt values(2,'b');
1 row created.
SQL>insert into tt values(3,'c');
1 row created.
SQL>commit;
Commit complete.
SQL>
SQL>select * from tt;
ID NAME
---------- ----------
1 a++
2 b
3 c
SQL>
(2).打开一个session A ,对改表进行update操作
session A >update tt set name='a' where id=1;
1 row updated.
session A >select * from tt;
ID NAME
---------- ----------
1 a
2 b
3 c
(3)打开一个session B ,进行查询..因为有一致性读的特性,所以在session B中,在A没有提交前,B是看不到A修改的数据的.
session B >select * from tt;
ID NAME
---------- ----------
1 a++
2 b
3 c
session B >
(4)可以根据rowid以及oracle提供的dbms_rowid包来查看该条记录所在的数据文件和数据块
session A >select id,name,rowid from tt;
ID NAME ROWID
---------- ---------- ------------------
1 a AAARFuAAIAAAAAQAAA
2 b AAARFuAAIAAAAAQAAB
3 c AAARFuAAIAAAAAQAAC
SQL>select dbms_rowid.rowid_relative_fno('AAARFuAAIAAAAAQAAA') as file#,
2 dbms_rowid.rowid_block_number('AAARFuAAIAAAAAQAAA') as block#
3 from dual;
FILE# BLOCK#
---------- ----------
8 16
(5)根据查询到的文件号和块号进行dump,注意这个地方dump的其实是内存里面的数据,如果需要dump磁盘上的数据文件,那么把8改成具体的路径就可以了,因为oracle写是异步的,这个时候磁盘的数据文件并不一定已经有这个信息了.
session A >alter system dump datafile 8 block 16;
System altered.
session A >SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
2 p.spid || '.trc' trace_file_name
3 FROM (SELECT p.spid
4 FROM v$mystat m, v$session s, v$process p
5 WHERE m.statistic# = 1
6 AND s.SID = m.SID
7 AND p.addr = s.paddr) p,
8 (SELECT t.INSTANCE
9 FROM v$thread t, v$parameter v
10 WHERE v.NAME = 'thread'
11 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
12 (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_3827.trc
(6)打开trace文件,进行观察,分别截取开头和有关事务的内容:
[root@pmiscs ~]# more /u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_3790.trc
Dump file /u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_3790.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: pmiscs
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:12 EDT
Machine: i686
Instance name: pmisdb
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 3790, image: oracle@pmiscs (TNS V1-V3)
*** ACTION NAME: -09-25 13:19:01.211
*** MODULE NAME:(sqlplus@pmiscs (TNS V1-V3)) 2012-09-25 13:19:01.211
*** SERVICE NAME:() 2012-09-25 13:19:01.211
*** SESSION ID:(544.3) 2012-09-25 13:19:01.211
Successfully allocated 2 recovery slaves
Using 543 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 613, block 2, scn 18284996
cache-low rba: logseq 613, block 449
on-disk rba: logseq 613, block 508, scn 18285712
change track rba: logseq 613, block 507, scn 18285711
start recovery at logseq 613, block 449, scn 0
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 29Kb in 0.19s =>0.15 Mb/sec
Total physical reads: 4096Kb
Longest record: 2Kb, moves: 0/11 (0%)
Longest LWN: 6Kb, moves: 0/47 (0%), moved: 0Mb
Last redo scn: 0x0000.0117048f (18285711)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 9/9 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 12/21 = 0.6
----------------------------------------------
*** 2012-09-25 13:19:01.442
KCRA: start recovery claims for 9 data blocks
*** 2012-09-25 13:19:01.499
KCRA: blocks processed = 9/9, claimed = 9, eliminated = 0
*** 2012-09-25 13:19:01.499
Recovery of Online Redo Log: Thread 1 Group 6 Seq 613 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 9/9 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 21/21 = 1.0
----------------------------------------------
kwqmnich: current time:: 5: 19: 6
kwqmnich: instance no 0 check_only flag 1
kwqmnich: initialized job cache structure
*** 2012-09-25 13:21:45.194
Start dump data blocks tsn: 10 file#: 8 minblk 16 maxblk 16
buffer tsn: 10 rdba: 0x0010 (8/16)
scn: 0x0000.01175499 seq: 0x01 flg: 0x00 tail: 0x54990601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DCC2400 to 0x0DCC4400
...
Block header dump: 0x02000010
Object id on Block? Y
seg/obj: 0x1116e csc: 0x00.1175499 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.005.00000c41 0x00800c4a.0692.13 ---- 1 fsc 0x0001.00000000
0x02 0x0003.01b.00000cfe 0x008006d1.072a.04 C--- 0 scn 0x0000.01151109
data_block_dump,data header at 0xdcc2464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0dcc2464
bdba: 0x02000010
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f5c
avsp=0x1f64
tosp=0x1f65
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f5c
0x14:pri[1] offs=0x1f88
0x16:pri[2] offs=0x1f80
block_row_dump:
tab 0, row 0, @0x1f5c
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 61
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 62
tab 0, row 2, @0x1f80
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [ 1] 63
end_of_block_dump
End dump data blocks tsn: 10 file#: 8 minblk 16 maxblk 16
这个dump文件开头对数据库的环境做了一些描述,中间是一些16进制的内容,最后面是事务和行的一些信息,任何一个事务想修改数据块,都必需要获取一个Itl:
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.005.00000c41 0x00800c4a.0692.13 ---- 1 fsc 0x0001.00000000
0x02 0x0003.01b.00000cfe 0x008006d1.072a.04 C--- 0 scn 0x0000.01151109
看上面的事务的信息,查看Flag,4个'-'代表有一个事务正在修改数据块,Lck代表当前锁定了一条数据,Itl=0x01,其实对应的就是下面的:
tab 0, row 0, @0x1f5c
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 61
当为0x1状态时,表明该条数据已经被锁定,加了TX锁,其他事务想访问它的时候会被阻塞..
(7)从这个时候Oracle数据块的强大开始体现出来,如果是其他数据块(如sqlserver)的话,那么就会等待,而Oracle的一致性读 的解决了这个问题,它不让阻塞,而是让其他session去undo段里读,具体的undo地址就是Uba(undo block address)所指示的地址:0x00800c4a.0692.13
我们对这个地址进行转换,查询它具体是哪个文件的哪个块,首先将16进制转换为10进制,再用相应的工具包进行转换查询:
SQL>select to_number('00800c4a','xxxxxxxx') from dual;
TO_NUMBER('0080B673','XXXXXXXX')
--------------------------------
8391754
SQL>select dbms_utility.data_block_address_file(8391754) as file#,
2 dbms_utility.data_block_address_block(8391754) as block#
3 from dual;
FILE# BLOCK#
---------- ----------
2 3146
这下很清晰的查看到了,是在第2个数据文件的,第3146块上,再查询下第2个数据文件是啥文件
SQL>select tablespace_name,file_id from dba_data_files where file_id=2;
TABLESPACE_NAME FILE_ID
------------------------------ ----------
UNDOTBS1 2
哈,这下更清晰了,那个地址指向的就是undo表空间里面的数据块!
(8)再根据数据文件号和数据块进行dump:
SQL>select tablespace_name,file_id from dba_data_files where file_id=2;
TABLESPACE_NAME FILE_ID
------------------------------ ----------
UNDOTBS1 2
SQL>alter system dump datafile 2 block 3146;
System altered.
SQL>SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
2 p.spid || '.trc' trace_file_name
3 FROM (SELECT p.spid
4 FROM v$mystat m, v$session s, v$process p
5 WHERE m.statistic# = 1
6 AND s.SID = m.SID
7 AND p.addr = s.paddr) p,
8 (SELECT t.INSTANCE
9 FROM v$thread t, v$parameter v
10 WHERE v.NAME = 'thread'
11 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
12 (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_4113.trc
(9)查看dump undo出来的文件:
Dump file /u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_4113.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: pmiscs
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:12 EDT 2008
Machine: i686
Instance name: pmisdb
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 4113, image: oracle@pmiscs (TNS V1-V3)
*** ACTION NAME:() 2012-09-25 13:31:36.874
*** MODULE NAME:(sqlplus@pmiscs (TNS V1-V3)) 2012-09-25 13:31:36.874
*** SERVICE NAME:(SYS$USERS) 2012-09-25 13:31:36.874
*** SESSION ID:(523.77) 2012-09-25 13:31:36.874
Start dump data blocks tsn: 1 file#: 2 minblk 3146 maxblk 3146
buffer tsn: 1 rdba: 0x00800c4a (2/3146)
scn: 0x0000.0117548c seq: 0x01 flg: 0x04 tail: 0x548c0201
frmt: 0x02 chkval: 0x3970 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0E101400 to 0x0E103400
...
********************************************************************************
UNDO BLK:
xid: 0x0004.005.00000c41 seq: 0x692 cnt: 0x13 irb: 0x13 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f94 0x02 0x1eac 0x03 0x1e04 0x04 0x1d68 0x05 0x1d04
0x06 0x1c68 0x07 0x1c04 0x08 0x1ba8 0x09 0x1b54 0x0a 0x1af8
0x0b 0x1aa4 0x0c 0x1a48 0x0d 0x19f4 0x0e 0x1900 0x0f 0x18b4
0x10 0x17f0 0x11 0x178c 0x12 0x1738 0x13 0x1684
*-----------------------------
* Rec #0x1 slt: 0x01 objn: 519(0x00000207) objd: 519 tblspc: 0(0x00000000)
* Layer: 10 (Index) opc: 22 rci 0x00
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00800c49
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800c49.0692.2f
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x401029 block=0x0040f182
(kdxlpu): purge leaf row
key :(10): 06 c5 2b 5f 60 0d 0e 02 c1 1d
...
*-----------------------------
* Rec #0x12 slt: 0x28 objn: 5141(0x00001415) objd: 5141 tblspc: 0(0x00000000)
* Layer: 10 (Index) opc: 22 rci 0x11
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800c4a.0692.11
Dump kdilk : itl=3, kdxlkflg=0x1 sdc=0 indexid=0x402b51 block=0x00402b52
(kdxlpu): purge leaf row
key :(10): 02 c1 04 06 00 40 2b 2a 00 08
*-----------------------------
* Rec #0x13 slt: 0x05 objn: 69998(0x0001116e) objd: 69998 tblspc: 10(0x0000000a)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x00800c4a.0692.10 ctl max scn: 0x0000.0116f8c0 prv tx scn: 0x0000.0116f8d8
txn start scn: scn: 0x0000.0117548c logon user: 0
prev brb: 8391750 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0009.00f.00000a96 uba: 0x0080019d.0670.22
flg: C--- lkc: 0 scn: 0x0000.0114e6a3
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x02000010 hdba: 0x0200000b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: 2
col 1: [ 3] 61 2b 2b
End dump data blocks tsn: 1 file#: 2 minblk 3146 maxblk 3146
(10)怎么去读这个dump文件,查找那条有事务的记录呢?其实在上面undo地址Uba(undo block address)所指示的地址:0x00800c4a.0692.13,已经告诉我们了,0x00800c4a是16进制的地址,而13就是那条update的记录!我们单独把那条记录拿出来:
*-----------------------------
* Rec #0x13 slt: 0x05 objn: 69998(0x0001116e) objd: 69998 tblspc: 10(0x0000000a)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x00800c4a.0692.10 ctl max scn: 0x0000.0116f8c0 prv tx scn: 0x0000.0116f8d8
txn start scn: scn: 0x0000.0117548c logon user: 0
prev brb: 8391750 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0009.00f.00000a96 uba: 0x0080019d.0670.22
flg: C--- lkc: 0 scn: 0x0000.0114e6a3
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x02000010 hdba: 0x0200000b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: 2
col 1: [ 3] 61 2b 2b
(11)对比session A dump出来的信息和从undo dump出来的信息:
--session A
col 1: [ 1] 61
--undo
col 1: [ 3] 61 2b 2b
通过对比,可以发现session A的col1的值为61,undo里面的col1的值为61 2b 2b,把这2个值转换成ascii码:
SQL>select chr(to_number('61','xx')),chr(to_number('2b','xx')) from dual;
CH CH
-- --
a +
哈哈,这下清楚了.原来61代表的是'a',2b代表的'+'
(12)这样,我们就把undo是怎么工作的实验做完了.把原理再完整的描述一遍:session A对某条记录做了dml操作,这个操作是在内存中完成的,这个时候在undo里面记录一条信息,如果满足了DBWn的条件那么就会写入到磁盘中,不满足的话就在内存中,在没有提交之前,undo的信息一直不会被清除.session B在查询该条记录时,因为A没有提交,所以在itl事务槽中对该条信息有一个记录,会告诉session B去undo相应的地址查找该条记录的内容,而不去使用内存中被改变的信息.这就是Oracle的一致性读.
7.回过头来,我们再看看相应的数据字典和动态性能视图:
(1).在之前查找undo的文件和块的地方,其实oracle已经给了我们一个视图,告诉了我们相关的信息了
SQL>select t.UBAFIL,t.UBABLK from v$transaction t;
UBAFIL UBABLK
---------- ----------
2 3146
(2).其他几个动态性能视图
SQL>select a.USN,a.XACTS from v$rollstat a where xacts 0;
USN XACTS
---------- ----------
4 1
SQL>select segment_name from dba_rollback_segs where segment_id = 4;
SEGMENT_NAME
------------------------------
_SYSSMU4$
SQL>select * from dba_extents where segment_name = '_SYSSMU4$';
1 SYS _SYSSMU4$ TYPE2 UNDO UNDOTBS1 0 2 57 65536 8 2
2 SYS _SYSSMU4$ TYPE2 UNDO UNDOTBS1 1 2 225 65536 8 2
3 SYS _SYSSMU4$ TYPE2 UNDO UNDOTBS1 2 2 3081 1048576 128 2
4 SYS _SYSSMU4$ TYPE2 UNDO UNDOTBS1 3 2 2057 1048576 128 2
从v$rollstat的xacts不为0d记录中可以得到当前能有事务的回滚段,根据回滚段号去dba_rollback_segs查找相应的名字,再根据名字去dba_extents去查找相应的信息,这样一来,就把所有的知识都联系起来了.
Oracle删除用户和表空间
racle 使用时间长了， 新增了许多user 和tablespace. 需要清理一下
对于单个user和tablespace 来说， 可以使用如下命令来完成，
步骤一： 删除user
drop user ×× cascade
说明： 删除了user，只是删除了该user下的schema objects，是不会删除相应的tablespace的。
步骤二： 删除tablespace
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
但是，因为是供开发环境来使用的db, 需要清理的user 和 table space 很多。
思路：
Export出DB中所有的user和tablespace, 筛选出系统的和有用的tablespace,把有用的信息load到一张表中去。
然后写例程循环，把不在有用表的tablespace删掉
1. select username,default_tablespace from dba_users;
2.
create table MTUSEFULSPACE
(
ID Number(4) NOT NULL PRIMARY KEY,
USERNAME varchar2(30),
TABLESPACENAME varchar2(60),
OWNERNAME varchar2(30)
);
3.
declare icount number(2);
tempspace varchar2(60);
begin
for curTable in (select username as allusr,default_tablespace as alltblspace from dba_users)
loop
tempspace :=curTable.alltblspace;
dbms_output.put_line(tempspace);
select count(TABLESPACENAME) into icount from MTUSEFULSPACE where TABLESPACENAME = tempspace;
if icount=0 then
DROP TABLESPACE tempspace INCLUDING CONTENTS AND DATAFILES;
end if;
commit;
end loop;
end;
执行后会报如下错误
ORA-06550: 第 10 行, 第 5 列:
PLS-00103: 出现符号 “DROP”在需要下列之一时：
begin case declare exit
for goto if loop mod null pragma raise return select update
while with
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
06550. 00000 - “line %s, column %s:\n%s”
*Cause: Usually a PL/SQL compilation error.
*Action:
好像是被锁了，
。
没办法，例程不能写，就只能组出语句执行了。
把需要删除的user, tablespace 导出到Excel. 使用CONCATENATE 组出SQL.
贴到SQLdevelop 批量执行。
整个删除会比较耗时间， 100多个user. 用了12个小时左右。
如要找datafile的具体位置，可以使用
select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts#;
SQL code
--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;
