Oracle Role detail report

Oracle role detail report


Author : Daniel W. Fink


SET LINESIZE 300 PAGESIZE 50000 FEEDBACK OFF ECHO OFF VERIFY OFF SHOWMODE OFF TRIMSPOOL ON
COLUMN grantee_name FORMAT A30 HEADING 'User Name'
COLUMN role_path FORMAT A50 HEADING 'Role Hierarchy'
COLUMN priv_obj_name FORMAT A60 HEADING 'Privilege/Object Name'
COLUMN sort_order1 NOPRINT
COLUMN priv_path_names NOPRINT
COLUMN insert_priv FORMAT A2 HEADING 'IN'
COLUMN update_priv FORMAT A2 HEADING 'UP'
COLUMN delete_priv FORMAT A2 HEADING 'DE'
COLUMN select_priv FORMAT A2 HEADING 'SE'
COLUMN alter_priv FORMAT A2 HEADING 'AL'
COLUMN execute_priv FORMAT A2 HEADING 'EX'
COLUMN other_priv FORMAT A2 HEADING 'OT'


BREAK ON grantee_name NODUPLICATE SKIP 1 ON role_path NODUPLICATE

SELECT 1 sort_order1,
urm.priv_path_names priv_path_names,
CASE WHEN urm.root_user_id != urm.priv_path THEN SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')-1)
ELSE urm.priv_path_names
END grantee_name,
CASE WHEN urm.root_user_id = urm.priv_path THEN 'Direct'
ELSE 'Role '||REPLACE(urm.priv_path_names, SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')))
END role_path,
spm.name priv_obj_name,
NULL insert_priv,
NULL update_priv,
NULL delete_priv,
NULL select_priv,
NULL alter_priv,
NULL execute_priv,
NULL other_priv
FROM system_privilege_map spm,
sysauth$ sa,
( SELECT CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, 1, INSTR(urm2.priv_path, '.')-1)
ELSE urm2.priv_path
END root_user_id,
CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, INSTR(urm2.priv_path, '.', -1)+1)
ELSE urm2.priv_path
END role_id,
urm2.priv_path,
urm2.priv_path_names
FROM ( SELECT TO_CHAR(u.user#) priv_path,
u.name priv_path_names
FROM user$ u
UNION
SELECT sam.priv_path priv_path,
sam.priv_path_names priv_path_names
FROM (SELECT sa.grantee#,
sa.privilege#,
LTRIM(SYS_CONNECT_BY_PATH(sa.grantee#, '.'), '.')||'.'||sa.privilege# priv_path,
LTRIM(SYS_CONNECT_BY_PATH(u1.name, '.'), '.')||'.'||u2.name priv_path_names
FROM sysauth$ sa,
user$ u1,
user$ u2
WHERE sa.privilege# > 0
AND u1.user# = sa.grantee#
AND u2.user# = sa.privilege#
CONNECT BY sa.grantee# = PRIOR sa.privilege#
ORDER SIBLINGS BY sa.privilege#
) sam
) urm2
) urm
WHERE urm.root_user_id IN ( SELECT usq.user#
FROM user$ usq
WHERE usq.type# = 1
AND usq.name NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','CTXSYS','PERFSTAT')
)
AND sa.grantee# = urm.role_id
AND sa.privilege# < 0
AND sa.privilege# = spm.privilege
UNION
SELECT 2 sort_order1,
urm.priv_path_names priv_path_names,
CASE WHEN urm.root_user_id != urm.priv_path THEN SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')-1)
ELSE urm.priv_path_names
END grantee_name,
CASE WHEN urm.root_user_id = urm.priv_path THEN 'Direct'
ELSE 'Role '||REPLACE(urm.priv_path_names, SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')))
END role_path,
u.name||'.'||o.name priv_obj_name,
MAX(DECODE(tpm.name, 'INSERT', 'X', NULL)) insert_priv,
MAX(DECODE(tpm.name, 'UPDATE', 'X', NULL)) update_priv,
MAX(DECODE(tpm.name, 'DELETE', 'X', NULL)) delete_priv,
MAX(DECODE(tpm.name, 'SELECT', 'X', NULL)) select_priv,
MAX(DECODE(tpm.name, 'ALTER', 'X', NULL)) alter_priv,
MAX(DECODE(tpm.name, 'EXECUTE', 'X', NULL)) execute_priv,
MAX(DECODE(tpm.name, 'INSERT', NULL, 'UPDATE', NULL, 'DELETE', NULL,
'SELECT', NULL, 'ALTER', NULL, 'EXECUTE', NULL,
'X' )) other_priv
FROM objauth$ oa,
obj$ o,
user$ u,
table_privilege_map tpm,
( SELECT CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, 1, INSTR(urm2.priv_path, '.')-1)
ELSE urm2.priv_path
END root_user_id,
CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, INSTR(urm2.priv_path, '.', -1)+1)
ELSE urm2.priv_path
END role_id,
urm2.priv_path,
urm2.priv_path_names
FROM ( SELECT TO_CHAR(u.user#) priv_path,
u.name priv_path_names
FROM user$ u
UNION
SELECT sam.priv_path priv_path,
sam.priv_path_names priv_path_names
FROM (SELECT sa.grantee#,
sa.privilege#,
LTRIM(SYS_CONNECT_BY_PATH(sa.grantee#, '.'), '.')||'.'||sa.privilege# priv_path,
LTRIM(SYS_CONNECT_BY_PATH(u1.name, '.'), '.')||'.'||u2.name priv_path_names
FROM sysauth$ sa,
user$ u1,
user$ u2
WHERE sa.privilege# > 0
AND u1.user# = sa.grantee#
AND u2.user# = sa.privilege#
CONNECT BY sa.grantee# = PRIOR sa.privilege#
ORDER SIBLINGS BY sa.privilege#
) sam
) urm2
) urm
WHERE urm.root_user_id IN ( SELECT usq.user#
FROM user$ usq
WHERE usq.type# = 1
AND usq.name NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','CTXSYS','PERFSTAT')
)
AND oa.grantee# = urm.role_id
AND oa.obj# = o.obj#
AND o.owner# != 59
AND o.owner# = u.user#
AND oa.privilege# = tpm.privilege
GROUP BY 2, urm.root_user_id, urm.priv_path, urm.priv_path_names, u.name, o.name
ORDER BY priv_path_names, sort_order1, priv_obj_name


SPOOL allprivs.lst

/

SPOOL OFF





Role RESOURCE CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE

Oracle Table info

Table info and statistics
Get current optimizer statistics for a table


Author : Daniel W. Fink


SET VERIFY OFF PAGESIZE 400 LINESIZE 135

DEFINE owner_table = &1
COLUMN spoolname FORMAT A50 NEW_VALUE spool_name NOPRINT

SELECT 'tab_idx_info_'||UPPER('&&owner_table')||'_'||TO_CHAR(SYSDATE, 'YYYYMMDDhh24miss')||'.log' spoolname
FROM dual
/

SPOOL &&spool_name


COLUMN tab_degree FORMAT 9999 HEADING 'Deg'
COLUMN tab_partitioned FORMAT A4 HEADING 'Prtn'
COLUMN tab_num_rows FORMAT 999,999,999 HEADING 'Rows'
COLUMN tab_alloc_blocks FORMAT 999,999,999 HEADING 'Allocated|Blocks'
COLUMN tab_hwm_blocks FORMAT 999,999,999 HEADING 'HWM|Blocks'
COLUMN tab_last_analyzed_time FORMAT A17 HEADING 'Analyzed Date'
COLUMN tab_analyzed_pct FORMAT 999.99 HEADING 'Analyze|Pct'
COLUMN tab_avg_space FORMAT 99999 HEADING 'Avg Block|Free Space'
COLUMN tab_avg_row_length FORMAT 99999 HEADING 'Avg Row|Length'
COLUMN tab_monitoring FORMAT A4 HEADING 'Mntr'

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Table Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT


SELECT TO_NUMBER(t.degree) tab_degree,
t.partitioned tab_partitioned,
t.num_rows tab_num_rows,
t.blocks tab_alloc_blocks,
(t.blocks - t.empty_blocks) tab_hwm_blocks,
TO_CHAR(t.last_analyzed, 'MM/DD/YYYY hh24:mi') tab_last_analyzed_time,
ROUND((t.sample_size/DECODE(t.num_rows,0,1,t.num_rows))*100,2)
tab_analyzed_pct,
t.avg_space tab_avg_space,
t.avg_row_len tab_avg_row_length,
t.monitoring tab_monitoring
FROM dba_tables t
WHERE t.owner||'.'||t.table_name = UPPER('&&owner_table')
ORDER BY t.table_name
/



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'

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Column Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT

SELECT tc.column_name tab_column_name,
tc.data_type tab_column_datatype,
DECODE(tc.nullable, 'N', 'NOT NULL', NULL) tab_column_nullable,
tc.num_distinct tab_column_numdistinct,
tc.density tab_column_density,
tc.num_nulls tab_column_numnulls,
DECODE(tc.histogram,'NONE', NULL, tc.histogram) tab_column_histogram,
TO_NUMBER(DECODE(tc.num_buckets,1,NULL,
tc.num_buckets)) tab_column_numbuckets
FROM dba_tab_columns tc
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
ORDER BY tc.column_id
/


PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT

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, 'MM/DD/YYYY hh24:mi') last_analyzed_time
FROM dba_indexes i
WHERE i.table_owner||'.'||i.table_name = UPPER('&&owner_table')
ORDER BY i.uniqueness DESC, i.index_name
/

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

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Columns for &&owner_table
PROMPT *********************************************************************************
PROMPT

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.owner||'.'||tc.table_name = UPPER('&&owner_table')
),
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_owner||'.'||ic.table_name = UPPER('&&owner_table')
AND ic.column_name = chlvt.column_name
ORDER BY ic.index_name, ic.column_position
/

SPOOL off

====


APTDB\sys> @table_index SCOTT.EMP






*********************************************************************************
Table Statistics for SCOTT.EMP
*********************************************************************************


Allocated HWM Analyze Avg Block Avg Row
Deg Prtn Rows Blocks Blocks Analyzed Date Pct Free Space Length Mntr
----- ---- ------------ ------------ ------------ ----------------- ------- ---------- ------- ----
1 NO 14 5 5 07/07/2011 22:00 100.00 0 38 YES



*********************************************************************************
Column Statistics for SCOTT.EMP
*********************************************************************************


Distinct Number
Column Name Datatype Nullable? Values Density of Nulls Histogram Buckets
------------------------------ -------------------- ---------- ------------ -------- ------------ ---------------- --------
EMPNO NUMBER NOT NULL 14 .07143 0
ENAME VARCHAR2 14 .07143 0
JOB VARCHAR2 5 .20000 0
MGR NUMBER 6 .16667 1
HIREDATE DATE 13 .07692 0
SAL NUMBER 12 .08333 0
COMM NUMBER 4 .25000 10
DEPTNO NUMBER 3 .03571 0 FREQUENCY 3

8 rows selected.



*********************************************************************************
Index Statistics for SCOTT.EMP
*********************************************************************************


IND_NAME Status Unq Lvl Leaf Blks Indx Rows Dstnct Keys Clstr Fctr Analyzed Date
------------------------------ ------- --- ---- --------- ------------ ------------ ------------ -----------------
PK_EMP VALID Y 0 1 14 14 1 07/07/2011 22:00



*********************************************************************************
Index Columns for SCOTT.EMP
*********************************************************************************


Index Name Column Name Low Value High Value
------------------------------ ------------------------------ -------------------- --------------------
PK_EMP EMPNO 7369 7934

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

define table_owner='SCOTT'

define owner='SCOTT'
define table_name='TABLESUSER'

define 1='SCOTT' --- owner 

COL owner NEW_V owner FOR A30 HEA 'TABLE_OWNER';
SELECT DISTINCT t.owner
  FROM dba_tables t,
       dba_users u
 WHERE u.username = t.owner
   AND u.oracle_maintained = 'N' 
   AND u.username NOT LIKE 'C##'||CHR(37) 
 ORDER BY 1
/
COL table_owner NEW_V table_owner FOR A30;
PRO
PRO 1. Table Owner:
DEF table_owner = '&1.';
UNDEF 1;
SELECT UPPER(TRIM(NVL('&&table_owner.', '&&owner.'))) table_owner FROM DUAL
/
--

COL name NEW_V name FOR A30 HEA 'TABLE_NAME';
COL num_rows FOR 99,999,999,990;
COL blocks FOR 99,999,999,990;
COL rows_per_block FOR 999,990.0;
COL avg_row_len FOR 999,990;
COL lobs FOR 9990;
SELECT t.table_name name, t.num_rows, t.blocks, ROUND(t.num_rows / NULLIF(t.blocks, 0), 1) AS rows_per_block, t.avg_row_len,
       (SELECT COUNT(*) FROM dba_lobs l WHERE l.owner = t.owner AND l.table_name = t.table_name) AS lobs,
       t.partitioned
  FROM dba_tables t,
       dba_users u
 WHERE t.owner = UPPER(TRIM('&&table_owner.'))
   AND u.username = t.owner
   AND u.oracle_maintained = 'N' 
   AND u.username NOT LIKE 'C##'||CHR(37) 
 ORDER BY 1
/



--
COL owner FOR A30 HEA 'Owner' TRUNC;
COL segment_name FOR A30 TRUNC;
COL partition_name FOR A30 TRUNC;
COL column_name FOR A30 TRUNC;
COL segments FOR 9,999,990;
--
COL mebibytes FOR 999,999,990.000 HEA 'Size MiB';
COL megabytes FOR 999,999,990.000 HEA 'Size MB';
COL tablespace_name FOR A30 HEA 'Tablespace';
BREAK ON REPORT;
COMPUTE SUM LABEL 'TOTAL' OF mebibytes megabytes segments ON REPORT;
--
PRO
PRO SEGMENTS (dba_segments) top 100 &&table_owner..&&table_name.
PRO ~~~~~~~~
WITH
t AS (
SELECT owner, table_name
  FROM dba_tables
 WHERE owner = '&&table_owner.'
   AND table_name = '&&table_name.'
),
s AS (
SELECT 1 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name
  FROM t, dba_segments s
 WHERE s.owner = t.owner
   AND s.segment_name = t.table_name
   AND s.segment_type LIKE 'TABLE%'
 UNION ALL
SELECT 2 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name
  FROM t, dba_indexes i, dba_segments s
 WHERE i.table_owner = t.owner
   AND i.table_name = t.table_name
   AND s.owner = i.owner
   AND s.segment_name = i.index_name
   AND s.segment_type LIKE 'INDEX%'
 UNION ALL
SELECT 3 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, l.column_name, s.bytes, s.tablespace_name
  FROM t, dba_lobs l, dba_segments s
 WHERE l.owner = t.owner
   AND l.table_name = t.table_name
   AND s.owner = l.owner
   AND s.segment_name = l.segment_name
   AND s.segment_type LIKE 'LOB%'
)
--SELECT ROUND(bytes/POWER(2,20),3) AS mebibytes, segment_type, owner, column_name, segment_name, partition_name, tablespace_name
SELECT ROUND(bytes/POWER(10,6),3) AS megabytes, segment_type, owner, column_name, segment_name, partition_name, tablespace_name
  FROM s
 ORDER BY bytes DESC, oby, segment_type, owner, column_name, segment_name, partition_name
 FETCH FIRST 100 ROWS ONLY
/
--
PRO
PRO SEGMENT TYPE (dba_segments) &&table_owner..&&table_name.
PRO ~~~~~~~~~~~~
WITH
t AS (
SELECT owner, table_name
  FROM dba_tables
 WHERE owner = '&&table_owner.'
   AND table_name = '&&table_name.'
),
s AS (
SELECT 1 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name
  FROM t, dba_segments s
 WHERE s.owner = t.owner
   AND s.segment_name = t.table_name
   AND s.segment_type LIKE 'TABLE%'
 UNION ALL
SELECT 2 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name
  FROM t, dba_indexes i, dba_segments s
 WHERE i.table_owner = t.owner
   AND i.table_name = t.table_name
   AND s.owner = i.owner
   AND s.segment_name = i.index_name
   AND s.segment_type LIKE 'INDEX%'
 UNION ALL
SELECT 3 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, l.column_name, s.bytes, s.tablespace_name
  FROM t, dba_lobs l, dba_segments s
 WHERE l.owner = t.owner
   AND l.table_name = t.table_name
   AND s.owner = l.owner
   AND s.segment_name = l.segment_name
   AND s.segment_type LIKE 'LOB%'
)
--SELECT segment_type, COUNT(*) AS segments, ROUND(SUM(bytes)/POWER(2,20),3) AS mebibytes, tablespace_name
SELECT segment_type, COUNT(*) AS segments, ROUND(SUM(bytes)/POWER(10,6),3) AS megabytes, tablespace_name
  FROM s
 GROUP BY oby, segment_type, tablespace_name
 ORDER BY oby, segment_type, tablespace_name
/
--
CLEAR BREAK COMPUTE;
--
COL partitioned FOR A4 HEA 'Part';
COL degree FOR A10 HEA 'Degree';
COL temporary FOR A4 HEA 'Temp';
COL blocks FOR 999,999,990 HEA 'Blocks';
COL num_rows FOR 999,999,999,990 HEA 'Num Rows';
COL avg_row_len FOR 999,999,990 HEA 'Avg Row Len';
COL size_MiB FOR 999,999,990.000 HEA 'Size MiB';
COL seg_size_MiB FOR 999,999,990.000 HEA 'Seg Size MiB';
COL estimated_MiB FOR 999,999,990.000 HEA 'Estimated MiB';
COL size_MB FOR 999,999,990.000 HEA 'Size MB';
COL seg_size_MB FOR 999,999,990.000 HEA 'Seg Size MB';
COL estimated_MB FOR 999,999,990.000 HEA 'Estimated MB';
COL sample_size FOR 999,999,999,990 HEA 'Sample Size';
COL last_analyzed FOR A19 HEA 'Last Analyzed';
COL compression FOR A12 HEA 'Compression';
COL tablespace_name FOR A30 HEA 'Tablespace';
--
PRO
PRO TABLES (dba_tables) &&table_owner..&&table_name.
PRO ~~~~~~
SELECT CASE t.partitioned WHEN 'YES' THEN (SELECT TRIM(TO_CHAR(COUNT(*))) FROM dba_tab_partitions tp WHERE tp.table_owner = t.owner AND tp.table_name = t.table_name) ELSE t.partitioned END AS partitioned,
       t.degree,
       t.temporary,
       t.blocks,
       --t.blocks * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(2,20) AS size_MiB,
       t.blocks * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(10,6) AS size_MB,
       --(SELECT SUM(s.bytes) / POWER(2,20) FROM dba_segments s WHERE s.owner = t.owner AND s.segment_name = t.table_name AND s.segment_type LIKE 'TABLE%') AS seg_size_MiB,
       (SELECT SUM(s.bytes) / POWER(10,6) FROM dba_segments s WHERE s.owner = t.owner AND s.segment_name = t.table_name AND s.segment_type LIKE 'TABLE%') AS seg_size_MB,
       t.num_rows,
       t.avg_row_len, 
       --t.num_rows * t.avg_row_len / POWER(2,20) AS estimated_MiB,
       t.num_rows * t.avg_row_len / POWER(10,6) AS estimated_MB,
       t.sample_size,
       TO_CHAR(t.last_analyzed, '&&cs_datetime_full_format.') AS last_analyzed,
       t.compression,
       t.tablespace_name
  FROM dba_tables t,
       dba_tablespaces b,
       v$parameter p
 WHERE t.owner = '&&table_owner.'
   AND t.table_name = '&&table_name.' 
   AND b.tablespace_name(+) = t.tablespace_name
   AND p.name = 'db_block_size'
/
--
COL analyzetime FOR A19 HEA 'Analyze Time';
COL rowcnt FOR 999,999,999,990 HEA 'Row Count';
COL blkcnt FOR 999,999,990 HEA 'Block Count';
COL avgrln FOR 999,999,990 HEA 'Avg Row Len';
COL samplesize FOR 999,999,999,990 HEA 'Sample Size';
COL rows_inc FOR 999,999,999,990 HEA 'Rows Increase';
COL days_gap FOR 999,990.0 HEA 'Days Gap';
COL monthly_growth_perc FOR 999,990.000 HEA 'Monthly Growth Perc%';
--
PRO
PRO CBO STAT TABLE HISTORY (wri$_optstat_tab_history and dba_tables) &&table_owner..&&table_name.
PRO ~~~~~~~~~~~~~~~~~~~~~~
WITH
cbo_hist AS (
SELECT h.analyzetime,
       h.rowcnt,
       h.blkcnt,
       h.avgrln,
       h.samplesize
  FROM dba_objects o,
       wri$_optstat_tab_history h
 WHERE o.owner = '&&table_owner.'
   AND o.object_name = '&&table_name.' 
   AND o.object_type = 'TABLE'
   AND h.obj# = o.object_id
   AND h.analyzetime IS NOT NULL
 UNION
SELECT t.last_analyzed AS analyzetime,
       t.num_rows AS rowcnt,
       t.blocks AS blkcnt,
       t.avg_row_len AS avgrln,
       t.sample_size AS samplesize
  FROM dba_tables t
 WHERE t.owner = '&&table_owner.'
   AND t.table_name = '&&table_name.' 
),
cbo_hist_extended AS (
SELECT h.analyzetime,
       h.rowcnt,
       h.blkcnt,
       h.avgrln,
       h.samplesize,
       h.rowcnt - LAG(h.rowcnt) OVER (ORDER BY h.analyzetime) AS rows_inc,
       h.analyzetime - LAG(h.analyzetime) OVER (ORDER BY h.analyzetime) AS days_gap,
       100 * (365.25 / 12) * (h.rowcnt - LAG(h.rowcnt) OVER (ORDER BY h.analyzetime)) / NULLIF((h.analyzetime - LAG(h.analyzetime) OVER (ORDER BY h.analyzetime)), 0) / NULLIF(h.rowcnt, 0) AS monthly_growth_perc
  FROM cbo_hist h
)
SELECT TO_CHAR(h.analyzetime, '&&cs_datetime_full_format.') AS analyzetime,
       h.blkcnt,
       h.rowcnt,
       h.rows_inc, 
       h.days_gap,
       h.monthly_growth_perc,
       h.avgrln,
       h.samplesize
  FROM cbo_hist_extended h
 ORDER BY
       1
/
PRO
PRO GROWTH (wri$_optstat_tab_history and dba_tables) &&table_owner..&&table_name.
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WITH
oldest AS (
SELECT h.analyzetime,
       h.rowcnt
  FROM dba_objects o,
       wri$_optstat_tab_history h
 WHERE o.owner = '&&table_owner.'
   AND o.object_name = '&&table_name.' 
   AND o.object_type = 'TABLE'
   AND h.obj# = o.object_id
   AND h.analyzetime IS NOT NULL
   AND h.rowcnt > 0
 ORDER BY
       h.analyzetime
 FETCH FIRST 1 ROW ONLY
),
newest AS (
SELECT t.last_analyzed AS analyzetime,
       t.num_rows AS rowcnt
  FROM dba_tables t
 WHERE t.owner = '&&table_owner.'
   AND t.table_name = '&&table_name.' 
   AND t.num_rows > 0
   AND ROWNUM = 1
)
SELECT 100 * (365 / 12) * (n.rowcnt - o.rowcnt) / (n.analyzetime - o.analyzetime) / o.rowcnt AS monthly_growth_perc
  FROM oldest o, newest n
 WHERE n.analyzetime > o.analyzetime
/
--
COL object_type HEA 'Object Type' FOR A30;
COL object_id FOR 999999999 HEA 'Object ID';
COL object_name FOR A30 HEA 'Object Name' TRUNC;
COL subobject_name FOR A30 HEA 'Sub Object Name' TRUNC;
COL created FOR A23 HEA 'Created';
COL last_ddl_time FOR A23 HEA 'Last DDL Time';
--
PRO
PRO TABLE OBJECTS (dba_objects) up to 100 &&table_owner..&&table_name.
PRO ~~~~~~~~~~~~~
SELECT o.object_type,
       o.subobject_name,
       o.object_id,
       TO_CHAR(o.created, '&&cs_datetime_full_format.') AS created,
       TO_CHAR(o.last_ddl_time, '&&cs_datetime_full_format.') AS last_ddl_time
  FROM dba_objects o
 WHERE o.owner = '&&table_owner.'
   AND o.object_name = '&&table_name.' 
   AND o.object_type LIKE 'TABLE%'
 ORDER BY
       o.object_type,
       o.object_id
FETCH FIRST 100 ROWS ONLY       
/
--
COL index_name FOR A30 HEA 'Index Name';
COL partitioned FOR A4 HEA 'Part';
COL orphaned_entries FOR A8 HEA 'Orphaned|Entries';
COL degree FOR A10 HEA 'Degree';
COL index_type FOR A27 HEA 'Index Type';
COL uniqueness FOR A10 HEA 'Uniqueness';
COL columns FOR 999,999 HEA 'Columns';
COL status FOR A8 HEA 'Status';
COL visibility FOR A10 HEA 'Visibility';
COL blevel FOR 99,990 HEA 'BLevel';
COL leaf_blocks FOR 999,999,990 HEA 'Leaf Blocks';
COL size_MiB FOR 999,999,990.000 HEA 'Size MiB';
COL seg_size_MiB FOR 999,999,990.000 HEA 'Seg Size MiB';
COL size_MB FOR 999,999,990.000 HEA 'Size MB';
COL seg_size_MB FOR 999,999,990.000 HEA 'Seg Size MB';
COL distinct_keys FOR 999,999,999,990 HEA 'Dist Keys';
COL clustering_factor FOR 999,999,999,990 HEA 'Clust Fact';
COL num_rows FOR 999,999,999,990 HEA 'Num Rows';
COL sample_size FOR 999,999,999,990 HEA 'Sample Size';
COL last_analyzed FOR A19 HEA 'Last Analyzed';
COL compression FOR A13 HEA 'Compression';
COL tablespace_name FOR A30 HEA 'Tablespace';
--
PRO
PRO INDEXES (dba_indexes) &&table_owner..&&table_name.
PRO ~~~~~~~
SELECT i.index_name,
       CASE i.partitioned WHEN 'YES' THEN (SELECT TRIM(TO_CHAR(COUNT(*))) FROM dba_ind_partitions ip WHERE ip.index_owner = i.owner AND ip.index_name = i.index_name) ELSE i.partitioned END AS partitioned,
       i.orphaned_entries,
       i.degree,
       i.index_type,
       i.uniqueness,
       (SELECT COUNT(*) FROM dba_ind_columns ic WHERE ic.index_owner = i.owner AND ic.index_name = i.index_name) AS columns,
       i.status,
       i.visibility,
       i.blevel,
       i.leaf_blocks,
       --i.leaf_blocks * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(2,20) AS size_MiB,
       i.leaf_blocks * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(10,6) AS size_MB,
       --(SELECT SUM(s.bytes) / POWER(2,20) FROM dba_segments s WHERE s.owner = i.owner AND s.segment_name = i.index_name AND s.segment_type LIKE 'INDEX%') AS seg_size_MiB,
       (SELECT SUM(s.bytes) / POWER(10,6) FROM dba_segments s WHERE s.owner = i.owner AND s.segment_name = i.index_name AND s.segment_type LIKE 'INDEX%') AS seg_size_MB,
       i.distinct_keys,
       i.clustering_factor,
       i.num_rows,
       i.sample_size,
       TO_CHAR(i.last_analyzed, '&&cs_datetime_full_format.') AS last_analyzed,
       i.compression,
       i.tablespace_name
  FROM dba_indexes i,
       dba_tablespaces b,
       v$parameter p
 WHERE i.table_owner = '&&table_owner.'
   AND i.table_name = '&&table_name.'
   AND b.tablespace_name(+) = i.tablespace_name
   AND p.name = 'db_block_size'
 ORDER BY
       i.index_name
/
--
PRO
PRO INDEX OBJECTS (dba_objects) up to 100 &&table_owner..&&table_name.
PRO ~~~~~~~~~~~~~
SELECT o.object_type,
       o.object_name,
       o.subobject_name,
       o.object_id,
       TO_CHAR(o.created, '&&cs_datetime_full_format.') AS created,
       TO_CHAR(o.last_ddl_time, '&&cs_datetime_full_format.') AS last_ddl_time
  FROM dba_indexes i,
       dba_objects o
 WHERE i.table_owner = '&&table_owner.'
   AND i.table_name = '&&table_name.'
   AND o.owner = i.owner
   AND o.object_name = i.index_name
   AND o.object_type LIKE 'INDEX%'
 ORDER BY
       o.object_type,
       o.object_name,
       o.subobject_name
FETCH FIRST 100 ROWS ONLY       
/
--
COL part_sub FOR A12 HEA 'LEVEL';
COL object_type FOR A5 HEA 'TYPE';
COL owner FOR A30 HEA 'Owner' TRUNC;
COL name FOR A30 HEA 'Name' TRUNC;
COL column_position FOR 999 HEA 'POS';
COL column_name FOR A30 TRUNC;
--
PRO
PRO PARTITION KEYS (dba_part_key_columns and dba_subpart_key_columns) &&table_owner..&&table_name.
PRO ~~~~~~~~~~~~~~
--
WITH /* PART_KEY_COLUMNS */
dba_tables_m AS (
SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(dba_tables) */ 
       t.owner, 
       t.table_name
  FROM dba_tables t
 WHERE t.owner = '&&table_owner.'
   AND t.table_name = '&&table_name.'
),
dba_indexes_m AS (
SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(dba_indexes) */ 
       i.owner,
       i.index_name
  FROM dba_tables_m t,
       dba_indexes i
 WHERE i.table_owner = t.owner
   AND i.table_name = t.table_name
),
objects_m AS (
SELECT owner, table_name AS name, 'TABLE' AS object_type
  FROM dba_tables_m
 UNION
SELECT owner, index_name AS name, 'INDEX' AS object_type
  FROM dba_indexes_m
)
SELECT 'PARTITION' AS part_sub,
       p.object_type,
       p.owner,
       p.name,
       p.column_position,
       p.column_name
  FROM dba_part_key_columns p,
       objects_m o
 WHERE o.owner = p.owner
   AND o.name = p.name
   AND o.object_type = p.object_type
 UNION ALL
SELECT 'SUBPARTITION' AS part_sub,
       p.object_type,
       p.owner,
       p.name,
       p.column_position,
       p.column_name
  FROM dba_subpart_key_columns p,
       objects_m o
 WHERE o.owner = p.owner
   AND o.name = p.name
   AND o.object_type = p.object_type
 ORDER BY
       1 ASC, 2 DESC, 3, 4, 5
/
--
COL index_name FOR A30 HEA 'Index Name';
COL visibility FOR A10 HEA 'Visibility';
COL partitioned FOR A4 HEA 'Part';
COL column_position FOR 999 HEA 'Pos';
COL column_name FOR A30 HEA 'Column Name';
COL data_type FOR A33 HEA 'Data Type';
COL nullable FOR A8 HEA 'Nullable';
COL data_default FOR A30 HEA 'Data Default';
COL num_distinct FOR 999,999,999,990 HEA 'Num Distinct';
COL low_value_translated FOR A64 HEA 'Low Value Translated';
COL high_value_translated FOR A64 HEA 'High Value Translated';
COL density FOR 0.000000000 HEA 'Density';
COL num_nulls FOR 999,999,999,990 HEA 'Num Nulls';
COL num_buckets FOR 999,990 HEA 'Buckets';
COL histogram FOR A15 HEA 'Histogram';
COL sample_size FOR 999,999,999,990 HEA 'Sample Size';
COL last_analyzed FOR A19 HEA 'Last Analyzed';
COL avg_col_len FOR 999,999,990 HEA 'Avg Col Len';
COL data_length FOR 999,999,990 HEA 'Data Length';
COL char_length FOR 999,999,990 HEA 'Char Length';
--
BRE ON index_name SKIP 1 ON visibility ON partitioned;
--
PRO
PRO INDEX COLUMNS (dba_ind_columns) &&table_owner..&&table_name.
PRO ~~~~~~~~~~~~~
WITH ic AS (SELECT /*+ MATERIALIZE NO_MERGE */ * FROM dba_ind_columns WHERE table_owner = '&&table_owner.' AND table_name = '&&table_name.' AND ROWNUM >= 1),
     tc AS (SELECT /*+ MATERIALIZE NO_MERGE */ * FROM dba_tab_cols WHERE owner = '&&table_owner.' AND table_name = '&&table_name.' AND ROWNUM >= 1),
     ix AS (SELECT /*+ MATERIALIZE NO_MERGE */ * FROM dba_indexes WHERE table_owner = '&&table_owner.' AND table_name = '&&table_name.' AND ROWNUM >= 1)
SELECT i.index_name,
       x.visibility,
       x.partitioned,
       i.column_position,
       c.column_name,
       c.data_type,
       c.nullable,
       c.data_default 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,64)
        WHEN c.data_type IN ('NVARCHAR2','NCHAR') THEN SUBSTR(to_char(utl_raw.cast_to_nvarchar2(c.low_value)),1,64)
        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'))||'T'||
                    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'))||'T'||
                    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,64)
        WHEN c.data_type IN ('NVARCHAR2','NCHAR') THEN SUBSTR(to_char(utl_raw.cast_to_nvarchar2(c.high_value)),1,64)
        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'))||'T'||
                    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'))||'T'||
                    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, '&&cs_datetime_full_format.') last_analyzed,
       c.avg_col_len,
       c.data_length, 
       c.char_length
  FROM ic i,
       tc c,
       ix x
 WHERE i.table_owner = '&&table_owner.'
   AND i.table_name = '&&table_name.'
   AND c.owner = i.table_owner
   AND c.table_name = i.table_name
   AND c.column_name = i.column_name
   AND x.table_owner = i.table_owner
   AND x.table_name = i.table_name
   AND x.index_name = i.index_name
 ORDER BY
       i.index_name,
       i.column_position
