Re: Migrating TOOLS tablespace to ASSM

  • From: "Ian Cary" <Ian.Cary@xxxxxxxxxxxxxx>
  • To: binhpham15@xxxxxxxxxxx
  • Date: Wed, 13 Jun 2007 10:23:01 +0100

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@hotmail|
|         |           .com              |
|         |           Sent by:          |
|         |           oracle-l-bounce@fr|
|         |           eelists.org       |
|         |                             |
|         |                             |
|         |           13/06/2007 02:23  |
|         |           Please respond to |
|         |           binhpham15        |
|         |                             |
|---------+----------------------------->
  
>--------------------------------------------------------------------------------------------------------------|
  |                                                                             
                                 |
  |       To:       tim@xxxxxxxxx                                               
                                 |
  |       cc:       oracle-l@xxxxxxxxxxxxx                                      
                                 |
  |       Subject:  Re: Migrating TOOLS tablespace to ASSM                      
                                 |
  
>--------------------------------------------------------------------------------------------------------------|





Tim,

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

"Long" column types, and hence the question.


>From: Tim Gorman <tim@xxxxxxxxx>
>Reply-To: tim@xxxxxxxxx
>To: binhpham15@xxxxxxxxxxx
>CC: oracle-l@xxxxxxxxxxxxx
>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.
>>
>>--
>>//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_migration_HM_mini_pcmag_0507


--
//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.
--
//www.freelists.org/webpage/oracle-l


Other related posts: