umount误操作引发数据库宕机

在开发数据库上,执行完其它测试工作后,随手执行命令卸载光盘,图省事,执行了下述命令:

[root@OEL511gR2 ~]# umount -all

然后,然后,就导致了一则不大不小的数据库宕机!!!
因为,我的开发库文件系统信息如下:

[root@OEL511gR2 ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/sda1     ext3    9.7G  7.9G  1.3G  87% /
tmpfs        tmpfs    502M     0  502M   0% /dev/shm
/dev/sdb1     ext4     67G   57G  6.5G  90% /u02/rimis_data
172.16.1.100:/backup
               nfs    466G  105G  361G  23% /backup
[root@OEL511gR2 ~]#

而数据库的数据文件信息如下:

[oracle@OEL511gR2 ~]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 24 17:09:59 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/RIMISDB/system01.dbf
/u01/app/oradata/RIMISDB/undotbs01.dbf
/u01/app/oradata/RIMISDB/sysaux01.dbf
/u01/app/oradata/RIMISDB/users01.dbf
/u02/rimis_data/css_ott.dbf
/u02/rimis_data/css_ltt_bk.dbf
/u02/rimis_data/css_lti_bl.dbf
/u02/rimis_data/css_oti.dbf
/u02/rimis_data/css_ltt_rp.dbf
/u02/rimis_data/css_cdi.dbf
/u02/rimis_data/css_lti_ob.dbf
NAME
--------------------------------------------------------------------------------
/u02/rimis_data/css_ltt_eh.dbf
/u02/rimis_data/css_blt.dbf
/u02/rimis_data/css_ltt_bl.dbf
/u02/rimis_data/css_eci.dbf
/u02/rimis_data/css_ect.dbf
/u02/rimis_data/css_lti_ec.dbf
/u02/rimis_data/css_lti.dbf
/u02/rimis_data/css_ltt_gw.dbf
/u02/rimis_data/css_ltt.dbf
/u02/rimis_data/css_ltt_tr.dbf
/u02/rimis_data/css_lti_rp.dbf
NAME
--------------------------------------------------------------------------------
/u02/rimis_data/css_lti_bk.dbf
/u02/rimis_data/css_gwi.dbf
/u02/rimis_data/css_bli.dbf
/u02/rimis_data/css_lti_tr.dbf
/u02/rimis_data/css_ltt_ec.dbf
/u02/rimis_data/css_gwt.dbf
/u02/rimis_data/css_lti_gw.dbf
/u02/rimis_data/css_cdt.dbf
/u02/rimis_data/css_ltt_ob.dbf
/u02/rimis_data/css_lti_eh.dbf
32 rows selected.
SQL>

接着,邮箱就瞬间收到了来自开发项目组发出的数据库不可用的邮件!
最后,重新挂载磁盘,重启数据库。
一个小插曲,启完数据库后,忘记了启监听,就电话通知开发部门数据库可用。
又接着还是一通反应数据库不可用,罪过啊,手工启监听,手工注册服务!
写在这里,给自己提个醒,在服务器上操作时,记得千万千万要谨慎!!!好在,本次故障中,硬盘没损坏!不然就KO了!

如何从逻辑备份的dumpfile文件里获取头部详细信息?

在上一篇日志里,我们了解了 如何从逻辑备份的dumpfile文件里获取DDL脚本? 在本篇里,我们参照 MetaLink文档ID 462488.1】来了解如何从dumpfile里获取更为详细的头部信息。

所有的dumpfile文件都是包含大小为4KB的头部数据区,该区域记录dumpfile文件的详细信息。从Oracle 10.2.0.1.0开始,可以调用系统包DBMS_DATAPUMP.GET_DUMPFILE_INFO来获取dumpfile的头部信息。

1   参照 MetaLink文档ID 462488.1】我们可以创建一个SHOW_DUMPFILE_INFO存储过程,通过调用该过程可以获取dumpfile的详细信息,该过程的源代码如下:

CONNECT sys/manager as sysdba;

CREATE PROCEDURE show_dumpfile_info(
p_dir  VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')
AS
-- p_dir         = directory object where dumpfile can be found
-- p_file        = simple filename of export dumpfile (case-sensitive)
v_separator   VARCHAR2(80) := '--------------------------------------' ||
'--------------------------------------';
v_path        all_directories.directory_path%type := '?';
v_filetype    NUMBER;                 -- 0=unknown 1=expdp 2=exp
v_fileversion VARCHAR2(15);           -- 0.1=10gR1 1.1=10gR2 2.1=11g
v_info_table  sys.ku$_dumpfile_info;  -- PL/SQL table with file info
type valtype  IS VARRAY(22) OF VARCHAR2(2048);
var_values    valtype := valtype();
no_file_found EXCEPTION;
PRAGMA        exception_init(no_file_found, -39211);

BEGIN

-- Show generic info:
-- ==================

dbms_output.put_line(v_separator);
dbms_output.put_line('Purpose..: Obtain details about export ' ||
'dumpfile.        Version: 19-MAR-2008');
dbms_output.put_line('Required.: RDBMS version: 10.2.0.1.0 or higher');
dbms_output.put_line('.          ' ||
'Export dumpfile version: 7.3.4.0.0 or higher');
dbms_output.put_line('.          ' ||
'Export Data Pump dumpfile version: 10.1.0.1.0 or higher');
dbms_output.put_line('Usage....: ' ||
'execute show_dumfile_info(''DIRECTORY'', ''DUMPFILE'');');
dbms_output.put_line('Example..: ' ||
'exec show_dumfile_info(''MY_DIR'', ''expdp_s.dmp'')');
dbms_output.put_line(v_separator);
dbms_output.put_line('Filename.: ' || p_file);
dbms_output.put_line('Directory: ' || p_dir);

-- Retrieve Export dumpfile details:
-- =================================

SELECT directory_path INTO v_path FROM all_directories
WHERE directory_name = p_dir
OR directory_name = UPPER(p_dir);

dbms_datapump.get_dumpfile_info(
filename   => p_file,       directory => UPPER(p_dir),
info_table => v_info_table, filetype  => v_filetype);

var_values.EXTEND(22);
FOR i in 1 .. 22 LOOP
BEGIN
SELECT value INTO var_values(i) FROM TABLE(v_info_table)
WHERE item_code = i;
EXCEPTION WHEN OTHERS THEN var_values(i) := '';
END;
END LOOP;

-- Show dumpfile details:
-- ======================
-- For Oracle10g Release 2 and higher:
--    dbms_datapump.KU$_DFHDR_FILE_VERSION        CONSTANT NUMBER := 1;
--    dbms_datapump.KU$_DFHDR_MASTER_PRESENT      CONSTANT NUMBER := 2;
--    dbms_datapump.KU$_DFHDR_GUID                CONSTANT NUMBER := 3;
--    dbms_datapump.KU$_DFHDR_FILE_NUMBER         CONSTANT NUMBER := 4;
--    dbms_datapump.KU$_DFHDR_CHARSET_ID          CONSTANT NUMBER := 5;
--    dbms_datapump.KU$_DFHDR_CREATION_DATE       CONSTANT NUMBER := 6;
--    dbms_datapump.KU$_DFHDR_FLAGS               CONSTANT NUMBER := 7;
--    dbms_datapump.KU$_DFHDR_JOB_NAME            CONSTANT NUMBER := 8;
--    dbms_datapump.KU$_DFHDR_PLATFORM            CONSTANT NUMBER := 9;
--    dbms_datapump.KU$_DFHDR_INSTANCE            CONSTANT NUMBER := 10;
--    dbms_datapump.KU$_DFHDR_LANGUAGE            CONSTANT NUMBER := 11;
--    dbms_datapump.KU$_DFHDR_BLOCKSIZE           CONSTANT NUMBER := 12;
--    dbms_datapump.KU$_DFHDR_DIRPATH             CONSTANT NUMBER := 13;
--    dbms_datapump.KU$_DFHDR_METADATA_COMPRESSED CONSTANT NUMBER := 14;
--    dbms_datapump.KU$_DFHDR_DB_VERSION          CONSTANT NUMBER := 15;
-- For Oracle11gR1:
--    dbms_datapump.KU$_DFHDR_MASTER_PIECE_COUNT  CONSTANT NUMBER := 16;
--    dbms_datapump.KU$_DFHDR_MASTER_PIECE_NUMBER CONSTANT NUMBER := 17;
--    dbms_datapump.KU$_DFHDR_DATA_COMPRESSED     CONSTANT NUMBER := 18;
--    dbms_datapump.KU$_DFHDR_METADATA_ENCRYPTED  CONSTANT NUMBER := 19;
--    dbms_datapump.KU$_DFHDR_DATA_ENCRYPTED      CONSTANT NUMBER := 20;
-- For Oracle11gR2:
--    dbms_datapump.KU$_DFHDR_COLUMNS_ENCRYPTED   CONSTANT NUMBER := 21;
--    dbms_datapump.KU$_DFHDR_ENCPWD_MODE         CONSTANT NUMBER := 22;

-- For Oracle10gR2: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 15;
-- For Oracle11gR1: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 20;
-- For Oracle11gR2: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 22;

dbms_output.put_line('Disk Path: ' || v_path);

IF v_filetype = 1 OR v_filetype = 2 THEN
-- Get characterset name:
BEGIN
SELECT var_values(5) || ' (' || nls_charset_name(var_values(5)) ||
')' INTO var_values(5) FROM dual;
EXCEPTION WHEN OTHERS THEN null;
END;
IF v_filetype = 2 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Original Export dumpfile)');
dbms_output.put_line(v_separator);
SELECT DECODE(var_values(13), '0', '0 (Conventional Path)',
'1', '1 (Direct Path)', var_values(13))
INTO var_values(13) FROM dual;
dbms_output.put_line('...Characterset ID.: ' || var_values(5));
dbms_output.put_line('...Direct Path.....: ' || var_values(13));
dbms_output.put_line('...Export Version..: ' || var_values(15));
ELSIF v_filetype = 1 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
dbms_output.put_line(v_separator);
SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual;
SELECT DECODE(var_values(1),
'0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
'1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
'2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
'3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
var_values(1)) INTO var_values(1) FROM dual;
SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)',
var_values(2)) INTO var_values(2) FROM dual;
SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)',
var_values(14)) INTO var_values(14) FROM dual;
SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)',
var_values(18)) INTO var_values(18) FROM dual;
SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)',
var_values(19)) INTO var_values(19) FROM dual;
SELECT DECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)',
var_values(20)) INTO var_values(20) FROM dual;
SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)',
var_values(21)) INTO var_values(21) FROM dual;
SELECT DECODE(var_values(22),
'1', '1 (Unknown)',
'2', '2 (None)',
'3', '3 (Password)',
'4', '4 (Dual)',
'5', '5 (Transparent)',
var_values(22)) INTO var_values(22) FROM dual;