/
--
CL BRE;
--
COL column_id FOR 999 HEA 'ID';
COL column_name FOR A30 HEA 'Column Name';
COL data_type FOR A33 HEA 'Data Type';
COL nullable FOR A8 HEA 'Nullable';
COL data_default FOR A30 HEA 'Data Default';
COL num_distinct FOR 999,999,999,990 HEA 'Num Distinct';
COL low_value_translated FOR A64 HEA 'Low Value Translated';
COL high_value_translated FOR A64 HEA 'High Value Translated';
COL density FOR 0.000000000 HEA 'Density';
COL num_nulls FOR 999,999,999,990 HEA 'Num Nulls';
COL num_buckets FOR 999,990 HEA 'Buckets';
COL histogram FOR A15 HEA 'Histogram';
COL sample_size FOR 999,999,999,990 HEA 'Sample Size';
COL last_analyzed FOR A19 HEA 'Last Analyzed';
COL avg_col_len FOR 999,999,990 HEA 'Avg Col Len';
COL data_length FOR 999,999,990 HEA 'Data Length';
COL char_length FOR 999,999,990 HEA 'Char Length';
--
BRE ON owner ON table_name SKIP 1;
--
PRO
PRO TABLE COLUMNS (dba_tab_cols) &&table_owner..&&table_name.
PRO ~~~~~~~~~~~~~
SELECT c.column_id,
       c.column_name,
       c.data_type,
       c.nullable,
       c.data_default 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,64)
        WHEN c.data_type IN ('NVARCHAR2','NCHAR') THEN SUBSTR(to_char(utl_raw.cast_to_nvarchar2(c.low_value)),1,64)
        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'))||'T'||
                    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'))||'T'||
                    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,64)
        WHEN c.data_type IN ('NVARCHAR2','NCHAR') THEN SUBSTR(to_char(utl_raw.cast_to_nvarchar2(c.high_value)),1,64)
        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'))||'T'||
                    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'))||'T'||
                    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, '&&cs_datetime_full_format.') last_analyzed,
       c.avg_col_len,
       c.data_length, 
       c.char_length
  FROM dba_tab_cols c
 WHERE c.owner = '&&table_owner.'
   AND c.table_name = '&&table_name.'
 ORDER BY
       c.column_id
