Database Reference
In-Depth Information
EODA@ORA12CR1> create table big_table2
2 ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
3 OBJECT_ID, DATA_OBJECT_ID,
4 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
5 TIMESTAMP, STATUS, TEMPORARY,
6 GENERATED, SECONDARY )
7 partition by hash(id)
8 (partition part_1 tablespace big2,
9 partition part_2 tablespace big2,
10 partition part_3 tablespace big2,
11 partition part_4 tablespace big2,
12 partition part_5 tablespace big2,
13 partition part_6 tablespace big2,
14 partition part_7 tablespace big2,
15 partition part_8 tablespace big2
16 )
17 as
18 select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
19 OBJECT_ID, DATA_OBJECT_ID,
20 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
21 TIMESTAMP, STATUS, TEMPORARY,
22 GENERATED, SECONDARY
23 from big_table;
Table created.
Now, each of those tables is in its own tablespace, so we can easily query the data dictionary to see the allocated
and free space in each tablespace:
EODA@ORA12CR1> select b.tablespace_name,
2 mbytes_alloc,
3 mbytes_free
4 from ( select round(sum(bytes)/1024/1024) mbytes_free,
5 tablespace_name
6 from dba_free_space
7 group by tablespace_name ) a,
8 ( select round(sum(bytes)/1024/1024) mbytes_alloc,
9 tablespace_name
10 from dba_data_files
11 group by tablespace_name ) b
12 where a.tablespace_name (+) = b.tablespace_name
13 and b.tablespace_name in ('BIG1','BIG2')
14 /
TABLESPACE_NAME MBYTES_ALLOC MBYTES_FREE
------------------------------ ------------ -----------
BIG2 1200 175
BIG1 1200 223
