Re: The example schemas

"Yong Liu" <fdu9774_at_rogers.com> wrote in message news:<jBKp9.227637$8b1.96329_at_news01.bloor.is.net.cable.rogers.com>...
> Hi,
>
> In Oracle 9i, example schemas such as scott, hr .. are installed at the
> system tablespace at the installation time. I want to transfer them to user
> tablespace. Now I can drop them and recreate them at users table space using
> the installtion scripts. But I am looking for a better alternative. What can
> you do to help?
>
> Thanks

I can give you a couple of solutions:

1. Export all the schema users. Now drop and re-create these schema users with USERS as DEFAULT TABLESPACE Ensure they dont have UNLIMITED TABLESPACE priviledge Import Back
2. You can create a script and execute it to move the tables ALTER TABLE emp MOVE TABLESPACE

   Ex.

SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES   2 WHERE TABLE_NAME='EMP' ;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMP                            SYSTEM

SQL> ALTER TABLE EMP MOVE TABLESPACE USERS ; Table altered.

SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES   2 WHERE TABLE_NAME='EMP' ;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMP                            USERS

Regards,
Sagi Received on Mon Oct 14 2002 - 18:29:33 CEST