ALTER TABLE READ_ONLY 测试

-- READ ONLY

点击(此处)折叠或打开

1. ALTER TABLE schema.table READ ONLY;
   
2. ALTER TABLE schema.table READ WRITE;

-- 测试

点击(此处)折叠或打开

1. EODA@BLACKBOX> SELECT TABLE_NAME, READ_ONLY FROM USER_TABLES;
   
2.         
   
3. TABLE_NAME                     READ_ONLY
   
4. ------------------------------ ----------
   
5. RUN_STATS                      NO
   
6. SALGRADE                       NO
   
7. BONUS                          NO
   
8. DEPT                           NO
   
9. TEST                           NO
   
10. T3                             NO
    
11. T2                             NO
    
12. T1                             NO
    
13. BIG_TABLE                      NO
    
14. 
    
15.         
    
16. 10 rows selected.
    
17.         
    
18. EODA@BLACKBOX> ALTER TABLE EMP READ ONLY;
    
19.         
    
20. Table altered.
    
21.         
    
22. EODA@BLACKBOX> SELECT TABLE_NAME, READ_ONLY FROM USER_TABLES;
    
23.         
    
24. TABLE_NAME                              READ_ONLY
    
25. ------------------------------ ----------
    
26. RUN_STATS                      NO
    
27. SALGRADE                       NO
    
28. BONUS                          NO
    
29. DEPT                           NO
    
30. EMP                            YES
    
31. TEST                           NO
    
32. T3                             NO
    
33. T2                             NO
    
34. T1                             NO
    
35. BIG_TABLE                      NO
    
36.         
    
37. 10 rows selected.

-- 增删改操作

点击(此处)折叠或打开

1. EODA@BLACKBOX> UPDATE EMP SET SAL = SAL + 200 WHERE EMPNO = 7369;
   
2. UPDATE EMP SET SAL = SAL + 200 WHERE EMPNO = 7369
   
3. *
   
4. ERROR at line 1:
   
5. ORA-12081: update operation not allowed on table "EODA"."EMP"
   
6.         
   
7. EODA@BLACKBOX> INSERT INTO EMP SELECT * FROM EMP;
   
8. INSERT INTO EMP SELECT * FROM EMP
   
9. *
   
10. ERROR at line 1:
    
11. ORA-12081: update operation not allowed on table "EODA"."EMP"
    
12.         
    
13. EODA@BLACKBOX> DELETE EMP WHERE EMPNO = 7369;
    
14. DELETE EMP WHERE EMPNO = 7369
    
15. *
    
16. ERROR at line 1:
    
17. ORA-12081: update operation not allowed on table "EODA"."EMP"

-- 对表进行DDL操作
     插入一个 column

点击(此处)折叠或打开

1. EODA@BLACKBOX> ALTER TABLE EMP ADD ID NUMBER(10);
   
2. ALTER TABLE EMP ADD ID NUMBER(10)
   
3. *
   
4. ERROR at line 1:
   
5. ORA-12081: update operation not allowed on table "EODA"."EMP"

     修改 column 属性

点击(此处)折叠或打开

1. EODA@BLACKBOX> DESC EMP
   
2.  Name                                                  Null?    Type
   
3.  ----------------------------------------------------- -------- ------------------------------------
   
4.  EMPNO                                                          NUMBER(4)
   
5.  ENAME                                                          VARCHAR2(10)
   
6.  JOB                                                            VARCHAR2(9)
   
7.  MGR                                                            NUMBER(4)
   
8.  HIREDATE                                                       DATE
   
9.  SAL                                                            NUMBER(7,2)
   
10.  COMM                                                           NUMBER(7,2)
    
11.  DEPTNO                                                         NUMBER(2)
    
12. 
    
13. EODA@BLACKBOX> ALTER TABLE EMP MODIFY DEPTNO NUMBER(3);
    
14. ALTER TABLE EMP MODIFY DEPTNO NUMBER(3)
    
15. *
    
16. ERROR at line 1:
    
17. ORA-12081: update operation not allowed on table "EODA"."EMP"

-- 表删除动作

点击(此处)折叠或打开

1. EODA@BLACKBOX> drop table emp purge;
   
2.         
   
3. Table dropped.

-- 改回读写状态

点击(此处)折叠或打开

1. EODA@BLACKBOX> ALTER TABLE EMP READ WRITE;
   
2.         
   
3. Table altered.
   
4.         
   
5. EODA@BLACKBOX> UPDATE EMP SET SAL = SAL + 200 WHERE EMPNO = 7369;
   
6.         
   
7. 1 row updated.

/*
通过测试得出结论
当表的READ_ONLY字段值为YES时，表只可以查询，不可以增删改表中数据,不可以对表结构进行更改
可以直接对表删除
*/

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

转载于:http://blog.itpub.net/31369239/viewspace-2122185/