RowMovement 练习

最新推荐文章于 2022-02-17 21:36:02 发布
最新推荐文章于 2022-02-17 21:36:02 发布
阅读量584 收藏
点赞数
分类专栏： Oracle 体系结构
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Tigers23/article/details/8151555
版权

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID                              
------------------------------ ------- ----------                              
DEPT                           TABLE                                           
EMP                            TABLE                                           
BONUS                          TABLE                                           
SALGRADE                       TABLE                                           
T5                             TABLE                                           
T6                             TABLE                                           
SYS_IOT_OVER_51451             TABLE                                           
SALES                          TABLE                                           
CLU1                           CLUSTER                                         
EMP_C1                         TABLE            1                              
DEPT_C1                        TABLE            2                              

TNAME                          TABTYPE  CLUSTERID                              
------------------------------ ------- ----------                              
T2                             TABLE                                           
T3                             TABLE                                           
T4                             TABLE                                           
CHAINED_ROWS                   TABLE                                           
T1                             TABLE                                           
T1_TEMP                        TABLE                                           
EMP1                           TABLE                                           

18 rows selected.

SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select * from emp where 0=9;

Table created.

SQL> alter table t1 enable rowmovement;
alter table t1 enable rowmovement
                      *
ERROR at line 1:
ORA-00905: missing keyword


SQL> alter table t1 enable row movement;

Table altered.

SQL> desc tabs
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 IOT_NAME                                           VARCHAR2(30)
 STATUS                                             VARCHAR2(8)
 PCT_FREE                                           NUMBER
 PCT_USED                                           NUMBER
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(3)
 BACKED_UP                                          VARCHAR2(1)
 NUM_ROWS                                           NUMBER
 BLOCKS                                             NUMBER
 EMPTY_BLOCKS                                       NUMBER
 AVG_SPACE                                          NUMBER
 CHAIN_CNT                                          NUMBER
 AVG_ROW_LEN                                        NUMBER
 AVG_SPACE_FREELIST_BLOCKS                          NUMBER
 NUM_FREELIST_BLOCKS                                NUMBER
 DEGREE                                             VARCHAR2(10)
 INSTANCES                                          VARCHAR2(10)
 CACHE                                              VARCHAR2(5)
 TABLE_LOCK                                         VARCHAR2(8)
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 PARTITIONED                                        VARCHAR2(3)
 IOT_TYPE                                           VARCHAR2(12)
 TEMPORARY                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NESTED                                             VARCHAR2(3)
 BUFFER_POOL                                        VARCHAR2(7)
 ROW_MOVEMENT                                       VARCHAR2(8)
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 SKIP_CORRUPT                                       VARCHAR2(8)
 MONITORING                                         VARCHAR2(3)
 CLUSTER_OWNER                                      VARCHAR2(30)
 DEPENDENCIES                                       VARCHAR2(8)
 COMPRESSION                                        VARCHAR2(8)
 DROPPED                                            VARCHAR2(3)

SQL> select table_name,ROW_MOVEMENT from tabs;

TABLE_NAME                     ROW_MOVE                                        
------------------------------ --------                                        
DEPT                           DISABLED                                        
EMP                            DISABLED                                        
BONUS                          DISABLED                                        
SALGRADE                       DISABLED                                        
T5                             DISABLED                                        
T6                             DISABLED                                        
SYS_IOT_OVER_51451             DISABLED                                        
DEPT_C1                        DISABLED                                        
EMP_C1                         DISABLED                                        
T2                             DISABLED                                        
T4                             DISABLED                                        

TABLE_NAME                     ROW_MOVE                                        
------------------------------ --------                                        
CHAINED_ROWS                   DISABLED                                        
T1_TEMP                        DISABLED                                        
EMP1                           ENABLED                                         
T1                             ENABLED                                         
SALES                          DISABLED                                        
T3                             DISABLED                                        

17 rows selected.

SQL> insert into t1 select * from emp;

14 rows created.

SQL> insert into t1 select * from t1;

14 rows created.

SQL> /

28 rows created.

SQL> /

56 rows created.

SQL> /

112 rows created.

SQL> /

224 rows created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_block_number(rowid) block#,count(*) from t1 group by dbms_rowid.rowid_block_number(rowid) order by 1;

    BLOCK#   COUNT(*)                                                          
---------- ----------                                                          
       116        170                                                          
       117        108                                                          
       120        170                                                          

SQL> alter table t1 shrink space;

Table altered.

SQL> select dbms_rowid.rowid_block_number(rowid) block#,count(*) from t1 group by dbms_rowid.rowid_block_number(rowid) order by 1;

    BLOCK#   COUNT(*)                                                          
---------- ----------                                                          
       116        170                                                          
       117        190                                                          
       118         88                                                          

SQL> alter table t1 shrink space;

Table altered.

