Search This Blog

Total Pageviews

Tuesday, 24 November 2020

Oracle Table info..

 

Oracle Table info.. ..



http://anuj-singh.blogspot.com/2010/08/oracle-all-table-info.html?m=0



ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

var h varchar2(30)
 begin :h := 'T4'; end;  ---- table name here!!!!
/

Or



var h varchar2(30)
 begin :h := '&tb_name'; end;  ---- table name here!!!!
/




SET LINESIZE 300 VERIFY OFF
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A20
COLUMN index_name FORMAT A30
col global_stats        for a15
col COMPRESS_FOR for a15
SELECT owner,
       table_name,
       num_rows,
COMPRESSION,
COMPRESS_FOR,
       blocks,
       empty_blocks,
       avg_space
       chain_cnt,
       avg_row_len,
       PARTITIONED,
       TABLESPACE_NAME,
       last_analyzed,
       SAMPLE_SIZE,
    --  stale_stats,
      global_stats,
      initial_extent,
      ini_trans,
      freelists
FROM   dba_tables
WHERE 1=1  
AND    table_name =:h
;



col TABLE_NAME      heading 'Table'          format a20 trunc
col NUM_ROWS        heading 'Rows'          format 99,999,999
col AVG_ROW_LEN     heading 'Avg|Row|Len'    format 99999
col BLOCKS          heading 'Blocks|In use' format 999,999
col EMPTY_BLOCKS    heading 'Empty|Blocks'  format 999,999
col PCT_BLKS_USED   heading 'Pct|Alloc|Spce|Used' format 999
col SPACE_FULL      heading 'Pct|Each|Blck|Full'  format 999
col PCT_FREE        heading 'Pct|Free'      format 999
col PCT_USED        heading 'Pct|Used'      format 999

select  TABLE_NAME,
        NUM_ROWS,
AVG_ROW_LEN,
        BLOCKS,
        EMPTY_BLOCKS, 
BLOCKS/decode((BLOCKS+EMPTY_BLOCKS),0,1,(BLOCKS+EMPTY_BLOCKS))*100 PCT_BLKS_USED,
        decode(BLOCKS,0,0,100-((BLOCKS * AVG_SPACE)/(BLOCKS * 8192))*100) SPACE_FULL,
        PCT_FREE,
        PCT_USED
from    sys.dba_tables
where   1=1
--and owner = upper('SCOTT') 
and table_name =:h
order by  table_name
/


 alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;

set linesize 300 pagesize 300
col OWNER for a20 
col OBJECT_NAME for a20
col SUBOBJECT_NAME for a20
define 1 ='XXX'

select owner,object_name,subobject_name,created from dba_objects  where object_name='&1' 
and owner= 'XXX'   
and object_type='TABLE PARTITION' 
and created > TRUNC(SYSDATE+1) - INTERVAL '1' YEAR 
order by created
;






SET LINESIZE 300 VERIFY OFF
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
COLUMN index_name FORMAT A30
col global_stats        for a15
col PARTITIONED for a15
SELECT owner,
       table_name,
       num_rows,
       blocks,
       empty_blocks,
       avg_space
       chain_cnt,
       avg_row_len,
       PARTITIONED,
       TABLESPACE_NAME,
       last_analyzed,
       SAMPLE_SIZE,
    --  stale_stats,
      global_stats,
      initial_extent,
      ini_trans,
      freelists
FROM   dba_tables
WHERE 1=1  
AND    and table_name=:h
;




SET LINESIZE 300 VERIFY OFF
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
COLUMN index_name FORMAT A30
col global_stats        for a15
col PARTITIONED for a15
col COMPRESS_FOR for a15
SELECT owner,
index_name,
status,       
table_name,
       num_rows,
COMPRESSION,
       PARTITIONED,
       TABLESPACE_NAME,
       last_analyzed,
       SAMPLE_SIZE,
      global_stats,
      initial_extent,
      ini_trans,
      freelists
FROM   dba_indexes



alter index scott.INDEX_NAME rebuild online;

set heading off
 set echo off
 Set pages 999
 set long 90000
 select dbms_metadata.get_ddl('INDEX','INDEX_NAME','SCOTT') from dual;


alter index scott.INDEX_NAME rebuild online;



--INI_TRANS for index and table 
set pagesize 300  linesize 300
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
col INDEX_TYPE for a12
col OWNER  for a12
col INDEX_NAME for a20
col table_name 
select i.table_name,i.owner,i.INDEX_NAME,i.INDEX_TYPE,i.INI_TRANS,i.MAX_TRANS,i.PCT_FREE,i.LAST_ANALYZED,t.PARTITIONED,t.INI_TRANS tab_INI_TRANS,t.MAX_TRANS tab_MAX_TRANS ,t.PCT_FREE tab_PCT_FREE ,t.TABLESPACE_NAME,t.TEMPORARY 
from dba_indexes i,dba_tables t
   where 1=1
--and t.table_name in (':h')
   -- and t.table_name ='LOG'
   and i.table_name=t.table_name
 --  and index_type='NORMAL' 
;




SET LINES 500 pagesize 300
COLUMN owner             FORMAT a20 HEADING 'Owner'
COLUMN index_name        FORMAT a25 HEADING 'Index'
COLUMN partition_name    FORMAT a20 HEADING 'Partition'
COLUMN tablespace_name   FORMAT a15 HEADING 'Tablespace'
COLUMN pct_free          FORMAT 9999 HEADING '%|Free'
COLUMN ini_trans         FORMAT 9999 HEADING 'Init|Tran'
COLUMN max_trans         FORMAT 9999 HEADING 'Max|Tran'
COLUMN initial_extent    FORMAT 9999999 HEADING 'Init|Extent'
COLUMN next_extent       FORMAT 9999999 HEADING 'Next|Extent'
COLUMN max_extent        HEADING 'Max|Extents'
COLUMN pct_increase      FORMAT 999 HEADING '%|Inc'
COLUMN distinct_keys     FORMAT 9999999 HEADING '#Keys'
COLUMN clustering_factor FORMAT 999999 HEADING 'Clus|Fact'
col table_name for a25
col high_value for a90
BREAK ON index_owner on index_name

SELECT
   a.index_owner,
   a.index_name,
   b.table_name,
   a.tablespace_name,
   a.partition_name,
   a.pct_free,
   a.ini_trans,
   a.max_trans,
   a.initial_extent,
   a.next_extent,
   a.max_extent,
   a.pct_increase,
   a.distinct_keys,
   a.clustering_factor,
   b.logging,
   b.status,
   a.compression, ----
 a.high_value
FROM    dba_ind_partitions a, dba_indexes b
where 1=1
and a.index_name=b.index_name
and b.table_name=:h
ORDER BY   index_owner,index_name;



SELECT 
owner,
index_name,
       blevel,
       leaf_blocks,
       distinct_keys,
       avg_leaf_blocks_per_key,
       avg_data_blocks_per_key,
       clustering_factor,
       num_rows,
       TABLESPACE_NAME,
       global_stats,
       last_analyzed,
initial_extent,
      ini_trans,
      freelists
FROM   dba_indexes
WHERE  1=1
and table_name =:h
ORDER BY index_name
;



-- index rebuild

define IF_COMMENT=' '
--define IF_COMMENT='--' 
SELECT 'ALTER INDEX ' || OWNER || '.' ||INDEX_NAME || ' REBUILD ' ||' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
&IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) 
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||INDEX_NAME ||' REBUILD PARTITION ' || PARTITION_NAME ||' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
&IF_COMMENT and INDEX_OWNER not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) 
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||INDEX_NAME ||' REBUILD SUBPARTITION '||SUBPARTITION_NAME||' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE'
&IF_COMMENT and INDEX_OWNER not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) 
;


SET VERIFY OFF LINESIZE 200

COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30

SELECT 
t.owner,
t.table_name,
       t.tablespace_name,
       t.num_rows,
       t.avg_row_len,
       t.blocks,
       t.empty_blocks,
       ROUND(t.blocks * ts.block_size/1024/1024,2) AS size_mb
FROM   dba_tables t
       JOIN dba_tablespaces ts ON t.tablespace_name = ts.tablespace_name
WHERE 1=1   
-- and t.owner = UPPER('')
and table_name =:h
ORDER BY t.table_name;


COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
COLUMN index_name FORMAT A30
col global_stats        for a15

SELECT owner,
      index_name,
       table_name,
        PARTITIONED,
       index_type,
       TABLESPACE_NAME,
       last_analyzed,
       SAMPLE_SIZE,
      global_stats,
      initial_extent,
      ini_trans,
      freelists
FROM   dba_indexes
WHERE 1=1  
AND    table_name =:h
;



define 1='402'
SET LINESIZE 300 VERIFY OFF
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A20
COLUMN edition_name FORMAT A15

SELECT owner,
       object_name,
       --subobject_name,
       object_id,
       data_object_id,
       object_type,
       TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') AS created,
       TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') AS last_ddl_time,
       timestamp,
       status,
       temporary,
       generated,
       secondary,
       --namespace,
       edition_name
FROM   dba_objects
WHERE  UPPER(object_name) LIKE UPPER('%&1%')
ORDER BY owner, object_name;




set linesize 300
COLUMN     tab_column_name           FORMAT A30              HEADING 'Column Name'
COLUMN     tab_column_datatype       FORMAT A20              HEADING 'Datatype'
COLUMN     tab_column_nullable       FORMAT A10              HEADING 'Nullable?'
COLUMN     tab_column_numdistinct    FORMAT 999,999,999      HEADING 'Distinct|Values'
COLUMN     tab_column_density        FORMAT 9.99999          HEADING 'Density'
COLUMN     tab_column_numnulls       FORMAT 999,999,999      HEADING 'Number|of Nulls'
COLUMN     tab_column_histogram      FORMAT A16              HEADING 'Histogram'
COLUMN     tab_column_numbuckets     FORMAT 999,999          HEADING 'Buckets'
col IND_NAME  for a20
col TABLE_NAME for a25
SELECT     i.index_name                                    ind_name,
           i.table_name ,
           i.status                                        ind_status,
           DECODE(i.uniqueness,'UNIQUE','Y',NULL)          ind_unique,
           i.blevel                                        ind_blevel,
           i.leaf_blocks                                   ind_leafblocks,
           i.num_rows                                      ind_numrows,
           i.distinct_keys                                 ind_distinctkeys,
           i.clustering_factor                             ind_clufac,
           TO_CHAR(i.last_analyzed, 'DD/MM/YYYY hh24:mi')  last_analyzed_time
FROM       dba_indexes i
WHERE      i.index_name = 'SYS_C0051402'
ORDER BY   i.uniqueness DESC, i.index_name
/



prompt DEFAULT ATTRIBUTES TABLESPACE for partition table 

set linesize 400 pagesize 300 
col owner for a20
col TABLE_NAME for a20 
select 
 OWNER   ,                  
 TABLE_NAME  ,              
 PARTITIONING_TYPE  ,       
 SUBPARTITIONING_TYPE  ,    
 PARTITION_COUNT,           
 DEF_SUBPARTITION_COUNT ,   
 PARTITIONING_KEY_COUNT ,   
 SUBPARTITIONING_KEY_COUNT ,
 STATUS   ,    
 DEF_TABLESPACE_NAME   ---- <<<<<<<  
from   DBA_PART_TABLES  
WHERE 1=1
-- DEF_TABLESPACE_NAME='OLD_TABLESPACE_NAME'
and TABLE_NAME=:h



-- ALTER USER XXX DEFAULT TABLESPACE users;

--ALTER TABLE t MODIFY DEFAULT ATTRIBUTES TABLESPACE users;
 --alter table t modify default attributes lob (x ) ( tablespace users );


------------------------***********
-- Function-based Index 

col owner for a15
col index_name for a30
col index_type for a21
select owner, index_name, index_type,table_name from dba_indexes
where 1=1
and index_type like 'FUNCTION-BASED%'
and table_name =:h;


--set linesize 300 pagesize 300
col TABLE_NAME for a20
col TABLE_OWNER for a20
col INDEX_NAME for a40
col column_expression for a100
SELECT table_owner,
       TABLE_NAME,
       index_name,
       column_expression
FROM dba_ind_expressions
WHERE 1=1
and table_owner not in ('SYS','SYSTEM')
AND    table_name =:h


------------------------***********

--- local index? 
select owner,index_name,DEF_TABLESPACE_NAME, PARTITIONING_TYPE,LOCALITY from DBA_PART_INDEXES 
where 1=1
-- and  status='UNUSABLE' 
and TABLE_NAME=:h
order by 1,2;




set linesize 300 
col INDEX_NAME for a20
col TABLE_NAME for a20
col locality for a15
col TABLE_NAME for a20
select
TABLE_NAME,
INDEX_NAME ,          
TABLE_NAME,           
PARTITIONING_TYPE ,   
SUBPARTITIONING_TYPE ,
PARTITION_COUNT ,     ----<<<<-- 
partitioning_type,
alignment,
locality
from   DBA_PART_indexes
where   table_name = :h;




prompt Any VIRTUAL_COLUMN !!!!!!!!!!!!?
set linesize 300 pagesize 300
col owner for a15
col hidden_column for a15
col virtual_column for a15
col table_name for a20
select owner,TABLE_NAME,column_name, column_id, internal_column_id, hidden_column, virtual_column
from dba_tab_cols
where table_name = :h
;


alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300
col column_name for a30
col low_value for a40
col high_value for a40
col endpoint_actual_value         for a30
col DATA_TYPE for a12
col DATA_DEFAULT for a15
col LOW_VALUE for a20 
col HIGH_VALUE for a20 
col OWNER for a15

SELECT 
owner,
column_id,
       column_name,
       num_distinct,
       avg_col_len,
       histogram,
       low_value,
       high_value,
       data_type,
       data_default,
       hidden_column
       FROM   dba_tab_columns
WHERE  1=1
AND    table_name =:h
ORDER BY column_id
;



BREAK ON TODAY
COLUMN TODAY NEW_VALUE _DATE
SELECT TO_CHAR(sysdate,'mm/dd/yy hh24:mi') TODAY FROM DUAL;     
COLUMN INSTANCE NEW_VALUE _INST
SELECT LTRIM(RTRIM(GLOBAL_NAME)) INSTANCE FROM GLOBAL_NAME;
BTITLE OFF
CLEAR BREAKS

TTITLE  LEFT   'dba_find_constr' -
CENTER _INST -
        RIGHT  _DATE SKIP 1 -
        CENTER 'ALL CONSTRAINTS FOR A GIVEN TABLE' -
        RIGHT  'Page ' FORMAT 9999 SQL.PNO SKIP 2
 

 
col for_owner         for     a10       head 'Child|Owner'          just l
col for_table         for     a10       head 'Child|Table'          just l
col for_constr        for     a35       head 'Child|Constraint'     just l
col pri_owner         for     a10       head 'Parent|Owner'         just l
col pri_table         for     a10       head 'Parent|Table'         just l
col pri_constr        for     a20       head 'Parent|Constraint'    just l
col rule              for     a10       head 'Rule'                 just l
col status            for     a08       head 'Status'               just l
col type              for     a04       head 'Type'     just l
 
spool dba_find_constr.lst

select
a.owner for_owner,
a.table_name for_table,
a.constraint_name for_constr,
b.owner pri_owner,
b.table_name pri_table,
b.constraint_name pri_constr,
a.delete_rule rule,
a.status status,
a.constraint_type type
from dba_constraints a, dba_constraints b
where a.r_constraint_name = b.constraint_name
  and a.constraint_type = 'R'
  and b.constraint_type = 'P'
  and b.owner = upper('&ownr')
  and b.table_name = :h
union all
select
b.owner for_owner,
b.table_name for_table,
b.constraint_name for_constr,
a.owner pri_owner,
a.table_name pri_table,
a.constraint_name pri_constr,
b.delete_rule rule,
b.status status,
b.constraint_type type
from dba_constraints a, dba_constraints b
where a.constraint_name = b.r_constraint_name
  and a.constraint_type = 'P'
  and b.constraint_type = 'R'
  --and b.owner = upper('ownr')
  and b.table_name = :h
union all
select
'' for_owner,
'' for_table,
'' for_constr,
b.owner pri_owner,
b.table_name pri_table,
b.constraint_name pri_constr,
b.delete_rule rule,
b.status status,
b.constraint_type type
from dba_constraints b
where 1=1 
-- and b.owner = upper('ownr')
  and b.table_name = :h
  and b.constraint_type in ('C','P')
/

undef ownr
undef tbl



set linesize 300
col constraint_name     for A18                HEADING "Constraint Name"
col constraint_type     for A20                HEADING "Constraint|Type"
col search_condition    for A15                HEADING "Search Condition"
col r_constraint_name   for A20                HEADING "R / Constraint Name"
col delete_rule         for A11                HEADING "Delete Rule"
col status                                     HEADING "Status"
col COLUMN_NAME for a20
BREAK ON constraint_name ON constraint_type
select 
    a.constraint_name
  , DECODE(a.constraint_type
             , 'P', 'Primary Key'
             , 'C', 'Check'
             , 'R', 'Referential'
             , 'V', 'View Check'
             , 'U', 'Unique'
             , a.constraint_type
    ) constraint_type
  , b.column_name
  , a.search_condition
  , NVL2(a.r_owner, a.r_owner || '.' ||  a.r_constraint_name, null) r_constraint_name
  , a.delete_rule
  , a.status
FROM     dba_constraints  a  , dba_cons_columns b
WHERE 1=1
  AND a.table_name       = b.table_name
  AND a.constraint_name  = b.constraint_name
  AND b.table_name       = :h
ORDER BY
    a.constraint_name , b.position
/

set linesize 300
col table_name for a20
col column_name for a15
select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name=:h
;


