| From System Tablespaces To Users Tablespace [message #62196] |
Mon, 05 July 2004 02:15  |
dr alex
Messages: 18
Registered: June 2004
|
Junior Member |
|
|
I have a running database system in Oracle 8i, all tables are in system tablespace. I want to move tables from System Tablespace to Users Tablespace, Can any body help in this regard?
Any suggestion will be appreciated.
Anup
|
|
|
|
| Re: From System Tablespaces To Users Tablespace [message #62197 is a reply to message #62196] |
Mon, 05 July 2004 03:02  |
Oskar
Messages: 26
Registered: May 2002
|
Junior Member |
|
|
for tables:
alter table yourtable move tablespace users;
if table has LOB column:
alter table yourtable move tablespace users
LOB (yourcolumn) STORE AS (tablespace users);
for indexes:
alter index yourindex rebuild tablespace users;
|
|
|
|
|
|
| Re: From System Tablespaces To Users Tablespace [message #62867 is a reply to message #62196] |
Fri, 20 August 2004 07:20 |
Balaji
Messages: 102
Registered: October 2000
|
Senior Member |
|
|
hi Oskar,
Im happy that you ve got all the tables moved but you have one more job to do ,
make the default tablespace of the logon users to the USERS table space other wise you will find new objects getting into the SYSTEM tablespace.
sql> select username,default_tablespace from dba_users where default_tablespace like 'SYS%';
sql> alter user username default_tablespace users ;
with best wishes
balaji
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