SQL> select dbms_rowid.rowid_block_number(rowid) block#,count(*) from t1 group by dbms_rowid.rowid_block_number(rowid) order by 1;

    BLOCK#   COUNT(*)                                                          
---------- ----------                                                          
       116        170                                                          
       117        190                                                          
       118         88                                                          

SQL> delete t1 where empno=7900;

32 rows deleted.

SQL> select dbms_rowid.rowid_block_number(rowid) block#,count(*) from t1 group by dbms_rowid.rowid_block_number(rowid) order by 1;

    BLOCK#   COUNT(*)                                                          
---------- ----------                                                          
       116        158                                                          
       117        176                                                          
       118         82                                                          

SQL> alter table t1 shrink space;

Table altered.

SQL> select dbms_rowid.rowid_block_number(rowid) block#,count(*) from t1 group by dbms_rowid.rowid_block_number(rowid) order by 1;

    BLOCK#   COUNT(*)                                                          
---------- ----------                                                          
       116        158                                                          
       117        176                                                          
       118         82                                                          

SQL> delete t1 where empno=7902;

32 rows deleted.

SQL> alter table t1 shrink space;                -------压缩空间

Table altered.

SQL> select dbms_rowid.rowid_block_number(rowid) block#,count(*) from t1 group by dbms_rowid.rowid_block_number(rowid) order by 1;

    BLOCK#   COUNT(*)                                                          
---------- ----------                                                          
       116        146                                                          
       117        163                                                          
       118         75                                                          

SQL> delete t1 where empno=7839;

32 rows deleted.

SQL> select dbms_rowid.rowid_block_number(rowid) block#,count(*) from t1 group by dbms_rowid.rowid_block_number(rowid) order by 1;

    BLOCK#   COUNT(*)                                                          
---------- ----------                                                          
       116        134                                                          
       117        149                                                          
       118         69                                                          

SQL> alter table t1 shrink space;

Table altered.

SQL> select dbms_rowid.rowid_block_number(rowid) block#,count(*) from t1 group by dbms_rowid.rowid_block_number(rowid) order by 1;

    BLOCK#   COUNT(*)                                                          
---------- ----------                                                          
       116        187                                                          
       117        165                                                          

SQL> create index k1 on t1(empno);

Index created.

SQL> select inedx_name,status from user_indexes;
select inedx_name,status from user_indexes
       *
ERROR at line 1:
ORA-00904: "INEDX_NAME": invalid identifier


SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS                                          
------------------------------ --------                                        
PK_DEPT                        VALID                                           
PK_EMP                         VALID                                           
SALES_PK                       VALID                                           
IDX_CLU1                       VALID                                           
K1                             VALID                                           

SQL> alter table t1 move tablespace users;

Table altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS                                          
------------------------------ --------                                        
PK_DEPT                        VALID                                           
PK_EMP                         VALID                                           
SALES_PK                       VALID                                           
IDX_CLU1                       VALID                                           
K1                             UNUSABLE                                        

SQL> alter index k1 rebuild;                       -----重构索引

Index altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS                                          
------------------------------ --------                                        
PK_DEPT                        VALID                                           
PK_EMP                         VALID                                           
SALES_PK                       VALID                                           
IDX_CLU1                       VALID                                           
K1                             VALID                                           

SQL> alter table t1 shrinl space;
alter table t1 shrinl space
               *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


SQL> alter table t1 shrink space;

Table altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS                                          
------------------------------ --------                                        
PK_DEPT                        VALID                                           
PK_EMP                         VALID                                           
SALES_PK                       VALID                                           
IDX_CLU1                       VALID                                           
K1                             VALID                        

