[20160910]快速修改表的schema.txt

[20160910]快速修改表的schema.txt

--以前也做过例子：
http://blog.itpub.net/267265/viewspace-741154/
http://blog.itpub.net/267265/viewspace-744787/

--第1种就是修改数据字典的情况，但是这种存在一定的风险，我当时的测试版本11.2.0.1还有修改obj$的字段spare3.
--第2种就是利用交换分区的方法。这种方式小量很行，大量也是不合适。

--第1种合适大量修改，但是确实存在一定风险，至少要严格测，除了以上方法，其实还可以传输表空间模式。
--还是通过例子来说明问题.

1.环境：
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

CREATE TABLESPACE LFREE DATAFILE
  'D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF' SIZE 100M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SCOTT@test01p> create table t tablespace lfree as select * from  dba_objects;
Table created.

SCOTT@test01p> select count(*) from t;
  COUNT(*)
----------
     91698

grant dba to test identified by test;

2.传输表空间：
--以sys用户登录：
SYS@test01p> execute dbms_tts.transport_set_check('lfree');
PL/SQL procedure successfully completed.

SYS@test01p> select * from transport_set_violations;
no rows selected

SYS@test01p> alter tablespace lfree read only;
Tablespace altered.

--奇怪windows 要使用双引号。
D:\tmp\expdp>exp userid=\"/@test01p as sysdba\" transport_tablespace=y tablespaces=lfree file=lfree.exp
exp userid=\"/@test01p as sysdba\" transport_tablespace=y tablespaces=lfree file=lfree.exp
Export: Release 12.1.0.1.0 - Production on Sat Sep 10 21:43:42 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace LFREE ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                              T
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

--备份表空间。可以使用os命令来拷贝，因为现在是read only。12c drop表空间支持keep datafiles。
SYS@test01p> drop tablespace lfree including contents keep datafiles;
Tablespace dropped.

D:\tmp\expdp>imp userid=\"/@test01p as sysdba\" transport_tablespace=y tablespaces=lfree datafiles=D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF fromuser=scott touser=test file=lfree.exp
imp userid=\"/@test01p as sysdba\" transport_tablespace=y tablespaces=lfree datafiles=D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF fromuser=scott touser=test file=lfree.exp
Import: Release 12.1.0.1.0 - Production on Sat Sep 10 21:52:04 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export file created by EXPORT:V12.01.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into TEST
. . importing table                            "T"
Import terminated successfully without warnings.


--以test用户登录，检查：
TEST@test01p> select count(*) from scott.t;
select count(*) from scott.t
                           *
ERROR at line 1:
ORA-00942: table or view does not exist


TEST@test01p> select count(*) from test.t;
  COUNT(*)
----------
     91698

--可以发现现在表t已经变成了test schema。

SYS@test01p> alter tablespace lfree read write ;
Tablespace altered.

--这种方式存在风险就是注意drop tablespace时注意要保留数据文件，不要删除数据文件!!

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

转载于:http://blog.itpub.net/267265/viewspace-2124787/