月归档：九月 2011

object_id和data_object_id区别与联系

发表于 2011 年 9 月 12 日由惜分飞

其实object_id和data_object_id同样是表示数据库对象的一个唯一标志，但是object_id表示的是逻辑id，data_object_id表示的是物理id。如果一些object没有物理属性的话那它就不存在data_object_id，例如procedure,function,package,data type,db link,mv定义,view定义,临时表,分区表定义等等这些object都是没有对应着某个segment，因此它们的data_object_id都为空。

当表刚创建的时候它的object_id和data_object_id都是相等的，但是如果表经过move或truncate等，涉及到segment发生改变后data_object_id将会有变化。

DATA_OBJECT_ID was introduced in 8.0 to track versions of the same segment (certain operations change the version). It is used to discover stale ROWIDs and stale undo records.

SQL> create table xff as select * from dba_objects where rownum<100;
 
Table created
 
SQL> select object_id,data_object_id from user_objects where object_name='XFF';
 
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    211325         211325
 
SQL> alter table xff move;
 
Table altered
 
SQL> select object_id,data_object_id from user_objects where object_name='XFF';
 
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    211325         211326
 
SQL> truncate table xff;
 
Table truncated
 
SQL> create index ind_xff on xff(object_id);
 
Index created
 
SQL>  select object_id,data_object_id from user_objects where object_name='IND_XFF';
 
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    211328         211328
 
SQL> ALTER INDEX IND_XFF REBUILD;
 
Index altered
 
SQL>  select object_id,data_object_id from user_objects where object_name='IND_XFF';
 
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    211328         211329

发表在 Oracle | 评论关闭

Oracle 传输表空间

发表于 2011 年 9 月 11 日由惜分飞

0、检查平台信息
所有tts支持平台
SELECT * FROM V$TRANSPORTABLE_PLATFORM;

当前系统平台情况
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

一、源端操作
检查是否符合TTS要求
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘ODU’, TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> SELECT COUNT(*) FROM DBA_TABLES WHERE TABLESPACE_NAME=’ODU’;

COUNT(*)
———-
59

SQL> SELECT file_name from dba_data_files where tablespace_name=’ODU’;

FILE_NAME
————————————————–
/opt/oracle/oradata/chf/odu01.dbf
/opt/oracle/oradata/chf/odu02.dbf

需要传输表空间至于readonly模式
SQL> ALTER TABLESPACE ODU READ ONLY;

Tablespace altered.

导出表空间元数据
[oracle@node1 ~]$ exp userid=\’/ as sysdba\’ tablespaces=ODU file=/tmp/ODU.dmp transport_tablespace=y

Export: Release 10.2.0.4.0 – Production on Sun Sep 11 10:01:52 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata…
For tablespace ODU …
. exporting cluster definitions
. exporting table definitions
. . exporting table T_ODU_03
. . exporting table T_ODU_01
. . exporting table T_ODU
. . exporting table DB
. . exporting table NODE
. . exporting table CONF
. . exporting table DBINC
. . exporting table CKP
. . exporting table TS
. . exporting table TSATT
. . exporting table DF
. . exporting table DFATT
. . exporting table TF
. . exporting table TFATT
. . exporting table OFFR
. . exporting table RR
. . exporting table RT
. . exporting table ORL
. . exporting table RLH
. . exporting table AL
. . exporting table BS
. . exporting table BP
. . exporting table BCF
. . exporting table CCF
. . exporting table XCF
. . exporting table BSF
. . exporting table BDF
. . exporting table CDF
. . exporting table XDF
. . exporting table BRL
. . exporting table BCB
. . exporting table CCB
. . exporting table SCR
. . exporting table SCRL
. . exporting table CONFIG
. . exporting table XAL
. . exporting table RSR
. . exporting table FB
. . exporting table GRSP
. . exporting table ROUT
. . exporting table RCVER
. . exporting table F_DROP
. . exporting table T_QUERY
. . exporting table T_UNDO
. . exporting table A
. . exporting table T1
. . exporting table T2_1
. . exporting table T2
. . exporting table T_MV
. . exporting table TAB2
. . exporting table MLOG$_T_MV
. . exporting table T_N
. . exporting table T_M
. . exporting table MLOG$_T_N
. . exporting table T_1
. . exporting table T_2
. . exporting table T_3
. . exporting table T_4
. . exporting table T_5
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

