oracle select with read only,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操作 。