dbms_output.put_line('...File Version....: ' || var_values(1));
dbms_output.put_line('...Master Present..: ' || var_values(2));
dbms_output.put_line('...GUID............: ' || var_values(3));
dbms_output.put_line('...File Number.....: ' || var_values(4));
dbms_output.put_line('...Characterset ID.: ' || var_values(5));
dbms_output.put_line('...Creation Date...: ' || var_values(6));
dbms_output.put_line('...Flags...........: ' || var_values(7));
dbms_output.put_line('...Job Name........: ' || var_values(8));
dbms_output.put_line('...Platform........: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance........: ' || var_values(10));
END IF;
dbms_output.put_line('...Language........: ' || var_values(11));
dbms_output.put_line('...Block size......: ' || var_values(12));
dbms_output.put_line('...Metadata Compres: ' || var_values(14));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed.: ' || var_values(18));
dbms_output.put_line('...Metadata Encrypt: ' || var_values(19));
dbms_output.put_line('...Data Encrypted..: ' || var_values(20));
dbms_output.put_line('...Column Encrypted: ' || var_values(21));
dbms_output.put_line('...Encrypt.pwd. mod: ' || var_values(22));
IF v_fileversion = '2.1' or v_fileversion = '3.1' THEN
dbms_output.put_line('...Master Piece Cnt: ' || var_values(16));
dbms_output.put_line('...Master Piece Num: ' || var_values(17));
END IF;
END IF;
IF v_fileversion >= '1.1' THEN
dbms_output.put_line('...Job Version.....: ' || var_values(15));
END IF;
dbms_output.put_line('...Max Items Code..: ' ||
dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE);
END IF;
ELSE
dbms_output.put_line('Filetype.: ' || v_filetype);
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Not an export dumpfile.');
END IF;
dbms_output.put_line(v_separator);

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Disk Path: ?');
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Directory Object does not exist.');
dbms_output.put_line(v_separator);
WHEN no_file_found THEN
dbms_output.put_line('Disk Path: ' || v_path);
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: File does not exist.');
dbms_output.put_line(v_separator);
END;
/

