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

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

--以前也做过例子&＃xff1a;

http://blog.itpub.net/267265/viewspace-741154/

http://blog.itpub.net/267265/viewspace-744787/

--第1种就是修改数据字典的情况&＃xff0c;但是这种存在一定的风险&＃xff0c;我当时的测试版本11.2.0.1还有修改obj$的字段spare3.

--第2种就是利用交换分区的方法。这种方式小量很行&＃xff0c;大量也是不合适。

--第1种合适大量修改&＃xff0c;但是确实存在一定风险&＃xff0c;至少要严格测&＃xff0c;除了以上方法&＃xff0c;其实还可以传输表空间模式。

--还是通过例子来说明问题.

1.环境&＃xff1a;

SCOTT&＃64;test01p> &＃64; 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

&＃39;D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF&＃39; SIZE 100M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED

LOGGING

ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

SCOTT&＃64;test01p> create table t tablespace lfree as select * from  dba_objects;

Table created.

SCOTT&＃64;test01p> select count(*) from t;

COUNT(*)

----------

91698

grant dba to test identified by test;

2.传输表空间&＃xff1a;

--以sys用户登录&＃xff1a;

SYS&＃64;test01p> execute dbms_tts.transport_set_check(&＃39;lfree&＃39;);

PL/SQL procedure successfully completed.

SYS&＃64;test01p> select * from transport_set_violations;

no rows selected

SYS&＃64;test01p> alter tablespace lfree read only;

Tablespace altered.

--奇怪windows 要使用双引号。

D:\tmp\expdp>exp userid&＃61;\"/&＃64;test01p as sysdba\" transport_tablespace&＃61;y tablespaces&＃61;lfree file&＃61;lfree.exp

exp userid&＃61;\"/&＃64;test01p as sysdba\" transport_tablespace&＃61;y tablespaces&＃61;lfree file&＃61;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命令来拷贝&＃xff0c;因为现在是read only。12c drop表空间支持keep datafiles。

SYS&＃64;test01p> drop tablespace lfree including contents keep datafiles;

Tablespace dropped.

D:\tmp\expdp>imp userid&＃61;\"/&＃64;test01p as sysdba\" transport_tablespace&＃61;y tablespaces&＃61;lfree datafiles&＃61;D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF fromuser&＃61;scott touser&＃61;test file&＃61;lfree.exp

imp userid&＃61;\"/&＃64;test01p as sysdba\" transport_tablespace&＃61;y tablespaces&＃61;lfree datafiles&＃61;D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF fromuser&＃61;scott touser&＃61;test file&＃61;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&＃39;s objects into TEST

. . importing table                            "T"

Import terminated successfully without warnings.

--以test用户登录&＃xff0c;检查&＃xff1a;

TEST&＃64;test01p> select count(*) from scott.t;

select count(*) from scott.t

*

ERROR at line 1:

ORA-00942: table or view does not exist

TEST&＃64;test01p> select count(*) from test.t;

COUNT(*)

----------

91698

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

SYS&＃64;test01p> alter tablespace lfree read write ;

Tablespace altered.

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

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