Find Oracle Table Size, Number Of Columns and Rows

Query to Find Table Size, Number of Rows and Columns in a Oracle Database

 Here is a query that gives the table size which include number of columns, number of rows and table data size in MB. I found these information are helpful during the data warehouse design phase.

 select col.table_name,

col.col_cnt as column_count,
rc.row_cnt as row_count,
s.size_in_MB as table_size_in_MB
from
(
/* number of columns */
SELECT upper(table_name), COUNT(*) col_cnt,
FROM dba_tab_columns
WHERE owner = 'V500'
group by upper(table_name)
) col

join

(
/* number of rows */
select
table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) as row_cnt
from dba_tables
where (iot_type != 'IOT_OVERFLOW'or iot_type is null)
and owner = 'SCOTT'
) rc
on upper(col.table_name) = upper(rc.table_name)

join

(
/* table size in MB */
SELECT
owner, table_name, (sum(bytes)/1024/1024) size_in_MB
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
and owner = 'SCOTT'
)

group by owner, table_name

) s
on upper(col.table_name) = upper(s.table_name);

Related Notes:

• Query To Retrieve Oracle Table Column Name And Data Type
• Find Current Running SQL Statements In Oracle

Retrieve Oracle Table Column Name And Data Type

Query to Get  Oolumn Names and Data Type of  an Oracle  Table

 

Here is at query that retrieves column names and their data type of Oracle tables in a database. I found it handy to have a searchable copy of the source tables and their column name and data types when doing data profiling, modeling and mapping.

 

select owner,

 table_name,
 column_name,
 data_type || decode(data_type,'DATE', ' ', '(' || data_length || decode(data_scale,null,'', ',' || data_scale) || ')' ) as   Data_Type,
nullable,
 data_default
from dba_tab_columns
where owner in('SCOTT','MARY') ;

 

If dba_tab_columns is not available, table user_tab_colimns will also work.

 

Related Notes:

• Find Oracle Table Size, Number Of Columns and Rows
• Find Current Running SQL Statements In Oracle

Find Current Running SQL Statements In Oracle

List Curent Running Queries From Oracle Database Serv er

 

Here was a query I used to check the queries which were submitted by the module “ASBAgent.exe” (the datastage ASB Agent, if you use DataStage). You can remove the module clause and the username clause to see all running queries .

 

 

SELECT first_load_time,
  module,
  sql_text,
  username,
  disk_reads_per_exec,
  buffer_gets,
  rows_processed,
  hit_ratio
  FROM
(SELECT module,
  sql_text ,
  u.username ,
  round((s.disk_reads/decode(s.executions,0,1, s.executions)),2) disk_reads_per_exec,
  s.buffer_gets ,
   100 - round(100 * s.disk_reads/greatest(s.buffer_gets,1),2) hit_ratio,
  s.first_load_time
FROM sys.v_$sql s, sys.all_users u
WHERE s.parsing_user_id=u.user_id
  and UPPER(u.username) ='SCOTT'
  and module = 'ASBAgent.exe'
) s
order by first_load_time desc;

 

Related Notes:

• Find Oracle Table Size, Number Of Columns and Rows
• Retrieve Oracle Table Column Name And Data Type

•