1)
This is the script I use to view free space by tablespace (and largest contigous extent sizes as well)
</code>
http://asktom.oracle.com/Misc/free.html <code>
2)
You would have to query the data dictionary - for to us, it is just "ranges" - a high and low data points, they need not all be the same:
ops$tkyte@ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(25)
6 )
7 PARTITION BY RANGE (dt)
8 -- subpartition by hash(x)
9 (
10 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
11 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
12 PARTITION part3 VALUES LESS THAN (to_date('14-jun-2003','dd-mon-yyyy')) ,
13 PARTITION part4 VALUES LESS THAN (to_date('14-sep-2003','dd-mon-yyyy')) ,
14 PARTITION part5 VALUES LESS THAN (to_date('14-mar-2004','dd-mon-yyyy')) ,
15 PARTITION junk VALUES LESS THAN (MAXVALUE)
16 )
17 /
Table created.
ops$tkyte@ORA10GR2> declare
2 l_last date;
3 l_curr date;
4 begin
5 for x in ( select high_value
6 from user_tab_partitions
7 where table_name = 'T'
8 order by partition_position )
9 loop
10 exit when x.high_value = 'MAXVALUE';
11 execute immediate 'begin :x := ' || x.high_value || '; end;' using OUT l_curr;
12 if ( l_last is not null )
13 then
14 dbms_output.put_line( (l_curr-l_last) || ' days between this and last partition' );
15 end if;
16 l_last := l_curr;
17 end loop;
18 end;
19 /
1 days between this and last partition
92 days between this and last partition
92 days between this and last partition
182 days between this and last partition
PL/SQL procedure successfully completed.
3) you can use dbca to do that, but in general:
o datafiles
o control files
o online redo log files
o init (.ora) files