Tables with a long (or LOB) datatype column is tough to be reorganized once it becomes fragmented, you can not use “Alter table move tablespace” command, you can’t use “Create Table As Select” command either.

But now you still have an option: use “Copy from using” command:

connect deltek/xxx@fin;
set heading off;
set feedback off;
set pagesize 1000;
set linesize 120;
set echo off;
spool C:\Oracle\job\reorg\copy_from.sql;
select ‘connect deltek/xxx@fin;’ from dual;
select ’select count(*) FROM ‘|| table_name ||’;'||chr(10)||
‘COPY FROM deltek/xxx@fin create ‘|| table_name ||’_ USING select * from ‘|| table_name ||’;'||chr(10)||
‘drop table ‘ || table_name || ‘;’||chr(10)||
‘rename ‘ || table_name || ‘_ to ‘|| table_name ||’;’
from user_tables
where table_name in (select table_name from user_tables where tablespace_name=’COSTPOINT_DATA’ and NUM_ROWS>0 ) ;
select ‘exit;’ from dual;
spool off;

Then run copy_from.sql to reorganize the tables.

If there is a primary key-foreign key relationship in a table, constraints must be disabled in order to drop the table.

spool C:\Oracle\job\reorg\const.sql;
SELECT ‘ALTER TABLE ‘ || table_name || ‘
DISABLE CONSTRAINT ‘ || constraint_name || ‘;’
FROM user_constraints where table_name
in (select table_name from user_tables where tablespace_name=’COSTPOINT_DATA’ );

spool off;

Then eable constraints:

SELECT 'ALTER TABLE ' || table_name || '
enable CONSTRAINT ‘ || constraint_name || ‘;’
FROM user_constraints;

You may also try dbms_redefinition to do reorganization.

Popularity: 1%

Bookmark This

Hide Sites