Solved

Moving Objects to a different tablespace

Posted on 2006-07-03
8
1,437 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
8 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 30 total points
Comment Utility
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
*******************************************
* 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
Comment Utility
Many thanks. Is there something different I need to do with LOBSEGMENT and LOBINDEX objects?

Thanks
0
 
LVL 2

Assisted Solution

by:Kong
Kong earned 30 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Expert Comment

by:Kong
Comment Utility
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
Comment Utility
oops, delete that last comment pls - wrong thread...
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 30 total points
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now