2  接下来,我们还以上篇博文里提到的使用EXPDP导出的dumpfile为例,其具体调用方法如下:

SQL> show user;
USER is "SYS"
SQL> select * from dba_directories where directory_name like 'MIG%';
OWNE DIRECTORY_NAME  DIRECTORY_PATH
---- --------------- --------------------------------------------------
SYS  MIG_DIR         /u01/app/oracle/mig_dir
SQL> !ls -l /u01/app/oracle/mig_dir
total 24360
-rw-r----- 1 oracle asmadmin 24797184 Nov 21 17:19 expdp_hr.dmp
-rw-r--r-- 1 oracle asmadmin     3299 Nov 21 17:19 expdp_hr.log
-rw-r--r-- 1 oracle asmadmin   103892 Nov 21 17:23 hr_ddl.sql
-rw-r--r-- 1 oracle asmadmin     1672 Nov 21 17:23 impdp_hr.log
SQL> exec show_dumpfile_info(p_dir=>'mig_dir',p_file=>'expdp_hr.dmp');
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile.        Version: 19-MAR-2008
Required.: RDBMS version: 10.2.0.1.0 or higher
.          Export dumpfile version: 7.3.4.0.0 or higher
.          Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
----------------------------------------------------------------------------
Filename.: expdp_hr.dmp
Directory: mig_dir
Disk Path: /u01/app/oracle/mig_dir
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...File Version....: 3.1 (Oracle11g Release 2: 11.2.0.x)
...Master Present..: 1 (Yes)
...GUID............: B23C2582426C247EE04010ACBF00579A
...File Number.....: 1
...Characterset ID.: 873 (AL32UTF8)
...Creation Date...: Mon Nov 21 17:19:48 2011
...Flags...........: 2
...Job Name........: "HR"."SYS_EXPORT_SCHEMA_01"
...Platform........: x86_64/Linux 2.4.xx
...Instance........: rac1.localdomain:devdb1
...Language........: AL32UTF8
...Block size......: 4096
...Metadata Compres: 1 (Yes)
...Data Compressed.: 0 (No)
...Metadata Encrypt: 0 (No)
...Data Encrypted..: 0 (No)
...Column Encrypted: 0 (No)
...Encrypt.pwd. mod: 2 (None)
...Master Piece Cnt: 1
...Master Piece Num: 1
...Job Version.....: 11.02.00.00.00
...Max Items Code..: 22
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
SQL>

可以看到较为详细的信息,其中的Block size 4096表示dumpfile的header为4K。

3   如果,想把该过程的执行权限给普通用户:

GRANT execute ON show_dumpfile_info TO username;

4   如果,想要删除改过程:

DROP PROCEDURE show_dumpfile_info;

5  对于UNIX平台,我们还可以借助操作系统的命令来简单获取一些简单的信息:

rac1-> pwd
/home/oracle
rac1-> ls -l
total 23688
drwxrwxr-x 17 root   root         4096 May 26 21:20 12419321
-rw-r--r--  1 oracle oinstall    15626 Aug 31 14:28 db.rsp
-rw-r--r--  1 oracle oinstall 24166400 Nov 21 16:51 exp_hr.dmp
-rw-r--r--  1 oracle oinstall     2493 Nov 21 16:51 exp_hr.log
drwxr-x---  3 oracle oinstall     4096 Aug 31 15:03 oradiag_oracle
-rw-r--r--  1 oracle oinstall    28766 Nov 21 17:03 show_ddl.log
rac1-> cat exp_hr.dmp |head|strings
iEXPORT:V11.02.00
RUSERS
8192
Mon Nov 21 16:50:25 2011exp_hr.dmp
#G#G
rac1->

结合,上一篇,我们算是对如何从dumpfile里获取DDL脚本以及如何获取dumpfile的header deatail information有了一个初步认识。希望这两篇文章可以对网友在学习Oracle数据库逻辑备份带去帮助!

如何从逻辑备份的dumpfile文件里获取DDL脚本?

在数据库的管理工作中,难免会遇到使用Export/Import或者是EXPort Data Pump/IMPort Data Pump工具来执行逻辑备份、恢复的场景。在有些时候,我们可能会对产生的dumpfile的内容感兴趣。本文就这两种工具产生的逻辑备份文件分别描述如何获取数据定义语句(Data Definition Language)的脚本?

1 先来看,如何从传统的Export工具产生的dumpfile里获取DDL语句?

首先,导出HR用户的所有对象:

rac1-> id

uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)

rac1-> pwd

/home/oracle

rac1-> ls -l

total 24

drwxrwxr-x 17 root   root      4096 May 26 21:20 12419321

-rw-r--r--  1 oracle oinstall 15626 Aug 31 14:28 db.rsp

drwxr-x---  3 oracle oinstall  4096 Aug 31 15:03 oradiag_oracle

rac1-> exp hr/hr file=exp_hr.dmp log=exp_hr.log owner=hr

Export: Release 11.2.0.1.0 - Production on Mon Nov 21 16:50:22 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user HR

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user HR

About to export HR's objects ...

. exporting database links

