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

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

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

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操作。

oracle 表空間和表 read only遷移後不再read only

相關文章