oracle 表空间和表 read only迁移后不再read only

DB :  11.2.0.3.0


1.将tablespace read only , 不允许再对表进行update、insert操作,测试dmp到另一个用户、表空间后是否可以update、insert 

2.将table read only, 不允许再对表进行update、insert操作, 测试dmp表到另一个用户、表空间后是否可以update、insert

1.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/yoon01.dbf
 
SQL> create tablespace yoon datafile '/u01/app/oracle/oradata/orcl/yoon01.dbf' size 100m;

Tablespace created.

SQL> create user yoon identified by yoon default tablespace yoon ;

User created.

SQL> grant dba to yoon;

Grant succeeded.

SQL>show user
USER is "YOON"

SQL> create table yoon as select * from scott.emp;

Table created.

SQL> alter tablespace yoon read only;

Tablespace altered.

更新：

 SQL> update yoon set empno=9999 where empno=7934;
update yoon set empno=9999 where empno=7934
       *
ERROR at line 1:
ORA-00372: file 5 cannot be modified at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/yoon01.dbf'

插入：
SQL> insert into yoon select * from scott.emp;
insert into yoon select * from scott.emp
            *
ERROR at line 1:
ORA-00372: file 5 cannot be modified at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/yoon01.dbf'

 备份:
[oracle@db01 backup]$ expdp system/admin directory=data dumpfile=yoon.dmp logfile=yoon.log tables=yoon.yoon

Export: Release 11.2.0.3.0 - Production on Fri Oct 31 21:53:46 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=data dumpfile=yoon.dmp logfile=yoon.log tables=yoon.yoon 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "YOON"."YOON"                               8.570 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/backup/yoon.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 21:54:02

[oracle@db01 backup]$ ls
yoon.dmp  yoon.log
 
导入至scott用户：
[oracle@db01 backup]$ impdp system/admin directory=data dumpfile=yoon.dmp logfile=yoon_imp.log  tables=yoon.yoon remap_schema=yoon:scott remap_tablespace=yoon:users 

Import: Release 11.2.0.3.0 - Production on Fri Oct 31 21:55:43 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=data dumpfile=yoon.dmp logfile=yoon_imp.log tables=yoon.yoon remap_schema=yoon:scott remap_tablespace=yoon:users 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."YOON"                              8.570 KB      14 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 21:55:50

查看scott用户下表的状态：
SQL> select owner,table_name,TABLESPACE_NAME,STATUS,TABLE_LOCK,READ_ONLY from dba_tables where table_name='YOON' and owner='SCOTT';

OWNER      TABLE_NAME      TABLESPACE_NAME                STATUS   TABLE_LO REA
---------- --------------- ------------------------------ -------- -------- ---
SCOTT      YOON            USERS                          VALID    ENABLED  NO


SQL> update yoon set empno=9999 where empno=7934;

1 row updated.

SQL> insert into yoon select * from emp;

14 rows created.

SQL> commit;
将表空间read only后,将表迁移至另一个用户、表空间，可以进行update、insert操作。



2.

SQL> alter tablespace yoon read write;

Tablespace altered.

SQL> alter table yoon read only;

Table altered.

备份yoon表：
[oracle@db01 backup]$ expdp system/admin directory=data dumpfile=yoon.dmp logfile=yoon.log tables=yoon.yoon

******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/backup/yoon.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 22:03:29
 
导入yoon表至scott用户：
[oracle@db01 backup]$ impdp system/admin directory=data dumpfile=yoon.dmp logfile=yoon_imp.log  tables=yoon.yoon remap_schema=yoon:scott remap_tablespace=yoon:users

Import: Release 11.2.0.3.0 - Production on Fri Oct 31 22:04:44 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=data dumpfile=yoon.dmp logfile=yoon_imp.log tables=yoon.yoon remap_schema=yoon:scott remap_tablespace=yoon:users 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."YOON"                              8.570 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 22:04:49

查看表的状态：

SQL> select owner,table_name,TABLESPACE_NAME,STATUS,TABLE_LOCK,READ_ONLY from dba_tables where table_name='YOON' and owner='SCOTT';

OWNER      TABLE_NAME      TABLESPACE STATUS   TABLE_LO REA
---------- --------------- ---------- -------- -------- ---
SCOTT      YOON            USERS      VALID    ENABLED  NO


SQL> update yoon set empno=9999 where empno=7934;

1 row updated.

SQL> insert into yoon select * from emp;

14 rows created.

SQL> commit;
将表设置read only,迁移至另一个用户、表空间后,可以进行update、insert操作 。