/
--
CL BRE;
--
COL column_name FOR A30 HEA 'Column Name';
COL index_name FOR A30 HEA 'Index Name';
COL segment_name FOR A30 HEA 'Segment Name';
COL bytes FOR 999,999,999,990 HEA 'Bytes';
COL blocks FOR 999,999,990 HEA 'Blocks';
COL size_MiB FOR 999,999,990.000 HEA 'Size MiB';
COL size_MB FOR 999,999,990.000 HEA 'Size MB';
COL deduplication FOR A13 HEA 'Deduplication';
COL compression FOR A11 HEA 'Compression';
COL encrypt FOR A7 HEA 'Encrypt';
COL cache FOR A5 HEA 'Cache';
COL securefile FOR A10 HEA 'SecureFile';
COL in_row FOR A6 HEA 'In Row';
COL tablespace_name FOR A30 HEA 'Tablespace';
--
BRE ON owner ON table_name SKIP 1;
--
SET HEA OFF;
PRO
PRO COLUMN USAGE REPORT (dbms_stats.report_col_usage) &&table_owner..&&table_name.
PRO ~~~~~~~~~~~~~~~~~~~
SELECT DBMS_STATS.report_col_usage('&&table_owner.', '&&table_name.')
  FROM DUAL
/
SET HEA ON;
--
PRO
PRO LOBS (dba_lobs) 
PRO ~~~~
SELECT l.column_name,
       l.index_name,
       l.segment_name,
       SUM(s.bytes) AS bytes,
       SUM(s.blocks) AS blocks,
       --SUM(s.blocks) * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(2,20) AS size_MiB,
       SUM(s.blocks) * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(10,6) AS size_MB,
       l.deduplication,
       l.compression,
       l.encrypt,
       l.cache,
       l.securefile,
       l.in_row,
       l.tablespace_name
  FROM dba_lobs l,
       dba_segments s,
       dba_tablespaces b,
       v$parameter p
 WHERE l.owner = '&&table_owner.'
   AND l.table_name = '&&table_name.'
   AND s.owner = l.owner
   AND s.segment_name = l.segment_name
   AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
   AND b.tablespace_name(+) = l.tablespace_name
   AND p.name = 'db_block_size'
 GROUP BY
       l.column_name,
       l.index_name,
       l.segment_name,
       l.deduplication,
       l.compression,
       l.encrypt,
       l.cache,
       l.securefile,
       l.in_row,
       l.tablespace_name,
       b.block_size,
       p.value
 ORDER BY
       l.column_name