Tigers23
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle中flashback table功能为什么需要开启row movement功能
07-10 1226
前言： 对于row movement的说明，网上的一些资料： 1.在flashback中使用，当需要使用flashback table功能时，需要首先打开row mvoement的选项，否则使用该功能会报错。 2.Sh...
Row Movement in Oracle(oracle中根据枚举字段创建分区)
xiaolang85的专栏
07-16 2011
One of the relatively newer features in Oracle concerns the moving ofrows. Why would a row move and who or what controls that movement?Furthermore, by “move,” what exactly does move mean? Does a r
讲解Row Movement对系统的影响(二)
csdn198008的博客
12-02 176
【源于网络】当开启ROW MOVEMENT后，表被顺利的flashback了，数据被找回。此时，再比较flashback前后记录的ROWID，大多数记录的物理位置都变化。这个过程的内部操作， ...
ORA-01735 无效的 ALTER TABLE 选项
似水流年兮流年似水的博客
02-28 2万+
用Alert column 会报“无效的 ALTER TABLE 选项”这是因为列里有了值，需要先清除掉被修改的字段里的值。否则不能成功。会有不能修改，已经有值这样的提示。 正确的应该是用第二个语句去解决，尽量少用第一个语句去解决问题。 以下俩SQL，用于改列的大小类型： ALTER TABLE ECIFMGR.TEL_AUTOMATIC_CONFIG ALTER COLUMN CONFI...
ORA-01735: 无效的 ALTER TABLE 选项
Krismile_的博客
05-16 9322
当我修改一个列的数据类型时出现这个错误，下面是我出错时的 ALTER TABLE student ALTER COLUMN sage SMALLINT; ALTER COLUMN sage SMALLINT * 第 2 行出现错误: ORA-01735: 无效的 ALTER TABLE 选项 出这样的错是因为sage这一属性列里已经有值了，无法使用ALTER COLUMN，需要清除掉被修改...
100道Python编程练习题
热门推荐
jiafengfu的CSDN博客
01-31 4万+
100+ Python challenging programming exercise 100道Python编程练习题，这些题如果能坚持每天至少完成一道，一定可以帮大家轻松 get Python 的编程技能。目前，这个项目已经获得了 2924 Stars，2468 Forks。 首先，这 100+ 练习题根据难易程度分为三个等级：Level 1、2 和 3。下面对如何定义这三个 Level 进行...
数据分析基础练习
BoHeqixi的博客
02-17 97
111111111111：将数据集中所有信息异常的行删除。 比如上面的样例中第4行数据只有3个元素，而其他行都有6个元素，所以第4行是信息异常的行，将其删除。 数据集中可能还会存在一些其他异常。 将全部信息处理之后，每行的元素以逗号为分隔符，写入文件`test1` csv标准库中的writerow在写入文件时会加入’\r\n’作为换行符 当写文件时newline=None，csv先是将’a\r\nb\r\n’写入内存，再写入文件时，universal newlines mode工作，换行符’\n’..
个人练习代码其一
tangzhihui520的博客
04-16 328
# class InputOutString(object): # # def __init__(self): # # self.s = "" # # # # def getString(self): # # print('请输入字符串：') # # self.s = input() # # # # def printString(self): # # print(self.s.upper()) # # # # # #
python练习题
weixin_33976072的博客
09-20 1914
1.Leveldescription LevelDescription Level1BeginnermeanssomeonewhohasjustgonethroughanintroductoryPythoncourse.Hecansolvesomeproblemswith1or2Pythonclasses...
Row Movement
10-23
ROW MOVEMENT 是 Oracle 中的一项特性，最初是在 8i 时引入的，旨在提高分区表的灵活性。这一特性默认是关闭的，需要在特定的场景下打开，例如使用 Flashback Table、Shrink Segment 和更新 Partition Key 等功能。 ...
SQL经典练习题_sql练习_
10-03
6. 高级查询：如自连接、集合操作（UNION、INTERSECT、EXCEPT）和窗口函数（ROW_NUMBER、RANK、DENSE_RANK等）。 7. 触发器（TRIGGER）：在满足特定条件时自动执行的代码。 8. 存储过程（STORED PROCEDURE）和函数...
row guidance.pdf
06-17
A Stereovision-based Crop Row Detection Method for Tractor-automated Guidance
pk.rar_row
09-22
排源程序
row_number
08-15
row_number的使用
oracle口令管理
Tiger_23的专栏
11-14 3346
第一，口令的管理 默认情况下oracle10g和oracle11g的失败登陆次数为10次 我肯可以查看得出 select * from dba_profile whereresource_name='FAILED_LOGIN_ATTEMPTS' 这个值是可以改变的 alter profile default limitfailed_login_attempts unlimited;
Oracle同义词与DBlink
Tiger_23的专栏
11-06 1511
ORACLE同义词 同义词从字面理解就是别名的意思，和视图的功能类似，就是一种映射关系 第一，创建同义词 create or replace synonymtable_name for user.table_name   如果要创建远程数据库一张表的同义词，就需要先创建数据库连接来扩展访问然后创建同义词 create or replace synonym user1.table_
ORACLE字符集
Tiger_23的专栏
11-07 623
ＯＲＡＣＬＥ字符集 一，什么是oracle字符集 oracle字符集是一个字节数据的解释的符号集合，有大小之分，有相互的包容关系。 有三部分组成（语言，地域，字符集）每个成分控制了NLS子集的特性 Language：指定服务器消息的语言，territory：指定服务器的日期和数字格式 charset：指定字符集。如：AMERICAN_AMERICA.ZHS16GBK nls_lang的
ORACLE日志的状态
Tiger_23的专栏
09-07 617
ORACLE数据库日志的状态 我们通过查询v$log视图可可以看到日志的状态 SQL> select group#,ARCHIVED,status fromv$log;   GROUP#  ARC  STATUS ----------   ---   ---------------- 1                 NO CURRENT 2                 YES
enable row movement
最新发布
03-16
启用行移动（enable row movement）是Oracle数据库中的一个功能，它允许在表中移动行，即将一行数据从一个位置移动到另一个位置，而不需要删除和重新插入该行数据。启用行移动可以帮助数据库管理员更有效地管理表中...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值