Home » RDBMS Server » Server Administration » move table issue (11g windows)
| move table issue [message #520668] |
Tue, 23 August 2011 20:16  |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
Dear all,
I want to know whether the table has been moved after redefine using dbms_redefinition?
|
|
|
|
|
|
|
|
| Re: move table issue [message #520695 is a reply to message #520686] |
Wed, 24 August 2011 02:23  |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
hi,
How to know whether the table has been moved by DBA_SEGMENTS;
SQL> desc Dba_Segments;
Name Type Nullable Default Comments
---------------- ------------ -------- ------- -----------------------------------------------------
OWNER VARCHAR2(30) Y Username of the segment owner
SEGMENT_NAME VARCHAR2(81) Y Name, if any, of the segment
PARTITION_NAME VARCHAR2(30) Y Partition/Subpartition Name, if any, of the segment
SEGMENT_TYPE VARCHAR2(18) Y Type of segment: "TABLE", "CLUSTER", "INDEX", "ROLLBACK",
"DEFERRED ROLLBACK", "TEMPORARY","SPACE HEADER", "TYPE2 UNDO"
or "CACHE"
SEGMENT_SUBTYPE VARCHAR2(10) Y SubType of Lob segment: "SECUREFILE", "ASSM", "MSSM", NULL
TABLESPACE_NAME VARCHAR2(30) Y Name of the tablespace containing the segment
HEADER_FILE NUMBER Y ID of the file containing the segment header
HEADER_BLOCK NUMBER Y ID of the block containing the segment header
BYTES NUMBER Y Size, in bytes, of the segment
BLOCKS NUMBER Y Size, in Oracle blocks, of the segment
EXTENTS NUMBER Y Number of extents allocated to the segment
INITIAL_EXTENT NUMBER Y Size, in bytes, of the initial extent of the segment
NEXT_EXTENT NUMBER Y Size, in bytes, of the next extent to be allocated to the segment
MIN_EXTENTS NUMBER Y Minimum number of extents allowed in the segment
MAX_EXTENTS NUMBER Y Maximum number of extents allowed in the segment
MAX_SIZE NUMBER Y Maximum number of blocks allowed in the segment
RETENTION VARCHAR2(7) Y Retention option for SECUREFILE segment
MINRETENTION NUMBER Y Minimum Retention Duration for SECUREFILE segment
PCT_INCREASE NUMBER Y Percent by which to increase the size of the next extent to be allocated
FREELISTS NUMBER Y Number of process freelists allocated in this segment
FREELIST_GROUPS NUMBER Y Number of freelist groups allocated in this segment
RELATIVE_FNO NUMBER Y Relative number of the file containing the segment header
BUFFER_POOL VARCHAR2(7) Y The default buffer pool to be used for segments blocks
FLASH_CACHE VARCHAR2(7) Y
CELL_FLASH_CACHE VARCHAR2(7) Y
[Updated on: Sun, 28 August 2011 23:18] by Moderator Report message to a moderator |
|
|
|
| Re: move table issue [message #520696 is a reply to message #520695] |
Wed, 24 August 2011 02:28 |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
hi
Quote:It has been moved if you tell it to move the table.
How to tell it move the table using dbms_redefinition? I can not find any parameters.
|
|
|
|
|
|
|
|
|
|
| Re: move table issue [message #520716 is a reply to message #520708] |
Wed, 24 August 2011 04:05 |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
hi,
I try a test and found the rowid is changged by dbms_redefinition, changge from AAAYWgAAEAAA63bAAA to
AAAYWiAAEAAA63zAAA, I think the table is moved by dbms_redefinition,right?
SQL> create table TB_HXL_MOVE
2 (
3 ID NUMBER,
4 CNT NUMBER
5 )
6 ;
Table created.
SQL>
SQL> Insert Into TB_HXL_MOVE Values(1,20);
1 row created.
SQL> Commit;
Commit complete.
SQL> Select Rowid,a.* From TB_HXL_MOVE a;
ROWID ID CNT
------------------ ---------- ----------
AAAYWgAAEAAA63bAAA 1 20
SQL> create table TB_HXL_MOVE_mid
2 (
3 ID NUMBER,
4 CNT NUMBER
5 )
6 ;
Table created.
SQL> Begin
2 dbms_redefinition.can_redef_table(uname => 'HXL',
3 tname =>'TB_HXL_MOVE',
4 options_flag => dbms_redefinition.cons_use_rowid);
5 End;
6 /
PL/SQL procedure successfully completed.
SQL> Begin
2 dbms_redefinition.start_redef_table(uname =>'HXL' ,
3 orig_table => 'TB_HXL_MOVE',
4 int_table => 'TB_HXL_MOVE_MID',
5 options_flag => dbms_redefinition.cons_use_rowid
6 );
7 End;
8 /
PL/SQL procedure successfully completed.
SQL> Begin
2 dbms_redefinition.sync_interim_table(uname =>'HXL'
3 ,orig_table => 'TB_HXL_MOVE'
4 ,int_table => 'TB_HXL_MOVE_MID'
5 );
6 End;
7
8 /
PL/SQL procedure successfully completed.
SQL> Begin
2 dbms_redefinition.finish_redef_table(uname =>'HXL'
3 ,orig_table => 'TB_HXL_MOVE'
4 ,int_table => 'TB_HXL_MOVE_MID'
5 );
6 End;
7 /
PL/SQL procedure successfully completed.
SQL> Select Rowid,a.* From TB_HXL_MOVE a;
ROWID ID CNT
------------------ ---------- ----------
AAAYWiAAEAAA63zAAA 1 20
SQL> |
|
|
|
|
|
| Re: move table issue [message #521331 is a reply to message #520716] |
Sun, 28 August 2011 22:09 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
It doesn't "move" the table.
It copies the data (all the rows) to the interim target table ('TB_HXL_MOVE_MID' in your case). This is like normal DML that does an INSERT INTO TB_HXL_MOVE_MID SELECT * FROM TB_HXL_MOVE.
(although it isn't really a straight-forward insert. Oracle treats the interim table as a snapshot and does a merge insert and also maintains a snapshot log (MV Log) on the source table to trace all other DML that may still be occurring against the source while the Redef is in progress. At the end, it merges updates from the snapshot log into the interim table).
Since these are two distinct tables, there are separate segments.
You can verify this in DBA_SEGMENTS : HEADER_FILE, HEADER_BLOCK.
When you execute "finish_redef", Oracle *renames* the segments. Thus the physical segment that "belonged" to TB_HXL_MOVE now belongs to TB_HXL_MOVE_MID and vice-versa !
Here's a demo :
SQL> create table TB_REDEF_TST
2 (
3 ID NUMBER,
4 CNT NUMBER
5 )
6 tablespace users
7 /
Table created.
SQL>
SQL> Insert Into TB_REDEF_TST Values(1,20);
1 row created.
SQL>
SQL> Commit;
Commit complete.
SQL>
SQL> Select Rowid,a.* From TB_REDEF_TST a;
ROWID ID CNT
------------------ ---------- ----------
AAAMKuAAEAAACXMAAA 1 20
SQL>
SQL>
SQL> create table TB_REDEF_TST_TEMP
2 (
3 ID NUMBER,
4 CNT NUMBER
5 )
6 tablespace HEMANT
7 /
Table created.
SQL>
SQL>
SQL> -- This is what you should check
SQL> select segment_name, header_file, header_block
2 from dba_segments
3 where owner = 'HEMANT'
4 and segment_type = 'TABLE'
5 and segment_name in ('TB_REDEF_TST','TB_REDEF_TST_TEMP')
6 order by 1
7 /
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
TB_REDEF_TST 4 9675
TB_REDEF_TST_TEMP 2 11
SQL>
SQL>
SQL> Begin
2 dbms_redefinition.start_redef_table(uname =>'HEMANT' ,
3 orig_table => 'TB_REDEF_TST',
4 int_table => 'TB_REDEF_TST_TEMP',
5 options_flag => dbms_redefinition.cons_use_rowid
6 );
7 End;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> Begin
2 dbms_redefinition.sync_interim_table(uname =>'HEMANT',
3 orig_table => 'TB_REDEF_TST',
4 int_table => 'TB_REDEF_TST_TEMP'
5 );
6 End;
7 /
PL/SQL procedure successfully completed.
SQL> Begin
2 dbms_redefinition.finish_redef_table(uname =>'HEMANT',
3 orig_table => 'TB_REDEF_TST',
4 int_table => 'TB_REDEF_TST_TEMP'
5 );
6 End;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> Select Rowid,a.* From TB_REDEF_TST a;
ROWID ID CNT
------------------ ---------- ----------
AAAMKvAACAAAAAMAAA 1 20
SQL>
SQL> -- This is what you should check
SQL> select segment_name, header_file, header_block
2 from dba_segments
3 where owner = 'HEMANT'
4 and segment_type = 'TABLE'
5 and segment_name in ('TB_REDEF_TST','TB_REDEF_TST_TEMP')
6 order by 1
7 /
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
TB_REDEF_TST 2 11
TB_REDEF_TST_TEMP 4 9675
SQL>
I deliberately created the tables in two separate tablespaces which will mandate that the header_file is distinct !
If you put them in the same tablespace, the header_file may or may not be distinct (because a tablespace may have more than one datafile) but the combination of "HEADER_FILE + HEADER_BLOCK" will be distinct.
Hemant K Chitale
|
|
|
|
| Re: move table issue [message #521386 is a reply to message #521331] |
Mon, 29 August 2011 08:49 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:It doesn't "move" the table.
It copies the data (all the rows) to the interim target table
OK, so in the same way, "mv" does not move a file and "gzip" does not compress a file
but everyone knows what it means.
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Mon Jan 27 02:56:02 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