/
--
CL BRE;
--
COL owner FOR A30 HEA 'Owner' TRUNC;
COL table_name FOR A30 HEA 'Table Name' TRUNC;
COL index_name FOR A30 HEA 'Index Name' TRUNC;
COL metadata FOR A200 HEA 'Metadata';
--
PRO
PRO TABLE METADATA (DBMS_METADATA.get_ddl) &&table_owner..&&table_name.
PRO ~~~~~~~~~~~~~~
SELECT t.owner, t.table_name, DBMS_METADATA.get_ddl('TABLE', t.table_name, t.owner) AS metadata
  FROM dba_tables t
 WHERE t.owner = '&&table_owner.'
   AND t.table_name = '&&table_name.' 
 ORDER BY
       t.owner, t.table_name
/
--
PRO
PRO INDEX METADATA (DBMS_METADATA.get_ddl) &&table_owner..&&table_name.
PRO ~~~~~~~~~~~~~~
SELECT i.owner, i.table_name, i.index_name, DBMS_METADATA.get_ddl('INDEX', i.index_name, i.owner) AS metadata
  FROM dba_tables t,
       dba_indexes i
 WHERE t.owner = '&&table_owner.'
   AND t.table_name = '&&table_name.' 
   AND i.table_owner = t.owner
   AND i.table_name = t.table_name
 ORDER BY
       i.owner,
       i.table_name,
       i.index_name
