PARTITION

CREATE TABLE big_t AS
  SELECT ROWNUM AS n_uniq
       , MOD(ROWNUM,10) AS n10
       , MOD(ROWNUM,100000) AS n100k
       , RPAD(rownum,1000,'A') AS filler
    FROM all_source, all_source
   WHERE rownum<= 100000;

SELECT COUNT(1) FROM big_t;

COUNT(1)
--------
  100000

Task completed in 2,249 seconds

SELECT COUNT(1)
  FROM big_t
 WHERE n10 = 1;

COUNT(1)
--------
   10000

Task completed in 2,328 sec


CREATE TABLE big_t_list
 PARTITION BY LIST(n10)
   (partition part1 VALUES (1)
   ,partition part2 VALUES (2,3,4)
   ,partition part3 VALUES (DEFAULT))
  AS SELECT *
       FROM big_t;

SELECT table_name, tablespace_name, blocks, num_rows
  FROM user_tables
 WHERE table_name LIKE 'BIG_T%';



SELECT table_name, partition_name, high_value, tablespace_name, blocks, num_rows
  FROM user_tab_partitions
 WHERE table_name LIKE 'BIG_T%';


select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where owner='&owner';
select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || OWNER || ';' from DBA_INDEXES WHERE OWNER IN ('SHY')

select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || OWNER || ';' from DBA_INDEXES WHERE OWNER IN ('OWNER1','OWNER2')
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || OWNER || ';' from DBA_TABLES WHERE OWNER IN ('OWNER1','OWNER2')