RE: Migrating TOOLS tablespace to ASSM

From: <Joel.Patterson_at_crowley.com>
Date: Wed, 13 Jun 2007 08:34:55 -0400
Message-ID: <02C2FA1C9961934BB6D16DE35707B27B03069B4B@jax-mbh-01.jax.crowley.com>

Why don't you take the tablespace offline before you move the datafile?

Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ian Cary Sent: Wednesday, June 13, 2007 5:23 AM
To: binhpham15_at_hotmail.com
Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org; tim_at_evdbt.com Subject: Re: Migrating TOOLS tablespace to ASSM

I didn't see you get an answer to this but the steps below seem to work. I
believe another alternative would be to use the rman convert command.

Cheers,

Ian

OTSTST10> create tablespace move_test datafile '/u02/oradata/otstst10/move_test.dbf' size 100m;

Tablespace created.

OTSTST10> create table long_test (x long) tablespace move_test;

Table created.

OTSTST10> create directory source_dir as '/u02/oradata/otstst10';

Directory created.

create directory target_dir as '+data/otstst10';

Directory created.

OTSTST10> alter tablespace move_test read only;

Tablespace altered.

OTSTST10> exec
dbms_file_transfer.copy_file('SOURCE_DIR','move_test.dbf','TARGET_DIR',' move_test.dbf');

PL/SQL procedure successfully completed.

OTSTST10> alter tablespace move_test offline;

Tablespace altered.

alter database rename file '/u02/oradata/otstst10/move_test.dbf' to '+data/otstst10/move_test.dbf';

Database altered.

OTSTST10> alter tablespace move_test online;

Tablespace altered.

|---------+----------------------------->


|         |           binhpham15_at_hotmail|

|         |           .com              |

|         |           Sent by:          |

|         |           oracle-l-bounce_at_fr|

|         |           eelists.org       |

|         |                             |

|         |                             |

|         |           13/06/2007 02:23  |

|         |           Please respond to |

|         |           binhpham15        |

|         |                             |

|---------+----------------------------->
 
>-----------------------------------------------------------------------
---------------------------------------|

>-----------------------------------------------------------------------
---------------------------------------|

Tim,

Thanks, however, the "alter table ... move..." does not work when they have

"Long" column types, and hence the question.

>From: Tim Gorman <tim_at_evdbt.com>
>Reply-To: tim_at_evdbt.com
>To: binhpham15_at_hotmail.com
>CC: oracle-l_at_freelists.org
>Subject: Re: Migrating TOOLS tablespace to ASSM
>Date: Mon, 11 Jun 2007 20:05:50 -0600
>
>Sure!  Use "ALTER TABLE ... MOVE TABLESPACE <another-tablespace>" to

move

>'em out.
>
>...don't forget to ALTER INDEX ... REBUILD on any indexes associated

with
a

>moved table...
>
>
>
>Binh Pham wrote:
>>We are trying to migrate this TOOLS tablespace to ASSM and

encountering
an

>>issue.  There are objects (tables,indexes, materialized views, etc...)
>>owned
>>by SYSTEM that prevents us from changing this TOOLS tablespace to

ASSM.

>>
>>Objects that are in the TOOLS for example:
>>
>>SYSTEM.USER_PROFILE
>>SYSTEM.SQLPLUS_PRODUCT_PROFILE
>>
>>
>>Any suggestion?  Thanks.
>>
>>--
>>http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>>

_________________________________________________________________

PC Magazine's 2007 editors' choice for best Web mail?award-winning Windows
Live Hotmail.
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migr ation_HM_mini_pcmag_0507

--
http://www.freelists.org/webpage/oracle-l



This email was received from the INTERNET and scanned by the Government
Secure Intranet Anti-Virus service supplied by Cable&Wireless in
partnership with MessageLabs. (CCTM Certificate Number 2006/04/0007.) In
case of problems, please call your organisation's IT Helpdesk.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.






For the latest data on the economy and society consult National
Statistics at http://www.statistics.gov.uk

************************************************************************
*********


Please Note:  Incoming and outgoing email messages are routinely
monitored for compliance with our policy on the use of electronic
communications
************************************************************************
*********


Legal Disclaimer  :  Any views expressed by the sender of this message
are not necessarily those of the Office for National Statistics
************************************************************************
*********


The original of this email was scanned for viruses by the Government
Secure Intranet Anti-Virus service supplied by Cable&Wireless in
partnership with MessageLabs. (CCTM Certificate Number 2006/04/0007.) On
leaving the GSi this email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 13 2007 - 07:34:55 CDT