/
--

COL kievlive FOR A8 HEA 'KievLive';
--
PRO
PRO KIEV LIVE (dba_tab_histograms) &&table_owner..&&table_name.
PRO ~~~~~~~~~
COL num_rows FOR 999,999,999,990 HEA 'Num Rows';
SELECT SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(SUBSTR(LPAD(TO_CHAR(h.endpoint_value,'fmxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),30,'0'),1,12)), 1, 8) kievlive,
       h.endpoint_number - LAG(h.endpoint_number, 1, 0) OVER (ORDER BY h.endpoint_value) num_rows
  FROM dba_tab_histograms h
 WHERE h.owner = '&&table_owner.'
   AND h.table_name = '&&table_name.'
  -- AND h.column_name = 'XXXX'
 ORDER BY
       1
/

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

define schema='ANUJ'
define table_name='EMP'

column degree format a6 heading "Degree"
column monitoring format a6 heading "Moni-|toring"
column buffer_pool format a7 heading "Buffer|Pool"
column cluster_name format a7 heading "Cluster"
column iot_type format a7 heading "IOT|Type"
column iot_name format a30 heading "IOT Name"
column block_size format a7 heading "Block-|size"
column tab_size format a9 heading "Size (MB)"
column percent_modified format 9999 heading "Modifi-|cations (%)"
select 
nvl(t.tablespace_name,'None') tablespace,
nvl2(ts.block_size, ts.block_size/1024||' KB','None') AS block_size,
CASE WHEN t.blocks IS NULL AND ts.block_size IS NOT NULL THEN 'No Statistics'
WHEN t.blocks IS NOT NULL and ts.block_size IS NULL THEN 'None'
ELSE to_char(round(t.blocks * ts.block_size/1048576,1))
END AS tab_size,
t.monitoring,
-- percentage of modified rows since last statistics gathering
CASE WHEN nvl(t.num_rows,0) > 0 THEN round((m.inserts + m.updates + m.deletes)/t.num_rows * 100,2)
ELSE null
END AS percent_modified,
nvl(t.buffer_pool,'None') buffer_pool,
trim(t.degree) degree,
t.cluster_name,
t.iot_type,
t.iot_name
from all_tables t, user_tablespaces ts, all_tab_modifications m
where 
    t.owner = '&schema'
and t.table_name = '&table_name'
and ts.tablespace_name(+)=t.tablespace_name
-- must use outer join since there may be no rows for the table in all_tab_modifications
and t.owner=m.table_owner(+)
and t.table_name=m.table_name(+)
/





col COLUMN_NAME for a20
col col for a20


select
    COLUMN_NAME,
	decode(t.DATA_TYPE,
		'NUMBER', t.DATA_TYPE|| 
			-- if scale is not null, open parenthesis
			decode(t.DATA_SCALE,NULL,NULL,'(') ||
			decode(t.DATA_PRECISION,NULL, 
				-- if precision is null and scale is null, then plain number
				-- else number(38,scale)
				decode(t.DATA_SCALE,NULL,NULL,38), 
				t.DATA_PRECISION) ||
			decode(t.DATA_SCALE,0,NULL,NULL,NULL,','||t.DATA_SCALE)
			||
			-- if scale is not null, close parenthesis
			decode(t.DATA_SCALE,NULL,NULL,')'),
		'VARCHAR2', t.DATA_TYPE||'('|| t.DATA_LENGTH||')',
		'CHAR', t.DATA_TYPE||'('|| t.DATA_LENGTH||')',
		t.data_type
	)
	||' '||
	decode(t.nullable,
              'N','NOT NULL',
              'n','NOT NULL',
              NULL) col,
    NUM_DISTINCT,
    DENSITY,
    NUM_BUCKETS,
    NUM_NULLS,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    t.last_analyzed
from all_tab_columns t
where 
  --  table_name = table_name
--and owner = :schema
   owner = '&schema'
and table_name = '&table_name'
order by column_id
/



column created heading "Created" format a15
column last_ddl_time heading "Last DDL" format a15
column index_owner_name heading "Index Owner|and Name" format a50 word_wrapped
column partitioned heading "Parti-|tioned" format a7
select 
    i.owner ||' . ' || i.INDEX_NAME AS index_owner_name,
    i.index_type,
    i.partitioned,
    o.created,
    o.last_ddl_time,
    i.last_analyzed,
    i.status,
    SAMPLE_SIZE,
    nvl2(i.tablespace_name,i.tablespace_name,'N/A') AS tablespace_name,
    nvl2(i.buffer_pool,i.buffer_pool,'N/A') AS buffer_pool,
    --decode(i.buffer_pool,NULL,'DEFAULT',i.buffer_pool) AS buffer_pool,
    trim(i.degree) degree
from 
    all_indexes i, all_objects o
where
    i.index_name=o.object_name 
and i.owner=o.owner
and i.table_name = '&table_name'
and i.table_owner = '&schema'
and o.object_type='INDEX'
/





-- index statistics
select 
    owner ||' . ' || INDEX_NAME AS index_owner_name,
    INDEX_TYPE,
    decode(UNIQUENESS,'UNIQUE','YES','NONUNIQUE','NO') UNIQUENESS,
    BLEVEL BLev,
    LEAF_BLOCKS,
    DISTINCT_KEYS,
    NUM_ROWS,
    AVG_LEAF_BLOCKS_PER_KEY,
    AVG_DATA_BLOCKS_PER_KEY,
    CLUSTERING_FACTOR,
    GLOBAL_STATS,
    USER_STATS
from 
    all_indexes
where 
    table_name = '&table_name'
and table_owner = '&schema'
/





break on index_owner_name
-- index structure
select
    i.index_owner ||' . ' || i.INDEX_NAME AS index_owner_name,
    i.COLUMN_NAME,
    i.COLUMN_POSITION,
	decode(t.DATA_TYPE,
		'NUMBER', t.DATA_TYPE|| 
			-- if scale is not null, open parenthesis
			decode(t.DATA_SCALE,NULL,NULL,'(') ||
			decode(t.DATA_PRECISION,NULL, 
				-- if precision is null and scale is null, then plain number
				-- else number(38,scale)
				decode(t.DATA_SCALE,NULL,NULL,38), 
				t.DATA_PRECISION) ||
			decode(t.DATA_SCALE,0,NULL,NULL,NULL,','||t.DATA_SCALE)
			||
			-- if scale is not null, close parenthesis
			decode(t.DATA_SCALE,NULL,NULL,')'),
		'VARCHAR2', t.DATA_TYPE||'('|| t.DATA_LENGTH||')',
		'CHAR', t.DATA_TYPE||'('|| t.DATA_LENGTH||')',
		t.data_type
	)
	||' '||
	decode(t.nullable,
              'N','NOT NULL',
              'n','NOT NULL',
              NULL) col
from 
    all_ind_columns i,
    all_tab_columns t
where 
    i.table_name = '&table_name'
and owner = '&schema'
and i.table_name = t.table_name
and i.column_name = t.column_name
and i.table_owner=t.owner
order by index_name,column_position
/






-- function based indexes
set long 10000
column column_expression format a20 word_wrapped
SELECT 
	i.index_owner ||' . ' || i.INDEX_NAME AS index_owner_name,
	i.column_position, i.column_expression
FROM all_ind_expressions i
where 
    i.table_name = '&table_name'
and i.table_owner = '&schema'
ORDER BY i.column_position
;
	


-- set verify on
column LOB_COLUMN_NAME  heading "LOB Column|Name" format a30
column chunk format 99999
column cache format a5
column in_row heading "In|Row" format a3
column pctversion heading "Pct-|version" format 9999999
col in_row format a6
select l.column_name AS lob_column_name,
l.segment_name,
-- l.tablespace_name -- 10g
&column_tablespace_name,
&column_block_size,
--ts.block_size/1024||' KB' block_size,
l.chunk,
l.pctversion,
l.retention, -- Oracle9i and 10gR1 cannot correctly report retention and pctversion (see bug 3844939)
l.cache,
l.in_row
--all_lobs l, user_tablespaces ts -- 10g
--all_lobs l -- 9i
from
&from_tables.
where l.owner =  '&schema'
and l.table_name = '&table_name'
--and l.tablespace_name=ts.tablespace_name
--and_clause.
;



col TABLE_PARTITION_NAME  for a20
column high_value format a25 word_wrapped
select
    PARTITION_NAME AS table_partition_name,
    tablespace_name,
    high_value,
    NUM_ROWS,
    BLOCKS,
    EMPTY_BLOCKS,
    AVG_SPACE,
    CHAIN_CNT,
    AVG_ROW_LEN,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    t.last_analyzed
from 
    all_tab_partitions t
where 
    table_owner = '&schema'
and table_name = '&table_name'
order by partition_position
/



break on table_partition_name
select
    PARTITION_NAME AS table_partition_name,
    COLUMN_NAME,
    NUM_DISTINCT,
    DENSITY,
    NUM_BUCKETS,
    NUM_NULLS,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    t.last_analyzed
from 
    all_PART_COL_STATISTICS t
where 
    table_name = '&table_name'
and owner = '&schema'

/



col INDEX_NAME for a20
-- Index Partitions
col status format a6
break on index_name
select 
    t.INDEX_NAME,
    t.PARTITION_NAME AS index_partition_name,
    t.status,
    t.BLEVEL BLev,
    t.LEAF_BLOCKS,
    t.DISTINCT_KEYS,
    t.NUM_ROWS,
    t.AVG_LEAF_BLOCKS_PER_KEY,
    t.AVG_DATA_BLOCKS_PER_KEY,
    t.CLUSTERING_FACTOR,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    t.last_analyzed
from 
    all_ind_partitions t, 
    all_indexes i
where 
    i.table_name = '&table_name'
and i.table_owner = '&schema'
and i.owner = t.index_owner
and i.index_name=t.index_name
/

select 
    PARTITION_NAME,
    SUBPARTITION_NAME,
    NUM_ROWS,
    BLOCKS,
    EMPTY_BLOCKS,
    AVG_SPACE,
    CHAIN_CNT,
    AVG_ROW_LEN,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    t.last_analyzed
from 
    all_tab_subpartitions t
where 
    table_owner = '&schema'
and table_name = '&table_name'
order by SUBPARTITION_POSITION
/


break on partition_name
select 
    p.PARTITION_NAME,
    t.SUBPARTITION_NAME,
    t.COLUMN_NAME,
    t.NUM_DISTINCT,
    t.DENSITY,
    t.NUM_BUCKETS,
    t.NUM_NULLS,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    t.last_analyzed
from 
    all_SUBPART_COL_STATISTICS t, 
    all_tab_subpartitions p
where 
    t.table_name = '&table_name'
and t.owner = '&schema'
and t.subpartition_name = p.subpartition_name
and t.owner = p.table_owner
and t.table_name=p.table_name
/




break on partition_name
select 
    t.INDEX_NAME,
    t.PARTITION_NAME AS index_partition_name,
    t.SUBPARTITION_NAME,
    t.BLEVEL BLev,
    t.LEAF_BLOCKS,
    t.DISTINCT_KEYS,
    t.NUM_ROWS,
    t.AVG_LEAF_BLOCKS_PER_KEY,
    t.AVG_DATA_BLOCKS_PER_KEY,
    t.CLUSTERING_FACTOR,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    t.last_analyzed
from 
    all_ind_subpartitions t, 
    all_indexes i
where 
    i.table_name =  '&table_name'
and i.table_owner = '&schema'
and i.owner = t.index_owner
and i.index_name=t.index_name
/

define schema='ODS'
define table_name='SETL_STMT_LINE_CHILDREN'

column degree format a6 heading "Degree"
column monitoring format a6 heading "Moni-|toring"
column buffer_pool format a7 heading "Buffer|Pool"
column cluster_name format a7 heading "Cluster"
column iot_type format a7 heading "IOT|Type"
column iot_name format a30 heading "IOT Name"
column block_size format a7 heading "Block-|size"
column tab_size format a9 heading "Size (MB)"
column percent_modified format 9999 heading "Modifi-|cations (%)"
select 
nvl(t.tablespace_name,'None') tablespace,
nvl2(ts.block_size, ts.block_size/1024||' KB','None') AS block_size,
CASE WHEN t.blocks IS NULL AND ts.block_size IS NOT NULL THEN 'No Statistics'
WHEN t.blocks IS NOT NULL and ts.block_size IS NULL THEN 'None'
ELSE to_char(round(t.blocks * ts.block_size/1048576,1))
END AS tab_size,
t.monitoring,
-- percentage of modified rows since last statistics gathering
CASE WHEN nvl(t.num_rows,0) > 0 THEN round((m.inserts + m.updates + m.deletes)/t.num_rows * 100,2)
ELSE null
END AS percent_modified,
nvl(t.buffer_pool,'None') buffer_pool,
trim(t.degree) degree,
t.cluster_name,
t.iot_type,
t.iot_name
from all_tables t, user_tablespaces ts, all_tab_modifications m
where 
    t.owner = '&schema'
and t.table_name = '&table_name'
and ts.tablespace_name(+)=t.tablespace_name
-- must use outer join since there may be no rows for the table in all_tab_modifications
and t.owner=m.table_owner(+)
and t.table_name=m.table_name(+)
/





col COLUMN_NAME for a20
col col for a20


select
    COLUMN_NAME,
	decode(t.DATA_TYPE,
		'NUMBER', t.DATA_TYPE|| 
			-- if scale is not null, open parenthesis
			decode(t.DATA_SCALE,NULL,NULL,'(') ||
			decode(t.DATA_PRECISION,NULL, 
				-- if precision is null and scale is null, then plain number
				-- else number(38,scale)
				decode(t.DATA_SCALE,NULL,NULL,38), 
				t.DATA_PRECISION) ||
			decode(t.DATA_SCALE,0,NULL,NULL,NULL,','||t.DATA_SCALE)
			||
			-- if scale is not null, close parenthesis
			decode(t.DATA_SCALE,NULL,NULL,')'),
		'VARCHAR2', t.DATA_TYPE||'('|| t.DATA_LENGTH||')',
		'CHAR', t.DATA_TYPE||'('|| t.DATA_LENGTH||')',
		t.data_type
	)
	||' '||
	decode(t.nullable,
              'N','NOT NULL',
              'n','NOT NULL',
              NULL) col,
    NUM_DISTINCT,
    DENSITY,
    NUM_BUCKETS,
    NUM_NULLS,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    t.last_analyzed
from all_tab_columns t
where 
  --  table_name = table_name
--and owner = :schema
   owner = '&schema'
and table_name = '&table_name'
order by column_id
/



column created heading "Created" format a15
column last_ddl_time heading "Last DDL" format a15
column index_owner_name heading "Index Owner|and Name" format a50 word_wrapped
column partitioned heading "Parti-|tioned" format a7
select 
    i.owner ||' . ' || i.INDEX_NAME AS index_owner_name,
    i.index_type,
    i.partitioned,
    o.created,
    o.last_ddl_time,
    i.last_analyzed,
    i.status,
    SAMPLE_SIZE,
    nvl2(i.tablespace_name,i.tablespace_name,'N/A') AS tablespace_name,
    nvl2(i.buffer_pool,i.buffer_pool,'N/A') AS buffer_pool,
    --decode(i.buffer_pool,NULL,'DEFAULT',i.buffer_pool) AS buffer_pool,
    trim(i.degree) degree
from 
    all_indexes i, all_objects o
where
    i.index_name=o.object_name 
and i.owner=o.owner
and i.table_name = '&table_name'
and i.table_owner = '&schema'
and o.object_type='INDEX'
/





-- index statistics
select 
    owner ||' . ' || INDEX_NAME AS index_owner_name,
    INDEX_TYPE,
    decode(UNIQUENESS,'UNIQUE','YES','NONUNIQUE','NO') UNIQUENESS,
    BLEVEL BLev,
    LEAF_BLOCKS,
    DISTINCT_KEYS,
    NUM_ROWS,
    AVG_LEAF_BLOCKS_PER_KEY,
    AVG_DATA_BLOCKS_PER_KEY,
    CLUSTERING_FACTOR,
    GLOBAL_STATS,
    USER_STATS
from 
    all_indexes
where 
    table_name = '&table_name'
and table_owner = '&schema'
/





break on index_owner_name
-- index structure
select
    i.index_owner ||' . ' || i.INDEX_NAME AS index_owner_name,
    i.COLUMN_NAME,
    i.COLUMN_POSITION,
	decode(t.DATA_TYPE,
		'NUMBER', t.DATA_TYPE|| 
			-- if scale is not null, open parenthesis
			decode(t.DATA_SCALE,NULL,NULL,'(') ||
			decode(t.DATA_PRECISION,NULL, 
				-- if precision is null and scale is null, then plain number
				-- else number(38,scale)
				decode(t.DATA_SCALE,NULL,NULL,38), 
				t.DATA_PRECISION) ||
			decode(t.DATA_SCALE,0,NULL,NULL,NULL,','||t.DATA_SCALE)
			||
			-- if scale is not null, close parenthesis
			decode(t.DATA_SCALE,NULL,NULL,')'),
		'VARCHAR2', t.DATA_TYPE||'('|| t.DATA_LENGTH||')',
		'CHAR', t.DATA_TYPE||'('|| t.DATA_LENGTH||')',
		t.data_type
	)
	||' '||
	decode(t.nullable,
              'N','NOT NULL',
              'n','NOT NULL',
              NULL) col
from 
    all_ind_columns i,
    all_tab_columns t
where 
    i.table_name = '&table_name'
and owner = '&schema'
and i.table_name = t.table_name
and i.column_name = t.column_name
and i.table_owner=t.owner
order by index_name,column_position
/






-- function based indexes
set long 10000
column column_expression format a20 word_wrapped
SELECT 
	i.index_owner ||' . ' || i.INDEX_NAME AS index_owner_name,
	i.column_position, i.column_expression
FROM all_ind_expressions i
where 
    i.table_name = '&table_name'
and i.table_owner = '&schema'
ORDER BY i.column_position
;
	


-- set verify on
column LOB_COLUMN_NAME  heading "LOB Column|Name" format a30
column chunk format 99999
column cache format a5
column in_row heading "In|Row" format a3
column pctversion heading "Pct-|version" format 9999999
col in_row format a6
select l.column_name AS lob_column_name,
l.segment_name,
-- l.tablespace_name -- 10g
&column_tablespace_name,
&column_block_size,
--ts.block_size/1024||' KB' block_size,
l.chunk,
l.pctversion,
l.retention, -- Oracle9i and 10gR1 cannot correctly report retention and pctversion (see bug 3844939)
l.cache,
l.in_row
--all_lobs l, user_tablespaces ts -- 10g
--all_lobs l -- 9i
from
&from_tables.
where l.owner =  '&schema'
and l.table_name = '&table_name'
--and l.tablespace_name=ts.tablespace_name
--and_clause.
;



col TABLE_PARTITION_NAME  for a20
column high_value format a25 word_wrapped
select
    PARTITION_NAME AS table_partition_name,
    tablespace_name,
    high_value,
    NUM_ROWS,
    BLOCKS,
    EMPTY_BLOCKS,
    AVG_SPACE,
    CHAIN_CNT,
    AVG_ROW_LEN,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    t.last_analyzed
from 
    all_tab_partitions t
where 
    table_owner = '&schema'
and table_name = '&table_name'
order by partition_position
/



break on table_partition_name
select
    PARTITION_NAME AS table_partition_name,
    COLUMN_NAME,
    NUM_DISTINCT,
    DENSITY,
    NUM_BUCKETS,
    NUM_NULLS,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    t.last_analyzed
from 
    all_PART_COL_STATISTICS t
where 
    table_name = '&table_name'
and owner = '&schema'

/



col INDEX_NAME for a20
-- Index Partitions
col status format a6
break on index_name
select 
    t.INDEX_NAME,
    t.PARTITION_NAME AS index_partition_name,
    t.status,
    t.BLEVEL BLev,
    t.LEAF_BLOCKS,
    t.DISTINCT_KEYS,
    t.NUM_ROWS,
    t.AVG_LEAF_BLOCKS_PER_KEY,
    t.AVG_DATA_BLOCKS_PER_KEY,
    t.CLUSTERING_FACTOR,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    t.last_analyzed
from 
    all_ind_partitions t, 
    all_indexes i
where 
    i.table_name = '&table_name'
and i.table_owner = '&schema'
and i.owner = t.index_owner
and i.index_name=t.index_name
/

select 
    PARTITION_NAME,
    SUBPARTITION_NAME,
    NUM_ROWS,
    BLOCKS,
    EMPTY_BLOCKS,
    AVG_SPACE,
    CHAIN_CNT,
    AVG_ROW_LEN,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    t.last_analyzed
from 
    all_tab_subpartitions t
where 
    table_owner = '&schema'
and table_name = '&table_name'
order by SUBPARTITION_POSITION
/


break on partition_name
select 
    p.PARTITION_NAME,
    t.SUBPARTITION_NAME,
    t.COLUMN_NAME,
    t.NUM_DISTINCT,
    t.DENSITY,
    t.NUM_BUCKETS,
    t.NUM_NULLS,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    t.last_analyzed
from 
    all_SUBPART_COL_STATISTICS t, 
    all_tab_subpartitions p
where 
    t.table_name = '&table_name'
and t.owner = '&schema'
and t.subpartition_name = p.subpartition_name
and t.owner = p.table_owner
and t.table_name=p.table_name
/




break on partition_name
select 
    t.INDEX_NAME,
    t.PARTITION_NAME AS index_partition_name,
    t.SUBPARTITION_NAME,
    t.BLEVEL BLev,
    t.LEAF_BLOCKS,
    t.DISTINCT_KEYS,
    t.NUM_ROWS,
    t.AVG_LEAF_BLOCKS_PER_KEY,
    t.AVG_DATA_BLOCKS_PER_KEY,
    t.CLUSTERING_FACTOR,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    t.last_analyzed
from 
    all_ind_subpartitions t, 
    all_indexes i
where 
    i.table_name =  '&table_name'
and i.table_owner = '&schema'
and i.owner = t.index_owner
and i.index_name=t.index_name
/

Oracle current session wait

oracle session wait

SET TIME ON VERIFY OFF
set linesize 200
COLUMN event FORMAT A30 HEADING 'Event' TRUNCATE
COLUMN wait_state FORMAT A7 HEADING 'State'
COLUMN seconds_in_wait FORMAT 999999999 HEADING 'Time|Waiting'
COLUMN wait_time FORMAT 999999 HEADING 'Time|Waited'
COLUMN p1 FORMAT A20 HEADING 'P1' TRUNCATE
COLUMN p2 FORMAT A20 HEADING 'P2' TRUNCATE
COLUMN p3 FORMAT A20 HEADING 'P3' TRUNCATE
col sid_serial format a15
col username format a20

SELECT vs.sid||','||vs.serial# sid_serial,
vs.username username,
vs.event,
CASE WHEN vs.state = 'WAITING' THEN vs.state
ELSE 'WAITED'
END wait_state,
vs.seconds_in_wait,
vs.wait_time,
DECODE(vs.p1text, NULL, NULL, vs.p1text||'='||vs.p1) p1,
DECODE(vs.p2text, NULL, NULL, vs.p2text||'='||vs.p2) p2,
DECODE(vs.p3text, NULL, NULL, vs.p3text||'='||vs.p3) p3
FROM v$session vs

Oracle Create user script

Create user script

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

from

Author : Daniel W. Fink




DEFINE schema_owner = &1
-- WHENEVER SQLERROR EXIT FAILURE

SET LINESIZE 132 PAGESIZE 0 FEEDBACK off VERIFY off TRIMSPOOL on LONG 1000000
COLUMN ddl_string FORMAT A100 WORD_WRAP
COLUMN row_order FORMAT 999 NOPRINT

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);

COLUMN rundate FORMAT A8 NEW_VALUE ddl_date NOPRINT
COLUMN dbname FORMAT A10 NEW_VALUE db_name NOPRINT
COLUMN spoolname FORMAT A50 NEW_VALUE spool_name NOPRINT
COLUMN maxtextlength FORMAT 9999 NEW_VALUE max_text_length NOPRINT
COLUMN schemaid NEW_VALUE schema_id NOPRINT

SELECT u.user# schemaid
FROM sys.user$ u
WHERE u.name = UPPER('&&schema_owner')
/

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') rundate
FROM dual
/

SELECT UPPER(SYS_CONTEXT('USERENV', 'DB_NAME')) dbname
FROM dual
/

SELECT 'schema_'||'&&db_name'||'_'||'&&schema_owner'||'_'||'&&ddl_date'||'_ddl.log' spoolname
FROM dual
/

SELECT MAX(LENGTH(s.source)) maxtextlength
FROM sys.obj$ o,
sys.source$ s
WHERE o.owner# = &&schema_id
AND o.obj# = s.obj#
/

SET LINESIZE &&max_text_length

-- SPOOL schema_&&db_name\_&&schema_owner\_&&ddl_date\_ddl.sql
SPOOL schema_ddl.sql

PROMPT WHENEVER SQLERROR EXIT FAILURE
PROMPT WHENEVER OSERROR EXIT FAILURE
PROMPT SPOOL &&spool_name
PROMPT
PROMPT

SELECT 0 row_order, '-- Object Count' ddl_string
FROM dual
UNION
SELECT 1 row_order, '-- '||DECODE(o.type#,
1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM',
6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
10, 'NON-EXISTENT', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE',
14, 'TYPE BODY', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 42, 'MATERIALIZED VIEW',
43, 'DIMENSION', 56, 'JAVA DATA', 'UNDEFINED')||' -- '||COUNT(1)
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
GROUP BY 1, o.type#
UNION
SELECT 2 row_order, CHR(10)||CHR(10)
FROM dual
/

PROMPT
PROMPT -- Profile Creation
PROMPT

SELECT DBMS_METADATA.GET_DDL('PROFILE', pr.name) ddl_string
FROM (SELECT DISTINCT pi.name
FROM sys.profname$ pi
WHERE pi.name != 'DEFAULT') pr
/

PROMPT
PROMPT -- User Creation
PROMPT

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('USER', '&&schema_owner')) ddl_string
FROM dual
/

PROMPT
PROMPT -- User Tablespace Quotas
PROMPT
-- This is failing with an error message, causing the script to terminate. No workaround yet.

/*
SELECT CASE
WHEN COUNT(1) != 0 THEN DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', '&&schema_owner')
ELSE NULL
END ddl_string
FROM sys.ts$ ts,
sys.tsq$ tq
WHERE tq.user# = &&schema_id
AND ts.ts# = tq.ts#
/
*/

PROMPT
PROMPT -- User Role
PROMPT

SELECT /*+ ordered */ 'GRANT "'||u.name||'" TO "'||upper('&&schema_owner')||'"'||
CASE WHEN min(sa.option$) = 1 THEN ' WITH ADMIN OPTION;' ELSE ';' END ddl_string
FROM sys.sysauth$ sa,
sys.user$ u
WHERE sa.grantee# = &&schema_id
AND u.user# = sa.privilege#
AND sa.grantee# != 1
GROUP BY u.name
/

PROMPT
PROMPT -- User System Privileges
PROMPT
-- If the dbms_metadata call for system grants does not find any for the schema owner
-- it fails with an error message, causing the script to terminate. The SELECT is used as a
-- workaround. A TAR has been filed w/Oracle (response 'expected behaviour' - RFE filed)

SELECT CASE
WHEN COUNT(1) != 0 THEN DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '&&schema_owner')
ELSE NULL
END ddl_string
FROM sys.sysauth$ sa
WHERE sa.grantee# = &&schema_id
/

PROMPT
PROMPT -- User Object Privileges
PROMPT
-- If the dbms_metadata call for object grants does not find any for the schema owner
-- it fails with an error message, causing the script to terminate. The SELECT is used as a
-- workaround. A TAR has been filed w/Oracle (response 'expected behaviour' - RFE filed)

SELECT CASE
WHEN COUNT(1) != 0 THEN DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', '&&schema_owner')
ELSE NULL
END ddl_string
FROM sys.objauth$ oa
WHERE oa.grantee# = &&schema_id
/


PROMPT
PROMPT -- Schema Sequences
PROMPT

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('SEQUENCE', o.name,'&&schema_owner')) ddl_string
FROM sys.seq$ s,
sys.obj$ o
WHERE o.owner# = &&schema_id
AND o.obj# = s.obj#
/


