Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1629
  • Last Modified:

Moving Objects to a different tablespace

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
maran_software
Asked:
maran_software
4 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
schwertnerCommented:
*******************************************
* 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
 
maran_softwareAuthor Commented:
Many thanks. Is there something different I need to do with LOBSEGMENT and LOBINDEX objects?

Thanks
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
KongCommented:
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
 
KongCommented:
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
 
KongCommented:
oops, delete that last comment pls - wrong thread...
0
 
schwertnerCommented:
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
 
MohanKNairCommented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now