set linesize 300 pagesize 100
col DATA_TYPE for a20
col COLUMN_NAME for a200
select DATA_TYPE , listagg(COLUMN_NAME, '|') within group (order by COLUMN_NAME) as COLUMN_NAME  from DBA_TAB_COLUMNS where TABLE_NAME=:h
group by DATA_TYPE;


--- 


--Statistics on Partitioned Tables -- analyzed time 

set linesize 300
col OWNER for a20
SELECT a.table_name,
       a.partition_name,
       a.tablespace_name,
       a.initial_extent,
       a.next_extent,
       a.pct_increase,
       a.num_rows,
       a.avg_row_len,
       last_analyzed,
       global_stats
FROM   dba_tab_partitions a
WHERE  a.table_name  = :h
-- AND    a.table_owner = Upper('')
ORDER BY a.table_name, a.partition_name



-- with Partition date ? 
set linesize 300 pagesize 300
col PARTITION_NAME for a30
col HIGH_VALUE for a40
col TABLE_NAME for a25
SELECT * from (
SELECT table_name,PARTITION_NAME,
extractvalue
( dbms_xmlgen.getxmltype
( 'select high_value
from DBA_TAB_PARTITIONS where table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.PARTITION_NAME || ''''),'//text()' ) as high_value 
FROM DBA_TAB_PARTITIONS t
WHERE TABLE_NAME = :h)
order by high_value desc;



set linesize 300 pagesize 300
col PARTITION_NAME for a30
col HIGH_VALUE for a40
col TABLE_NAME for a25
col num_rows for 99999999999
SELECT * from (
SELECT TABLE_NAME,PARTITION_NAME,tablespace_name,NUM_ROWS,
extractvalue
( dbms_xmlgen.getxmltype
( 'select high_value
from DBA_TAB_PARTITIONS where table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.PARTITION_NAME || ''''),
'//text()' ) as high_value 
FROM DBA_TAB_PARTITIONS t
WHERE 1=1
--and TABLE_NAME = 'XXX'
and TABLE_NAME = :h
)
where 1=1
--and HIGH_VALUE like '%2023%' 
order by high_value desc;

===

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI';
 
 
 set linesize 300 pagesize 300 
 col TABLE_NAME   for a15                                                                                                                    
 col PARTITION_NAME for a15
 col TABLE_OWNER for a15
 SELECT 
 t.TABLE_OWNER
 ,t.table_name
 ,      t.partition_name
 ,      t.partition_position
 ,      (to_date(x2.dt,'YYYYMMDDHH24MISS') -1 )hi
 FROM   dba_tab_partitions t
 ,      xmltable('for $i in /ROWSET/ROW/HI
                  return $i'
                 passing xmltype(
                           dbms_xmlgen.getxml
                           ('select high_value hi from dba_tab_partitions x'
                          ||' where x.table_name     = '''||t.table_name||''''
                          ||' and   x.partition_name = '''|| t.partition_name|| ''''))
                 columns dt varchar2(4000) path '/') x
   ,      xmltable('for $i in /ROWSET/ROW/DT
                    return $i'
                 passing xmltype(dbms_xmlgen.getxml(q'[select to_char(]'||x.dt||q'[,'YYYYMMDDHH24MISS') dt from dual]'))
                 columns dt varchar2(16) path '/') x2
 WHERE  t.table_name = 'REP'
--order by t.partition_position 
;


====



-- Partition size 

alter session set nls_date_format='dd-mm-yyyy hh24:mi';


alter session set container=xxx ;

define SEGMENT_NAME='REP'  --- table name 
define TABLE_OWNER='REP'
define TABLE_NAME='REP'

set linesize 300 pagesize 300
col TABLE_NAME for a20
col PARTITION_NAME for a20
col HIGH_VALUE for a40
 col size1 for a8

select  distinct table_name, a.partition_name, num_rows, dbms_xplan.FORMAT_SIZE(BYTES) size1 , last_analyzed,created,high_value
 from (SELECT distinct d.table_name, d.partition_name, d.num_rows,SUM(BYTES) BYTES, d.last_analyzed,created
                            FROM DBA_SEGMENTS S,dba_tab_partitions d,dba_objects o
                            WHERE 1=1
                          -- S.PARTITION_NAME = P.PARTITION_NAME
                            and S.PARTITION_NAME = d.PARTITION_NAME
                            AND SEGMENT_NAME='&SEGMENT_NAME'
                           -- and s.OWNER='REPORTS'
                            and o.OWNER=s.OWNER
and o.subobject_name=s.PARTITION_NAME
--and d.PARTITION_NAME='SYS_P9988'
and TABLE_OWNER='&TABLE_OWNER'
--and s.OWNER=d.TABLE_OWNER
and o.OWNER=d.TABLE_OWNER
and SUBOBJECT_NAME=d.PARTITION_NAME
and object_name=TABLE_OWNER
group by d.table_name, d.partition_name, d.num_rows, d.last_analyzed,created
order by created
)a,
(select distinct 
extractvalue
( dbms_xmlgen.getxmltype
( 'select high_value
from DBA_TAB_PARTITIONS where table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.PARTITION_NAME || ''''),
'//text()' ) as high_value ,PARTITION_NAME
FROM DBA_TAB_PARTITIONS t
WHERE 1=1
and TABLE_NAME = '&TABLE_NAME') b
where 1=1
and a.partition_name=b.partition_name
order by high_value
;





  col TABLE_OWNER for a20
  col TABLE_NAME for a20
  WITH subq_pos_empty AS
   (SELECT t.table_owner
    ,      t.table_name
    ,      t.partition_name
    ,      x.cnt
    FROM   dba_segments         s
    ,      dba_tab_partitions   t
    ,      xmltable('for $i in /ROWSET/ROW/CNT
                     return $i'
                    passing xmltype(
                             dbms_xmlgen.getxml
                             ('select count(*) cnt '
                            ||'from '||t.table_owner||'.'||t.table_name||' PARTITION ('||t.partition_name||') '
                            --||'SAMPLE(.01)' -- If you want to sample to speed up unexpected large seg counts
                              ))
                    columns cnt number path '/') x
    WHERE  s.segment_type       = 'TABLE PARTITION'
    --AND    t.table_owner      LIKE 'XYZ%'
	and t.table_owner ='REPORTS'
    AND    t.table_name         = 'REPORTS' 
    AND    t.table_owner        = s.owner
    AND    t.table_name         = s.segment_name
    AND    t.partition_name     = s.partition_name
  --  AND    t.num_rows           = 0
    AND    t.partition_position > 1)
   SELECT *
   FROM   subq_pos_empty
   WHERE 1=1
-- and   cnt = 0
   ORDER BY
          table_owner
   ,      table_name
   ,      partition_name;





--- 

-- exec dbms_stats.gather_table_stats('OWNER', 'TABLE_NAME', granularity => 'SUBPARTITION', partname => 'P_20100211');


alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set linesize 200 pages 250 set verify off
col blevel format 99
col GLOBAL_STATS for a15
col PARTITIONED for a15
col table_name format a22 heading 'TABLE NAME' 
col u     format a1 heading 'U'
col index_name format a25 heading 'INDEX NAME'
col column_name format a23 heading 'COLUMN NAME' 
col column_position format 99 heading 'SEQ'
col column_length format 9999 heading 'LEN'
col leaf_blocks format 999990 heading 'LEAF|BLOCKS'
col distinct_keys format 9999990 heading 'DISTINCT|KEYS'
col avg_leaf_blocks_per_key format 999990 heading 'LEAF|BLKS|/KEY' 
col avg_data_blocks_per_key format 999990 heading 'DATA|BLKS|/KEY' 
rem break on table_name skip 1 on index_name on u rem
select i.table_name,COLUMN_NAME,i.blevel, i.leaf_blocks, i.distinct_keys,i.avg_leaf_blocks_per_key, i.avg_data_blocks_per_key,
decode( i.uniqueness, 'NONUNIQUE', null, 'UNIQUE', 'U', 'BITMAP', 'B', '?' ) u, i.index_name,i.last_analyzed, i.clustering_factor,partitioned,global_stats,status
from sys.dba_ind_columns c, sys.dba_indexes i
where 1=1
-- and  (i.table_owner,i.table_name) in (select OBJECT_OWNER,OBJECT_NAME from gV$SQL_PLAN where HASH_VALUE= )
and i.owner = c.index_owner
and i.index_name = c.index_name
and i.table_name=:h
--and owner='ANUJ'
order by i.table_owner, i.table_name, i.index_name, c.column_position
/


set linesize 300 pagesize 300
col subpartition_name  for a20
col partition_name for a20
select
     owner ,                  
     table_name    ,          
     partition_name   ,       
     partition_position   ,   
     subpartition_name ,      
     stale_stats
     from dba_tab_statistics 
     where table_name =:h
;


-- Reverse Key Indexes index_type  --- 'NORMAL/REV' ??

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set linesize 300 pages 250 set verify off
col blevel format 99
col GLOBAL_STATS for a15
col PARTITIONED for a15
col table_name format a22 heading 'TABLE NAME' 
col u format a1 heading 'U'
col index_name format a25 heading 'INDEX NAME'
col column_name format a23 heading 'COLUMN NAME' 
col column_position format 99 heading 'SEQ'
col column_length format 9999 heading 'LEN'
col leaf_blocks format 999990 heading 'LEAF|BLOCKS'
col distinct_keys format 9999990 heading 'DISTINCT|KEYS'
col avg_leaf_blocks_per_key format 999990 heading 'LEAF|BLKS|/KEY' 
col avg_data_blocks_per_key format 999990 heading 'DATA|BLKS|/KEY' 
COL tsp_name FORMAT A20 HEADING "Tablespace|Name"
rem break on table_name skip 1 on index_name on u rem

select i.table_name,COLUMN_NAME,i.blevel, i.leaf_blocks, i.distinct_keys,i.avg_leaf_blocks_per_key, i.avg_data_blocks_per_key,
decode( i.uniqueness, 'NONUNIQUE', null, 'UNIQUE', 'U', 'BITMAP', 'B', '?' ) u,
 i.index_name,i.last_analyzed, i.clustering_factor,partitioned,global_stats,status
, index_type  --- 'NORMAL/REV' ??
,tablespace_name tsp_name
,initial_extent
,ini_trans
,freelists
from sys.dba_ind_columns c, sys.dba_indexes i
where 1=1
-- and  (i.table_owner,i.table_name) in (select OBJECT_OWNER,OBJECT_NAME from gV$SQL_PLAN where HASH_VALUE= )
and i.owner = c.index_owner
and i.index_name = c.index_name
and i.table_name=:h
--and owner='SYS'
order by i.table_owner, i.table_name, i.index_name, c.column_position
/




SET LINESIZE 200
col owner for A20
col extension_name for A15
col extension for A50
col TABLE_NAME  for a25
SELECT owner, table_name, extension_name, extension FROM   dba_stat_extensions where 1=1
and table_name=:h
ORDER by owner, table_name;


column col_group format a30
 
select e.extension col_group,
       t.num_distinct,
       t.histogram
from   dba_stat_extensions e
       join dba_tab_col_statistics t on e.extension_name=t.column_name
and    t.table_name = :h;


 
-- Display extended statistics distinct values and histograms

column col_group format a30
 
select e.extension col_group,
       t.num_distinct,
       t.histogram
from   dba_stat_extensions e
       join dba_tab_col_statistics t on e.extension_name=t.column_name
and    t.table_name = :h;




Prompt Foreign Keys without Index

set lines 200  pages 200
set wrap off
column columns format a30 word_wrapped
column table_name format a30 word_wrapped
col COLUMNS_INDEXED for a20
col Status for a20
select decode( b.table_name, NULL, '****', 'ok' ) Status,   a.table_name, a.columns, b.columns columns_indexed
from 
( select substr(a.table_name,1,30) table_name, 
substr(a.constraint_name,1,30) constraint_name, 
     max(decode(position, 1,     substr(column_name,1,30),NULL)) || 
     max(decode(position, 2,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position, 3,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position, 4,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position, 5,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position, 6,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position, 7,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position, 8,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position, 9,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position,10,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position,11,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position,12,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position,13,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position,14,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position,15,', '||substr(column_name,1,30),NULL)) || 
     max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
    from all_cons_columns a, all_constraints b
   where 1=1 
  -- and a.owner=upper('POW')
    and a.table_name=:h
     and a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a, 
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name, 
     max(decode(column_position, 1,     substr(column_name,1,30),NULL)) || 
     max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) || 
     max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
    from all_ind_columns 
    where  1=1
   -- and TABLE_OWNER=upper('POW')
    and table_name=:h
   group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'
/




set linesize 300 
col TABLE_NAME for a30
col OWNER for a25
SELECT
   owner, 
   table_name, 
   TRUNC(sum(bytes)/1024/1024/1024) GB
   -- ,   ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes FROM dba_segments
 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes  FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE 1=1
--and owner in ('XX')
and table_name =:h
GROUP BY table_name, owner
-- HAVING SUM(bytes)/1024/1024/1024 > 10  -- 1gb 
ORDER BY SUM(bytes) desc
;



set lines 300 set pages 300
col table_name for a40
col owner for a30 
select distinct owner, PARTITION_NAME,NUM_ROWS,table_name, STALE_STATS, last_analyzed, stattype_locked   from dba_tab_statistics
where 1=1
and table_name=:h
;


col anlyzd_rows form 99999,999,999
col tot_rows form 99999,999,999
col tab_name form a45
col chngs form 99,999,999,999
col pct_c form 9999999990.99
col truncated head 'Trun|cated' for a5 justify l
select dbta.owner||'.'||dbta.table_name tab_name
, dbta.num_rows anlyzd_rows
, to_char(dbta.last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_anlzd
, nvl(dbta.num_rows,0)+nvl(dtm.inserts,0) -nvl(dtm.deletes,0) tot_rows
, nvl(dtm.inserts,0)+nvl(dtm.deletes,0)+nvl(dtm.updates,0) chngs
,(nvl(dtm.inserts,0)+nvl(dtm.deletes,0)+nvl(dtm.updates,0)) /greatest(nvl(dbta.num_rows,0),1) pct_c
, dtm.truncated
from dba_tab_statistics dbta
left outer join sys.dba_tab_modifications dtm
on dbta.owner = dtm.table_owner
and dbta.table_name = dtm.table_name
and dtm.partition_name is null
where 1=1
--and dbta.last_analyzed < sysdate - 1
--and STALE_STATS = 'YES'
and dbta.owner=:h
order by dbta.last_analyzed desc
;



col TABLE_OWNER for a30 
select TABLE_OWNER,table_name,inserts,updates,deletes,truncated,timestamp   from sys.dba_tab_modifications
  where 1=1
--and table_owner=user 
and table_name= :h
;



alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;
set linesize 300 pagesize 300
col TABLE_OWNER for a25
col NAME for a20
col IS_PARTITION for a15
select m.TABLE_OWNER,
'NO' as IS_PARTITION,
m.TABLE_NAME as NAME,
m.INSERTS,
m.UPDATES,
m.DELETES,
m.TRUNCATED,
m.TIMESTAMP as LAST_MODIFIED,
round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
t.num_rows as last_known_rows_number,
t.last_analyzed
From dba_tab_modifications m,dba_tables t
where m.table_owner=t.owner
and     m.table_name=t.table_name
and m.table_owner not in ('SYS','SYSTEM')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
and m.TABLE_NAME=:h
union
select m.TABLE_OWNER,
'YES' as IS_PARTITION,
m.PARTITION_NAME as NAME,
m.INSERTS,
m.UPDATES,
m.DELETES,
m.TRUNCATED,
m.TIMESTAMP as LAST_MODIFIED,
round((m.inserts+m.updates+m.deletes)*100/NULLIF(p.num_rows,0),2) as EST_PCT_MODIFIED,
p.num_rows as last_known_rows_number,
p.last_analyzed
From dba_tab_modifications m,dba_tab_partitions p
where m.table_owner=p.table_owner
and     m.table_name=p.table_name
and      m.PARTITION_NAME = p.PARTITION_NAME
and m.table_owner not in ('SYS','SYSTEM')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(p.num_rows,0) > 10 or p.last_analyzed is null)
and m.TABLE_NAME=:h
order by 8 desc
;




select command from
(select (nvl(a.updates,0)+nvl(a.inserts,0)+nvl(a.deletes,0))*100/ nvl(b.num_rows,1) as change,
   'exec DBMS_STATS.GATHER_TABLE_STATS (' || '''' || a.TABLE_OWNER || '''' || ',' || '''' || a.TABLE_NAME|| '''' || ', granularity => ' || '''' || 'PARTITION' || ''''||  ', PARTNAME => ' || '''' ||  a.PARTITION_NAME || '''' || ', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=> ' || '''' || 'FOR ALL COLUMNS SIZE AUTO' || '''' || ') ;' as command
from dba_tab_modifications a, dba_tab_partitions b
  where 1=1
 -- and a.table_name = :h
  and a.table_name = b.table_name
  and a.partition_name = b.partition_name
  and nvl(a.updates,0)+nvl(a.inserts,0)+nvl(a.deletes,0) > 0
  )
  where change >= 10
  


  Best Method to Gather Stats of Partition Tables When Using Granularity (Doc ID 2352723.1)
  exec dbms_stats.gather_table_stats(ownname=>'AAA',tabname=>'BBB',partname=>'CCC',granularity=>'PARTITION',CASCADE=> true,estimate_percent=>dbms_stats.auto_sample_size,degree=>dbms_stats.auto_degree);

  
  (or)
exec dbms_stats.gather_table_stats (ownname=>'AAA',tabname=>'BBB',granularity =>'GLOBAL',CASCADE=> true,estimate_percent=>dbms_stats.auto_sample_size,degree=>dbms_stats.auto_degree);





-- from web 
set linesize 300 pagesize 300
col schema_name for a15
col column_name for a15
col default_value for a20
col table_name for a25
col comments for a20
col data_type_ext for a20 
col data_type for a20
select /*+ PARALLEL(4) */ col.owner as schema_name,
       col.table_name, 
       col.column_name, 
       col.data_type,
       decode(char_length,   0, data_type,  data_type || '(' || char_length || ')') as data_type_ext,
       col.data_length, 
       col.data_precision,  
       col.data_scale,  
       col.nullable, 
       col.data_default as default_value,
       nvl(pk.primary_key, ' ') as primary_key, 
       nvl(fk.foreign_key, ' ') as foreign_key, 
       nvl(uk.unique_key, ' ') as unique_key, 
       nvl(check_const.check_constraint, ' ') check_constraint,
       comm.comments
  from all_tables tab
       inner join all_tab_columns col 
           on col.owner = tab.owner 
          and col.table_name = tab.table_name    
       left join all_col_comments comm  on col.owner = comm.owner
          and col.table_name = comm.table_name 
          and col.column_name = comm.column_name 
       left join (select constr.owner, col_const.table_name,col_const.column_name, 'PK' primary_key
                    from all_constraints constr 
                         inner join all_cons_columns col_const  on constr.constraint_name = col_const.constraint_name 
                            and col_const.owner = constr.owner
                   where constr.constraint_type = 'P') pk
           on col.table_name = pk.table_name 
          and col.column_name = pk.column_name
          and col.owner = pk.owner
       left join (select constr.owner, col_const.table_name,col_const.column_name, 'FK' foreign_key
                    from all_constraints constr
                         inner join all_cons_columns col_const  on constr.constraint_name = col_const.constraint_name 
                            and col_const.owner = constr.owner 
                   where constr.constraint_type = 'R'
                   group by constr.owner, 
                            col_const.table_name, 
                            col_const.column_name) fk
           on col.table_name = fk.table_name 
          and col.column_name = fk.column_name
          and col.owner = fk.owner
       left join (select constr.owner,   col_const.table_name,  col_const.column_name, 'UK' unique_key
                    from all_constraints constr
                         inner join all_cons_columns col_const   on constr.constraint_name = col_const.constraint_name 
                            and constr.owner = col_const.owner
                   where constr.constraint_type = 'U' 
                   union
                  select ind.owner,   col_ind.table_name, col_ind.column_name,  'UK' unique_key
                    from all_indexes ind
                         inner join all_ind_columns col_ind  on ind.index_name = col_ind.index_name                  
                   where ind.uniqueness = 'UNIQUE') uk
           on col.table_name = uk.table_name 
          and col.column_name = uk.column_name
          and col.owner = uk.owner
       left join (select constr.owner,   col_const.table_name, col_const.column_name, 'Check' check_constraint
                    from all_constraints constr 
                         inner join all_cons_columns col_const   on constr.constraint_name = col_const.constraint_name 
                         and col_const.owner = constr.owner
                   where constr.constraint_type = 'C'
                   group by constr.owner, col_const.table_name,  col_const.column_name) check_const
           on col.table_name = check_const.table_name 
          and col.column_name = check_const.column_name      
          and col.owner = check_const.owner
 where 1=1
/* and col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
'LBACSYS', 'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS',
'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000',
'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA',
'ORACLE_OCM','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
'XS$NULL','PUBLIC')
*/
--  and col.owner = 'XX' 
--  and lower(tab.table_name) like '%'   
and tab.table_name=:h
order by col.owner,col.table_name,  col.column_name;
   
  




set lines 300 set pages 300
col table_name for a40
col owner for a30 
select owner,table_name,stats_update_time  from dba_tab_stats_history  where table_name = :h


SELECT TABLES.OWNER, TABLES.TABLE_NAME, ROUND((DELETES + UPDATES + INSERTS)/NUM_ROWS*100) PERCENTAGE FROM DBA_TABLES TABLES, DBA_TAB_MODIFICATIONS MODIFICATIONS
WHERE TABLES.OWNER = MODIFICATIONS.TABLE_OWNER
AND TABLES.TABLE_NAME = MODIFICATIONS.TABLE_NAME 
AND NUM_ROWS > 0
AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
and TABLES.OWNER=:h
ORDER BY 3 desc



PROMPT 
PROMPT +----------------------------------------------------------------------------+
PROMPT | PARTITIONS (INDEX)                                                         |
PROMPT +----------------------------------------------------------------------------+

col  index_name              for A25       HEADING "Index Name"
col  partitioning_type       for A9        HEADING "Type"
col  partition_count         for 99999     HEADING "Part.|Count"
col  partitioning_key_count  for 99999     HEADING "Part.|Key Count"
col  locality                for A8        HEADING "Locality"
col  alignment               for A12       HEADING "Alignment"

SELECT
    a.owner || '.' || a.index_name   index_name
  , b.column_name
  , a.partitioning_type
  , a.partition_count
  , a.partitioning_key_count
  , a.locality
  , a.alignment
FROM     dba_part_indexes  a , dba_part_key_columns  b
WHERE 1=1      
  AND a.table_name         = :h
  AND RTRIM(b.object_type) = 'INDEX'
  AND b.owner              = a.owner
  AND b.name               = a.index_name
ORDER BY     a.index_name  , b.column_position
/



PROMPT 
PROMPT +----------------------------------------------------------------------------+
PROMPT | TRIGGERS                                                                   |
PROMPT +----------------------------------------------------------------------------+

col  trigger_name            for A25       HEADING "Trigger Name"
col  trigger_type            for A18       HEADING "Type"
col  triggering_event        for A9        HEADING "Trig.|Event"
col  referencing_names       for A65       HEADING "Referencing Names" newline
col  when_clause             for A65       HEADING "When Clause" newline
col  trigger_body            for A65       HEADING "Trigger Body" newline

SELECT
    owner || '.' || trigger_name  trigger_name
  , trigger_type
  , triggering_event
  , status
  , referencing_names
  , when_clause
  , trigger_body
FROM   dba_triggers
WHERE 1=1
 AND table_name  = :h
ORDER BY  trigger_name
/



set linesize 300 pagesize 300
col table_name for a25
col referencing_object for a20 
select referenced_owner || '.' || referenced_name as table_name, referenced_type as type,  owner || '.' || name as referencing_object,   type as referencing_type
from sys.all_dependencies
where referenced_type in('TABLE', 'VIEW')
      and referenced_name = :h -- put your table or view 
      --and referenced_owner = 'schema name'
      and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS','MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST','WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','OUTLN', 'WKSYS',  'LBACSYS') 
order by referencing_object;



set linesize 300 pagesize 300
col object_name for a15
col owner       for a12
col kill info   for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,t.start_scnw,t.start_scnb
,t.start_time -- <<<<--- format mm/dd/yy 
,s.username, o.object_name,o.owner,t.used_ublk ,t.used_urec ,s.event,s.sql_id,s.prev_sql_id,s.status 
from gv$transaction t, gv$session s, gv$locked_object l,dba_objects o 
where 1=1 
and t.ses_addr  = s.saddr 
and t.inst_id   = s.inst_id 
and t.xidusn    = l.xidusn 
and t.xidslot   = l.xidslot 
and t.xidsqn    = l.xidsqn 
and t.inst_id   = l.inst_id 
and l.object_id = o.object_id 
-- and username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
-- and s.STATUS!='INACTIVE'
-- l.object_id IN ('') ----< OBJECT ID FROM DBA_OBJECTS
and o.object_name=:h
;


--desc table 

COLUMN column_name         FORMAT A20                HEADING "Column Name"
COLUMN data_type           FORMAT A25                HEADING "Data Type"
COLUMN nullable            FORMAT A13                HEADing "Null?"

SELECT
    column_name
  , DECODE(nullable, 'Y', ' ', 'NOT NULL') nullable
  , DECODE(data_type
               , 'RAW',      data_type || '(' ||  data_length || ')'
               , 'CHAR',     data_type || '(' ||  data_length || ')'
               , 'VARCHAR',  data_type || '(' ||  data_length || ')'
               , 'VARCHAR2', data_type || '(' ||  data_length || ')'
               , 'NUMBER', NVL2(   data_precision
                                 , DECODE(    data_scale
                                            , 0
                                            , data_type || '(' || data_precision || ')'
                                            , data_type || '(' || data_precision || ',' || data_scale || ')'
                                   )
                                 , data_type)
               , data_type
    ) data_type
FROM
    dba_tab_columns
WHERE 1=1
 AND table_name = :h
ORDER BY   column_id
/


col owner        form a6 word wrap
col table_name   form a15 word wrap
col column_name  form a22 word wrap
col data_type    form a12
col M            form a1
col num_vals     form 99999,999
col dnsty        form 0.9999
col num_nulls    form 99999,999
col low_v        form a18
col hi_v         form a18
col data_type    form a10
set lines 110
break on owner nodup on table_name nodup

select --owner
--      ,table_name
      column_name
      ,data_type
      ,decode (nullable,'N','Y','N')  M
      ,num_distinct num_vals
      ,num_nulls
      ,density dnsty
,decode(data_type
  ,'NUMBER'       ,to_char(utl_raw.cast_to_number(low_value))
  ,'VARCHAR2'     ,to_char(utl_raw.cast_to_varchar2(low_value))
  ,'NVARCHAR2'    ,to_char(utl_raw.cast_to_nvarchar2(low_value))
  ,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(low_value))
  ,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(low_value))
  ,'DATE',to_char(1780+to_number(substr(low_value,1,2),'XX')
         +to_number(substr(low_value,3,2),'XX'))||'-'
       ||to_number(substr(low_value,5,2),'XX')||'-'
       ||to_number(substr(low_value,7,2),'XX')||' '
       ||(to_number(substr(low_value,9,2),'XX')-1)||':'
       ||(to_number(substr(low_value,11,2),'XX')-1)||':'
       ||(to_number(substr(low_value,13,2),'XX')-1)
,  low_value
       ) low_v
,decode(data_type
  ,'NUMBER'       ,to_char(utl_raw.cast_to_number(high_value))
  ,'VARCHAR2'     ,to_char(utl_raw.cast_to_varchar2(high_value))
  ,'NVARCHAR2'    ,to_char(utl_raw.cast_to_nvarchar2(high_value))
  ,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(high_value))
  ,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(high_value))
  ,'DATE',to_char(1780+to_number(substr(high_value,1,2),'XX')
         +to_number(substr(high_value,3,2),'XX'))||'-'
       ||to_number(substr(high_value,5,2),'XX')||'-'
       ||to_number(substr(high_value,7,2),'XX')||' '
       ||(to_number(substr(high_value,9,2),'XX')-1)||':'
       ||(to_number(substr(high_value,11,2),'XX')-1)||':'
       ||(to_number(substr(high_value,13,2),'XX')-1)
,  high_value
       ) hi_v
from dba_tab_columns
where 1=1
-- and owner      like upper('&tab_own')
--and   table_name like upper(nvl('&tab_name','OOPS')||'%')
AND table_name         = :h
ORDER BY owner,table_name,COLUMN_ID
/


-- ===============


alter session set nls_date_format='dd-MM-YYYY hh24:mi';
set linesize 300 pagesize 300
col owner for a20
col table_name  for a20
col index_name for a20
col TABLE_OWNER for a20
select owner,
   index_name,
   decode(INDEX_TYPE,
  'NORMAL',
  'B-tree',
  decode(index_type,
'BITMAP',
'BitMap',
decode(table_type,
'IOT',
'IOT',
decode(index_type, 'LOB', 'LOB')))) idx_type,
   table_owner,
   table_name,
   table_type,
   compression,
   status,
   blevel,
   clustering_factor,
   uniqueness,
   distinct_keys,
   last_analyzed,
   last_ddl_time
  from (select distinct t.INDEX_NAME,
t.OWNER,
t.index_type,
t.TABLE_OWNER,
t.TABLE_NAME,
decode(u.partitioned,
   'YES',
   'PART',
   decode(u.temporary,
  'Y',
  'TEMP',
  decode(u.iot_type,
'IOT',
'IOT',
'NORMAL'))) table_type,
t.compression,
t.status,
t.blevel,
t.clustering_factor,
t.distinct_keys,
t.uniqueness,
t.last_analyzed,
s.last_ddl_time
  from dba_indexes t, dba_objects s, dba_tables u
where t.index_name = s.object_name
   and u.table_name = t.table_name
   and s.owner = u.owner
   and t.owner = s.owner
   and s.object_type = 'INDEX'
  --  and t.owner = 'SYS'
--  and t.INDEX_NAME =''
and u.table_name=:h
order by t.index_name);


set linesize 300 pagesize 300 
col column_name for a20 
col subpartition_name  for a20 
col partition_name for a20
select t.table_owner,
   t.table_name,
   s.object_id,
   s.OBJECT_TYPE,
   'part tab' as part_role,
   s.DATA_OBJECT_ID,
   t.partition_name,
   t.subpartition_count,
   t.partition_position,
   t.CHAIN_CNT,
   trunc(((t.AVG_ROW_LEN * t.NUM_ROWS) / 8) / (decode(t.BLOCKS, 0, 1, t.BLOCKS)) * 100) as HWM_STAT,
   t.blocks,
   t.num_rows,
   t.avg_row_len,
   t.last_analyzed,
   s.LAST_DDL_TIME
  from dba_tab_partitions t, dba_objects s
 where t.partition_name = s.subobject_name
   and t.table_name = s.object_name
   and t.table_owner = s.owner
   and t.table_name not like '%BIN$%'
   and s.OBJECT_TYPE = 'TABLE PARTITION'
 --  and t.table_owner = 'SYS'
 and t.table_name =:h
 order by table_owner, table_name, partition_name
 ;
 



set linesize 300 pagesize 300 
col column_name for a20 
col subpartition_name  for a20 
col partition_name for a20
select t.table_owner,
   t.table_name,
   s.object_id,
   s.OBJECT_TYPE,
   'part tab son' as part_role,
   s.DATA_OBJECT_ID,
   t.partition_name,
   t.SUBPARTITION_NAME,
   t.SUBPARTITION_POSITION,
   u.column_name,
   t.CHAIN_CNT,
   t.blocks,
   t.num_rows,
   t.avg_row_len,
   trunc(((t.AVG_ROW_LEN * t.NUM_ROWS) / 8) / (decode(t.BLOCKS, 0, 1, t.BLOCKS)) * 100) as HWM_STAT,
   t.last_analyzed,
   s.LAST_DDL_TIME
  from dba_tab_subpartitions t, dba_objects s,dba_subpart_key_columns u
 where t.table_name = u.name
 and t.subpartition_name = s.subobject_name
   and t.table_name = s.object_name
   and t.table_owner = s.owner
   and t.table_name not like '%BIN$%'     
   and s.OBJECT_TYPE = 'TABLE SUBPARTITION'
  -- and t.table_owner = 'SYS'
  and t.table_name =:h 
 order by table_owner, table_name, partition_name
 ;


set linesize 300 pagesize 300 
col column_name for a20 
col subpartition_name  for a20 
col partition_name for a20
select t.table_owner,
   t.table_name,
   s.object_id,
   s.OBJECT_TYPE,
   'part tab son' as part_role,
   s.DATA_OBJECT_ID,
   t.partition_name,
   t.SUBPARTITION_NAME,
   t.SUBPARTITION_POSITION,
   u.column_name,
   t.CHAIN_CNT,
   t.blocks,
   t.num_rows,
   t.avg_row_len,
   trunc(((t.AVG_ROW_LEN * t.NUM_ROWS) / 8) / (decode(t.BLOCKS, 0, 1, t.BLOCKS)) * 100) as HWM_STAT,
   t.last_analyzed,
   s.LAST_DDL_TIME
  from dba_tab_subpartitions t, dba_objects s,Dba_Subpart_Key_Columns u
 where t.table_name = u.name
 and t.subpartition_name = s.SUBOBJECT_NAME
   and t.table_name = s.object_name
   and t.table_owner = s.owner
   and t.table_name not like '%BIN$%'     
   and s.OBJECT_TYPE = 'TABLE SUBPARTITION'
   --and t.table_owner = 'SYS'
   and t.table_name =:h 
 order by table_owner, table_name, partition_name;
 ;


set linesize 300 pagesize 300 
col column_name for a20 
col subpartition_name  for a20 
col partition_name for a20
col IOT_NAME for a15
   select t.owner,
   t.table_name,
   decode(t.partitioned,
  'YES',
  'PART',
  decode(t.temporary, 'Y', 'TEMP', decode (t.iot_type,'IOT','IOT','NORMAL'))) table_type,
   s.object_type,
   t.iot_name,
   t.NUM_ROWS,
   t.BLOCKS,
   t.AVG_ROW_LEN,
   t.LAST_ANALYZED,
   s.last_ddl_time,
   t.CHAIN_CNT,
   trunc(((t.AVG_ROW_LEN * t.NUM_ROWS) / 8) / (decode(t.BLOCKS, 0, 1, t.BLOCKS)) * 100) as HWM_STAT,
   t.COMPRESSION
  from dba_tables t, dba_objects s
where t.table_name = s.object_name
   and t.owner = s.owner
   and s.object_type = 'TABLE'
   and t.table_name not like '%BIN%'
    and t.table_name =:h 
--  and t.owner = 'obj_owner'
;


set linesize 300 pagesize 300 
col column_name for a20 
col subpartition_name  for a20 
col partition_name for a20
col iot_name for a15  
select t.owner,
   t.index_name,
   t.table_name,
   u.partition_name,
   u.partition_position,       
   v.column_name,
   t.partitioning_type,
   t.subpartitioning_type,
   t.partition_count,
   t.locality,
   t.alignment,
   u.compression,
   u.status,
   u.blevel,
   u.clustering_factor,
   u.distinct_keys,
   u.last_analyzed
  from dba_part_indexes t, dba_ind_partitions u,dba_part_key_columns v
 where t.index_name = u.index_name
   and t.index_name = v.name
   and t.owner = u.index_owner
  -- and t.owner = 'obj_owner'
  and t.TABLE_NAME=:h 
 order by u.index_name,u.partition_name
 ;
 

select s.segment_name,
       t.partition_name,
       s.partition_name,
       sum(s.bytes) / 1024 / 1024 as tab_space
  from dba_segments s, dba_tab_subpartitions t
 where s.partition_name = t.subpartition_name
   and s.segment_type = 'TABLE SUBPARTITION'
   and s.owner = t.table_owner
  -- and s.owner = :owner
  AND    table_name =:h
 group by s.owner, s.segment_name,t.partition_name,s.partition_name;




set linesize 300
select owner, table_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len 
from dba_tables 
where table_name=:h
;

---------------------------------------------------------------------

col MB for 999,999,999.99

set linesize 300
SELECT owner, table_name, NVL(num_rows*avg_row_len,0)/1024000 MB FROM dba_tables 
where 1=1
and table_name=:h
ORDER BY owner, table_name;

---------------------------------------------------------------------

col ALLOCATED_MB for 999,999,999.99
col REQUIRED_MB for 999,999,999.99
col TABLE_NAME for a20
col TABLESPACE_NAME for a20
SELECT SUBSTR(s.segment_name,1,20) TABLE_NAME,
SUBSTR(s.tablespace_name,1,20) TABLESPACE_NAME,
ROUND(DECODE(s.extents, 1, s.initial_extent,(s.initial_extent + (s.extents-1) * s.next_extent))/1024000,2) ALLOCATED_MB,ROUND((t.num_rows * t.avg_row_len / 1024000),2) REQUIRED_MB
FROM dba_segments s, dba_tables t
WHERE s.owner = t.owner
AND s.segment_name = t.table_name
-- and t.owner='TEST'
and t.table_name=:h
ORDER BY ALLOCATED_MB;

col segment_name for a32
select segment_name, substr(s.segment_name,1,20) table_name,substr(s.tablespace_name,1,20) tablespace_name,
round(decode(s.extents, 1, s.initial_extent,(s.initial_extent + (s.extents-1) * s.next_extent))/1048576,2) allocated_mb, 
bytes/(1024*1024) m,segment_type 
from dba_segments s
where 1=1 
--and segment_name like 'accountrat%'
and segment_name =:h
--and t.owner=:h
order by allocated_mb;

select owner, table_name, TABLESPACE_NAME, ROUND((t.num_rows * t.avg_row_len / 1048576),2) REQUIRED_MB 
from dba_tables t
where 1=1
--and owner= 'TEST'
and table_name=:h
;



set lines 200 pages 200
col value for 999,999,999,999.99
col STATISTIC_NAME for a30
col OWNER for a12
col OBJECT_TYPE for a10

select *
from
  (select statistic_name,
     st.owner,
     st.obj#,
     st.object_type,
     st.object_name,
     st.value,
     dense_rank() over(partition by statistic_name
   order by st.value desc) rnk
   from gv$segment_statistics st)
where rnk <= 10
and object_name=:h
;

 ---Table size 

set linesize 200 pagesize 200
col partition_name for a20
col segment_name for a30
col owner for  a20
select owner, segment_name, segment_type, partition_name, tablespace_name , round(bytes/(1024*1024*1024),2) size_gb
from dba_segments 
where 1=1 
and segment_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION','INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY', 'LOBINDEX', 'LOBSEGMENT', 'LOB PARTITION')
--AND segment_type = 'TABLE'
 and segment_name=:h
-- AND ROUND(bytes/(1024*1024*1024),2) > 10  --- 10gb 
--and segment_name in  ('XXXX')
ORDER BY bytes DESC;



set linesize 300 pagesize 300
col SEGMENT_NAME  format a40
col PARTITION_NAME for a20
col TABLE_NAME for a30
col TABLESPACE_NAME for a20
select s.segment_name,TABLE_NAME, s.partition_name, bytes/1024/1024/1024 "Size (GB)",s.TABLESPACE_NAME
from dba_segments s, dba_lobs l
 where s.segment_name = l.segment_name
-- and s.segment_name not like 'SYS%'
 --and s.owner ='OBX'
 --and s.segment_name in ('SYS_LOB0001163662C00007$$','SYS_LOB0001720371C00012$$' ,'SYS_LOB0001720358C00009$$','OBI.SYS_LOB0001163644C00007$$')
  and s.segment_name= :h
 order by s.segment_name, s.partition_name;



set linesize 500
col securefile for a15
col owner for a20
select distinct e.owner,l.table_name,l.segment_name,encrypt,compression,deduplication,format,securefile,retention_type,retention_value from dba_extents e, dba_lobs l
where e.owner = l.owner
and e.segment_name = l.segment_name
--and e.segment_type = 'LOBSEGMENT'
--and l.segment_name like 'SYS_LOB0000C00014$$'
and l.table_name=:h
order by 1
;


set linesize 300 pagesize 300
col owner for a20
col column_name for a20
col segment_name for a30
col TABLE_NAME for a30
col TABLESPACE_NAME for a30
SELECT owner
, table_name
, column_name
, segment_name
,securefile
,TABLESPACE_NAME
FROM dba_lobs
WHERE 1=1
AND segment_name IN (
                     SELECT segment_name
                     FROM dba_segments
                     WHERE 1=1
                     AND owner = 'ANUJ'  ----<<<<
                     AND segment_type = 'LOBSEGMENT' -- or LOB SUBPARTITION, LOBINDEX, LOB PARTITION
                     )


col OWNER for a12
col OBJECT_NAME for a30
col OBJECT_TYPE for a14
col SUBOBJECT_NAME for a30
SELECT ob.owner, ob.object_name, ob.subobject_name, ob.object_type,obj#, savtime, flags, rowcnt, blkcnt, avgrln ,samplesize, analyzetime, cachedblk, cachehit, logicalread
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE 1=1 --and owner=upper('OWNER')
and object_name=:h
and object_type in ('TABLE')
and object_id=obj#
order by savtime asc;





COLUMN     tab_column_name           FORMAT A30              HEADING 'Column Name'
COLUMN     tab_column_datatype       FORMAT A20              HEADING 'Datatype'
COLUMN     tab_column_nullable       FORMAT A10              HEADING 'Nullable?'
COLUMN     tab_column_numdistinct    FORMAT 999,999,999      HEADING 'Distinct|Values'
COLUMN     tab_column_density        FORMAT 9.99999          HEADING 'Density'
COLUMN     tab_column_numnulls       FORMAT 999,999,999      HEADING 'Number|of Nulls'
COLUMN     tab_column_histogram      FORMAT A16              HEADING 'Histogram'
COLUMN     tab_column_numbuckets     FORMAT 999,999          HEADING 'Buckets'

SELECT     i.index_name                                    ind_name,
           i.status                                        ind_status,
           DECODE(i.uniqueness,'UNIQUE','Y',NULL)          ind_unique,
           i.blevel                                        ind_blevel,
           i.leaf_blocks                                   ind_leafblocks,
           i.num_rows                                      ind_numrows,
           i.distinct_keys                                 ind_distinctkeys,
           i.clustering_factor                             ind_clufac,
           TO_CHAR(i.last_analyzed, 'DD/MM/YYYY hh24:mi')  last_analyzed_time
FROM       dba_indexes i
WHERE      i.table_name = :h
ORDER BY   i.uniqueness DESC, i.index_name
/




-- from https://github.com/macartain/sql/blob/master/get_table_index_info.sql


set linesize 300 pagesize 300
COLUMN   index_name  FORMAT A30 HEADING 'Index Name'
COLUMN   column_name FORMAT A30 HEADING 'Column Name'
COLUMN   low_value   FORMAT A20 HEADING 'Low Value'
COLUMN   high_value  FORMAT A20 HEADING 'High Value'

BREAK ON index_name NODUP

WITH col_hi_lo_vals AS
( select     tc.column_name
        ,    tc.data_type
        ,    tc.low_value raw_low_value
        ,    tc.high_value raw_high_value
        ,    SUBSTR(dump(tc.low_value), (INSTR(dump(tc.low_value),': ')+2)) date_low_val
        ,    SUBSTR(dump(tc.high_value), (INSTR(dump(tc.high_value),': ')+2)) date_high_val   
  from       dba_tab_columns tc
  WHERE      tc.table_name = :h
),
col_hi_lo_vals_translated AS
( SELECT     column_name
         ,   data_type
         ,   CASE when data_type = 'DATE'
             THEN 
                  TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,1)-100, '09')|| -- low_century 
                  TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,2)-100, '09')|| -- low_year
                  TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,3),'09')|| --      low_month     
                  TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,4),'09')|| --      low_day     
                  TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,5)-1,'09')|| --      low_hour24     
                  TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,6)-1,'09')|| --      low_minute     
                  TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,7)-1,'09')   --      low_second
             ELSE
                 NULL
             END low_date
         ,   CASE when data_type = 'DATE'
             THEN 
                  TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,1)-100, '09')|| -- high_century
                  TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,2)-100, '09')|| -- high_year
                  TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,3), '09')|| --     high_month     
                  TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,4), '09')|| --     high_day
                  TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,5)-1, '09')|| --     high_hour24
                  TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,6)-1, '09')|| --     high_minute 
                  TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,7)-1, '09')   --     high_second     
             ELSE
                  NULL
             END high_date
         ,   CASE WHEN data_type = 'NUMBER'
                  THEN
                       utl_raw.cast_to_number(raw_low_value)
             ELSE
                  NULL
             END low_num
         ,   CASE WHEN data_type = 'NUMBER'
                  THEN
                       utl_raw.cast_to_number(raw_high_value)
             ELSE
                  NULL
             END high_num           
         ,   CASE WHEN data_type LIKE '%CHAR%'
                  THEN
                       utl_raw.cast_to_varchar2(raw_low_value)
             ELSE
                  NULL
             END low_char
         ,   CASE WHEN data_type LIKE '%CHAR%'
                  THEN
                       utl_raw.cast_to_varchar2(raw_high_value)
             ELSE
                  NULL
             END high_char           