PROMPT
PROMPT -- Schema Database Links
PROMPT

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('DB_LINK', l.name,'&&schema_owner')) ddl_string
FROM sys.link$ l
WHERE l.owner# = &&schema_id
/


PROMPT
PROMPT -- Schema Directories
PROMPT

SELECT DBMS_METADATA.GET_DDL('DIRECTORY', o.name, '&&schema_owner') ddl_string
FROM sys.obj$ o,
sys.dir$ d
WHERE o.owner# = &&schema_id
AND o.obj# = d.obj#
/

PROMPT
PROMPT -- Schema Tables
PROMPT

/* Add the BITAND(o.flags, 128) to exclude tables in the recyclebin */

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLE', o.name,'&&schema_owner')) ddl_string
FROM sys.obj$ o,
sys.tab$ t
WHERE o.owner# = &&schema_id
AND o.obj# = t.obj#
AND BITAND(o.flags, 128) = 0
/

PROMPT
PROMPT -- Schema Table RI Constraints
PROMPT

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT', oc.name, '&&schema_owner')||'/' ddl_string
FROM sys.con$ oc,
sys.obj$ o,
sys.cdef$ c
WHERE oc.owner# = &&schema_id
AND oc.con# = c.con#
AND c.obj# = o.obj#
AND c.type# = 4
/

