Wednesday, July 20, 2011

Oracle query to find out table basic information

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.