| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Moving data between tablespaces
try this.
Is my reorganization script.
It don´t work if the table has LONG or LONG RAW.
You must change the value of owner and tablespace. You can change the values of INITIAL, NEXT, etc.
set serveroutput on size 1000000
set feedback off
set echo off
set trimspool on
spool c:\move_tables.sql
DECLARE
var1 number; var2 number; var3 number; var4 number; var5 number; var6 number; var7 number;
BEGIN
dbms_output.put_line('set feedback on');
dbms_output.put_line('set echo on');
dbms_output.put_line('spool c:\move_tables.log');
dbms_output.put_line('alter session set SORT_AREA_SIZE=25000000;');
dbms_output.put_line('select to_char(sysdate, ''MM/DD/YYYY HH24:MI:SS'')
from dual;');
FOR TB in (select owner, table_name, pct_free, pct_used, ini_trans,
max_trans, initial_extent,
decode(next_extent,null,initial_extent,next_extent) nexte, min_extents,
max_extents, pct_increase, freelists, freelist_groups, decode(logging,
'YES', ' logging ', ' nologging ') logg
from dba_tables where owner = 'EPSILON' order by TABLE_NAME) LOOP dbms_output.put_line(chr(0));
dbms_output.put_line('alter TABLE '|| TB.owner ||'.'|| TB.table_name || ' move tablespace USERS ' || TB.logg
|| chr(10) || ' pctfree ' || TB.pct_free || ' pctused ' || TB.pct_used || ' initrans ' || TB.ini_trans || ' maxtrans '|| TB.max_trans
|| chr(10) || ' storage ( initial ' || TB.initial_extent || ' next ' || TB.nexte ||
' minextents ' || TB.min_extents || ' maxextents UNLIMITED ' ||
' pctincrease 0 freelists ' || TB.freelists || ' freelist groups '
|| TB.freelist_groups ||');');
dbms_space.unused_space(upper(''||TB.owner||''),upper(''||TB.table_name||'') ,'TABLE',VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7); dbms_output.put_line('-- %Libre:'|| round((VAR4*100)/VAR2) ||' Total_KB:'|| VAR2/1024 || ' Libre_KB:'||VAR4/1024 ||' -- Blk_Total:'|| VAR1 || ' Blk_Libres:'||VAR3);
dbms_space.unused_space(upper(''||INDX.owner||''),upper(''||INDX.index_name| |''),'INDEX',VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7); dbms_output.put_line('-- %Libre:'|| round((VAR4*100)/VAR2) ||' Total_KB:'|| VAR2/1024 || ' Libre_KB:'||VAR4/1024 ||' -- Blk_Total:'|| VAR1 || ' Blk_Libres:'||VAR3);
END LOOP;
END LOOP;
dbms_output.put_line('select to_char(sysdate, ''MM/DD/YYYY HH24:MI:SS'')
from dual;');
dbms_output.put_line('select * from dba_indexes where status<>''VALID'';');
dbms_output.put_line('spool off');
END;
/
spool off
spool c:\extensiones_mon_cache.log
column segment_name format a20
column owner format a10
prompt ****** EXTENSIONES. Cambia INITIAL de los siguientes segmentos (pulsa
ENTER):
pause
select owner, segment_name, segment_type, tablespace_name,
sum(bytes),count(*) from dba_extents where owner <>'SYS' group by
segment_name,owner,segment_type,tablespace_name having count(*)>3 order by
count(*);
prompt ***** MONITORING y CACHE (pulsa ENTER):
pause
select table_name, monitoring, cache from dba_tables where owner='EPSILON'
and (cache not like '%N%' or monitoring<>'NO');
spool off
-----Mensaje original-----
De: root_at_fatcity.com [mailto:root_at_fatcity.com]En nombre de Karthikeyan S
Enviado el: martes, 17 de septiembre de 2002 16:04
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Moving data between tablespaces[Scanned]
Thomas / Amar,
I moved the tables from SYSTEM to the DATA tablespace. But now I am getting
the following error.
"ORA-01502: index 'ETAIL_TEST_NEW.AGENT_PK' or partition of such index is in
unusable state"
Is it because of moving the table to a different tablespace or is it
something else?
TIA
regards,
Karthik
-----Original Message-----
Sent: Tuesday, September 17, 2002 6:13 PM
To: Multiple recipients of list ORACLE-L
Karthik,
Look at the ALTER TABLE {table_name} MOVE {tablespace}; command.
It will do exactly what you want.
You can also ALTER INDEX {index_name} REBUILD {tablespace} to move indexes.
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Tuesday, September 17, 2002 4:58 AM
To: Multiple recipients of list ORACLE-L
Hi All,
Some of my tables are accidentally created in the SYSTEM tablespace. Is there any way to move the records and the table to some other tablespace?
regards,
Karthik
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karthikeyan S INET: skarthik_at_globalsw-in.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: j.miranda_at_sermatica.es Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Sep 17 2002 - 09:48:29 CDT
 |
 |