Solved

Moving Objects to a different tablespace

Posted on 2006-07-03
8
1,498 Views
Last Modified: 2010-08-05
Hi

I have a number of Objects owned by a user called DBO. The objects have been mistakenly created in the tablespace SYSTEM.

Is it possible to move these objects to a different Tablespace? Is there a statement that can do it? Some of the objects are LOBs, Indexes , Tables.

Thanks
0
Comment
Question by:maran_software
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 30 total points
ID: 17029922
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17030004
*******************************************
* TO CHANGE THE TABLESPACE OF A TABLE:    *
*******************************************
SQL> alter table IMAGEDATA_CURRENT1 move  tablespace tsdsystem;

Table altered.

***********************************************
* TO CHANGE THE TABLESPACE OF AN INDEX:       *
***********************************************
SQL> ALTER INDEX SYS_C0024261 REBUILD TABLESPACE tsisystem;

Index altered.

But there are some prerequisites.
1. Create tablespace for storing the tables and other objects:

CREATE TABLESPACE "TSDCONTACTS"
LOGGING
DATAFILE  '/u02/oradata/oradata/o10f/tsdContacts00.dbf'  SIZE 100M
AUTOEXTEND ON    NEXT 100M   MAXSIZE UNLIMITED
PERMANENT   ONLINE
EXTENT  MANAGEMENT LOCAL
;

2. Grant the user quota over the tablespace

ALTER USER LSCONTAINER QUOTA UNLIMITED ON TSICONTAINER;
0
 

Author Comment

by:maran_software
ID: 17030340
Many thanks. Is there something different I need to do with LOBSEGMENT and LOBINDEX objects?

Thanks
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 2

Assisted Solution

by:Kong
Kong earned 30 total points
ID: 17030444
Don't forget to check for unusable indexes after the table move!!!

SELECT index_name, tablespace_name
FROM user_indexes
WHERE status = 'UNUSABLE';

SELECT distinct index_name, tablespace_name
FROM user_ind_partitions
WHERE status = 'UNUSABLE';

Yes, you'll need new syntax for LOBs:

  ALTER TABLE foo MOVE
  TABLESPACE new_tbsp STORAGE(new_storage)
  LOB (lobcol) STORE AS lobsegment
  (TABLESPACE new_tbsp STORAGE (new_storage));

0
 
LVL 2

Expert Comment

by:Kong
ID: 17030489
Not sure if this output helps, but there shouldn't be any [failed] lines:

[root@lu3cduddb1 ~]# multipath -l
mpath2 (3600508b4001026000000600000420000)
[size=150 GB][features="0"][hwhandler="0"]
\_ round-robin 0 [active]
 \_ 0:0:0:3 sdc 8:32  [active][ready]
\_ round-robin 0 [enabled]
 \_ 0:0:1:3 sdf 8:80  [failed][faulty]
\_ round-robin 0 [enabled]
 \_ 1:0:0:3 sdi 8:128 [active][ready]
\_ round-robin 0 [enabled]
 \_ 1:0:1:3 sdl 8:176 [failed][faulty]

mpath1 (3600508b40010260000006000003b0000)
[size=20 GB][features="0"][hwhandler="0"]
\_ round-robin 0 [enabled]
 \_ 0:0:0:2 sdb 8:16  [failed][faulty]
\_ round-robin 0 [active]
 \_ 0:0:1:2 sde 8:64  [active][ready]
\_ round-robin 0 [enabled]
 \_ 1:0:0:2 sdh 8:112 [failed][faulty]
\_ round-robin 0 [enabled]
 \_ 1:0:1:2 sdk 8:160 [active][ready]

mpath0 (3600508b4001026000000600000340000)
[size=1 GB][features="0"][hwhandler="0"]
\_ round-robin 0 [active]
 \_ 0:0:0:1 sda 8:0   [active][ready]
\_ round-robin 0 [enabled]
 \_ 0:0:1:1 sdd 8:48  [failed][faulty]
\_ round-robin 0 [enabled]
 \_ 1:0:0:1 sdg 8:96  [active][ready]
\_ round-robin 0 [enabled]
 \_ 1:0:1:1 sdj 8:144 [failed][faulty]
0
 
LVL 2

Expert Comment

by:Kong
ID: 17030493
oops, delete that last comment pls - wrong thread...
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 30 total points
ID: 17030782
There are dificulties with IOT tables and LOB parts (indexes).
The LOB index should be in the same tablespace as LOB segment.
So I hope if you move LOB segment, the index will also move.

See


How to move LOB Data to Another Tablespace
------------------------------------------
If you want to make no other changes to the table containing a lob other than to rebuild it, use:    
ALTER TABLE foo MOVE;  
This will rebuild the table segment.  It does not affect any of the lob  segments associated with the lob columns which is the desired optimization.  

If you want to change one or more of the physical attibutes of the table containing the lob, however no attributes of the lob columns are to be changed, use the following syntax:    
ALTER TABLE foo MOVE TABLESPACE new_tbsp STORAGE(new_storage);  
This will rebuild the table segment.  It does not rebuild any of the lob  segments associated with the lob columns which is the desired optimization.  

If a table containing a lob needs no changes to the physical attributes of the  table segment, but you want to change one or more lob segments; for example, you want to move the lob column to a new tablespace as well as the lob's  storage attributes, use the following syntax:    
ALTER TABLE foo MOVE LOB(lobcol) STORE AS lobsegment    (TABLESPACE new_tbsp STORAGE (new_storage));  
Note that this will also rebuild the table segment (although, in this case, in the same tablespace and without changing the table segment physical attributes).

 If a table containing a lob needs changes to both the table attributes as well  as the lob attributes then use the following syntax:    ALTER TABLE foo MOVE   TABLESPACE new_tbsp STORAGE(new_storage)   LOB (lobcol) STORE AS lobsegment   (TABLESPACE new_tbsp STORAGE (new_storage));  

 Explanation
 -----------
 The 'ALTER TABLE foo MODIFY LOB (lobcol) ...' syntax does not allow for a change of tablespace  
 ALTER TABLE  my_lob    MODIFY LOB (a_lob)    (TABLESPACE new_tbsp);    (TABLESPACE new_tbsp)  
 *   ORA-22853: invalid LOB storage option specification  You have to use the MOVE keyword instead as shown in the examples.  
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 35 total points
ID: 17035076
var newts varchar2(100);

begin :newts := '<tablespace name>'; end;
/

set linesize 100

spool c:\temp\tmp.sql

select 'alter table '||table_name||'
move tablespace '||:newts||';' from user_tables
where table_name not in(select table_name from user_lobs)
UNION ALL
select 'alter index '||index_name||'
move tablespace '||:newts||';' from user_indexes where index_type='NORMAL'
UNION ALL
select 'alter table '||table_name||'
move tablespace '||:newts||'
LOB('||column_name||')
store as (tablespace '||:newts||');' from user_lobs;

spool off
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question