To answer your second question first: yes you should partition. Oracle's query optimizer has a feature called partition elimination, which will check the predicate for the partition and only execute the SQL on the appropriate partitions.
Partitioning also leaves all the data in one space. Conceptually, think of it as many tables of identical structure, with an implicit UNION ALL
between them if you were to do a SELECT
from the entire table. Except "under the hood" Oracle sorts the actual rows into the right "table" based on the criteria you specify. Any rows that come in that don't match any of the criteria, go into what's known as the "default" partition.
For what you want to do, a "range partition" might be a good approach (so you can add more tenants later), e.g.:
create table transaction (id, tenant_id, a, b, c, d)
partition by range(tenant_id)
partition p_tenant1 values less than (2) tablespace ts_tenant1
partition p_tenant2 values less than (3) tablespace ts_tenant2
partition p_tenant3 values less than (4) tablespace ts_tenant3
partition p_tenantd values less than (MAXVALUE) tablespace ts_default;
Then later
alter table transaction
add partition p_tenant4 values less than (5) tablespace ts_tenant4;
This will create something that looks and behaves just like a normal table, but actually rows where tenant_id=1 will be in a partition in tablespace ts_tenant1, and queries will ignore all other partitions. Queries across the entire table can run in parallel on each partition. If tenant_id=4 in this scenario, the row will live in ts_default unless you add the new partition as shown, but the INSERT
won't be rejected because there's no partition for it!
FWIW At my site we use partitioned tables in our 40Tb DW, you don't need to worry about this approach scaling or performing, if you choose your partitioning strategy well (e.g. you could partition on tenant_id then subpartition on month perhaps), create the right indexes, and so on.
Gaius is right, use DBA_TABLES
for NUM_ROWS
and DBA_SEGMENTS
for size:
select owner,table_name,num_rows,last_analyzed from dba_tables;
The num_rows count is as of LAST_ANALYZED
date, which should be close enough even without running DBMS_STATS.
For sizing:
select owner,segment_type,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_type;
or (depending what level of details you need):
select owner,segment_name,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_name;
For LOBs you'll need to join it to DBA_LOBS
, for indexes to DBA_INDEXES
, for tables to DBA_TABLES
. A lot will depend on your specific requirement. Since you mentioned you are trying to figure out used space in tablespace it might be as simple as:
select sum(bytes)/1024/1024 size_mb
from dba_segments
where tablespace_name='YOUR_TBLSP_NAME';
Your solution would really work only with small DB as it would not be feasible to read all data on large DB.
I am hesitant to post how to address ORA-1555
as in this case this it NOT your primary issue but just for completeness - you are on 10g using automatic undo management so your DBA would have to increase undo_retention
in your database (the ixora link is relevant to a DB without auto undo management).
Best Answer
will show you the amount of space actually consumed by each index. I'm not clear if that's exactly what sorts of overhead you're trying to account for and how you are distinguishing "used" and "allocated" in the context of an index. If you want to account for free space in the index, you can use the DBMS_SPACE.SPACE_USAGE procedure to determine how many partially empty blocks are in the index.