long到number转换 ts_使用copy实现long类型转移表空间 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13...

在一次8.1.6的数据库恢复过程中,发现一个表空间的数据文件损坏,在转移该表空间相关表时,遇到让人郁闷的long类型.不能使用ctas和move来实现转移,最后通过古老的copy来实现该项工作.

模拟LONG类型表

SQL> create table chf.t_long (id number,name long) tablespace ts_xifenfei;

Table created.

SQL> insert into chf.t_long select object_id,object_name from dba_objects where rownum<10;

9 rows created.

SQL> commit;

Commit complete.

SQL> desc chf.t_long

Name Null? Type

----------------------------------------- -------- -----------------

ID NUMBER

NAME LONG

测试ctas和move

SQL> create table chf.t_long_bak

2 as

3 select * from chf.t_long;

select * from chf.t_long

*

ERROR at line 3:

ORA-00997: illegal use of LONG datatype

SQL> alter table chf.t_long move tablespace users;

alter table chf.t_long move tablespace users

*

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

使用copy实现LONG表跟换表空间

SQL> SET LONG 1000

SQL> select dbms_metadata.get_ddl('TABLE','T_LONG','CHF') from dual;

DBMS_METADATA.GET_DDL('TABLE','T_LONG','CHF')

--------------------------------------------------------------------------------

CREATE TABLE "CHF"."T_LONG"

( "ID" NUMBER,

"NAME" LONG

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "TS_XIFENFEI"

SQL> CREATE TABLE "CHF"."T_LONG_BAK"

2 ( "ID" NUMBER,

3 "NAME" LONG

4 ) SEGMENT CREATION IMMEDIATE

5 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

6 NOCOMPRESS LOGGING

7 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

8 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

9 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

10 TABLESPACE "USERS";

Table created.

SQL> copy from chf/xifenfei@ora11g_d INSERT chf.t_long_bak using select * from chf.t_long;

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

9 rows selected from chf@ora11g_d.

9 rows inserted into CHF.T_LONG_BAK.

9 rows committed into CHF.T_LONG_BAK at DEFAULT HOST connection.

SQL> alter table t_long rename to t_long_old;

Table altered.

SQL> alter table t_long_bak rename to t_long;

Table altered.

SQL> select tablespace_name,table_name from dba_tables where table_name like 'T_LONG%';

TABLESPACE_NAME TABLE_NAME

--------------- ---------------

TS_XIFENFEI T_LONG_OLD

USERS T_LONG

SQL> DROP TABLE T_LONG_OLD PURGE;

Table dropped.