From oracle-l-bounce@freelists.org Fri Mar 19 12:33:37 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2JIXb921771 for ; Fri, 19 Mar 2004 12:33:37 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2JIXao21766 for ; Fri, 19 Mar 2004 12:33:36 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9941F390D54; Fri, 19 Mar 2004 13:31:38 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 19 Mar 2004 13:30:33 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from enhbgsmtp01.state.pa.us (enhbgsmtp01.state.pa.us [206.224.21.47]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 474F3390D42 for ; Fri, 19 Mar 2004 13:30:33 -0500 (EST) Received: from ENHBGPRI11.PA.LCL ([206.224.21.35]) by enhbgsmtp01.state.pa.us with Microsoft SMTPSVC(5.0.2195.6713); Fri, 19 Mar 2004 13:36:51 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C40DE1.24DDBFBC" Subject: RE: transfer Tablespace Date: Fri, 19 Mar 2004 13:36:51 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: transfer Tablespace Thread-Index: AcQN3Q9ubISNMwljSdqCM/gFLTx7jwAAManwAACiF5A= From: "Freeman, Donald" To: X-OriginalArrivalTime: 19 Mar 2004 18:36:51.0365 (UTC) FILETIME=[250D5550:01C40DE1] X-archive-position: 1285 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: dofreeman@state.pa.us Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l ------_=_NextPart_001_01C40DE1.24DDBFBC Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable This will work for table and index segments. I created a "REORG" = tablespace to hold everything until I rebuilt the source tablespace. =20 SELECT 'ALTER '||segment_type||' '||owner||'.'||segment_name||' REBUILD = TABLESPACE REORG;' FROM dba_segments WHERE tablespace_name =3D'TEFL_INDX' AND SEGMENT_TYPE =3D'INDEX'; =20 SELECT 'ALTER '||segment_type||' '||owner||'.'||segment_name||' MOVE = TABLESPACE REORG;' FROM dba_segments WHERE tablespace_name =3D'TEFL_DATA' AND SEGMENT_TYPE =3D'TABLE'; =20 I have one table with a blob column: I moved the table as inidicated in = the first lline and moved the blob column as indicated in teh second column.=20 =20 ALTER TABLE TEFL.FREE_FORM_DATA move TABLESPACE TEFL_DATA; ALTER TABLE TEFL.FREE_FORM_DATA move lob(BINARY_DATA) store as ( = tablespace TEFL_DATA ); =20 "BINARY_DATA" is the blob column name. You should be able to do both = these things with one statement but I couldn't get it to work. =20 -----Original Message----- From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org]On Behalf Of Huascar Espinoza Sent: Friday, March 19, 2004 1:15 PM To: oracle-l@freelists.org Subject: transfer Tablespace How can I transfer a database to other Tablespace and datafile without = loss data? =20 Thank you, =20 Huascar ------_=_NextPart_001_01C40DE1.24DDBFBC Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

This=20 will work for table and index segments. I created a "REORG" tablespace = to hold=20 everything until I rebuilt the source tablespace.

SELECT 'ALTER = '||segment_type||'=20 '||owner||'.'||segment_name||' REBUILD TABLESPACE REORG;'
FROM=20 dba_segments
WHERE tablespace_name =3D'TEFL_INDX'
AND SEGMENT_TYPE=20 =3D'INDEX';

SELECT 'ALTER = '||segment_type||'=20 '||owner||'.'||segment_name||' MOVE TABLESPACE REORG;'
FROM=20 dba_segments
WHERE tablespace_name =3D'TEFL_DATA'
AND SEGMENT_TYPE=20 =3D'TABLE';

I have=20 one table with a blob column: I moved the table as inidicated in = the first=20 lline and moved the blob column

as=20 indicated in teh second column.

ALTER=20 TABLE TEFL.FREE_FORM_DATA move TABLESPACE TEFL_DATA;
ALTER TABLE=20 TEFL.FREE_FORM_DATA move lob(BINARY_DATA) store as ( tablespace = TEFL_DATA=20 );

"BINARY_DATA" is the blob column name. You should be able = to do=20 both these things with one statement but I couldn't

get it=20 to work.

-----Original Message-----
From:=20 oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org]On=20 Behalf Of Huascar Espinoza
Sent: Friday, March 19, 2004 = 1:15=20 PM
To: oracle-l@freelists.org
Subject: transfer=20 Tablespace

How can I transfer a database to other Tablespace and datafile without=20 loss data?

Thank=20 you,

Huascar

------_=_NextPart_001_01C40DE1.24DDBFBC-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------