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 ||');');
-- Espacio ocupado por la tabla
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);
-- Indices de esa tabla
FOR INDX in (select owner, index_name, pct_free, 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_indexes where table_owner=TB.owner and
table_name=TB.table_name
order by index_name)
LOOP
dbms_output.put_line(' alter INDEX ' || INDX.owner ||'.'||
INDX.index_name || ' rebuild '|| INDX.logg
|| chr(10) || ' pctfree ' || INDX.pct_free || ' initrans ' ||
INDX.ini_trans || ' maxtrans '|| INDX.max_trans
|| chr(10) || ' storage ( initial ' || INDX.initial_extent ||
' next '
|| INDX.nexte ||
' minextents ' || INDX.min_extents || ' maxextents UNLIMITED '
||
' pctincrease 0 freelists ' || INDX.freelists || ' freelist
groups
' || INDX.freelist_groups ||');');
-- Espacio ocupado por el �ndice
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
