【原创】10g Trabsport_Tablespace 实验 （一）

做了个迁移表空间的实验，过程如下：

Source DB: 10.2.0.1.0/Linux -32

Target DB: 10.2.0.2.0/Windows -32

[@more@]

SQL> select * from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
/oradata/test/users01.dbf 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 5177344 632 ONLINE
/oradata/test/sysaux01.dbf 3 SYSAUX 251658240 30720 AVAILABLE 3 YES 3435972198 4194302 1280 251592704 30712 ONLINE
/oradata/test/undotbs01.dbf 2 UNDOTBS1 26214400 3200 AVAILABLE 2 YES 3435972198 4194302 640 26148864 3192 ONLINE
/oradata/test/system01.dbf 1 SYSTEM 503316480 61440 AVAILABLE 1 YES 3435972198 4194302 1280 503250944 61432 SYSTEM

新建测试用的表空间 tb1：

SQL> create tablespace tb1 datafile '/oradata/test/tb1_01.dbf' size 50M autoextend off;

Tablespace created

新建测试用的索引表空间Index1：

SQL> create tablespace index1 datafile '/oradata/test/index1_01.dbf' size 50M autoextend off;

Tablespace created

SQL>
SQL>

新建测试帐户：
SQL>
SQL> create user ct identified by "test" default tablespace tb1 quota unlimited on tb1;

User created

SQL> alter user ct quota unlimited on index1;

User altered

SQL> grant resource,connect to ct;

Grant succeeded

用测试帐户登陆：

SQL> conn ct/test@test
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ct

SQL> create table table1 ( a int,b int );

Table created

增加主健约束：

SQL> alter table table1 add primary key(a);

Table altered

SQL> select * from user_objects;

OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
SYS_C005143 51354 51354 INDEX 2007-5-2 1: 2007-5-2 1:35 2007-05-02:01:35:26 VALID N Y N
TABLE1 51353 51353 TABLE 2007-5-2 1: 2007-5-2 1:35 2007-05-02:01:34:28 VALID N N N

SQL> select * from user_indexes;

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---------- ----------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- ------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- ------------- ---------------------------------------- ---------------------------------------- ----------- --------- --------- --------- ----------- ---------- --------------- ----------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------ ------------- --------------- -------------- ---------- ----------------------- -------
SYS_C005143 NORMAL CT TABLE1 TABLE UNIQUE DISABLED TB1 2 255 65536 1 2147483645 10 YES VALID 1 1 NO N Y N DEFAULT NO NO NO NO NO

在Index1表空间上重建索引：

SQL> alter index SYS_C005143 rebuild tablespace index1 online;

Index altered

SQL> select * from user_objects;

OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
SYS_C005143 51354 51355 INDEX 2007-5-2 1: 2007-5-2 1:36 2007-05-02:01:35:26 VALID N Y N
TABLE1 51353 51353 TABLE 2007-5-2 1: 2007-5-2 1:36 2007-05-02:01:34:28 VALID N N N

SQL> select * from user_indexes;

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---------- ----------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- ------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- ------------- ---------------------------------------- ---------------------------------------- ----------- --------- --------- --------- ----------- ---------- --------------- ----------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------ ------------- --------------- -------------- ---------- ----------------------- -------
SYS_C005143 NORMAL CT TABLE1 TABLE UNIQUE DISABLED INDEX1 2 255 65536 1 2147483645 10 YES VALID 1 1 NO N Y N DEFAULT NO NO NO NO NO

插入数据：

SQL> insert into table1 values(0,0);

1 row inserted

SQL> insert into table1 values(1,1);

1 row inserted

SQL> insert into table1 values(2,2);

1 row inserted

SQL> insert into table1 values(3,3);

1 row inserted

SQL>
SQL>
SQL> commit;

Commit complete

用SYSDBA连接源数据库：

SQL> conn sys/admin@test as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL>

将tb1和index1表空间置为只读：
SQL> alter tablespace tb1 read only;

Tablespace altered

SQL> alter tablespace index1 read only;

Tablespace altered

SQL>
SQL>

新建directory：

SQL> create or replace directory dump_dir as '/oradata/test/dump';

Directory created

将directory的读写权限赋予public：

SQL> grant read on directory dump_dir to public;

Grant succeeded

SQL> grant write on directory dump_dir to public;

Grant succeeded

SQL>

待续

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7437037/viewspace-913117/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7437037/viewspace-913117/