innodb共享表空间vs独立表空间(（精选6篇））由网友“BLINGBLING”投稿提供，以下是小编收集整理的innodb共享表空间vs独立表空间，仅供参考，希望对大家有所帮助。
篇1：innodb共享表空间vs独立表空间
innodb共享表空间vs独立表空间
在使用Innodb引擎时将要面对两种表空间的管理选择的问题，Innodb有两种管理表空间的方法：
1． 共享表空间（也可以拆分成多个小的表空间）
2． 独立表空间每一个表有一个独立的表空间，
我个人推荐使用独立表空间。在性能和运维上独立表空间比共享的表空间有很多优势。下面我将分别说明一下两种表空间管理的特点。
共享表空间：
优点：
可以放表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同步的文件上）。数据和文件放在一起方便管理。
缺点：
所有的数据和索引存放到一个文件中以为着将有一个很常大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。
我们知道共享表空间管理会出现表空间分配后不能回缩的问题，当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了。我们存在磁盘监控时，也许就报警不断了，但实际上MySQL还可以运行良好。另外，当磁盘上占用较多时性能也不是太好。
这种情况处理只能是是建一个新的Slave从主库上Dump出来，然后在Dump到从库中，动作较大。
对于InnoDB Hot Backup备份的操作（或是直接冷备），每次需要CP的文件比较大。如果现在有180G的表空间，但实际数据只有50多G，那么我们将面对每次需要拷180G的数据。
这种方式也许mysqldump是一个好的处理方式了。
独立表空间：
在配置文件（my.cnf）中设置： innodb_file_per_table
优点：
1． 每个表都有自已独立的表空间。
2． 每个表的数据和索引都会存在自已的表空间中，
3． 可以实现单表在不同的数据库中移动。
4． 空间可以回收（除drop table操作处，表空不能自已回收）
a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
c) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
缺点：
单表增加过大，如超过100个G。
对于单表增长过大的问题，如果使用共享表空间可以把文件分开，但有同样有一个问题，如果访问的范围过大同样会访问多个文件，一样会比较慢。对于独立表空间也有一个解决办法是：使用分区表，也可以把那个大的表空间移动到别的空间上然后做一个连接。其实从性能上出发，当一个表超过100个G有可能响应也是较慢了，对于独立表空间还容易发现问题早做处理。
备份：
InnoDB Hot Backup（冷备）的表空间cp不会面对很多无用的copy了。而且利用innodb hot backup及表空间的管理命令可以实现单现移动。
监控：
可以更好从系统上监控数据的大小，每个表的大小。
另外推荐使用独立表空间的原因：
从性能上对比共享表空间和独立表空间：
共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。这里也有一个TIPS当启用独立表空间时，请合理调整一下：innodb_open_files 。
从Linux系统处理上出发：
文件系统fsync一大片更新数据，对系统io冲击较大。若分隔成多个小数据fsync，能够减少对读的影响。 同时从mysql代码，发现mysql保证两次fsync之间至少有20ms的sleep，这样的话，若将一次fsync变成多次小数据操作，应该能够减少慢查询的比例。所以对于大量更新操作的系统不太适合用共享表空间。
来源 www.mysqlsupport.cn/
篇2：可传输表空间
oracle2271
对于可传输表空间有一个重要概念：自包含（Self-Contained），
在表空间传输的中，要求表空间集为自包含的，自包含表示用于传输的内部表空间集没有引用指向外部表空间集。自包含分为两种：一般自包含表空间集和完全（严格）自包含表空间集。
常见的以下情况是违反自包含原则的：
索引在内部表空间集，而表在外部表空间集（相反地，如果表在内部表空间集，而索引在外部表空间集，则不违反自包含原则）。
分区表一部分区在内部表空间集，一部分在外部表空间集（对于分区表，要么全部包含在内部表空间集中，要么全不包含）。
如果在传输表空间时同时传输约束，则对于引用完整性约束，约束指向的表在外部表空间集，则违反自包含约束；如果不传输约束，则与约束指向无关。
表在内部表空间集，而lob列在外部表空间集，则违反自包含约束。
通常可以通过系统包DBMS_TTS来检查表空间是否自包含，验证可以以两种方式执行：非严格方式和严格方式。
以下是一个简单的验证过程，假定在eygle表空间存在一个表eygle，其上存在索引存储在USERS表空间：
SQL> create table eygle as select rownum id ,username from dba_users;Table created.
SQL> create index ind_id on eygle(id) tablespace users;
Index created.
以SYS用户执行非严格自包含检查（full_check=false）：
SQL> connect / as sysdbaConnected.
SQL> exec dbms_tts.transport_set_check(‘EYGLE‘, TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
执行严格自包含检查（full_check=true）：
SQL> exec dbms_tts.transport_set_check(‘EYGLE‘, TRUE, True);PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
--------------------------------------------------------------------------------------
Index EYGLE.IND_ID in tablespace USERS points to table EYGLE.EYGLE in tablespace EYGLE
反过来对于USERS表空间来说，非严格检查也是无法通过的：
SQL> exec dbms_tts.transport_set_check(‘USERS‘, TRUE);PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
----------------------------------------------------------------------------------------
Index EYGLE.IND_ID in tablespace USERS points to table EYGLE.EYGLE in tablespace EYGLE
但是可以对多个表空间同时传输，则一些自包含问题就可以得到解决：
SQL> exec dbms_tts.transport_set_check(‘USERS,EYGLE‘, TRUE, True);PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
官方解释如下：
There may be logical or physical dependencies between objects in the transportable set and those outside of the set. You can only transport a set of tablespaces that is self-contained. In this context “self-contained” means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. Some examples of self contained tablespace violations are:
An index inside the set of tablespaces is for a table outside of the set of tablespaces.
Note:
It is not a violation if a corresponding index for a table is outside of the set of tablespaces.A partitioned table is partially contained in the set of tablespaces.
The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you want to transport a subset of a partition table, you must exchange the partitions into tables.
A referential integrity constraint points to a table across a set boundary.
When transporting a set of tablespaces, you can choose to include referential integrity constraints. However, doing so can affect whether or not a set of tablespaces is self-contained. If you decide not to transport constraints, then the constraints are not considered as pointers.
A table inside the set of tablespaces contains a LOB column that points to LOBs outside the set of tablespaces.
An XML DB schema (*.xsd) that was registered by user A imports a global schema that was registered by user B, and the following is true: the default tablespace for user A is tablespace A, the default tablespace for user B is tablespace B, and only tablespace A is included in the set of tablespaces.
To determine whether a set of tablespaces is self-contained, you can invoke the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS. You must have been granted the EXECUTE_CATALOG_ROLE role (initially signed to SYS) to execute this procedure.
When you invoke the DBMS_TTS package, you specify the list of tablespaces in the transportable set to be checked for self containment. You can optionally specify if constraints must be included. For strict or full containment, you must additionally set the TTS_FULL_CHECK parameter to TRUE.
The strict or full containment check is for cases that require capturing not only references going outside the transportable set, but also those coming into the set. Tablespace Point-in-Time Recovery (TSPITR) is one such case where dependent objects must be fully contained or fully outside the transportable set.
For example, it is a violation to perform. TSPITR on a tablespace containing a table t but not its index i because the index and data will be inconsistent after the transport. A full containment check ensures that there are no dependencies going outside or coming into the transportable set. See the example for TSPITR in the Oracle Database Backup and Recovery User‘s Guide.
incl_constraints
TRUE if you want to count in referential integrity constraints when examining if the set of tablespaces is self-contained. (Theincl_constraints parameter is a default so that TRANSPORT_SET_CHECK will work if it is called with only the ts_list argument.)
full_check
Indicates whether a full or partial dependency check is required. If TRUE, treats all IN and OUT pointers (dependencies) and captures them as violations if they are not self-contained in the transportable set. The parameter should be set to TRUE for TSPITR or if a strict version of transportable is desired. By default the parameter is set to false. It will only consider OUT pointers as violations.
1.3.5可传输表空间
在很多Oracle文档中，可能大家都注意过Oracle用来进行测试的一个表空间，这个表空间中有一系列预置的用户和数据，可以用于数据库或BI的很多测试实验。
这个表空间在使用模板建库时是可以选择的，在如图1-22所示的这个界面中，可以选择建库时包含这个范例表空间（缺省是未选择的）。
vcWxvr2ru+HT0Mv5uMSx5KOs1vfSqtT2vNPBy8jnz8LT777ko7o8L3A+CjwvYmxvY2txdW90ZT4KPGJsb2NrcXVvdGU+CmNvbm5lY3QgJnF1b3Q7U1lTJnF1b3Q7LyZxdW90OyZhbXA7JmFtcDtzeXNQYXNzd29yZCZxdW90OyBhcyBTWVNEQkE8YnI+CkBDOlxvcmFjbGVcMTAuMi4wXGRlbW9cc2NoZW1hXG1rcGx1Zy5zcWwgJmFtcDsmYW1wO3N5c1Bhc3N3b3JkIGNoYW5nZV9vbl9pbnN0YWxsIGNoYW5nZV9vbl9pbnN0YWxsIDxicj4KY2hhbmdlX29uX2luc3RhbGwgY2hhbmdlX29uX2luc3RhbGwgY2hhbmdlX29uX2luc3RhbGwgY2hhbmdlX29uX2luc3RhbGwgQzpcb3JhY2xlXDEwLjIuMFxhc3Npc3RhbnRzXGRiY2FcdGVtcGxhdGVzXGV4YW1wbGUuZG1wIEM6XG9yYWNsZVwxMC4yLjBcYXNzaXN0YW50c1xkYmNhXHRlbXBsYXRlc1xleGFtcGxlMDEuZGZiIEM6XG9yYWNsZVxvcmFkYXRhXGV5Z2xlXGV4YW1wbGUwMS5kYmYgQzpcb3JhY2xlXGFkbWluXGV5Z2xlXHNjcmlwdHNcICZxdW90Ow==“SYS/&&sysPassword as SYSDBA‘”;
看到这里，再次引用了模板目录中的文件：
C:>dir C:oracle10.2.0assistantsdbcatemplatesex*驱动器 C 中的卷是 SYSTEM
卷的序列号是 8C88-D1B4
C:oracle10.2.0assistantsdbcatemplates 的目录
-09-07 13:02 983,040 example.dmp
2005-09-07 13:02 20,897,792 example01.dfb
2 个文件 21,880,832 字节
0 个目录 915,578,880 可用字节
通过mkplug.sql脚本来加载这个范例表空间，来看一下这个脚本的主要内容，
电脑资料
同样，最重要的是通过dbms_backup_restore包从example01.dfb文件中恢复数据文件：
SELECT TO_CHAR(systimestamp, ‘YYYYMMDD HH:MI:SS‘) FROM dual;
variable new_datafile varchar2(512)
declare
done boolean;
v_db_create_file_dest VARCHAR2(512);
devicename varchar2(255);
data_file_id number;
rec_id number;
stamp number;
resetlogs_change number;
creation_change number;
checkpoint_change number;
blksize number;
omfname varchar2(512);
real_file_name varchar2(512);
begin
dbms_output.put_line(‘ ‘);
dbms_output.put_line(‘ Allocating device.... ‘);
dbms_output.put_line(‘ Specifying datafiles... ‘);
devicename := dbms_backup_restore.deviceAllocate;
dbms_output.put_line(‘ Specifing datafiles... ‘);
SELECT MAX(file_id)+1 INTO data_file_id FROM dba_data_files;
SELECT value INTO v_db_create_file_dest FROM v$parameter WHERE name =‘db_create_file_dest‘;
IF v_db_create_file_dest IS NOT NULL
THEN
dbms_backup_restore.restoreSetDataFile;
dbms_backup_restore.getOMFFileName(‘EXAMPLE‘,omfname);
dbms_backup_restore.restoreDataFileTo(data_file_id, omfname, 0,‘EXAMPLE‘);
ELSE
dbms_backup_restore.restoreSetDataFile;
dbms_backup_restore.restoreDataFileTo(data_file_id,‘&data_file_name‘);
END IF;
dbms_output.put_line(‘ Restoring ... ‘);
dbms_backup_restore.restoreBackupPiece(‘&data_file_backup‘, done);
SELECT max(recid) INTO rec_id FROM v$datafile_copy;
-- Now get the real file name. It could be also OMF filename
SELECT name, stamp, resetlogs_change#, creation_change#, checkpoint_change#,block_size
INTO real_file_name, stamp,resetlogs_change, creation_change, checkpoint_change, blksize
FROM V$DATAFILE_COPY
WHERE recid = rec_id and file# = data_file_id;
-- Uncatalog the file from V$DATAFILE_COPY. This important.
dbms_backup_restore.deleteDataFileCopy(recid => rec_id,
stamp => stamp,
fname => real_file_name,
dfnumber => data_file_id,
resetlogs_change => resetlogs_change,
creation_change => creation_change,
checkpoint_change => checkpoint_change,
blksize => blksize,
no_delete => 1,
force => 1);
-- Set the bindvariable to the real filename
:new_datafile := real_file_name;
if done then
dbms_output.put_line(‘ Restore done.‘);
else
dbms_output.put_line(‘ ORA-XXXX: Restore failed ‘);
end if;
end;
/
这个恢复完成之后，接下来最重要的部分就是通过传输表空间技术将example表空间导入到当前的数据库。
考虑一下这种情况，当进行跨数据库迁移时，需要将一个用户表空间中的数据迁移到另外一个数据库，应该使用什么样的方法呢？最常规的做法可能是通过EXP工具将数据全部导出，然后在目标数据库上IMP导入，可是这种方法可能会比较缓慢。EXP工具同时还提供另外一种技术-可传输表空间技术，可以用于加快这个过程。
在exp -help的帮助中，可以看到这样一个参数：
TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)
通过这个选项，我们可以对一组自包含、只读的表空间只导出元数据，然后在操作系统层将这些表空间的数据文件拷贝至目标平台，并将元数据导入数据字典（这个过程称为插入，plugging），即完成迁移。
对于可传输表空间有一个重要概念：自包含（Self-Contained）。
在表空间传输的中，要求表空间集为自包含的，自包含表示用于传输的内部表空间集没有引用指向外部表空间集。自包含分为两种：一般自包含表空间集和完全（严格）自包含表空间集。
常见的以下情况是违反自包含原则的：
索引在内部表空间集，而表在外部表空间集（相反地，如果表在内部表空间集，而索引在外部表空间集，则不违反自包含原则）。
分区表一部分区在内部表空间集，一部分在外部表空间集（对于分区表，要么全部包含在内部表空间集中，要么全不包含）。
如果在传输表空间时同时传输约束，则对于引用完整性约束，约束指向的表在外部表空间集，则违反自包含约束；如果不传输约束，则与约束指向无关。
表在内部表空间集，而lob列在外部表空间集，则违反自包含约束。
通常可以通过系统包DBMS_TTS来检查表空间是否自包含，验证可以以两种方式执行：非严格方式和严格方式。
以下是一个简单的验证过程，假定在eygle表空间存在一个表eygle，其上存在索引存储在USERS表空间：
SQL> create table eygle as select rownum id ,username from dba_users;Table created.
SQL> create index ind_id on eygle(id) tablespace users;
Index created.
以SYS用户执行非严格自包含检查（full_check=false）：
SQL> connect / as sysdbaConnected.
SQL> exec dbms_tts.transport_set_check(‘EYGLE‘, TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
执行严格自包含检查（full_check=true）：
SQL> exec dbms_tts.transport_set_check(‘EYGLE‘, TRUE, True);PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
--------------------------------------------------------------------------------------
Index EYGLE.IND_ID in tablespace USERS points to table EYGLE.EYGLE in tablespace EYGLE
反过来对于USERS表空间来说，非严格检查也是无法通过的：
SQL> exec dbms_tts.transport_set_check(‘USERS‘, TRUE);PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
----------------------------------------------------------------------------------------
Index EYGLE.IND_ID in tablespace USERS points to table EYGLE.EYGLE in tablespace EYGLE
但是可以对多个表空间同时传输，则一些自包含问题就可以得到解决：
SQL> exec dbms_tts.transport_set_check(‘USERS,EYGLE‘, TRUE, True);PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
表空间自包含确认之后，进行表空间传输就很方便了，一般包含如下几个步骤。
（1） 将表空间设置为只读：
alter tablespace users read only;
（2） 导出表空间。在操作系统提示符下执行：
exp username/passwd tablespaces=users transport_tablespace=y file=exp_users.dmp
此处的导出文件只包含元数据，所以导出文件很小，导出速度也会很快。
（3） 转移。将导出的元数据文件（此处是exp_users.dmp）和传输表空间的数据文件（此处是users表空间的数据文件user01.dbf）转移至目标主机（转移过程如果使用FTP方式，应该注意使用二进制方式）。
（4） 传输。在目标数据库将表空间插入到数据库中，完成表空间传输。在操作系统命令提示符下执行下面的语句：
imp username/passwd tablespaces=users transport_tablespace=y file=exp_users.dmp datafiles=‘users01.dbf‘
了解了Oracle的可传输表空间技术后，来看一下example表空间的插入，以下脚本仍然来自mkplug.sql脚本：
---- Importing the metadata and plugging in the tablespace at the same
-- time, using the restored database file
--
DEFINE imp_logfile = &log_path.tts_example_imp.log
-- When importing use filename got after restore is finished
host imp “‘sys/&&password_sys AS SYSDBA‘” transport_tablespace=y file=&imp_file log=&imp_logfile datafiles=‘&datafile‘ tablespaces=EXAMPLE tts_owners=hr,oe,pm,ix,sh
完成plugging之后，这个表空间就被包含在了新建的数据库之中。
篇3：手工创建数据库和表空间
--创建临时表空间
create temporary tablespace publish_temp01
tempfile 'e:/oradate/publish_temp01.dbf'
size 32m
autoextend on
next 32m maxsize 1024m
extent management local;
--创建表空间
create tablespace publish_data01
logging
datafile 'e:/oradate/publish_data01.dbf'
size 32m
autoextend on
next 32m maxsize 1024m
extent management local;
--创建用户指定表空间
create user brand identified by brand
default tablespace publish_data01
temporary tablespace publish_temp01;
--给用户授权
grant connect,resource to brand;
篇4：表空间数据文件详解（一）
一．表空间的创建
创建表空间：
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
篇5：谈谈Oracle undo表空间
谈谈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: 0x02000010 (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去查找相应的信息,这样一来,就把所有的知识都联系起来了.
篇6：Oracle删除用户和表空间
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;
★ 基于XMLSchema的地理信息元数据模式及存储映射研究
★ InnoDBInsert（插入）操作（下）mysql技术内幕
★ php 面试题
【innodb共享表空间vs独立表空间（精选6篇）】相关文章：
MySQL文件系统先睹为快数据库2022-12-06
Oracle深入学习2023-03-24
如何保护MySQL 中的重要数据2023-05-25
php笔试题附带详细答案2023-01-07
降成本增效益具体措施2023-07-20
关于MSSQL SERVER 中数据乱码的问题数据库教程2022-09-20
了解一下NULLs怎样影响IN和EXISTS数据库教程2023-09-10
软件实习总结心得体会2023-03-20
推荐没有虚拟主机的小巧的Mysql数据库备份脚本（PHP）2022-04-30
linux关闭mysql strict mode的方法介绍linux操作系统2023-05-04