Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Moving Objects to a different tablespace

Posted on 2006-07-03
8
Medium Priority
?
1,572 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 90 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Assisted Solution

by:Kong
Kong earned 90 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 90 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 105 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

688 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