SQL> alter tablespace odu read write;

Tablespace altered.

传输到目标段
[oracle@node1 ~]$ scp /opt/oracle/oradata/chf/odu0* 192.168.11.12:/opt/oracle/oradata/test
The authenticity of host ’192.168.11.12 (192.168.11.12)’ can’t be established.
RSA key fingerprint is db:3c:b4:34:7f:d7:e4:97:ab:b6:8b:b0:ab:22:43:35.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ’192.168.11.12′ (RSA) to the list of known hosts.
oracle@192.168.11.12′s password:
odu01.dbf 100% 100MB 3.3MB/s 00:30
odu02.dbf 100% 11GB 2.8MB/s 1:05:00
[oracle@node1 ~]$ scp /tmp/ODU.dmp 192.168.11.12:/tmp
oracle@192.168.11.12′s password:
Permission denied, please try again.
oracle@192.168.11.12′s password:
ODU.dmp 100% 456KB 456.0KB/s 00:00

二、目标端操作
导入元数据库
[oracle@ECP-UC-DB1 ~]$ imp userid=\’/ as sysdba\’ tablespaces=ODU file=/tmp/ODU.dmp transport_tablespace=y datafiles=/opt/oracle/oradata/test/odu01.dbf, /opt/oracle/oradata/test/odu02.dbf fromuser=chf touser=chf

Import: Release 10.2.0.4.0 – Production on Sun Sep 11 11:13:25 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata…
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF’s objects into CHF
. . importing table “T_ODU_03″
. . importing table “T_ODU_01″
. . importing table “T_ODU”
. . importing table “DB”
. . importing table “NODE”
. . importing table “CONF”
. . importing table “DBINC”
. . importing table “CKP”
. . importing table “TS”
. . importing table “TSATT”
. . importing table “DF”
. . importing table “DFATT”
. . importing table “TF”
. . importing table “TFATT”
. . importing table “OFFR”
. . importing table “RR”
. . importing table “RT”
. . importing table “ORL”
. . importing table “RLH”
. . importing table “AL”
. . importing table “BS”
. . importing table “BP”
. . importing table “BCF”
. . importing table “CCF”
. . importing table “XCF”
. . importing table “BSF”
. . importing table “BDF”
. . importing table “CDF”
. . importing table “XDF”
. . importing table “BRL”
. . importing table “BCB”
. . importing table “CCB”
. . importing table “SCR”
. . importing table “SCRL”
. . importing table “CONFIG”
. . importing table “XAL”
. . importing table “RSR”
. . importing table “FB”
. . importing table “GRSP”
. . importing table “ROUT”
. . importing table “RCVER”
. . importing table “F_DROP”
. . importing table “T_QUERY”
. . importing table “T_UNDO”
. . importing table “A”
. . importing table “T1″
. . importing table “T2_1″
. . importing table “T2″
. . importing table “T_MV”
. . importing table “TAB2″
. . importing table “MLOG$_T_MV”
. . importing table “T_N”
. . importing table “T_M”
. . importing table “MLOG$_T_N”
. . importing table “T_1″
. . importing table “T_2″
. . importing table “T_3″
. . importing table “T_4″
. . importing table “T_5″
About to enable constraints…
Import terminated successfully without warnings.

SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME STATUS
—————————— ———
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
XFF ONLINE
ODU READ ONLY

7 rows selected.

修改为readwrite模式（根据需求）
SQL> alter tablespace odu read write;

Tablespace altered.

SQL> SELECT COUNT(*) FROM DBA_TABLES WHERE TABLESPACE_NAME=’ODU’;

COUNT(*)
———-
59

