Re: Move user to different tablespace

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:abtgi9$pr5$1_at_babylon.agtel.net...
> You didn't mention version of Oracle...
> On 8i and later you can do the following:
>
> alter user xxx default tablespace new_tbs
> then connect as xxx and
>
> begin
> for t in (select table_name from user_tables) loop
> -- won't work for tables with LONGs
> begin
> execute immediate 'alter table '||t.table_name||' move tablespace
new_tbs';
> exception
> -- ignore errors
> when others then null;
> end;
> end loop;

you might prefer to catch any errors but change the loop to

for t in (select count(*) from user_tables tabs where not exists (
select table_name from user_tab_columns cols where data_type in ('LONG','LONG RAW')
and tabs.table_name=cols.table_name)) loop

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

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