Info about a table

How many times did you wanted to know stuff about a table but were afraid to ask?

These scripts are only a head start because Data Dictionary can’t never get enough of info about a table but these few scripts give you the basic stuff.

set linesize 120
set pagesize 1000
col owner format a8
col tablespace_name format a15
set verify off

accept tbl_nm prompt "Please type in the name of the table:"

select owner, table_name, initial_extent, next_extent, pct_increase, 
       tablespace_name, num_rows, last_analyzed, CHAIN_CNT Chaned_Rows from dba_tables
where table_name = upper ( '&tbl_nm' )
order by 1, 2
select owner, table_name, index_name, distinct_keys, initial_extent, next_extent, 
       pct_increase, tablespace_name from dba_indexes
where table_name = upper ( '&tbl_nm' )
order by 1, 2, 3
select table_name, index_name, column_name, column_position from dba_ind_columns
where table_name = upper ( '&tbl_nm' )
order by 1, 2, 4
select segment_name, segment_type, count(1), sum(bytes)/1024/1024 Mega,
       sum(bytes)/8192 Blocks_8K from dba_extents
where segment_name = upper ( '&tbl_nm' ) or
segment_name in ( select index_name from dba_indexes 
                  where table_name=upper ( '&tbl_nm' ) ) 
group by segment_name, segment_type
select * from dba_cons_columns
where constraint_name in (select constraint_name from dba_constraints where 
                          constraint_type='P' and table_name=upper ( '&tbl_nm' ) )
select table_name, count(1) Nro_triggers from dba_triggers
where table_name=upper ( '&tbl_nm' )
group by table_name
About these ads