这是一道作业问题的一部分,描述如下:在oracle中
建立一个tablespace,建立一个table,插入20行数据。
此时使用了block 12-18,每个block有3行数据(最后一个block18只有2行)
现在删除block 12 的数据,
即:当前使用block 13 - 18
此时要求,将block 18的数据全部移动到block 12去。应当如何完成?操作结束后应当是:
block 12中有2行数据,block 13-17中各有3行数据,block 18中没有数据。ps. 已经尝试过ALTER TABLE MOVE, ALTER TABLE SHRINK,均不符合要求。
尝试过ALTER TABLE DEALLOCATE,只有一次成功了,其余十多次都没有成功。

解决方案 »

  1.   

    楼主看一下这个吧,有点麻烦的:
    http://www.itpub.net/viewthread.php?tid=112239&extra=&page=1
      

  2.   

    我是楼主,补充这个问题:以下SQL是查看每一行数据在哪个block里面的。
    COLUMN OWNER  FORMAT A10 HEADING "Owner"
    COLUMN SEGMENT  FORMAT A12 HEADING "Segment|name"
    COLUMN FNO  FORMAT 999999 HEADING "File|number"
    COLUMN BNO  FORMAT 999999 Heading "Block|number"
    COLUMN RNO  FORMAT 999999 Heading "Row|number"
    ACCEPT SNAME CHAR PROMPT 'Table name>'
    ACCEPT ONAME CHAR PROMPT 'Owner name>'
    SELECT  UPPER('&ONAME') OWNER, 
    UPPER('&SNAME') SEGMENT,
            DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FNO,
            DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BNO,
            DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) RNO
    FROM &ONAME..&SNAME
    ORDER BY BNO;以下是预期结果:
               Segment         File   Block     Row                                                     
    Owner      name          number  number  number                                                     
    ---------- ------------ ------- ------- -------                                                     
    SYSTEM     T1                 9      12       0                                                     
    SYSTEM     T1                 9      12       1                                                   
    SYSTEM     T1                 9      13       0                                                     
    SYSTEM     T1                 9      13       1                                                     
    SYSTEM     T1                 9      13       2                                                     
    SYSTEM     T1                 9      14       0                                                     
    SYSTEM     T1                 9      14       1                                                     
    SYSTEM     T1                 9      14       2                                                     
    SYSTEM     T1                 9      15       0                                                     
    SYSTEM     T1                 9      15       2                                                     
    SYSTEM     T1                 9      15       1                                                     
    SYSTEM     T1                 9      16       0                                                     
    SYSTEM     T1                 9      16       1                                                     
    SYSTEM     T1                 9      16       2                                                     
    SYSTEM     T1                 9      17       0                                                     
    SYSTEM     T1                 9      17       1                                                     
    SYSTEM     T1                 9      17       2                                                          20 rows selected.二楼给的地址讲了一大堆理论,但是并没有实际解决问题。
    请给我一个直接的SQL。谢谢。