PROMPT
PROMPT -- Schema Indexes
PROMPT
--- This is used to exclude primary key and unique key indexes that have already been defined
--- as part of the table generation statement.

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('INDEX', o.name,'&&schema_owner')) ddl_string
FROM sys.ind$ i,
sys.obj$ o
WHERE o.owner# = &&schema_id
AND o.obj# = i.obj#
AND bitand(i.property,1) = 1
AND i.type# != 8
/

PROMPT
PROMPT -- Schema Views
PROMPT
-- View extraction is not functioning properly in 9.2. Breaks occur in middle of words (column_names, clauses, etc).
-- Bug has been accepted with Oracle. No response of when/if backport to 9.2.0.5 will be available

-- SELECT REPLACE(TO_CHAR(DBMS_METADATA.GET_DDL('VIEW', v.view_name,'&&schema_owner')), '","','", "') ddl_string
-- FROM dba_views v
-- WHERE v.owner = '&&schema_owner'
-- /

-- Here is a workaround version
COLUMN viewname NOPRINT

CREATE GLOBAL TEMPORARY TABLE parsed_view_text
(view_name VARCHAR2(30),
text_id NUMBER,
view_text VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS;

DECLARE
num_iter NUMBER := 0;
whole_clob CLOB;
parsed_string VARCHAR2(32767);
start_pos NUMBER := 1;
num_chars NUMBER := 3000;

CURSOR view_text_cur IS
SELECT o.name view_name, v.text text, v.textlength text_length, v.cols view_columns
FROM sys.obj$ o,
sys.view$ v
WHERE o.obj# = v.obj#
AND o.owner# = &&schema_id;

view_text_rec view_text_cur%ROWTYPE;

BEGIN

FOR view_text_rec IN view_text_cur
LOOP
whole_clob := TO_CLOB(view_text_rec.text);

DBMS_OUTPUT.PUT_LINE('View Name: '||view_text_rec.view_name||' Text Length :'|| view_text_rec.text_length);
LOOP
IF (view_text_rec.text_length - start_pos) < 3000
THEN
parsed_string := SUBSTR(whole_clob, start_pos);
INSERT INTO parsed_view_text VALUES (view_text_rec.view_name, (view_text_rec.view_columns + num_iter), parsed_string||CHR(10)||'/');
EXIT;
END if;
parsed_string := SUBSTR(whole_clob, start_pos, 3000);
num_chars := GREATEST(INSTR(parsed_string, ', ', -1, 1), INSTR(parsed_string, ',"', -1, 1),
(INSTR(parsed_string, '),', -1, 1)+1), INSTR(parsed_string, ')', -1, 1));
parsed_string := SUBSTR(whole_clob, start_pos, num_chars);
INSERT INTO parsed_view_text VALUES (view_text_rec.view_name, (view_text_rec.view_columns + num_iter), parsed_string);
start_pos := start_pos + num_chars;
num_iter := num_iter + 1;
END LOOP;
COMMIT;
start_pos := 1;
num_chars := 3000;
num_iter := 1;
END LOOP;
END;
/

SELECT 0 row_order,
o.name viewname,
'CREATE OR REPLACE FORCE VIEW "'||'&&schema_owner'||'"."'||o.name||'"' ddl_string
FROM sys.obj$ o,
sys.view$ v
WHERE o.obj# = v.obj#
AND o.owner# = &&schema_iD
UNION
SELECT decode(c.col#, 0, to_number(null), c.col#) row_order,
o.name viewname,
CASE WHEN decode(c.col#, 0, to_number(null), c.col#) = 1 THEN '("'||c.name||'",'
WHEN decode(c.col#, 0, to_number(null), c.col#) = v.cols THEN ' "'||c.name||'") AS '
ELSE ' "'||c.name||'",'
END ddl_string
FROM sys.col$ c,
sys.obj$ o,
sys.view$ v
WHERE o.obj# = c.obj#
AND o.owner# = &&schema_id
AND o.obj# = v.obj#
UNION
SELECT pv.text_id row_order,
pv.view_name viewname,
REPLACE(TO_CHAR(pv.view_text), '","', '", "') ddl_string
FROM parsed_view_text pv
ORDER BY viewname, row_order
/

TRUNCATE TABLE parsed_view_text;
DROP TABLE parsed_view_text;

SET LINESIZE 4005
COLUMN ddl_string FORMAT A4000

PROMPT
PROMPT -- Schema Functions
PROMPT

SELECT DBMS_METADATA.GET_DDL('FUNCTION', o.name,'&&schema_owner') ddl_string
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
AND o.type# = 8
/

PROMPT
PROMPT -- Schema Packages (specs and body)
PROMPT

SELECT DBMS_METADATA.GET_DDL('PACKAGE', o.name,'&&schema_owner') ddl_string
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
AND o.type# = 9
/

PROMPT
PROMPT -- Schema Procedures
PROMPT

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', o.name,'&&schema_owner') ddl_string
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
AND o.type# = 7
/

COLUMN ddl_string FORMAT A125
SET LINESIZE 132

PROMPT
PROMPT -- Schema Synonyms
PROMPT

SELECT 'CREATE SYNONYM "&&schema_owner"."'||o.name||'" FOR "'||s.owner||'"."'||s.name||NVL2(s.node, '@'||s.node||'";', '";') ddl_string
FROM sys.syn$ s,
sys.obj$ o
WHERE o.owner# = &&schema_id
AND o.obj# = s.obj#
AND o.type# = 5
/

PROMPT
PROMPT -- End of ddl
PROMPT

PROMPT SET LINESIZE 132 PAGESIZE 45 FEEDBACK OFF
PROMPT COLUMN line FORMAT 9999
PROMPT COLUMN text FORMAT A40 WORD_WRAP
PROMPT
PROMPT -- Checking for errors
PROMPT
PROMPT SELECT name, type, line, text
PROMPT FROM dba_errors
PROMPT WHERE owner = '&&schema_owner'
PROMPT /

PROMPT SPOOL OFF

SPOOL OFF

UNDEFINE schema_owner

=================================================================================
Or


Use Datapump




SQL> CREATE DIRECTORY anujtmp AS '/tmp';

Directory created.



SQL> CREATE DIRECTORY anujtmp AS '/tmp';

Directory created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@apt-amd-02:~> expdp system/sys dumpfile=scott_metadata.dmp schemas=scott content=metadata_only

Export: Release 11.2.0.1.0 - Production on Thu Sep 1 13:13:49 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=scott_metadata.dmp schemas=scott content=metadata_only
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/opt/app/oracle/admin/orcl/dpdump/scott_metadata.dmp <<<<<<<<<<<<<<<<< default location
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:16:37




or with DIRECTORY



oracle@apt-amd-02:~> expdp system/sys dumpfile=scott_metadata.dmp schemas=scott content=metadata_only DIRECTORY=anujtmp

Export: Release 11.2.0.1.0 - Production on Thu Sep 1 13:33:35 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=scott_metadata.dmp schemas=scott content=metadata_only DIRECTORY=anujtmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/tmp/scott_metadata.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:35:04

oracle security

http://blog.red-database-security.com/category/oracle-security/

Unix find command on shell script

cat mfind.sh


echo "enter the path:\c"
read path
echo "enter the file name:\c"
read filename
find $path -name $filename -print > x
nl=`wc -l < x`
if [ $nl -eq 0 ]
then
echo File not found
else
cat < x
fi

Unix tar command

tar :Description The "tar" command stands for tape archive.

This command is used to create new archives, list files in existing archives, and extract files from archives.

c - add to archive, using an absolute path
x - extract from archive
t - read the contents of an archive
f - you work with a file, otherwise it's a tape
p - preserve file's permissions and modes
v - verbose, display the result
-I - use an include list
X - use an exclude list





Examples tar cvf /dev/rct0 /home

This command writes a tar archive to the tape device /dev/rct0. It copies the files in the /home directory, and all subdirectories of /home to the tape device.




tar cvf /tmp/home.tar /home

This command creates a tar archive named /tmp/home.tar. The tar command copies the files in the /home directory, and all subdirectories of /home.


tar cvf /tmp/home.tar /home


tar -cvf /users/anuj/b.tar *.sql

tar xvf b.tar -----extract

tar tvf b.tar ----------list the file

tar rvf b.tar *.* appends files at end of archive


Using tar

Using tar to create a file archive on a tape device:

# tar -cvf /dev/rmt0 file.name

or

# tar -cvf /dev/rmt0 .

or for multiple directory hierarchies

# tar -crvf my.tar `find /tmp/junk -type f` `find /var/tmp -type f`

using tar to list the entries in a file archive on a tape device:
# tar -tvf /dev/rmt0

using tar to retrieve a file from a tape device:
# tar -xvf /dev/rmt0 file.name


To list the contents of your archive issue the following command:

tar tf my_arch.tar

To extract the contents of your archive:

tar xvfp my_arch1.tar

Note, that everything will be extracted exactly at the same location from where it was taken. If some directories do no exist then they will be created.


If you want to include into your archive several different directories you might use the following command:

tar cvf my_arch.tar -I include.lst


create an archive file called "sasdir.tar". In this case, the tar command when executed in your home directory will be:

tar -cvf sasdir.tar -C sasdir .
Suppose, you wish to archive the subdirectories "sasdir" and "spssdir" both of which are in your home directory. In this case the tar command executed in your home directory will be:

tar -cvf sasdir.tar -C sasdir . -C ../spssdir .