OraFAQ Forum: Server Administration » change in table space

Home » RDBMS Server » Server Administration » change in table space

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

change in table space [message #64635]

Wed, 16 February 2005 21:47

annu
Messages: 34
Registered: February 2002

Member

i have a one user which store data in system table space area. now i would like to change this table space and want to store data in users table space area.My question is how can i change table space and data store in system area to users area and is it possiable that system area is resize again as its appropariate size along with data already store in it.

For that one already suggested me that i have to recreate user again and set there table spaces and recreate tables again in this table space area.

Is there any other solution.

i m using oracle 9i
and my table spaces are like follwing details.
system 495mb used 417.629
users 25mb used 4.563
temp 490mb used 489

any suggestions are appriciable..

regards

Report message to a moderator

Re: change in table space [message #64639 is a reply to message #64635]

Thu, 17 February 2005 04:38

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>For that one already suggested me that i have to recreate user again and set there table spaces and recreate tables again in this table space area.

No need
You can just move the objects to USERS tablespace.

mag > select table_name,tablespace_name from dba_tables where table_name='EMP';

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

mag > alter table emp move tablespace users;

Table altered.

mag > select table_name,tablespace_name from dba_tables where table_name='EMP';

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

Report message to a moderator

Re: change in table space [message #64640 is a reply to message #64635]

Thu, 17 February 2005 04:52

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

a simple script like this, would help.
spool the output.
and execute the spool file

  1* select 'alter table '||table_name||' move tablespace tools;' from dba_tables where owner='MAG' and tablespace_name='USERS'
mag@mutation_mutation > /

'ALTERTABLE'||TABLE_NAME||'MOVETABLESPACETOOLS;'
-----------------------------------------------------------------
alter table DEPT move tablespace tools;
alter table EMP move tablespace tools;
alter table FOO move tablespace tools;

Report message to a moderator

Re: change in table space [message #108852 is a reply to message #64635]

Sat, 19 February 2005 05:34

d balaji
Messages: 1
Registered: February 2005

Junior Member

SQL> alter user username default tablespace users ;

then move all teh tables of the user as Mr.mahesh suggested .

Report message to a moderator

Re: change in table space [message #109285 is a reply to message #64635]

Wed, 23 February 2005 09:09

dbalaji_oracle
Messages: 4
Registered: February 2005

Junior Member

Hi ,

i have a one user which store data in system table space area.
sol:
SQL> select username,default_tablespace from dba_users
where default_tablespace ='SYSTEM' and username not like 'SYS%' ;

the above will tell u wgo are the users using system table space.
-----------------------
now i would like to change this table space and want to store data in users table space area.
SQL> alter user <username> default tablespace users ;

------------------------
My question is how can i change table space and data store in system area to users area and is it possiable that system area is resize again as its appropariate size along with data already store in it.

SQL>select table_name from dba_tables where owner='usename' and tablespace_name like 'SYS' ;

now u ll get a list of tables already created in system table space and u can move them to users tablespace .

SQL> alter table <tablename> move tablespace users ;

like wise if u find indexes them move them too .

SQL>select index_name,table_name from dba_indexeswhere tablespace_name like 'SYS%' and table_owner like 'username' ;

now u can rebuild indexes in the index table space

SQL> alter index <indexname> rebuild tablespace INDX;

HOE THIS WORKS .

with regards
DBAlaji

btw it is better to restrict a user from writing system table space like this

SQL> alter user <username> quota 0 on system ;

Report message to a moderator

Previous Topic:	Central Server Architecture
Next Topic:	Oracle 9i client in Oracle 8i Server?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 27 04:20:51 CDT 2024