. exporting sequence numbers

.....

.....

. exporting statistics

Export terminated successfully without warnings.

rac1-> ls -l

total 23656

drwxrwxr-x 17 root   root         4096 May 26 21:20 12419321

-rw-r--r--  1 oracle oinstall    15626 Aug 31 14:28 db.rsp

-rw-r--r--  1 oracle oinstall 24166400 Nov 21 16:51 exp_hr.dmp

-rw-r--r--  1 oracle oinstall     2493 Nov 21 16:51 exp_hr.log

drwxr-x---  3 oracle oinstall     4096 Aug 31 15:03 oradiag_oracle

rac1->

我们已经顺利导出了一份exp_hr.dmp的dumpfile,那么该如何从该文件里获取DDL语句呢?

我们可以利用传统的导入工具Import附带SHOW=y选项来获取:

rac1-> imp hr/hr file=exp_hr.dmp log=show_ddl.log  show=y

Import: Release 11.2.0.1.0 - Production on Mon Nov 21 17:03:26 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. importing HR's objects into HR

"BEGIN  "

...

...

Import terminated successfully without warnings.

rac1->

rac1-> ls -l

total 23688

drwxrwxr-x 17 root   root         4096 May 26 21:20 12419321

-rw-r--r--  1 oracle oinstall    15626 Aug 31 14:28 db.rsp

-rw-r--r--  1 oracle oinstall 24166400 Nov 21 16:51 exp_hr.dmp

-rw-r--r--  1 oracle oinstall     2493 Nov 21 16:51 exp_hr.log

drwxr-x---  3 oracle oinstall     4096 Aug 31 15:03 oradiag_oracle

-rw-r--r--  1 oracle oinstall    28766 Nov 21 17:03 show_ddl.log

rac1-> head -30 show_ddl.log

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. importing HR's objects into HR

"BEGIN  "

"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"

"CURRENT_SCHEMA'), export_db_name=>'DEVDB', inst_scn=>'41155490');"

"COMMIT; END;"

"CREATE TYPE "MY_TEST_TYPE" TIMESTAMP '2011-10-12:09:59:12' OID 'AF115DDE620"

"59E4AE04010ACBF000672'   as object(id number,name varchar2(10));"

"CREATE SEQUENCE "LOCATIONS_SEQ" MINVALUE 1 MAXVALUE 9900 INCREMENT BY 100 S"

"TART WITH 3300 NOCACHE NOORDER NOCYCLE"

"CREATE SEQUENCE "DEPARTMENTS_SEQ" MINVALUE 1 MAXVALUE 9990 INCREMENT BY 10 "

"START WITH 280 NOCACHE NOORDER NOCYCLE"

"CREATE SEQUENCE "EMPLOYEES_SEQ" MINVALUE 1 MAXVALUE 99999999999999999999999"

"99999 INCREMENT BY 1 START WITH 207 NOCACHE NOORDER NOCYCLE"

"CREATE CLUSTER "DEPT_EMP_CLUSTER" ("DEPARTMENT_ID" NUMBER(4, 0))  PCTFREE 1"

"0 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 262144 NEXT 1048576 MI"

"NEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "U"

"SERS""

"CREATE INDEX "IDX_DEPT_EMP_CLUSTER" ON CLUSTER "DEPT_EMP_CLUSTER" PCTFREE 1"

"0 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 F"

"REELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS""

"CREATE TABLE "BIGFILE_TAB" ("ID" NUMBER, "NAME" VARCHAR2(10))  PCTFREE 10 P"

"CTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXT"

"ENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "BIG_T"

rac1->

从上,我们可以看到,我们在IMPORT命令行上带上SHOW=Y的选项时,IMPORT工具并没有真正的执行数据导入,而是生成了一份含有DDL语句的日志文件,我们可以直接编辑该日志文件,以获取我们所需的脚本。

2 那么,对于EXPDP产生的dumpfile,又该如何获取DDL语句呢?

先以EXPDP导出dumpfile:

rac1-> pwd

/u01/app/oracle/mig_dir

rac1-> ls -l

total 0

rac1-> expdp hr/hr directory=mig_dir dumpfile=expdp_hr.dmp logfile=expdp_hr.log schemas=hr

Export: Release 11.2.0.1.0 - Production on Mon Nov 21 17:16:45 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

...

Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:19:48

rac1-> ls -l

total 24248

-rw-r----- 1 oracle asmadmin 24797184 Nov 21 17:19 expdp_hr.dmp

-rw-r--r-- 1 oracle asmadmin     3299 Nov 21 17:19 expdp_hr.log

rac1->

该如何从expdp_hr.dmp文件里获取DDL语句呢?IMPDP工具给我们提供了SQLFILE的命令行选项,同样是只获取DDL语句,并未真正的执行数据导入。

rac1-> impdp hr/hr directory=mig_dir dumpfile=expdp_hr.dmp logfile=impdp_hr.log schemas=hr sqlfile=hr_ddl.sql

Import: Release 11.2.0.1.0 - Production on Mon Nov 21 17:23:23 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "HR"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded

...

...

Job "HR"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 17:23:47

rac1-> ls -l

total 24360

-rw-r----- 1 oracle asmadmin 24797184 Nov 21 17:19 expdp_hr.dmp

-rw-r--r-- 1 oracle asmadmin     3299 Nov 21 17:19 expdp_hr.log

-rw-r--r-- 1 oracle asmadmin   103892 Nov 21 17:23 hr_ddl.sql

-rw-r--r-- 1 oracle asmadmin     1672 Nov 21 17:23 impdp_hr.log

rac1-> head -30 hr_ddl.sql

-- CONNECT HR

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'DEVDB', inst_scn=>'41161309');

COMMIT;

END;

/

-- new object type path: SCHEMA_EXPORT/TYPE/TYPE_SPEC

CREATE TYPE "HR"."MY_TEST_TYPE"

OID 'AF115DDE62059E4AE04010ACBF000672' as object(id number,name varchar2(10));

/

ALTER TYPE "HR"."MY_TEST_TYPE"

