Solved

Moving Objects to a different tablespace

Posted on 2006-07-03
8
1,443 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
ID: 17029922
0
 
LVL 47

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
 
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
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
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 47

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

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.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

895 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

12 Experts available now in Live!

Get 1:1 Help Now