Why don't you take the tablespace offline before you move the datafile? Joel Patterson Database Administrator joel.patterson@xxxxxxxxxxx x72546 904 727-2546 -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ian Cary Sent: Wednesday, June 13, 2007 5:23 AM To: binhpham15@xxxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx; tim@xxxxxxxxx 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@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_migr ation_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 -- //www.freelists.org/webpage/oracle-l