COMPILE SPECIFICATION

PLSQL_OPTIMIZE_LEVEL=  2

PLSQL_CODE_TYPE=  INTERPRETED

PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

/

-- new object type path: SCHEMA_EXPORT/SEQUENCE/SEQUENCE

CREATE SEQUENCE  "HR"."LOCATIONS_SEQ"  MINVALUE 1 MAXVALUE 9900 INCREMENT BY 100 START WITH 3300 NOCACHE  NOORDER  NOCYCLE ;

rac1->

最后,简单一句话总结:

对于使用传统工具EXPORT导出的dumpfile,我们可以使用IMPORT加上SHOW=Y的选项获取DDL

对于使用数据泵EXPDP导出的dumpfile,我们可以使用IMPDP加上SQLFILE的选项获取DDL

ORA-39142 IMPDP跨版本导入数据报错解决

前几天,公司某项目组搭建一套演示应用系统,需要将后台的Oracle数据从一台服务器迁移到另外一套系统。在执行IMPDP导入的过程遭遇下述错误,后经查找Metalink给出解决方法。

-bash-3.2$ impdp directory=mig_dir dumpfile=dms_expdp.dmp logfile=impdp.log schemas=dms
Import: Release 10.2.0.5.0 - 64bit Production on Thursday, 10 November, 2011 3:12:56
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Username: sys/oracle as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "/home/oracle/mig_dir/dms_expdp.dmp"
[oracle@OEL511gR2 mig_dir]$ oerr ora 39142
39142, 00000, "incompatible version number %s in dump file \"%s\""
// *Cause:  A dump file was specified for an import operation whose version
//          number is incompatible with the dump file version of the
//          Data Pump product currently running on the system. Usually this
//          message indicates that the dump file was produced by a newer
//          version of the Data Pump export utility.
// *Action: Import this dump file using the Data Pump import utility with
//          the same version as the export which created the file.
[oracle@OEL511gR2 mig_dir]$

ORA-39142的错误是指要导入的dump file中记录的数据库版本与要导入的数据库版本不一致。果不其然,这份导出的dump file是其他同事从一套11.1.0.6.0数据库上EXPDP 导出的,而这套要导入演示系统的数据库版本是10.2.0.5.0。将11g数据库中导出的dump file导入到10g数据库的过程中,就报这个错误了,Metalink ID 553337.1给出了详细的解决方法。

我这里的解决方法是在源库,即11g的数据库上重新以EXPDP加上version=10.2.0.5.0参数重新执行导出,然后在目标库上重新执行上述导入命令,OK!

需要注意的是,经过测试:直接在目标库(10.2.0.5.0)上以IMPDP附带version=11.1.0.6.0参数导入第一份dump file文件是行不通的,依旧会报上述ORA-39142的错!
那么,dump file里包含的数据库版本信息及其它详细信息应该怎么获取呢?我们将在下一篇介绍。

Oracle 数据逻辑恢复及权限控制一例

近日,应公司内部东南亚Regional Integrated Management Information System项目组需求,完成下述测试环境的搭建:

1 在现有开发库中,创建一个新的名为RIMISADMIN的schema,将现有RIMIS这个schema下的所有数据逻辑备份出来并导入到新建的RIMISADMIN这个schema下;

2 另外创建一个新的测试schema 名为RIMISTEST,该测试schema与RIMISADMIN公用一份数据,并限制其权限仅可以执行DML语句,对数据表有增删改查功能,不允许执行DDL语句,同时不允许对如下表执行DML语句操作:
cd_menu;cd_functionpoint;cd_menu_function;cd_office_fmenu;cd_role_fmenu;cd_office_biz_config;
cd_serial_no;cd_codedict;cd_codetype;edi_code_table;edi_code_type;pr_report_config;
上述内容是公司同事发给我的一封邮件,当然是我与需求同事沟通确认后,整理出来的需求信息。

我的解决思路:
1 在现有RAC数据库中将RIMIS schema用户下的所有数据用EXPDP按照schema模式全部逻辑导出;

2 准备使用IMPDP工具附带remap_schema=rimis:rimisadmin参数全部导入,在这一步执行之前确认RAC数据库的存储信息时,发现ASM磁盘组的总空间为236G,剩余空间只有区区的30G左右。而原有RIMIS schema下的所有数据在数据库中占用将近55G的空间,看来不能直接在这台RAC数据库上搭建这个测试环境,除非先给ASM磁盘组添加磁盘,而开发库又需要给各个项目组使用,不好直接停服务加磁盘,怕影响其他项目组进度。只好选择一种迂回的方案,先将测试环境搭建到另外一套数据库中,等有机会的话,再停RAC数据库,然后添加存储之后另作处理。

3 在新的测试环境上执行逻辑导入,导入之前,在新环境下创建必要的表空间,表空间名需要与原环境下RIMIS用户对象所在表空间名相同,否则,在导入时就得指定REMAP_TABLESPACE选项了,不然,就不可能顺利导入。具体的导入的命令如下:

impdp directory=mig_dir dumpfile=expdp_rimis_20111114.dmp logfile=impdp_rimis.log  remap_schema=rimis:rimisadmin exclude=table_statistics

4 导入之后,发现有些对象编译失效,图省事,执行下述命令直接编译RIMISADMIN schema下的所有对象:

SQL> exec dbms_utility.compile_schema('RIMISADMIN');

5 以SYS用户登录数据库,修改RIMISADMIN schema的口令,并确认RIMISADMIN 可以正常访问数据库;

[oracle@OEL511gR2 mig_dir]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Nov 16 16:10:42 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn / as sysdba
Connected.
SQL> alter user rimisadmin identified by fjxm;
User altered.
SQL> conn rimisadmin/fjxm;
Connected.
SQL>

6 新环境下创建RIMIS schema,原来需求是创建RIMISTEST,又经更改需求要命名为RIMIS,并只给RIMIS用户赋予最小CREATE SESSION的权限及CREATE SYNONYM(见第8步,具体用处)的权限:

SQL> conn / as sysdba
Connected.
SQL> create user rimis identified by rimis;
User created.
SQL> grant create session to rimis;
Grant succeeded.
SQL> conn rimis/rimis
Connected.
SQL>

7 给RIMIS用户赋予可以对RIMISADMIN对象的增删改查的权限,见下述SQL:

SQL> conn rimisadmin/fjxm
Connected.
SQL> spool /home/oracle/privs.sql
SQL> select 'grant select,insert,update,delete on '||table_name||' to rimis;' from user_tables;
SQL> spool off;

简单修改/home/oracle/privs.sql后,由RIMISADMIN用户执行/home/oracle/privs.sql赋权脚本。

8 RIMIS用户创建一系列的同义词:
RIMISADMIN先生成创建同义词的脚本:

SQL> conn rimisadmin/fjxm
Connected.
SQL> spool /home/oracle/create_synonym.sql
SQL> select 'create synonym '||table_name||' for rimisadmin.'||table_name||';' from user_tables;
SQL> spool off;

然后交由RIMIS用户执行上述创建同义词的脚本;
至此,RIMIS用户就可以正常访问RIMISADMIN用户下的对象。只是,还有一个特殊的上述那些表的权限控制,不能执行DML操作。

9 移除对特定表的增删改的权限:

SQL> conn rimisadmin/fjxm
Connected.
SQL> select 'revoke insert,update,delete on '||table_name||' from rimis;'
   2 from user_tables
   3 where lower(table_name) in ('cd_menu','cd_functionpoint','cd_menu_function',
   4 'cd_office_fmenu','cd_role_fmenu','cd_office_biz_config','cd_serial_no',
   5 'cd_codedict','cd_codetype','edi_code_table','edi_code_type','pr_report_config');

至此,完成RIMIS项目组的数据逻辑恢复、及相关权限的控制。以前,在数据库日常管理中,很少有遇到权限比较细化的场景,所以简单做一记录。
在发布本博文前,收到同事的邮件,说权限已经控制OK!

解析Oracle rowid系列三(完)

承接解析Oracle rowid系列一系列二。今天,我们来探讨特定场景下的Oracle rowid,大文件表空间下的Oracle rowid。大文件的表空间是Oracle 10g的新特性,关于该特性暂且不作过多表述,简单一句话来说,同小文件类型的表空间(数据库默认的表空间类型)相比,该类型的表空间只能包含一个而且最多只能有一个数据文件。正是因为如此,所以位于大文件类型表空间下的表的rowid显得有些特殊,接下来我们探讨究竟特殊在哪儿?

首先,准备场景,建立大文件表空间,并在该表空间下建立一张普通的Heap表。

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create bigfile tablespace big_tbs datafile size 10m autoextend on;
Tablespace created.
SQL> select d.file_id,d.tablespace_name,t.bigfile
2  from dba_data_files d,dba_tablespaces t
3  where d.tablespace_name=t.tablespace_name
4  order by 1
5  ;
FILE_ID   TABLESPACE_NAME        BIG
---------- ----------------- ---
1 SYSTEM                         NO
2 SYSAUX                         NO
3 UNDOTBS1                       NO
4 USERS                          NO
5 EXAMPLE                        NO
6 UNDOTBS2                       NO
7 TEST_TBS                       NO
8 TEST_TBS                       NO
9 BIG_TBS                        YES
9 rows selected.
SQL> alter user hr quota unlimited on big_tbs;
User altered.
SQL> conn hr/hr
Connected.
SQL> create table bigfile_tab(id number,name varchar2(10))
2  tablespace big_tbs
3  ;
Table created.
SQL> insert into bigfile_tab values(1,'oracle');
1 row created.
SQL> insert into bigfile_tab values(2,'oracle');
1 row created.
SQL> commit;
Commit complete.
SQL>

然后,我们来查询bigfile_tab表中的rowid:

SQL> select id,rowid from bigfile_tab;
ID ROWID
---------- ------------------
1 AAAUHfAAAAAAACGAAA
2 AAAUHfAAAAAAACGAAB
SQL> select rowid,
2  dbms_rowid.rowid_object(rowid) object_id,
3  dbms_rowid.rowid_relative_fno(rowid)  file_id,
4  dbms_rowid.rowid_block_number(rowid)  block_id ,
5  dbms_rowid.rowid_row_number(rowid)   num
6  from bigfile_tab
7  ;
ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM
------------------ ---------- ---------- ---------- ----------
AAAUHfAAAAAAACGAAA      82399          0        134          0
AAAUHfAAAAAAACGAAB      82399          0        134          1
SQL>

这时,我们发到貌似诡异的相对文件号竟然为0,本来我们的这张测试表位于bigfile_tbs表空间下,而bigfile_tbs表空间的file_id为9,我们从上述的第一次查询结果可以验证。可是为什么从rowid中查询的相对文件号为什么为0呢?

原来,对于大文件表空间下的rowid有如下特定的格式,这有别于小文件表空间下的rowid格式:

OOOOOOBBBBBBBBBRRR

即,6位的数据库对象号+9位的数据块号+3位的行号,同样也是以18位的64进制值来表示80位的二进制数。只不过,在这里少了相对文件号,其中6位的数据库对象号用32位的二进制数来存放(即一个数据库最多可以拥有232=4G个数据块对象),9位的数据块号同样用32位的二进制数来存放(即一个大文件表空间可以拥有232个数据块儿),最后3位的行号占用剩余的16位的二进制数,正好占满80位。

最后,我们就可以很容易理解为什么本实验中bigfile_tab表的相对文件号为0了?因为该表的rowid格式中根本就不存在相对文件号的信息,最本质的原因是大文件表空间下永远只能有且仅有1个数据文件,也就没有相对文件号的概念了

同时,我们也可以推算出为什么官方文档中说,对于大文件的表空间,如果数据块大小为32K的话,那么这个表空间的上限是128Tb?因为大文件表空间下最多可以有232个数据块,那么该表空间大小=232*32K=237K=227M=217G=27T=128T,答案也就在于此。

至此,关于Oracle rowid的探讨一、二、三系列结束。如果,大家对该系列有不同的理解,或认为本人理解有误的地方,还请不吝指正!!!

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
PL/SQL Release 11.2.0.1.0 – Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production
SQL> create bigfile tablespace big_tbs datafile size 10m autoextend on;
Tablespace created.
SQL> select d.file_id,d.tablespace_name,t.bigfile
2  from dba_data_files d,dba_tablespaces t
3  where d.tablespace_name=t.tablespace_name
4  order by 1
5  ;
FILE_ID TABLESPACE_NAME                BIG
———- —————————— —
1 SYSTEM                         NO
2 SYSAUX                         NO
3 UNDOTBS1                       NO
4 USERS                          NO
5 EXAMPLE                        NO
6 UNDOTBS2                       NO
7 TEST_TBS                       NO
8 TEST_TBS                       NO
9 BIG_TBS                        YES
9 rows selected.
SQL> alter user hr quota unlimited on big_tbs;
User altered.
SQL> conn hr/hr
Connected.
SQL> create table bigfile_tab(id number,name varchar2(10))
2  tablespace big_tbs
3  ;
Table created.
SQL> insert into bigfile_tab values(1,’oracle’);
1 row created.
SQL> insert into bigfile_tab values(2,’oracle’);
1 row created.
SQL> commit;
Commit complete.
SQL>

解析Oracle rowid系列二

解析Oracle rowid系列一里,我们简单探讨了Oracle rowid的格式及获取rowid的方法,在本篇中我们探讨的主题是:通过Oracle rowid真的可以唯一定位到表的一条记录吗?或者说在同一Oracle数据库中,rowid就一定唯一、没有重复的吗?
实验步骤:
1 构建cluster table并插入测试数据:

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> conn hr/hr;
Connected.
SQL> create cluster dept_emp_cluster(department_id number(4));
Cluster created.
SQL> create index idx_dept_emp_cluster on cluster dept_emp_cluster;
Index created.
SQL> create table dept cluster dept_emp_cluster(department_id)
2  as select * from departments;
Table created.
SQL> create table emp cluster dept_emp_cluster(department_id)
2  as select * from employees;
Table created.
SQL> select count(*) from dept;
COUNT(*)
----------
27
SQL> select count(*) from emp;
COUNT(*)
----------
107

2 接下来我们分别查询emp、dept表中department_id=10的rowid:

SQL> select department_id,rowid from dept where department_id=10;
DEPARTMENT_ID ROWID
------------- ------------------
           10 AAAUB3AAEAAAAK3AAA
SQL> select department_id,rowid from emp where department_id=10;
DEPARTMENT_ID ROWID
------------- ------------------
           10 AAAUB3AAEAAAAK3AAA
SQL> select department_id,rowid,
  2  dbms_rowid.rowid_object(rowid) object_id,
  3  dbms_rowid.rowid_relative_fno(rowid)  file_id,
  4  dbms_rowid.rowid_block_number(rowid)  block_id,
  5  dbms_rowid.rowid_row_number(rowid)   num
  6  from dept where department_id=10;
DEPARTMENT_ID ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM
------------- ------------------ ---------- ---------- ---------- ----------
           10 AAAUB3AAEAAAAK3AAA      82039          4        695          0
SQL> select department_id,rowid,
  2  dbms_rowid.rowid_object(rowid) object_id,
  3  dbms_rowid.rowid_relative_fno(rowid)  file_id,
  4  dbms_rowid.rowid_block_number(rowid)  block_id,
  5  dbms_rowid.rowid_row_number(rowid)   num
  6  from emp where department_id=10;
DEPARTMENT_ID ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM
------------- ------------------ ---------- ---------- ---------- ----------
           10 AAAUB3AAEAAAAK3AAA      82039          4        695          0
SQL>

从上,我们看到对于dept、emp这两张不同的表,对于department_id=10的两条记录的rowid完全一样,均为AAAUB3AAEAAAAK3AAA
其中AAAUB3为数据库对象号,即转成10进制后位82309;数据文件号为AAE=4,位于第4号数据文件上;位于第4号文件的第AAAAK3=695个数据块上。
为什么会这样呢?不是说,rowid是唯一的吗,通过rowid可以唯一定位表里的一条记录吗?可是,现在却有两张完全不同的表中的rowid竟然完全重复?
3 原来,我们这个场景比较特殊,对象号为82309的对象是一个聚簇表,而dept,emp是位于该cluster下的。那么,emp、dept表中拥有完全重复的rowid也就不足为奇了。因为,这本身就是聚簇表的特征。Oracle的Cluster Table就是要将不同表中的数据放在同一个数据块中存放。关于Cluster Table我们将在后续探讨。

SQL> select object_name,object_id,object_type
  2  from user_objects where object_id='82039';
OBJECT_NAME           OBJECT_ID OBJECT_TYPE
-------------------- ---------- -------------------
DEPT_EMP_CLUSTER          82039 CLUSTER
SQL> select table_name,tablespace_name,cluster_name from user_tables
  2  where table_name in ('DEPT','EMP');
TABLE_NAME               TABLESPACE_NAME         CLUSTER_NAME
------------------------ ----------------------- --------------------
DEPT                     USERS                   DEPT_EMP_CLUSTER
EMP                      USERS                   DEPT_EMP_CLUSTER
SQL>

4 结论:对于普通的Heap Table,我们说rowid的确可以精确定位到表里的记录,并且任何两张表中得rowid绝对不会重复。而对于Cluster Table就不一样了,完全有可能会出现rowid重复的情况。

解析Oracle rowid系列一

搞Oracle的人,都知道表里有一个叫做rowid的伪列,也知道可以通过rowid来快速定位表里的记录,至少通过oracle的索引来快速查找表里的数据就是通过rowid来定位的。可是,我们真的对rowid很了解吗?至少,我之前存在了一些误区,甚至有点儿荒谬!在此,对rowid作一个全面解析,同大家分享,以飨网友。

1 Oracle rowid格式:

Oracle rowid format

rowid

OOOOOO

FFF

BBBBBB

RRR