三、相关说明
1、如果平台字节顺序不同，需要使用rman convert转换
2、导出导入元数据可以使用data pump实现
3、检查视图、触发器、包、过程、函数等对象，如果没有需要使用exp/imp row=n导入或者人工建立

发表在逻辑备份/恢复 | 评论关闭

Oracle DDL 并行(10G)

发表于 2011 年 9 月 9 日由惜分飞

1、CREATE INDEX 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR create index ind_t2 on t1(object_name) online nologging PARALLEL(degree 4);

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2130784087

--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT   |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IND_T2   |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE PARALLEL DDL;

Session altered.

SQL> EXPLAIN PLAN FOR create index ind_t2 on t1(object_name) online nologging PARALLEL(degree 4);

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2130784087

--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT   |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IND_T2   |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

2、REBUILD INDEX 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR alter  index ind_t2 rebuild online nologging PARALLEL(degree 4);

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2130784087

--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT    |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IND_T2   |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE PARALLEL DDL;

Session altered.

SQL> EXPLAIN PLAN FOR alter  index ind_t2 rebuild online nologging PARALLEL(degree 4);

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2130784087

--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT    |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IND_T2   |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

3、CREATE TABLE 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR create table t_6 parallel (degree 4)
  2    as select /*+ parallel (t1,4) */ * from t1 where rownum<5000000;

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2102891290

------------------------------------------------------------------------------
| Id  | Operation                    | Name     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT       |          |        |      |            |
|   1 |  PX COORDINATOR              |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ20001 |  Q2,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT            | T_6      |  Q2,01 | PCWP |            |
|   4 |     BUFFER SORT              |          |  Q2,01 | PCWC |            |
|   5 |      PX RECEIVE              |          |  Q2,01 | PCWP |            |
|   6 |       PX SEND ROUND-ROBIN    | :TQ20000 |        | S->P | RND-ROBIN  |
|   7 |        COUNT STOPKEY         |          |        |      |            |
|   8 |         PX COORDINATOR       |          |        |      |            |
|   9 |          PX SEND QC (RANDOM) | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|  10 |           COUNT STOPKEY      |          |  Q1,00 | PCWC |            |
|  11 |            PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|  12 |             TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------

SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE PARALLEL DDL;

Session altered.

SQL> EXPLAIN PLAN FOR create table t_6 parallel (degree 4)
  2    as select /*+ parallel (t1,4) */ * from t1 where rownum<5000000;

Explained.

SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2102891290

------------------------------------------------------------------------------
| Id  | Operation                    | Name     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT       |          |        |      |            |
|   1 |  PX COORDINATOR              |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ20001 |  Q2,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT            | T_6      |  Q2,01 | PCWP |            |
|   4 |     BUFFER SORT              |          |  Q2,01 | PCWC |            |
|   5 |      PX RECEIVE              |          |  Q2,01 | PCWP |            |
|   6 |       PX SEND ROUND-ROBIN    | :TQ20000 |        | S->P | RND-ROBIN  |
|   7 |        COUNT STOPKEY         |          |        |      |            |
|   8 |         PX COORDINATOR       |          |        |      |            |
|   9 |          PX SEND QC (RANDOM) | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|  10 |           COUNT STOPKEY      |          |  Q1,00 | PCWC |            |
|  11 |            PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|  12 |             TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------

4、说明
1）本次测试的数据库版本为10.2.0.4，Linux环境，其他版本可能有差异
2）关于INDEX的并行操作，并行度可能不会和指定相同（测试为2倍）
3）操作过程中，是否指定ddl 并发，效果相同。建议指定未佳：
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL DDL;
4）修改表结构操作，指定并发无效（待寻找方法）

发表在 Oracle性能优化 | 评论关闭

17813235971

QQ 咨询

月归档：九月 2011

object_id和data_object_id区别与联系

Oracle 传输表空间

Oracle DDL 并行(10G)

文章分类

最近发表

月归档：九月 2011

object_id和data_object_id区别与联系

Oracle 传输表空间

Oracle DDL 并行(10G)

标签云

文章分类

最近发表