ALTER USER LIS DEFAULT tablespace dmspace_tmp;
ALTER USER DM_QUARTZ_PROCESS DEFAULT tablespace dmspace_tmp;
ALTER USER MIGRATION_USER DEFAULT tablespace dmspace_tmp;
ALTER USER EZPUBLISH DEFAULT tablespace dmspace_tmp;
ALTER USER DELIVERY DEFAULT tablespace dmspace_tmp;
ALTER USER DMSOLID DEFAULT tablespace dmspace_tmp;
ALTER USER LIS_TEST DEFAULT tablespace dmspace_tmp;
ALTER USER LIS DEFAULT tablespace dmspace_tmp;
-- przeniesienie lobów przed tabelami
spool Z:\oracle\scripts\lob.sql  
  select distinct 'alter table ' || dtc.owner || '.' || dtc.table_name || 
   ' move lob(' || column_name || ')' ||
   ' store as (tablespace DMSPACE_TMP);' as cmd_to_invoke
from dba_tab_columns dtc,
     dba_tables dt,
     dba_indexes di
where dt.owner=dtc.owner and dt.owner=di.owner
  and dt.table_name=di.table_name and dt.table_name=dtc.table_name
  and dtc.data_type like '%LOB%'
  and di.index_name in 
     (select segment_name from dba_segments inner 
      where inner.owner=dt.owner and tablespace_name='DMSPACE') ;

spool off
@Z:\oracle\scripts\lob.sql 
-- usunięcie 2 tabel
SQL> drop table lis.plan_table;

SQL> drop table lis_test.plan_table;
-- przeniesienie tabel 
DECLARE
  l_sql_stmt VARCHAR2(1000);
  t1 timestamp; 
  t2 timestamp; 
BEGIN
  t1 := systimestamp; 
  dbms_output.put_line('Start: '||t1);
  FOR i IN (SELECT owner,
                   table_name
              FROM all_tables
             WHERE tablespace_name = 'DMSPACE')
  LOOP
    l_sql_stmt := 'alter table ' || i.owner || '.' || i.table_name ||
                  '  move tablespace DMSPACE_tmp';
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
  t2 := systimestamp; 
  dbms_output.put_line('End: '||t2); 
END;
/

DECLARE
  l_sql_stmt VARCHAR2(1000);
BEGIN
  FOR i IN (SELECT owner,
                   index_name
              FROM all_indexes
             WHERE tablespace_name = 'DMSPACE')
  LOOP
    l_sql_stmt := 'ALTER INDEX ' || i.owner || '.' || i.index_name ||
                  '  REBUILD TABLESPACE DMSPACE_tmp';
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
END;


PURGE DBA_RECYCLEBIN;

drop tablespace dmspace including contents;

select count(*) from LIS.TBLSCORERESULTDATA;
