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

最新推荐文章于 2023-12-31 21:16:59 发布

weixin_40008870

最新推荐文章于 2023-12-31 21:16:59 发布

阅读量167

点赞数

文章标签： oracle修改表的schema

本文详细介绍了如何使用Oracle数据库的表空间传输功能进行数据迁移。通过创建表空间、导出导入数据、切换表空间权限等步骤，展示了在不丢失数据的情况下，将SCOTT用户的表t从一个模式迁移到TEST用户的过程。该方法适用于大量数据的修改，但需要注意在删除表空间时保留数据文件。

摘要由CSDN通过智能技术生成

[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

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

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