Re: moving one user schema out of system tablespace

I'm including a sample of moving SCOTT's tables and indexes to another tablespace below (I moved tables to USERS tablespace and indexes to INDX)

set echo off
set heading off
set verify off
set feedback off
set timing off
set arraysize 1

spool move_scott_obj

prompt spool move_scott_obj.out

SELECT
'ALTER TABLE scott.' || table_name || ' MOVE TABLESPACE USERS;' FROM dba_tables
WHERE owner = 'SCOTT';

SELECT
'ALTER INDEX scott.' || index_name || ' REBUILD TABLESPACE INDX;' FROM dba_indexes
WHERE owner = 'SCOTT';

prompt spool off;

spool off;

@ move_scott_obj
"CS" <wchuacs_at_pc.jaring.my> wrote in message news:b4f6q9$6t9$1_at_news6.jaring.my...
> Hi,
>
> I have installed Oracle8i (8.1.7.0.0) on my home PC running Windows 2000
> Professional, and there was a pre-configured database created too.
>
> I found there are a few users created, eg: Scott, Blake, Jones, etc. Who
> knows their initial passwords ? i knew only Scott/Tiger, the rest I don't.
>
> And they were created in SYSTEM tablespace, both default and temporary,
what
> is the best solution to move them to other tablespace, eg: USERS
tablespace
> (created) ?
>
> Please help me with your excellent experience to outline what are the
steps
> to follow and also any other considerations to care about ?
>
> thankyou.
>
>
Received on Tue Mar 11 2003 - 13:46:24 CST