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
/
SET LINESIZE 300 VERIFY OFF
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
COLUMN index_name FORMAT A30
col global_stats for a15
col PARTITIONED for a15
SELECT owner,
table_name,
num_rows,
blocks,
empty_blocks,
avg_space
chain_cnt,
avg_row_len,
PARTITIONED,
TABLESPACE_NAME,
last_analyzed,
SAMPLE_SIZE,
-- stale_stats,
global_stats,
initial_extent,
ini_trans,
freelists
FROM dba_tables
WHERE 1=1
AND and table_name=:h
;
SET LINESIZE 300 VERIFY OFF
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
COLUMN index_name FORMAT A30
col global_stats for a15
col PARTITIONED for a15
col COMPRESS_FOR for a15
SELECT owner,
index_name,
status,
table_name,
num_rows,
COMPRESSION,
PARTITIONED,
TABLESPACE_NAME,
last_analyzed,
SAMPLE_SIZE,
global_stats,
initial_extent,
ini_trans,
freelists
FROM dba_indexes
alter index scott.INDEX_NAME rebuild online;
set heading off
set echo off
Set pages 999
set long 90000
select dbms_metadata.get_ddl('INDEX','INDEX_NAME','SCOTT') from dual;
alter index scott.INDEX_NAME rebuild online;
--INI_TRANS for index and table
set pagesize 300 linesize 300
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
col INDEX_TYPE for a12
col OWNER for a12
col INDEX_NAME for a20
col table_name
select i.table_name,i.owner,i.INDEX_NAME,i.INDEX_TYPE,i.INI_TRANS,i.MAX_TRANS,i.PCT_FREE,i.LAST_ANALYZED,t.PARTITIONED,t.INI_TRANS tab_INI_TRANS,t.MAX_TRANS tab_MAX_TRANS ,t.PCT_FREE tab_PCT_FREE ,t.TABLESPACE_NAME,t.TEMPORARY
from dba_indexes i,dba_tables t
where 1=1
--and t.table_name in (':h')
-- and t.table_name ='LOG'
and i.table_name=t.table_name
-- and index_type='NORMAL'
;
SET LINES 500 pagesize 300
COLUMN owner FORMAT a20 HEADING 'Owner'
COLUMN index_name FORMAT a25 HEADING 'Index'
COLUMN partition_name FORMAT a20 HEADING 'Partition'
COLUMN tablespace_name FORMAT a15 HEADING 'Tablespace'
COLUMN pct_free FORMAT 9999 HEADING '%|Free'
COLUMN ini_trans FORMAT 9999 HEADING 'Init|Tran'
COLUMN max_trans FORMAT 9999 HEADING 'Max|Tran'
COLUMN initial_extent FORMAT 9999999 HEADING 'Init|Extent'
COLUMN next_extent FORMAT 9999999 HEADING 'Next|Extent'
COLUMN max_extent HEADING 'Max|Extents'
COLUMN pct_increase FORMAT 999 HEADING '%|Inc'
COLUMN distinct_keys FORMAT 9999999 HEADING '#Keys'
COLUMN clustering_factor FORMAT 999999 HEADING 'Clus|Fact'
col table_name for a25
col high_value for a90
BREAK ON index_owner on index_name
SELECT
a.index_owner,
a.index_name,
b.table_name,
a.tablespace_name,
a.partition_name,
a.pct_free,
a.ini_trans,
a.max_trans,
a.initial_extent,
a.next_extent,
a.max_extent,
a.pct_increase,
a.distinct_keys,
a.clustering_factor,
b.logging,
b.status,
a.compression, ----
a.high_value
FROM dba_ind_partitions a, dba_indexes b
where 1=1
and a.index_name=b.index_name
and b.table_name=:h
ORDER BY index_owner,index_name;
SELECT
owner,
index_name,
blevel,
leaf_blocks,
distinct_keys,
avg_leaf_blocks_per_key,
avg_data_blocks_per_key,
clustering_factor,
num_rows,
TABLESPACE_NAME,
global_stats,
last_analyzed,
initial_extent,
ini_trans,
freelists
FROM dba_indexes
WHERE 1=1
and table_name =:h
ORDER BY index_name
;
-- index rebuild
define IF_COMMENT=' '
--define IF_COMMENT='--'
SELECT 'ALTER INDEX ' || OWNER || '.' ||INDEX_NAME || ' REBUILD ' ||' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
&IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||INDEX_NAME ||' REBUILD PARTITION ' || PARTITION_NAME ||' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
&IF_COMMENT and INDEX_OWNER not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||INDEX_NAME ||' REBUILD SUBPARTITION '||SUBPARTITION_NAME||' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE'
&IF_COMMENT and INDEX_OWNER not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
;
SET VERIFY OFF LINESIZE 200
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
SELECT
t.owner,
t.table_name,
t.tablespace_name,
t.num_rows,
t.avg_row_len,
t.blocks,
t.empty_blocks,
ROUND(t.blocks * ts.block_size/1024/1024,2) AS size_mb
FROM dba_tables t
JOIN dba_tablespaces ts ON t.tablespace_name = ts.tablespace_name
WHERE 1=1
-- and t.owner = UPPER('')
and table_name =:h
ORDER BY t.table_name;
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
COLUMN index_name FORMAT A30
col global_stats for a15
SELECT owner,
index_name,
table_name,
PARTITIONED,
index_type,
TABLESPACE_NAME,
last_analyzed,
SAMPLE_SIZE,
global_stats,
initial_extent,
ini_trans,
freelists
FROM dba_indexes
WHERE 1=1
AND table_name =:h
;
define 1='402'
SET LINESIZE 300 VERIFY OFF
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A20
COLUMN edition_name FORMAT A15
SELECT owner,
object_name,
--subobject_name,
object_id,
data_object_id,
object_type,
TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') AS created,
TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') AS last_ddl_time,
timestamp,
status,
temporary,
generated,
secondary,
--namespace,
edition_name
FROM dba_objects
WHERE UPPER(object_name) LIKE UPPER('%&1%')
ORDER BY owner, object_name;
set linesize 300
COLUMN tab_column_name FORMAT A30 HEADING 'Column Name'
COLUMN tab_column_datatype FORMAT A20 HEADING 'Datatype'
COLUMN tab_column_nullable FORMAT A10 HEADING 'Nullable?'
COLUMN tab_column_numdistinct FORMAT 999,999,999 HEADING 'Distinct|Values'
COLUMN tab_column_density FORMAT 9.99999 HEADING 'Density'
COLUMN tab_column_numnulls FORMAT 999,999,999 HEADING 'Number|of Nulls'
COLUMN tab_column_histogram FORMAT A16 HEADING 'Histogram'
COLUMN tab_column_numbuckets FORMAT 999,999 HEADING 'Buckets'
col IND_NAME for a20
col TABLE_NAME for a25
SELECT i.index_name ind_name,
i.table_name ,
i.status ind_status,
DECODE(i.uniqueness,'UNIQUE','Y',NULL) ind_unique,
i.blevel ind_blevel,
i.leaf_blocks ind_leafblocks,
i.num_rows ind_numrows,
i.distinct_keys ind_distinctkeys,
i.clustering_factor ind_clufac,
TO_CHAR(i.last_analyzed, 'DD/MM/YYYY hh24:mi') last_analyzed_time
FROM dba_indexes i
WHERE i.index_name = 'SYS_C0051402'
ORDER BY i.uniqueness DESC, i.index_name
/
prompt DEFAULT ATTRIBUTES TABLESPACE for partition table
set linesize 400 pagesize 300
col owner for a20
col TABLE_NAME for a20
select
OWNER ,
TABLE_NAME ,
PARTITIONING_TYPE ,
SUBPARTITIONING_TYPE ,
PARTITION_COUNT,
DEF_SUBPARTITION_COUNT ,
PARTITIONING_KEY_COUNT ,
SUBPARTITIONING_KEY_COUNT ,
STATUS ,
DEF_TABLESPACE_NAME ---- <<<<<<<
from DBA_PART_TABLES
WHERE 1=1
-- DEF_TABLESPACE_NAME='OLD_TABLESPACE_NAME'
and TABLE_NAME=:h
-- ALTER USER XXX DEFAULT TABLESPACE users;
--ALTER TABLE t MODIFY DEFAULT ATTRIBUTES TABLESPACE users;
--alter table t modify default attributes lob (x ) ( tablespace users );
------------------------***********
-- Function-based Index
col owner for a15
col index_name for a30
col index_type for a21
select owner, index_name, index_type,table_name from dba_indexes
where 1=1
and index_type like 'FUNCTION-BASED%'
and table_name =:h;
--set linesize 300 pagesize 300
col TABLE_NAME for a20
col TABLE_OWNER for a20
col INDEX_NAME for a40
col column_expression for a100
SELECT table_owner,
TABLE_NAME,
index_name,
column_expression
FROM dba_ind_expressions
WHERE 1=1
and table_owner not in ('SYS','SYSTEM')
AND table_name =:h
------------------------***********
--- local index?
select owner,index_name,DEF_TABLESPACE_NAME, PARTITIONING_TYPE,LOCALITY from DBA_PART_INDEXES
where 1=1
-- and status='UNUSABLE'
and TABLE_NAME=:h
order by 1,2;
set linesize 300
col INDEX_NAME for a20
col TABLE_NAME for a20
col locality for a15
col TABLE_NAME for a20
select
TABLE_NAME,
INDEX_NAME ,
TABLE_NAME,
PARTITIONING_TYPE ,
SUBPARTITIONING_TYPE ,
PARTITION_COUNT , ----<<<<--
partitioning_type,
alignment,
locality
from DBA_PART_indexes
where table_name = :h;
prompt Any VIRTUAL_COLUMN !!!!!!!!!!!!?
set linesize 300 pagesize 300
col owner for a15
col hidden_column for a15
col virtual_column for a15
col table_name for a20
select owner,TABLE_NAME,column_name, column_id, internal_column_id, hidden_column, virtual_column
from dba_tab_cols
where table_name = :h
;
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300
col column_name for a30
col low_value for a40
col high_value for a40
col endpoint_actual_value for a30
col DATA_TYPE for a12
col DATA_DEFAULT for a15
col LOW_VALUE for a20
col HIGH_VALUE for a20
col OWNER for a15
SELECT
owner,
column_id,
column_name,
num_distinct,
avg_col_len,
histogram,
low_value,
high_value,
data_type,
data_default,
hidden_column
FROM dba_tab_columns
WHERE 1=1
AND table_name =:h
ORDER BY column_id
;
BREAK ON TODAY
COLUMN TODAY NEW_VALUE _DATE
SELECT TO_CHAR(sysdate,'mm/dd/yy hh24:mi') TODAY FROM DUAL;
COLUMN INSTANCE NEW_VALUE _INST
SELECT LTRIM(RTRIM(GLOBAL_NAME)) INSTANCE FROM GLOBAL_NAME;
BTITLE OFF
CLEAR BREAKS
TTITLE LEFT 'dba_find_constr' -
CENTER _INST -
RIGHT _DATE SKIP 1 -
CENTER 'ALL CONSTRAINTS FOR A GIVEN TABLE' -
RIGHT 'Page ' FORMAT 9999 SQL.PNO SKIP 2
col for_owner for a10 head 'Child|Owner' just l
col for_table for a10 head 'Child|Table' just l
col for_constr for a35 head 'Child|Constraint' just l
col pri_owner for a10 head 'Parent|Owner' just l
col pri_table for a10 head 'Parent|Table' just l
col pri_constr for a20 head 'Parent|Constraint' just l
col rule for a10 head 'Rule' just l
col status for a08 head 'Status' just l
col type for a04 head 'Type' just l
spool dba_find_constr.lst
select
a.owner for_owner,
a.table_name for_table,
a.constraint_name for_constr,
b.owner pri_owner,
b.table_name pri_table,
b.constraint_name pri_constr,
a.delete_rule rule,
a.status status,
a.constraint_type type
from dba_constraints a, dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.constraint_type = 'R'
and b.constraint_type = 'P'
and b.owner = upper('&ownr')
and b.table_name = :h
union all
select
b.owner for_owner,
b.table_name for_table,
b.constraint_name for_constr,
a.owner pri_owner,
a.table_name pri_table,
a.constraint_name pri_constr,
b.delete_rule rule,
b.status status,
b.constraint_type type
from dba_constraints a, dba_constraints b
where a.constraint_name = b.r_constraint_name
and a.constraint_type = 'P'
and b.constraint_type = 'R'
--and b.owner = upper('ownr')
and b.table_name = :h
union all
select
'' for_owner,
'' for_table,
'' for_constr,
b.owner pri_owner,
b.table_name pri_table,
b.constraint_name pri_constr,
b.delete_rule rule,
b.status status,
b.constraint_type type
from dba_constraints b
where 1=1
-- and b.owner = upper('ownr')
and b.table_name = :h
and b.constraint_type in ('C','P')
/
undef ownr
undef tbl
set linesize 300
col constraint_name for A18 HEADING "Constraint Name"
col constraint_type for A20 HEADING "Constraint|Type"
col search_condition for A15 HEADING "Search Condition"
col r_constraint_name for A20 HEADING "R / Constraint Name"
col delete_rule for A11 HEADING "Delete Rule"
col status HEADING "Status"
col COLUMN_NAME for a20
BREAK ON constraint_name ON constraint_type
select
a.constraint_name
, DECODE(a.constraint_type
, 'P', 'Primary Key'
, 'C', 'Check'
, 'R', 'Referential'
, 'V', 'View Check'
, 'U', 'Unique'
, a.constraint_type
) constraint_type
, b.column_name
, a.search_condition
, NVL2(a.r_owner, a.r_owner || '.' || a.r_constraint_name, null) r_constraint_name
, a.delete_rule
, a.status
FROM dba_constraints a , dba_cons_columns b
WHERE 1=1
AND a.table_name = b.table_name
AND a.constraint_name = b.constraint_name
AND b.table_name = :h
ORDER BY
a.constraint_name , b.position
/
set linesize 300
col table_name for a20
col column_name for a15
select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name=:h
;
set linesize 300 pagesize 100
col DATA_TYPE for a20
col COLUMN_NAME for a200
select DATA_TYPE , listagg(COLUMN_NAME, '|') within group (order by COLUMN_NAME) as COLUMN_NAME from DBA_TAB_COLUMNS where TABLE_NAME=:h
group by DATA_TYPE;
---
--Statistics on Partitioned Tables -- analyzed time
set linesize 300
col OWNER for a20
SELECT a.table_name,
a.partition_name,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.pct_increase,
a.num_rows,
a.avg_row_len,
last_analyzed,
global_stats
FROM dba_tab_partitions a
WHERE a.table_name = :h
-- AND a.table_owner = Upper('')
ORDER BY a.table_name, a.partition_name
-- with Partition date ?
set linesize 300 pagesize 300
col PARTITION_NAME for a30
col HIGH_VALUE for a40
col TABLE_NAME for a25
SELECT * from (
SELECT table_name,PARTITION_NAME,
extractvalue
( dbms_xmlgen.getxmltype
( 'select high_value
from DBA_TAB_PARTITIONS where table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.PARTITION_NAME || ''''),'//text()' ) as high_value
FROM DBA_TAB_PARTITIONS t
WHERE TABLE_NAME = :h)
order by high_value desc;
---
-- exec dbms_stats.gather_table_stats('OWNER', 'TABLE_NAME', granularity => 'SUBPARTITION', partname => 'P_20100211');
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set linesize 200 pages 250 set verify off
col blevel format 99
col GLOBAL_STATS for a15
col PARTITIONED for a15
col table_name format a22 heading 'TABLE NAME'
col u format a1 heading 'U'
col index_name format a25 heading 'INDEX NAME'
col column_name format a23 heading 'COLUMN NAME'
col column_position format 99 heading 'SEQ'
col column_length format 9999 heading 'LEN'
col leaf_blocks format 999990 heading 'LEAF|BLOCKS'
col distinct_keys format 9999990 heading 'DISTINCT|KEYS'
col avg_leaf_blocks_per_key format 999990 heading 'LEAF|BLKS|/KEY'
col avg_data_blocks_per_key format 999990 heading 'DATA|BLKS|/KEY'
rem break on table_name skip 1 on index_name on u rem
select i.table_name,COLUMN_NAME,i.blevel, i.leaf_blocks, i.distinct_keys,i.avg_leaf_blocks_per_key, i.avg_data_blocks_per_key,
decode( i.uniqueness, 'NONUNIQUE', null, 'UNIQUE', 'U', 'BITMAP', 'B', '?' ) u, i.index_name,i.last_analyzed, i.clustering_factor,partitioned,global_stats,status
from sys.dba_ind_columns c, sys.dba_indexes i
where 1=1
-- and (i.table_owner,i.table_name) in (select OBJECT_OWNER,OBJECT_NAME from gV$SQL_PLAN where HASH_VALUE= )
and i.owner = c.index_owner
and i.index_name = c.index_name
and i.table_name=:h
--and owner='ANUJ'
order by i.table_owner, i.table_name, i.index_name, c.column_position
/
set linesize 300 pagesize 300
col subpartition_name for a20
col partition_name for a20
select
owner ,
table_name ,
partition_name ,
partition_position ,
subpartition_name ,
stale_stats
from dba_tab_statistics
where table_name =:h
;
-- Reverse Key Indexes index_type --- 'NORMAL/REV' ??
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set linesize 300 pages 250 set verify off
col blevel format 99
col GLOBAL_STATS for a15
col PARTITIONED for a15
col table_name format a22 heading 'TABLE NAME'
col u format a1 heading 'U'
col index_name format a25 heading 'INDEX NAME'
col column_name format a23 heading 'COLUMN NAME'
col column_position format 99 heading 'SEQ'
col column_length format 9999 heading 'LEN'
col leaf_blocks format 999990 heading 'LEAF|BLOCKS'
col distinct_keys format 9999990 heading 'DISTINCT|KEYS'
col avg_leaf_blocks_per_key format 999990 heading 'LEAF|BLKS|/KEY'
col avg_data_blocks_per_key format 999990 heading 'DATA|BLKS|/KEY'
COL tsp_name FORMAT A20 HEADING "Tablespace|Name"
rem break on table_name skip 1 on index_name on u rem
select i.table_name,COLUMN_NAME,i.blevel, i.leaf_blocks, i.distinct_keys,i.avg_leaf_blocks_per_key, i.avg_data_blocks_per_key,
decode( i.uniqueness, 'NONUNIQUE', null, 'UNIQUE', 'U', 'BITMAP', 'B', '?' ) u,
i.index_name,i.last_analyzed, i.clustering_factor,partitioned,global_stats,status
, index_type --- 'NORMAL/REV' ??
,tablespace_name tsp_name
,initial_extent
,ini_trans
,freelists
from sys.dba_ind_columns c, sys.dba_indexes i
where 1=1
-- and (i.table_owner,i.table_name) in (select OBJECT_OWNER,OBJECT_NAME from gV$SQL_PLAN where HASH_VALUE= )
and i.owner = c.index_owner
and i.index_name = c.index_name
and i.table_name=:h
--and owner='SYS'
order by i.table_owner, i.table_name, i.index_name, c.column_position
/
SET LINESIZE 200
col owner for A20
col extension_name for A15
col extension for A50
col TABLE_NAME for a25
SELECT owner, table_name, extension_name, extension FROM dba_stat_extensions where 1=1
and table_name=:h
ORDER by owner, table_name;
column col_group format a30
select e.extension col_group,
t.num_distinct,
t.histogram
from dba_stat_extensions e
join dba_tab_col_statistics t on e.extension_name=t.column_name
and t.table_name = :h;
-- Display extended statistics distinct values and histograms
column col_group format a30
select e.extension col_group,
t.num_distinct,
t.histogram
from dba_stat_extensions e
join dba_tab_col_statistics t on e.extension_name=t.column_name
and t.table_name = :h;
Prompt Foreign Keys without Index
set lines 200 pages 200
set wrap off
column columns format a30 word_wrapped
column table_name format a30 word_wrapped
col COLUMNS_INDEXED for a20
col Status for a20
select decode( b.table_name, NULL, '****', 'ok' ) Status, a.table_name, a.columns, b.columns columns_indexed
from
( select substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
from all_cons_columns a, all_constraints b
where 1=1
-- and a.owner=upper('POW')
and a.table_name=:h
and a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
from all_ind_columns
where 1=1
-- and TABLE_OWNER=upper('POW')
and table_name=:h
group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
and b.columns (+) like a.columns || '%'
/
set linesize 300
col TABLE_NAME for a30
col OWNER for a25
SELECT
owner,
table_name,
TRUNC(sum(bytes)/1024/1024/1024) GB
-- , ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE 1=1
--and owner in ('XX')
and table_name =:h
GROUP BY table_name, owner
-- HAVING SUM(bytes)/1024/1024/1024 > 10 -- 1gb
ORDER BY SUM(bytes) desc
;
set lines 300 set pages 300
col table_name for a40
col owner for a30
select distinct owner, PARTITION_NAME,NUM_ROWS,table_name, STALE_STATS, last_analyzed, stattype_locked from dba_tab_statistics
where 1=1
and table_name=:h
;
col anlyzd_rows form 99999,999,999
col tot_rows form 99999,999,999
col tab_name form a45
col chngs form 99,999,999,999
col pct_c form 9999999990.99
col truncated head 'Trun|cated' for a5 justify l
select dbta.owner||'.'||dbta.table_name tab_name
, dbta.num_rows anlyzd_rows
, to_char(dbta.last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_anlzd
, nvl(dbta.num_rows,0)+nvl(dtm.inserts,0) -nvl(dtm.deletes,0) tot_rows
, nvl(dtm.inserts,0)+nvl(dtm.deletes,0)+nvl(dtm.updates,0) chngs
,(nvl(dtm.inserts,0)+nvl(dtm.deletes,0)+nvl(dtm.updates,0)) /greatest(nvl(dbta.num_rows,0),1) pct_c
, dtm.truncated
from dba_tab_statistics dbta
left outer join sys.dba_tab_modifications dtm
on dbta.owner = dtm.table_owner
and dbta.table_name = dtm.table_name
and dtm.partition_name is null
where 1=1
--and dbta.last_analyzed < sysdate - 1
--and STALE_STATS = 'YES'
and dbta.owner=:h
order by dbta.last_analyzed desc
;
col TABLE_OWNER for a30
select TABLE_OWNER,table_name,inserts,updates,deletes,truncated,timestamp from sys.dba_tab_modifications
where 1=1
--and table_owner=user
and table_name= :h
;
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;
set linesize 300 pagesize 300
col TABLE_OWNER for a25
col NAME for a20
col IS_PARTITION for a15
select m.TABLE_OWNER,
'NO' as IS_PARTITION,
m.TABLE_NAME as NAME,
m.INSERTS,
m.UPDATES,
m.DELETES,
m.TRUNCATED,
m.TIMESTAMP as LAST_MODIFIED,
round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
t.num_rows as last_known_rows_number,
t.last_analyzed
From dba_tab_modifications m,dba_tables t
where m.table_owner=t.owner
and m.table_name=t.table_name
and m.table_owner not in ('SYS','SYSTEM')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
and m.TABLE_NAME=:h
union
select m.TABLE_OWNER,
'YES' as IS_PARTITION,
m.PARTITION_NAME as NAME,
m.INSERTS,
m.UPDATES,
m.DELETES,
m.TRUNCATED,
m.TIMESTAMP as LAST_MODIFIED,
round((m.inserts+m.updates+m.deletes)*100/NULLIF(p.num_rows,0),2) as EST_PCT_MODIFIED,
p.num_rows as last_known_rows_number,
p.last_analyzed
From dba_tab_modifications m,dba_tab_partitions p
where m.table_owner=p.table_owner
and m.table_name=p.table_name
and m.PARTITION_NAME = p.PARTITION_NAME
and m.table_owner not in ('SYS','SYSTEM')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(p.num_rows,0) > 10 or p.last_analyzed is null)
and m.TABLE_NAME=:h
order by 8 desc
;
select command from
(select (nvl(a.updates,0)+nvl(a.inserts,0)+nvl(a.deletes,0))*100/ nvl(b.num_rows,1) as change,
'exec DBMS_STATS.GATHER_TABLE_STATS (' || '''' || a.TABLE_OWNER || '''' || ',' || '''' || a.TABLE_NAME|| '''' || ', granularity => ' || '''' || 'PARTITION' || ''''|| ', PARTNAME => ' || '''' || a.PARTITION_NAME || '''' || ', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=> ' || '''' || 'FOR ALL COLUMNS SIZE AUTO' || '''' || ') ;' as command
from dba_tab_modifications a, dba_tab_partitions b
where 1=1
-- and a.table_name = :h
and a.table_name = b.table_name
and a.partition_name = b.partition_name
and nvl(a.updates,0)+nvl(a.inserts,0)+nvl(a.deletes,0) > 0
)
where change >= 10
Best Method to Gather Stats of Partition Tables When Using Granularity (Doc ID 2352723.1)
exec dbms_stats.gather_table_stats(ownname=>'AAA',tabname=>'BBB',partname=>'CCC',granularity=>'PARTITION',CASCADE=> true,estimate_percent=>dbms_stats.auto_sample_size,degree=>dbms_stats.auto_degree);
(or)
exec dbms_stats.gather_table_stats (ownname=>'AAA',tabname=>'BBB',granularity =>'GLOBAL',CASCADE=> true,estimate_percent=>dbms_stats.auto_sample_size,degree=>dbms_stats.auto_degree);
-- from web
set linesize 300 pagesize 300
col schema_name for a15
col column_name for a15
col default_value for a20
col table_name for a25
col comments for a20
col data_type_ext for a20
col data_type for a20
select /*+ PARALLEL(4) */ col.owner as schema_name,
col.table_name,
col.column_name,
col.data_type,
decode(char_length, 0, data_type, data_type || '(' || char_length || ')') as data_type_ext,
col.data_length,
col.data_precision,
col.data_scale,
col.nullable,
col.data_default as default_value,
nvl(pk.primary_key, ' ') as primary_key,
nvl(fk.foreign_key, ' ') as foreign_key,
nvl(uk.unique_key, ' ') as unique_key,
nvl(check_const.check_constraint, ' ') check_constraint,
comm.comments
from all_tables tab
inner join all_tab_columns col
on col.owner = tab.owner
and col.table_name = tab.table_name
left join all_col_comments comm on col.owner = comm.owner
and col.table_name = comm.table_name
and col.column_name = comm.column_name
left join (select constr.owner, col_const.table_name,col_const.column_name, 'PK' primary_key
from all_constraints constr
inner join all_cons_columns col_const on constr.constraint_name = col_const.constraint_name
and col_const.owner = constr.owner
where constr.constraint_type = 'P') pk
on col.table_name = pk.table_name
and col.column_name = pk.column_name
and col.owner = pk.owner
left join (select constr.owner, col_const.table_name,col_const.column_name, 'FK' foreign_key
from all_constraints constr
inner join all_cons_columns col_const on constr.constraint_name = col_const.constraint_name
and col_const.owner = constr.owner
where constr.constraint_type = 'R'
group by constr.owner,
col_const.table_name,
col_const.column_name) fk
on col.table_name = fk.table_name
and col.column_name = fk.column_name
and col.owner = fk.owner
left join (select constr.owner, col_const.table_name, col_const.column_name, 'UK' unique_key
from all_constraints constr
inner join all_cons_columns col_const on constr.constraint_name = col_const.constraint_name
and constr.owner = col_const.owner
where constr.constraint_type = 'U'
union
select ind.owner, col_ind.table_name, col_ind.column_name, 'UK' unique_key
from all_indexes ind
inner join all_ind_columns col_ind on ind.index_name = col_ind.index_name
where ind.uniqueness = 'UNIQUE') uk
on col.table_name = uk.table_name
and col.column_name = uk.column_name
and col.owner = uk.owner
left join (select constr.owner, col_const.table_name, col_const.column_name, 'Check' check_constraint
from all_constraints constr
inner join all_cons_columns col_const on constr.constraint_name = col_const.constraint_name
and col_const.owner = constr.owner
where constr.constraint_type = 'C'
group by constr.owner, col_const.table_name, col_const.column_name) check_const
on col.table_name = check_const.table_name
and col.column_name = check_const.column_name
and col.owner = check_const.owner
where 1=1
/* and col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
'LBACSYS', 'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS',
'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000',
'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA',
'ORACLE_OCM','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
'XS$NULL','PUBLIC')
*/
-- and col.owner = 'XX'
-- and lower(tab.table_name) like '%'
and tab.table_name=:h
order by col.owner,col.table_name, col.column_name;
set lines 300 set pages 300
col table_name for a40
col owner for a30
select owner,table_name,stats_update_time from dba_tab_stats_history where table_name = :h
SELECT TABLES.OWNER, TABLES.TABLE_NAME, ROUND((DELETES + UPDATES + INSERTS)/NUM_ROWS*100) PERCENTAGE FROM DBA_TABLES TABLES, DBA_TAB_MODIFICATIONS MODIFICATIONS
WHERE TABLES.OWNER = MODIFICATIONS.TABLE_OWNER
AND TABLES.TABLE_NAME = MODIFICATIONS.TABLE_NAME
AND NUM_ROWS > 0
AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
and TABLES.OWNER=:h
ORDER BY 3 desc
PROMPT
PROMPT +----------------------------------------------------------------------------+
PROMPT | PARTITIONS (INDEX) |
PROMPT +----------------------------------------------------------------------------+
col index_name for A25 HEADING "Index Name"
col partitioning_type for A9 HEADING "Type"
col partition_count for 99999 HEADING "Part.|Count"
col partitioning_key_count for 99999 HEADING "Part.|Key Count"
col locality for A8 HEADING "Locality"
col alignment for A12 HEADING "Alignment"
SELECT
a.owner || '.' || a.index_name index_name
, b.column_name
, a.partitioning_type
, a.partition_count
, a.partitioning_key_count
, a.locality
, a.alignment
FROM dba_part_indexes a , dba_part_key_columns b
WHERE 1=1
AND a.table_name = :h
AND RTRIM(b.object_type) = 'INDEX'
AND b.owner = a.owner
AND b.name = a.index_name
ORDER BY a.index_name , b.column_position
/
PROMPT
PROMPT +----------------------------------------------------------------------------+
PROMPT | TRIGGERS |
PROMPT +----------------------------------------------------------------------------+
col trigger_name for A25 HEADING "Trigger Name"
col trigger_type for A18 HEADING "Type"
col triggering_event for A9 HEADING "Trig.|Event"
col referencing_names for A65 HEADING "Referencing Names" newline
col when_clause for A65 HEADING "When Clause" newline
col trigger_body for A65 HEADING "Trigger Body" newline
SELECT
owner || '.' || trigger_name trigger_name
, trigger_type
, triggering_event
, status
, referencing_names
, when_clause
, trigger_body
FROM dba_triggers
WHERE 1=1
AND table_name = :h
ORDER BY trigger_name
/
set linesize 300 pagesize 300
col table_name for a25
col referencing_object for a20
select referenced_owner || '.' || referenced_name as table_name, referenced_type as type, owner || '.' || name as referencing_object, type as referencing_type
from sys.all_dependencies
where referenced_type in('TABLE', 'VIEW')
and referenced_name = :h -- put your table or view
--and referenced_owner = 'schema name'
and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS','MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST','WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','OUTLN', 'WKSYS', 'LBACSYS')
order by referencing_object;
set linesize 300 pagesize 300
col object_name for a15
col owner for a12
col kill info for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,t.start_scnw,t.start_scnb
,t.start_time -- <<<<--- format mm/dd/yy
,s.username, o.object_name,o.owner,t.used_ublk ,t.used_urec ,s.event,s.sql_id,s.prev_sql_id,s.status
from gv$transaction t, gv$session s, gv$locked_object l,dba_objects o
where 1=1
and t.ses_addr = s.saddr
and t.inst_id = s.inst_id
and t.xidusn = l.xidusn
and t.xidslot = l.xidslot
and t.xidsqn = l.xidsqn
and t.inst_id = l.inst_id
and l.object_id = o.object_id
-- and username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
-- and s.STATUS!='INACTIVE'
-- l.object_id IN ('') ----< OBJECT ID FROM DBA_OBJECTS
and o.object_name=:h
;
--desc table
COLUMN column_name FORMAT A20 HEADING "Column Name"
COLUMN data_type FORMAT A25 HEADING "Data Type"
COLUMN nullable FORMAT A13 HEADing "Null?"
SELECT
column_name
, DECODE(nullable, 'Y', ' ', 'NOT NULL') nullable
, DECODE(data_type
, 'RAW', data_type || '(' || data_length || ')'
, 'CHAR', data_type || '(' || data_length || ')'
, 'VARCHAR', data_type || '(' || data_length || ')'
, 'VARCHAR2', data_type || '(' || data_length || ')'
, 'NUMBER', NVL2( data_precision
, DECODE( data_scale
, 0
, data_type || '(' || data_precision || ')'
, data_type || '(' || data_precision || ',' || data_scale || ')'
)
, data_type)
, data_type
) data_type
FROM
dba_tab_columns
WHERE 1=1
AND table_name = :h
ORDER BY column_id
/
col owner form a6 word wrap
col table_name form a15 word wrap
col column_name form a22 word wrap
col data_type form a12
col M form a1
col num_vals form 99999,999
col dnsty form 0.9999
col num_nulls form 99999,999
col low_v form a18
col hi_v form a18
col data_type form a10
set lines 110
break on owner nodup on table_name nodup
select --owner
-- ,table_name
column_name
,data_type
,decode (nullable,'N','Y','N') M
,num_distinct num_vals
,num_nulls
,density dnsty
,decode(data_type
,'NUMBER' ,to_char(utl_raw.cast_to_number(low_value))
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(low_value))
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(low_value))
,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(low_value))
,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(low_value))
,'DATE',to_char(1780+to_number(substr(low_value,1,2),'XX')
+to_number(substr(low_value,3,2),'XX'))||'-'
||to_number(substr(low_value,5,2),'XX')||'-'
||to_number(substr(low_value,7,2),'XX')||' '
||(to_number(substr(low_value,9,2),'XX')-1)||':'
||(to_number(substr(low_value,11,2),'XX')-1)||':'
||(to_number(substr(low_value,13,2),'XX')-1)
, low_value
) low_v
,decode(data_type
,'NUMBER' ,to_char(utl_raw.cast_to_number(high_value))
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(high_value))
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(high_value))
,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(high_value))
,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(high_value))
,'DATE',to_char(1780+to_number(substr(high_value,1,2),'XX')
+to_number(substr(high_value,3,2),'XX'))||'-'
||to_number(substr(high_value,5,2),'XX')||'-'
||to_number(substr(high_value,7,2),'XX')||' '
||(to_number(substr(high_value,9,2),'XX')-1)||':'
||(to_number(substr(high_value,11,2),'XX')-1)||':'
||(to_number(substr(high_value,13,2),'XX')-1)
, high_value
) hi_v
from dba_tab_columns
where 1=1
-- and owner like upper('&tab_own')
--and table_name like upper(nvl('&tab_name','OOPS')||'%')
AND table_name = :h
ORDER BY owner,table_name,COLUMN_ID
/
-- ===============
alter session set nls_date_format='dd-MM-YYYY hh24:mi';
set linesize 300 pagesize 300
col owner for a20
col table_name for a20
col index_name for a20
col TABLE_OWNER for a20
select owner,
index_name,
decode(INDEX_TYPE,
'NORMAL',
'B-tree',
decode(index_type,
'BITMAP',
'BitMap',
decode(table_type,
'IOT',
'IOT',
decode(index_type, 'LOB', 'LOB')))) idx_type,
table_owner,
table_name,
table_type,
compression,
status,
blevel,
clustering_factor,
uniqueness,
distinct_keys,
last_analyzed,
last_ddl_time
from (select distinct t.INDEX_NAME,
t.OWNER,
t.index_type,
t.TABLE_OWNER,
t.TABLE_NAME,
decode(u.partitioned,
'YES',
'PART',
decode(u.temporary,
'Y',
'TEMP',
decode(u.iot_type,
'IOT',
'IOT',
'NORMAL'))) table_type,
t.compression,
t.status,
t.blevel,
t.clustering_factor,
t.distinct_keys,
t.uniqueness,
t.last_analyzed,
s.last_ddl_time
from dba_indexes t, dba_objects s, dba_tables u
where t.index_name = s.object_name
and u.table_name = t.table_name
and s.owner = u.owner
and t.owner = s.owner
and s.object_type = 'INDEX'
-- and t.owner = 'SYS'
-- and t.INDEX_NAME =''
and u.table_name=:h
order by t.index_name);
set linesize 300 pagesize 300
col column_name for a20
col subpartition_name for a20
col partition_name for a20
select t.table_owner,
t.table_name,
s.object_id,
s.OBJECT_TYPE,
'part tab' as part_role,
s.DATA_OBJECT_ID,
t.partition_name,
t.subpartition_count,
t.partition_position,
t.CHAIN_CNT,
trunc(((t.AVG_ROW_LEN * t.NUM_ROWS) / 8) / (decode(t.BLOCKS, 0, 1, t.BLOCKS)) * 100) as HWM_STAT,
t.blocks,
t.num_rows,
t.avg_row_len,
t.last_analyzed,
s.LAST_DDL_TIME
from dba_tab_partitions t, dba_objects s
where t.partition_name = s.subobject_name
and t.table_name = s.object_name
and t.table_owner = s.owner
and t.table_name not like '%BIN$%'
and s.OBJECT_TYPE = 'TABLE PARTITION'
-- and t.table_owner = 'SYS'
and t.table_name =:h
order by table_owner, table_name, partition_name
;
set linesize 300 pagesize 300
col column_name for a20
col subpartition_name for a20
col partition_name for a20
select t.table_owner,
t.table_name,
s.object_id,
s.OBJECT_TYPE,
'part tab son' as part_role,
s.DATA_OBJECT_ID,
t.partition_name,
t.SUBPARTITION_NAME,
t.SUBPARTITION_POSITION,
u.column_name,
t.CHAIN_CNT,
t.blocks,
t.num_rows,
t.avg_row_len,
trunc(((t.AVG_ROW_LEN * t.NUM_ROWS) / 8) / (decode(t.BLOCKS, 0, 1, t.BLOCKS)) * 100) as HWM_STAT,
t.last_analyzed,
s.LAST_DDL_TIME
from dba_tab_subpartitions t, dba_objects s,dba_subpart_key_columns u
where t.table_name = u.name
and t.subpartition_name = s.subobject_name
and t.table_name = s.object_name
and t.table_owner = s.owner
and t.table_name not like '%BIN$%'
and s.OBJECT_TYPE = 'TABLE SUBPARTITION'
-- and t.table_owner = 'SYS'
and t.table_name =:h
order by table_owner, table_name, partition_name
;
set linesize 300 pagesize 300
col column_name for a20
col subpartition_name for a20
col partition_name for a20
select t.table_owner,
t.table_name,
s.object_id,
s.OBJECT_TYPE,
'part tab son' as part_role,
s.DATA_OBJECT_ID,
t.partition_name,
t.SUBPARTITION_NAME,
t.SUBPARTITION_POSITION,
u.column_name,
t.CHAIN_CNT,
t.blocks,
t.num_rows,
t.avg_row_len,
trunc(((t.AVG_ROW_LEN * t.NUM_ROWS) / 8) / (decode(t.BLOCKS, 0, 1, t.BLOCKS)) * 100) as HWM_STAT,
t.last_analyzed,
s.LAST_DDL_TIME
from dba_tab_subpartitions t, dba_objects s,Dba_Subpart_Key_Columns u
where t.table_name = u.name
and t.subpartition_name = s.SUBOBJECT_NAME
and t.table_name = s.object_name
and t.table_owner = s.owner
and t.table_name not like '%BIN$%'
and s.OBJECT_TYPE = 'TABLE SUBPARTITION'
--and t.table_owner = 'SYS'
and t.table_name =:h
order by table_owner, table_name, partition_name;
;
set linesize 300 pagesize 300
col column_name for a20
col subpartition_name for a20
col partition_name for a20
col IOT_NAME for a15
select t.owner,
t.table_name,
decode(t.partitioned,
'YES',
'PART',
decode(t.temporary, 'Y', 'TEMP', decode (t.iot_type,'IOT','IOT','NORMAL'))) table_type,
s.object_type,
t.iot_name,
t.NUM_ROWS,
t.BLOCKS,
t.AVG_ROW_LEN,
t.LAST_ANALYZED,
s.last_ddl_time,
t.CHAIN_CNT,
trunc(((t.AVG_ROW_LEN * t.NUM_ROWS) / 8) / (decode(t.BLOCKS, 0, 1, t.BLOCKS)) * 100) as HWM_STAT,
t.COMPRESSION
from dba_tables t, dba_objects s
where t.table_name = s.object_name
and t.owner = s.owner
and s.object_type = 'TABLE'
and t.table_name not like '%BIN%'
and t.table_name =:h
-- and t.owner = 'obj_owner'
;
set linesize 300 pagesize 300
col column_name for a20
col subpartition_name for a20
col partition_name for a20
col iot_name for a15
select t.owner,
t.index_name,
t.table_name,
u.partition_name,
u.partition_position,
v.column_name,
t.partitioning_type,
t.subpartitioning_type,
t.partition_count,
t.locality,
t.alignment,
u.compression,
u.status,
u.blevel,
u.clustering_factor,
u.distinct_keys,
u.last_analyzed
from dba_part_indexes t, dba_ind_partitions u,dba_part_key_columns v
where t.index_name = u.index_name
and t.index_name = v.name
and t.owner = u.index_owner
-- and t.owner = 'obj_owner'
and t.TABLE_NAME=:h
order by u.index_name,u.partition_name
;
select s.segment_name,
t.partition_name,
s.partition_name,
sum(s.bytes) / 1024 / 1024 as tab_space
from dba_segments s, dba_tab_subpartitions t
where s.partition_name = t.subpartition_name
and s.segment_type = 'TABLE SUBPARTITION'
and s.owner = t.table_owner
-- and s.owner = :owner
AND table_name =:h
group by s.owner, s.segment_name,t.partition_name,s.partition_name;
set linesize 300
select owner, table_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
from dba_tables
where table_name=:h
;
---------------------------------------------------------------------
col MB for 999,999,999.99
set linesize 300
SELECT owner, table_name, NVL(num_rows*avg_row_len,0)/1024000 MB FROM dba_tables
where 1=1
and table_name=:h
ORDER BY owner, table_name;
---------------------------------------------------------------------
col ALLOCATED_MB for 999,999,999.99
col REQUIRED_MB for 999,999,999.99
col TABLE_NAME for a20
col TABLESPACE_NAME for a20
SELECT SUBSTR(s.segment_name,1,20) TABLE_NAME,
SUBSTR(s.tablespace_name,1,20) TABLESPACE_NAME,
ROUND(DECODE(s.extents, 1, s.initial_extent,(s.initial_extent + (s.extents-1) * s.next_extent))/1024000,2) ALLOCATED_MB,ROUND((t.num_rows * t.avg_row_len / 1024000),2) REQUIRED_MB
FROM dba_segments s, dba_tables t
WHERE s.owner = t.owner
AND s.segment_name = t.table_name
-- and t.owner='TEST'
and t.table_name=:h
ORDER BY ALLOCATED_MB;
col segment_name for a32
select segment_name, substr(s.segment_name,1,20) table_name,substr(s.tablespace_name,1,20) tablespace_name,
round(decode(s.extents, 1, s.initial_extent,(s.initial_extent + (s.extents-1) * s.next_extent))/1048576,2) allocated_mb,
bytes/(1024*1024) m,segment_type
from dba_segments s
where 1=1
--and segment_name like 'accountrat%'
and segment_name =:h
--and t.owner=:h
order by allocated_mb;
select owner, table_name, TABLESPACE_NAME, ROUND((t.num_rows * t.avg_row_len / 1048576),2) REQUIRED_MB
from dba_tables t
where 1=1
--and owner= 'TEST'
and table_name=:h
;
set lines 200 pages 200
col value for 999,999,999,999.99
col STATISTIC_NAME for a30
col OWNER for a12
col OBJECT_TYPE for a10
select *
from
(select statistic_name,
st.owner,
st.obj#,
st.object_type,
st.object_name,
st.value,
dense_rank() over(partition by statistic_name
order by st.value desc) rnk
from gv$segment_statistics st)
where rnk <= 10
and object_name=:h
;
---Table size
set linesize 200 pagesize 200
col partition_name for a20
col segment_name for a30
col owner for a20
select owner, segment_name, segment_type, partition_name, tablespace_name , round(bytes/(1024*1024*1024),2) size_gb
from dba_segments
where 1=1
and segment_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION','INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY', 'LOBINDEX', 'LOBSEGMENT', 'LOB PARTITION')
--AND segment_type = 'TABLE'
and segment_name=:h
-- AND ROUND(bytes/(1024*1024*1024),2) > 10 --- 10gb
--and segment_name in ('XXXX')
ORDER BY bytes DESC;
set linesize 300 pagesize 300
col SEGMENT_NAME format a40
col PARTITION_NAME for a20
col TABLE_NAME for a30
col TABLESPACE_NAME for a20
select s.segment_name,TABLE_NAME, s.partition_name, bytes/1024/1024/1024 "Size (GB)",s.TABLESPACE_NAME
from dba_segments s, dba_lobs l
where s.segment_name = l.segment_name
-- and s.segment_name not like 'SYS%'
--and s.owner ='OBX'
--and s.segment_name in ('SYS_LOB0001163662C00007$$','SYS_LOB0001720371C00012$$' ,'SYS_LOB0001720358C00009$$','OBI.SYS_LOB0001163644C00007$$')
and s.segment_name= :h
order by s.segment_name, s.partition_name;
set linesize 500
col securefile for a15
col owner for a20
select distinct e.owner,l.table_name,l.segment_name,encrypt,compression,deduplication,format,securefile,retention_type,retention_value from dba_extents e, dba_lobs l
where e.owner = l.owner
and e.segment_name = l.segment_name
--and e.segment_type = 'LOBSEGMENT'
--and l.segment_name like 'SYS_LOB0000C00014$$'
and l.table_name=:h
order by 1
;
set linesize 300 pagesize 300
col owner for a20
col column_name for a20
col segment_name for a30
col TABLE_NAME for a30
col TABLESPACE_NAME for a30
SELECT owner
, table_name
, column_name
, segment_name
,securefile
,TABLESPACE_NAME
FROM dba_lobs
WHERE 1=1
AND segment_name IN (
SELECT segment_name
FROM dba_segments
WHERE 1=1
AND owner = 'ANUJ' ----<<<<
AND segment_type = 'LOBSEGMENT' -- or LOB SUBPARTITION, LOBINDEX, LOB PARTITION
)
| col OWNER for a12 | |
| col OBJECT_NAME for a30 | |
| col OBJECT_TYPE for a14 | |
| col SUBOBJECT_NAME for a30 | |
| SELECT ob.owner, ob.object_name, ob.subobject_name, ob.object_type,obj#, savtime, flags, rowcnt, blkcnt, avgrln ,samplesize, analyzetime, cachedblk, cachehit, logicalread | |
| FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob | |
| WHERE 1=1 --and owner=upper('OWNER') | |
| and object_name=:h | |
| and object_type in ('TABLE') | |
| and object_id=obj# | |
| order by savtime asc; |
COLUMN tab_column_name FORMAT A30 HEADING 'Column Name'
COLUMN tab_column_datatype FORMAT A20 HEADING 'Datatype'
COLUMN tab_column_nullable FORMAT A10 HEADING 'Nullable?'
COLUMN tab_column_numdistinct FORMAT 999,999,999 HEADING 'Distinct|Values'
COLUMN tab_column_density FORMAT 9.99999 HEADING 'Density'
COLUMN tab_column_numnulls FORMAT 999,999,999 HEADING 'Number|of Nulls'
COLUMN tab_column_histogram FORMAT A16 HEADING 'Histogram'
COLUMN tab_column_numbuckets FORMAT 999,999 HEADING 'Buckets'
SELECT i.index_name ind_name,
i.status ind_status,
DECODE(i.uniqueness,'UNIQUE','Y',NULL) ind_unique,
i.blevel ind_blevel,
i.leaf_blocks ind_leafblocks,
i.num_rows ind_numrows,
i.distinct_keys ind_distinctkeys,
i.clustering_factor ind_clufac,
TO_CHAR(i.last_analyzed, 'DD/MM/YYYY hh24:mi') last_analyzed_time
FROM dba_indexes i
WHERE i.table_name = :h
ORDER BY i.uniqueness DESC, i.index_name
/
-- from https://github.com/macartain/sql/blob/master/get_table_index_info.sql
set linesize 300 pagesize 300
COLUMN index_name FORMAT A30 HEADING 'Index Name'
COLUMN column_name FORMAT A30 HEADING 'Column Name'
COLUMN low_value FORMAT A20 HEADING 'Low Value'
COLUMN high_value FORMAT A20 HEADING 'High Value'
BREAK ON index_name NODUP
WITH col_hi_lo_vals AS
( select tc.column_name
, tc.data_type
, tc.low_value raw_low_value
, tc.high_value raw_high_value
, SUBSTR(dump(tc.low_value), (INSTR(dump(tc.low_value),': ')+2)) date_low_val
, SUBSTR(dump(tc.high_value), (INSTR(dump(tc.high_value),': ')+2)) date_high_val
from dba_tab_columns tc
WHERE tc.table_name = :h
),
col_hi_lo_vals_translated AS
( SELECT column_name
, data_type
, CASE when data_type = 'DATE'
THEN
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,1)-100, '09')|| -- low_century
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,2)-100, '09')|| -- low_year
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,3),'09')|| -- low_month
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,4),'09')|| -- low_day
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,5)-1,'09')|| -- low_hour24
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,6)-1,'09')|| -- low_minute
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,7)-1,'09') -- low_second
ELSE
NULL
END low_date
, CASE when data_type = 'DATE'
THEN
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,1)-100, '09')|| -- high_century
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,2)-100, '09')|| -- high_year
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,3), '09')|| -- high_month
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,4), '09')|| -- high_day
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,5)-1, '09')|| -- high_hour24
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,6)-1, '09')|| -- high_minute
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,7)-1, '09') -- high_second
ELSE
NULL
END high_date
, CASE WHEN data_type = 'NUMBER'
THEN
utl_raw.cast_to_number(raw_low_value)
ELSE
NULL
END low_num
, CASE WHEN data_type = 'NUMBER'
THEN
utl_raw.cast_to_number(raw_high_value)
ELSE
NULL
END high_num
, CASE WHEN data_type LIKE '%CHAR%'
THEN
utl_raw.cast_to_varchar2(raw_low_value)
ELSE
NULL
END low_char
, CASE WHEN data_type LIKE '%CHAR%'
THEN
utl_raw.cast_to_varchar2(raw_high_value)
ELSE
NULL
END high_char
FROM col_hi_lo_vals
)
SELECT ic.index_name,
ic.column_name
, CASE WHEN chlvt.data_type = 'DATE'
THEN TO_CHAR(TO_DATE(REPLACE(chlvt.low_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
WHEN chlvt.data_type = 'NUMBER'
THEN LPAD(TO_CHAR(chlvt.low_num),20)
WHEN chlvt.data_type LIKE '%CHAR%'
THEN chlvt.low_char
END low_value
, CASE WHEN chlvt.data_type = 'DATE'
THEN TO_CHAR(TO_DATE(REPLACE(chlvt.high_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
WHEN chlvt.data_type = 'NUMBER'
THEN LPAD(TO_CHAR(chlvt.high_num),20)
WHEN chlvt.data_type LIKE '%CHAR%'
THEN chlvt.high_char
END high_value
FROM dba_ind_columns ic
, col_hi_lo_vals_translated chlvt
WHERE ic.table_name = :h
AND ic.column_name = chlvt.column_name
ORDER BY ic.index_name, ic.column_position
/
Prompt Table metadata ...
SET LONG 10000 LONGCHUNKSIZE 10000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner)
FROM all_tables
WHERE 1=1
-- and owner = UPPER('1')
AND table_name = :h;
set PAGESIZE 80
****
var h varchar2(30)
begin :h := 'TEST'; end; ---- table name here!!!!
/
set heading off echo off pages 999 long 90000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
var h varchar2(30)
begin :h := 'TEST'; end; ---- table name here!!!!
/
var O varchar2(30)
begin :O := 'ANUJ'; end; ---- table owner here!!!!
/
set heading off echo off pages 999 long 90000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner)
FROM all_tables
WHERE 1=1
--and owner = :O
AND table_name = :h;
SELECT DBMS_METADATA.get_ddl ('INDEX', INDEX_NAME, owner)
FROM all_indexes
WHERE 1=1
--and owner = :O
AND table_name = :h;
select dbms_metadata.get_dependent_ddl('CONSTRAINT', table_name, owner)
FROM all_tables
WHERE 1=1
--and owner = :O
AND table_name = :h;
select dbms_metadata.get_dependent_ddl('TRIGGER', table_name, owner)
FROM all_tables
WHERE 1=1
-- and owner = :O
AND table_name = :h;
==============
GATHER_TABLE_STATS for stale stats
set linesize 300 pagesize 300
col gather_stats for a200
select 'EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'''||owner||''', tabname=>'''||table_name||''',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => ''FOR ALL COLUMNS SIZE AUTO'', degree=>16,cascade=>true);' gather_stats
from dba_tables t
where 1=1
and owner='XXX'
--and PARTITIONED='NO'
--and LAST_ANALYZED<sysdate-4
and t.TABLE_NAME in (select TABLE_NAME from DBA_TAB_STATISTICS where STALE_STATS='YES'
and OWNER='XX'
)
;
gather stats ...
define tabname='EMP'
define owner='ANUJ'
set pages 100 lines 250
--set echo off feedback off heading on
col gather for a200
spool gather.sql
select 'SET ECHO ON FEEDBACK ON TIMING ON' FROM DUAL;
select 'exec dbms_stats.gather_table_stats (ownname => ''' || owner ||''', tabname => '''||table_name||''' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE,method_opt => '''||' FOR ALL COLUMNS SIZE AUTO'''||' , degree => 8);' gather from dba_tables
where table_name in ('&&tabname')
and OWNER='&&owner'
;
or
BEGIN
for i in (select OWNER,TABLE_NAME from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='ANUJ'
) loop
SYS.DBMS_STATS.GATHER_TABLE_STATS (
ownname => i.OWNER
,TabName => i.TABLE_NAME
,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Method_Opt => 'FOR ALL COLUMNS SIZE AUTO '
,Degree => NULL
,Cascade => DBMS_STATS.AUTO_CASCADE
,No_Invalidate => DBMS_STATS.AUTO_INVALIDATE
,Force => FALSE);
end loop;
END;
/
ww
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ANUJ',tabname => 'T100', degree => DBMS_STATS.AUTO_DEGREE,cascade => DBMS_STATS.AUTO_CASCADE, method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
====
change table name
set linesize 100 pagesize 300
define tabname='EMP'
define owner='ANUJ'
select rpad('ANDV_ALGO_INTERNAL_OBSERVE : ',50)||dbms_stats.get_prefs(pname=>'ANDV_ALGO_INTERNAL_OBSERVE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('APPROXIMATE_NDV : ',50)||dbms_stats.get_prefs(pname=>'APPROXIMATE_NDV', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('APPROXIMATE_NDV_ALGORITHM : ',50)||dbms_stats.get_prefs(pname=>'APPROXIMATE_NDV_ALGORITHM', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('AUTO_STAT_EXTENSIONS : ',50)||dbms_stats.get_prefs(pname=>'AUTO_STAT_EXTENSIONS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('AUTOSTATS_TARGET : ',50)||dbms_stats.get_prefs(pname=>'AUTOSTATS_TARGET', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('CASCADE : ',50)||dbms_stats.get_prefs(pname=>'CASCADE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('CONCURRENT : ',50)||dbms_stats.get_prefs(pname=>'CONCURRENT', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('DEBUG : ',50)||dbms_stats.get_prefs(pname=>'DEBUG', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('DEGREE : ',50)||dbms_stats.get_prefs(pname=>'DEGREE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('ENABLE_HYBRID_HISTOGRAMS : ',50)||dbms_stats.get_prefs(pname=>'ENABLE_HYBRID_HISTOGRAMS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('ENABLE_TOP_FREQ_HISTOGRAMS : ',50)||dbms_stats.get_prefs(pname=>'ENABLE_TOP_FREQ_HISTOGRAMS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('ESTIMATE_PERCENT : ',50)||dbms_stats.get_prefs(pname=>'ESTIMATE_PERCENT', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('GATHER_AUTO : ',50)||dbms_stats.get_prefs(pname=>'GATHER_AUTO', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('GATHER_SCAN_RATE : ',50)||dbms_stats.get_prefs(pname=>'GATHER_SCAN_RATE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('GLOBAL_TEMP_TABLE_STATS : ',50)||dbms_stats.get_prefs(pname=>'GLOBAL_TEMP_TABLE_STATS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('GRANULARITY : ',50)||dbms_stats.get_prefs(pname=>'GRANULARITY', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('INCREMENTAL : ',50)||dbms_stats.get_prefs(pname=>'INCREMENTAL', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('INCREMENTAL_INTERNAL_CONTROL : ',50)||dbms_stats.get_prefs(pname=>'INCREMENTAL_INTERNAL_CONTROL', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('INCREMENTAL_LEVEL : ',50)||dbms_stats.get_prefs(pname=>'INCREMENTAL_LEVEL', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('INCREMENTAL_STALENESS : ',50)||dbms_stats.get_prefs(pname=>'INCREMENTAL_STALENESS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('JOB_OVERHEAD : ',50)||dbms_stats.get_prefs(pname=>'JOB_OVERHEAD', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('JOB_OVERHEAD_PERC : ',50)||dbms_stats.get_prefs(pname=>'JOB_OVERHEAD_PERC', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('METHOD_OPT : ',50)||dbms_stats.get_prefs(pname=>'METHOD_OPT', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('NO_INVALIDATE : ',50)||dbms_stats.get_prefs(pname=>'NO_INVALIDATE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('OPTIONS : ',50)||dbms_stats.get_prefs(pname=>'OPTIONS', tabname=>'&&TABLE' ,ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('PREFERENCE_OVERRIDES_PARAMETER : ',50)||dbms_stats.get_prefs(pname=>'PREFERENCE_OVERRIDES_PARAMETER', tabname=>'&&TABLE' ,ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('PUBLISH : ',50)||dbms_stats.get_prefs(pname=>'PUBLISH', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('SCAN_RATE : ',50)||dbms_stats.get_prefs(pname=>'SCAN_RATE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('STALE_PERCENT : ',50)||dbms_stats.get_prefs(pname=>'STALE_PERCENT', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('STAT_CATEGORY : ',50)||dbms_stats.get_prefs(pname=>'STAT_CATEGORY', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('SYS_FLAGS : ',50)||dbms_stats.get_prefs(pname=>'SYS_FLAGS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('TRACE : ',50)||dbms_stats.get_prefs(pname=>'TRACE', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
select rpad('WAIT_TIME_TO_UPDATE_STATS : ',50)||dbms_stats.get_prefs(pname=>'WAIT_TIME_TO_UPDATE_STATS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE
-- 19c
--select rpad('AUTO_TASK_INTERVAL : ',50)||dbms_stats.get_prefs(pname=>'AUTO_TASK_INTERVAL', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
--select rpad('AUTO_TASK_MAX_RUN_TIME : ',50)||dbms_stats.get_prefs(pname=>'AUTO_TASK_MAX_RUN_TIME', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
--select rpad('AUTO_TASK_STATUS : ',50)||dbms_stats.get_prefs(pname=>'AUTO_TASK_STATUS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
--select rpad('ROOT_TRIGGER_PDB : ',50)||dbms_stats.get_prefs(pname=>'ROOT_TRIGGER_PDB', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
--select rpad('COORDINATOR_TRIGGER_SHARD : ',50)||dbms_stats.get_prefs(pname=>'COORDINATOR_TRIGGER_SHARD', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
--select rpad('MAINTAIN_STATISTICS_STATUS : ',50)||dbms_stats.get_prefs(pname=>'MAINTAIN_STATISTICS_STATUS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual UNION ALL
FROM dual;
PREFS_FOR__EMP
----------------------------------------------------------------------------------------------------
ANDV_ALGO_INTERNAL_OBSERVE : FALSE
APPROXIMATE_NDV : TRUE
APPROXIMATE_NDV_ALGORITHM : REPEAT OR HYPERLOGLOG
AUTO_STAT_EXTENSIONS : OFF
AUTOSTATS_TARGET : AUTO
CASCADE : DBMS_STATS.AUTO_CASCADE
CONCURRENT : OFF
DEBUG : 0
DEGREE : NULL
ENABLE_HYBRID_HISTOGRAMS : 3
ENABLE_TOP_FREQ_HISTOGRAMS : 3
ESTIMATE_PERCENT : DBMS_STATS.AUTO_SAMPLE_SIZE
GATHER_AUTO : AFTER_LOAD
GATHER_SCAN_RATE : HADOOP_ONLY
GLOBAL_TEMP_TABLE_STATS : SESSION
GRANULARITY : AUTO
INCREMENTAL : FALSE
INCREMENTAL_INTERNAL_CONTROL : TRUE
INCREMENTAL_LEVEL : PARTITION
INCREMENTAL_STALENESS : ALLOW_MIXED_FORMAT
JOB_OVERHEAD : -1
JOB_OVERHEAD_PERC : 1
METHOD_OPT : FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE : DBMS_STATS.AUTO_INVALIDATE
OPTIONS : GATHER
PREFERENCE_OVERRIDES_PARAMETER : FALSE
PUBLISH : TRUE
SCAN_RATE : 0
STALE_PERCENT : 10
STAT_CATEGORY : OBJECT_STATS
SYS_FLAGS : 1
TABLE_CACHED_BLOCKS : 1
TRACE : 0
WAIT_TIME_TO_UPDATE_STATS : 15
34 rows selected.
====
col owner for a20
col table_name for a20
col incremental for a15
col granularity for a15
col stale_percent for a15
col estimate_percent for a30
col cascade for a30
col method_opt for a30
SELECT
owner, table_name,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'INCREMENTAL') incremental,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'GRANULARITY') granularity,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'STALE_PERCENT') stale_percent,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'ESTIMATE_PERCENT') estimate_percent,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'CASCADE') cascade,
DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
FROM dba_tables
WHERE table_name like :h
order by owner, table_name;
OWNER TABLE_NAME INCREMENTAL GRANULARITY STALE_PERCENT ESTIMATE_PERCENT CASCADE METHOD_OPT
-------------------- -------------------- --------------- --------------- --------------- ------------------------------ ------------------------------ ------------------------------
ANUJ EMP FALSE AUTO 10 DBMS_STATS.AUTO_SAMPLE_SIZE DBMS_STATS.AUTO_CASCADE FOR ALL COLUMNS SIZE AUTO
ANUJ1 EMP FALSE AUTO 10 DBMS_STATS.AUTO_SAMPLE_SIZE DBMS_STATS.AUTO_CASCADE FOR ALL COLUMNS SIZE AUTO
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
exec dbms_stats.set_table_prefs('SCOTT', EMP', 'STALE_PERCENT', '1');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');
exec dbms_stats.set_table_prefs('SCOTT', EMP', 'STALE_PERCENT', '10');
for trace
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
ALTER SESSION SET EVENTS '10046 trace name context off';
=====
Stats info ...
define tab='EMP'
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
col INDEX_NAME for A20
col TABLE_NAME for A20
col COLUMN_NAME for A20
col OWNER for a20
select OWNER,table_name,stats_update_time from dba_tab_stats_history where table_name = '&tab'
-- tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len from dba_tables
where table_name = '&tab';
select OWNER,table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len from dba_tab_pending_stats
where table_name = '&tab'
and partition_name is null;
-- indexes
select OWNER,index_name, last_analyzed ANALYZE_TIME, num_rows, leaf_blocks, distinct_keys from dba_indexes
where table_name = '&tab'
order by index_name;
select OWNER,table_name,index_name, last_analyzed "analyze time", num_rows, leaf_blocks, distinct_keys from dba_ind_pending_stats
where table_name = '&tab'
and partition_name is null
order by index_name;
-- columns
select OWNER,table_name,column_name, last_analyzed ANALYZE_TIME, num_distinct, num_nulls, density from dba_tab_columns
where table_name = '&tab'
order by OWNER,table_name,column_name;
select OWNER,table_name,column_name, last_analyzed "analyze time", num_distinct, num_nulls, density from dba_col_pending_stats
where table_name = '&tab'
and partition_name is null
order by column_name;
select OWNER,table_name,stats_update_time from dba_tab_stats_history where table_name = '&tab';
====
define T_NAME='EMP'
set verify off
set linesize 300
set long 30
column constraint_name format a30
column constraint_type format a2 heading CT
column column_name format a30
column position format 99 heading CP
column r_owner format a30
column r_constraint_name format a30
break on constraint_name skip 1 on constraint_type
select con.constraint_name, con.constraint_type, con.status, con.r_owner, con.r_constraint_name,
col.column_name, col.position, con.search_condition
from dba_constraints con,
dba_cons_columns col
where col.owner = con.owner
and col.constraint_name = con.constraint_name
-- and con.owner = 'T_OWNER'
and con.table_name = '&T_NAME'
order by con.constraint_name, col.position;
====
-- any analysed tables with significant chaining
set pages 200 linesize 300
col table_name form a25 word wrap
col owner form a15 word wrap
col num_rows form 999,999,999
col chain_cnt form 9,999,999
col pct_chn form 99.99
--accept tab_name char prompt 'which tables are to be checked ? >'
prompt only works on analyzed tables/partitioned tables
--spool tab_chain.lst
select table_name
,owner
,num_rows
,chain_cnt
,(nvl(chain_cnt,1)/nvl(num_rows,1))*100 pct_chn
from sys.dba_tables
-- NB keep greatest for num_rows and percentage in line else a table with
-- (in this case) less than 1000 rows will always be detected.
where greatest(nvl(chain_cnt,1),1) /greatest(nvl(num_rows,1),1000) > 0.001
and table_name like upper(':h'||'%')
and owner not in ('SYS','SYSTEM')
union
select table_name||' '||partition_name table_name
,table_owner owner
,num_rows
,chain_cnt
,(nvl(chain_cnt,1)/nvl(num_rows,1))*100 pct_chn
from sys.dba_tab_partitions
-- NB keep greatest for num_rows and percentage in line else a table with
-- (in this case) less than 1000 rows will always be detected.
where greatest(nvl(chain_cnt,1),1) /greatest(nvl(num_rows,1),1000) > 0.001
and table_name like upper(':h'||'%')
and table_owner not in ('SYS','SYSTEM')
order by 1,2
/
--spool off
clear colu
====
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
var h varchar2(30)
begin :h := 'ANUJ'; end; ---- User Name !!!!
/
SELECT
username,
DBMS_STATS.get_prefs(ownname=>USER,pname=>'INCREMENTAL') incremental,
DBMS_STATS.get_prefs(ownname=>USER,pname=>'GRANULARITY') granularity,
DBMS_STATS.get_prefs(ownname=>USER,pname=>'STALE_PERCENT') stale_percent,
DBMS_STATS.get_prefs(ownname=>USER,pname=>'ESTIMATE_PERCENT') estimate_percent,
DBMS_STATS.get_prefs(ownname=>USER,pname=>'CASCADE') cascade,
DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
FROM dba_users
WHERE username = :h
ORDER BY username;
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
var h varchar2(30)
begin :h := 'T101'; end; ---- table name here!!!!
/
SELECT
owner, table_name,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'INCREMENTAL') incremental,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'GRANULARITY') granularity,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'STALE_PERCENT') stale_percent,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'ESTIMATE_PERCENT') estimate_percent,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'CASCADE') cascade,
DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
FROM dba_tables
WHERE table_name = :h
ORDER BY owner, table_name;
exec dbms_stats.set_table_prefs('SCOTT', EMP', 'STALE_PERCENT', '1');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');
exec dbms_stats.set_table_prefs('SCOTT', EMP', 'STALE_PERCENT', '10');
===
alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
set linesize 300
select * from (select owner, table_name,num_rows,last_analyzed from dba_tables where table_name='XXX'),
(select count(*) from o.XXX);
=======
set head off feed off
set pages 10000 lines 500
col command for a200
select 'exec dbms_stats.gather_table_stats(ownname => '''||owner||''',tabname => '''||table_name ||''', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE,method_opt => '' FOR ALL COLUMNS SIZE AUTO'', degree => 8);' command
from dba_tables
where owner='TEST';
set pages 10000 lines 500
col command for a250
select 'exec dbms_stats.gather_index_stats( ownname=> '''||owner||''' ,indname=>''' || index_name ||''' ,estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE , degree=> 6 ,no_invalidate=> DBMS_STATS.AUTO_INVALIDATE ,granularity=> ''AUTO'');' command
from dba_indexes
where owner ='TEST';
SELECT 'Exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'''||table_owner||''',tabname=>'''||table_name||''',partname=>'''||partition_name||''',
method_opt=>''FOR ALL COLUMS SIZE 1",estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE=>TRUE,DEGREE=>6);'
from DBA_TAB_PARTITIONS where table_owner in ('SCHEMA');
=========
--
DBA_TAB_STATS_HISTORY provides a history of table statistics modifications for all tables in the databasedefine 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
;
