1. Moving all tables in a schema

  select 'alter table ' || table_name || ' move tablespace users;' from user_tables;

2. Moving all indexes in a schema

  select 'ALTER INDEX ' ||Index_name || ' REBUILD TABLESPACE INDX' from user_indexes;

3. Check Tablespaces size in a database

column "TABLESPACE" justify left format a30 
column "TOTAL_SPACE" justify right format 999,999,999,999.99 
column "FREE_SPACE" justify right format 999,999,999,999.99 
column "TOTAL_SPACE_GB" justify right format 9,999,999.99 
column "USED_SPACE_GB" justify right format 9,999,999.99 
column "FREE_SPACE_GB" justify right format 9,999,999.99
set linesize 200
set pages 80 
set feedback off  

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
       fs.free_space FREE_SPACE, df.total_space_gb TOTAL_SPACE_GB,
       (df.total_space_gb - fs.free_space_gb) USED_SPACE_GB,
       fs.free_space_gb FREE_SPACE_GB,
       ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
             ROUND(SUM(bytes) / 1073741824) TOTAL_SPACE_GB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
             ROUND(SUM(bytes) / 1073741824) FREE_SPACE_GB
      FROM dba_free_space
      GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY 7;

4.  Check Transaction lock for  oracle 10g and up.

 
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status, s1.SQL_ID
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;

5. Check Segment Growth

column "Percent of Total Disk Usage" justify right format 999.99 
column "Space Used (MB)" justify right format 9,999,999.99 
column "Total Object Size (MB)" justify right format 9,999,999.99 
set linesize 150 
set pages 80 
set feedback off   

select * from (select to_char(end_interval_time, 'MM/DD/YY') mydate, 
               sum(space_used_delta) / 1024 / 1024 "Space used (MB)", 
               avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)", 
			   round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage" 
			   from dba_hist_snapshot sn, 
                    dba_hist_seg_stat a, 
                    dba_objects b, 
                    dba_segments c 
			   where begin_interval_time > trunc(sysdate) - &days_back and 
			         sn.snap_id = a.snap_id and 
					 b.object_id = a.obj# and 
					 b.owner = c.owner and 
					 b.object_name = c.segment_name and 
					 c.segment_name = '&segment_name' 
					 group by to_char(end_interval_time, 'MM/DD/YY')) 
					 order by to_date(mydate, 'MM/DD/YY');

6.Check Log Switch 24 Hours a week

column day format a10
column Switches_per_day format 9999
column 00 format 999
column 01 format 999
column 02 format 999
column 03 format 999
column 04 format 999
column 05 format 999
column 06 format 999
column 07 format 999
column 08 format 999
column 09 format 999
column 10 format 999
column 11 format 999
column 12 format 999
column 13 format 999
column 14 format 999
column 15 format 999
column 16 format 999
column 17 format 999
column 18 format 999
column 19 format 999
column 20 format 999
column 21 format 999
column 22 format 999
column 23 format 999

select to_char(first_time,'DD-MON') day,
       sum(decode(to_char(first_time,'hh24'),'00',1,0)) "00",
       sum(decode(to_char(first_time,'hh24'),'01',1,0)) "01",
       sum(decode(to_char(first_time,'hh24'),'02',1,0)) "02",
       sum(decode(to_char(first_time,'hh24'),'03',1,0)) "03",
       sum(decode(to_char(first_time,'hh24'),'04',1,0)) "04",
       sum(decode(to_char(first_time,'hh24'),'05',1,0)) "05",
       sum(decode(to_char(first_time,'hh24'),'06',1,0)) "06",
       sum(decode(to_char(first_time,'hh24'),'07',1,0)) "07",
       sum(decode(to_char(first_time,'hh24'),'08',1,0)) "08",
       sum(decode(to_char(first_time,'hh24'),'09',1,0)) "09",
       sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10",
       sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11",
       sum(decode(to_char(first_time,'hh24'),'12',1,0)) "12",
       sum(decode(to_char(first_time,'hh24'),'13',1,0)) "13",
       sum(decode(to_char(first_time,'hh24'),'14',1,0)) "14",
       sum(decode(to_char(first_time,'hh24'),'15',1,0)) "15",
       sum(decode(to_char(first_time,'hh24'),'16',1,0)) "16",
       sum(decode(to_char(first_time,'hh24'),'17',1,0)) "17",
       sum(decode(to_char(first_time,'hh24'),'18',1,0)) "18",
       sum(decode(to_char(first_time,'hh24'),'19',1,0)) "19",
       sum(decode(to_char(first_time,'hh24'),'20',1,0)) "20",
       sum(decode(to_char(first_time,'hh24'),'21',1,0)) "21",
       sum(decode(to_char(first_time,'hh24'),'22',1,0)) "22",
       sum(decode(to_char(first_time,'hh24'),'23',1,0)) "23",
       count(to_char(first_time,'MM-DD')) Switches_per_day
from v$log_history
where trunc(first_time) between trunc(sysdate) - 6 and trunc(sysdate)
group by to_char(first_time,'DD-MON') 
order by to_char(first_time,'DD-MON') ;

7. Check Which session using temp tablespace

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;