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'

;

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',

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',

'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;

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;

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Tuesday, 24 November 2020

Oracle Table info..

Oracle Table info.. ..

object_dependent_segments

Oracle DBA

anuj blog Archive