说明

数据对象号

相对文件号

数据块号

行号

上述表格是Oracle 9i及以上版本数据库中的rowid格式:6位对象号+3位相对文件号+6位数据块号+3位行号,是一个18位的64进制值。这个18位的64进制值在数据库内却是以10个bytes合计80个bit的二进制数存放的,和我们直接看到的结果有所区别。这里的64进制和10进制的对应编码如下表:

64进制-10进制转换表

64进制编码

A

B

Z

a

b

z

0

1

9

+

/

10进制值

0

1

25

26

27

51

52

53

61

62

63

2 那么这个18位的64进制值又是如何同80位的二进制数对应的呢?

其中,6位的对象号在数据库中是用32位二进制来存放的,也就意味着一个oracle数据库中最多可以有232个对象,即4G个对象;3位的相对文件号在数据库中是用10位二进制来存放的,也就意味着一个表空间中最多可以容纳210=1024去掉全0和全1个数据文件,即1022个数据文件;6位的数据块号用22位二进制来存放的,也就意味着一个数据文件最多可以包含222=4M个数据块;3位的行号在数据库中是用16位二进制来存放的,也就意味着一个数据块上最多可以容纳216=65536行记录。

3 我们已经对rowid有了基本认识,我们又该如何获取rowid呢?见下例

SQL> show user;
USER is "HR"
SQL> select employee_id,last_name,rowid from employees where employee_id=100;

EMPLOYEE_ID LAST_NAME                 ROWID
----------- ------------------------- ------------------
100 King                      AAAR5pAAFAAAADPAAA

SQL>

我们可以在查询列表中,把rowid伪列当做普通的字段来查询。上例中,AAAR5p就是HR.employees在数据库中的对象号,AAF表示相对文件号,AAAADP则表示数据块编号,最后的3个AAA则表示行号。当然,我们完全可以根据上述的转换表,将这些64进制值直接转成对应的10进制值。分别就是HR.employees表在数据库内的对象号是73321,而该表在数据库内部是存放在第5号文件(AAF=0*642+0*641+5*640=5)上的,employee_id=100的记录则是放在第5号文件的第207个数据块上的;最后的AAA表示该记录是位于第5号文件上的第207个数据块上的第1条记录。

4 除了上述方法,我们还可以通过数据库提供给我们的工具包来获取该信息:

SQL> select rowid,
2  dbms_rowid.rowid_object(rowid) object_id,
3  dbms_rowid.rowid_relative_fno(rowid)  file_id,
4  dbms_rowid.rowid_block_number(rowid)  block_id ,
5  dbms_rowid.rowid_row_number(rowid)   num ,
6  rowidtochar(rowid) from employees where employee_id=100
7  ;

ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM ROWIDTOCHAR(ROWID)
------------------ ---------- ---------- ---------- ---------- ------------------
AAAR5pAAFAAAADPAAA      73321          5        207          0 AAAR5pAAFAAAADPAAA

SQL>

很显然,我们通过调用系统的dbms_rowid包,获得的rowid信息同我们手工将64进制转成10进制得到的结果完全一致!

这是探讨Oracle rowid系列之一,后续将有系列二探讨Cluster table rowid、系列三探讨Bigfile tablespace rowid,敬请关注。

游泳卡免费赠送

近日来,厦门的天气不太正常,突然有点儿夏天的感觉,就想起之前在厦门佳健游泳馆办的那张会员卡,一再申请LP前去游泳未果,只好屁颠屁颠儿的一个人在上个周末游了两次,感觉挺舒服,缓解了疲劳,活动了颈椎。

只是,那张游泳卡是2011年11月30日就到期了,截止目前还有14次未用,估计自己是消费不完了。在此,向各位互联网的朋友们免费赠送出去,如果有需要的,可以及时回复我,或转至About页面获取我的联系方式。

友情提醒读者:

1 工作之余,加强锻炼身体,尤其是IT同行,长期坐立工作,易患职业病;

2 季节变换,天气变化,尤其是厦门这几天的温度变化明显,注意保暖;

3 如需要免费游泳卡的请从速,这个月30号就到期了,或者友情转发本文;

4 直接联系我就成,别不好意思,我很爽快的。

Oracle 11g Concepts 笔记3:什么是B树索引及其介绍

上一篇笔记里我们简单了解了Oracle索引的分类及唯一索引的特征,在本篇里我们简单了解什么是B-Tree Indexes及对其作一基本介绍。

B-trees, short for balanced trees, are the most common type of database index.顾名思义,B-Tree是一个平衡树的结构注意这里的B表示Balanced平衡的意思,而不是Binary二叉】,B树索引也是Oracle里最为常见的索引类型。B树索引里的数据是已经按照键字或者是被索引字段事先排好序存放的,默认是升序存放,也解释了为什么我们在创建索引的时候有可能会用到数据库的临时表空间的临时段。下面,看一幅B树索引的内部结构图:

b-tree-index

对于这幅B树存储结构图作以下几点介绍:

1 索引高度是指从根块到达叶子块时所遍历的数据块的个数,而索引层次=索引高度-1;本图中的索引的高度是3,索引层次等于2;通常,索引的高度是2或者3,即使表中有上百万条记录,也就意味着,从索引中定位一个键字只需要2或3次I/O,索引越高,性能越差;

2 B树索引包含两种数据块儿:分枝块(Branch Block)和叶子块(Leaf Block);

3 分枝块里存放指向下级分枝块(索引高度大于2,即有超过两层分枝块的情况)或者直接指向叶子块的指针(索引高度等于2,即层次为1的索引);

4 叶子块,就是位于B树结构里最底层的数据块。叶子块里存放的是索引条目,即索引关键字和rowid,rowid用来精确定位表里的记录;索引条目都是按照索引关键字+rowid已经排好序存放的;同一个叶子块里的索引条目同时又和左右兄弟条目形成链表,并且是一个双向链表;

5 B树索引的所有叶子块一定位于同一层上,这是由B树的数据结构定义的。因此,从根块到达任何一个叶子块的遍历代价都是相同的;