Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

The example schemas

0 views
Skip to first unread message

Yong Liu

unread,
Oct 11, 2002, 9:07:27 PM10/11/02
to
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


Sean

unread,
Oct 12, 2002, 5:11:27 PM10/12/02
to
export the Scott, and related schemas, import them to the desired tablespace

"Yong Liu" <fdu...@rogers.com> wrote in message
news:jBKp9.227637$8b1....@news01.bloor.is.net.cable.rogers.com...

Odd Morten Sveås

unread,
Oct 12, 2002, 5:57:24 PM10/12/02
to
"Yong Liu" <fdu...@rogers.com> wrote in message news:<jBKp9.227637$8b1....@news01.bloor.is.net.cable.rogers.com>...

Hi

alter user scott default tablespace user;

Regards
Odd Morten

Sagi

unread,
Oct 14, 2002, 12:29:33 PM10/14/02
to
"Yong Liu" <fdu...@rogers.com> wrote in message news:<jBKp9.227637$8b1....@news01.bloor.is.net.cable.rogers.com>...

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

0 new messages