Query below helped me to know the current status of my tables and their places in the oracle db. So that I could further decide whether I should move it or not, notwithstanding that I must understand the undergoing code accessing those tables.
select tbl.OWNER || '.' || tbl.TABLE_NAME table_name, tbl.TABLESPACE_NAME, trunc(ds.BYTES/1024/1024) || ' Mb' table_size, tbl.NUM_ROWS, tbl.LAST_ANALYZED from SYS.ALL_TABLES tbl
join SYS.dba_segments ds on ds.SEGMENT_NAME = tbl.TABLE_NAME
where ds.SEGMENT_TYPE = 'TABLE'
The output of the query will have:
- Table name
- Tablespace where the table is stored
- The size of the table in Mb
- Number of rows
- Last analyzed
The above query could be expanded further just as needed.