FROM         col_hi_lo_vals
)
SELECT     ic.index_name,
           ic.column_name
      ,    CASE WHEN chlvt.data_type = 'DATE'
                THEN TO_CHAR(TO_DATE(REPLACE(chlvt.low_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
                WHEN chlvt.data_type = 'NUMBER'
                THEN LPAD(TO_CHAR(chlvt.low_num),20)
                WHEN chlvt.data_type LIKE '%CHAR%'
                THEN chlvt.low_char
           END low_value
      ,    CASE WHEN chlvt.data_type = 'DATE'
                THEN TO_CHAR(TO_DATE(REPLACE(chlvt.high_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
                WHEN chlvt.data_type = 'NUMBER'
                THEN LPAD(TO_CHAR(chlvt.high_num),20)
                WHEN chlvt.data_type LIKE '%CHAR%'
                THEN chlvt.high_char
           END high_value           
FROM       dba_ind_columns ic
      ,    col_hi_lo_vals_translated chlvt
WHERE      ic.table_name = :h
  AND      ic.column_name = chlvt.column_name
ORDER BY   ic.index_name, ic.column_position
/



Prompt Table metadata ...

SET LONG 10000 LONGCHUNKSIZE 10000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner)
FROM   all_tables
WHERE 1=1   
-- and owner      = UPPER('1')
AND    table_name = :h;

set PAGESIZE 80

****



var h varchar2(30)
 begin :h := 'TEST'; end;  ---- table name here!!!!
/

set heading off  echo off pages 999  long 90000 

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

var h varchar2(30)
 begin :h := 'TEST'; end;  ---- table name here!!!!
/

var O varchar2(30)
 begin :O := 'ANUJ'; end;  ---- table owner here!!!!
/

set heading off  echo off pages 999  long 90000 

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);


SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner)
FROM   all_tables
WHERE 1=1   
 --and owner      = :O
AND    table_name = :h;



SELECT DBMS_METADATA.get_ddl ('INDEX', INDEX_NAME, owner)
FROM   all_indexes
WHERE 1=1   
--and owner      = :O
AND    table_name = :h;



select dbms_metadata.get_dependent_ddl('CONSTRAINT', table_name, owner)
FROM   all_tables
WHERE 1=1   
--and owner      = :O
AND    table_name = :h;


select dbms_metadata.get_dependent_ddl('TRIGGER', table_name, owner)
FROM   all_tables
WHERE 1=1   
-- and owner      = :O
AND    table_name = :h;


==============


GATHER_TABLE_STATS for stale stats 

set linesize 300 pagesize 300
col gather_stats for a200
select 'EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'''||owner||''', tabname=>'''||table_name||''',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => ''FOR ALL COLUMNS SIZE AUTO'', degree=>16,cascade=>true);' gather_stats
from dba_tables t
where 1=1
and owner='XXX' 
--and PARTITIONED='NO' 
--and LAST_ANALYZED<sysdate-4
and t.TABLE_NAME in (select TABLE_NAME from DBA_TAB_STATISTICS where STALE_STATS='YES' 
and OWNER='XX'
)
;



gather stats ... 

define tabname='EMP'
define owner='ANUJ'

set pages 100 lines 250
--set echo off feedback off heading on 
col gather for a200
spool gather.sql
select 'SET ECHO ON FEEDBACK ON TIMING ON' FROM DUAL;
select 'exec dbms_stats.gather_table_stats (ownname => ''' || owner ||''', tabname => '''||table_name||''' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE,method_opt => '''||' FOR ALL COLUMNS SIZE AUTO'''||' , degree => 8);' gather  from   dba_tables
where table_name in ('&&tabname')
and OWNER='&&owner'
;


or



BEGIN
for i in (select OWNER,TABLE_NAME from DBA_TAB_STATISTICS  where STALE_STATS='YES' and OWNER='ANUJ'
         ) loop
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
     ownname          => i.OWNER
    ,TabName           => i.TABLE_NAME
    ,Estimate_Percent  => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
    ,Method_Opt        => 'FOR ALL COLUMNS SIZE AUTO '
    ,Degree            => NULL
    ,Cascade           => DBMS_STATS.AUTO_CASCADE
    ,No_Invalidate     => DBMS_STATS.AUTO_INVALIDATE
    ,Force             => FALSE);
end loop;
END;
/



ww
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname    => 'ANUJ',tabname    => 'T100', degree => DBMS_STATS.AUTO_DEGREE,cascade => DBMS_STATS.AUTO_CASCADE, method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

====


change table name

set linesize 100 pagesize 300
define tabname='EMP'
define owner='ANUJ'

select rpad('ANDV_ALGO_INTERNAL_OBSERVE : ',50)||dbms_stats.get_prefs(pname=>'ANDV_ALGO_INTERNAL_OBSERVE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('APPROXIMATE_NDV : ',50)||dbms_stats.get_prefs(pname=>'APPROXIMATE_NDV', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('APPROXIMATE_NDV_ALGORITHM : ',50)||dbms_stats.get_prefs(pname=>'APPROXIMATE_NDV_ALGORITHM', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('AUTO_STAT_EXTENSIONS : ',50)||dbms_stats.get_prefs(pname=>'AUTO_STAT_EXTENSIONS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('AUTOSTATS_TARGET : ',50)||dbms_stats.get_prefs(pname=>'AUTOSTATS_TARGET', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('CASCADE : ',50)||dbms_stats.get_prefs(pname=>'CASCADE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('CONCURRENT : ',50)||dbms_stats.get_prefs(pname=>'CONCURRENT', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('DEBUG : ',50)||dbms_stats.get_prefs(pname=>'DEBUG', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('DEGREE : ',50)||dbms_stats.get_prefs(pname=>'DEGREE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('ENABLE_HYBRID_HISTOGRAMS : ',50)||dbms_stats.get_prefs(pname=>'ENABLE_HYBRID_HISTOGRAMS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('ENABLE_TOP_FREQ_HISTOGRAMS : ',50)||dbms_stats.get_prefs(pname=>'ENABLE_TOP_FREQ_HISTOGRAMS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('ESTIMATE_PERCENT : ',50)||dbms_stats.get_prefs(pname=>'ESTIMATE_PERCENT', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('GATHER_AUTO : ',50)||dbms_stats.get_prefs(pname=>'GATHER_AUTO', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('GATHER_SCAN_RATE : ',50)||dbms_stats.get_prefs(pname=>'GATHER_SCAN_RATE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('GLOBAL_TEMP_TABLE_STATS : ',50)||dbms_stats.get_prefs(pname=>'GLOBAL_TEMP_TABLE_STATS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('GRANULARITY : ',50)||dbms_stats.get_prefs(pname=>'GRANULARITY', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('INCREMENTAL : ',50)||dbms_stats.get_prefs(pname=>'INCREMENTAL', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('INCREMENTAL_INTERNAL_CONTROL : ',50)||dbms_stats.get_prefs(pname=>'INCREMENTAL_INTERNAL_CONTROL', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('INCREMENTAL_LEVEL : ',50)||dbms_stats.get_prefs(pname=>'INCREMENTAL_LEVEL', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('INCREMENTAL_STALENESS : ',50)||dbms_stats.get_prefs(pname=>'INCREMENTAL_STALENESS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('JOB_OVERHEAD : ',50)||dbms_stats.get_prefs(pname=>'JOB_OVERHEAD', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('JOB_OVERHEAD_PERC : ',50)||dbms_stats.get_prefs(pname=>'JOB_OVERHEAD_PERC', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('METHOD_OPT : ',50)||dbms_stats.get_prefs(pname=>'METHOD_OPT', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('NO_INVALIDATE : ',50)||dbms_stats.get_prefs(pname=>'NO_INVALIDATE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('OPTIONS : ',50)||dbms_stats.get_prefs(pname=>'OPTIONS', tabname=>'&&TABLE' ,ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('PREFERENCE_OVERRIDES_PARAMETER : ',50)||dbms_stats.get_prefs(pname=>'PREFERENCE_OVERRIDES_PARAMETER', tabname=>'&&TABLE' ,ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('PUBLISH : ',50)||dbms_stats.get_prefs(pname=>'PUBLISH', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('SCAN_RATE : ',50)||dbms_stats.get_prefs(pname=>'SCAN_RATE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('STALE_PERCENT : ',50)||dbms_stats.get_prefs(pname=>'STALE_PERCENT', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('STAT_CATEGORY : ',50)||dbms_stats.get_prefs(pname=>'STAT_CATEGORY', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('SYS_FLAGS : ',50)||dbms_stats.get_prefs(pname=>'SYS_FLAGS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('TRACE : ',50)||dbms_stats.get_prefs(pname=>'TRACE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('WAIT_TIME_TO_UPDATE_STATS : ',50)||dbms_stats.get_prefs(pname=>'WAIT_TIME_TO_UPDATE_STATS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE
-- 19c
--select rpad('AUTO_TASK_INTERVAL : ',50)||dbms_stats.get_prefs(pname=>'AUTO_TASK_INTERVAL', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
--select rpad('AUTO_TASK_MAX_RUN_TIME : ',50)||dbms_stats.get_prefs(pname=>'AUTO_TASK_MAX_RUN_TIME', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
--select rpad('AUTO_TASK_STATUS : ',50)||dbms_stats.get_prefs(pname=>'AUTO_TASK_STATUS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
--select rpad('ROOT_TRIGGER_PDB : ',50)||dbms_stats.get_prefs(pname=>'ROOT_TRIGGER_PDB', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
--select rpad('COORDINATOR_TRIGGER_SHARD : ',50)||dbms_stats.get_prefs(pname=>'COORDINATOR_TRIGGER_SHARD', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
--select rpad('MAINTAIN_STATISTICS_STATUS : ',50)||dbms_stats.get_prefs(pname=>'MAINTAIN_STATISTICS_STATUS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
FROM dual;

PREFS_FOR__EMP
----------------------------------------------------------------------------------------------------
ANDV_ALGO_INTERNAL_OBSERVE :                      FALSE
APPROXIMATE_NDV :                                 TRUE
APPROXIMATE_NDV_ALGORITHM :                       REPEAT OR HYPERLOGLOG
AUTO_STAT_EXTENSIONS :                            OFF
AUTOSTATS_TARGET :                                AUTO
CASCADE :                                         DBMS_STATS.AUTO_CASCADE
CONCURRENT :                                      OFF
DEBUG :                                           0
DEGREE :                                          NULL
ENABLE_HYBRID_HISTOGRAMS :                        3
ENABLE_TOP_FREQ_HISTOGRAMS :                      3
ESTIMATE_PERCENT :                                DBMS_STATS.AUTO_SAMPLE_SIZE
GATHER_AUTO :                                     AFTER_LOAD
GATHER_SCAN_RATE :                                HADOOP_ONLY
GLOBAL_TEMP_TABLE_STATS :                         SESSION
GRANULARITY :                                     AUTO
INCREMENTAL :                                     FALSE
INCREMENTAL_INTERNAL_CONTROL :                    TRUE
INCREMENTAL_LEVEL :                               PARTITION
INCREMENTAL_STALENESS :                           ALLOW_MIXED_FORMAT
JOB_OVERHEAD :                                    -1
JOB_OVERHEAD_PERC :                               1
METHOD_OPT :                                      FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE :                                   DBMS_STATS.AUTO_INVALIDATE
OPTIONS :                                         GATHER
PREFERENCE_OVERRIDES_PARAMETER :                  FALSE
PUBLISH :                                         TRUE
SCAN_RATE :                                       0
STALE_PERCENT :                                   10
STAT_CATEGORY :                                   OBJECT_STATS
SYS_FLAGS :                                       1
TABLE_CACHED_BLOCKS :                             1
TRACE :                                           0
WAIT_TIME_TO_UPDATE_STATS :                       15

34 rows selected.



====

col owner for a20
col table_name for a20
col incremental  for a15
col granularity for a15
col stale_percent for a15
col estimate_percent for a30
col cascade for a30
col method_opt for a30
SELECT 
  owner, table_name,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'INCREMENTAL') incremental,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'GRANULARITY') granularity,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'STALE_PERCENT') stale_percent,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'ESTIMATE_PERCENT') estimate_percent,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'CASCADE') cascade,
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
FROM dba_tables
WHERE table_name like :h
order by owner, table_name;


OWNER                TABLE_NAME           INCREMENTAL     GRANULARITY     STALE_PERCENT   ESTIMATE_PERCENT               CASCADE                        METHOD_OPT
-------------------- -------------------- --------------- --------------- --------------- ------------------------------ ------------------------------ ------------------------------
ANUJ                 EMP                  FALSE           AUTO            10              DBMS_STATS.AUTO_SAMPLE_SIZE    DBMS_STATS.AUTO_CASCADE        FOR ALL COLUMNS SIZE AUTO
ANUJ1                EMP                  FALSE           AUTO            10              DBMS_STATS.AUTO_SAMPLE_SIZE    DBMS_STATS.AUTO_CASCADE        FOR ALL COLUMNS SIZE AUTO


exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
exec dbms_stats.set_table_prefs('SCOTT', EMP', 'STALE_PERCENT', '1');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');

exec dbms_stats.set_table_prefs('SCOTT', EMP', 'STALE_PERCENT', '10');


for trace


 ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
  ALTER SESSION SET EVENTS '10046 trace name context off';

=====

Stats info ...


define tab='EMP'
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

col INDEX_NAME for A20
col TABLE_NAME for A20
col COLUMN_NAME for A20
col OWNER for a20


select OWNER,table_name,stats_update_time     from dba_tab_stats_history     where table_name = '&tab'


-- tables

select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len from dba_tables
where table_name = '&tab';

select OWNER,table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len from dba_tab_pending_stats
where table_name = '&tab' 
and partition_name is null;


-- indexes
select OWNER,index_name, last_analyzed ANALYZE_TIME, num_rows, leaf_blocks, distinct_keys from dba_indexes
where table_name = '&tab'
order by index_name;


select OWNER,table_name,index_name, last_analyzed "analyze time", num_rows, leaf_blocks, distinct_keys from dba_ind_pending_stats
where table_name = '&tab' 
and partition_name is null
order by index_name;

-- columns
select OWNER,table_name,column_name, last_analyzed ANALYZE_TIME, num_distinct,      num_nulls, density from dba_tab_columns
where table_name = '&tab'
order by OWNER,table_name,column_name;
 

select OWNER,table_name,column_name, last_analyzed "analyze time", num_distinct,  num_nulls, density from dba_col_pending_stats
where table_name = '&tab' 
and partition_name is null
order by column_name;

select OWNER,table_name,stats_update_time from dba_tab_stats_history  where table_name = '&tab';

====

define T_NAME='EMP'
set verify off

set linesize 300
set long 30

column constraint_name format a30
column constraint_type format a2 heading CT
column column_name format a30
column position format 99 heading CP
column r_owner format a30
column r_constraint_name format a30

break on constraint_name skip 1 on constraint_type

select con.constraint_name, con.constraint_type, con.status, con.r_owner, con.r_constraint_name, 
       col.column_name, col.position, con.search_condition
from dba_constraints con,
     dba_cons_columns col
where col.owner = con.owner
      and col.constraint_name = con.constraint_name
    --  and con.owner = 'T_OWNER'
      and con.table_name = '&T_NAME'
order by con.constraint_name, col.position;


====


-- any analysed tables with significant chaining
set pages 200  linesize 300

col table_name form a25 word wrap
col owner      form a15 word wrap
col num_rows   form 999,999,999
col chain_cnt  form 9,999,999
col pct_chn    form 99.99
--accept tab_name char prompt 'which tables are to be checked ? >'
prompt only works on analyzed tables/partitioned tables
--spool tab_chain.lst
select table_name
      ,owner
      ,num_rows
      ,chain_cnt
      ,(nvl(chain_cnt,1)/nvl(num_rows,1))*100 pct_chn
from sys.dba_tables
-- NB keep greatest for num_rows and  percentage in line else a table with
-- (in this case) less than 1000 rows will always be detected.
where greatest(nvl(chain_cnt,1),1) /greatest(nvl(num_rows,1),1000)   > 0.001
and table_name like upper(':h'||'%')
and owner not in ('SYS','SYSTEM')
union
select table_name||' '||partition_name table_name
      ,table_owner                     owner
      ,num_rows
      ,chain_cnt
      ,(nvl(chain_cnt,1)/nvl(num_rows,1))*100 pct_chn
from sys.dba_tab_partitions
-- NB keep greatest for num_rows and  percentage in line else a table with
-- (in this case) less than 1000 rows will always be detected.
where greatest(nvl(chain_cnt,1),1) /greatest(nvl(num_rows,1),1000)   > 0.001
and table_name like upper(':h'||'%')
and table_owner not in ('SYS','SYSTEM')
order by 1,2
/
--spool off
clear colu



====


ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

var h varchar2(30)
 begin :h := 'ANUJ'; end;  ---- User Name !!!!
/    

SELECT 
  username,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'INCREMENTAL') incremental,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'GRANULARITY') granularity,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'STALE_PERCENT') stale_percent,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'ESTIMATE_PERCENT') estimate_percent,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'CASCADE') cascade,
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
FROM dba_users
WHERE username = :h
ORDER BY username;



ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

var h varchar2(30)
 begin :h := 'T101'; end;  ---- table name here!!!!
/    
SELECT 
  owner, table_name,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'INCREMENTAL') incremental,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'GRANULARITY') granularity,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'STALE_PERCENT') stale_percent,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'ESTIMATE_PERCENT') estimate_percent,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'CASCADE') cascade,
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
FROM dba_tables
WHERE table_name = :h
ORDER BY owner, table_name;




exec dbms_stats.set_table_prefs('SCOTT', EMP', 'STALE_PERCENT', '1');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');

exec dbms_stats.set_table_prefs('SCOTT', EMP', 'STALE_PERCENT', '10');

===



alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
set linesize 300
select * from (select owner, table_name,num_rows,last_analyzed from dba_tables where table_name='XXX'),
               (select count(*) from o.XXX);


=======


set head off feed off
set pages 10000  lines 500
col command for a200
select 'exec dbms_stats.gather_table_stats(ownname => '''||owner||''',tabname => '''||table_name ||''', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE,method_opt => '' FOR ALL COLUMNS SIZE AUTO'', degree => 8);' command
from dba_tables 
where owner='TEST';



 set pages 10000  lines 500
col command for a250
select 'exec dbms_stats.gather_index_stats(  ownname=> '''||owner||'''   ,indname=>''' || index_name ||'''  ,estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE , degree=> 6   ,no_invalidate=> DBMS_STATS.AUTO_INVALIDATE  ,granularity=> ''AUTO'');'  command
from dba_indexes 
where owner ='TEST';


 SELECT 'Exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'''||table_owner||''',tabname=>'''||table_name||''',partname=>'''||partition_name||''',
method_opt=>''FOR ALL COLUMS SIZE 1",estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE=>TRUE,DEGREE=>6);' 
from DBA_TAB_PARTITIONS where table_owner in ('SCHEMA');



=========


-- DBA_TAB_STATS_HISTORY provides a history of table statistics modifications for all tables in the database



define owner='TTTTT'
define table='XXXX'

col OWNER     format a20                                                                                                                       
col TABLE_NAME format a20
column partition_name format a20
column subpartition_name format a20
set linesize 500
set pages 9999
select owner,table_name,partition_name,subpartition_name,stats_update_time from dba_tab_stats_history where owner='&owner' and table_name='&table'
order by stats_update_time;





set long 2000000
set pagesize 1000

select * from table(dbms_stats.diff_table_stats_in_history(
                    ownname => '&owner',
                    tabname => '&table',
                    time1 => systimestamp,
                    time2 => to_timestamp('10-MAR-22 07.47.19 PM','DD-MON-YY hh12.mi.ss AM'),
                    pctthreshold => 0));  


======================



object_dependent_segments 



  object_type_table = 1
  object_type_nested_table = 2
  object_type_index = 3
  object_type_cluster = 4
  object_type_table_partition = 7
  object_type_index_partition = 8
  object_type_table_subpartition = 9
  object_type_index_subpartition = 10
  object_type_mv = 13
  object_type_mvlog = 14




set linesize 200
col segment_owner format a25
col segment_name format a25
col segment_type format a20
col tablespace_name format a20
col partition_name format a20
col lob_column_name format a12
set serveroutput on
  select
     segment_owner,
     segment_name,
     segment_type,
     tablespace_name
  from
     (table(dbms_space.object_dependent_segments(
               objowner => 'SCOTT',
             objname     => 'EMP',
             partname    => NULL,
             objtype     => 1)));
 
 
 
SEGMENT_OWNER             SEGMENT_NAME              SEGMENT_TYPE         TABLESPACE_NAME
------------------------- ------------------------- -------------------- --------------------
SCOTT                     EMP                       TABLE                USERS
SCOTT                     PK_EMP                    INDEX                USERS
SCOTT                     IX_DEPTNO                 INDEX                USERS





*****************************



define IF_COMMENT=' '
--define IF_COMMENT='--' 

define schema='SCOTT'
define table_name ='EMP'

SET ECHO        OFF 
SET FEEDBACK    10  HEADING     ON  LINESIZE    180  LONG   9000  PAGESIZE 300 TERMOUT ON  TIMING  OFF  TRIMOUT  ON  TRIMSPOOL   ON VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | TABLE INFORMATION                                                      |
PROMPT +------------------------------------------------------------------------+

COLUMN owner               FORMAT a20                   HEADING "Owner"
COLUMN table_name          FORMAT a30                   HEADING "Table Name"
COLUMN tablespace_name     FORMAT a30                   HEADING "Tablespace"
COLUMN last_analyzed       FORMAT a23                   HEADING "Last Analyzed"
COLUMN num_rows            FORMAT 9,999,999,999,999     HEADING "# of Rows"

SELECT
    owner
  , table_name
  , tablespace_name
  , TO_CHAR(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') last_analyzed
  , num_rows
FROM
    dba_tables
WHERE 1=1
   &IF_COMMENT and    owner      = UPPER('&schema')
 AND table_name = UPPER('&table_name')
/

PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | OBJECT INFORMATION                                                     |
PROMPT +------------------------------------------------------------------------+

COLUMN object_id                                     HEADING "Object ID"
COLUMN data_object_id                                HEADING "Data Object ID"
COLUMN created             FORMAT A23                HEADING "Created"
COLUMN last_ddl_time       FORMAT A23                HEADING "Last DDL"
COLUMN status                                        HEADING "Status"

SELECT
    object_id
  , data_object_id
  , TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS')        created
  , TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS')  last_ddl_time
  , status
FROM
    dba_objects
WHERE  1=1
    &IF_COMMENT and     owner       = UPPER('&schema')
  AND object_name = UPPER('&table_name')
  AND object_type = 'TABLE'
/

PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | SEGMENT INFORMATION                                                    |
PROMPT +------------------------------------------------------------------------+

COLUMN segment_name        FORMAT a30                HEADING "Segment Name"
COLUMN partition_name      FORMAT a30                HEADING "Partition Name"
COLUMN segment_type        FORMAT a16                HEADING "Segment Type"
COLUMN tablespace_name     FORMAT a30                HEADING "Tablespace"
COLUMN num_rows            FORMAT 9,999,999,999,999  HEADING "Num Rows"
COLUMN bytes               FORMAT 9,999,999,999,999  HEADING "Bytes"
COLUMN last_analyzed       FORMAT a23                HEADING "Last Analyzed"

SELECT 
    seg.segment_name      segment_name
  , null                  partition_name
  , seg.segment_type      segment_type
  , seg.tablespace_name   tablespace_name
  , tab.num_rows          num_rows
  , seg.bytes             bytes
  , TO_CHAR(tab.last_analyzed, 'DD-MON-YYYY HH24:MI:SS') last_analyzed
from
    dba_segments seg
  , dba_tables tab
WHERE  1=1
   &IF_COMMENT and      seg.owner = UPPER('&schema')
  AND seg.segment_name = UPPER('&table_name')
  AND seg.segment_name = tab.table_name
  AND seg.owner = tab.owner
  AND seg.segment_type = 'TABLE'
UNION ALL
SELECT 
    seg.segment_name      segment_name
  , seg.partition_name    partition_name
  , seg.segment_type      segment_type
  , seg.tablespace_name   tablespace_name
  , part.num_rows         num_rows
  , seg.bytes             bytes
  , TO_CHAR(part.last_analyzed, 'DD-MON-YYYY HH24:MI:SS') last_analyzed
FROM
    dba_segments seg
  , dba_tab_partitions part
WHERE  1=1
    &IF_COMMENT and     part.table_owner = UPPER('&schema')
  AND part.table_name = UPPER('&table_name')
  AND part.partition_name = seg.partition_name
  AND seg.segment_type = 'TABLE PARTITION'
ORDER BY
    segment_name
  , partition_name
/


PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | COLUMNS                                                                |
PROMPT +------------------------------------------------------------------------+

COLUMN column_name         FORMAT a30                HEADING "Column Name"
COLUMN data_type           FORMAT a25                HEADING "Data Type"
COLUMN nullable            FORMAT a13                HEADing "Null?"

SELECT
    column_name
  , DECODE(nullable, 'Y', ' ', 'NOT NULL') nullable
  , DECODE(data_type
               , 'RAW',      data_type || '(' ||  data_length || ')'
               , 'CHAR',     data_type || '(' ||  data_length || ')'
               , 'VARCHAR',  data_type || '(' ||  data_length || ')'
               , 'VARCHAR2', data_type || '(' ||  data_length || ')'
               , 'NUMBER', NVL2(   data_precision
                                 , DECODE(    data_scale
                                            , 0
                                            , data_type || '(' || data_precision || ')'
                                            , data_type || '(' || data_precision || ',' || data_scale || ')'
                                   )
                                 , data_type)
               , data_type
    ) data_type
FROM
    dba_tab_columns
WHERE  1=1
     &IF_COMMENT and    owner      = UPPER('&schema')
  AND table_name = UPPER('&table_name')
ORDER BY
    column_id
/


PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | INDEXES                                                                |
PROMPT +------------------------------------------------------------------------+

COLUMN index_name          FORMAT a40                HEADING "Index Name"
COLUMN column_name         FORMAT a30                HEADING "Column Name"
COLUMN column_length                                 HEADING "Column Length"

BREAK ON index_name SKIP 1

SELECT 
    index_owner || '.' || index_name  index_name
  , column_name
  , column_length
FROM
    dba_ind_columns
WHERE  1=1
    &IF_COMMENT and     table_owner  = UPPER('&schema')
  AND table_name   = UPPER('&table_name')
ORDER BY
    index_name
  , column_position
/


PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | CONSTRAINTS                                                            |
PROMPT +------------------------------------------------------------------------+

COLUMN constraint_name     FORMAT a30                HEADING "Constraint Name"
COLUMN constraint_type     FORMAT a13                HEADING "Constraint|Type"
COLUMN search_condition    FORMAT a30                HEADING "Search Condition"
COLUMN r_constraint_name   FORMAT a30                HEADING "R / Constraint Name"
COLUMN delete_rule         FORMAT a12                HEADING "Delete Rule"
COLUMN status                                        HEADING "Status"

BREAK ON constraint_name ON constraint_type

SELECT 
    a.constraint_name
  , DECODE(a.constraint_type
             , 'P', 'Primary Key'
             , 'C', 'Check'
             , 'R', 'Referential'
             , 'V', 'View Check'
             , 'U', 'Unique'
             , a.constraint_type
    ) constraint_type
  , b.column_name
  , a.search_condition
  , NVL2(a.r_owner, a.r_owner || '.' ||  a.r_constraint_name, null) r_constraint_name
  , a.delete_rule
  , a.status
FROM 
    dba_constraints  a
  , dba_cons_columns b
WHERE  1=1
    &IF_COMMENT and     a.owner            = UPPER('&schema')
  AND a.table_name       = UPPER('&table_name')
  AND a.constraint_name  = b.constraint_name
  AND b.owner            = UPPER('&schema')
  AND b.table_name       = UPPER('&table_name')
ORDER BY
    a.constraint_name
  , b.position
/


PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | PARTITIONS (TABLE)                                                     |
PROMPT +------------------------------------------------------------------------+

COLUMN partition_name                                HEADING "Partition Name"
COLUMN column_name         FORMAT a30                HEADING "Column Name"
COLUMN tablespace_name     FORMAT a30                HEADING "Tablespace"
COLUMN composite           FORMAT a9                 HEADING "Composite"
COLUMN subpartition_count                            HEADING "Sub. Part.|Count"
COLUMN logging             FORMAT a7                 HEADING "Logging"
COLUMN high_value          FORMAT a13                HEADING "High Value" TRUNC

BREAK ON partition_name

SELECT
    a.partition_name
  , b.column_name
  , a.tablespace_name
  , a.composite
  , a.subpartition_count
  , a.logging
FROM 
    dba_tab_partitions    a
  , dba_part_key_columns  b
WHERE  1=1
   &IF_COMMENT and      a.table_owner        = UPPER('&schema')
  AND a.table_name         = UPPER('&table_name')
  AND RTRIM(b.object_type) = 'TABLE'
  AND b.owner              = a.table_owner
  AND b.name               = a.table_name
ORDER BY
    a.partition_position
  , b.column_position
/


PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | PARTITIONS (INDEX)                                                     |
PROMPT +------------------------------------------------------------------------+

COLUMN index_name              FORMAT a30                HEADING "Index Name"
COLUMN partitioning_type       FORMAT a9                 HEADING "Type"
COLUMN partition_count         FORMAT 99999              HEADING "Part.|Count"
COLUMN partitioning_key_count  FORMAT 99999              HEADING "Part.|Key Count"
COLUMN locality                FORMAT a8                 HEADING "Locality"
COLUMN alignment               FORMAT a12                HEADING "Alignment"

SELECT
    a.owner || '.' || a.index_name   index_name
  , b.column_name
  , a.partitioning_type
  , a.partition_count
  , a.partitioning_key_count
  , a.locality
  , a.alignment
FROM 
    dba_part_indexes      a
  , dba_part_key_columns  b
WHERE  1=1
   &IF_COMMENT and      a.owner              = UPPER('&schema')
  AND a.table_name         = UPPER('&table_name')
  AND RTRIM(b.object_type) = 'INDEX'
  AND b.owner              = a.owner
  AND b.name               = a.index_name
ORDER BY
    a.index_name
  , b.column_position
/



PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | TRIGGERS                                                               |
PROMPT +------------------------------------------------------------------------+

COLUMN trigger_name            FORMAT a30                HEADING "Trigger Name"
COLUMN trigger_type            FORMAT a18                HEADING "Type"
COLUMN triggering_event        FORMAT a9                 HEADING "Trig.|Event"
COLUMN referencing_names       FORMAT a65                HEADING "Referencing Names" newline
COLUMN when_clause             FORMAT a65                HEADING "When Clause" newline
COLUMN trigger_body            FORMAT a65                HEADING "Trigger Body" newline

SELECT
    owner || '.' || trigger_name  trigger_name
  , trigger_type
  , triggering_event
  , status
  , referencing_names
  , when_clause
  , trigger_body
FROM
    dba_triggers
WHERE  1=1
    &IF_COMMENT and  table_owner = UPPER('&schema')
  AND table_name  = UPPER('&table_name')
ORDER BY
     trigger_name
/

====


Script to Show Objects That are Missing Statistics ( Doc ID 957993.1 )

SET LINESIZE 300 PAGESIZE 300 time on timing on 
COL OWNER FOR A20
COL OBJECT_NAME FOR A40
col stale_stats for a14
col stattype_locked for a18

select /*+ PARALLEL(8) */  * from (
SELECT 0 "con_id" ,'TABLE' object_type,owner, table_name object_name, last_analyzed, stattype_locked, stale_stats FROM all_tab_statistics
WHERE (last_analyzed IS NULL OR stale_stats = 'YES') and stattype_locked IS NULL
and owner NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' )
AND owner NOT LIKE 'FLOW%'
and owner NOT LIKE 'APEX%'
union all
SELECT con_id,'TABLE' object_type,owner, table_name object_name, last_analyzed, stattype_locked, stale_stats FROM cdb_tab_statistics
WHERE (last_analyzed IS NULL OR stale_stats = 'YES') and stattype_locked IS NULL
and owner NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' )
AND owner NOT LIKE 'FLOW%'
and owner NOT LIKE 'APEX%'
UNION ALL
SELECT 0,'INDEX' object_type,owner, index_name object_name,  last_analyzed, stattype_locked, stale_stats FROM all_ind_statistics
WHERE (last_analyzed IS NULL OR stale_stats = 'YES') and stattype_locked IS NULL
AND owner NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' )
AND owner NOT LIKE 'FLOW%'
and owner NOT LIKE 'APEX%'
UNION ALL
SELECT con_id ,'INDEX' object_type,owner, index_name object_name,  last_analyzed, stattype_locked, stale_stats FROM cdb_ind_statistics
WHERE (last_analyzed IS NULL OR stale_stats = 'YES') and stattype_locked IS NULL
AND owner NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' )
AND owner NOT LIKE 'FLOW%'
and owner NOT LIKE 'APEX%'
ORDER BY object_type desc, owner, object_name
)



col last_analyzed for a13


prompt 'Statistics for SYS tables'
SELECT NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) dictionary_tables
FROM dba_tables
WHERE owner = 'SYS'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

prompt 'Statistics for Fixed Objects'
select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;




==




set linesize 500 pagesize 300
define tables_list='EMP'

--SET LONG 200 LONGC 20

COL index_and_column_name FOR A70
COL table_and_column_name FOR A70
COL data_type FOR A20
COL data_default FOR A20
COL low_value FOR A32
COL high_value FOR A32
COL low_value_translated FOR A32
COL high_value_translated FOR A32
col TABLE_AND_COLUMN_NAME for a25
PRO
PRO Table Columns 
PRO ~~~~~~~~~~~~~

SELECT c.owner||'.'||c.table_name||' '||c.column_name table_and_column_name,
       c.data_type,
       c.nullable,
       c.data_default,
       c.num_distinct,
       CASE WHEN c.data_type = 'NUMBER' THEN to_char(utl_raw.cast_to_number(c.low_value))
        WHEN c.data_type IN ('VARCHAR2', 'CHAR') THEN SUBSTR(to_char(utl_raw.cast_to_varchar2(c.low_value)),1,32)
        WHEN c.data_type IN ('NVARCHAR2','NCHAR') THEN SUBSTR(to_char(utl_raw.cast_to_nvarchar2(c.low_value)),1,32)
        WHEN c.data_type = 'BINARY_DOUBLE' THEN to_char(utl_raw.cast_to_binary_double(c.low_value))
        WHEN c.data_type = 'BINARY_FLOAT' THEN to_char(utl_raw.cast_to_binary_float(c.low_value))
        WHEN c.data_type = 'DATE' THEN rtrim(
                    ltrim(to_char(100*(to_number(substr(c.low_value,1,2) ,'XX')-100) + (to_number(substr(c.low_value,3,2) ,'XX')-100),'0000'))||'-'||
                    ltrim(to_char(     to_number(substr(c.low_value,5,2) ,'XX')  ,'00'))||'-'||
                    ltrim(to_char(     to_number(substr(c.low_value,7,2) ,'XX')  ,'00'))||'/'||
                    ltrim(to_char(     to_number(substr(c.low_value,9,2) ,'XX')-1,'00'))||':'||
                    ltrim(to_char(     to_number(substr(c.low_value,11,2),'XX')-1,'00'))||':'||
                    ltrim(to_char(     to_number(substr(c.low_value,13,2),'XX')-1,'00')))
        WHEN c.data_type LIKE 'TIMESTAMP%' THEN rtrim(
                    ltrim(to_char(100*(to_number(substr(c.low_value,1,2) ,'XX')-100) + (to_number(substr(c.low_value,3,2) ,'XX')-100),'0000'))||'-'||
                    ltrim(to_char(     to_number(substr(c.low_value,5,2) ,'XX')  ,'00'))||'-'||
                    ltrim(to_char(     to_number(substr(c.low_value,7,2) ,'XX')  ,'00'))||'/'||
                    ltrim(to_char(     to_number(substr(c.low_value,9,2) ,'XX')-1,'00'))||':'||
                    ltrim(to_char(     to_number(substr(c.low_value,11,2),'XX')-1,'00'))||':'||
                    ltrim(to_char(     to_number(substr(c.low_value,13,2),'XX')-1,'00'))||'.'||
                    to_number(substr(c.low_value,15,8),'XXXXXXXX'))
       END low_value_translated,
       CASE WHEN c.data_type = 'NUMBER' THEN to_char(utl_raw.cast_to_number(c.high_value))
        WHEN c.data_type IN ('VARCHAR2', 'CHAR') THEN SUBSTR(to_char(utl_raw.cast_to_varchar2(c.high_value)),1,32)
        WHEN c.data_type IN ('NVARCHAR2','NCHAR') THEN SUBSTR(to_char(utl_raw.cast_to_nvarchar2(c.high_value)),1,32)
        WHEN c.data_type = 'BINARY_DOUBLE' THEN to_char(utl_raw.cast_to_binary_double(c.high_value))
        WHEN c.data_type = 'BINARY_FLOAT' THEN to_char(utl_raw.cast_to_binary_float(c.high_value))
        WHEN c.data_type = 'DATE' THEN rtrim(
                    ltrim(to_char(100*(to_number(substr(c.high_value,1,2) ,'XX')-100) + (to_number(substr(c.high_value,3,2) ,'XX')-100),'0000'))||'-'||
                    ltrim(to_char(     to_number(substr(c.high_value,5,2) ,'XX')  ,'00'))||'-'||
                    ltrim(to_char(     to_number(substr(c.high_value,7,2) ,'XX')  ,'00'))||'/'||
                    ltrim(to_char(     to_number(substr(c.high_value,9,2) ,'XX')-1,'00'))||':'||
                    ltrim(to_char(     to_number(substr(c.high_value,11,2),'XX')-1,'00'))||':'||
                    ltrim(to_char(     to_number(substr(c.high_value,13,2),'XX')-1,'00')))
        WHEN c.data_type LIKE 'TIMESTAMP%' THEN rtrim(
                    ltrim(to_char(100*(to_number(substr(c.high_value,1,2) ,'XX')-100) + (to_number(substr(c.high_value,3,2) ,'XX')-100),'0000'))||'-'||
                    ltrim(to_char(     to_number(substr(c.high_value,5,2) ,'XX')  ,'00'))||'-'||
                    ltrim(to_char(     to_number(substr(c.high_value,7,2) ,'XX')  ,'00'))||'/'||
                    ltrim(to_char(     to_number(substr(c.high_value,9,2) ,'XX')-1,'00'))||':'||
                    ltrim(to_char(     to_number(substr(c.high_value,11,2),'XX')-1,'00'))||':'||
                    ltrim(to_char(     to_number(substr(c.high_value,13,2),'XX')-1,'00'))||'.'||
                    to_number(substr(c.high_value,15,8),'XXXXXXXX'))
        END high_value_translated,
       c.density,
       c.num_nulls,
       c.num_buckets,
       c.histogram,
       c.sample_size,
       TO_CHAR(c.last_analyzed, 'YYYY-MM-DD"T"HH24:MI:SS') last_analyzed,
       c.global_stats,
       c.avg_col_len
  FROM dba_tab_cols c
 WHERE 1=1 
-- (c.owner, c.table_name) IN ('&&tables_list')
and c.table_name IN ('&&tables_list')
 ORDER BY
       c.owner,
       c.table_name,
       c.column_name
/




TABLE_AND_COLUMN_NAME     DATA_TYPE            N DATA_DEFAULT         NUM_DISTINCT LOW_VALUE_TRANSLATED             HIGH_VALUE_TRANSLATED               DENSITY  NUM_NULLS NUM_BUCKETS HISTOGRAM       SAMPLE_SIZE LAST_ANALYZED       GLO AVG_COL_LEN
------------------------- -------------------- - -------------------- ------------ -------------------------------- -------------------------------- ---------- ---------- ----------- --------------- ----------- ------------------- --- -----------
ANUJ.EMP COMM             NUMBER               Y                                 4 0                                1400                                    .25    2621440           1 NONE                1048576 2022-12-19T04:01:36 YES           2
ANUJ.EMP DEPTNO           NUMBER               Y                                 3 10                               30                               .333333333          0           1 NONE                3670016 2022-12-19T04:01:36 YES           3
ANUJ.EMP EMPNO            NUMBER               Y                                14 7369                             7934                             1.3624E-07          0          14 FREQUENCY           3670016 2022-12-19T04:01:36 YES           4
ANUJ.EMP ENAME            VARCHAR2             Y                                14 ADAMS                            WARD                             .071428571          0           1 NONE                3670016 2022-12-19T04:01:36 YES           6
ANUJ.EMP HIREDATE         DATE                 Y                                13 1980-12-17/00:00:00              1987-05-23/00:00:00              .076923077          0           1 NONE                3670016 2022-12-19T04:01:36 YES           8
ANUJ.EMP JOB              VARCHAR2             Y                                 5 ANALYST                          SALESMAN                                 .2          0           1 NONE                3670016 2022-12-19T04:01:36 YES           8
ANUJ.EMP MGR              NUMBER               Y                                 6 7566                             7902                             .166666667     262144           1 NONE                3407872 2022-12-19T04:01:36 YES           4
ANUJ.EMP SAL              NUMBER               Y                                12 800                              5000                             .083333333          0           1 NONE                3670016 2022-12-19T04:01:36 YES           4
ANUJ1.EMP COMM            NUMBER               Y                                 4 0                                1400                                    .25         10           1 NONE                     4 2018-03-04T13:30:54 YES            2
ANUJ1.EMP DEPTNO          NUMBER               Y                                 3 10                               30                               .035714286          0           3 FREQUENCY               14 2018-03-04T13:30:54 YES            3
ANUJ1.EMP EMPNO           NUMBER               N                                14 7369                             7934                             .071428571          0           1 NONE                    14 2018-03-04T13:30:54 YE



=====





-- identify statistics



set linesize 300 pagesize 300
define table_name='SCHEDULER$_EVENT_LOG'  ------<<<<

col TABLE_NAME     for a20                 
col INDEX_NAME for a27  
select   table_name ,last_analyzed,stattype_locked,stale_stats, num_rows, blocks,LAST_ANALYZED from dba_tab_statistics 
where    1=1
and table_name = '&table_name'
--and stale_stats ='YES'
;

select   table_name,index_name, blevel, leaf_blocks, distinct_keys,clustering_factor, num_rows ,LAST_ANALYZED from  dba_ind_statistics
where    1=1
-- and index_name = 'XXXX'
and table_name = '&table_name'
;


col COLUMN_NAME for a20
col LOW_VALUE for a30
col HIGH_VALUE for a30
select    table_name, column_name, num_distinct, density,low_value, high_value, histogram from  dba_tab_col_statistics
where    1=1
-- and  table_name = 'XXXX'
and table_name = '&table_name'
;


col ENDPOINT_VALUE for 99999999999999999999999999999999999999999999999999999999999
select   table_name ,  column_name, endpoint_number, endpoint_value from  dba_tab_histograms
where    1=1
-- and  table_name = 'XXXX'
and table_name = '&table_name'
;


col INDEX_OWNER for a20 
col INDEX_NAME for a27 
col PARTITION_NAME for a20
select ds.index_owner, ds.index_name, ds.partition_name, ds.status from dba_ind_partitions ds 
where ds.index_name='xxx'  

col owner for a20 
col index_name for a27
col partition_name for a27
col dba_ind_partitions_status for a15
col dba_indexes_status for a20
select d1.owner,
       d1.index_name,
       d1.partitioned,
       d2.partition_name,
       d1.status     dba_indexes_status,
       d2.status     dba_ind_partitions_status
  from dba_indexes d1, dba_ind_partitions d2
 where     d1.index_name = d2.index_name
       and d1.table_name = '&table_name'

OWNER                INDEX_NAME                  PAR PARTITION_NAME              DBA_INDEXES_STATUS   DBA_IND_PARTITI
-------------------- --------------------------- --- --------------------------- -------------------- ---------------
AUDSYS               SYS_IL0000017948C00097$$    YES SYS_IL_P25515               N/A                  USABLE
AUDSYS               SYS_IL0000017948C00097$$    YES SYS_IL_P25935               N/A                  USABLE
A

column extension format a40
select extension_name, extension from   dba_stat_extensions
where  table_name = '&table_name';



SELECT DS.INDEX_OWNER,
         DS.INDEX_NAME,
         DS.PARTITION_NAME,
         DS.PARTITION_POSITION,
            'ALTER INDEX '
         || INDEX_OWNER
         || '.'
         || INDEX_NAME
         || ' REBUILD PARTITION '
         || PARTITION_NAME
         || ' TABLESPACE '
         || TABLESPACE_NAME
         || ' ONLINE;'
             ONLINE_REBUILD_COMMAND
    FROM DBA_IND_PARTITIONS DS
   WHERE STATUS = 'UNUSABLE' 
   AND DS.INDEX_NAME = '&Index_NAME'
ORDER BY DS.PARTITION_POSITION DESC 
/




The following identifies  potential for Extended Statistics

exec DBMS_STATS.SEED_COL_USAGE(null,null,600);


SET LONG 10000  LONGCHUNKSIZE 7000  LINESIZE 100


Select dbms_stats.report_col_usage('SCOTT','EMP') from dual ;



COLUMN USAGE REPORT FOR SCOTT.EMP
.................................

1. EMPNO                               : EQ
2. ENAME                               : EQ
###############################################################################




EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression


======


size

define owner='SCOTT'
define tbl_name='EMP'

set linesize 200
col TABLE_NAME for a20
SELECT
SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
ROUND(BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,
ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) USED_MB,
ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) - (AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"
FROM DBA_TABLES
WHERE 1=1
and NUM_ROWS IS NOT NULL 
AND OWNER = upper('&owner') 
AND table_name = upper('&tbl_name')
ORDER BY 4 DESC
/





set linesize 500 
col INDEX_OWNER for a20
col INDEX_NAME for a20
col PARTITION_NAME for a20


define OWNERNAME='SCOTT'
define TABLENAME='EMP'



SELECT index_owner, index_name, partition_name, leaf_blocks, pct_free, col_len, num_rows, ini_trans,
  TRUNC (leaf_blocks - ( (num_rows * (col_len + 10)) / ( (block_size - 66 - ini_trans * 24) * (1 - pct_free / 100)))) extra_blocks, 
  ROUND (100 * (1 - (leaf_blocks - ( (num_rows * (col_len + 10)) / ( (block_size - 66 - ini_trans * 24) * (1 - pct_free / 100)))) / leaf_blocks), 2) density,
  ROUND (leaf_blocks * 8 / 1024 / 1024, 2) GB,
  ROUND (TRUNC (leaf_blocks - ( (num_rows * (col_len + 10)) / ( (block_size - 66 - ini_trans * 24) * (1 - pct_free / 100)))) * 8 / 1024 / 1024, 2) extra_GB, 
  ROUND (TRUNC (leaf_blocks - ( (num_rows * (col_len + 10)) / ( (block_size - 66 - ini_trans * 24) * (1 - 0 / 100)))) * 8 / 1024 / 1024, 2) extra_GB_0_PCTFREE
FROM
  (
    SELECT ip.index_owner, ip.index_name, ip.partition_name, ip.leaf_blocks, ip.num_rows, ip.pct_free, ip.ini_trans,
      cl.col_len, ts.block_size
    FROM dba_ind_partitions ip, dba_indexes i, dba_tablespaces ts, (
        SELECT ic.index_owner, ic.index_name, tc.partition_name, SUM (tc.avg_col_len) col_len
        FROM dba_ind_columns ic, DBA_PART_COL_STATISTICS tc
        WHERE ic.table_owner = '&OWNERNAME'
        AND ic.table_name = '&TABLENAME'
        AND tc.owner = ic.table_owner
        AND tc.table_name = ic.table_name
        AND tc.column_name = ic.column_name
        GROUP BY ic.index_owner, ic.index_name, partition_name
      )
      cl
    WHERE ip.index_owner = i.owner
    AND ip.index_name = i.index_name
    AND i.table_owner = '&OWNERNAME'
    AND i.table_name = '&TABLENAME'
    AND cl.index_owner = i.owner
    AND cl.index_name = i.index_name
    AND cl.partition_name = ip.partition_name
    AND ip.tablespace_name = ts.tablespace_name
    AND ip.leaf_blocks > 0
    UNION ALL
    SELECT i.owner index_owner, i.index_name, NULL partition_name, i.leaf_blocks, i.num_rows, i.pct_free, i.ini_trans,
      cl.col_len, ts.block_size
    FROM dba_indexes i, dba_tablespaces ts, (
        SELECT ic.index_owner, ic.index_name, SUM (tc.avg_col_len) col_len
        FROM dba_ind_columns ic, DBA_TAB_COL_STATISTICS tc
        WHERE ic.table_owner = '&OWNERNAME'
        AND ic.table_name = '&TABLENAME'
        AND tc.owner = ic.table_owner
        AND tc.table_name = ic.table_name
        AND tc.column_name = ic.column_name
        GROUP BY ic.index_owner, ic.index_name
      )
      cl
    WHERE i.table_owner = '&OWNERNAME'
    AND i.table_name = '&TABLENAME'
    AND i.partitioned = 'NO'
    AND cl.index_owner = i.owner
    AND cl.index_name = i.index_name
    AND i.tablespace_name = ts.tablespace_name
    AND i.leaf_blocks > 0    
  )
ORDER BY index_owner, index_name, partition_name ;


INDEX_OWNER          INDEX_NAME           PARTITION_NAME       LEAF_BLOCKS   PCT_FREE    COL_LEN   NUM_ROWS  INI_TRANS EXTRA_BLOCKS    DENSITY         GB   EXTRA_GB EXTRA_GB_0_PCTFREE
-------------------- -------------------- -------------------- ----------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ------------------
SCOTT                PK_EMP                                              1         10          4         14          2            0        2.7          0          0                  0


====





set linesize 300 pagesize 300
col EXTENSION for a30
WITH i as ( /*composite indexes*/
SELECT i.table_owner, i.table_name, i.owner index_owner, i.index_name, i.distinct_keys
,        '('||(LISTAGG('"'||c.column_name||'"',',') WITHIN GROUP (order by c.column_position))||')' column_list
FROM    dba_indexes i
,       dba_ind_columns c
WHERE   i.table_owner = c.table_owner
AND     i.table_name = c.table_name
AND     i.owner = c.index_owner
AND     i.index_name = c.index_name
AND     i.table_name NOT LIKE 'BIN$%'
AND     i.table_owner NOT IN ('SPLUNK','FLOWS_030000','ANONYMOUS','APEX_030200','APEX_040000','APEX_040200','APEX_SSO','APPQOSSYS','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','MDSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS')
AND     i.table_owner NOT IN ('SI_INFORMTN_SCHEMA','SQLTXADMIN','SQLTXPLAIN','SYS','SYSMAN','SYSTEM','TRCANLZR','WMSYS','XDB','XS$NULL','PERFSTAT','STDBYPERF','MGDSYS','OJVMSYS')
GROUP BY i.table_owner, i.table_name, i.owner, i.index_name, i.distinct_keys
HAVING COUNT(*) > 1 /*index with more than one column*/
), e as ( /*extended stats*/
SELECT  e.owner, e.table_name, e.extension_name
,       CAST(e.extension AS VARCHAR(1000)) extension
,       se.histogram, se.num_buckets, se.num_distinct
FROM    dba_stat_extensions e
,       dba_tab_col_statistics se
WHERE   e.creator = 'USER'
AND     se.owner = e.owner
AND     se.table_name = e.table_name
AND     se.column_name = e.extension_name
AND     e.table_name NOT LIKE 'BIN$%'
AND     e.owner NOT IN ('SPLUNK','FLOWS_030000','ANONYMOUS','APEX_030200','APEX_040000','APEX_040200','APEX_SSO','APPQOSSYS','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','MDSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS')
AND     e.owner NOT IN ('SI_INFORMTN_SCHEMA','SQLTXADMIN','SQLTXPLAIN','SYS','SYSMAN','SYSTEM','TRCANLZR','WMSYS','XDB','XS$NULL','PERFSTAT','STDBYPERF','MGDSYS','OJVMSYS')
)
SELECT  e.owner, e.table_name
,       'Extension' object_type
,       e.extension_name object_name, e.num_distinct, e.num_buckets, e.extension
,       sc.column_name
,       sc.num_distinct col_num_distinct
,       sc.num_buckets col_num_buckets
,       sc.histogram col_histogram
FROM    e
,       dba_tab_col_statistics sc
WHERE   e.histogram = 'NONE'
AND     e.extension LIKE '%"'||sc.column_name||'"%'
AND     sc.owner = e.owner
AND     sc.table_name = e.table_name
AND     sc.histogram != 'NONE'
AND     sc.num_buckets > 1 /*histogram on column*/
AND     e.num_buckets = 1 /*no histogram on extended stats*/
UNION ALL
SELECT  /*+  NO_MERGE  */ /* 3c.25 */
        i.table_owner, i.table_name
,       'Index' object_type
,       i.index_name object_name, i.distinct_keys, TO_NUMBER(null), i.column_list
,       sc.column_name
,       sc.num_distinct col_num_distinct
,       sc.num_buckets col_num_buckets
,       sc.histogram col_histogram
From    i
,       dba_ind_columns ic
,       dba_tab_col_statistics sc
WHERE   ic.table_owner = i.table_owner
AND     ic.table_name = i.table_name
AND     ic.index_owner = i.index_owner
AND     ic.index_name = i.index_name
AND     sc.owner = i.table_owner
AND     sc.table_name = ic.table_name
AND     sc.column_name = ic.column_name
AND     sc.histogram != 'NONE' 
AND     sc.num_buckets > 1 /*histogram on column*/
AND NOT EXISTS( /*report index if no extension*/
        SELECT 'x'
        FROM    e
        WHERE   e.owner = i.table_owner
        AND     e.table_name = i.table_name
        AND     e.extension = i.column_list)
ORDER BY 1,2,3,4;



----

set pagesize 300 linesize 500
define Owner=''

select u.TIMESTAMP,
t.last_analyzed,
d.owner,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
decode(d.num_rows,0,'Table Stats indicate No Rows',
nvl(TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99'),'Null Value in dba_TAB_MODIFICATIONS')) percent
from dba_tables t,dba_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = t.table_name
and d.table_name = t.table_name
--and d.owner not in ('SYS')
--and d.owner in ('MUSER')
--and (u.inserts > 3000 or u.updates > 3000 or u.deletes > 3000)
and u.TIMESTAMP > sysdate -30
--order by t.last_analyzed
order by 1
/






define table_name='TS$'

SET EMBEDDED ON HEADING ON

COLUMN Ts FORMAT a30
COLUMN Ta FORMAT a30
COLUMN Clu FORMAT a30
COLUMN Pcf FORMAT 99999999999990
COLUMN Pcu FORMAT 99999999999990
COLUMN Int FORMAT 99,999,999,990
COLUMN Mat FORMAT 99,999,999,990
COLUMN Inx FORMAT 99,999,999,990
COLUMN Nxt FORMAT 99,999,999,990
COLUMN Mix FORMAT 99,999,999,990
COLUMN Max FORMAT 99,999,999,990
COLUMN Pci FORMAT 99999999999990
COLUMN Num FORMAT 99,999,999,990
COLUMN Blo FORMAT 99,999,999,990
COLUMN Emp FORMAT 99,999,999,990
COLUMN Avg FORMAT 99,999,999,990
COLUMN Cha FORMAT 99,999,999,990
COLUMN Rln FORMAT 99,999,999,990
COLUMN Hdg FORMAT a30 newline
SET HEADING OFF
SELECT 'Table Name' Hdg, table_name Ta,
       'Tablespace_name' Hdg, tablespace_name Ts,
       'Cluster Name' Hdg, cluster_name Clu,
       '% Free' Hdg, Pct_Free Pcf,
       '% Used' Hdg, Pct_Used Pcu,
       'Ini Trans' Hdg, Ini_Trans Int,
       'Max Trans' Hdg, Max_Trans Mat,
       'Initial Extent (K)' Hdg, Initial_Extent/1024 Inx,
       'Next Extent (K)' Hdg, Next_extent/1024 Nxt,
       'Min Extents' Hdg, Min_extents Mix,
       'Max Extents' Hdg, Max_extents Max,
       '% Increase' Hdg, Pct_Increase Pci,
       'Number of Rows' Hdg, Num_Rows Num,
       'Number of Blocks' Hdg, Blocks Blo,
       'Number of Empty Blocks' Hdg, Empty_Blocks Emp,
       'Average Space' Hdg, Avg_Space Avg,
       'Chain Count' Hdg, Chain_Cnt Cha,
       'Average Row Length' Hdg, Avg_Row_len Rln
FROM dba_tables
where 1=1
and  table_name = UPPER('&&table_name')
--and rownum <3
--AND owner=UPPER('tab_owner')
/

Table Name                     TS$
Tablespace_name                SYSTEM
Cluster Name                   C_TS#
% Free                                       0
% Used                                       0
Ini Trans                                    0
Max Trans                                    0
Initial Extent (K)                          64
Next Extent (K)                          1,024
Min Extents                                  1
Max Extents                      2,147,483,645
% Increase
Number of Rows                               6

Number of Blocks                             7
Number of Empty Blocks                       0
Average Space                                0
Chain Count                                  0
Average Row Length                          90


====


 set linesize 300 pagesize 300 
 col COLUMN_NAME for a20 
 col DATA_TYPE for a20 
 col LOW_VALUE for a20 
 col HIGH_VALUE for a20
select column_id
     , column_name
     , data_type
     , num_distinct num_vals
     , num_nulls
     , density dnsty
     , case when data_type = 'NUMBER' then to_char(utl_raw.cast_to_number(low_value))
            when data_type = 'VARCHAR2' then to_char(utl_raw.cast_to_varchar2(low_value))
            when data_type = 'NVARCHAR2' then to_char(utl_raw.cast_to_nvarchar2(low_value))
            when data_type = 'BINARY_DOUBLE' then to_char(utl_raw.cast_to_binary_double(low_value))
            when data_type = 'BINARY_FLOAT' then to_char(utl_raw.cast_to_binary_float(low_value))
            when data_type = 'DATE' or data_type like 'TIMESTAMP%' then rtrim(ltrim(to_char(100*(to_number(substr(low_value,1,2),'XX')-100)
+ (to_number(substr(low_value,3,2),'XX')-100),'0000'))||'-'||
ltrim(to_char(to_number(substr(low_value,5,2),'XX'),'00'))||'-'||
ltrim(to_char(to_number(substr(low_value,7,2),'XX'),'00'))||' '||
ltrim(to_char(to_number(substr(low_value,9,2),'XX')-1,'00'))||':'||
ltrim(to_char(to_number(substr(low_value,11,2),'XX')-1,'00'))||':'||
ltrim(to_char(to_number(substr(low_value,13,2),'XX')-1,'00'))) 
           else utl_raw.cast_to_varchar2(low_value)
           end low_value
     , case when data_type = 'NUMBER' then to_char(utl_raw.cast_to_number(high_value))
            when data_type = 'VARCHAR2' then to_char(utl_raw.cast_to_varchar2(high_value))
            when data_type = 'NVARCHAR2' then to_char(utl_raw.cast_to_nvarchar2(high_value))
            when data_type = 'BINARY_DOUBLE' then to_char(utl_raw.cast_to_binary_double(high_value))
            when data_type = 'BINARY_FLOAT' then to_char(utl_raw.cast_to_binary_float(high_value))
            when data_type = 'DATE' or data_type like 'TIMESTAMP%' then rtrim(ltrim(to_char(100*(to_number(substr(high_value,1,2),'XX')-100)
+ (to_number(substr(high_value,3,2),'XX')-100),'0000'))||'-'||
ltrim(to_char(to_number(substr(high_value,5,2),'XX'),'00'))||'-'||
ltrim(to_char(to_number(substr(high_value,7,2),'XX'),'00'))||' '||
ltrim(to_char(to_number(substr(high_value,9,2),'XX')-1,'00'))||':'||
ltrim(to_char(to_number(substr(high_value,11,2),'XX')-1,'00'))||':'||
ltrim(to_char(to_number(substr(high_value,13,2),'XX')-1,'00'))) 
           else utl_raw.cast_to_varchar2(high_value)
           end high_value
from dba_tab_cols
where owner      = 'SCOTT'
  and table_name = 'EMP'
 -- and column_name = 'XXX'
 ;
 
 
 
 
  COLUMN_ID COLUMN_NAME          DATA_TYPE              NUM_VALS  NUM_NULLS      DNSTY LOW_VALUE            HIGH_VALUE
---------- -------------------- -------------------- ---------- ---------- ---------- -------------------- --------------------
         1 EMPNO                NUMBER                       14          0 .071428571 7369                 7934
         2 ENAME                VARCHAR2                     14          0 .071428571 ADAMS                WARD
         3 JOB                  VARCHAR2                      5          0         .2 ANALYST              SALESMAN
         4 MGR                  NUMBER                        6          1 .166666667 7566                 7902
         5 HIREDATE             DATE                         13          0 .076923077 1980-12-17 00:00:00  1987-05-23 00:00:00
         6 SAL                  NUMBER                       12          0 .083333333 800                  5000
         7 COMM                 NUMBER                        6          8 .166666667 0                    1400
         8 DEPTNO               NUMBER                        3          0 .333333333 10                   30

8 rows selected.




								

set linesize 300 pagesize 300
col OWNER       for a20                                                                                                                      
col TABLE_NAME for a27
col PARTITION_NAME for a20 
col SUBPARTITION_NAME for a20
col STATS_UPDATE_TIME for a35 
select * from  dba_TAB_STATS_HISTORY where 1=1
and OWNER ='XXX'
order by 2;



select min(STATS_UPDATE_TIME), max(STATS_UPDATE_TIME)
from  dba_TAB_STATS_HISTORY where 1=1
and OWNER ='XXX'




declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname=>'ANUJ', options=>'LIST STALE',objlist=>mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).objname); 
end loop;
end;
/




SET SERVEROUTPUT ON

DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=> 'ANUJ', objlist=> ObjList, options=> 'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/


	   
set lines 300 pages 300
col table_name 		for a40
col owner 			for a20 
col partition_name 	for a25
col stale_stats 	for a14
col stattype_locked for a15
select distinct owner, table_name, partition_name,num_rows, stale_stats,NUM_ROWS, last_analyzed, stattype_locked   from dba_tab_statistics
where 1=1
--and table_name=:h
and owner='XXX'
and stattype_locked is not null
;

set lines 300 pages 300 numf 999999999999999999999999
col table_name          for a40
col owner               for a20
col partition_name   	for a25
 col stale_stats    	for a14
 col stattype_locked 	for a15
 select distinct owner, table_name, partition_name, stale_stats,num_rows, last_analyzed, stattype_locked   from dba_tab_statistics
 where 1=1
 --and table_name=:h
 and owner='XXX'
 and STALE_STATS!='NO'
 --and stattype_locked is not null
 --and NUM_ROWS>1000
 ;



*************************************************************************

https://anuj-singh.blogspot.com/2011/11/oracle-count-number-of-rows-in-tables.html?m=1


oracle partition row count


define table_owner='USER2'
define TABLE_NAME='SALES'

set serverout on size 1000000
set verify off
declare
sql_stmt varchar2(1024);
row_count number;
cursor get_tab is
select table_name,partition_name
from dba_tab_partitions
where table_owner=upper('&&TABLE_OWNER') 
and table_name='&&TABLE_NAME'
;
begin
dbms_output.put_line('Checking Record Counts for table_name');
dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');
dbms_output.put_line('....');
for get_tab_rec in get_tab loop
BEGIN
sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';

EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name||'('||get_tab_rec.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');
exception when others then
dbms_output.put_line
('Error counting rows for table '||get_tab_rec.table_name);
END;
end loop;
end;
/
set verify on


Table SALES(SALES_AUG)                                   0 rows.
Table SALES(SALES_DEV)                                   0 rows.
Table SALES(SALES_NOV)                                   1 rows.
Table SALES(SALES_OCT)                                   0 rows.
Table SALES(SALES_SEP)                                   0 rows.

PL/SQL procedure successfully completed.


*********************************

table name with digit !!

set linesize 300 pagesize 300
col owner for a20 
select owner ,
       table_name
from sys.dba_tables
where regexp_like (table_name, '[0-9]') 
-- excluding some Oracle maintained schemas
and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000','APEX_030200', 'APEX_PUBLIC_USER','DIP', 
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC', 'WKSYS')
order by owner,     table_name;

====


---!from Web 

define 1='xxx'  -- table name 
define 2='xx'  --- owner


set echo off feed off termout off
set linesize 180 trimspool on
set pagesize 60

repheader off
ttitle off

col num_rows                format a6                   heading 'Num|rows'                  justify right
col avg_row_len             format 9999                 heading 'Avg|len'
col blocks                  format a6                   heading 'Blocks'                    justify right
col seg_size                format 999999999.99              heading 'Segment|GB'
col sample_size             format a6                   heading 'Sample'                    justify right
col user_stats              format a5                   heading 'User|stats'
col temporary               format a2                   heading 'Te|mp'
col degree                  format a3                   heading 'DOP'
col partitioned             format a3                   heading 'Partitioned'
col iot_type                format a3                   

col data_type               format a15                  truncate
col avg_col_len             format 9999                 heading 'Avg|len'
col num_distinct            format a6                   heading 'Num di|stinct'
col density                 format 9.99EEEE
col num_nulls               format a6                   heading 'Num|nulls'                 justify right
col num_buckets             format 999                  heading 'Buck|ets'
col low_value               format a20                                                      trunc
col high_value              format a20                                                      trunc

col index_name              format a30                  
col column_name             format a30                  truncate
col prefix_length           format 99                   heading 'Pre|fix'
col avg_leaf_blocks_per_key format 999,999              heading 'Leafs|per key'
col avg_data_blocks_per_key format 999,999              heading 'Data|per key'
col clustering_factor       format a6                   heading 'Cluste|ring'               justify right
col blevel                  format 99                   heading 'BLev'
col leaf_blocks             format a6                   heading 'Leaf|blocks'               justify right
col distinct_keys           format 999,999,999          heading 'Distinct|keys'
col rows_per_key            format 999,999,999          heading 'Rows per key'

col DENSITY for 9999999999999999999

col p1 new_value 1
col p2 new_value 2
select null p1, null p2 from dual where 1 = 2;



var tab_name varchar2(30)
var owner varchar2(30)
exec :tab_name := upper('&1');
exec :owner := case when upper('&2') is null then user else upper('&2') end;

set termout on

ttitle left 'Table statistics'

select 
table_name,
lpad(case when num_rows < 1e5 then num_rows || ' '
                 when num_rows < 1e8 then round(num_rows / 1e3) || 'K'
                 when num_rows < 1e11 then round(num_rows / 1e6) || 'M'
                 else round(num_rows / 1e9) || 'G'
            end, 6, ' '
       ) num_rows
     , avg_row_len
     , lpad(case when blocks < 1e5 then blocks || ' '
                 when blocks < 1e8 then round(blocks / 1e3) || 'K'
                 when blocks < 1e11 then round(blocks / 1e6) || 'M'
                 else round(blocks / 1e9) || 'G'
            end, 6, ' '
       ) blocks
     , (select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = table_name and owner = :owner) seg_size
     , lpad(case when sample_size < 1e5 then sample_size || ' '
                 when sample_size < 1e8 then round(sample_size / 1e3) || 'K'
                 when sample_size < 1e11 then round(sample_size / 1e6) || 'M'
                 else round(sample_size / 1e9) || 'G'
            end, 6, ' '
       ) sample_size
     , last_analyzed
     , user_stats
     , temporary
     , compression
     , substr(replace(degree, ' '), 1, 3) degree
     , partitioned
     , iot_type
  from dba_tables t 
 where table_name = :tab_name
   and owner = :owner;

ttitle left "Column statistics"

select column_name
     , avg_col_len
     , data_type || decode(data_type, 'NUMBER', '(' || data_precision || decode(data_scale, 0, null, ',' || data_scale) || ')'
                                    , 'VARCHAR2', '(' || data_length || ')'
                                    , null
       ) data_type
     , lpad(case when num_distinct < 1e5 then num_distinct || ' '
                 when num_distinct < 1e8 then round(num_distinct / 1e3) || 'K'
                 when num_distinct < 1e11 then round(num_distinct / 1e6) || 'M'
                 else round(num_distinct / 1e9) || 'G'
            end, 6, ' '
       ) num_distinct
     , density
     , lpad(case when num_nulls < 1e5 then num_nulls || ' '
                 when num_nulls < 1e8 then round(num_nulls / 1e3) || 'K'
                 when num_nulls < 1e11 then round(num_nulls / 1e6) || 'M'
                 else round(num_nulls / 1e9) || 'G'
            end, 6, ' '
       ) num_nulls
     , num_buckets
     , user_stats
     , lpad(case when sample_size < 1e5 then sample_size || ' '
                 when sample_size < 1e8 then round(sample_size / 1e3) || 'K'
                 when sample_size < 1e11 then round(sample_size / 1e6) || 'M'
                 else round(sample_size / 1e9) || 'G'
            end, 6, ' '
       ) sample_size
     , decode(data_type, 'NUMBER', to_char(utl_raw.cast_to_number(low_value)),
                         'VARCHAR2', utl_raw.cast_to_varchar2(low_value),
                         'CHAR', utl_raw.cast_to_varchar2(low_value)
       ) low_value
     , decode(data_type, 'NUMBER', to_char(utl_raw.cast_to_number(high_value)),
                         'VARCHAR2', utl_raw.cast_to_varchar2(high_value),
                         'CHAR', utl_raw.cast_to_varchar2(high_value)
       ) high_value
  from dba_tab_cols
 where table_name = :tab_name
   and owner = :owner
 order by column_id;
 
ttitle left Histogram data for NUMBER columns
select *
  from dba_tab_histograms h
 where 1=0
  

ttitle left "Index statistics"
break on index_name skip 1

select ui.index_name
     , uic.column_name
     , prefix_length
     , blevel
     , lpad(case when leaf_blocks < 1e5 then leaf_blocks || ' '
                 when leaf_blocks < 1e8 then round(leaf_blocks / 1e3) || 'K'
                 when leaf_blocks < 1e11 then round(leaf_blocks / 1e6) || 'M'
                 else round(leaf_blocks / 1e9) || 'G'
            end, 6, ' '
       ) leaf_blocks
     , avg_leaf_blocks_per_key
     , avg_data_blocks_per_key
--     , distinct_keys
     , (num_rows / nullif(distinct_keys, 0)) rows_per_key
     , lpad(case when num_rows < 1e5 then num_rows || ' '
                 when num_rows < 1e8 then round(num_rows / 1e3) || 'K'
                 when num_rows < 1e11 then round(num_rows / 1e6) || 'M'
                 else round(num_rows / 1e9) || 'G'
            end, 6, ' '
       ) num_rows
     , lpad(case when clustering_factor < 1e5 then clustering_factor || ' '
                 when clustering_factor < 1e8 then round(clustering_factor / 1e3) || 'K'
                 when clustering_factor < 1e11 then round(clustering_factor / 1e6) || 'M'
                 else round(clustering_factor / 1e9) || 'G'
            end, 6, ' '
       ) clustering_factor
     , lpad(case when sample_size < 1e5 then sample_size || ' '
                 when sample_size < 1e8 then round(sample_size / 1e3) || 'K'
                 when sample_size < 1e11 then round(sample_size / 1e6) || 'M'
                 else round(sample_size / 1e9) || 'G'
            end, 6, ' '
       ) sample_size
     , user_stats
     , substr(replace(degree, ' '), 1, 3) degree
     , last_analyzed
  from dba_indexes ui
     , dba_ind_columns uic
 where ui.table_name = :tab_name
   and ui.owner = :owner
   and ui.index_name = uic.index_name
   and ui.table_name = uic.table_name
 order by index_name, column_position;


====



set pagesize 50000 linesize 200 trimspool on  long 2000000  long 2000000
col ddl format a100

define v_owner='SCOTT'
define v_table_name='EMP'


var v_owner varchar2(30)
var v_table_name varchar2(30)

begin
	:v_owner := '&&v_owner';
	:v_table_name := '&&v_table_name';
end;
/


col v_owner new_value v_owner noprint
col v_table_name new_value v_table_name noprint

begin
	dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE);
	dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
	dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',TRUE);
	dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE', TRUE);
	dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',TRUE);
	dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICATION',TRUE);
	dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',TRUE);
	dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',TRUE);
end;
/


prompt --########################################
prompt --## TABLE DDL
prompt --########################################

select replace(dbms_metadata.get_ddl('TABLE',:v_table_name,:v_owner),'"','') ddl from dual
/

prompt --########################################
prompt --## INDEX DDL
prompt --########################################

select replace(dbms_metadata.get_ddl('INDEX',i.index_name, i.owner),'"','') ddl
from dba_indexes i
where i.owner = :v_owner
and i.table_name = :v_table_name
/

prompt --########################################
prompt --## PRIMARY KEY
prompt --########################################

select replace(dbms_metadata.get_ddl('CONSTRAINT',c.constraint_name, c.owner),'"','') ddl
from dba_constraints c
where c.owner = :v_owner
and c.table_name = :v_table_name
and c.constraint_type = 'P'
/

prompt --########################################
prompt --## FOREIGN KEYS
prompt --########################################

select replace(dbms_metadata.get_ddl('REF_CONSTRAINT',c.constraint_name, c.owner),'"','') ddl
from dba_constraints c
where c.owner = :v_owner
and c.table_name = :v_table_name
and c.constraint_type = 'R'
/

prompt --########################################
prompt --## CHECK CONSTRAINTS
prompt --########################################

select replace(dbms_metadata.get_ddl('CONSTRAINT',c.constraint_name, c.owner),'"','') ddl
from dba_constraints c
where c.owner = :v_owner
and c.table_name = :v_table_name
and c.constraint_type = 'C'
--and c.generated not like 'GENERATED%'
/

undef 1 2









set linesize 200 trimspool on  pagesize 100

col owner format a30 head 'Owner'
col segment_type format a7 head 'Segment|Type'
 col ini_trans format 999 head 'ITL'
 col ini_trans_count format 99,999 head 'ITL|Count'

 break on owner skip 1 on segment_type

 with data as (
    select owner, 'Table' segment_type,      nvl(ini_trans,0) ini_trans
    from dba_tables
    union all
    select table_owner, 'TabPart' segment_type,     nvl(ini_trans,0) ini_trans
    from dba_tab_partitions
    union all
    select table_owner, 'TabSubPart' segment_type,  nvl(ini_trans,0) ini_trans
    from dba_tab_subpartitions
    union all
    select owner, 'Index' segment_type,      nvl(ini_trans,0) ini_trans
    from dba_indexes
    union all
    select index_owner, 'IndPart' segment_type,     nvl(ini_trans,0) ini_trans
    from dba_ind_partitions
    union all
    select index_owner, 'IndSubPart' segment_type,  nvl(ini_trans,0) ini_trans
    from dba_ind_subpartitions
)
 select owner, segment_type, ini_trans, count(*) ini_trans_count
 from data d
join dba_users u on u.username = d.owner
and u.oracle_maintained = 'N' --------------------------------- <<<<<<------
   group by owner, segment_type, ini_trans
   order by owner, segment_type, ini_trans
/ 



                               Segment          ITL
Owner                          Type     ITL   Count
------------------------------ ------- ---- -------
ANUJXX_TEST                    IndPart    2      30
                               Index      0       3
                               TabPart    1      10
                               Table      0       1




-- Partition count info 

define 1 ='XXXX';  ---owner 

define 2 ='XX';   --Table_name 

set serveroutput on

declare

p_count number;

begin
for i in (select table_name,table_owner,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION from dba_tab_partitions where table_owner='&1' and table_name='&2' order by PARTITION_POSITION) loop
execute immediate 'select /*+ parallel(112) */ count(*) from '||i.table_owner||'.'||i.table_name||' partition('||i.PARTITION_NAME||')' into p_count;
dbms_output.put_line(substr(i.table_name,1,15)||' '|| substr(i.table_owner,1,15)||'  ' ||to_char(i.HIGH_VALUE)||'  ' ||i.PARTITION_NAME||'  '||i.PARTITION_POSITION ||'  '||to_char(p_count));
end loop;
end ;







BEGIN  
dbms_stats.gather_table_stats
(ownname => 'ANUJ'  
tabname =>'table1',  
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
cascade => DBMS_STATS.AUTO_CASCADE,  
method_opt => 'FOR ALL COLUMNS SIZE AUTO',  
degree => DBMS_STATS.DEFAULT_DEGREE); 
END;




7 comments:

Anuj Singh said...





Begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'XXX',
tabname => :h,
degree => 2,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;

Anuj Singh said...


DBA_INDEXES there are some indexes with status 'N/A'.
Index with Status 'N/A' (Doc ID 445158.1)

select PARTITION_NAME,STATUS from DBA_IND_PARTITIONS where
INDEX_NAME='XXXX';

Anuj Singh said...

http://anuj-singh.blogspot.com/2021/

Anuj Singh said...



Objects metadata
index metadata

http://anuj-singh.blogspot.com/2011/09/oracle-index-metadata-for-given-table.html

Anuj Singh said...
This comment has been removed by the author.
Anuj Singh said...


define 1='ANUJ'

col TABLE_NAME for a20
select 'dba_tables',1 CON_ID ,TABLE_NAME,' ' object_type from dba_tables where TABLE_NAME like '%&1%'
union all
select 'cdb_tables ',CON_ID,TABLE_NAME ,' ' from cdb_tables where TABLE_NAME like '%&1%'
union all
select 'cdb_objects', CON_ID,object_name,object_type from cdb_objects where object_name like '%&1%'
union all
select 'dba_objects', 1,object_name , object_type from dba_objects where object_name like '%&1%' ;

Anuj Singh said...



http://anuj-singh.blogspot.com/2021/12/object-waits-with-event.html

Oracle DBA

anuj blog Archive